数据库基础06-Mysql索引使用原则及优化
索引使用的原则
列的离散度高的列建索引，离散度低的列尽量避免建索引
列的离散度
- 列的离散度的计算公式
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