《高性能MySQL》第05章-创建高性能索引

intro

索引优化通常是优化查询性能的最有效手段

5.1 索引的基础

5.1.1 索引的类型 B-Tree 索引

“B-Tree”这么叫的原因是MySQL在CREATE TABLE和其他语句中也使用该关键字
存储引擎以不同的方式使用B-Tree，例如MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储；MyISAM索引通过数据的物理位置引用索引的行，而InnoDB则根据主键引用被索引的行
B-Tree 对索引是顺序组织的，很适合查找范围数据。
索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。
索引中的列的顺序十分重要

索引生效的场景：

全值匹配（使用索引中所有列进行匹配）
匹配最左前缀（只使用索引的前几列）
匹配列前缀（每列支持前缀匹配）
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询

B-Tree索引的限制：

如果不是按照索引的最左列开始查询，无法使用索引
不能跳过索引中的列
如果查询中某个列的范围查询，则其右边所有的列都无法使用索引优化查找

哈希索引

只有精确匹配索引所有列的查询才有效
Memory引擎显式支持哈希索引
哈希索引只包含索引值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
不支持排序
不支持部分索引列匹配查找，只支持等值比较查询
如果哈希冲突很多，一些索引的维护操作的代价也会很高，查找也会变慢

空间数据索引（R-Tree）

全文索引

其他索引类型

5.2 索引的优点

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

5.3 高性能索引策略

5.3.1 独立的列：索引列不能是表达式的一部分 5.3.2 前缀索引和索引选择性：决定合适的前缀长度 5.3.3 多列索引 5.3.4 选择合适的索引列顺序

通常将选择性最高的列放在前面
同时可能需要根据那些运行频率最高的查询来调整索引列的顺序
总之，让整体执行的次数最少 5.3.5 聚簇索引优点：
可以把相关数据保存在一起
数据访问更快
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了。
插入速度严重依赖于插入顺序。（按照主键的顺序插入）（如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE 命令重新组织一下表）
更新聚簇索引列的代价很高，因为会强制InnoDB将每个更新的行移动到新的位置
页分裂（导致表占用更多的空间）
可能导致全表扫描变慢，尤其是行比较稀疏，或者是由于页分裂导致数据存储不连续的时候。
二级索引（非聚簇索引）可能比想象大，需要两次索引查找

随机大量插入主键（例如插入UUID）会导致：

写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没被加载到缓存中，这将导致大量的随机I/O
因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个。
频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

顺序的主键什么时候会造成更坏的后果？

主键的上界会成为“热点”（可能导致间隙锁竞争）
AUTO_INCREMENT 锁机制（解决方法：更改innodb_autonic_lock_mode 配置）

5.3.6 覆盖索引 5.3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：（1）排序操作（2）按索引扫描
如果EXPLAIN出来的type的列的值为index, 则说明使用了索引扫描来做排序（注意不要和Extra列的Using Index混淆）

MySQL可以使用同一个索引既满足排序，又用于查找行，设计索引时应该考虑这一点。

只有当索引的列顺序和ORDER BY子句的顺序完全一致时，并且所有列的排列方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序
如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。
ORDRE BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求
前导列为常量时也满足条件（本质上还是索引匹配，需要明确先join 后 where）

例子：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


create table rental (
    ...
    primary key (rental_id),
    unique key rental_date (rental_date, inventory_id, customer_id),
    key idx_fk_inventory_id(inventory_id),
    key idx_fk_customer_id(customer_id),
    key idx_fk_staff_id(staff_id)
    ... 
)


# 可以使用索引：
select rental_id, staff_id from rental 
where rental_data = '2005-05-25'
order by invenroty_id, customer_id;

# 可以使用索引：
... where rental_date = '2005-05-25' order by inventory_id desc;


# 无法使用索引: （排序方向不相同） 
... where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc;

# 无法使用索引：（staff_id不在索引中）
... where rental_date = '2005-05-25' order by inventory_id, staff_id;

# 无法使用索引：（无法组合成索引的最左前缀）
... where rental_date = '2005-05-25' order by customer_id;


# 无法使用索引：（第一列是范围查询）
... where rental_date > '2005-05-25' order by inventory_id, customer_id; 

# 无法使用索引：（inventory_id 列上有多个等于条件）
... where rental_date > '2005-05-25' and inventory_id in (1, 2) order by customer_id; 

5.3.8 压缩（前缀压缩）索引

5.3.9 冗余和重复索引

通常应该避免出现重复索引
大多数情况下我们不需要冗余索引，应该扩展已有的索引。通常当扩展已有索引会导致原索引性能大幅下降的情况下，建立一个冗余索引。
开销问题，当冗余索引一多，表的规模一大，那么索引的插入，更新、删除的速度就会越慢。

5.3.10 未使用的索引应该删除未使用的索引

5.3.11 索引和锁

InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁，这消除了使用覆盖索引的可能性，并且使得select for update 比lock in share mode 或非锁定查询慢很多（？？？？）

5.4 索引案例学习

5.4.1 支持多种过滤条件将范围查询放到最后面

5.4.2 避免多个范围条件解决方法：为了使用索引，最多保留一个范围条件，将其他的范围条件转换为等值查询，通常需要额外的字段和定时任务维护。

5.4.3 优化排序

延迟关联：通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这样可以减少MySQL扫描那些需要丢弃的行数（？？？）

5.5 维护索引和表

维护表有三个主要目的：（1）找到并修复受损的表（2）维护准确的索引统计信息（3）减少碎片

5.5.1 找到并修复损坏的表 5.5.2 更新索引统计信息

analyze table
InnoDB通过抽样的方式来计算统计信息
表首次打开、执行analyze table、表的大小变化超过十六分之一或者插入了20亿行的时候都会触发计算索引的统计信息。
innoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX，抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新

5.5.3 减少索引和数据的碎片行碎片行间碎片剩余空间碎片

可以通过OPTIMIZE TABLE或者导出再导入的方式来重新整理数据

5.6 总结

三个原则：

单行访问是很慢的（随机I/O）
按顺序访问范围数据是很快的
索引覆盖查询是很快的

intro#

5.1 索引的基础#

5.2 索引的优点#

5.3 高性能索引策略#

5.4 索引案例学习#

5.5 维护索引和表#

5.6 总结#