mysql索引优化设计原则

示例表

-- 给name age position加联合索引
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('xiaohong',i),i,'dev');
        SET i=i+1;
    END WHILE;
END$$
CALL insert_emp();

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

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

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

强制走索引

force index(idx_name_age_position)

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

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

-- 关闭查询缓存
set global query_cache_size=0;  
set global query_cache_type=0;
-- 执行时间0.115s(默认)
SELECT * FROM employees WHERE name > 'LiLei';
-- 执行时间0.163s(强制走索引)
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

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

数据量小不一定走索引

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

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B) 
#等价于：循环B查询A减小循环次数
　　for(select id from B){
      select * from A where A.id = B.id
    }

exists：当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)
#等价于:循环A查询B减小循环次数,把exists括号语句放到代码块中,循环A
    for(select * from A){
      select * from B where B.id = A.id
    }

A表与B表的ID字段(或者关联字段)应建立索引
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

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

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

索引下推

上面我看可以看到,前面like前模糊查询,后面的age 和name 都用到了索引,之前说过,如果因为是范围查询,一般来说,接下来的索引是不生效的,这里是因为用到了索引下推
在MySQL5.6之前的版本:
如果是这类型的查询,mysql会将前模糊在联合索引树中查询出来的所有结果,得到的唯一主键id回表查询,在过滤掉age和position的结果集;
MySQL 5.6引入了索引下推优化
某些情况下,比如范围查询前模糊查询等,mysql会根据实际情况,比如表数据量大小等,是否决定使用索引下推,
就是先在聚合索引树中过滤范围查询结果,同时还会在索引里过滤age和position这两个字段,拿着过滤完剩下的索引对应的主键id再回表查整行数据。
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

常见sql深入优化

Order by与Group by优化

mysql的排序,分组查询也需要符合索引的最左前缀法则,分组查询之前,得先做排序

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position;

查询语言用到了索引,Using filesort表示用到了文件排序,排序这一块并没有使用到索引
因为:用了name position 中间断层了age,所以position结果集是无序的,无法使用索引进行排序

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age,position;

相比上一个语句加了age,但是用的是索引树排序,所以没有出现 Using filesort
因为:前面用了name age定位到了position 这个时候的position是有序的,mysql底层b+tree结构,所以可以使用索引进行排序

EXPLAIN SELECT name,age,position FROM employees WHERE name = 'LiLei' ORDER BY age asc,position  desc;

这里用到了文件排序,覆盖索引,查询索引
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

EXPLAIN SELECT name,age,position FROM employees WHERE name in ('LiLei','xiaohong') ORDER BY age,position;

同理,in也算范围查询

优化总结：

1、MySQL支持两种方式的排序Using filesort和Using where，Using where是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解

filesort文件排序方式

单路排序

原理,通过查询获取的结果,根据索引id一次性取出结果集的所有字段,在然后在sort buffer(缓存)中进行排序；
因为配置的缓存够用,能够支撑所有需要的字段,所有缓存中排序就好

双路排序（又叫回表排序模式）

原理,通过查询获取的结果,根据索引id一次性取出排序需要用到的字段,和对应的主键id,先用这些字段排序完成之后,更加排序结果的主键回表去获取需要的结果;
因为配置的缓存不够用,不能够支撑所有需要的字段,所有只能退一步,取需要排序的字段和主键,排序之后在回表查
可以减少获取的字段,减低字段占用的缓存大小,进行单路排序,或者配置扩大缓存

结果

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模·式。
注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

索引设计原则

代码先行，索引后上

不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

联合索引尽量覆盖条件

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

不要在小基数字段上建立索引

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

长字符串我们可以采用前缀索引

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于== KEY index(name(20),age,position)==。
此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

where与order by冲突时优先where

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。

基于慢sql查询做优化

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
关于慢sql查询不清楚的可以参考这篇文章：
这里这里