MySQL-索引优化

回顾聚簇索引，官网的定义在这里。

聚簇索引无法显示创建，存储引擎会根据一定的规则自动创建，具体规则优先级是： 主键--》非空唯一索引--》隐藏列rowid

**InnoDB表的主键列应该选择有序的顺序ID。

 

慢SQL查找（慢查询日志）

查看是否开启，默认不开启。如果不需要进行调优，一般不建议开启。参数为：slow_query_log

show variables like '%slow_query_log%';

临时开启：

set global slow_query_log = 1;

查看慢查询的时长：

show variables like 'long_query_time';

 临时修改时长：set global long_query_time = 3;  -- 需要重新打开连接

查看慢sql的个数：

show global status like '%slow_queries%';

再使用mysql内置工具：mysqldumpslow 进行日志汇总。更多慢sql分析查阅。

 

一些基本的手段如：索引覆盖，最左匹配，MRR，ICP ，避免全表扫描等在上一篇文章 MySQL-带你上官网看索引 已经分析过，其核心

就是两个词：避免回表，顺序磁盘读。

本质是对mysql磁盘存储原理解析和存储引擎数据结构特性的最大利用。

MySQL官方的优化非常多，在官网中区分了非常多的分类，下面只描述一些重点项目。因为从sql语句，索引，存储引擎，缓冲池，Server层，IO，缓存等等

细节优化的点是非常多的，不是专职做数据库的同学可能没有精力全面的去探索。这里说一些常用的重点，需要再去官网查询吧。

 

 优化SQL语句：

1，索引列上的函数操作

验证SQL：

explain select * from staff where staff_id=power(1,2); -- yes（确定性函数）
explain select * from staff where staff_id=FLOOR(1 + RAND() * 49); -- no(非确定性函数）
explain select * from staff where power(staff_id,6)=1; -- no(索引列上加函数)

**要点：索引列等号右边使用函数，分为确定性函数和非确定性函数两种情况：确定性函数走索引，非确定性函数不走索引。

             同时，如果对索引列本身做函数计算，不管函数是确定性函数还是非确定性函数，都不走索引。即索引完全失效。

 

2，not in，!= 使用二级索引在某些场景下查找成本超过全表扫描，触发全表扫描。

这个可以理解，使用二级索引必然会涉及到是否会回表的问题，当回表成本大于全表扫描成本，即触发全表扫描（查询优化器的选择）。

 

3，like以%开头，这个不解释了，直接违背了排序规律和最左匹配原则。不会走索引。

 

4，columnname is null不会走索引。

 

5，应该使用组合索引代替为单个列建N个索引。

 

由于网上的一些文章的很多优化点没有阐述数据量背景，个人认为直接下判断是不正确的。比如很多文章说范围条件右边的列，不等号等不走索引，

但真相是在数据量少时都是走索引的。

还是基于数据量背景和实验数据再下判断和定论吧。

 

附美团的一个优化案例。