Mysql索引优化实战(一)

1.索引下推优化
2.mysql优化索引选择探究
3.索引优化order by与group by
4.Using filesort 文件排序详解
5.索引设计原则与实战

1.创建示例表

代码如下：

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();

联合索引第一个字段用范围不会走索引
EXPLAIN SELECT * FROM employees WHERE name > ‘LiLei’ AND age = 22 AND position =‘manager’;

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

强制走索引

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

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

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

EXPLAIN SELECT * FROM employees force

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

索引下推
在mysql5.6之前的版本没有引入索引下推的时候。
例如sql : SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’;
mysql条件查询的步骤是
从索引中查询出name中带有lilei的结果集，然后拿到他们的id集合再去聚簇索引中根据条件筛选(age=22，position=manager)。
然而引入索引下推之后区别在于查询索引拿到lilei的集合的时候，还会继续去比对age和position的条件，符合才会将id拿出来去聚簇索引中拿记录。

问题：为什么大于号mysql没有使用索引下推？
答:推测，大部分情况下 like过滤出来的结果集会少于>号所过滤出来的结果集
，由于可能大于号过滤出来的结果集过大那么如果每一个集都进行索引下推的话可能效率还不如直接回表来得高。

常见的sql优化
如sql:EXPLAIN select * from employees where name =‘LiLei’ and position=‘dev’ order by age;

key_len=74可以看出上面的sql是走了索引，虽然理论上只走了索引的name索引，但是order by走索引在长度上是不体现的。

如上面name是等值的情况下 跳过了age直接order by position是不会使用索引(文件排序 using filesort)但是还是走了索引。

问:EXPLAIN select * from employees where name in(‘LiLei’,‘zhuge’) order by age, position 为什么不会用到覆盖索引?

结论:找出来的第一个字段的结果集他们的col2和col3是无序的索引是无法使用到覆盖索引

SQL1:EXPLAIN select * from employees where name >‘a’ order by name ;

SQL2:EXPLAIN select name,age,position from employees where name >‘a’ order by name ;

问:为什么sql1不会走覆盖索引，会进行全表扫描 而sql2会使用覆盖索引？

原因:sql1走了索引，但是mysql会认为数据量比较大，查出来的数据都得去进行回表拿到聚簇索引里面存储的记录字段，不如直接全表扫描来的快。
而sql2查询所要求的字段刚好被索引树所覆盖。直接扫索引一次就能完成查询。

filesort文件排序方式
（都是基于扫描聚簇索引）
select * from T order by col
1.单路排序:
单路排序会把order by前面的sql执行完把所有的数据拿出来进行排序(放置在sort buffer排序内存中排序)。

2.双路排序(又叫回表排序模式)
双路排序会从聚簇索引中把 id以及排序字段 load到内存中去进行排序，
排好序之后再根据id进行回表查询出来数据。

总结:单路排序一次性将查出来的聚簇索引load到内存中去排序，造成了排序的时候消耗内存更大一些，但是过程会更快一些，双路排序排序的时候占用的内存更小一些但是最后需要回表一次，速度会慢一些。各有各的优缺点。一般不需要去设置，mysql内部有自己的设置。

索引设计原则
1、代码先行，索引后上
2、联合索引尽量覆盖条件（索引占存储空间，大多数sql都是多个条件去查询，mysql大多数情况下只会选择一个索引）
3、不要在小基数字段上建立索引(如性别、是否有效字段这样的)
4.长字符串我们可以采用前缀索引（如字段varchar(255)可以选取前面的20个字段index(name(20),age,position)。）
5、where与order by冲突时优先where
6、基于慢sql查询做优化

---

优化总结：

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同样也满足最左前缀法则），其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了