0%

MySQL 性能-索引

发表于 更新于 分类于

索引类型

索引在存储引擎层实现。

扫描索引本身很快，但按索引顺序读取数据的速度通常要比顺序的全表扫描慢，因为基本是随机 I/O。

B-Tree 索引

此处的 B 是平衡，而非二叉（演化自 AVLTree ）

B-Tree 作为术语使用（即使是 T-Tree 结构），多数存储引擎其实是 B+Tree 。

索引对多个值进行排序的依据是 create table 中定义索引时列的顺序。

适合如下查询：

  • 匹配全值，即所有列；
  • 匹配最左前缀，即索引第一列；
  • 匹配列前缀，即列值的开头部分；
  • 匹配范围值；
  • 精确匹配某一列范围匹配另一列；
  • 仅访问索引。

因为索引树的节点有序，还可用于查询中的 order by 操作。

限制：

  • 必须从索引的最左列 / 列的最左开始查找；
  • 不能跳过索引中的列；
  • 范围查询右边的所有列无法使用索引，where last_name = 'S' and first_name like 'J%' and ... 只能使用索引的前两项（key(last_name, first_name, ...)）。

哈希索引

仅 Memory 引擎显式支持。

创建自定义哈希索引

InnoDB 引擎中，某些索引值使用非常频繁时，会在内存中基于 B-Tree 索引再创建一个哈希索引。类似，在 B-Tree 基础上创建一个伪哈希索引，使用哈希值而不是键值本身进行索引查找（在 where 子句中使用哈希函数）：

1
2
3
4
select id from url where url="http://...";
-- 删除 url 列的索引，新增被索引的 url_crc 列，使用 CRC32 做哈希。
select id from url where url="http://..." and url_crc=CRC32("http://...");
-- 缺陷：通过手动/触发器在插入和更新时维护 url_crc 列

ps：SHA1() 和 MD5() 计算出的哈希值过长，代价较大，除非最大限度消除冲突。

处理哈希冲突

数据表非常大时，考虑自己实现一个 64 位返回整数的哈希函数（比如截取 MD5() 的一部分）。

使用哈希索引进行查询时，必须在 where 子句中包含常量值：

1
2
-- 错误！哈希冲突可能导致返回多条记录。
select id from url where url_crc=CRC32("http://...");

摘要

空间数据索引（R-Tree）

MyISAM 支持空间索引，用作地理数据存储。

全文索引

适用于 match against 操作而非 where 条件操作。

索引策略

非常小的表使用简单的全表扫描可能更高效。

独立的列

索引列不能是表达式的一部分，或是函数的参数：

1
2
-- 错误！
select id from actor where id + 1 = 5;

前缀索引和索引选择性

对于很长的字符串，通常索引开始的部分字符，可以大大节约索引空间，但也会降低索引的选择性。

索引的选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间，选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。

前缀的基数应该接近完整列的基数：

1
2
3
4
5
6
select count(distinct city) / count(*) from city_demo;
select count(distinct left(city, 3)) / count(*) as sel3,
-> count(distinct left(city, 4)) / count(*) as sel4,
-> count(distinct left(city, 5)) / count(*) as sel5,
->from city_demo;
-- 平均选择性无法发现数据分布的不均匀。

创建前缀索引（MySQL 无法使用前缀索引做 order by 和 group by ，也无法做覆盖扫描）：

1
alert table city_demo add key(city(7));

多列索引

一个多列索引与多个列索引 MySQL 在解析执行上是不一样的，如果在 explain 中看到有索引合并，应该好好检查一下查询的表和结构是不是已经最优：

  • 当出现服务器对多个索引做交互操作时（通常有多个 and 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引；
  • 当服务器需要对多个索引做联合操作时（通常有多个 or 条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

合适的索引列顺序（B-Tree）

当不需要考虑排序和分组时，通常将选择性最高的列放在前面，此时索引只用于优化 where 条件的查找。

性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值的分布有关。和选择前缀索引的长度需要考虑的地方一样，可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能（当使用前缀索引时，在某些条件值的基数比正常值高的时候）。

聚簇索引

一种数据存储方式，InnoDB 中实际是在同一个结构中保存了 B-Tree 索引和数据行。因为数据行不能同时存放在两个地方，所以一个表只能有一个聚簇索引（但覆盖索引可模拟）。

InnoDB 使用主键聚集数据，若没有定义主键则选择一个唯一的非空索引代替，若仍没有则隐式定义一个主键作为聚簇索引；二级索引（非聚簇索引）的叶子节点是引用行的主键列（行指针并非指向行的物理地址而是主键值），即二级索引访问需要两次 B-Tree 查找，不过免去了行移动或页分裂时二级索引的维护工作。

一些注意点：

  • 聚簇索引提高 I/O 密集型应用性能，若数据都放在内存里则没有多大优势；
  • 插入速度严重依赖插入顺序，最好按主键的顺序插入（比如自增长），若随机插入使用 optimize table 重新组织一下表；
  • 更新局促索引列的代价很高，每个被更新的行要移动到新位置；
  • 插入新行，或主键更新导致行移动时，可能有页分裂问题（内碎片和占用更多磁盘空间）；

覆盖索引

索引中包含所有需要查询的字段的值，不需要回表查询；因为要存储值，所以只能用 B-Tree 索引。

发起索引覆盖查询时，explain 的 extra 列为 using index 。

使用延迟关联解决索引无法覆盖问题：

1
2
SELECT * FROM products WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%'
-- 1.没有覆盖全部列的索引；2.like操作只有在匹配左前缀时才能使用索引。

建立 (actor, title, prod_id) 索引。先在子查询中找到匹配的 prod_id ，然后在外层查询中匹配获取所有列值。优化效果取决于 where 条件匹配返回的行数：

1
2
3
4
SELECT * FROM products 
    JOIN (SELECT prod_id FROM products 
          WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%')
	AS t1 ON (t1.prod_id = products.prod_id)

当符合 where 条件的数据数量远小于 actor 过滤出的数据数量的时效率尤其高。因为根据子查询的where过滤出数据之后才与外层查询关联，而后者使用 actor 读取出数据之后，再用 title 进行关联。

使用索引扫描做排序

使用索引扫描排序时，explain 的 type 列为 index 。MySQL 可以使用一个索引既满足排序，又满足查找。只有当索引的列顺序和 order by 子句顺序一致，且列的排序方向（倒序或正序）都一样时，才能用索引对结果做排序。

order by 子句需要满足索引的最左前缀要求，除非前导列制定为常量：

1
2
3
4
5
6
7
8
9
10
11
key col_key (col1, col2, col3)

...where col1 = 1 order by col2, col3; -- (√) 
...where col1 = 1 order by col2; -- (√)
...where col1 > 1 order by col1, col2; -- (√)

...where col1 = 1 order by col2 desc, col3 asc; -- (X)
...where col1 = 1 order by col2, col4; -- (X)
...where col1 = 1 order by col3; -- (X)
...where col1 > 1 order by col2, col3; -- (X) 范围条件
...where col1 = 1 and col2 in(1,3) order by col3; -- (X) 范围条件

前缀压缩索引

MyISAM 先保存索引块的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，如 “perform” “performance” 类似 “7,ance” 的形式。

因为每个值的压缩前缀都依赖前面的值，所以查找时只能从头开始扫描（无法二分查找），倒序扫描的速度就更差了（order by desc）。

冗余和重复索引

增加新索引将会导致 insert update delete 等操作的速度变慢。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，索引类型不同不算重复。

主键限制和唯一限制是通过索引来实现的，如果再定义索引就会重复。

对于 B-Tree 索引，若创建了索引 (A,B) 再创建索引 (A) 则冗余，而索引 (B,A) 和 (B) 不是，因为 (B) 不是最左前缀。

冗余索引通常发生在添加新索引的时候。如已有索引 (A) ，增加 (A,B) 而不是扩展；或扩展为 (A,PK)，对于 InnoDB 而言主键列已经包含在二级索引中。

  • 避免扩展已有的索引导致其变得太大，从而影响其他使用该索引查询的性能。

索引和锁

InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。

InnoDB在二级索引上使用共享锁（读锁），但在访问主键时使用排它锁（写锁）。

ps 在即使使用了索引，也可能锁住一些不需要的行。

维护索引和表

表损坏

当碰到古怪的问题，比如不应该发生的主键冲突，可以通过 CHECK TABLE 来检查是否发生了表损坏。该命令通常能够找出大多数表和索引的错误。 可以执行 REPAIR TABLE 来修复损坏的表。 也可以通过一个不做任何数据操作的 ALTER 操作来重建表，以达到修复的目的：ALTER TABLE innodb_tbl ENGINE=INNODB;

减少碎片

行碎片，行间碎片，剩余空间碎片（页内碎片）。

InnoDB 会移动短小的行重写到一个片段中，不会出现短小的行碎片。

通过 optimize table 或者导出再导入的方式重新整理数据。

考虑数据是否已到稳定状态，以免之后的更新操作引发分页和重组。