（十）索引

1. 索引的概述

索引可以帮助我们从海量的数据中快速定位想要查找的数据。不过索引也存在一些不足，比如占用存储空间、降低数据库写操作的性能等，如果有多个索引还会增加索引选择的时间。

索引主要有4种，分别是：

• 普通索引：是基础的索引，没有任何约束，主要用于提高查询效率。
• 唯一索引：在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。
• 主键索引：在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。
• 全文索引：全文索引用的不多，MySQL自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如ES(ElasticSearch)

按照物理实现方式，索引可以分为2种：

• 聚集索引：书签
• 非聚集索引：目录。我们也把非聚集索引称为二级索引或者辅助索引。

2. 如何使用索引

• 多个单列索引在多条件查询时只会生效一个索引（MySQL会选择其中一个限制最严格的作为索引）
• 字段的数值有唯一性的字段
• 在数据表大的情况下，频繁作为WHERE查询条件的字段
• 需要经常GROUP BY和ORDER BY的列，就需要对分组或者排序的字段进行索引。
• UPDATE、DELETE的WHERE条件列，一般也需要创建索引
• DISTINCT字段需要创建索引，也会提升查询效率
• 做多表JOIN连接操作时，创建索引需要注意以下的原则
  • 连接表的数量尽量不要超过3张，因为每增加一张表，数量级增长会非常快，严重影响查询的效率。
  • 对WHERE条件字段创建索引
  • 对用于连接的字段创建索引

3. 索引失效情况

• 如果索引进行了表达式计算，则会失效

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001

• 对索引使用函数，也会造成失效

SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

• 在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text = '462eed7ac6e791292a79'

• 当我们使用LIKE进行模糊查询的时候，以%开头的字段会失效

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc'

• 联合索引不是最左原则

• 索引列尽量设置为NOT NULL约束。