什么是索引？

---

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效检索数据的数据结构。简单的来说，索引就相当于字典的目录，帮助我们更快地找到需要查找的文字。

索引的优缺点

---

优点：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量），这也是创建索引的主要原因
• 如果是唯一索引，可以确保数据表中的每一行记录的唯一性

缺点：

• 索引需要占用额外的存储空间
• 创建和维护索引需要耗费时间。MySQL执行增删改操作时，也需要对索引进行修改，会降低SQL的执行效率

常见的索引结构

---

按数据结构分，常用的有hash索引、B树索引和B+树索引，InnoDB使用的是B+树索引，下面将介绍这些索引并分析为何使用B+树索引。

hash：底层就是hash表。hash表是键值对（key-value）的集合，通过哈希算法（也叫散列算法）对key进行计算得到value的index，实现在接近 O（1）下快速检索到数据 。

虽然hash索引查找数据的速度很快，但存在两个问题：

1. hash冲突，指的是多个key通过哈希算法得到的index相同。通常的解决方法是链地址法，将hash冲突的数据存放在链表中，比如Java中的HashMap在一定条件下就是使用链地址法来解决hash冲突的。

2. hash索引不支持顺序查询和范围查询，这个是hash索引最大的缺点。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

   InnoDB实际上会使用一种名为自适应哈希索引(adaptive hash index）来提高查询效率。具体的，InnoDB会监控对表上索引的查找，如果某些索引被频繁访问，则会在其上建立哈希索引来提高检索效率，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的 B+ 树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引。

B树和B+树：B树是一种多路平衡查找树，而B+树是B树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

两者的主要区别：

1. B树的所有节点都存放的key（键）和data（数据），而B+树只有叶子节点才存放key+data，非叶子节点只存放key。
2. B树的叶子节点都是没有关联的，B+树的叶子节点之间通过指针来连接，构成一个有序链表。

上面的两点也是MySQL使用B+树的原因，有如下优点：

1. B+树的树高要比B树低得多（B+树树高为3时就可以存储千万级数据），树高低意味着查询时磁盘IO操作少，效率就更快了。
2. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程（不考虑缓存）。
3. 进行范围查询时，B树需要做局部的中序遍历，可能要跨层访问，跨层访问代表着要进行额外的磁盘I/O操作，B+树可以通过叶子节点之间的指针进行范围查询。

B+树中一个节点的大小多少合适？

---

1页或页的倍数最为合适。因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页等倍数页大小最为合适。

这里说的“页”是 MySQL 自定义的单位（和操作系统类似），MySQL 的 Innodb 引擎中1页的默认大小是16k，可以使用命令SHOW GLOBAL STATUS LIKE ‘Innodb_page_size’ 查看。

MySQL默认一个节点大小为1页，即16K。

为什么B+树高度为3就能存储千万级数据？

---

我们知道，在InnoDB中，非叶子节点存的是key+指针，叶子节点存的是key+data（通常也就是整一行数据）。

对于叶子节点：我们假设一行数据大小为1K（对于一般业务绝对足够了），那么一页就是16条数据。

对于非叶子节点：假设key使用bigint为8字节，指针在Mysql中为6字节，一共是14字节，则16K能存放16 * 1024 / 14=1170个。那么一棵高度为3的B+树能存储的数据为1170 * 1170 * 16 = 21902400 （千万级）

所以，在节点为一页16K时，树高3的B+树就能存储千万级的数据，千万级数据下仅需要3次IO即可找到数据（一般再多就要考虑拆分了）。

索引类型

---

主键索引

数据表的主键列使用的就是主键索引。

一张表有且只能有一个主键。在InndoDB，当没有显式指定表的主键时，如果有不为null的唯一索引，那么会选择该索引字段作为主键；如果没有，InnoDB会自动创建一个 6Byte 的隐藏自增主键字段。

二级索引（辅助索引）

二级索引叶子节点data域存储的是主键。

二级索引又可以分为：

1. 唯一索引（Unique Key）：顾名思义，字段的值不能重复，唯一索引值可以为NULL。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引（Index）：普通索引的唯一作用是为了提高查询效率，允许数据重复和NULL。
3. 前缀索引（Prefix）：前缀索引只适用于字符串类型的数据。前缀索引是截取文本中具有区分度的前缀创建索引，索引数据更小，同时也保证了查询的效率。
4. 全文索引（Full Text）：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。 实际中很少用到。

聚簇索引和非聚簇索引（InnoDB引擎）

---

聚簇索引

聚簇索引即索引结构和数据一起存放的索引。聚簇索引只在InnoDB中有，主键索引属于聚簇索引，聚簇索引文件即数据文件。

• 聚簇索引的优点

  聚簇索引的查询速度非常快，定位到索引的节点，就相当于找到了数据。

• 聚簇索引的缺点

  1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引

非聚簇索引即索引结构和数据分开存放的索引。二级索引属于非聚簇索引。

• 非聚簇索引的优点

  更新代价比聚簇索引小。非聚簇索引的data域不存放数据本身，只有一个主键，索引节点更小。

• 非聚簇索引的缺点

  1. 和聚簇索引一样，依赖于有序的数据。
  2. 可能会二次查询（回表）：当找到索引节点后，可能还需要根据主键再到主键索引中查询。

什么是回表查询？

---

在InnoDB中，对于主键索引，只需要走一遍主键索引的查询就能在叶子节点拿到数据。

而对于二级索引，叶子节点存放的是key+主键值，因此，当查询语句中需要的列在索引中不存在时，需要根据主键值去到主键索引拿取列数据，称为回表。

联合索引

---

联合索引底层使用的还是B+树，并且还是只有一棵树，只是此时的排序会：首先按照第一个索引排序，在第一个索引相同的情况下，再按第二个索引排序，依次类推。

ICP

---

ICP，全称Index Condition Pushdown（索引下推）。

下面我们以一个例子来说明

建表语句

CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) NOT NULL,
 `b` int(11) NOT NULL,
 `c` varchar(64) NOT NULL,
 `d` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_a_b_c` (`a`,`b`,`c`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

test表有联合索引idx_a_b_c (a,b,c)

1. SELECT * FROM test WHERE a = 1 AND b = 2 AND c LIKE 'name%';

   在5.6之前不使用ICP的情况下，最用最左匹配原则。只能使用联合索引的a，然后根据索引data域的主键回表查询，对b和c条件进行过滤。

   使用ICP之后，除了匹配a之外，还额外匹配联合索引的b和c，这样一来，使用联合索引就可以尽可能排除符合where条件的记录，减少回表查询的数据量。

2. SELECT * FROM test WHERE a = 1 AND c LIKE 'name%';

   这条语句也会使用ICP，ICP的目的是尽可能的过滤不符合条件的记录，哪怕不符合最左匹配的原则，因为这样能降低执行的成本。

综上，ICP的实质就是通过二级索引尽可能的过滤不符合条件的记录，哪怕不符合最左匹配原则，减少回表，降低执行成本。

参考：MySQL索引条件下推–优化实战

扩展-EXPLAIN

---

explain的字段有：

• id：标识符。id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行 。

• select_type

  ：查询类型。

  • SIMPLE(简单SELECT，不使用UNION或子查询等)
  • PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
  • UNION(UNION中的第二个或后面的SELECT语句)
  • DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
  • UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
  • SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
  • DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
  • DERIVED(派生表的SELECT, FROM子句的子查询)
  • UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

• table：显示查询的表名称，有时不是真实的表名字 。

• type：对表访问方式。常用的类型有：ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

  • ALL：全表查询
  • index: Full Index Scan，index与ALL区别为index类型只遍历索引树
  • range:只检索给定范围的行，使用一个索引来选择行
  • ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  • eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
  • const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
  • NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

• possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

• Key：key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。不损失精确性的情况下，长度越短越好

• ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• row：查询所需要扫描的行数，是一个估算值

• Extra：该列包含MySQL解决查询的详细信息

  • Using where：使用覆盖索引时会出现，不用读取表中所有信息，仅通过索引就可以获取所需数据。
  • Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
  • Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
  • Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
  • Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）
  • Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
  • No tables used：Query语句中使用from dual 或不含任何from子句

参考：MySQL Explain详解

如何做慢SQL优化

---

首先要搞明白SQL慢的原因是什么？是查询了不需要的列或行？还是数据量太大？或是查询条件没有命中索引？大致步骤如下：

• 首先使用explain分析语句的执行计划，查看索引的使用情况，是不是没有走查询索引，是的话可以考虑加索引解决。
• 分析语句，看是否存在一些导致索引失效的用法（如在where条件中进行运算、隐式转换等），是否加载了不需要的列和行，是的话对语句进行重写。
• 如果对语句的优化已无法进行，考虑表中的数据量是否太大，如果是的话可以进行垂直或者水平拆分。

创建索引的一些建议

---

1. 选择合适的字段创建索引：
   • 不为NULL的字段：数据库对于为NULL的字段，需要更多的存储空间，并且在查询时索引统计和值比较都更复杂，优化更复杂。
   • 频繁作为条件查询、排序的字段：可以利用索引，加快检索和排序效率。
   • 更新频率较低的字段：索引需要维护，频繁更新会降低SQL效率。
   • 字段的值应该具有区分度：作为索引的字段应尽量做到重复值较少，如性别字段只有两个值就不适合作为索引。
2. 尽可能的考虑建立联合索引而不是单列索引，避免冗余索引：索引是需要占用磁盘空间的，如果一个联合索引能覆盖列a、b，那就不需要单独建一个列a的索引了。
3. 字符串类型的索引考虑使用前缀索引：在保证索引区分度的情况下，截取字符串的某段前缀，是空间和时间上的平衡取舍。