《高性能 MySQL》笔记
《高性能 MySQL》笔记
部分章节内容更偏向于 DBA 的工作，在实际的开发工作中相关性较少，直接略过。
第一章 MySQL 架构与历史
MySQL 逻辑架构
MySQL 逻辑架构分为三层：
- 连接层 - 连接管理、认证管理
- 核心服务层 - 缓存、解析、优化、执行
- 存储引擎层 - 数据实际读写
并发控制
解决并发问题的最常见方式是加锁。
排它锁（exclusive lock） - 也叫写锁（write lock）。锁一次只能被一个线程所持有。
共享锁（shared lock） - 也叫读锁（read lock）。锁可被多个线程所持有。
加锁、解锁，检查锁是否已释放，都需要消耗资源，因此锁定的粒度越小，并发度越高。
MySQL 中支持多种锁粒度：
- 表级锁（table lock） - 锁定整张表，会阻塞其他用户对该表的读写操作。
- 行级锁（row lock） - 可以最大程度的支持并发处理。
事务
事务就是一组原子性的 SQL 查询。事务内的语句，要么全部执行成功，要么全部执行失败。
ACID
ACID 是数据库事务正确执行的四个基本要素。
- 原子性 (Atomicity)：一个事务被视为不可分割的最小工作单元，一个事务的所有操作要么全部提交成功，要么全部失败回滚。
- 一致性 (Consistency)：数据库总是从一个一致的状态到另一个一致的状态。事务没有提交，事务的修改就不会保存到数据库中。
- 隔离性 (isolation)：通常来说，一个事务所作的操作在最终提交之前，对其他事务来说是不可见的。
- 持久性 (durability)：一旦事务提交，则其所作的修改就会永久的保存到数据库中。
事务隔离级别
SQL 标准提出了四种“事务隔离级别”。事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。
事务隔离级别从低到高分别是：
- “读未提交（read uncommitted）” - 是指，事务中的修改，即使没有提交，对其它事务也是可见的。
- 读未提交存在脏读问题。“脏读（dirty read）”是指当前事务可以读取其他事务未提交的数据。
- **“读已提交（read committed）” ** - 是指，事务提交后，其他事务才能看到它的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
- 读已提交解决了脏读的问题。
- 读已提交存在不可重复读问题。“不可重复读（non-repeatable read）”是指一个事务内多次读取同一数据，过程中，该数据被其他事务所修改，导致当前事务多次读取的数据可能不一致。
- 读已提交是大多数数据库的默认事务隔离级别，如 Oracle。
- “可重复读（repeatable read）” - 是指：保证在同一个事务中多次读取同样数据的结果是一样的。
- 可重复读解决了不可重复读问题。
- 可重复读存在幻读问题。“幻读（phantom read）”是指一个事务内多次读取同一范围的数据过程中，其他事务在该数据范围新增了数据，导致当前事务未发现新增数据。
- 可重复读是 InnoDB 存储引擎的默认事务隔离级别。
- 串行化（serializable ） - 是指，强制事务串行执行，对读取的每一行数据都加锁，一旦出现锁冲突，必须等前面的事务释放锁。
- 串行化解决了幻读问题。由于强制事务串行执行，自然避免了所有的并发问题。
- 串行化策略会在读取的每一行数据上都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，所以一般不会采用这个级别。
事务隔离级别对并发一致性问题的解决情况：
| 隔离级别 | 丢失修改 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 读未提交 | ✔️️️ | ❌ | ❌ | ❌ |
| 读已提交 | ✔️️️ | ✔️️️ | ❌ | ❌ |
| 可重复读 | ✔️️️ | ✔️️️ | ✔️️️ | ❌ |
| 可串行化 | ✔️️️ | ✔️️️ | ✔️️️ | ✔️️️ |
死锁
死锁是指两个或多个事务竞争同一资源，从而导致恶性循环的现象。多个事务视图以不同顺序锁定资源时，就可能会产生死锁；多个事务同时锁定同一资源时，也会产生死锁。
InnoDB 目前处理死锁的方法是将持有最少行级锁的事务进行回滚。
事务日志
InnoDB 通过事务日志记录修改操作。事务日志的写入采用追加方式，因此是顺序 I/O，比随机 I/O 快很多。
事务日志持久化后，内存中被修改的数据由后台程序慢慢刷回磁盘，这称为预写日志（Write Ahead Logging，WAL）
如果数据修改以及记录到事务日志并持久化，此时系统崩溃，存储引擎可以在系统重启之后自动恢复数据。
MySQL 中的事务
MySQL 提供了两种事务存储引擎：InnoDB 和 NDB CLuster。
MySQL 默认采用自动提交模式（AUTOCOMMIT）。即如果不显式的声明一个事务，MySQL 会把每一个查询都当作一个事务来操作。
可以通过设置 AUTOCOMMIT 来启用或禁用自动提交模式。
可以通过执行 SET TRANSACTION ISOLATION LEVEL 来设置事务隔离级别。
InnoDB 采用两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 时才会释放，并且所有的锁都在一瞬间释放。
InnoDB 也支持通过特定语句显示加锁：
// 先在表上加上 IS 锁，然后对读取的记录加 S 锁
select ... lock in share mode;
// 当前读：先在表上加上 IX 锁，然后对读取的记录加 X 锁
select ... for update;
多版本并发控制
可以将 MVCC 视为行级锁的一个变种，它在很多情况下避免了加锁，因此开销更低。
MVCC 是通过保存数据在某个时刻的快照来实现的。也就是说，不管执行多久，每个事务看到的数据是一致的。根据事务开始时间不同， 每个事务对同一张表，同一时刻看到的数据可能是不一样的。
不同存储引擎实现 MVCC 的方式有所不同，典型的有乐观并发控制和悲观并发控制。
InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏列来实现。一个列保存了行的创建时间，一个是保存了过期时间。当然存储的不是实际的时间，而是系统版本号（system version number），每开始一个新事务，系统版本号都会自动递增。事务开始时刻的系统版本号作为事务的版本号，用来和查询到的每行记录的版本号作比较。
- Select - InnoDB 会根据这两个条件来查询：
- 只查找版本号小于或者等于当前事务的数据行，这样可以保证事务读取到的数据要么是在事务开始前就存在的，要么是自己插入或者修改的。
- 行的删除版本要么未定义，要么大于当前事务的版本号，这样可以保证读取到的数据在事务开始之前没有被删除。
- Insert - InnoDB 为新插入的每一行数据保存当前的系统版本号为行版本号。
- Delete - InnoDB 为删除的每一行保存当前的版本号为行删除标识。
- Update - InnoDB 为插入一条新纪录，保存当前系统版本号为行版本号，同时保存当前系统的版本号到原来的行为行删除标识。
MVCC 只在可重复读和读已提交两个隔离级别下工作。
MySQL 的存储引擎
Mysql 将每个数据库保存为数据目录下的一个子目录。建表时，MySQL 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。因为 MySQL 使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关：在 Windows 中，大小写不敏感；在 Linux 中，大小写敏感。
Mysql 常见存储引擎
- InnoDB - 默认事务引擎。
- MyISAM - Mysql 5.1 及之前的默认引擎。
- Archive
- Memory
- NDB
第二章 MySQL 基准测试（略）
第三章 服务器性能剖析（略）
第四章 Schema 与数据类型优化
数据类型
整数类型
整数类型有可选的 UNSIGNED 属性，标识不允许负值，大致可以使正数的上限提高一倍。
| 类型 | 大小 | 作用 |
|---|---|---|
TINYINT | 1 字节 | 小整数值 |
SMALLINT | 2 字节 | 大整数值 |
MEDIUMINT | 3 字节 | 大整数值 |
INT | 4 字节 | 大整数值 |
BIGINT | 8 字节 | 极大整数值 |
浮点数类型
FLOAT 和 DOUBLE 分别使用 4 个字节、8 个字节存储空间，它们支持使用标准的浮点运算进行近似计算，存在丢失精度的可能。
DECIMAL 类型用于存储精确的小数，支持精确计算，但是计算代价高。只有在需要对小数进行精确计算时，才应该使用 DECIMAL，例如财务数据。此外，当数据量较大时，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位乘以需要精确的倍数即可。
| 类型 | 大小 | 用途 |
|---|---|---|
FLOAT | 4 字节 | 单精度浮点数值 |
DOUBLE | 8 字节 | 双精度浮点数值 |
DECIMAL | 精确的小数值 |
字符串类型
VARCHAR 类型用于存储可变长字符串。
CHAR 类型是定长字符串。
与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY，它们存储的是二进制字符串。
| 类型 | 大小 | 用途 |
|---|---|---|
CHAR | 0-255 字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
BLOB 和 TEXT
BLOB 和 TEXT 都用于存储很大的数据，分别采用二进制和字符串方式存储。
| 类型 | 大小 | 用途 |
|---|---|---|
TINYBLOB | 0-255 字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 字节 | 短文本字符串 |
BLOB | 0-65 535 字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535 字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 字节 | 极大文本数据 |
日期和时间类型
| 类型 | 大小 | 格式 | 作用 | 备注 |
|---|---|---|---|---|
| DATE | 3 字节 | YYYY-MM-DD | 日期值 | |
| TIME | 3 字节 | HH:MM:SS | 时间值或持续时间 | |
| YEAR | 1 字节 | YYYY | 年份值 | |
| DATETIME | 8 字节 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 | 有效时间范围为 1000-01-01 00:00:00 到 9999-12-31 23:59:59 |
| TIMESTAMP | 4 字节 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 | 有效时间范围为 1970-01-01 00:00:01 到 2038-01-19 03:14:07 |
特殊类型
- ENUM - 枚举类型，用于存储单一值，可以选择一个预定义的集合。
- SET - 集合类型，用于存储多个值，可以选择多个预定义的集合。
Schema 设计简单规则
- 尽量避免过度设计，例如会导致极其复杂查询的 schema 设计，或者有很多列的表设计。
- 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用 NULL 值。
- 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
- 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
- 尽量使用整型定义标识列。
- 避免使用 MySQL 已经遗弃的特性，例如制定浮点数的精度，或者整数的显示宽度。
- 小心使用 ENUM 和 SET，虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱，最好避免使用 BIT。
范式意味着不存储冗余数据，但往往需要多关联查询，增加了查询的复杂度；反范式意味着存储冗余数据，但是减少了关联查询。在实际应用中，范式和反范式应当混合使用。
ALTER TABLE 如果操作的是大表，需要耗费大量时间。一般的操作是：用新结构创建一张空表，从旧表查出所有数据插入新表，然后删除旧表。
有两种替代方案：
- 在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库进行切换。
- 影子拷贝：创建一张新表，然后通过重命名和删表操作交换两张表。
第五章 创建高性能的索引
索引是存储引擎用于快速找到记录的一种数据结构。
索引优化应该是对查询性能优化最有效的手段了。
索引基础
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能高效地使用索引的最左前缀列。
B-Tree 索引
大多数 MySQL 引擎都支持 B-Tree 索引。存储引擎以不同的方式使用 B-Tree 索引，性能也各有不同，各有优劣。例如，MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原数据格式进行存储。再如 MyISAM 索引通过数据的物理位置引用被索引的行，而 InnoDB 则根据主键引用被索引的行。
B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
B-Tree 索引从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。
叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。
B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。
假设有如下数据表：
CREATE TABLE People(
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m','f')not null,
key(last_name, first_name, dob)
);
对于表中的每一行数据，索引中包含了 last_name、 first_name 和 dob 列的值。
请注意，索引对多个值进行排序的依据是 CREATE TABLE 语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都-样，则根据他们的出生日期来排列顺序。
可以使用 B-Tree 索引的查询类型。B-Tree 索引适用于全键值、键值范围或键前缀查找。
其中键前缀查找只适用于根据最左前缀的查找生。前面所述的索引对如下类型的查询有效。
- 全值匹配 - 全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为 Cuba Allen、出生于 1960-01-01 的人。
- 匹配最左前缀 - 前面提到的索引可用于查找所有姓为 Allen 的人，即只使用索引的第一列。
- 匹配列前缀 - 也可以只匹配某–列的值的开头部分。例如前面提到的索引可用于查找所有以 J 开头的姓的人。这里也只使用了索引的第一列。
- 匹配范围值 - 例如前面提到的索引可用于查找姓在 Allen 和 Barrymore 之间的人。这里也只使用了索引的第一列。
- 精确匹配某一列并范围匹配另外一列 - 前面提到的索引也可用于查找所有姓为 Allen, 并且名字是字母 K 开头的人。即第一列 last_ name 全匹配，第二列 first_name 范围匹配。
- 只访问索引的查询 - B-Tree 通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。也叫做覆盖索引。
因为索引树中的节点是有序的，所以除了按值查找外，索引还可以用于查询中的排序操作。
B-Tree 索引的限制：
- 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为 Bill 的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
- 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为 Smith 并且在某个特定日期出生的人。如果不指定名 (first_name)，则 MySQL 只能使用索引的第一列。
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询
WHERE last_name=' Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23'，这个查询只能使用索引的前两列，因为这里 LIKE 是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。
哈希索引
哈希索引 (hashindex) 基于哈希表实现，只有精确匹配索引所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算-一个哈希码 (hash code), 哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
哈希索引的限制：
哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A, 则无法使用该索引。
哈希索引只支持等值比较查询，包括
=、IN()、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price > 100。访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
空间数据索引 (R-Tree)
MyISAM 表支持空间索引，可以用作地理数据存储。和 B-Tree 索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。
查询时，可以有效地使用任意维度来组合查询。必须使用 MySQL 的 GIS 相关函数如 MBRCONTAINS() 等来维护数据。MySQL 的 GIS 支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对 GIS 的解决方案做得比较好的是 PostgreSQL 的 PostGIS
全文索引
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。
全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。
全文索引更类似于搜索引擎做的事情，而不是简单的 WHERE 条件匹配。
在相同的列上同时创建全文索引和基于值的 B-Tree 索引不会有冲突，全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件操作。
索引的优点
索引有以下优点：
- 索引大大减少了服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机 I/O 变为顺序 I/O。
索引是最好的解决方案吗？
对于非常小的表，大部分情况下简单的全表扫描更高效。
对于中到大型的表，索引就非常有效。
但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如可以使用分区技术。
如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录。哪个用户的信息存储在哪个表中”的元数据，这样在查询时就可以直接忽略那些不包含指定用户信息的表。对于大型系统，这是一个常用的技巧。
高性能的索引策略
正确地创建和使用索引是实现高性能查询的基础。
独立的列
独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
下面两个例子都无法使用索引：
SELECT actor_ id FROM sakila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_ DAYS(date_col) <= 10;
前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢。一种策略是，可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和总记录数的比值。索引的选择性越高则查询效率越高。
对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度。
前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。通常来说，选择性能够接近 0.03，基本上就可用了。
计算前缀索引选择性的示例
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 se16,
COUNT(DISTINCT LEFT (city, 7)) / COUNT(*) AS sel7,
FROM sakila.city demo;
多列索引
在多个列上建立独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。
例如，表 film_actor 在字段 film_id 和 actor_id 上各有一个单列索引。但对于下面这个查询 WHERE 条件，这两个单列索引都不是好的选择：
SELECT film_id, actor_id FROM sakila.film_actor
WHERE actor_id = 10 or film_id = 1;
选择合适的索引列顺序
正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。
如何选择索引的列顺序：
- 将选择性最高的列放到索引最前列。
- 可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
SELECT * FROM payment WHERE staff.id = 2 AND customer._id = 584;
是应该创建一个 (staffid, customer id) 索引还是应该颠倒一下顺序？
可以跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。
聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
具体的细节依赖于其实现方式，在 InnoDB 中，数据行实际上存放在索引的叶子页 (leaf page) 中。
聚簇索引的优点：
- 可以把相关数据保存在一起，访问数据时，可以减少磁盘 I/O。
- 数据访问更快。聚簇索引将索引和数据保存在同一个 B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引的缺点：
- 聚簇数据最大限度地提高了 I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表。
- 更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂 (page split) 的问题。当行的主键值要求必须将这一行插人到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
- 二级索引 （非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次。（回表）
InnoDB 和 MyISAM 的数据分布对比
MyISAM 存储引擎采用非聚簇索引存储数据，而 InnoDB 存储引擎采用聚簇索引存储数据。
来看下 MyISAM 和 InnoDB 是如何存储下面的表：
CREATE TABLE layout_test (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2),
);
对于 MyISAM，其数据分布比较简单，按照数据插入的顺序存储在磁盘上。对于每一行数据，都是一个行号，从 0 开始递增。由于行是定长的，所以 MyISAM 可以从表的开头跳过所需的字节找到需要的行（有点类似于数组）。如下图：
MyISAM 使用主键索引查找数据时，在 B+Tree 的叶子节点除了存储索引键之外，还保存了每个键所处的行指针（可以理解为行号）。当找到某个索引键对应的行指针后，就能定位到它对应的数据。如下图：
对于 MyISAM 的二级索引，它的存储方式跟主键索引没有什么区别，如下图：
所以对于 MyISAM 来讲，主键索引和其它索引在存储结构上并没有什么区别。主键索引就是一个名为 PRIMARY 的惟一非空索引。
对于 InnoDB 来讲，主键索引是聚簇的，也就是主键索引就是表，所以不像 MyISAM 那样需要独立的行存储。 聚簇索引的每个叶子节点都包含了主键值、事务 ID、用于事务和 MVCC 的回滚指针以及所有剩余列（这个例子中是 col2）。对于 InnoDB 的主键索引，数据分布如下图：
InnoDB 的二级索引和聚簇索引区别比较大，它的二级索引的叶子节点存储的不是”行指针”，而是主键值。存储主键值带来的好处是，InnoDB 在移动行时无须更新二级索引的这个指针。如下图：
由于 InnoDB 是通过主键聚集数据，所以使用 InnoDB 时，一定要指定主键，如果没有定义主键，InnoDB 会选择一个惟一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。
由于聚簇索引插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式，所以通常我们都使用一个递增 ID 作为主键。
最后，我们使用一个比较抽象的图，对比一下聚簇和非聚簇的数据分布：
覆盖索引
如果一个索引包含所有需要查询的字段的值，我们就称之为“ 覆盖索引"。覆盖索引能极大地提高性能。
- 索引条目通常远小于数据行大小，所以如果只需要读取索引，那 MySQL 就会极大地减少数据访问量。
- 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。
- 一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
- InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。
使用索引扫描来做排序
如果 EXPLAIN 出来的 type 列的值为 index, 则说明 MySQL 使用了索引扫描来做排序（不要和 Extra 列的 Using index 搞混淆了）。
MySQL 可以使用同一个索引既满足排序，又用于查找行。只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL 才能够使用索引来对结果做排序。
索引和锁
InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。