高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。

索引的字段应该尽可能的小

因为每次查索引本身也是一次IO,较小的索引可以有更好的性能。

索引限制

使用索引的操作符 <,<=,=,>,>=,between, IN
like:‘name%’使用索引,’%name%’不适用索引
扫描的记录数超过30%会进行全表扫描

独立的列

如果查询中的列不是独立的,则mysql就不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数。
  • 示例一:索引列不能是表达式的一部分
SELECT item_code FROM cc_item WHERE item_code + 1 = '282005';
  • 示例二:索引列不能是函数的参数
SELECT ... WHERE TO_DAYS(CURRENT_DATE()) - TO_DAYS(date_col) <= '10';

前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变的大且慢,一个策略是模拟哈希索引
通常还可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率,但这样也会降低索引的选择性索引的选择性越高则查询效率越高,因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能。对于BLOBTEXT或者很长的VARCHAR类型的列,必须使用前缀索引,因为mysql不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。
计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。

mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) FROM people;
+------------------------------------+
| COUNT(DISTINCT last_name)/COUNT(*) |
+------------------------------------+
| 0.8462 |
+------------------------------------+


通常来说,示例中如果前缀的选择性能够接近于0.846,基本上就可用了。可以在一个查询中针对不同前缀长度进行计算,这对于大表非常有用。

mysql> SELECT COUNT(DISTINCT LEFT(last_name, 2))/COUNT(*) AS sel2,
-> COUNT(DISTINCT LEFT(last_name, 3))/COUNT(*) AS sel3,
-> COUNT(DISTINCT LEFT(last_name, 4))/COUNT(*) AS sel4,
-> COUNT(DISTINCT LEFT(last_name, 5))/COUNT(*) AS sel5,
-> COUNT(DISTINCT LEFT(last_name, 6))/COUNT(*) AS sel6,
-> COUNT(DISTINCT LEFT(last_name, 7))/COUNT(*) AS sel7,
-> COUNT(DISTINCT LEFT(last_name, 8))/COUNT(*) AS sel8,
-> COUNT(DISTINCT LEFT(last_name, 9))/COUNT(*) AS sel9
-> FROM people;
+--------+--------+--------+--------+--------+--------+--------+--------+
| sel2 | sel3 | sel4 | sel5 | sel6 | sel7 | sel8 | sel9 |
+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.4615 | 0.4615 | 0.7692 | 0.7692 | 0.7692 | 0.7692 | 0.7692 | 0.8462 |
+--------+--------+--------+--------+--------+--------+--------+--------+

只看平均选择性是不够的,也有例外的情况,需要考虑最坏情况下的选择性。如果数据分布很不均匀,可能就会有陷阱。
前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点:mysql无法使用前缀索引做GROUP BYORDER BY,也无法使用前缀索引做覆盖扫描。


多列索引

一个常见的错误是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引。为每个列创建独立索引的策略,一般是听从“把WHERE条件里面的列都建上索引”这种错误建议。

在多个列上建立独立的索引大部分情况下并不能提高mysql的查询性能。mysql 5.0和更新版本引入了一种叫索引合并(index merge)的策略,一定程度上可以使用表上的多个单列索引来定位指定的行,查询能够同时使用这两个单列索引进行扫描,并将结果进行合并,这种算法有三个变种:
  • OR条件的联合(union)
  • AND条件的相交(intersection)
  • 组合前两种情况的联合及相交
例如,字段last_name、first_name上各有一个单列索引:
mysql> EXPLAIN SELECT last_name,first_name FROM people WHERE last_name = 'yanzuojing' OR first_name = 'h'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: people
type: ALL
possible_keys: last_name,first_name
key: NULL
key_len: NULL
ref: NULL
rows: 13
Extra: Using where

索引合并策略有时候是一种优化的结果,但实际上更多时候说明了表上的索引建的很糟糕:
  • 当有多个AND条件,通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引
  • 当有多个OR条件,通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上
  • 更重要的是,优化器不会把这些计算到查询成本中,优化器只关心随机页面读取。这会使得查询的成本被低估,导致该执行计划还不如直接走全表扫描

选择合适的索引顺序

正确的索引顺序依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要。
在一个多列B-Tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,等等。所以,索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的ORDER BYGROUP BYDISTINCT等子句的查询需求。
至于如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的,这时候索引的作用只是用于优化WHERE条件的查找。然而,性能不只是依赖于所有索引列的选择性(整体基数),也和查询条件的具体值有关,也就是和值的分布有关。
SELECT * FROM people WHERE last_name = 'yanzuojing' AND first_name = 'h';

上述示例是创建一个(last_name, first_name)索引还是应该颠倒顺序?
根据前述经验法则,应该将last_name放到前面,因为对应值的last_name数量更小,但这样查询的结果非常依赖于选定的具体值。如果没有类似的具体查询来运行,最好还是按经验法则来做,因为经验法则考虑的是全局基数和选择性,而不是某个具体查询:
mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) AS l_select,
-> COUNT(DISTINCT first_name)/COUNT(*) AS f_select,
-> COUNT(*)
-> FROM people;
+----------+----------+----------+
| l_select | f_select | COUNT(*) |
+----------+----------+----------+
| 0.8462 | 0.9231 | 13 |
+----------+----------+----------+

first_name的选择性更高,所以将其作为索引列的第一列。

聚簇索引

当表有聚簇索引时,它的数据行实际上存放在索引的叶子叶中“聚簇”表示数据行和相邻的键值紧凑的存储在一起


InnoDB通过主键聚集数据,即上图中被索引的列就是主键列。如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。
聚集的数据的优点:
  • 可以把相关数据保存在一起
  • 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
聚簇主键可能对性能有帮助,但也可能导致严重的性能问题。所以需要仔细的考虑聚簇索引,尤其是将表的存储引擎从InnoDB改成其它引擎时。聚簇索引的缺点:
  • 聚簇数据最大限度的提高了I/O密集型应用的性能,但如果数据全部都放在内存中,则访问的顺序也就没那么重要了,聚簇索引也就没什么优势了
  • 插入速度严重依赖于插入顺序
  • 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置
  • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行时,可能面临页分裂(page split)的问题
  • 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候
  • 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列
  • 二级索引访问需要两次索引查找,而不是一次

二级索引叶子结点保存的不是指向行的物理位置的指针,而是行的主键值。这意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子结点获得对应的主键值,然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作:两次B-Tree查找而不是一次,对于InnoDB,自适应哈希索引能够减少这样的重复工作。 聚簇索引的每一个叶子结点都包含了主键值、事务ID、用于事务和多版本控制(MVVC)的回滚指针以及所有的剩余列


InnoDB按主键顺序插入

如果正在使用InnoDB表并且没有什么数据需要聚集,那么可以定义一个代理键作为主键,这种主键的数据应该和应用无关,最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好。
最好避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是对于I/O密集型的应用,它使得聚簇索引的插入变的完全随机,使数据没有任何聚集特性。使用InnoDB时应该尽可能的按主键顺序插入数据,并且尽可能的使用单调增加的聚簇键的值来插入新行

覆盖索引

如果一个索引包含(或者覆盖)所有需要查询的字段的值,则称之为覆盖索引,优点如下:
  • 索引条目通常远小于数据行大小,所以如果只需要读取索引,mysql就会极大的减少数据访问量
  • 因为索引是按照列值顺序存储(至少在单个页内是如此),所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
  • 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询
不是所有类型的索引都可以成为覆盖索引,覆盖索引必须要存储索引列的值,

而哈希索引、空间索引和全文索引等都不存储索引列的值,所以mysql只能使用B-Tree索引做覆盖索引

InnoDB的二级索引的叶子结点都包含了主键的值,这意味着InnoDB的二级索引可以有效的利用这些“额外”的主键列来覆盖查询。

使用索引扫描做排序

mysql有两种方式可以生成有序的结果:通过排序操作,或者按索引顺序扫描。如果EXPLAIN中的type列的值为index,则表明mysql使用了索引扫描来做排序。
扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O,因此按索引顺序读取的速度要比顺序的全表扫描慢。

只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向都一样时,mysql才能使用索引来对结果做排序

有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候,示例如下,

KEY last_name (first_name,last_name) USING BTREE
SELECT dob,address FROM people WHERE first_name = 'm' ORDER BY last_name\G

冗余索引

如果创建了索引(A, B),再创建索引(A)就是冗余索引,因为这只是前一个索引的前缀索引。
mysql的唯一限制和主键限制都是通过索引实现
大多数情况下都不需要冗余索引,应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变的太大,从而影响其他使用该索引的查询的性能。
一般来说,增加新索引将会导致INSERTUPDATEDELETE等操作的速度变慢,特别是当新增索引后导致达到了内存瓶颈的时候。

索引和锁

索引可以让查询锁定更少的行。如果查询从不访问那些不需要的行,那么就会锁定更少的行,从两个方面来看这对性能都有好处:
  • 首先,虽然InnoDB的行锁效率很高,内存使用也很少,但是锁定行的时候仍然会带来额外开销
  • 其次,锁定超过需要的行会增加锁争用并减少并发性
在mysql 5.1和更新的版本中,InnoDB可以在服务器端过滤掉行后就释放锁。
InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)。这消除了使用覆盖索引的可能性,并且使得SELECT ... FOR UPDATE 比 LOCK IN SHARE MODE或非锁定查询要慢的多



上一篇:InnoDb索引实现
下一篇:三星索引

成为你想看到的世界变革力量

创建者:万乐荣
最后更新时间 : 2018年9月4日 17:59

评论