文章目录
  1. 1. 索引类型
    1. 1.1. B-Tree索引
    2. 1.2. 哈希索引
    3. 1.3. 空间数据索引(R-Tree)
    4. 1.4. 全文索引
  2. 2. 索引优点
  3. 3. 高效能的索引策略
    1. 3.1. 独立的列
    2. 3.2. 前缀索引和索引选择性
    3. 3.3. 多列索引
    4. 3.4. 选择合适的索引列顺序
    5. 3.5. 聚簇索引
    6. 3.6. 在InnoDB中按主键顺序插入行
    7. 3.7. 覆盖索引
    8. 3.8. 利用索引进行排序
    9. 3.9. 压缩索引
    10. 3.10. 冗余和重复索引
    11. 3.11. 未使用的索引
    12. 3.12. 索引和锁
  4. 4. 三个原则

索引(mysql中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,本文我们就详细介绍一下。

MySQL只能高效地使用索引的最左前缀列。

索引类型

mysql中,索引是在存储引擎层而非服务器层实现的,没有统一的标准,不同存储引擎的索引工作方式并不一样。

B-Tree索引

大多数mysql引擎支持此类索引,Archive是例外。

存储引擎以不同的方式使用B-Tree索引,性能也各有不同,各有优劣。例如:MyISAM使用前缀压缩技术使得索引更小,但InnoDB按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同。如下图:

B-Tree索引能够加快访问数据的速度,意味存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。

B-Tree对索引是顺序组织存储的,所以很适合查找范围数据。

B-Tree索引适用于全关键字、关键字范围和关键字前缀查询。其中键前缀查找只适用于根据最左前缀的查找。可以使用B-Tree索引的查询类型。

  1. 匹配全值(Match the full value):对索引中的所有列都指定具体的值。
  2. 匹配最左前缀(Match a leftmost prefix):你可以利用索引查找last name为Allen的人,仅仅使用索引中的第1列。
  3. 匹配列前缀(Match a column prefix):例如,你可以利用索引查找last name以J开始的人,这仅仅使用索引中的第1列。
  4. 匹配值的范围查询(Match a range of values):可以利用索引查找last name在Allen和Barrymore之间的人,仅仅使用索引中第1列。
  5. 匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part):可以利用索引查找last name为Allen,而first name以字母K开始的人。
  6. 仅对索引进行查询(Index-only queries):如果查询的列都位于索引中,则不需要读取元组的值。

由于B-树中的节点都是顺序存储的,所以可以利用索引进行查找(找某些值),也可以对查询结果进行ORDER BY。

当然B-Tree也存在一些限制:

  1. 查询必须从索引的最左边的列开始。
  2. 不能跳过某一索引列。
  3. 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询。例如,如果你的查询语句为WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23’,则该查询只会使用索引中的前两列,因为LIKE是范围查询。

哈希索引

MySQL中,只有Memory存储引擎显示支持hash索引,是Memory表的默认索引类型,尽管Memory表也可以使用B-Tree索引。Memory存储引擎支持非唯一hash索引,这在数据库领域是罕见的,如果多个值有相同的hash code,索引把它们的行指针用链表保存到同一个hash表项中。

索引自身只需存储对应的哈希值,所以索引的结构十分紧凑,这也让哈希表查询速度非常快。但是也有一些限制:

  1. 由于索引仅包含hash code和记录指针,而不存储字段值,所以,MySQL不能通过使用索引避免读取行。但是访问内存中的记录是非常迅速的,不会对性能造成太大的影响。
  2. 不能使用hash索引排序。
  3. Hash索引不支持键的部分匹配,因为是通过整个索引值来计算hash值的。
  4. Hash索引只支持等值比较,不支持范围查询。例如使用=,IN( )和<=>。对于WHERE price>100并不能加速查询。
  5. 访问哈希索引非常快,除非出现很多哈希冲突。
  6. 如果哈希冲突很多,一些索引的维护操作的代价也会很高。

InnoDB存在一个“自适应哈希索引”,当InnoDB注意到某些索引值被使用得非常频繁时,就会在内存中基于B-Tree索引之上再创建一个哈希索引。

如果出错引擎不支持哈希索引,可以自己定义哈希索引,但是需要手动维护。不用使用MD5()和SHA1()作为hash函数,因为这两个函数计算出来的哈希值是非常长的字符串,会浪费很大的空间,比较时也比较慢。

空间数据索引(R-Tree)

MyISAM支持空间索引,主要用于地理空间数据类型,例如GEOMETRY。

全文索引

全文索引是MyISAM的一个特殊索引类型,主要用于全文检索。它查找的是文本中的关键字,而不是直接比较索引中的值。

索引优点

  1. 大大减少服务器需要扫描的数据量
  2. 帮助服务器避免排序和临时表
  3. 可以将随机I/O变成顺序I/O

高效能的索引策略

独立的列

是指索引不能是表达式的一部分,也不能是函数的参数。eg,

where actor_id+1=5

要修改成actor_id = 4;

前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得大且慢。可以索引开始的部分字符,但是这样会降低选择性。

选择前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。

另一个方法是计算完整列的选择性使其前缀的选择性接近于完整列的选择性。

Mysql无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。

多列索引

在多个列上建立独立的单列索引大部分情况下不能提高Mysql的查询性能。Mysql5.0采用索引合并策略,一定程度上可以使用表上的多个单列索引来定位指定的行,但是之前版本不支持。

索引合并有时候是一种优化策略,但实际上更多时候说明了表上的索引建立的很糟糕。

选择合适的索引列顺序

当不需要考虑分组和排序时,将选择性最高的列放到索引最前列。

避免随机I/O和排序更重要。一定不要忘记where子句中的排序、分组和范围条件等其他因素。

聚簇索引

并不是一种单独索引类型,而是一种数据存储方式。InnoDB(通过主键聚集数据)的聚簇索引实际上是在同一个结构中保存了B-Tree索引和数据行。数据行存储在索引的叶子页中,节点页值包含了索引列。

优点:

  1. 把相关数据保存在一起
  2. 数据访问更快
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点:

  1. 最大限度提高I/O密集型应用性能,但是如果数据全部放在内存中,就没什么必要了
  2. 插入速度严重依赖于插入速度
  3. 更新聚簇索引代价高
  4. 插入新行或者主键被更新面临“页分裂”问题
  5. 可能导致全表扫描变慢
  6. 二级索引(非聚簇索引)可能比较大,需要两次查找。

在InnoDB中按主键顺序插入行

如果你用InnoDB,而且不需要特殊的聚簇索引,一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列,这会保证记录按照顺序插入,而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键,例如,字符串主键就是一个不好的选择,它使得插入操作变得随机。

覆盖索引

果索引包含满足查询的所有数据,就称为覆盖索引。覆盖索引是一种非常强大的工具,能大大提高查询性能。只需要读取索引而不用读取数据。有如下优点:

  1. 索引项通常比记录要小,所以MySQL访问更少的数据;
  2. 索引都按值的大小顺序存储,相对于随机访问记录,需要更少的I/O;
  3. 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
  4. 覆盖索引对于InnoDB表尤其有用,因为InnoDB使用聚集索引组织数据,如果二级索引中包含查询所需的数据,就不再需要在聚集索引中查找了

覆盖索引不能是任何索引,只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同,并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。

利用索引进行排序

MySQL中,有两种方式生成有序结果集:一是使用filesort,二是按索引顺序扫描。利用索引进行排序操作是非常快的,而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时,可以使用索引来排序。如果查询是连接多个表,仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序)。对于filesort,MySQL有两种排序算法。

  1. 两遍扫描算法(Two passes)

    实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的内存(通过参数sort_buffer_size设定)中进行排序,完成排序之后再次通过行指针信息取出所需的Columns。

    注:该算法是4.1之前采用的算法,它需要两次访问数据,尤其是第二次读取操作会导致大量的随机I/O操作。另一方面,内存开销较小。

  2. 一次扫描算法(single pass)

    该算法一次性将所需的Columns全部取出,在内存中排序后直接将结果输出。

    注:从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数,效率较高,但是内存开销也较大。如果我们将并不需要的Columns也取出来,就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中,可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种。为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

压缩索引

MyISAM压缩每个索引的方法:先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。

压缩块使用更少的可见,但是代价是某些操作可能变慢。如查找时不能使用二分查找。

冗余和重复索引

避免重复索引和冗余索引。

未使用的索引

考虑删除

索引和锁

索引对于InnoDB非常重要,因为它可以让查询锁更少的元组。这点十分重要,因为MySQL 5.0中,InnoDB直到事务提交时才会解锁。有两个方面的原因:首先,即使InnoDB行级锁的开销非常高效,内存开销也较小,但不管怎么样,还是存在开销。其次,对不需要的元组的加锁,会增加锁的开销,降低并发性。

InnoDB仅对需要访问的元组加锁,而索引能够减少InnoDB访问的元组数。但是,只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。

三个原则

  1. 单行访问很慢
  2. 按顺序访问范围数据是很快的。
  3. 索引覆盖查询是很快的。
文章目录
  1. 1. 索引类型
    1. 1.1. B-Tree索引
    2. 1.2. 哈希索引
    3. 1.3. 空间数据索引(R-Tree)
    4. 1.4. 全文索引
  2. 2. 索引优点
  3. 3. 高效能的索引策略
    1. 3.1. 独立的列
    2. 3.2. 前缀索引和索引选择性
    3. 3.3. 多列索引
    4. 3.4. 选择合适的索引列顺序
    5. 3.5. 聚簇索引
    6. 3.6. 在InnoDB中按主键顺序插入行
    7. 3.7. 覆盖索引
    8. 3.8. 利用索引进行排序
    9. 3.9. 压缩索引
    10. 3.10. 冗余和重复索引
    11. 3.11. 未使用的索引
    12. 3.12. 索引和锁
  4. 4. 三个原则