重新看了一遍做得记录。

独立的列

索引列不能是表达式的一部分，也不能是函数的参数。在 where 语句里，始终将索引列单独放在比较符号的一侧。

前缀索引和索引的选择性

选择性是指不重复的索引值和数据表的记录总数的比值。
选择性越高查询效率越高，唯一索引的选择性是 1。

对于很长的索引列，判断它的前缀是否有足够的选择性。
MySQL 无法用前缀索引做 order by 和 group by ，也无法用前缀索引做覆盖扫描。

多列索引

MySQL 5.0 引入索引合并策略。索引合并使用的范围： OR 条件的联合，AND 条件的相交，组合前两种条件的组合和相交。
索引合并暗示着不良的设计，可以考虑组合索引。

索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

将选择性高的列放在最前列，通常不如避免随机 I/O 和排序重要。
不需要考虑排序、分组时，应将选择性最高的列放在最前面。

性能不只是依赖于所有索引的列的选择性，也和查询条件的具体值有关，也就是和值的分布有关。

聚族索引

聚族索引不是索引的一种类型，而是一种数据存储格式。（类似 Oracle 的索引组织表）

表有聚族索引时，它的数据行实际上存放在索引的叶子页中。

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

InnoDB 只聚集在同一个页面中的数据，包含相邻键值的页面可能相距甚远（物理不相邻）。

聚族索引的优缺点

优点：

• 把相关数据保存在一起。
• 数据访问快。聚族索引将索引和数据保存在同一个 B-Tree 中，因此从聚族索引中获取数据通常比在非聚族索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

• 聚族数据最大限度提高了 I/O 密集型应用的性能，但如果数据都在内存中，则没有这个优势。
• 插入速度依赖插入顺序。按照主键的顺序插入 是加载数据到 InnoDB 表中速度最快的方式。如果不是按主键顺序加载的，加载完成后最好使用 optimize table 命令重新组织下表。
• 更新聚族索引的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
• 基于聚族索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂(page split)”的问题。页分裂会导致表占用更多的磁盘空间。
• 聚族索引可能导致全表扫描会变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚族索引）可能比想象中的要大，因为在二级索引的叶子节点也包含了引用行的主键列。
• 二级索引访问需要两次的索引查找，而不是一次。（二级索引保存的不是指向行的物理位置的指针，而是行的主键值。因此是现在二级索引中找到主键值，然后用主键值在聚族索引中查找。） 这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。

使用 InnoDB 时应该尽可能地按主键的顺序插入数据，并且尽可能地使用单调递增加的值来插入新行。最好避免随机的（不连续且值的分布范围非常大，比如 UUID）聚族索引，特别是对于 I/O 密集型的应用。

使用随机主键可能存在如下缺点：

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

顺序主键的缺点：

• 主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。
• 另一个热点可能是 auto_increment 锁机制。

覆盖索引

如果一个索引包含查询需要的所有字段的值，就称为索引覆盖。这样只需要扫描索引无须回表。

好处：

• 索引条目通常远小于数据行的大小，只读取索引会极大减少数据访问量。
• 索引是按照值顺序存储的（至少单个页内是如此），对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。
• 一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，特别是那些系统调用占了数据访问的最大开销的场景。
• 由于 InnoDB 的聚族索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次访问。

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值。MySQL 只能使用 B-Tree 索引做覆盖索引。

当发起一个被索引覆盖的查询时，在 explain 的 Extra 列可以看到 “Using Index” 的信息。（Extra 列的 “Using Index” 与 type 的 “index” 完全不同， type 列和覆盖索引毫无关系，它只是表示这个查询访问数据的方式，或者说是 MySQL 查找行的方式。）

MySQL 能在索引中最左前缀匹配的 LIKE 比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的 LIKE 查询，存储引擎就无法做比较匹配。

使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：通过排序，或者按索引顺序扫描。如果 explain 出来的 type 列的值为 “index” 说明 MySQL 使用了索引扫描来做排序。

如果索引不能覆盖查询所需的全部列，那就不得不 每扫描一条索引记录就都回表查询一次对应的行。这基本都是随机 I/O，因此按索引顺序读取数据的速度通常要比顺序全表扫描慢，尤其是在 I/O 密集型的工作负载时。

只有当索引的列顺序和 order by 子句的顺序完全一致，并且所有列的排序方向（降序或升序）都一样时，MySQL才能够使用索引来对结果做排序。
如果查询需要关联多张表，则只有当 order by 子句引用的字段全部为第一个表时，才能使用索引做排序。order by 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则 MySQL 都需要执行排序，而无法利用索引排序。

有一种情况下 order by 子句可以在不满足索引的最左前缀的要求，就是前导列为常量时（用前导列等于给定值的形式）。

冗余和重复索引

MySQL 允许在相同列上创建多个索引，索引类型不同，就不算重复索引。（Oracle 不区分索引类型，只看索引的列和顺序）

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。

MySQL 的唯一约束和主键都是通过索引实现的。

索引和锁

索引可以让查询锁定更少的行。

InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。但这只有当 InnoDB 在存储引擎层能够过滤掉所有不需要的行时才有效（也就是从存储引擎层返回到服务器层过滤的仍然会锁住行）。

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

其他

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

对于范围条件查询，MySQL 无法再使用范围列后面的其他索引列，对于“多个等值条件查询（in 子句）”则没有这个限制。

延迟关联：通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。

在选择和编写利用索引的查询时，记住下面三个原则：

• 单行访问是很慢的。（特别是机械硬盘存储，SSD 的随机 I/O 快很多，不过这点仍然成立。）最好读取的块中包含尽可能多所需的行。使用索引可以创建位置引用以提升效率。
• 按顺序访问范围数据是很快的。原因：1，顺序 I/O 不需要多次磁盘寻道，所以比随机 I/O 快很多（特别是机械硬盘）；2，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且 group by 查询也无须再做排序和将行按组进行聚合计算。
• 索引覆盖查询是很快的。存储引擎不需要回表查找行。