MySQL----高性能索引策略（1）

前言

读《高性能MySQL》所做笔记

高性能索引策略

正确地创建和使用索引是实现高性能查询的基础，接下来要做的是如何真正发挥这些索引的优势。

独立的列

如果查询中的列不是独立的，那么MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

示例：

SELECT actor_id FROM sakila.actor WHERE actor_id+1=5;

SELECT ... WHERE TO_DAYS(CURRENT_DATE) -TO_DAYS(date_col) <=10;

MySQL无法解析WHERE子句后面的表达式。我们应当养成简化WHERE条件的习惯，始终将索引列单独的放在比较符号的一侧。

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。
有两种解决方式：

1. 哈希索引
2. 前缀索引

关于前缀索引：

对于某些BLOB、TEXT或者非常长的VARCHAR类型的列，因为MySQL不允许索引这些列的完整长度。所以通常可以索引开始的部分字符，这样可以大大的节约索引空间，从而提高索引效率，但这样也会降低索引的选择性^注1

注1：索引的选择性是指：不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

那么问题就来了：怎么在保证索引的选择性较高的前提下，同时又不能太长（以便节约空间），前缀应该足够长，以使得前缀索引的选择性接近于索引整个列

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较

缺点

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

值得一提的是：有时候后缀索引也有用途（例如找到某个域名的所有电子邮件地址）。虽然MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引……

多列索引

误区：

• 为每个列创建独立的索引
• 按照错误的顺序创建多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0及之后版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中的某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的有多糟糕：

• 当出现服务器对多个索引做相交操作（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
• 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上
• 更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取，这会使得查询成本被低估，导致该执行计划还不如直接走全表扫描

可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

覆盖索引

设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。

索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点已经包含要查询的数据，那么还有什么必要再回表查询？如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为”覆盖索引“

覆盖索引让查询只需要扫描索引而无须回表所带来的好处：

• 索引的数目远小于数据行大小，如果只需要读取索引就能获取想要的数据，那么MySQL就会极大地减少数据访问量
• 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多
• 一些存储引擎只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
• 对InnoDB的聚簇索引来说，索引覆盖非常有用。InnoDB的二级索引能直接在叶子节点上拿到想要的数据，无需回表

事实上，MySQL只能使用B-Tree索引做覆盖索引，诸如哈希索引，空间索引，全文索引等都不行，因为覆盖索引需要存储要查询的数据！

EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY'
AND title LIKE '%APOLLO%'\G

索引无法覆盖查询的原因：

1. 使用了SELECT * FROM ...因为有时候可能会存在这样一种情况：没有任何索引覆盖了所有的列
2. LIKE操作：MySQL不能再索引中执行LIKE操作。这是底层API的限制，MySQL 5.5及之前的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于）。MySQL能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作，但是如果时通配符开头的LIKE查询，存储引擎就无法做比较匹配了。

可以使用”延迟关联“来解决上面两个问题

EXPLAIN SELECT *
FROM products
	JOIN(
    	SELECT prod_id
        FROM products
        WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'
    )AS t1 ON (t1.prod_id=products.prod_id)\G

这种方法延迟了队列的访问。在查询的第一阶段MySQL可以使用覆盖索引，在FROM子句的子查询中找到匹配的prod_id，然后根据这些prod_id在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好。

压缩(前缀压缩)索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认值压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM压缩索引块的方法是：先完全保存索引块的第一个值，然后把其他值与第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可

示例：

第一个值：perform

第二个值：performance

那么第二个值的前缀压缩后存储的是类似”7,ance“这样的形式。
MyISAM对行指针也采用类似的前缀压缩方式
前缀压缩会让索引块使用更少的空间，但会让某些操作变得更慢
例如MyISAM查找是无法在索引块使用二分查找而只能从头开始扫描，因为每个值的压缩前缀都依赖前面的值。

未完待续……