豆丁微信公众号
君，已阅读到文档的结尾了呢~~
扫扫二维码，随身浏览文档
手机或平板扫扫即可继续访问
举报该文档为侵权文档。
举报该文档含有违规或不良信息。
反馈该文档无法正常浏览。
举报该文档为重复文档。
推荐理由：
将文档分享至：
分享完整地址
文档地址：
粘贴到BBS或博客
flash地址：
支持嵌入FLASH地址的网站使用
html代码：
&embed src='http://www.docin.com/DocinViewer-4.swf' width='100%' height='600' type=application/x-shockwave-flash ALLOWFULLSCREEN='true' ALLOWSCRIPTACCESS='always'&&/embed&
450px*300px480px*400px650px*490px
支持嵌入HTML代码的网站使用
您的内容已经提交成功
您所提交的内容需要审核后才能发布，请您等待！
3秒自动关闭窗口一个表里有200万的数据量大概占多大硬盘啊？
[问题点数：20分，结帖人superren]
一个表里有200万的数据量大概占多大硬盘啊？
[问题点数：20分，结帖人superren]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2005年 总版技术专家分年内排行榜第四
2006年11月 总版技术专家分月排行榜第一2006年3月 总版技术专家分月排行榜第一2006年2月 总版技术专家分月排行榜第一2006年1月 总版技术专家分月排行榜第一2005年12月 总版技术专家分月排行榜第一
匿名用户不能发表回复！|他的最新文章
他的热门文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)MySQL 学习笔记Alpha
本笔记还没有完成，现在还处于 Alpha 版，后续还会持续更新。
如果您觉得这个笔记对您有所帮助，看在D瓜哥码这么多字的辛苦上，请友情支持一下，D瓜哥感激不尽，?
官网及版本库
本文档的版本库托管在 Github 上，另外单独发布。
“地瓜哥”博客网
。D瓜哥的个人博客。欢迎光临，不过，内容很杂乱，请见谅。不见谅，你来打我啊，??
本文档官网
。为了方便阅读，这里展示了处理好的文档。阅读请点击这个网址。
本文档版本库
。欢迎大家发送 PR。
有个需要特别注意的地方，这里重点提出：本文档部分的内容可能会过时！
这个文档绝大部分是参考《高性能 MySQL》写的，可以说就是一个读书笔记。书中反复强调，绝大部分内容是针对 MySQL 5.5 书写的。随着 MySQL 的发展，本文档部分的内容可能会过时。所以，文档如果有错误之处，还请以相应版本的官方文档为准！
1. Schema 设计
良好的逻辑设计和物理设计是高性能的基石。
1.1. 数据类型的选择
更小的通常更好
尽量避免 Null
InnoDB 使用单独的位存储 Null 值，所以对于稀疏数据（多数为 Null，少数非 Null）有很好的空间效率。
MySQL 很多数据类型只是别名，可以用 SHOW CREATE TABLE 查看对应的基本类型。
1.1.1. 整数
整数类型： TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT 、 BIGINT；分别使用 8、16、24、32、64 位存储空间。存储的范围从 -2(N-1) 到 2(N-1)-1。
整数类型有可选的 UNSIGNED，表示不允许负值。
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。
MySQL 可以为整数类型指定宽度，例如 INT(11)，这实际没有意义：它不会限制值的合法范围。对于存储和计算来说， INT(1) 和 INT(20) 是相同的。
1.1.2. 实数
DECIMAL 类型用于存储精确的小数。CPU 不支持对 DECIMAL 的直接计算。
CPU 直接支持原生浮点计算，所以浮点运算明显更快。
MySQL 5.0 和更高版本中的 DECIMAL 类型运行最多 65 个数字。
代码 1. 测试 DECIMAL 类型的最大数字数
DROP TABLE IF EXISTS decimal_
CREATE TABLE decimal_test (
col1 DECIMAL(65, 10),
col2 DECIMAL(66, 10) (1)
1 执行时报错，改为65即可执行。
浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。 FLOAT 使用 4 个字节存储； DOUBLE 占用 8 个字节。
MySQL 使用 DOUBLE 作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL 。
在数据量比较大的时候，可以考虑使用 BIGINT 代替 DECIMAL ，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。
1.1.3. 字符串类型
从 MySQL 4.1 开始，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则。
用于存储可变长字符串，比定长类型更节省空间。
例外：MySQL表使用 ROW_FORMAT=FIXED 创建的话，每一行都会使用定长存储。
VARCHAR 需要使用 1 或 2个额外字节记录字符串的长度：如果列的最大长度小于或者等于255字节，则只使用1个字节表示，否则使用 2 个字节。
VARCHAR 节省了存储空间，所以对性能也有帮助。但是，行变长时，如果页内没有更多的空间可以存储，MyISAM 会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。
每页最多能存多少数据？ 28 = 256， 216 = 65536。数据能否超过65536？如果不能，超过了会怎么样？-- MySQL 中 VARCHAR 类型的最大长度限制为 65535。
上面只是计算出来的结果，我们使用建表语句测试 VARCHAR 类型的最大长度限制。
CREATE TABLE varchar_test
INT PRIMARY KEY AUTO_INCREMENT,
varchar_field VARCHAR(65535)
DEFAULT ''
执行，结果报错：
Column length too big for column 'varchar_field' (max = 21845); use BLOB or TEXT instead
但是，如果把字段长度改为 21845，然后结果就成这样了：
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
VARCHAR 类型的最大长度限制到底是多少呢？
InnoDB 更灵活，可以把过长的 VARCHAR 存储为 BLOB。
变化的阈值是多少？
定长，根据定义分配足够的空间。当存储 CHAR 值时，MySQL 会删除所有的末尾空格。CHAR 值会根据需要采用空格进行填充以方便比较。
CHAR 适合存储很短的字符串，或者所有值都接近同一个长度，比如密码的 MD5 值。
对于经常变更的数据， CHAR 也比 VARCHAR 更好，定长不容易产生碎片。
非常短的列， CHAR 比 VARCHAR 在存储空间上更有效率。
代码 2. 测试数据两端的空格保留情况
# 测试 CHAR
DROP TABLE IF EXISTS char_
CREATE TABLE char_test (char_col CHAR(10));
INSERT INTO char_test VALUES ('string1'), ('
string2'), ('string3
SELECT CONCAT("'", char_col, "'") FROM char_ (1)
# 测试 VARCHAR
DROP TABLE IF EXISTS varchar_
CREATE TABLE varchar_test (varchar_col VARCHAR(10));
INSERT INTO varchar_test VALUES ('string1'), ('
string2'), ('string3
SELECT CONCAT("'", varchar_col, "'") FROM varchar_ (1)
1 注意观察查询结果中字符串两边的空格变化。
数据如何存储取决于存储引擎。
与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY，它们存储的是二进制字符串。二进制字符串存储的是字节码而不是字符。MySQL 填充 BINARY 采用的是 \0 （零字节）而不是空格，在检索时也不会去掉填充值。
二进制比较的优势并不仅仅体现在大小写敏感上。MySQL 比较 BINARY 字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比字符串比较简单很多，所以也更快。
慷慨是不明智的。
1.1.3.1. BLOB和TEXT 类型
BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。
字符串类型： TINYTEXT、 SMALLTEXT、 TEXT、 MEDIUMTEXT、 LONGTEXT 二进制类型： TINYBLOB、 SMALLBLOB、 BLOB、 MEDIUMBLOB、 LONGBLOB
BLOB 是 SMALLBLOB 的同义词； TEXT 是 SMALLTEXT 的同义词。
MySQL 把每个 BLOB 和 TEXT 值当做一个独立的对象处理。InnoDB 会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要 1 ~ 4 个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制，没有排序规则或字符集，而 TEXT 类型有字符集和排序规则。
BLOB 和 TEXT 只对每个列的最前 max_sort_length 字节而不是整个字符串做排序。
MySQL 不能将 BLOB 和 TEXT 列全部长度的字符串进行索引。
1.1.3.2. 使用枚举（ENUM）代替字符串
枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的 .frm 文件中保存 “数字-字符串” 映射关系的 “查找表”。
代码 3. 测试枚举的存储值
DROP TABLE IF EXISTS enum_
CREATE TABLE enum_test (e ENUM ('fish', 'apple', 'dog'));
INSERT INTO enum_test (e) VALUES ('fish'), ('dog'), ('apple'); (1)
SELECT e + 0 FROM enum_
SELECT e FROM enum_test ORDER BY (2)
SELECT e FROM enum_test ORDER BY field(e, 'apple', 'dog', 'fish'); (3)
1 三行数据实际存储为整数，而不是字符串。
2 测试排序性
3 根据定义的字符串排序
如果使用数字作为 ENUM 枚举常量，很容易导致混乱。尽量避免这么做。
枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。一种绕过这种限制的方式是按照需要的顺序来定义枚举列。也可以在查询中使用 FIELD() 函数显式地指定排序顺序，但是会导致 MySQL 无法利用索引消除排序。
枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用 ALTER TABLE。在 MySQL 5.1 中支持只在列表末尾添加元素，而不用重建整个表。
把枚举保存为整数，必须查找才能转换为字符串，有开销。尤其和字符串的列关联查询时，甚至不如字符串关联字符性能好。
通用的设计实践：在“查找表”时采用整数主键而避免采用基于字符串进行关联。
根据 SHOW TABLE STATUS 命令输出结果中 Data_length 列的值，把列转换为 ENUM 可以让表的大小缩小.
1.1.4. 日期和时间类型
MySQL 能存储的最小时间粒度为秒。但，也可以使用微秒级的粒度进行临时运算。
保存大范围的值，从 1001 年到 9999 年，精度为秒。把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关。使用 8 个字节的存储空间。
保存从 1970 年 1 月 1 日午夜以来的秒数，和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间，范围是从 1970 年到 2038 年。
MySQL 4.1 以及更新的版本按照 DATETIME 的方式格式化 TIMESTAMP 的值。TIMESTAMP 的存储格式在各个版本都是一样的。
TIMESTAMP 显示的值也依赖于时区。MySQL 服务器、操作系统以及客户端连接都有时区设置。因此，存储值为 0 的 TIMESTAMP 在美国东部时区显示为 “ 19:00:00”，与格林尼治时间差5个小时。
如果在多个时区存储或访问数据， TIMESTAMP 和 DATETIME 的行为将会很不一样。前者提供的值与时区有关，后者则保留文本表示的日期和时间。
如果在东八区保存为 日17:34:17，在格林尼治显示为多少？
默认情况下，如果插入时没有指定第一个 TIMESTAMP 列的值，MySQL 则设置这个列的值为当前时间。
TIMESTAMP 列默认为 NOT NULL。
通常应该尽量使用 TIMESTAMP ，因为它比 DATETIME 空间效率更高。
可以使用 BIGINT 类型存储微秒级别的时间戳，或者使用 DOUBLE 存储秒之后的小数部分。
1.1.5. 位数据类型
1.1.6. 选择标识符（键列）
更有可能使用标识列与其他值进行比较，或者通过标识列寻找其他列。
选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑 MySQL 对这种类型怎么执行计算和比较。
一旦选定一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括像 UNSIGNED 这样的属性。混用不同数据类型可能导致性能问题，在比较操作时隐式类型转换也可能导致很难发现的错误。
在可以满足值的范围的需求，并且预留为了增长空间的前提下，应该选择最小的数据类型。
整数通常是标识列最好的选择，因为它们很快并且可以使用 AUTO_INCREMENT。
ENUM 和 SET 类型
通常是一个糟糕的选择。 ENUM 和 SET 列适合存储固定信息。
字符串类型
如果可能，应该避免使用字符串作为标识列，因为它们很消耗空间，并且通常比数字类型慢。MyISAM 默认对字符串使用压缩索引，这会导致查询慢很多。
使用完全“随机”的字符串也需要多加注意，例如 MD5()、SHA1()、 UUID()产生的字符串。这些新值会任意分布在很大的空间内，这会导致 INSERT 以及一些 SELECT 语句变得很慢：
插入值会随机地写到索引的不同位置，所以使得 INSERT 语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
SELECT 语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部访问性原理失效。如果真个数据集都一样的“热”，那么缓存任何一部分特别数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。
如果存储 UUID 值，则应该移除 “-” 符号；更好的做法是，使用 UNHEX() 函数转换 UUID 值为 16 字节的数字，并且存储在一个 BINARY(16) 列中。检索时可以通过 `HEX()`函数来格式化为十六进制格式。
UUID 值还是有一定的顺序的。
1.1.7. 特殊类型数据
低于秒级精度的时间戳
IPv4 地址 — INET_ATON() 和 INET_NTOA()。
1.2. MySQL Schema 设计中的陷阱
MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将解码过的列转换成行数据结构的操作代价是非常高的。 MyISAM 定长行结构正好匹配，不需要转换。MyISAM 的变长行结构和 InnoDB 的行结构则总是需要转换。转换的代价依赖于列的数量。
太多的关联
MySQL 限制了每个关联操作最多只能有 61 张表。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在 12 个表以内做关联。
全能的枚举
注意防止过度使用枚举。修改枚举，就需要 ALTER TABLE，在 5.1 和更新版本中，只有在末尾增加值时，不需要 ALTER TABLE。
变相的枚举
枚举列允许在列中存储一组定义值中的单个值，集合（ SET ）列则允许在列中存储一组定义值中的一个或多个值。比如: CREATE TABLE set_test ( is_default SET ('Y', 'N') NOT NULL DEFAULT 'N' ); 真假只有一个，定义为枚举更好。
非此发明的 NULL
建议不要存 NULL。但是不要走极端。当确实需要表示未知值时也不要害怕使用 NULL。处理 NULL 确实不容易，但有时候会比它的替代方案更好。
1.3. 范式和反范式
符合1NF的关系中的每个属性都不可再分。1NF是所有关系型数据库的最基本要求。
范式化通常带来的好处：
范式化的更新操作通常比反范式化要快。
当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
范式化的表通常更小，可以更好地存放在内存里，所以执行操作会更快。
很少有多余的数据意味着检索列表数据时，更少需要 DISTINCT 或者 GROUP BY 语句。
范式化设计的 Schema 的缺点是通常需要关联。
反范式的优缺点
反范式化的 Schema 因为所有数据都在一张表中，可以很好地避免关联。
单独的表也能使用更有效的索引策略。
混用范式化和反范式化
完全的范式化和完全的反范式化 Schema 都是实验室里才有的东西。在实际应用中经常需要混用，可能使用部分范式化的 Schema、缓存表，以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。
从父表冗余一些数据到子表的利益是排序的需要。
缓存衍生值也是有用的。
1.4. 缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据；有时也需要创建一张完全独立的汇总表或缓存表。
缓存表表示存储那些可以比较简单地从 Schema 其他表获取数据的表。 汇总表表示保存的是使用 GROUP BY 语句聚合数据的表。
一个有用的技巧是对缓存表使用不同的存储引擎。例如：主表用 InnoDB，使用 MyISAM 作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文检索。
全文检索还是使用专门的工具，比如 ElasticSearch 更好。
在使用缓存表和汇总表时，必须决定是实时维护数据还是定时重建。看需求。定时重建不仅节省资源，还保持表不会有很多碎片，以及完全顺序组织的索引（这会更加高效）。
当重建汇总表和缓存表时，使用“影子表”来保证数据在操作时依然可用。
DROP TABLE IF EXISTS my_summary_new, my_summary_
CREATE TABLE my_summary_new LIKE my_
-- TODO：执行汇总操作
RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_
1.4.1. 物化视图
物化视图是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。
MySQL 并不原生支持物化视图。
Justin Swanhart 的开源工具 Flexviews， 。
1.4.2. 计数器表
可以利用 CurrentHashMap 分段锁的思想，将对同一个计算器的修改，打散到多个变量上，然后在求和。
DROP TABLE IF EXISTS hit_
CREATE TABLE hit_counter (
slot TINYINT UNSIGNED NOT NULL
PRIMARY KEY,
INT UNSIGNED
)ENGINE = InnoDB;
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_
一个常见需要时每个一段时间开始一个新的计算器（例如，每天一个）。
DROP TABLE IF EXISTS daily_hit_
CREATE TABLE daily_hit_counter (
slot TINYINT UNSIGNED NOT NULL,
INT UNSIGNED
PRIMARY KEY (day, slot)
)ENGINE = InnoDB;
-- 插入数据
INSERT INTO daily_hit_counter (day, slot, cnt)
VALUES (current_date, rand() * 100, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
-- 定期执行：合并所有结果到 0 号槽，并且删除所有其他的槽：
UPDATE daily_hit_counter AS c
INNER JOIN (
min(slot) AS mslot
FROM daily_hit_counter
GROUP BY day
) AS x USING (day)
SET c.cnt = if(c.slot = x.mslot, x.cnt, 0),
= if(c.slot = x.mslot, 0, c.slot);
DELETE FROM daily_hit_counter WHERE slot && 0 AND cnt = 0;
为了提升度查询的速度，可以建立额外索引；这样会增加些查询的负担，虽然写的慢，但是更显著提高了读操作的性能。
1.5. 加快 ALTER TABLE 操作的速度
MySQL 的 ALTER TABLE 操作的性能对于大表来说是个大问题。 MySQL 执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。
一般而言，大部分 ALTER TABLE 操作将导致 MySQL 服务中断。有两个技巧可以避免：
先在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库进行切换；
影子拷贝：用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表的操作交换两张表。还有一些第三方工具可以完成：
Shlomi Noach
不是所有的 ALTER TABLE 操作都会引起表重建。
-- 很慢，N 多次读和 N 多次插入操作
ALTER TABLE film
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;
-- 直接修改 _.frm_ 文件而不设计表数据。操作非常快。
ALTER TABLE film
ALTER COLUMN rental_duration SET DEFAULT 5;
ALTER TABLE 允许使用 ALTER COLUMN、 MODIFY COLUMN 和 CHANGE COLUMN 语句修改列。这三种操作都是不一样的。 有什么不一样呢？
1.5.1. 只修改 .frm 文件
下面的这些操作有可能不需要重建表：
移除一个列的 AUTO_INCREMENT 属性；
增加、移除，或更改 ENUM 和 SET 常量。
基本的技术是为想要的表结构创建一个新的 .frm 文件，然后用它替换掉已经存在的那张表的 .frm 文件。步骤如下：
创建一张有相同结构的空表，并进行所需要的修改；
执行 FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开；
交换 .frm 文件；
执行 UNLOCK TABLES 来释放第2步的读锁。
1.5.2. 快速创建 MyISAM 索引
为了高效地载入数据到 MyISAM 表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引。
ALTER TABLE load_data DISABLE KEYS;
-- 载入数据
ALTER TABLE load_data ENABLE KEYS;
不过，这个办法对唯一索引无效，因为 DISABLE KEYS 只对非唯一索引有效。
现代版本的 InnoDB 中有类似的技巧。
尽量避免过度设计；
使用小而简单的合适数据类型，除非真的需要，否则应尽可能避免使用 NULL；
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列；
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存；
尽量使用整型定义标识列；
避免使用 MySQL 已经遗弃的特性，例如指定浮点数的精度，或者整型的显示宽度；
小心使用 ENUM 和 SET；
最好避免使用 BIT。
2. 创建索引
索引优化应该是对查询性能优化最有效的手段。可以轻松提高几个数量级。
创建一个真正的“最优”的索引经常需要重写查询。
常言道：知其然，知其所以然。学习一门技术的时候，不仅要学怎么使用，还要学习这门技术出现的背景是什么，是为了解决什么问题出现的，技术本身又有什么不足。这样才能更好地理解这门技术。所以，在正式开始讲解索引之前，让我们先看看索引出现的原因以及实现索引时使用的数据结构。
2.1. 追本溯源
计算机科学分为两块，一块是硬件；另外，一块就是软件。我们从这两方面说起。
计算机中，数据最后还是要落地到存储介质中。所以，我们需要了解一下计算机中的存储介质。
1984 年获得了图灵奖者瑞士计算机科学家尼克劳斯·威茨（Niklaus Wirth）提出一个著名公式 “算法 + 数据结构 = 程序”（Algorithm + Data Structures = Programs），简明扼要地说明了算法、数据结构及程序三者之间的关系。程序设计是一种智力劳动，算法与数据结构是编程之道中的“内功心法”，缺乏算法与数据结构素养，编程实践难以深入，也会限制码农利用计算机解决实际问题的能力。
我们先了解一下硬件相关的基础知识。
2.1.1. 存储金字塔
计算机中最重要的存储介质分为几类：硬盘、内存、二级缓存、寄存器。它们之间的对比如下：
图 1. 存储金字塔
从上面的图中，我们可以看出，从下往上，速度从慢到快，制造成本也越来越高。几种有代表性的存储设备的典型访问速度如下：
图 2. 存储访问时间
从这个图中，我们可以很明显的看出：高速缓存的访问速度是主存的 10~100 倍，而主存的访问速度则是硬盘的 1～10W 倍。
大概就是走路和坐飞机的差别了。虽然坐飞机是飞一样的感觉，但是走路还是我们最常用的移动方式。数据存储也一样，对于一台独立的计算机，数据最后还是要落地到磁盘上。所以，我们来看看机械硬盘的结构。
2.1.2. 机械硬盘结构
机械硬盘中的大致结构如下图，类似很多电影和电视剧中的留声机：
图 3. 机械硬盘单个盘面结构轮廓图
机械硬盘中，每一个磁盘盘面的组成结构如下：
图 4. 磁盘上的磁道、扇区和簇
英文名词解释：
Spindle Motor 主轴马达
Permanent Magnent 永久磁铁
Voice Coil 音圈
Spinning Hard Disk 旋转的硬盘
每个机械磁盘都有很多个盘面组成。整个机械磁盘的组成结构如下：
图 5. 磁盘内部结构
单词解释：
spindle 转轴，主轴
track 磁道
sector 扇区
cylinder 磁柱
platter 磁盘
机械臂组件
T-seek 是指将读写磁头移动至正确的磁道上所需要的时间。寻道时间越短，I/O操作越快，目前磁盘的平均寻道时间一般在 3－15ms。
T-rotation 是指盘片旋转将请求数据所在扇区移至读写磁头下方所需要的时间。旋转延迟取决于磁盘转速，通常使用磁盘旋转一周所需时间的 1/2 表示。比如，7200 rpm 的磁盘平均旋转延迟大约为 60 * 1000 / 7200 / 2 = 4.17ms，而转速为 15000 rpm 的磁盘其平均旋转延迟为 2ms。
数据传输时间
T-transfer 是指完成传输所请求的数据所需要的时间，它取决于数据传输率，其值等于数据大小除以数据传输率。目前 IDE/ATA 能达到 133MB/s，SATA II 可达到 300MB/s 的接口数据传输率，数据传输时间通常远小于前两部分消耗时间。简单计算时可忽略。
常见磁盘平均物理寻道时间为：
7200 转/分的 STAT 硬盘平均物理寻道时间是 9ms
10000 转/分的 STAT 硬盘平均物理寻道时间是 6ms
15000 转/分的 STAT 硬盘平均物理寻道时间是 4ms
常见硬盘的旋转延迟时间为：
7200 rpm的磁盘平均旋转延迟大约为 60* = 4.17ms
10000 rpm的磁盘平均旋转延迟大约为 60*/2 = 3ms，
15000 rpm的磁盘其平均旋转延迟约为 60*/2 = 2ms。
了解磁盘读取数据的原理以各种延迟后，我们再来看看顺序读取和随机读取的差别：
图 6. 顺序读取和随机读取
因为机械硬盘的磁头移动至正确的磁道上需要时间，随机读写时，磁头不停的移动，时间都花在了磁头寻道上，导致的就是性能不高。所以，对于机械硬盘来说，连续读写性很好，但随机读写性能很差。具体对比如下：
图 7. 对比在硬盘和内存上的随机读取和顺序读取
2.1.3. 局部性原理与磁盘预读
由于存储介质的特性，硬盘本身存取就比主存慢很多，再加上机械运动耗费，硬盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘 I/O。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。磁盘往往也不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：
当一个数据被用到时，其附近的数据也通常会马上被使用。
接下来，我们了解一下算法相关的背景知识。
2.1.4. 时间复杂度
时间复杂度用来检验某个算法处理一定量的数据要花多长时间。
重要的不是数据量，而是当数据量增加时运算如何增加。
图 8. 时间复杂度变化
红：O(log(n)) 即使在十亿级数量时也很低
粉：O(n2) 快速膨胀
数据量低时，O(1) 和 O(n2)的区别可以忽略不计。比如，你有个算法要处理2000条元素。
O(1) 算法会消耗 1 次运算
O(log(n)) 算法会消耗 7 次运算
O(n) 算法会消耗 2000 次运算
O(n*log(n)) 算法会消耗 14,000 次运算
O(n2) 算法会消耗 4,000,000 次运算
处理 1,000,000 条元素（这对数据库来说也不算大）。
O(1) 算法会消耗 1 次运算
O(log(n)) 算法会消耗 14 次运算
O(n) 算法会消耗 1,000,000 次运算
O(n*log(n)) 算法会消耗 14,000,000 次运算
O(n2) 算法会消耗 1,000,000,000,000 次运算
这里可以明白：
搜索一个好的哈希表会得到 O(1) 复杂度
搜索一个均衡的树会得到 O(log(n)) 复杂度
搜索一个阵列会得到 O(n) 复杂度
最好的排序算法具有 O(n*log(n)) 复杂度
糟糕的排序算法具有 O(n2) 复杂度
2.1.5. 归并排序
合并排序基于这样一个技巧：将 2 个大小为 N/2 的已排序序列合并为一个 N 元素已排序序列仅需要 N 次操作。这个方法叫做合并。
图 9. 归并排序
这个算法有两点特别棒的优势：
可以更改算法，以便于同时使用磁盘空间和少量内存而避免巨量磁盘 I/O。方法是只向内存中加载当前处理的部分。在仅仅100MB的内存缓冲区内排序一个几个GB的表时，这是个很重要的技巧。
可以更改算法，以便于在多处理器/多线程/多服务器上运行。 分布式归并排序时 Hadoop 的关键组件之一。
2.1.6. 二分查找
图 10. 二分查找-最好情况
图 11. 二分查找-最坏的情况
二叉树需要讲数组全部加载到内存中。但是，如果数据量特别大，加载不完，怎么办呢？能否只加载一部分数据呢？
树，这种数据结构就能满足我们的需求，我们可以只把树的上面几级保存到内存中，方便操作。如下图：
树的节点也可以保持有序状态：
图 13. 搜索树
我们来看一下最简单的树结构。
2.1.8. 二叉查找树
在二叉查找树和在有序数组中查找某一个指定元素的对比如下：
图 14. 二叉查找树
二叉查找树中每个节点要保证两点：
比保存在左子树的任何键值都要大
比保存在右子树的任何键值都要小
这个查询的成本是 log2(n)。
上面的是理想状况下的情况。但在极端情况下，二叉查找树的查询成本有可能是 n。例如：
图 15. 最坏情况下的二叉查找树
有没有一种数据结构，能避免这种情况出现呢？
2.1.9. 平衡二叉查找树
图 16. 二叉搜索树对比
平衡二叉搜索树在添加元素时，通过旋转来保证自身的平衡性。
图 17. 平衡二叉搜索树旋转
不仅能左旋，还可以右旋。左右旋转示意图：
图 18. 二叉搜索树旋转
对于查找一个特定值这种树挺好用。还有一个问题：如果查找一个范围内的值呢？比如年龄大于 16，小于 29 的美女呢？这个还可以枚举。如果我要查找身高大于 170cm 的美女，怎么搞？
2.1.10. B+Tree
为了解决高效查找某一个范围内的元素的问题，我们引入一个修订后的树：B+树。这也是目前大部分现代数据库索引使用的数据结构。在一个B+树里：
只有最底层的节点（叶子节点）才保存信息（相关表的行位置）
其它节点只是在搜索中用来指引到正确节点的。
图 19. B+Tree 索引结构
找到了 M 个后续节点，树总共有 N 个节点。对指定节点的搜索成本是 log(N)，跟上一个树相同。但是当你找到这个节点，你得通过后续节点的连接得到 M 个后续节点，这需要 M 次运算。那么这次搜索只消耗了 M+log(N) 次运算，区别于上一个树所用的 N 次运算。
B+树种的 B 不是代表二叉（binary），而是代表平衡（balance），因为 B+树是从最早的平衡二叉树演化而来，但是 B+树不是一个二叉树。
2.1.11. 哈希表
为了构建一个哈希表，你需要定义：
元素的关键字
关键字的哈希函数。关键字计算出来的哈希值给出了元素的位置（叫做哈希桶）。
关键字比较函数。一旦你找到正确的哈希桶，你必须用比较函数在桶内找到你要的元素。
图 20. 哈希表
真正的挑战是找到好的哈希函数，让哈希桶里包含非常少的元素。如果有了好的哈希函数，在哈希表里搜索的时间复杂度是 O(1)。
2.2. InnoDB 逻辑存储结构。
所有数据都被逻辑地存放在一个空间中，称为表空间（tablespace）。表空间由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也被称为块（block）。大致结构如下：
图 21. InnoDB 逻辑存储结构
InnoDB 存储引擎是面向列的（row-oriented），也就是说数据是按行进行存放的。每个页存放的行记录是有硬性定义的，最多允许存放 16KB / 2-200 行的记录，即 7992 行记录。
2.3. 索引基础
索引类似书籍目录。
在MySQL 中，索引是在存储引擎层而不是服务器层实现的。
2.3.1. 索引类型
2.3.1.1. B-Tree 索引
大部分 MySQL 引擎都支持 B-Tree 索引。
NDB 集群存储引擎内部实际使用了 T-Tree 结构； InnoDB 则使用的是 B+Tree。
MyISAM 使用前缀压缩技术是索引更小；
MyISAM 索引通过数据的物理位置引用被索引的行，而 InnoDB 则根据逐渐引用被索引的行。
B-Tree 通常以为这所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。如下图：
图 22. B-Tree 索引结构
B-Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。
图 23. B-Tree 索引结构概图
问：索引的根节点的值变还是不变？
叶子节点比较特别，他们的指针指向的是被索引的数据，而不是其他的节点页。
树的深度和表的大小直接相关。
B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。
CREATE TABLE people (
VARCHAR(50)
first_name VARCHAR(50)
ENUM ('m', 'f') NOT NULL,
KEY (last_name, first_name, dob)
三个列组成的联合索引的结构如下：
图 24. B-Tree 联合索引
注意：索引对多个值进行排序的依据是 CREATE TABLE 语句中定义索引时列的顺序。
B-Tree 索引有效的查询：
全值匹配指的是和索引中的所有列进行匹配。
匹配最左前缀
只使用索引前面的列。
匹配列前缀
也可以只匹配某一列的值的开头部分。
匹配范围值
比如只匹配名字
精确匹配某一列并范围匹配另外一列
精确匹配第一列，范围匹配第二列。
只访问索引的查询
查询只需要访问索引，而无须访问数据行。“覆盖索引”。
是因为索引树种的节点是有序的，除了查找之外，还可以用于查询中的 ORDER BY 操作。一般来说，如果 B-Tree 可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果 ORDER BY 子句满足前面列出的几种查询类型，则这个索引页可以满足对应的排序需求。
B-Tree 索引的限制：
如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
再次提醒：索引列的顺序是多么重要，这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。
B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。
2.3.1.2. 哈希索引
哈希索引（hash index）基于哈希表实现，只有精确匹配查询索引所有列的查询才有效。
在 MySQL 中，只有 Memory 引擎显式支持哈希索引。 Memory 引擎是支持 非唯一哈希索引的。
CREATE TABLE hash_test (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH (fname) (1)
) ENGINE = MEMORY; (2)
1 建立哈希索引的方式
2 指定引擎的方式
如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
哈希索引的限制：
哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
哈希索引只支持等值比较查询，包括 =、 IN()、 &⇒(注意 && 和 &⇒ 是不同的操作)。
访问哈希索引的数据非常快，除非有很多哈希冲突。哈希冲突时使用链表来解决哈希冲突。
如果哈希冲突很多的话，一些所以维护操作的代价也会很高。冲突越多，代价越大。
因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。
除了 Memory 索引外，NDB 集群引擎也支持唯一哈希索引，且在 NDB 集群引擎中作用非常特殊。
InnoDB 引擎有一个特殊的功能叫“自适应哈希索引（adaptive hash index）”。当 InnoDB 注意到某些索引值使用得特别频繁时，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引，这样就让 B-Tree 索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，如有必要，可以关闭。
创建自定义哈希索引
如果存储引擎不支持哈希索引，可以模拟 InnoDB 一样创建哈希索引。思路：在 B-Tree 基础上创建一个伪哈希索引。并不是真正的哈希索引，本质还是使用 B-Tree 进行查找，但它使用哈希值而不是键本身进行查找。需要做的就是在查询的 WHERE 子句中手动指定使用哈希函数。
代码 4. 以 URL 列为例的自定义哈希索引
WHERE url='http://www.diguage.com/';
-- 创建自定义哈希索引
-- 注意：这里需要在 url_crc 字段上创建索引
WHERE url='http://www.diguage.com/'
AND url_crc=CRC32('http://www.diguage.com/');
-- 另外一种方式就是对完整的 URL 字符串做索引，那样会非常慢。
自定义哈希索引的缺陷是需要维护哈希值。可以手动维护，也可以使用触发器实现。示例如下：
代码 5. 基于触发器的自定义哈希索引
DROP TABLE IF EXISTS
CREATE TABLE url (
INT UNSIGNED NOT NULL AUTO_INCREMENT,
VARCHAR(255) NOT NULL,
url_crc INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY (url_crc)
DELIMITER //
-- 插入触发器
CREATE TRIGGER url_crc_ins
BEFORE INSERT ON url
FOR EACH ROW BEGIN
SET new.url_crc = crc32(new.url);
-- 更新触发器
CREATE TRIGGER url_crc_upd
BEFORE UPDATE ON url
FOR EACH ROW BEGIN
SET new.url_crc = crc32(new.url);
INSERT INTO url (url) VALUES ('http:\/\/www.diguage.com/');
UPDATE url
SET url = 'http:\/\/www.diguage.com'
WHERE id = 1;
WHERE url_crc = crc32('http:\/\/www.diguage.com/')
AND url = 'http:\/\/www.diguage.com/'; (3)
1 这个索引必须创建。
2 注意查看查询结果中的 url_crc 字段的值。
3 为避免冲突问题，使用哈希索引查询时，必须在 WHERE 子句中包含常量值。
生日悖论，出现哈希冲突的概率的增长速度可能比想象的要快得多。
CRC32('gnu'),
CRC32('codding');
可以把哈希索引的实现原理对比 HashMap 的代码实现。
采用这种方式，记住不要使用 SHA1() 和 MD5() 作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，更新时也会更慢。 SHA1() 和 MD5() 设计目标是最大限度消除冲突，但这里并不需要这样高的要求。简单哈希函数的冲突在一个可以接受的范围，同时又能够提供更好的性能。
如果数据表非常大， CRC32() 会出现大量的哈希冲突，则可以实现一个简单的 64 位哈希函数。一个简单的办法可以使用 MD5() 函数返回值的一部分来作为自定义函数。性能稍差，但实现简单。
SELECT CONV(RIGHT(MD5('http:\/\/www.diguage.com/'), 16), 16, 10) AS hash64;
2.3.1.3. 空间数据索引（R-Tree）
MyISAM 表支持空间索引，可以用作地理数据存储。空间索引会从所有唯独来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用 MySQL 的 GIS 相关函数如 MBRCONTAINS() 等来维护数据。
开源关系数据库系统中对 GIS 的解决方案做得比较好的是 PostgreSQL 的 PostGIS。
2.3.1.4. 全文索引
全文索引时一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。
全文索引更类似于搜索引擎做的事情，而不是简单的 WHERE 条件匹配。
全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件查询。
2.3.1.5. 分形树索引（fractal tree index）
这是一类比较新开发的数据结构，既有 B-Tree 的很多优点，也避免了 B-Tree 的一些缺点。
2.4. 索引的优点
索引可以快速定位到表的指定位置；可以用作 ORDER BY 和 GROUP BY 操作；某些查询只使用索引就能够完成全部查询。
索引的三个有点：
索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机 I/O 变为顺序 I/O 。
关于索引推荐阅读 Tapio Lahdenmaki 和 Michael Leach 编写的 ，该书详细介绍了如何计算索引的成本和作用、如何评估查询速度、如何分析索引维护的代价和其带来的好处等。
Tapio Lahdenmaki 和 Michael Leach 在书中介绍了如何评价一个索引是否适合某个查询的“三星系统”（three-star system）：
索引将相关的记录放到一起则获得一星；
如果索引中的数据顺序和查找中的排列顺序一致则获得二星；
如果索引中的列包含了查询中需要的全部列则获得“三星”。
索引不总是最好的工具。只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这时就需要分区技术。
如果表的数量特别多，可以建立一个元数据信息表，用于查询需要用到的某些特性。例如
对于 TB 级别的数据，定位单条记录的意义不大，所以需要经常会使用块级别元数据技术来替代索引。
2.5. 高性能的索引策略
正确地创建和使用索引时实现高性能查询的基础。
2.5.1. 独立的列
“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
应该养成简化 WHERE 条件的习惯，始终将索引列单独放在比较符合的一侧。
2.5.2. 前缀索引和索引选择性
当索引很长的字符列，会让索引变得大且慢，一个策略是前面提到过的模拟哈希索引。
通常可以索引开始的部分字符，可以大大节约索引空间，从而提高索引效率。但这样会降低索引的选择性。
索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于 BLOB、 TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以是的前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。
为了觉得前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。
代码 6. 使用 SQL 语句来查看前缀长度的选择性
COUNT(DISTINCT LEFT(last_name, 3)) / COUNT(*) AS nam3,
COUNT(DISTINCT LEFT(last_name, 4)) / COUNT(*) AS nam4,
COUNT(DISTINCT LEFT(last_name, 5)) / COUNT(*) AS nam5,
COUNT(DISTINCT LEFT(last_name, 6)) / COUNT(*) AS nam6,
COUNT(DISTINCT LEFT(last_name, 7)) / COUNT(*) AS nam7
前缀索引时一种能使索引更小、更快的有效办法；也有缺点，MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖索引。
一个常见的场景是针对很长的十六进制唯一 ID 使用前缀索引。例如 SessionID。
有时后缀索引(suffix index)也有用途。 MySQL 原生不支持反向索引，但可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。
2.5.3. 多列索引
一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
在多个列上山里独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。 MySQL 5.0 和更新版本引入了一种“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
索引合并测试有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：
当出现服务器对多个索引做相交操作时（通常有多个 AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要多多个索引做联合操作时（通常有多个 OR 条件），通常需要耗费大量 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些计算的“查询成本”中，优化器只关心随机页面读取。这使得查询的成本被“低估”。
如果在 EXPLAIN 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。
2.5.4. 选择合适的索引列顺序
最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。本节内容适用于 B-Tree 索引。
在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，以此类推。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDER BY、 GROUP BY 和 DISTINCT 等子句的查询需求。
在 Lahdenmaki 和 Leach 的系统中，列顺序也决定了一个索引是否能够成为一个真正的“三星索引”。
对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。通常不如避免随机 IO 和排序那么重要。
当不需要考虑排序和分组时，将选择性最高的列放到索引最前列通常是很好的。
这就是在思考建立联合索引时的一个指导原则！选择方法如下：
sum(staff_id = 2),
sum(customer_id = 584)
1 这里使用了 MySQL 官方提供的 sakila 示例数据库。
根据执行结果，结合上面提到的指导原则，应该讲结果值更小的列放在前面。
这里有个地方需要注意：上面查询的结构非常依赖于选定的具体值。对其他查询可能就不适用。
经验法则考虑的是全局基数和选择性，而不是某个具体查询。
COUNT(DISTINCT staff_id) / COUNT(*)
AS staff_id_selectivity,
COUNT(DISTINCT customer_id) / COUNT(*) AS customer_id_selectivity,
根据执行结构，选择数字比较高的列作为索引列的第一列。
性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。
可能需要根据那些运行效率最高的查询来调整索引列的顺序。
尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了 WHERE 子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能早晨非常大的影响。
2.5.5. 聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一结构中保存了 B-Tree 索引和数据行。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因此，一个表只有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。
图 25. 聚簇索引的数据分布
InnoDB 通过主键聚集数据。如果没有定义主键， InnoDB 会选择一个唯一的非空索引代替；如果没有这样的索引， InnoDB 会隐式定义哥主键来作为聚簇索引。
聚集的数据的一些重要的优点：
可以把相关数据保存在一起。例如，根据用户ID来聚集数据，可以顺序读取某个用户的全部邮件。
数据访问更快。聚簇索引将索引和数据保存在同一个 B-Tree 中，因此从聚簇索引中获取数据通常比非聚簇索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚集数据的一些缺点：
聚簇数据最大限度提高了 I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表。
更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。页分裂会导致表占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引（非聚簇索引）可能给想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。
二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。二级索引要两次 B-Tree 查找而不是一次，对于 InnoDB，自适应哈希索引能够减少这样的重复工作。为什么能减少？
2.5.5.1. InnoDB 和 MyISAM 的数据分布对比
为了方便讲解，分别使用 InnoDB 和 MyISAM 引擎建立结构如下的表，并按主键随机顺序插入主键值在 1 ~ 10000 的10000条数据：
CREATE TABLE layout_test (
col1 INT NOT NULL,
col2 INT NOT NULL,
PRIMARY KEY (col1),
KEY (col2)
1 请在建立的时候指定引擎类型
MyISAM 的数据分布
MyISAM 按照数据插入的顺序存储在磁盘上。如图：
图 26. MyISAM 表 layout_test 的数据分布
在行旁边显示了行号，从 0 开始递增。因为行是定长的，所以 MyISAM 可以从表的开头跳过所需要的字节找到需要的行。（MyISAM 是根据定长还是变长的行使用不同策略来确定行号。）
图 27. MyISAM 表 layout_test 的主键索引分布
这里有两点需要注意：
主键叶子节点存放的指向数据行的指针。
主键和其他索引没有什么区别。
图 28. MyISAM 表 layout_test 的主键索引分布
图 29. MyISAM 表 layout_test 的二级索引分布
事实上， MyISAM 中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为 PRIMARY 的唯一非空索引。
InnoDB 的数据分布
InnoDB 支持聚簇索引，所以使用不同的方式存储同样的数据。
图 30. InnoDB 表 layout_test 的主键索引分布
注意：该图显示了整个表，而不是只有索引。在 InnoDB 中，聚簇索引“就是”表。
聚簇索引的每一个叶子节点都包含了主键值、事务 ID、用于事务和 MVCC 的回滚指针以及所有的剩余列。如果主键是一个列前缀索引， InnoDB 也会包含完整的主键列和剩下的其他列。
图 31. InnoDB 表 layout_test 的主键索引分布
前文说 InnoDB 把 BLOB 类型的会放在单独区域，如果主键是 BLOB 类型的列前缀索引，该如何存储？
InnoDB 的二级索引和聚簇索引很不相同。 InnoDB 二级索引的叶子节点存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是， InnoDB 在移动行时无须更新二级索引中的这个“指针”。
对比来看， MyISAM 在更新时，如果出现行移动，则要更新所有的二级索引的行指针。
图 32. InnoDB 表 layout_test 的二级索引分布
注意两点：
每个叶子节点都包含了索引列，紧接着是主键索引。
非叶子节点包含了索引列和一个指向下级节点的指针。这对聚簇索引和二级索引都是用。
图 33. 聚簇和非聚簇表对比
2.5.5.2. 在 InnoDB 表中按主键顺序插入行
保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于 I/O 密集型的应用。随机主键使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
图 34. 向聚簇索引插入顺序的索引值
因为主键的值时顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录都会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满。
图 35. 向聚簇索引插入无序的索引值
因为主键值不一定比之前插入的大，所以 InnoDB 无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置 — 通常是已有数据的中间位置 — 并且分开空间。这会增加很多额外的工作，并导致数据分布不够优化。缺点：
写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中， InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机 I/O。
因为写入是乱序的， InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。 为什么最少是三个页？
由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。
在把随机值载入到聚簇索引以后，也许需要做一次 OPTIMIZE TABLE 来重建表并优化页的填充。
.顺序主键也会造成更坏的结果
对于高并发工作负载，在 InnoDB 中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。并发插入可能导致间隙锁竞争。另一个热点可能是 `AUTO_INCREMENT` 锁机制。
2.5.6. 覆盖索引
设计优秀的索索引应该考虑到整个查询，而不单单是 WHERE 条件部分。
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，则称之为“覆盖索引”。
覆盖索引时非常有用的工具，能够极大地提高性能。优点如下：
索引条目通常远小于数据行大小，所以如果只需要读取索引，则 MySQL 就会极大地减少数据访问量。
因为索引时按照列值顺序存储的（至少在单个页内是如此），所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。
一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题。
由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
不是所有的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。也不是所有的存储引擎都支持覆盖索引，比如 Memory 不支持。
索引覆盖查询还有很多陷阱可能会导致无法实现优化。 MySQL 查询优化器会在执行查询前判断是否有一个索引能进行覆盖。
这里思考一下，什么样的查询才是覆盖索引？需要满足什么条件？从 SQL 语句的组成来看。
从下面的查询来看：
FROM products
WHERE actor = 'SEAN CARREY'
AND title LIKE '%APOLLO%';
这里索引无法覆盖该查询，有两个原因：
没有任何索引能够覆盖这个查询。查询从表中选择了所有的行，而没有任何索引覆盖了所有的列。
MySQL 不能在索引中执行 LIKE 操作。这是底层存储引擎 API 的限制。MySQL 能在索引中做最左前缀匹配的 LIKE 比较。
可以重新查询并巧妙地设计索引，先将索引扩展至覆盖三个数据列（actor、title、prod_id），然后如下方式重写查询：
FROM products
JOIN (SELECT prod_id
FROM products
WHERE actor = 'SEAN CARREY'
AND title LIKE '%APOLLO%') AS t1
ON t1.prod_id = products.prod_
这种方式叫做延迟关联（deferred join），因为延迟了对列的访问。在查询的第一阶段 MySQL 可以使用覆盖索引，在 FROM 子句的子查询中找到匹配的 prod_id，然后根据这些 prod_id 值在外层查询匹配获取需要的所有列值。
这种优化方式在数据量很大，符合条件的数据很小时，优化效果明显；在数据量很大，符合条件的数据很大时，效果不明显，因为大部分时间是花在读取和发送数据了；如果数据量很小，子查询反而会拖慢查询。
以前觉得写 SQL 语句就是个技术活，现在来看，它还是一门艺术，一门需要思考的艺术！
这里还有一点需要特别点出： InnoDB 的二级索引中还存放的是指向数据行的主键 ID。所以，除了索引列外，还有主键 ID 也可以在覆盖索引中使用。
上面提到限制主要是因为存储引擎 API 不允许 MySQL 将过滤条件传到存储引擎层导致的。MySQL 5.6 中包含了在存储引擎 API 上所做的一个重要的改进，其被称为“索引条件推送”（index condition pushdown），可以大大改善现在的查询执行方式，如此一来上面介绍的很多技巧也就不再需要了。
2.5.7. 使用索引扫描来做排序
MySQL 有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。
MySQL 可以使用同一个索引既满足排序，又用于查找行。设计索引时应该尽可能地同时满足这两种任务。
只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时， MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。 ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则， MySQL 都需要执行排序操作，而无法利用索引排序。
如果需要安装不同方向做排序，一个技巧是存储该列值的反转串或者相反数。
还有一种情况下 ORDER BY 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。可以在 WHERE 子句或者 JOIN 子句中对这些列指定了常量，就可以 “弥补” 索引的不足。
使用索引做排序的一个最重要的用法是当查询同时有 ORDER BY 和 LIMIT 子句的时候。
2.5.8. 压缩（前缀压缩）索引
MyISAM 使用前缀压缩来减少索引的大小，可让更多索引放入内存中，某些情况可以极大提高性能。默认只压缩字符串，通过参数设置可以对整数做压缩。
MyISAM 压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。
压缩块使用更少的空间，代价是某些操作可能更慢。 MyISAM 查找时无法再索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描，就惨了！
对于 CPU 密集型应用，压缩使得 MyISAM 在索引查找上要慢好几倍。
可以在 CREATE TABLE 语句汇总指定 PACK_KEYS 参数来控制索引压缩的方式。
2.5.9. 冗余和重复索引
重复索引指在相同的列上按照相同的顺序创建的相同类型的索引。
MySQL 的唯一限制和主键限制都是通过索引实现的。
冗余索引和重复索引有一些不同。如果创建了索引（A，B），再创建索引（A）就是冗余索引。
还有一种情况是将一个索引扩展为（A，ID），其中 ID 是主键，对于 InnoDB 来说主键列已经包含在二级索引中，这也是冗余。
大多数情况下都不需要冗余索引，应该尽快扩展已有的索引而不是创建新索引。但有时处于性能的考虑需要冗余，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
有时为了覆盖查询，也需要扩展索引。
一般来说，增加新索引将会对导致 INSERT、 UPDATE、 DELETE 等操作的速度变慢，特别是当新增加索引后导致达到了内存瓶颈的时候。
解决冗余索引和重复索引的方法很简单，删除这些索引即可，但首先要做的是找出这样的索引。
在决定哪些索引可以被删除的时候要非常小心。要考虑查询、排序等。可以使用 Percona 工具箱中的 pt-upgrade 工具来检查计划中的索引变更。
2.5.10. 未使用的索引
除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，完全是累赘，建议考虑删除。
最简单有效的办法是在 Percona Server 或者 MariaDB 中先打开 userstates 变量，让服务器运行一段时间，再通过查询 INFORMATION_SCHEMA.INDEX_STATISTICS 就能查到每个索引的使用频率。
Percona Toolkit 的 pt-index-usage 读取查询日志，并对日志中的查询进行 EXPLAIN 查找，然后打印出关于索引和查询的报告。
2.5.11. 索引和锁
索引可以让查询锁定更少的行。锁定超过需要的行会增加锁争用并减少并发性。
InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。
InnoDB 在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。
2.6. 索引案例学习
第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。
2.6.1. 支持多种过滤条件
需要看看哪些列拥有很多不同的取值，哪些列在 WHERE 子句中出现得最频繁。有更多不同值的列上创建索引的选择性会更好。
2.7. 维护索引和表
维护表有三个主要目的：
找到并修复损坏的表。
维护准确的索引统计信息。
减少碎片。
2.7.1. 找到并修复损坏的表
损坏的索引导会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。
CHECK TABLE 通常能够找出大多数表和索引的错误。
REPAIR TABLE 来修复损坏的表。
如果存储引擎不支持，也可以通过一个不做任何操作的 ALTER 操作来重建表。
如果 InnoDB 引擎的表出现了损坏，那一定是发生了严重的错误，需要立刻调查一下原因。
如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复，否则很有可能还会不断损坏。
2.7.2. 更新索引统计信息
records_in_range() 通过向存储引擎传入两个边界值获取在这个范围大概有多少记录。
info() 返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。
MySQL 优化器使用的是基于成功的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果信息不准确，优化器可能做出错误的决定。
ANALYZE TABLE 来重新生成统计信息。
SHOW INDEX FROM 来查看索引的基数（Cardinality）。
InnoDB 的统计信息值得深入研究。 InnoDB 引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。
InnoDB 会在表首次打开，或者执行 ANALYZE TABLE，抑或表的大小发生非常大的变化时计算索引的统计信息。
2.7.3. 减少索引和数据的碎片
B-Tree 索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。
根据设计，B-Tree 需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多倍；对于索引覆盖扫描这一点更加明显。
如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。
数据存储的碎片化有三种类型：
行碎片（Row fragementation）
指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
行间碎片（Intra-row fragementation）
指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。对全表扫描或聚簇索引扫描之类的操作有很大的影响。
剩余空间碎片（Free space fragementation）
指数据页中有大量的空余空间。会导致服务器读取大量不需要的数据，从而造成浪费。
对于 MyISAM 表，这三类碎片化都可能发生。但 InnoDB 不会出现短小的行碎片；InnoDB 会移动短小的行并重写到一个片段中。
OPTIMIZE TABLE 或者导出再导入的方式重新整理数据。
对不支持 OPTIMIZE TABLE 的存储引擎，可以通过一个不做任何操作的 ALTER TABLE 操作来重建表。只需要将表的存储引擎修改为当前的引擎即可：
ALTER TABLE &table& ENGINE=&engine&;
在选择索引和编写利用这些索引的查询时，有三个原则始终需要记住：
单行访问时很慢的。最好读取的块中能包含尽可能多所需要的行。
按顺序访问范围数据是很快的。
顺序 I/O 不需要多次磁盘寻道，所以比随机 I/O 要快很多
如果服务器能够按需要顺序读取数据，那么久不再需要额外的排序操作，并且 GROUP BY 查询也无须再做排序和将行按组进行聚合计算了。
索引覆盖查询很快。
这与上完提到的
是一致的。
3. 查询性能优化
查询优化、索引优化、库表结构优化需要齐头并进，一个不落。
3.1. 为什么查询速度会慢？
真正重要的是响应时间。
查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向地城存储引擎检索数据的调用，这些调用需要在内存操作、CPU 操作和内存不足时导致的 I/O 操作上消耗时间。
了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。
3.2. 慢查询基础：优化数据访问
查询性能低下最基本的原因是访问的数据太多。
对于低效的查询，下面两步分析总是有效的：
确认应用程序是否在检索大量超过需要的数据。访问了太多的行，有时也可能访问太多的列。
确认 MySQL 服务器层是否在分析大量超过需要的数据行。
查询大量不需要的数据的典型案例：
查询不需要的记录
最简单有效的解决方法就是在这样的查询后面加上 LIMIT。
多表关联时返回全部列
总是取出全部列
每次看到 SELECT * 时都需要用怀疑的眼光审视，是不是真的需要返回全部的列！
重复查询相同的数据
最简单的衡量查询开销的三个指标如下：
扫描的行数
返回的行数
这三个指标都会记录到 MySQL 的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。
可以写一个脚本来分析 MySQL 的日志，进而找出比较慢的查询。
响应时间只是表面上的一个值；但，响应时间仍然是最重要的指标。
响应时间是两部分之和：服务时间和排队时间。
指数据库处理这个查询真正花了多长时间。
指服务器因为等待某些资源而没有真正执行查询的时间—​可能是等 I/O 操作完成，也可能是等待行锁等待。
一般最常见和重要的等待是 I/O 和锁等待。
一书讲述了一种估算查询的响应时间方法：快速上限估计。概括地说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机 I/O，再用其乘以在具体硬件条件下一次 I/O 的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。
扫描的行数和返回的行数
并不是所有的行的访问代价是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。
理想情况下扫描的行数和返回的行数应该是相同的。扫描的行数对返回的行数比率通常很小，一般在 1:1 和 10:1 之间。
扫描的行数和访问类型
在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。
在 EXPLAIN 语句中的 type 列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。
如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引让 MySQL 以最高效、扫描行数最少的方式找到需要的记录。
FROM film_actor
WHERE film_id = 1;
mysql& EXPLAIN SELECT * FROM film_actor WHERE film_id = 1 \G (1)
*************************** 1. row ***************************
select_type: SIMPLE
table: film_actor
partitions: NULL
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ALTER TABLE film_actor
DROP FOREIGN KEY fk_film_actor_
ALTER TABLE film_actor
DROP KEY idx_fk_film_
FROM film_actor
WHERE film_id = 1;
mysql& EXPLAIN SELECT * FROM film_actor WHERE film_id = 1 \G (2)
*************************** 1. row ***************************
select_type: SIMPLE
table: film_actor
partitions: NULL
possible_keys: NULL
key_len: NULL
rows: 5462
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
1 从下面的结果也能看出，MySQL 在索引 idx_fk_film_id 上使用了 ref 访问类型来执行 SQL。
2 删除索引后，访问类型变成了一个全表扫描（ ALL ），现在 MySQL 预估需要扫描 5073 条记录来完成这个查询。 Using where 表示 MySQL 将通过 WHERE 条件来筛选存储引擎返回的记录。
一般 MySQL 能够使用如下三种方式应用 WHERE 条件，从好到坏以此为：
在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
使用索引覆盖扫描（在 Extra 列中出现了 Using index）来返回记录，直接从索引中过滤掉不需要的记录并返回命中的结果。这是在 MySQL 服务器层完成的，但无须再回表查询记录。
从数据表中返回数据，然后过滤掉不满足条件的记录（在 Extra 列中出现 Using Where）。这在 MySQL 服务器层完成，MySQL 需要先从数据表读出记录然后过滤。
好的索引可以让查询使用合适的访问类型，尽可能地值扫描需要的数据行。但也不是说增加索引就能让扫描的行数等于返回的行数。例如 COUNT(*) 查询。
不幸的是，MySQL 不会告诉我们生成结果实际上需要扫描多少行数据，而只会告诉我们生成结果时一共扫描了多少行数据。扫描的行数中的大部分都很可能是被 WHERE 条件过滤掉的，对最终的结果集并没有贡献。理解一个查询需要扫描多少行和实际需要使用的行数需要先去理解这个查询背后的逻辑和思想。
如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
改变库表结构。例如使用单独的汇总表。
重写这个复杂的查询，让 MySQL 优化器能够以更优化的方式执行这个查询。
3.3. 重构查询的方式
在优化有问题的查询时，目标应该是找到一个更优的方法获取实际需要的结果—​而不一定总是需要从 MySQL 获取一模一样的结果集。
3.3.1. 一个复杂查询还是多个简单查询
设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。
MySQL 从设计上让连接和断开连接都 很轻量级，在返回一个小的查询结果方面很高效。
MySQL 内部每秒能够扫描内存中上百万行数据。
在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。
3.3.2. 切分查询
有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。例如删除旧的数据。
这个原则不仅仅适用于数据库，在很多地方都适用。
3.3.3. 分解关联查询
可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
用分解关联查询的方式重构查询有如下的优势：
让缓存的效率更高。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。
可以减少冗余记录的查询。
这样做相当于在应用中实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多。
3.4. 查询执行的基础
当希望 MySQL 能够以更高的性能运行查询时，最好的办法就是弄清楚 MySQL 是如何优化和执行查询的。
图 36. 查询执行路径
当我们向 MySQL 发送一个请求的时候， MySQL 执行如下操作：
客户端发送一条查询给服务器。
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
服务器进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
将结果返回给客户端。
3.4.1. MySQL 客户端/服务器通信协议
一般来说，不需要去理解 MySQL 通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL 客户端和服务器之间的通信心意是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切分成小块独立来发送。
通信简单，也有很多限制。一个明显的限制是，这意味着没法进行流量控制。一旦一段开始发送消息，另一段要接收完整个消息才能响应它。
客户端用一个独立的数据包将查询传给服务器。
相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。这也是在必要的时候一定要在查询中加上 LIMIT 限制的原因。
当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是 MySQL 在向客户端推送数据的过程。客户端没法让服务器停下来。
多数连接 MySQL 的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。
当使用多数连接 MySQL 的库函数从 MySQL 获取数据时，其结果看起来都像是从 MySQL 服务器获取数据，而实际上都是从这个库函数的缓存获取数据。
这里的意思是，处理 ResultSet 时，数据已经从 MySQL 服务器上读取过来数据，然后直接从 ResultSet 中取数据。
对于一个 MySQL 连接，或者说一个线程，任何时刻都有一个状态，该状态表示了 MySQL 当前正在做什么。有很多方式查看当前的状态，最简单的是使用 SHOW FULL PROCESSLIST 命令。
线程正在等待客户端发送新的请求。
线程正在执行查询或者正在将结果发送给客户端。
在 MySQL 服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如 InnoDB 的行锁，并不会体现在线程状态中。
Analyzing and statistics
线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table [on disk]
线程正在执行查询，并且将结果集都复制到一个临时表中，这种状态一般要么是在做 GROUP BY 操作，要么是文件排序操作，或者是 UNION 操作。如果这个状态后面还有 on disk 标记，那表示 MySQL 正在将一个内存临时表放到磁盘上。
Sorting result
线程正在对结果集进行排序。
Sending data
这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。
3.4.2. 查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据。检查是通过对大小写敏感的哈希查找实现的。不匹配则进行下一阶段处理。
命中缓存，那么在返回结果前 MySQL 会检查一次用户权限。如果没有问题，则直接从缓存中拿到结果返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会执行。
3.4.3. 查询优化处理
查询的生命周期的下一步是将一个 SQL 转换成一个执行计划，MySQL 再按照这个执行计划和存储引擎进行交互。这包含多个子阶段： 解析 SQL、预处理、优化 SQL 执行计划。
3.4.3.1. 语法解析器和预处理
首先，MySQL 通过关键字将 SQL 语句进行解析，并生成一课对应的“解析树”。MySQL 解析器将使用 MySQL 语法规则验证和解析查询。
预处理器则根据一些 MySQL 规则进一步检查解析树是否合法。
下一步预处理器会验证权限。通常很快，除非有非常多的权限配置。
3.4.3.2. 查询优化器
一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL 使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。可以通过查询当前会话的 Last_query_cost 的值来得知 MySQL 计算的当前查询的成本。
SELECT SQL_NO_CACHE count(*)
FROM film_
SHOW STATUS LIKE 'Last_query_cost'; (1)
1 在不同机器上，结果可能不一样。
这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。
优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘 I/O。
导致 MySQL 优化器选择错误的执行计划的原因：
统计信息不准确。 MySQL 依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。
执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精确，优化器给出的执行计划也可能不是最优的。
MySQL 的最优可能和你想的最优不一样。由此可见，根据执行成本选择执行计划并不是完美的模型。
MySQL 从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
MySQL 也并不是任何时候都是基于成本的优化。例如全文检索。
MySQL 不会考虑不受其控制的操作的成本。
优化器有时无法去估算所有可能的执行计划。
MySQL 的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。静态优化不依赖于特别的数值。静态优化在第一次完成后就一直有效，即使使用不同的参数值重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。
动态优化则和查询的上下文有关，也可能和很多其他因素有关，需要在每次查询时都重新评估，可以认为是“运行时优化”。有时甚至在查询的执行过程中也会重新优化。
MySQL 能够处理的优化类型：
重新定义关联表的顺序
数据表的关联并不总是安装在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能。
将外连接转化成内连接
并不是所有的 OUTER JOIN 语句都必须以外连接的方式执行。
使用等价变换规则
MySQL 可以使用一些等价变换来简化并规范表达式。可以科比能够一些比较，移除一些恒成立和一些恒不成立的判断等等。
优化 COUNT()、MIN() 和 MAX()
索引和列是否可为空通常可以帮助 MySQL 优化这类表达式。例如：从 B-Tree 索引中取最大值或者最小值；没有任何 WHERE 条件的 COUNT(*) 查询。
预估并转化为常数表达式
当 MySQL 检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
让人惊讶的是，在优化阶段，有时候甚至一个查询也能够转化为一个常数。例如：在索引列上执行 MIN()；甚至主键或者唯一键查找语句。
f.film_id,
fa.actor_id
FROM film f
INNER JOIN film_actor fa USING (film_id)
WHERE f.film_id = 1 \G
*************************** 1. row ***************************
select_type: SIMPLE
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
select_type: SIMPLE
partitions: NULL
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
filtered: 100.00
Extra: Using index
MySQL 分两步来执行查询。第一步从 film 表找到需要的行。因为在 film_id 字段上有主键索引，所以 MySQL 优化器知道这只会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息知道将返回多少行数据。因为优化器已经明确知道有多少个值（ WHERE 条件中的值）需要做索引查询，所以这里的表访问类型是 const。 第二步，MySQL 将第一步中返回的 film_id 列当做一个已知取值的列来处理。因为优化器清楚再第一步执行完成后，该值就会是明确的了。注意到正如第一步中一样，使用 film_actor 字段对表的访问类型也是 const。P212
另一种会看到常数条件的情况是通过等式将常数值从一个表传给另一个表，这可以通过 WHERE、USING 或者 ON 语句来限制某列值为常数。
覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候， MySQL 就可以使用索引返回需要的数据，而无须查询对应的数据行。
子查询优化
MySQL 在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问。
提前终止查询
在发现已经满足查询需求的时候，MySQL 总是能够立刻终止查询。例如：LIMIT 子句；再例如，发现一个不成立的条件。
SELECT film_id
WHERE film_id = -1 \G
*************************** 1. row ***************************
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key_len: NULL
rows: NULL
filtered: NULL
Extra: no matching row in const table
从这个例子看到，查询在优化阶段就已经终止。
如果两个列的值通过等式关联，那么 MySQL 能够把其中一个列的 WHERE 条件传递到另一列上。
列表 IN() 的比较
在很多数据库系统中，IN() 完全等同于多个 OR 条件的子句，因为这两者是完全等价的。而 MySQL 将 IN() 列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是 O(log n) 复杂度；转化成 OR 查询则为 O(n)。
不要自以为比优化器更聪明！
3.4.3.3. 数据和索引的统计信息
不同的存储引擎可能会存储不同的统计信息（也可以按照不同的格式存储统计信息）。
MySQL 查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等等
3.4.3.4. MySQL 如何执行关联查询
MySQL 认为任何一个查询都是一次“关联” — 并不仅仅是一个查询需要到两个表匹配才叫关联，所以在 MySQL 中，每一个查询，每一个片段（包括子查询，甚至基于单表的 SELECT）都可能使关联。
对于 UNION 查询，MySQL 先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成 UNION 查询。
MySQL 关联执行的策略：MySQL 对任何关联都执行嵌套循环关联操作，即 MySQL 先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，知道找到所有表中匹配的行位置。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL 会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL 返回到上一层次关联表，看是否能够找到更多的匹配记录，以此类推迭代执行。可以使用如下代码来解释：
-- 内关联查询 ----------------------------------------------------
tbl1.col1,
INNER JOIN tbl2 USING (col3)
WHERE tbl1.col1 IN (5, 6);
-- 用伪代码来解释 MySQL 关联执行的策略则是如下：
outer_iter = iteratro over tbl1 WHERE col1 IN (5, 6)
outer_row = outer_iter.next
while outer_row
inner_iter = iteratro over tbl2 WHERE col3 = outer_row.col3
= inner_iter.next
while inner_row
output [outer_row.col1, inner_row.col2]
inner_row = inner_iter.next
outer_row = outer_iter.next
-- 左外关联查询 --------------------------------------------------
tbl1.col1,
LEFT OUTER JOIN tbl2 USING (col3)
WHERE tbl1.col1 IN (5, 6);
-- 用伪代码来解释 MySQL 关联执行的策略则是如下：
outer_iter = iteratro over tbl1 WHERE col1 IN (5, 6)
outer_row = outer_iter.next
while outer_row
inner_iter = iteratro over tbl2 WHERE col3 = outer_row.col3
= inner_iter.next
if inner_row
while inner_row
output [outer_row.col1, inner_row.col2]
inner_row = inner_iter.next
output [outer_row.col1, NULL]
outer_row = outer_iter.next
可视化查询执行计划的方法是根据优化器执行的路径绘制出对应的“泳道图”。
图 37. 关联查询泳道图
从本质上来说，MySQL 对所有的类型的查询都以同样的方式运行。例如：子查询先放到一个临时表；UNION 也用类似的临时表。
在 MySQL 5.6 和 MariaDB 中有了重大改变，这两个版本都引入了更加复杂的执行计划。
3.4.3.5. 执行计划
MySQL 生成查询的一棵指令树，然后通过存储引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。
如果读某个查询执行 EXPLAIN EXTENDED 后，再执行 SHOW WARNINGS，就可以看到重构出的查询。
3.4.3.6. 关联查询优化器
MySQL 优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。关联查询优化器通过评估不同关联顺序时的成本来选择一个代价最小的关联顺序。
film.film_id,
film.title,
film.release_year,
actor.actor_id,
actor.first_name,
actor.last_name
INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id) \G
*************************** 1