【MySQL】 索引的创建原则、索引失效的场景

索引创建的原则有哪些？

在项目中主要用到主键索引、唯一索引、根据业务创建的索引(复合索引)

1. *针对数据量较大，而且查询比较频繁的表建立索引 （单表超过10w条数据（增加用户体验））

2. *针对常作为查询条件（where）、排序（order by）、分组（group by）操作做的字段建立索引

3. 尽量选择区分度高的字段作为索引，尽量建立唯一索引，区分多越高，索引的效率越高

4. 如果是字符串类型的字段，字段的长度过长，可以针对字段的特点，建立前缀索引

5. *尽量使用复合索引，减少单列索引，查询时复合索引可以覆盖索引，节省存储空间，避免回表查询，提高查询效率

6. *要控制索引数量，索引越多维护成本也越高，也会影响增删改的效率

7. 索引不能存储NULL时，应在建立时设置为NOT NULL约束它，优化器判断索引是否包含空值，可以更好地确定用于查询的索引

索引失效的场景

• 在where子语句中使用一些逻辑操作符 not in、≠（无法进行索引查找）

• 对索引字段进行计算或者函数操作

• 使用LIKE操作符进行模糊匹配时，%通配符在前会导致索引失效（MySQL无法确定查找的起始点）

• 数据类型错误也会导致索引失效

• 索引列是NULL

• 如果MySQL估计全表扫描比索引所读快，会使用全表扫描（数据量小，大部分行都满足where子句）

• 在复合索引中，没有满足最左前缀法则，也会导致索引失效

索引失效：模型数空运最快（口诀）

模 模糊查询 like “%”

型 数据类型不一致

数 对索引字段使用内部函数

空 索引列有空值

运 索引列进行四则运算 +、-、*、/

最 最左原则复合索引不按索引最左开始查找

快 全表查询速度比索引快