MySQL-索引优化实战

 

针对联合索引来说，如果第一列就是用范围查询，例如大于小于这些，就会认为查询的行很多，如果不是覆盖索引，那么就不再使用这个二级索引，认为使用二级索引还要频繁的去回表查询等等，消耗更大，所以就会去全表扫描。

但是可以使用force index(索引名称)去强制使用指定的索引，但是一般不建议这么做；要相信MySQL自己的优化结果。

in和or使用时，如果后面括号里面的值不多，并且表里面的数据很多，就会走索引，否则可能不会走索引。

针对like 'xx%'；这样的查询一般都会去走索引的；因为5.6版本之后引入了一个索引下推的技术；

索引下推

如果ABC三个列建立了联合索引，查询条件是 A like 'xx%' and B = '' and C = '';在5.6版本之前，会去联合索引中找到符合A列的所有主键值，去主键索引中进行回表，然后将这些数据加载到内存中，然后进行B和C条件的匹配。这样回表次数会比较多，并且返回的数据量也很大，带宽压力也大。

5.6版本之后，引擎进行了优化，在匹配符合A列的条件之后，还会在二级索引中去进一步匹配B和C这两列的查询条件，这样一来会减少最终查询的值的数量，然后就会减少回表的次数，然后也会减少返回给调用方的数据量，减少网络带宽的压力。

使用索引下推的场景一般都是对于非主键的联合索引，这样可以减少回表次数，进而减少数据量

 

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

因为对MySQL来说，认为一般情况下范围查询的数据量是很大的，而like模糊查询的数据量是较少的；这样就可以减少查询到的主键的数据量，减少回表次数。当然这个like使用索引下推并不是绝对的，大于小于这种范围查询不使用索引下推也不是绝对的。

 

trace

trace工具可以展示出来经过引擎优化之后的查询语句，但是这个功能默认是关闭的，开启会消耗性能。

set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace

select * from 表名 where 条件 > 'a' order by 条件;

SELECT * FROM information_schema.OPTIMIZER_TRACE;

注意，两个select要一起执行，这样第二个才能在控制台输出优化后的SQL语句等。

第一阶段是sql格式化；

第二阶段是SQL优化，针对条件是否要挪位等等；

第三阶段就是评估使用各个索引的查询成本，这个只是一个预估值。

第四阶段就是确定最终使用哪个索引来进行查询。

注意每次查询只能使用一个索引。（回表使用的是主键索引）

 

set session optimizer_trace="enabled=off"; ‐‐关闭trace

 

常见sql深入优化

Order by与Group by优化

针对排序和分组来说，通过explain分析之后，我们主要是看extra这一列。主要有两个值，有using index和using filesort。using index是使用到了索引（这个是最佳情况），using filesort是文件排序。

MySQL不会对order by和group by后面的查询条件进行优化，例如挪位。（联合索引是AB，开发者写的条件是BA，此时就是using filesort）

小总结：

1. MySQL使用order by支持index和filesort两种排序，index是能使用到已有索引的，效率高；filesort无法使用到索引，效率低。
2. 满足using index的情况：1.order by后面的查询严格符合联合索引的最左前缀原则；2.where条件和order by的条件完全符合最左前缀原则。
3. 排序时尽量使用已有的索引列，尽量使用到索引；
4. 查询能使用覆盖索引就使用覆盖索引。

group by和order by类似，但是group by之前会先进行order by一次；如果不需要排序可以加上order by null；

注意，where高于having，能写在where中的限定条件就不要去having限定了。

 

using filesort文件排序原理

分为单路排序和双路排序。

单路排序：就是一次性将符合条件的数据全部加载出来，在系统的sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >。

该排序的好吃就是一次性就将数据加载出来，无需进行回表操作，但是对内存大小要求较高。

双路排序：也叫做回表排序，就是根据条件先取出来排序字段和能直接定位到行的主键值，然后在sort buffer中排序，之后要进行回表将别的要查询的值查询出来填充进去。用trace工具可以看到sort_mode信息里显示< sort_key, rowid >。

该排序的好处就是对内存压力小，但是需要进行二次回表，IO压力变大。

 

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；

如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。

 

这个值是可以修改的，通过命令set max_length_for_sort_data = 10，但是并不建议修改，非专业DBA不要去修改这些默认配置。

排序中有这样一个参数："number_of_tmp_files": 3, ‐‐表示使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序。尽量使用内存排序，这样更高效。

如果排序内存sort_buffer比较小，可以适当改小max_length_for_sort_data 值，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。

注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

 

索引设计原则

1. 索引不是越多越好，索引多了，第一是占用空间，第二维护麻烦。

2. 代码先行，待开发功能差不多完成时，将相关表的操作全部整理出来，进行分析，而不是建表时想当然的创建索引。

3. 尽量使用联合索引，因为每次查询只能命中一个索引，所以要尽可能的让查询条件都命中索引，可以适当设计三四个左右的联合索引。

4. 尽量在区分度大的列上建立索引，最好不要在性别字段加索引，因为性别就女和男，这样区分度太小，类似全表扫描。

5. 针对长字段来说，可以截取前20位当作索引，这样可以减少索引所占的空间，也能起到良好的查询效果。当然如果业务要求必须这个字段整体作为索引，那么也不能为了节省空间而去想别的方式，技术是为了业务而服务的。还有一点就是如果使用了前缀，那么对这个字段进行分组或者排序的时候是无法使用索引的，因为针对这个字段的整体来说是无序的。

6. where和order by冲突时，要优先以where为基准，先过滤，经过过滤会少很多的数据，此时再去排序就会快很多的。

7. 基于慢查询进行优化。MySQL的慢查询默认是关闭的，因为开启会影响部分性能；慢查询默认是以十秒为基准的，低于十秒的不记录。

 

实战开发原则：

首先是分析针对表的所有查询语句，争取让百分之八十以上的查询都走索引，并且尽量使用到覆盖索引。

当一个联合索引不能覆盖查询场景的时候，可以创建别的联合索引来满足查询业务，一张表可以设计三到四个联合索引。

同时针对场景进行分析，特定的场景抽取特定的字段去创建索引，并且尽量让范围模糊查询的字段放到最后面，这样保证索引的高效使用。

针对一些时间范围的查询，可以新增一个冗余的标识字段，额外启动一个定时器，进行字段值的修改。

SQL语句的查询条件尽量符合联合索引的最左前缀原则。

 

先过滤，再去排序。

 

MySQL索引相关限制：

 

1. MySQL对索引字段长度有限制

   innodb引擎的每个索引列长度限制为767字节（bytes），所有组成索引列的长度和不能大于3072字节

   myisam引擎的每个索引列长度限制为1000字节，所有组成索引列的长度和不能大于1000字节

2. varchar的最大长度是指字符长度，若数据库字符集为utf-8，则一个字符占3个bytes。因此在utf-8字符集下，innodb引擎创建的单列索引长度不能超过255个字符。

mysql版本不同而导致的索引长度限制不同

在MySQL5.5版本，引入了innodb_large_prefix，用来禁用大型前缀索引，以便与不支持大索引键前缀的早期版本的InnoDB兼容

开启innodb_large_prefix可以使单索引的长度限制达到3072字节（但是联合索引总长度限制还是3072字节），禁用时单索引的长度限制为767字节

在MySQL5.5版本与MySQL5.6版本，innodb_large_prefix是默认关闭的，在MySQL5.7及以上版本则默认开启

在MySQL8.0版本中，innodb_large_prefix已被移除

这就是我在自己机器（MySQL8.0）上可以创建1024字符（utf8字符集下表示3072字节）长的索引，而在服务器（MySQL5.5）上不行的原因