3.索引优化工具——Trace工具

一、先分析几种容易理解错误的案例

这里还是以表employees为例，并插入1000000条数据

CREATE TABLE 'employees'( 
    'id' int(11) NOT NULL AUTO INCREMENT, 
    'name' varchar(24) NOT NULL DEFAULT '' COMMENT'姓名', 
    'age' int(11) NOT NULL DEFAULT 'O' COMMENT '年龄', 
    'position' varchar(28) 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=4 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('xzh',i),i,'dev'); 
    set i=i+1; 
    end while; 
end;; 
delimiter ; 
call insert_emp()

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

    若第一个索引字段使用范围索引，mysql优化器内部会认为数据量较大,回表率不高，不如直接使用全表扫秒

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

2.强制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 A ND position ='manager';

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

3.覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager'

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

    这里演示有1000003条数据时会走索引，有效减少回表次数，如果只有3条数据时，不会走索引，直接全盘扫秒更快。

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager'

EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

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

    这里常规理解name进行查询时，以'kk'开头查询所有符合条件的字段，本身我们的联合索引中的name索引在b+tree上就是排好序的，这里我们可以理解走了索引的排序，但是所有以'kk'开头的数据汇总到一起，age和position字段可能不是排好序的，应该不会走索引？

   在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

    MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

为什么范围查找Mysql没有用索引下推优化？

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

二、如何选择索引

EXPLAIN select * from employees where name > 'a';

常规理解，这里name会走索引，去联合索引中检索数据，同时这里是范围查找，可能会找到很多数据，然后还要去聚簇索引中去回表检索，成本比直接全表扫秒还高，可以使用覆盖索引，这样只需要遍历name字段的联合索引树即可。

EXPLAIN select name,age,position from employees where name > 'a' ;

对于这种 name >'a'这种首个索引字段范围查询，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭

trace工具用法：