创建高性能的索引

索引基础

索引可以包含一个或多个列的值，MySQL 只能高效地使用索引的最左前缀，如果索引包含多个列，那么列的顺序也十分重要。

B 树索引

B 树中所有的值按顺序存储，每个叶节点到根的距离相同。

索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

B 树适用于全键值、键值范围或键前缀查找：

全值匹配：和索引中的所有列进行匹配
匹配最左前缀：只使用索引的第一列
匹配列前缀：只匹配某一列的开头部分
匹配范围值：匹配值处于某个区间
精确匹配某一列并范围匹配另一列
只访问索引的查询

索引树中的节点是有序的，因此还可以用于查询中的ORDER BY操作。如果ORDER BY子句满足前面列出的集中查询类型，则索引也可以满足对应的排序需求。

B 树索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。如为last_name, first_name, birthday创建索引，索引无法用于查询名字为 Bill 的人，也无法用于查找某个特定的生日。类似的，无法查找姓氏以某个字结尾的人。
不能跳过索引中的列。前述的索引无法用于查找特定姓氏并且在某个特定生日的人，如果不指定 first_name，只能使用索引的第一列。
如果查询中有某个列的范围查询，则其右边所有的列无法使用索引优化查询。

哈希索引

hash index 只有在精确匹配索引所有列的时候才有效。对于一行数据，存储引擎对所有索引列计算一个 hash code。

MySQL 中只有 Memory 引擎显式支持哈希索引。此外其还支持非唯一哈希索引，哈希值相同是，索引会以链表的形式存放多个记录指针到同一个哈希条目中。

哈希索引无法用于排序，不支持部分列匹配。

InnoDB 引擎支持“自适应哈希索引（adaptive hash index）”，当 InnoDB 注意到某些索引值被使用得相对平凡是，它会在 B 树索引之上再创建一个哈希索引。这是一个内部行为，用户无法控制或配置。

可以维护一个 hash 列，查询时手动指定使用 hash 函数，实现类似哈希索引的效果。

空间数据索引（R-Tree）

MyISAM 支持空间索引，可以用作地理数据存储，用所有维度来索引数据，必须使用 GIS 相关函数MBRCONTAINS()等来维护数据，但并不完善。PostgreSQL 中的 PostGIS 较好。

全文索引

详见第 7 章

索引的优点

三大优点：

减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机 IO 变为顺序 IO

高性能的索引策略

独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。我们应该养成简化 where 条件的习惯，时钟将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

索引很长的字符列会让索引变得大且慢，一个策略是模拟 hash 索引。

此外还可以只索引开头的部分字符，节约索引空间，提升效率，但会降低索引的选择性。索引的选择性是指，不重复的索引值（基数，cardinality）和记录总数的比值。

为了确定前缀的合适长度，需要将常见值和常见的前缀进行比较。

方法一：定性比较

SELECT COUNT(*) AS cnt, city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

-- 修改前缀长度，寻找合适长度
SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

方法二：计算选择度，定量比较

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,
COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6
FROM city_demo;

方法二中的数值反应平均选择性，还要通过观察调整避免极端案例（最常出现前缀）带来的影响。

创建前缀索引

1	ALTER TABLE city_demo ADD KEY (city(7));

前缀索引是一种能够是索引更小更快的办法，但也有缺陷：无法使用前缀索引来做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

一个常见场景是针对很长的十六进制唯一 ID 使用前缀索引。

后缀索引（suffix index）也有用途，例如查找某个域名的邮箱地址。MySQL 原生并不支持，但可以把字符串反转后存储，创建前缀索引。通过触发器来维护。

多列索引

在多个列上分别创建单列索引大部分情况下不能提高查询性能。MySQL 5.x 引入了“索引合并（index merge）”的策略，一定程度上使用表上的多个单列索引来定位指定的行。算法包括：OR 条件的联合，AND 条件的相交，组合前两种情况的联合及相交。可以在EXPLAIN中的 Extra查看。

出现索引合并意味着索引建得比较糟糕：

当出现多个索引相交（多个 AND），通常需要创建一个包含多个列的索引
当出现联合操作（多个 OR 条件），通常需要耗费大量 CPU 和内存用于缓存、排序和合并
更重要的是，优化器不会吧这些计算到查询成本中，优化器只关心随机页面读取。有时运行这样查询不如直接全表扫描。可通过optimizer_switch关闭索引合并，也可以使用IGNORE INDEX提示让优化器忽略有些索引。

选择合适的索引顺序

场景不同，选择不同，需要根据运行频率最高的查询来调整索引列的顺序。

当不想需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的。

聚簇索引

聚簇索引（Oracle 称为索引组织表，index-organized table）并不是单独的数据类型而是一种数据存储方式，InnoDB 的聚簇索引在同一个结构中保存了 B-Tree 索引和数据行。当表有聚簇索引时，它的数据行存放在索引的叶子页（leaf page）。因为无法同时把数据放在不同的地方，因此一个表只能有一个聚簇索引。

InnoDB 默认通过主键聚集数据，没有主键会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键作为聚簇索引。

聚簇索引的优点：

相关数据共同存储，减少随机 IO
数据访问更快
使用覆盖索引扫描的查询可以直接使用页节点中的主键值 ——不太理解

缺点：

数据存放在内存中时没有优势
插入顺序，如果不是按照主键顺序插入数据，那么在数据插入完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表
更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
基于聚簇索引的表在插入行或更新主键时，可能面临“页分裂（page split）”问题。当行的主键值要求不许将这一行插入到某个已满的页中是，存储引擎会将该页分裂成两个页面来容纳该行（两个页可能隔得很远），页分裂会导致表占用更多磁盘空间
聚簇索引可能导致全表扫面变慢，尤其是行比较稀疏，或者因为页分裂导致数据存储不连续的时候
二级索引访问需要两次索引查找：二级索引的叶节点不存储地址而是存储行的主键，通过二级索引查找行，需要先在二级索引的叶节点中找到对应的主键值，然后根据主键值在聚簇索引中找到对应的行。

InnoDB 和 MyISAM 的数据分布对比

MyISAM：按照数据插入的顺序存储在磁盘上。索引的叶节点存储数据的物理地址，主键索引和其他索引没有什么不同

InnoDB：在 InnoDB 中聚簇索引就是表，不需要独立的行存储，聚簇索引的每个叶节点都包含了主键值、事务 ID、回滚指针以及剩余的列。如果使用主键的列前缀索引，InnoDB 也会包含完整的主键列。

二级索引中叶节点存储的不是“行指针”，而是主键值，这样的策略减少了行移动或数据页分裂时二级索引的维护工作，但当主键值较长时会让二级索引占用更多的空间。

在 InnoDB 表中按主键顺序插入行

如果表中没有什么数据需要聚集，可以定义一个代理键（surrogate key）作为主键，最简单的就是自增列，可以保证数据行顺序写入。

最好避免随机的（不连续且范围很大）聚簇索引，特别是对于 IO 密集型的应用。从这个角度，使用 UUID 作为聚簇索引很糟糕，插入的花费时间更长，索引占用的空间更大。

顺序主键什么时候会造成坏的结果？
对于高并发工作符合，按主键顺序插入可能会造成明显的争用

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。使用覆盖索引可以直接获得列的数据，不需要再去读取数据行。

索引条目通常远小于数据行大小，只读取索引会极大减小数据访问量。
索引按照列值顺序存储，范围查询有优势
一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统缓存，只扫描索引减少系统调用。
对于聚簇索引，如果二级索引能够实现覆盖查询，可以避免对主键索引的二级查询。

MySQL 只能使用 B 树索引做覆盖索引（其他索引类型不存储索引列的值）。

如果索引覆盖了 WHERE 条件的字段，但不是整个查询涉及的字段，MySQL 5.5 及更早版本会回表获取数据行。可以通过延迟关联（deferred join）进行优化，对能覆盖索引的字段进行子查询，得到主键后在外层查询获取所有列值。

使用索引扫描来做排序

扫描索引本身很快，但如果索引不能覆盖查询所需的列，那就不得不回表查询数据行，因此按索引读取数据的速度通常比顺序全表扫描要慢。

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且各列的排序方向都一样时，MySQL 才能使用索引的结果做排序。需要关联多张表时，只有 ORDER BY 子句的字段全部属于第一张表时才能使用索引做排序。通常 ORDER BY 子句要满足最左前缀要求，除非前导列限定为常数。

1
2
3

SELECT RENTAL_ID, STAFF_ID FROM RENTAL 
WHERE RENTAL_DATA = '2005-05-25'
ORDER BY INVENTORY_ID, CUSTIMER_ID

如上查询中能够利用建立在（RENTAL_DATA，INVENTORY_ID，CUSTIMER_ID）上的索引。

压缩索引

MyISAM 使用前缀压缩减少索引的大小，让更多的索引放到内存中，提高性能。但是对于部分操作可能更慢。因为每个值的前缀压缩以来于前面的值，因此只在正序扫面的时候有利，二分查找和倒序扫面都不行。

冗余和重复索引

重复索引是指在相同的列上按照找相同的顺序创建的相同类型的索引。重复索引影响性能，应该移除。

唯一限制和主键限制都是通过索引实现的，三个都创建相当于在主键列上创建了三个相同的索引。

如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。索引（A，B）可以当作索引（A）使用（对于 B 树）。还有一个情况是将一个索引扩展为（A，ID），因为主键列 ID 已经包含在二级索引中了，因此这种扩展也是冗余的。

索引和锁

索引案例学习

多种条件过滤

常用的列放在前面，如果不限制该列可以用 in() 指定所有值

尽可能将需要做范围查询的列放在索引的后面，以便使用尽可能多的索引列。

避免多个范围条件

当有 A>N and B>m 两个范围条件时可以维护一个冗余列记录 A>n 的真假（如果 N 不会变动）

优化排序

对于分页查询

1	select * from profiles where sex='M' order by rating limit 10000,10;

即使有索引（sex, rating) 依然很慢。可以通过延迟关闭，通过覆盖索引查询到需要的主键再去获取需要的行，减少 MySQL 扫描多余的行。

select * from profiles inner join(
    select id from profiles
        where sex='M' order by rating limit 10000, 10
) as x using(id);

维护索引和表

维护表的三个目的：

找到并修复损坏的表
维护准确的索引统计信息
减少碎片

找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。

可以通过运行CHECK TABLE来检查是否发生了表损坏（因不同存储引擎而异）。

可使用REPAIR TABLE来修复损坏的表，对于不支持的存储引擎，可使用空的ALTER操作来重建表：ALTER TABLE innodb_tbl ENGINE=INNODB。如果损坏的是系统区域或者数据，只能从备份恢复。

MyISAM 的表损坏（corruption）通常是系统崩溃导致的。InnoDB 一般不会出现损坏，如果发生损坏，可能是

硬件问题，如内存或磁盘故障（有可能）
数据库管理员的错误操作，如在 MySQL 外部直接操作数据文件（有可能）
InnoDB 本身缺陷（不太可能）

如果遇到数据损坏，不能只是简单修复，更重要的是找到损坏原因，避免在此发生。

更新索引统计信息

MySQL 的查询优化器通过两个 API 来icol_per_row存储引擎的索引值分布情况

records_in_range()：传入两个边界值，查询范围内有多少记录，MyISAM 返回精确值，InnoDB 返回估算值。
info()：返回各种类型的数据，包括索引的基数（每个键值有多少条记录）

可以通过ANALYZE TABLE来生成统计信息。如果存储引擎提供的扫描行数是不准确的，或者执行计划本身太复杂无法准确获取各个阶段的行数，优化器会使用索引统计信息来估算扫描行数。

每种存储引擎实现索引统计信息的方式不同：

Mymory 引擎不存储索引统计信息
MyISAM 将索引统计信息存储在磁盘中，ANALYZE TABLE 需要全索引扫描计算索引基数，此过程中需要锁表
InnoDB 不再磁盘中存储索引统计信息，通过随机的索引访问进行评估将其存储在内存中。

SHOW INDEX FROM TABLE可查看索引的基数（Cardinality），也可以通过information_schema.statistics 表获取。

InnoDB 会在表首次打开，或执行ANALYZE TABLE，或表的大小发生很大变化（大小变化超过 1/16 或插入 20 亿行）时计算索引的统计信息：随机读取少量索引页面，以此为样本计算。可通过参数设置样本页数量。

InnoDB 打开某些information_schema表，使用 SHOW TABLE STATUS 或 SHOW INDEX，或 MySQL 客户端开启自动补全的时候会触发索引统计信息的更新，可能影响性能。可以关闭innodb_stats_on_metadata参数避免此问题。

减少索引和数据碎片

B 树索引会碎片化，造成查询效率降低。

表的数据存储碎片化有三种类型：

行碎片（Row fragmentation）：数据行被存储在多个片段中，对单行的访问性能下降
行间碎片（Intra-row fragmentation）：逻辑上顺序的页，在磁盘上不是顺序存储的。对全表扫描和聚簇索引扫描之类的操作影响很大
剩余空间碎片（Free space fragmentation）：数据页中有大量空余空间，读取数据时读取大量不需要的数据，造成浪费

MyISAM 中三种都可能发生，InnoDB 中不会出现行碎片。

解决方式

OPTIMIZE TABLE 或导入再导出
MyISAM 可通过排序算法重建索引
InnoDB 可先删除索引再重新创建
空的ALTER操作来重建表：ALTER TABLE innodb_tbl ENGINE=INNODB（消除表的碎片）

总结

三个原则：

单行访问成本很高。如果读取一个块只为了获取一行数据，很浪费，最好使一次块读取能获取尽可能多的行
顺序访问数据很快：顺序 IO 不需要磁盘寻道，对于排序的查询也更快
索引覆盖查询很快：如果一个所以覆盖了查询所需的所有列，那存储引擎就不用回表查询