MYSQL基础06-Mysql索引使用原则及优化

官网地址：https://dev.mysql.com/doc/refman/5.7/en/optimization.html

索引使用的原则

列的离散度高的列建索引，离散度低的列尽量避免建索引

列的离散度

• 列的离散度的计算公式

select count(distinct(column_name)) / count(column_name)  as rate from tb

• 查询出来的值越趋近于1，离散度越高，反之越小
• 离散度太低会导致索引失效

联合索引最左匹配原则

• 构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建，联合索引在使用中也是从最左边开始匹配

覆盖索引

• 索引中已经包含了所有需要读取的列数据的查询方式称为覆盖索引（或索引覆盖）。

索引下推

• 索引下推是把数据库计算WHERE行的计算下推到存储引擎，对二级索引查询进行优化，减少回表查询完整数据行次数；
• 本质上是减少了完整行的数据访问，从而降低了数据库磁盘I/O流操作；

索引创建的规则

• 在WHERE条件项，Order排序字段，Join关联字段上增加索引
• 索引创建不宜过多，会造成资源浪费
• 离散度太低的列不要建索引，会导致扫描行数过多，数据库优化器可能会进行全表扫描。
• 频繁更新的列，不要建索引，容易造成数据页分裂
• 组合索引建议把离散度高的放在前面
• 尽可能使用复合索引，而不是单列索引
• 对于过长的字段可以使用前缀索引
• 随机无序的值，不建议作为索引，例如身份证、UUID

索引失效的场景

• 索引列使用了函数（replace/CONCAT/SUBSTR）,索引列进行自增自减等计算操作，索引列含有表达式 where age = 10 + 1等
• 索引列存在隐式的类型转换
  • 例如：name='张三' 和 name=张三，这种类似于增加了CAST(name AS varchar) 的操作，如上条所说，索引列使用函数会使索引失效
• like条件项前使用%
  • 查询行数资源耗费较多，开启全文检索
• 使用了反向查询
  • <>，!=，NOT LIKE 这种，会导致全表扫描
• 未遵守索引列的最左匹配原则
• 使用OR连接
  • OR前后条件项只要存在不是索引列的情况，就会出现索引失效的情况

参考博客地址：

https://zhuanlan.zhihu.com/p/481750465
https://www.cnblogs.com/ljl150/p/12934071.html