MySql 索引选择与使用

提起优化SQL，你可能会把它理解为优化索引。简单来说这也不算错，索引在SQL优化中占了很大的比重。索引用得好，可以将SQL查询的效率提升10倍甚至更多。但索引是万能的吗？既然索引可以提升效率，只要创建索引不就好了吗？实际上，在有些情况下，创建索引反而会降低效率。

一 什么是索引？

本段解决两个问题：

1. 索引的种类有哪些？
2. 什么情况下创建索引，什么时候不需要索引？
   

首先我们需要了解什么是索引（Index）。数据库中的索引，就好比一本书的目录，它可以帮我们快速进行特定值的定位与查找，从而加快数据查询的效率。

索引就是帮助数据库管理系统高效获取数据的数据结构。

1.1 索引种类

虽然使用索引的本质目的是帮我们快速定位想要查找的数据，但实际上，索引有很多种类。

从功能逻辑上说，索引主要有4种，分别是普通索引、唯一索引、主键索引和全文索引。

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

其实前三种索引（普通索引、唯一索引和主键索引）都是一类索引，只不过对数据的约束性逐渐提升。在一张数据表中只能有一个主键索引，这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。但可以有多个普通索引或者多个唯一索引。

按照物理实现方式，索引可以分为2种：聚集索引和非聚集索引。我们也把非聚集索引称为二级索引或者辅助索引。

1. 聚集索引：可以按照主键来排序存储数据，这样在查找行的时候非常有效。举个例子，如果是一本汉语字典，我们想要查找“数”这个字，直接在书中找汉语拼音的位置即可，也就是拼音“shu”。这样找到了索引的位置，在它后面就是我们想要找的数据行。
2. 非聚集索引：在数据库系统会有单独的存储空间存放非聚集索引，这些索引项是按照顺序存储的，但索引项指向的内容是随机存储的。也就是说系统会进行两次查找，第一次先找到索引，第二次找到索引对应的位置取出数据行。

聚集索引与非聚集索引的原理区别：非聚集索引不会把索引指向的内容像聚集索引一样直接放到索引的后面，而是维护单独的索引表（只维护索引，不维护索引指向的数据），为数据检索提供方便。我们还以汉语字典为例，如果想要查找“数”字，那么按照部首查找的方式，先找到“数”字的偏旁部首，然后这个目录会告诉我们“数”字存放到第多少页，我们再去指定的页码找这个字。

聚集索引指表中数据行按索引的排序方式进行存储，对查找行很有效。只有当表包含聚集索引时，表内的数据行才会按找索引列的值在磁盘上进行物理排序和存储。每一个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

聚集索引与非聚集索引的原理不同，在使用上也有一些区别：

1. 聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。
2. 一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。
3. 使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

二、怎么选择索引？

我之前讲过页这个结构，表和索引都会存储在页中，不同的DBMS默认的页的大小是不同的，同时我们也了解到DBMS会有缓冲池的机制，在缓冲池里需要有足够多的空间，存储经常被使用到的页，尽可能减少直接的磁盘I/O操作。这种策略对SQL查询的底层执行来说非常重要，可以从物理层面上最大程度提升SQL的查询效率。
但同时我们还需要关注索引的设计，如果只是针对SQL查询，我们是可以设计出理想的索引的，不过在实际工作中这种理想的索引往往会带来更多的资源消耗。

1. 什么是索引片？如何计算过滤因子？
2. 设计索引的时候，可以遵循哪些原则呢？
3. 为什么理想的索引很难在实际工作中应用起来？
   

2.1 索引片和过滤因子

2.1.1 索引片

索引片就是 SQL查询语句在执行中需要扫描的一个索引片段，我们会根据索引片中包含的匹配列的数量不同，将索引分成窄索引（比如包含索引列数为1或2）和宽索引（包含的索引列数大于2）。

如果索引片越宽，那么需要顺序扫描的索引页就越多；如果索引片越窄，就会减少索引访问的开销
宽索引需要顺序扫描的索引页很多，不过它也可以避免通过索引找到主键，再通过主键回表进行数据查找的情况。回表指的就是数据库根据索引找到了数据行之后，还需要通过主键再次到数据表中读取数据的情况。

2.1.2 过滤因子

在索引片的设计中，我们还需要考虑一个因素，那就是过滤因子，它描述了谓词的选择性。在WHERE条件语句中，每个条件都称为一个谓词，谓词的选择性也等于满足这个条件列的记录数除以总记录数的比例。

联合过滤因子有更高的过滤能力，这里还需要注意一个条件，那就是条件列的关联性应该尽量相互独立，否则如果列与列之间具有相关性，联合过滤因子的能力就会下降很多。比如城市名称和电话区号就有强相关性，这两个列组合到一起不会加强过滤效果。

你能看到过滤因子决定了索引片的大小（注意这里不是窄索引和宽索引），过滤因子的条件过滤能力越强，满足条件的记录数就越少，SQL查询需要扫描的索引片也就越小。同理，如果我们没有选择好索引片中的过滤因子，就会造成索引片中的记录数过多的情况。

2.2 针对SQL查询的理想索引设计：三星索引

刚才我介绍了宽索引和窄索引，有些时候宽索引可以提升SQL的查询效率，那么你可能会问，如果针对SQL查询来说，有没有一个标准能让SQL查询效率最大化呢？

实际上，存在着一个三星索引的标准，这就好比我们在学习数据表设计时提到的三范式一样。三星索引具体指的是：

1. 在WHERE条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列；
2. 将 GROUP BY 和 ORDER BY 中的列加入到索引中；
3. 将 SELECT 字段中剩余的列加入到索引片中。

你能看到这样操作下来，索引片基本上会变成一个宽索引，把能添加的相关列都加入其中。可同时带来另一个问题：对于一条SQL查询来说，这样做的效率是最高的吗？

首先，如果我们要通过索引查找符合条件的记录，就需要将WHERE子句中的等值谓词列加入到索引片中，这样索引的过滤能力越强，最终扫描的数据行就越少。

另外，如果我们要对数据记录分组或者排序，都需要重新扫描数据记录。为了避免进行file sort排序，可以把GROUP BY和ORDER BY中涉及到的列加入到索引中，因为创建了索引就会按照索引的顺序来存储数据，这样再对这些数据按照某个字段进行分组或者排序的时候，就会提升效率。

最后，我们取数据的时候，可能会存在回表情况。回表就是通过索引找到了数据行，但是还需要通过主键的方式在数据表中查找完成的记录。这是因为SELECT所需的字段并不都保存在索引中，因此我们可以将SELECT中的字段都保存在索引中避免回表的情况，从而提升查询效率。

2.3 为什么很难存在理想的索引设计

从三星索引的创建过程中，你能看到三星索引实际上分析了在SQL查询过程中所有可能影响效率的环节，通过在索引片中添加索引的方式来提升效率。通过上面的原则，我们可以很快创建一个SQL查询语句的三星索引（有时候可能只有两星，比如同时拥有范围谓词和ORDER BY的时候）。

但就同三范式一样，很多时候我们并没有遵循三范式的设计原则，而是采用了反范式设计。同样，有时候我们并不能需要完全遵循三星索引的原则，原因主要有以下两点：

1. 采用三星索引会让索引片变宽，这样每个页能够存储的索引数据就会变少，从而增加了页加载的数量。从另一个角度来看，如果数据量很大，比如有1000万行数据，过多索引所需要的磁盘空间可能会成为一个问题，对缓冲池所需空间的压力也会增加。
2. 增加了索引维护的成本。如果我们为所有的查询语句都设计理想的三星索引，就会让数据表中的索引个数过多，这样索引维护的成本也会增加。

举个例子，当我们添加一条记录的时候，就需要在每一个索引上都添加相应的行（存储对应的主键值），假设添加一行记录的时间成本是10ms（磁盘随机读取一个页的时间），那么如果我们创建了10个索引，添加一条记录的时间就可能变成0.1s，如果是添加10条记录呢？就会花费近1s的时间。从索引维护的成本来看消耗还是很高的。当然对于数据库来说，数据的更新不一定马上回写到磁盘上，但即使不及时将脏页进行回写，也会造成缓冲池中的空间占用过多，脏页过多的情况。

2.4 本节总结

2.4.1 概念总结

你能看到针对一条SQL查询来说，三星索引是个理想的方式，但实际运行起来我们要考虑更多维护的成本，在索引效率和索引维护之间进行权衡。

三星索引会让索引变宽，好处就是不需要进行回表查询，减少了磁盘I/O的次数，弊端就是会造成频繁的页分裂和页合并，对于数据的插入和更新来说，效率会降低不少。

2.4.2 那我们该如何设计索引呢？

首先一张表的索引个数不宜过多，否则一条记录的增加和修改，会因为过多的索引造成额外的负担。针对这个情况，当你需要新建索引的时候，首先考虑在原有的索引片上增加索引，也就是采用复合索引的方式，而不是新建一个新的索引。另外我们可以定期检查索引的使用情况，对于很少使用到的索引可以及时删除，从而减少索引数量。

同时，在索引片中，我们也需要控制索引列的数量，通常情况下我们将WHERE里的条件列添加到索引中，而SELECT中的非条件列则不需要添加。除非SELECT中的非条件列数少，并且该字段会经常使用到。

另外单列索引和复合索引的长度也需要控制，在MySQL InnoDB中，系统默认单个索引长度最大为767 bytes，如果单列索引长度超过了这个限制，就会取前缀索引，也就是取前 255 字符。这实际上也是告诉我们，字符列会占用较大的空间，在数据表设计的时候，尽量采用数值类型替代字符类型，尽量避免用字符类型做主键，同时针对字符字段最好只建前缀索引。

三、怎么使用索引？

之前讲了索引的概念和底层原理，现在讲一讲索引的使用原则。既然我们的目标是提升SQL的查询效率，那么该如何通过索引让效率最大化呢？

1. 什么情况下使用索引？当我们进行数据表查询的时候，都有哪些特征需要我们创建索引？
2. 索引不是万能的，索引设计的不合理可能会阻碍数据库和业务处理的性能。那么什么情况下不需要创建索引？
3. 创建了索引不一定代表一定用得上，甚至在有些情况下索引会失效。哪些情况下，索引会失效呢？又该如何避免这一情况？
   

3.1 创建索引有哪些规律？

创建索引有一定的规律。当这些规律出现的时候，我们就可以通过创建索引提升查询效率，下面我们来看看什么情况下可以创建索引：

1. 字段的数值有唯一性的限制，比如用户名

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。

2. 频繁作为WHERE查询条件的字段，尤其在数据表大的情况下

在数据量大的情况下，某个字段在SQL查询的WHERE条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。

3. 需要经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。

4. UPDATE、DELETE的WHERE条件列，一般也需要创建索引

你能看到，对数据按照某个条件进行查询后再进行UPDATE或DELETE的操作，如果对WHERE字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据WHERE条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5. DISTINCT字段需要创建索引

有时候我们需要对某个字段进行去重，使用DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6. 做多表JOIN连接操作时，创建索引需要注意以下的原则

首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如user_id在product_comment表和user表中都为int(11)类型，而不能一个为int另一个为varchar类型。
如果我们不使用WHERE条件查询，而是直接采用JOIN…ON…进行连接的话，即使使用了各种优化手段，总的运行时间也会很长（>100s）。

3.2 什么时候不需要创建索引

索引不是万能的，有一些情况是不需要创建索引的。

第一种情况，WHERE条件（包括GROUP BY、ORDER BY）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。举个例子：

SELECT comment_id, product_id, comment_time
FROM product_comment
WHERE user_id = 41251

因为我们是按照user_id来进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在SELECT字段中。

第二种情况是，如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。

第三种情况是，字段中如果有大量重复数据，也不用创建索引，比如性别字段。不过我们也需要根据实际情况来做判断，主要判断指标是过滤因子。

最后一种情况是，频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

3.3 什么情况下索引失效

创建了索引，还要避免索引失效，有哪些情况会造成索引失效呢？
下面是一些常见的索引失效的例子：

1. 如果索引进行了表达式计算，则会失效。
2. 如果对索引使用函数，也会造成失效。
3. 在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。
   因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。
4. 当我们使用LIKE进行模糊查询的时候，前面不能是% 这个很好理解，如果一本字典按照字母顺序进行排序，我们会从首位开始进行匹配，而不会对中间位置进行匹配，否则索引就失效了。
5. 索引列尽量设置为NOT NULL约束。
   MySQL官方文档建议我们尽量将数据表的字段设置为NOT NULL约束，这样做的好处是可以更好地使用索引，节省空间，甚至加速SQL的运行。
   判断索引列是否为NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为NOT NULL约束比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串(’’)。
6. 在使用联合索引的时候要注意最左原则。
   最左原则也就是需要从左到右的使用索引中的字段，一条SQL语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效。我在讲联合索引的时候举过索引失效的例子。

3.4 本节总结

梳理了索引的6个使用原则，我们知道使用好索引可以提升SQL查询的效率，但同时也要注意索引不是万能的。为了避免全表扫描，我们还需要注意有哪些情况可能会导致索引失效，这时就需要进行查询重写，让索引发挥作用。

实际工作中，查询的需求多种多样，创建的索引也会越来越多。这时还需要注意，我们要尽可能扩展索引，而不是新建索引，因为索引数量过多需要维护的成本也会变大，导致写效率变低。同时，我们还需要定期查询使用率低的索引，对于从未使用过的索引可以进行删除，这样才能让索引在SQL查询中发挥最大价值。