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

 

总结

1.索引的优点?缺点?

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

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

3.索引的原理?

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

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

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

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

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

8.什么是索引覆盖?

不积跬步,无以至千里。不积小流,无以成江海!
posted @ 2019-04-24 22:21  静水楼台/Java部落阁  阅读(1074)  评论(0编辑  收藏  举报