Mysql中的索引总结

索引是数据库优化中最常用也最重要的手段之一，通过索引可以解决大多数的sql性能问题。在mysql中，索引是在存储引引擎层而不是服务器层实现的，所以，并没有统一的索引标准：不同的存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不通过。

一、索引的分类

InnoDB存储引擎支持以下几种常见的索引：

• B+树索引：我们通常在讨论索引的时候，如果没有特别指明，通常说的就是B+树索引。MyISAM和InnoDB存储引擎的表默认创建的都是B+树索引。B+树索引中的B不代表二叉树(binary)，而是代表平衡树(balanced)。B+树索引并不是一颗二叉树。
• Hash索引：InnoDB存储引擎是支持hash索引的，并且是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成创建hash索引，不能人为干预是否在表中创建hash索引。
• 全文索引：InnoDB从Mysql5.6版本开始提供对全文索引的支持。仅限于CHAR、VARCHAR和TEXT列。全文索引只能用于英文，如果出现中文，还是应该使用Lucene等第三方开源框架来处理。

Mysql官方文档Introduction to InnoDB中图表已经表明了InnoDB存储引擎并不支持Hash索引，但InnoDB内部会使用自适应Hash索引来进行字典的查找，但仅仅是内部使用，并不能手动进行干预。

二、B+树索引

B+树是由二叉查找树(BST)，再由平衡二叉树(AVL)，B树演化而来，这几种树的知识可以参考排序二叉树、平衡二叉树、红黑树、B树、B+树。B+树的操作可以参考B+树的插入和删除操作。

1.为什么数据库索引使用B+树结构，而不选择Hash结构或其它树结构？

索引为什么不用Hash结构？

我们可以看一下Mysql参考官方文档中B树索引和Hash索引的对比：8.3.8 Comparison of B-Tree and Hash Indexes，其中Hash索引的特点中已经说的很清楚了：

• 对于等值查找非常快，但不适合等范围比较操作，如<，between等。
• 不适合顺序查找。(优化器不能使用Hash索引进行ORDER BY操作)
• MySQL不能判断两个值间大致有多少行记录(范围优化器会根据这来决定选用哪个索引)
• 不适合前缀匹配查找

所以Hash索引不适合作为数据库索引。不适合并不表示数据库不支持，实际上Mysql是支持Hash索引的，其Memory存储引擎就支持Hash索引，前面也提到了InnoDB内部会使用自适应Hash索引来进行字典的查找。

索引为什么不用其它树型结构？

普通的二叉树虽然查找时间为O(logN)，但极端条件下会退化到O(N)。

平衡二叉树(AVL树和红黑树)解决了极端条件下查找效率低的问题，但无论如何，二叉的结构决定了树的高度太大，导致IO次数过度，不太适合。

所以只有平衡的多路查找树才比较适合，其中由B树和B+树的不同特点，决定了B+树才适合作为数据库的索引。

①磁盘IO

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，所以索引的组织结构要尽量减少查找过程中磁盘I/O的存取次数。B树的节点既要存索引信息，又要存数据，与B+树相比，在相同数据量下，B树相对需要更多次的IO操作。

②查找方式

数据库需要经常进行范围查找，B+由于其数据都按照顺序保存在叶子节点中，且相互间有指针相连。这样的特点非常适合顺序查找和范围查找。

2.OLTP和OLAP应用中B+树索引的使用

应用程序分为两种：OLTP和OLAP。OLTP应用中，查询操作只从数据库中取得一小部分数据，一般在10条记录以内，甚至很多时候只取一条记录，如根据主键获取用户信息，根据订单号取得订单详细信息，这都是典型的OLTP应用。在这种情况下，B+树索引建立后，对该索引的使用应该只是通过该索引取得表中少部分数据。这时建立B+树索引才是有意义的，否则即使建立了，优化器也可能选择不适用索引。

而对于OLAP应用，则需要访问表中大量的数据，根据这些数据来产生查询的结果，这些查询多是面向分析的查询，目的是为决策者提供支持。如本月每个用户的消费情况，销售额同比、环比增长情况等。这些复杂的查询要涉及到多张表之间的连接操作，因此索引的添加依然是有意义的。但是，如果连接操作使用的Hash Join，那么所以可能又变得不是非常重要。这就需要仔细研究自己的应用了。OLAP应用通常会需要对时间字段进行索引，这是因为大多数统计需要根据时间维度来进行数据的筛选。

三、聚集索引和非聚集索引

数据库中的B+树索引可以分为聚集索引和辅助索引(非聚集索引)。无论是聚集的还是辅助的，其内部都是B+树，即高度平衡的，叶子存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

1.聚集索引(clustered index)

官方文档：14.6.2.1 Clustered and Secondary Indexes

聚集索引，又称为聚簇索引，它并不是一种单独的索引类型，而是一种数据存储方式。每张InnoDB表都有一个聚集索引，表中的数据行就存储在聚集索引中。通常来讲，聚集索引和主键具有相同的含义，但是并不总是如此。

• 如果表显式的定义了主键，InnoDB就会将该主键作为聚集索引。
• 如果表没有显式的定义主键，则会使用第一个非NULL的唯一索引作为聚集索引。非空指的是该唯一索引中的所有key都是非空的。
• 如果没有显式的定义主键，也没有合适的唯一索引，InnoDB就会隐式的创建一个6位的主键自增列，并以该列来创建名为GEN_CLUST_INDEX的聚集索引。该自增列中会记录当前行的行ID，每插入一行记录，对应行的行ID就会自增1，因此行的排列顺序就是行的物理插入顺序。

聚集索引是如何加速查询速度的

通过聚集索引来查询时，最终索引到的页中就直接存储了所有的行记录，无需再进行额外的查询操作，所以大大提升了查询效率。当表非常大时，与未使用聚集索引的索引结构相比，则会节省磁盘IO。

2.辅助索引(非聚集索引，nonclustered index)

辅助索引，也称为非聚集索引或二级索引，除聚集索引以外的所有索引都称为辅助索引。在InnoDB中，辅助索引中的每个记录都包含了该行的主键列，以及为辅助索引指定的列。InnoDB使用此主键值在聚集索引中搜索行，我们称之为回表。

3.两者的区别

非聚集索引和聚集索引的区别在于：

由于聚集索引的叶子节点存放的是完整的行记录信息，而非聚集索引的叶子节点存放的是所要查找的行记录的主键值。所以通过聚集索引可以一次查到需要查找的数据，而通过非聚集索引第一次只能查到记录对应的主键值，需要再次使用主键的值通过聚集索引查找才能到需要的数据。

聚集索引一张表只能有一个，而非聚集索引一张表可以有多个。

四、联合索引和覆盖索引

1.联合索引

官方文档：8.3.5 Multiple-Column Indexes

联合索引是在表上的多个列进行索引。

比如在表中的a，b两列上创建一个联合索引(a,b)，对应的B+树如下。通过叶子节点可以逻辑上顺序的读出所有数据：(1,1)，(1,2)，(2,1)，(2,4)，(3,1)，(3,2)

下面是查询语句使用联合索引的一些情况。

#能使用联合索引
select * from t where a=xxx and b=xxx
select * from t where a=xxx

#不能使用联合索引。【叶子节点上b的值为1,2,1,4,1,2，显然不是排序的】。
select * from t where b=xxx

 

使用联合索引的另一个好处是，可对后续紧挨的列进行排序处理，减少一次filesort。

总的来说，只要满足最左匹配原则，联合索引就能生效。

#联合索引(a,b)：
当a相同的情况下b本来就是排序的，所以能够用到联合索引，且不需要额外再进行排序。
select * from t where a=xxx order by b

#联合索引(a,b,c)：
下面这些能用上联合索引
select * from t where a=xxx order by b
select * from t where b=xxx order by c
select * from t where a=xxx and b=xxx order by c
select * from t where a=xxx
select * from t where a=xxx and b=xxx
select * from t where a=xxx and b=xxx and c=xxx
select * from t where b=xxx and c=xxx

下面这些不能用上联合索引
select * from t where b=xxx
select * from t where c=xxx

2.设计联合索引时，如何选择列的顺序？

联合索引的列顺序非常重要，列的顺序能够决定一个索引的好坏。对于如何选择索引的列顺序，有一个经验法则：

将选择性最高的列放到索引最前列。当然这只是一个建议，场景不同选择也不同。 

当不需要考虑排序和分组时，可以将选择性最高的列放在前面。这时索引的作用只是用于优化WHERE条件的查找，能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，不只是依赖于所有索引列的选择性，也和查询条件的具体值(的分布)有关。

例如，下面的查询是应该创建(staff_id，customer_id)索引还是颠倒顺序呢？

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

可以跑一些查询来确定表中值的分布情况，并确定哪个列的选择性更高。

mysql> SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G
************************************
SUM(staff_id = 2): 7992
SUM(customer_id = 584): 30

根据前面的经验法则，应该将索引列customer_id放在前面，因为对应条件值的customer_id数量更小。(小表驱动大表)

然而，这里的查询结果非常依赖于选定的特定值，如果按此来优化，可能对其它条件值的查询不公平而使得整体性能更糟。所以，我们可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性更高。

如果没法进行上面的查询，事先进行评估，那么最好还是按经验法则来做，毕竟经验法就是基于全局考虑的，而非某个具体的查询。

 

更具体的说明可以参考《高性能mysql》5.3.4 P159—多列索引章节

3.覆盖索引(covering index)

要查询的所有列都被包含在一个索引中，称为覆盖索引，此时只需要通过索引就能得到结果。(表示使用非聚集索引，且不需要回表)

当出现索引覆盖情况时，在explain的extra列中可以看到Using index的信息。

更具体的例子和详细说明可以参考《高性能mysql》5.3.6 P159覆盖索引章节

4.能够使用索引和索引失效的典型场景

见能够使用索引和索引失效的典型场景

 

五、索引下推(ICP，Index Condition Pushdown)

索引下推（ICP，index condition pushdown ）是在Mysql5.6版本中推出的，用于优化查询。

• 在不使用ICP的情况下，在使用非聚集索引进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

对于InnoDB表，ICP仅用于辅助索引。ICP的目标是减少全行读取次数，从而减少I / O操作。

对于InnoDB聚集索引，完整的记录已被读入InnoDB缓冲区。在这种情况下使用ICP不会减少IO。

 

假设有一个people表，含有人的基本信息和地址信息，并且建立了一个联合索引(zipcode, lastname, firstname)。当我们知道某人的邮编，但对其它信息比较模糊时，可以有如下的查询。

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

根据 "最左前缀" 的原则，这里使用了联合索引(zipcode, lastname, firstname)进行了查询。Mysql会通过索引扫描zip=‘95054’的记录，但后面的部分lastname LIKE '%etrunia%'并不能用来限制扫描到的行记录数。在没有ICP优化时，查询会返回所有zip=‘95054’的记录，将记录返回给服务器层，服务器层再去通过where条件过滤查询记录行。

而在有ICP优化时，由于whrere过滤条件中的lastname和address列已经是索引中的一部分，所以会将这部分查询条件下推到存储引擎层，存储引擎层在使用索引扫描时，就会利用这些下推的条件将不符合的条件的行过滤掉了，返回给服务层的记录行就会减少。这样就大大优化了查询。

想一想，如果是一个回表操作，根据聚集索引查询到的行结果越多，回表的次数就会越多。而有了ICP优化，回表次数就能大大降低了。

六、更多

《高性能mysql》第5章

官方文档：

8.3 Optimization and Indexes

• 8.3.1 How MySQL Uses Indexes
• 8.3.2 Primary Key Optimization
• 8.3.3 Foreign Key Optimization
• 8.3.4 Column Indexes
• 8.3.5 Multiple-Column Indexes
• 8.3.6 Verifying Index Usage
• 8.3.7 InnoDB and MyISAM Index Statistics Collection
• 8.3.8 Comparison of B-Tree and Hash Indexes
• 8.3.9 Use of Index Extensions
• 8.3.10 Optimizer Use of Generated Column Indexes
• 8.3.11 Indexed Lookups from TIMESTAMP Columns

 

总结

1.索引的优点？缺点？

2.不适合创建索引的场景？

对于非常小的表，大部分情况下简单的扫描更高效。

3.索引的原理？

4.索引为什么使用B+树数据结构，而不选择Hash或者红黑树数据结构？

5.聚集索引和非聚集索引的区别?

6.能有效利用索引的情况和索引失效的情况？

需要注意的是：B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

7.索引中为什么不能有NULL值?

8.什么是索引覆盖？