引入
关于 MySQL 的优化，大部分人都听过这一条：避免使用 select * 来查找字段，而是要在 select 后面写上具体的字段。那么这么做的原因就是为了减少数据量的传输。
其实还有更重要的一个原因就是：使用 select *，就基本上不可能使用到覆盖索引。
而这样一个本应该使用覆盖索引的查询变成不能使用覆盖索引的查询，会导致随机 I/O 和回表查询。
索引原理
索引类型
索引（index）是在存储引擎（storage engine）层面实现的，而不是在 server 层面。不是所有的存储引擎都支持所有的索引类型。即便多个存储引擎支持某一索引类型，他们的实现和行为也可能有所差别。
B-Tree 索引
最常见的索引类型，基于 B-Tree （或者其变种）数据结构实现。B-Tree 的基本思想是：所有值（被索引的列）都是排过序的，每个叶节点到根节点的距离相等。所以 B-Tree 适合用来查找一定范围内的数据，而且可以直接支持数据排序（ORDER BY）。
但是当索引多列时，列的顺序特别重要，需要特别重要。InnoDB 和 MyISAM 都支持 B-Tree 索引。InnoDB 用的是一个变种 B+Tree，而 MyISAM 为了节省空间对索引进行了压缩，从而牺牲了性能。
哈希（Hash）索引
基于 Hash 表实现。所以这种索引只支持精确查找，不支持范围查找并且不支持排序。这意味着范围查找或者 ORDER BY 都要依赖于 server 层的额外工作。
目前只有 Memory 引擎支持显式的 Hash 索引（但是它的 hash 是 nonunique 的，冲突太多的话会影响查找性能）。虽然它也支持 B-Tree 索引，但Memory 引擎默认的索引类型却是 Hash 索引。
自定义哈希索引
思路：在 B-Tree 的基础上创建一个伪哈希索引。这和真正的哈希索引并不是一回事，因为还是在 B-Tree 中进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的时候在 WHERE 子句中手动指定使用哈希函数。
全文（Full-text）索引
主要用来查找文本中的关键字，而不是直接与索引中的值进行比较。全文索引跟其他索引大不相同，它更像是一个搜索引擎，而不是简单的 WHERE 语句的参数匹配。
可以对某列分别进行全文索引和 B-Tree 索引，两者互不冲突。
全文索引配合 MATCH AGAINST 操作使用，而不是一般的 WHERE 语句加上 LIKE 。
空间（Spatial (R-Tree)）索引
只有 MyISAM 引擎支持，并且支持的不好。可以忽略。
其他索引类别
还有很多三方的存储引擎使用不同类型的数据结构来存储索引。
索引策略
正确地创建和使用索引是实现高性能查询的基础。
独立的列
独立的列是指：索引列不能是表达式的一部分，也不能是函数的参数。
前缀索引
有时候需要索引一个很长的字符列，这会让索引变得很慢并且很大。
一个策略是之前的提到的哈希索引，另一个就是可以只索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但是这样也会降低索引的选择性。
前缀索引是一种能使索引更小、更快的有效办法。但也是有缺点的： MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
创建前缀索引：
1 | ALTER TABLE table_name ADD KEY(name(5)); |
多列索引
多列索引最常见的错误就是：为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
MySQL 在 5.0 版本之后引入了索引合并的策略，在一定程度上可以使用表上的多个单列索引来定位指定的行。这种策略包含：OR 条件的联合、 AND 条件的相交、组合前两种情况的联合和相交。
合适的索引列顺序
正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的要求。
如何选择索引列顺序的方法就是：将选择性最高的列放到索引的最前列。同时性能不只是依赖于所有索引的选择性（整体基数），也和查询条件的具体值有关，也就是值的分布范围有关。
在关于索引的选择性和基数的方面，尽管他们很重要，但是别忘了还有 WHERE 子句中的排序、分组和范围条件等其他因素，这些因素可能会对查询的性能造成非常大的影响。
索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
在 InnoDB 中，通过主键聚集数据。如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引作为主键。如果没有这样的索引，InnoDB 会隐式的定义一个主键来作为聚簇索引。同时 InnoDB 只聚集在同一个页面中的记录，包含相邻键值的页面也可能相距甚远。
聚簇索引的优点如下：
- 可以把相关数据保存在一起。
- 数据访问更快。
- 使用覆盖索引扫描的查询可以直接使用叶节点的主键值。
聚簇索引的缺点：
- 聚簇索引极大的提高了 I/O 密集型应用的性能，但是所有的数据都放在内存中，聚簇索引就没有了优势。
- 插入速度严重依赖与插入顺序。这也就是为什么 InnoDB 一般都会设置一个自增的 int 列作为主键。
- 更新聚簇索引的代价很高，因为会强制 InnoDB 将每个被更新的行移到新的位置。
- 如果不按照顺序插入新数据时，可能会导致“页分裂”。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候。
- 二级索引（非聚簇索引）可能比想象的更大，因为二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次。
建议去看看原书的P164~P170！
覆盖索引
覆盖索引是指一个索引包含或覆盖所有需要查询的字段值。所以可能一个索引对于某些查询是覆盖索引，而对于其他的查询则不是。其实就是覆盖索引本质上就是一个二级索引，只不过满足一些特定的条件。
覆盖索引是一个非常有用的工具，可以极大地提升性能。可以极大地避免非主键索引带来的二次回表查询。好处如下：
- 索引行通常远小于数据行的大小。如果只是需要索引，那就会极大的减少数据访问量。
- 因为索引是按照顺序存储的，索引对于 I/O 密集型的范围查询比随机从磁盘读取每一行数据的 I/O 要少的多。
- 由于 InnoDB 的聚簇索引，所以覆盖索引对 InnoDB 特别有用。
索引扫描做排序
MySQL 有两种方式可以生成有序的结果：通过排序操作、按索引顺序扫描。
不能使用索引排序的查询：
- 两种不同的排序方向。
- 引用了一个不在索引中的列。
- 排序的列无法组成索引的最左前缀（不匹配最左匹配原则）。
- 使用范围查询。
- 在表关联中使用列。
压缩（前缀压缩）索引
MyISAM 使用前缀压缩来减少索引的大小，从而可以让更多的索引放入到内存中，这在一些情况下可以极大的提高性能。
MyISAM 压缩每个索引块的方法是：先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同的字节数和剩余的不同后缀部分，把这部分存储起来即可。MyISAM 对行指针也采用类似的前缀压缩方式。
压缩块使用更少的空间，代价就是某些操作可能更慢。因为每个值的压缩前缀都依赖于前面的值，所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。
冗余和重复索引
重复索引是指：在相同的列上按照相同的顺序创建的相同类型的索引。（如果类型不同则不算重复索引）
大多数情况下并不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得很大，从而影响其他使用该索引的查询性能。
未使用的索引
除了冗余索引和重复索引，可能还会有一些不会使用到的索引，这类索引完全是累赘，建议删除。
索引和锁
索引可以让查询锁定更少的行。
虽然 InnoDB 的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁竞争并较少并发性。
InnoDB 在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得 SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢很多。
释义
二级索引
二级索引为什么要回表查询？答案在二级索引中保存的“行指针”本质：二级索引在叶子节点中保存的并不是指向行物理地址的指针，而是行的主键值。因此如果本次查询不是覆盖查询，就会利用二级索引叶子节点中保存的行主键值再去表里进行二次查询。
回表查询是指二级查询无法直接查询出所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到需要的数据，这种通过二级索引查询出来的过程，就叫做回表。
在使用 explain 查看 SQL 语句的执行计划，在 Extra 中看到 Using index condition 出现就是二级索引回表，而 Using where 只是过滤。
页分裂
索引的选择性
索引的选择性是指：不重复的索引列（也称为基数，cardinality ）与数据表的总记录（ #T ）的比值，范围是从 1/#T 和 1 之间。
索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。
唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。
那么如何找到前缀索引和索引选择性之间的平衡呢？
- 选择足够长的前缀以保证比较高的选择性，同时又不能太长（以便节约空间）。
- 计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。
示例可以去《高性能MySQL》 P154~P157 中查看。
聚簇
聚簇是指：表示数据行和相邻的键值紧凑地存储在一起。
最左匹配原则
在 MySQL 中建立联合索引时要遵循最左前缀匹配的原则，即最左优先。在检索数据时从联合索引的最左边开始匹配。
示例：
对列 col1 、列 col2 和列 col3 建一个联合索引
1 | KEY test_col1_col2_col3 on test(col1,col2,col3); |
而在这之中联合索引 test_col1_col2_col3 实际建立了 (col1) 、 (col1,col2) 、 (col,col2,col3) 三个索引。
1 | SELECT col1, col2, col4 FROM test WHERE col1="1" AND clo2="2" AND clo4="4" |
上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引 (col1,col2) 进行数据匹配。
索引条件下推（ICP）
在 MySQL 5.6 中引入 Index Condition Pushdown 优化。可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
MySQL 官方手册中是这样描述的：
1 | The goal of ICP is to reduce the number of full-record reads and thereby reduce IO operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce IO. |
翻译意思是：
1 | ICP 的目的是通过减少完整记录读取的数量来减少 I/O 操作。对于 InnoDB 的聚簇索引，完整记录已经被读取到 InnoDB 缓冲里，在这种情况下使用 ICP 不能减少 I/O 。 |
根据 (name, is_del) 联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出未删除的用户数据。过程如下图：
无索引下推执行流程：
索引下推执行流程：
上述图片的区别在于，InnoDB 在 (name, is_del) 索引内部就判断了数据是否逻辑除外，对于逻辑除外的记录，直接判断并跳过。因此可以通过上述示例，原本需要回表 4 次数据判断，而现在只需要 2 次回表。
注意：
- 索引条件下推只能对二级索引有效，这是因为 InnoDB 的主键索引叶子节点上保存的是全行数据，所以这时使用索引条件下推并不会起到减少查询全行数据的效果。
- 索引条件下推一般可用于所求查询字段不是或不全是联合索引的字段，查询条件为多条件查询且查询条件子句字段全是联合索引。
MySQL 默认启用索引下推，我们也可以通过修改系统变量 optimizer_switch的index_condition_pushdown 标志来控制 SET optimizer_switch = 'index_condition_pushdown=off'; 。
SQL 示例学习
总结
实践出真知，绝知此事需躬行。
参考
个人备注
此博客内容均为作者学习所做笔记，侵删！
若转作其他用途，请注明来源！