《高性能MYSQL》--高性能的索引策略，总结索引类型

高性能的索引策略

前缀索引

在列储存的值长度过长，可以选择固定长度的前缀来作为前缀索引，这样能提高系统的性能，需要注意的是前缀索引的选择性要与原来的大致相等。

就是说要在选择性和性能之间做一个平衡，选择性是指

不重复的索引值（也称为基数，cardinality）和数据表的记录总数(＃T)的比值

唯一索引的选择性是1，就是每个索引是唯一的，所有上面这个比值算得是1

一般情况下，列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或 者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL并不支持对这些列的完整内容进行索引。

对city_demo的做前缀为8的索引就是这样：

前缀索引是一种能使索引更小、更快的有效办法，但它也有缺点：MySQL无法使用前缀 索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。

多列索引，索引合并策略

在多列上独立地创建多个单列索引，这种做法不太好，触发你许多查询都是基于单个列进行的，否则在创建了多列索引的情况下进行以多个列作为条件的查询时，mysql会进行索引合并，查询能够同时使用两个单列索引进行扫 描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union），AND条件的相 交（intersection），组合前两种情况的联合及相交。

索引合并缺点不少:

1.当查询语句有许多AND的时候，其实这时候说明应该做的是联合索引，而不是多列索引

2.当查询有许多OR的时候，索引合并策略会对单列索引进行扫描，再合并结果，这会很消耗性能

3.优化器不会把这些操作计算到“查询成本”（cost）中，优化器只关心 随机页面读取。这会使得查询的成本被“低估”，这时将查询改用UNION操作会好一点

如果在EXPLAIN中看到有索引合并，那么就应该好好检查一下查询语句的写法和表的结 构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能，还可以使用IGNORE INDEX语法让优化器强制忽略掉某些索引，从而避免优化器使用包含索引合并的执行计划。

选择合适的索引列顺序

索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的，这种情况就是说索引只是用来优化查询语句

当使用前缀索引时，要注意一些特殊情况：

比如你在用户的用户名前5个字节做了前缀索引，但是，在某些应用程序中，对于没有登录的用户，都将其用户名记录为“guset”，一旦查询涉及这个guset，那么索引起到的作用就没那么大了。

注意：不要假设平 均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。

聚簇索引

聚簇索引不是索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。

一张表只能有一个聚簇索引

聚簇索引中的记录是这样存放的：

叶子页存放的是数据行的全部数据，而节点页存放的是索引列，在这个案例中，索引列包含的是整数值。

• 将数据存储和索引放到了一块，找到了索引也就找到了数据
• 聚集索引存储记录是物理上连续存在，⽽⾮聚集索引是逻辑上的连续，物理存储并不连续。
• InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引
• 插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。

聚簇索引优点：

1.可以把相互关联的数据保存在一起。例如，在实现电子邮箱应用时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。因为一个用户的全部邮件都存储在一片连续的物理地址上，如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。

2.数据访问更快。因为键和值存放在一起

3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引缺点：

1.聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存

中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。

2.插入速度严重依赖于插入顺序

3.更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置

4.基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂（page split）的问题。

5.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存 储不连续的时候

6.二级索引（非聚簇索引）可能比想象中的要更大，因为二级索引的叶子节点包含了 引用行的主键列。

7. 二级索引访问需要两次索引查找，而不是一次。因为二级索引的叶子节点的值是聚簇索引，要再通过聚簇索引的值去查到对应的数据行。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引。需要注意的是，只有B-tree索引可以用于覆盖索引。

使用索引扫描来做排序

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或

正序）都一样时，MySQL才能使用索引来对结果做排序。

如果查询需要联接多张表， 则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序

ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则无法利用索引排序。

有一种特殊情况，如果前面的列为常量的时候，ORDER BY子句中的列也可以不满足索引 的最左前缀的要求。如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可 以“填补”索引字段的间隙了。

例如，Sakila示例数据库的表rental在列（rental_date，inventory_id，customer_id）上建有

名称为rental_date的索引：

我这样进行排序是利用到了索引的

因为指定了索引中的第一列魏常量

冗余和重复索引

MySQL允许在相同列上创建多个相同的索引。虽然MySQL会抛出一个警告， 但是并不会阻止你这么做。

MySQL需要单独维护重复的索引，优化器在优化查询的时候也需要逐个地进行评估，这会影响性能，同时也浪费磁盘空间。