投稿
  1. 极客之音首页
  2. 技术随笔

MySQL进阶【四】—— MySQL索引优化实战

小半 • • 技术随笔 • 阅读 227

导读：本篇文章讲解 MySQL进阶【四】—— MySQL索引优化实战，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

传送门：
MySQL进阶【一】—— 一条SQL是如何执行的 https://blog.csdn.net/shehuinidaye/article/details/108690037
MySQL进阶【二】—— 一文讲清楚为什么MySQL选择B+树索引https://blog.csdn.net/shehuinidaye/article/details/108691042
MySQL进阶【三】—— Explain详解与实战https://blog.csdn.net/shehuinidaye/article/details/108692631
MySQL进阶【五】—— MySQL查询优化器是如何选择索引的https://blog.csdn.net/shehuinidaye/article/details/108834484

示例表

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

 ‐‐ 插入一些示例数据
 drop procedure if exists insert_emp;
 delimiter ;;
 create procedure insert_emp()
 begin
 declare i int;
 set i=1;
 while(i<=100000)do
 insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_emp();

1、覆盖索引查询

使用覆盖索引，不需要回表查询，减少io，提升性能

mysql> EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND
age = 22 AND position ='manaer';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL |    1 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

2、like查询时，选择右侧模糊，一般右侧模糊可以总索引

mysql> EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 140     | NULL |    1 |    33.33 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

3、建索引时尽量建联合索引，联合索引可以用到索引下推

索引下推：mysql5.6之后引入的优化，索引下推可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可 以有效的减少回表次数。

4、order by 和 group by优化

case 1
mysql> explain select * from employees where name = 'LiLei' and position = 'dev' order by age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
  • where 条件里面的name = ‘LiLei’ 和 order by 后面的age字段满足了联合索引的最左前缀，执行计划里面key字段下面使用到了索引 idx_name_age_position，说明索引生效了。
  • extra是Using index condition说明排序也使用到索引了
case 2
mysql> explain select * from employees where name = 'LiLei' order by position;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.01 sec)
  • where 条件里面的name = ‘LiLei’ 和 order by 后面的position字段，都存在于联合索引中，执行计划中key字段是使用了索引 idx_name_age_position，where条件后面的联合索引生效了，
  • extra中是Using index condition; Using filesort，说明order by 排序时没有使用到索引，原因是在联合索引 KEY idx_name_age_position (name,age,position) 中，name 和 position不连续，中间还存在字段age，所以排序时没有用到索引 （ Using filesort是性能比较差的）
case 3
mysql> explain select * from employees where name = 'LiLei' order by age,position;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
  • 与case1类似，where条件和extra都用到了索引，原因是where条件字段name，与order by排序字段，是联合索引 KEY idx_name_age_position (name,age,position) 中的连续字段，重点是连续的才可以使用到索引
case 4
mysql> explain select * from employees where name = 'LiLei' order by position,age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.01 sec)
  • 和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为 name,age,position，但是排序的时候age和position颠倒位置了。所以order by 在使用联合索引排序时，尽量不要颠倒顺序
case 5
mysql> explain select * from employees where name = 'LiLei' and age = 18 order b
y position,age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
  • 与case4对比，case5的order by使用到了索引，原因：age=18是个定值，所以排序字段position,age相当于是按照position排序，且where条件和order by条件的顺序刚好是联合索引的顺序
  • where条件如果不满足联合索引顺序，查询优化器会调整为联合索引的顺序，所以where条件改为 age = 18 and name = ‘LiLei’ 执行计划是一样的。（见下面的执行计划）
mysql> explain select * from employees where age = 18 and name = 'LiLei' order by position,age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
case 6
mysql> explain select * from employees where name = 'LiLei' order by age asc,position desc;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
  • order by 使用了Using filesort，原因：order by 默认升序，这里的position倒叙排列，导致与索引排序方式不同，所以无法生效
  • 索引本质就是一个有序排列的数据结构，当查询条件无法满足有序或与索引顺序不一致时，索引会失效
case 7
mysql> explain select * from employees where name in ('LiLei','zhuge') order by age,position;
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
  • order by还是用到了Using filesort ，这里where条件和order by 条件看起来是联合索引KEY idx_name_age_position (name,age,position)的顺序，为什么不生效呢？
  • 解析：where 条件是 name in (‘LiLei’,‘zhuge’)，这里相当于范围查询，而当name不固定时，在索引树中，age和position肯定是无序的，索引无法生效
case 8
mysql> explain select * from employees where name > 'a' order by name;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL | 100185 |    50.00 | Using where; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)
  • 解析：这里要说到mysql查询优化器了，在这条SQL里面首先执行where条件，如果使用索引，需要再回表查询，此时mysql查询优化器认为，全表扫表更快，所以没有使用索引
  • 那如何优化呢？我们试试
mysql> explain select name,age,position from employees where name > 'a' order by name;
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL | 50092 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
  • 使用覆盖索引后，我们发现执行计划中，使用到了索引
  • 分析：还是回到mysql的索引结构上，mysql的二级索引存储着索引字段和主键id，所以扫描索引树不需要回表就可以将select的字段取出（回表会增加很多io开销）
小结
  • 1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
  • 2、order by满足两种情况会使用Using index。
    • (1) order by语句使用索引最左前列。
    • (2) 使用where子句与order by子句条件列组合满足索引最左前列。
  • 3、尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则。
  • 4、如果order by的条件不在索引列上，就会产生Using filesort。
  • 5、能用覆盖索引尽量用覆盖索引
  • 6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/83666.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！