MySQL突击-索引优化实战(一)

Denticle
168 阅读7分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第4天，点击查看活动详情

我们先来创建一张表，所有的实战都基于这张表来进行

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='员工记录表';

再使用存储过程(或者Navicat内置工具)生成一批数据,一下给出示例脚本：

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.png

以上脚本中我们创建了一个name,age,position三个字段组成的联合索引。

1. 联合索引第一个字段使用范围不会走索引

EXPLAIN SELECT * FROM employees WHERE name > '徐晓明' AND age = 22 AND position ='manager';

image.png

我们可以看执行计划标注红框的两列，possible_keys可能使用idx_name_age_position索引，但是通过type我们可以看到最终是ALL，扫描的全表，mysql最终没有选择走idx_name_age_position索引。

分析： 联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

什么是回表： 一张表除主键索引pi之外还有另一个索引ui，那么sql通过ui查询定位到主键之后还需要从pi中再次查询才能找到需要的值，回到ui再次查询这个步骤就叫做回表。

针对上边的例子我们来试试让他强制走索引，然后看下执行效率

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > '徐晓明' AND age = 22 AND position ='演员';

image.png 执行计划确认索引命中没问题，然后我们执行真正的查询例子看下他们的执行时长： 在执行之前我们需要先暂时关闭mysql的查询缓存：

set global query_cache_size=0;
set global query_cache_type=0;

接下来执行一下sql：

--①
SELECT * FROM employees WHERE name > '徐晓明';
--②
SELECT * FROM employees force index(idx_name_age_position) WHERE name > '徐晓明';

最终执行结构(可以执行多次)，单位s：

  • 第一条sql平均执行时长：0.204
  • 第二条sql平均执行时长：0.262

分析： 虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

2. 使用覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > '徐晓明' AND age = 22 AND position ='演员';

image.png 执行时间：0.046

分析： 此处我们需要回想下联合索引的数据结构，通过之前的文章我们知道，联合索引的叶子节点存储了联合索引字段以及主键id。 结合本文例子也就是联合索引存储了name,age,position三个字段以及id字段，我们再看下该例子中的sql，select后不是 * 而是name,age,position这三个字段。 mysql再查询优化时，发现根据辅助索引树可以获取到需要查询的所有字段，不需要再去主键索引树回表去查询，所以会走idx_name_age_position索引进行查询。

什么是覆盖索引： mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有usingindex；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，而不需要去回表。

3. in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

EXPLAIN SELECT * FROM employees WHERE name in ('徐晓明','顾宇宁','于子异') AND age = 22 AND position ='制片人';

image.png

EXPLAIN SELECT * FROM employees WHERE (name = '徐晓明' or name = '顾宇宁') AND age = 22 AND position ='制片人';

image.png

通过执行计划我们发现，以上两条sql执行时，mysql优化最终选择走索引(此时表内数据为10W+)，我们再copy一个employees表，命名为employees_copy且新表只保留10条数据，然后再此执行以上两条执行计划。

image.png

EXPLAIN SELECT * FROM employees_copy WHERE name in ('徐晓明','顾宇宁','于子异') AND age = 22 AND position ='制片人';

image.png

EXPLAIN SELECT * FROM employees_copy WHERE (name = '徐晓明' or name = '顾宇宁') AND age = 22 AND position ='制片人';

image.png

4. like KK% 一般情况都会走索引

EXPLAIN SELECT * FROM employees WHERE name like '徐%' AND age = 22 AND position ='演员';

image.png

分析： like KK%依然符合最左前缀， 同时mysql在查询优化时，like KK%用了索引下推优化。

什么时索引下推： 对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则， SELECT * FROM employees WHERE name like '徐%' AND age = 22 AND position ='演员' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。 在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 '徐' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。 MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 '徐' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

本篇主要还是通过以上几个例子来再次加深下我们对MySQL索引数据结构的理解，下一篇我们再来看看常见的一些SQL优化

面试延申

为什么索引结构默认使用B+Tree，而不是Hash，二叉树，红黑树？

  • B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
  • Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
  • 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
  • 红黑树：树的高度随着数据量增加而增加，IO代价高。
  • B+tree： 从两个方面来回答
    • B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B-tree更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
    • 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
avatar
文章
阅读
粉丝