数据库调优-索引篇
1、前言
面试官：小明，你简历上写了你会数据库调优，你都是怎么调优的？ 小明：加索引。 面试官：除此之外还有么？ 小明：额。。。没了。 面试官：。。。那我们聊点别的吧。 这是我臆想的一个面试场景，但是内容很真实，相信很多人被问到数据库调优的第一反应就是加索引，那么除了加索引还有别的吗？或者索引相关的点你全部都知道么？聚簇索引，非聚簇索引，普通索引，唯一索引，覆盖索引？还有索引为什么会失效？这些大家知道嘛？
为什么SQL会慢
我们在项目中会经常提到慢SQL，那都有哪些原因会导致SQL查询变慢呢。一般原因有以下三种： 1、硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。 2、没有索引或者索引失效。 3、数据过多（MySQL官方文档说数据在超过500~800w条记录左右性能开始逐渐下降）
数据库调优调的是什么
在聊这个之前我们先了解下数据库的组成结构。如图：
我们所谓的调优也就是在执行器执行之前的分析器，优化器阶段完成的，数据库调优其实一般情况都是指我们的SQL调优，那我们开发工作中怎么去调优的呢？在开发工作中索引优化应该是我们最常用且最有效的手段了。
2、索引
说到SQL调优，大家的第一反应就是加索引，那什么是索引呢？ MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。如果把数据库表比喻成一本汉语字典，索引相当于这本书的目录，拼音、笔画、偏旁部首等排序的目录就相当于不同字段的索引，我们可以通过索引快速查找到需要的字。
索引类型
我们经常会被问到索引有哪几种类型，多数人的第一反应是：B+tree索引、Hash索引、主键索引、唯一索引、联合索引等。这么描述也没有错，但我觉得不够准确；我个人总结MYSQL索引类型的分类其实是分几种维度的，具体如下：
按数据结构分类：B+tree索引、Hash索引、全文索引、空间索引。
B+Tree索引：
B+tree 索引是MySQL中被存储引擎采用最多的索引类型。它适用于全键值、键值范围和最左前缀查找。使用联合索引时，如果不是按照索引列的顺序进行查找，则无法使用索引。除了适用于查找，还可以用于排序和分组。
Hash索引
Hash索引是基于Hash算法实现的,它将一系列的最终的键值通过哈希函数转化为存储实际数据桶的地址数值。Hash索引在精确查询时的效率要远高于B+Tree索引，虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了很多限制和弊端，我们一般不使用Hash索引。补充说明下：MySQL的InnoDB或MyISAM存储引擎是支持Hash索引，但是需要通过伪Hash索引来实现，叫自适应Hash索引。
全文索引
Full-text索引一般使用倒排索引实现，倒排索引同B+tree索引一样，也是一种索引结构。MySQL中InnoDB存储引擎在之前版本中是不支持全文检索的，要使用全文检索的话只能使用MyISAM存储引擎。在MySQL5.6.4版本中InnoDB存储引擎才开始支持Full-text索引。
空间索引
空间索引是对空间数据类型的字段建立的索引，创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。
按存储方式分类：聚簇索引、非聚簇索引（也叫二级索引、辅助索引）。
聚簇索引
聚簇索引就是按照每张表的主键构造一颗 B+tree，一般来说，聚簇索引就是我们常说的主键索引。InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非空的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增id列，并在此列上建立聚簇索引。
非聚簇索引
非聚簇索引的结构和聚集索引基本相同。其区别在于：每个索引上包含的字段内容不同，聚簇索引包含所有真实的物理数据，非聚簇索引只包含索引字段和主键字段。此外，聚簇索引一个表只能有一个，而非聚集索引一个表可以存在多个。
按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
主键索引
建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。
唯一索引
建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。
普通索引
建立在普通字段上的索引被称为普通索引。按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。
单列索引
建立在单个列上的索引被称为单列索引。
联合索引
建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。在MySQL中使用联合索引时要遵循最左前缀匹配原则。
此处单独说明下什么是覆盖索引：
一个索引包含（或者说覆盖）所有需要查询的字段值,那么就称为覆盖索引。如果通过索引就能查询到数据，就能避免回表查询，这样能极大提高性能。日常工作中我们经常说SQL语句尽量不要用select * from table，其目的就是为了使SQL查询有机会走覆盖索引。
3、索引优化
介绍完索引的基础信息后，接下来开始进行SQL优化的实操阶段。我们通常需要用Explain关键字查看SQL执行计划，那Explain是什么？怎么用？它有哪些参数呢？参数的指标是什么？我们具体来看看：
Explain
EXPLAIN是MySQl必不可少的一个分析工具，主要用来测试sql语句的性能及对sql语句的优化，或者说模拟优化器执行SQL语句。只要在SQL语句前加上该关键字就能查看该SQL的执行计划，其具体参数如下：
type：查询所使用的类型，从差到优依次是 all < index < range < ref < eq_ref < const；一般保证达到range，ref最好。
ALL：扫描全表数据行。 index：索引行全扫描。 range：使用索引进行范围扫描，between、in、>、< 等。 ref：使用非唯一索引或唯一索引前缀进行扫描，返回索引过滤的数据，可为多条，常出现在关联查询中。 eq_ref：与 ref 类似，区别在于是使用唯一索引进行关联扫描。 const / system：表示通过索引一次就查询到了相关记录，一般为主键或唯一索引查询，system 为 const 的特殊情况，表中仅有一条数据。
possible_keys：SQL语句可能用到的索引
key：SQL语句实际用到的索引
rows：查询数据所扫描的行数，rows越少越好
extra：SQL语句执行的一些额外的信息。
Using filesort：文件排序，表示MySQL需要通过语句查找所有匹配的行，并保存排序关键字与行指针，再通过对关键字进行排序，并按排序后的顺序检索出对应的行。 Using temporary：使用临时表保存中间结果，常见的情况有 使用 distinct 关键字、join 语句中使用 order by、使用 group by 无索引列、order by 与 group by 字段不同、union 子查询等。 Using where：未使用上索引，使用 where 条件扫描过滤。 Using index：表示使用了索引覆盖，仅从索引中就可以查找到对应的数据，不需要访问表。如果与 Using where 一起出现则表示索引用于查询过滤，还需回表查询出所需数据。 Using index condition：5.6版本之后新增的功能，称为索引下推；通过使用索引对存储引擎索引出的数据进行再过滤，减少回表查询的次数。
索引失效及优化分析
在实际项目中我们经常遇到明明字段创建了索引，但是查询还是很慢，这可能是因为索引失效导致的。接下来我们具体分析下项目中我们常遇到有索引失效的场景及对应优化方案。
数据准备：创建一个测试表同步tb_user。分别对字段 realname , create_time，update_time建立普通索引、对id_num，username，age建立组合索引，建表语句如下：
错误示例：
正确示例：
分析与结论：从图中可以看出，错误示例的执行计划type=all，说明查询的是全表，key=null，说明未使用索引。两个示例比较，优化前耗时需要50ms，优化后耗时只要1ms，前后查询效率相差50倍。
错误示例：
正确示例：
第五种索引失效场景：索引列参与了运算，会导致全表扫描，索引失效。
错误示例：
正确示例：
结论：如图，两个示例比较，优化前查询耗时44ms，优化后耗时只要2ms，优化前后执行效率相差22倍。
第六种索引失效场景：查询条件使用or关键字，其中一个字段没有创建单独索引，则会导致整个查询语句索引失效。
1、or关键字两边其中一个没有索引。
2、这里特别说明or两边的索引都必须是单独索引，组合索引也会导致索引失效。
3、or两边为“>”和“<”范围查询时，也会导致索引失效。
第七种索引失效场景：当查询条件使用in，not in，exist，ont exist，between and时。索引有可能失效。
1、查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
错误示例：
正确示例：
2、查询条件使用not exists时，索引失效。
3、当查询条件为">="、"in"等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描，具体以explain分析为准。
其他索引失效场景：上述七种索引失效场景是我实际工作中常遇到的一些场景，除此之外，还有一些其他索引失效场景，如：条件使用is not null，以及Mysql优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。有时候索引失效并不一定代表SQL查询一定会慢，具体情况我们需要根据explain关键字具体分析。
4、索引优缺点分析
优点：
索引最大的优点是可以大幅提高系统性能，提高查询的速度；
缺点：
索引的存储需要占用磁盘空间； 当数据的量非常巨大时，索引的创建和维护所耗费的时间也是相当大的； 当每次执行新增、修改、删除操作时，索引也需要动态维护，降低了数据的维护速度。
综上所述，索引虽然能大幅度提高我们的查询数据，但是也带来了很多的负担，所以创建索引需要注意以下几点：
索引应该建在离散度高的字段上，即尽量不要对数据库中某个含有大量重复的值的字段建立索引。(枚举字段等，常量字段等) 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。(text类型等超长文本) 因为每次执行新增、修改、删除操作时，索引需要重新维护，所以对于频繁更新的表来说，不要建立太多的索引。(单表索引数量最好不要超过7个) 对于where子句中经常出现的字段可以考虑创建组合索引，其主列字段一般是选择性较好的字段。(最左匹配原则) 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；(不建议超过3个字段)
5、总结
本文介绍了数据库调优中索引的一些相关知识，以及实际项目中索引失效的一些场景及对应优化方案，由于本文中用的测试表数据量较小，效果比对不是很明显，实际工作中大表查询语句通过索引检索查询与非索引检索的查询，SQL执行效率的差别是非常明显的。
作者介绍
Chen·Small-K 信也后端研发专家
招聘信息
Java、大数据、前端、测试等各种技术岗位热招中，欢迎扫码了解~