《高性能MySql》读书笔记（三）

索引基础

索引的类型

B-Tree索引
B-Tree索引即使用B-Tree数据结构来存储数据。B-Tree通常意味着所有值都是按顺序存储的，并且每个叶子页到根的距离相同。存储引擎已不同的方式来使用B-Tree索引，性能也各不相同。
有效的查询类型：

• 全值匹配——全值匹配是指和索引中的所有列进行匹配。例如：例如有一个索引是key(name,age)，该索引可用于查找name=xxx and age=20的人；
• 匹配最左前准——最左前缀即只使用索引的第一列。例如：上边提到的索引，只使用name=xxx；
• 匹配列前缀——也可以只匹配某一列的的值的开头部分。例如：上述索引可用于查找所有以J开头的姓的人；
• 匹配范围值——例如：上述索引可用于查找姓在allen和bootstrap之间的人；
• 精确匹配某一列并范围匹配另外一列——例如：第一列全值匹配，第二列范围匹配；
• 只访问索引的查询——例如覆盖索引。
  限制：
• 如果不是按照索引的最左列开始查找，则索引失效。例如：索引key(name,age)，无法查找where age=20 and name='tom’的人；
• 不能跳过索引中的列。例如索引key(name,age,address)，如果查找where name=“xxx” and address=“xxx”，则只能使用第一列索引；
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。例如：索引key(name,age,address)，如果查询条件where name like ‘S%’ and age=20，这个查询只能使用索引的第一列。

哈希索引
哈希索引(hash index)基千哈希表实现， 只有精确匹配索引所有列的查询才有效。
在MySQL中， 只有Memory引擎显式支持哈希索引。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，如果多个列的哈希码相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
限制：

• 哈希索引值存储哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行数据；
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；
• 哈希索引不支持部分索引列匹配查询，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
• 哈希索引只支持等值比较查询，包括=、IN()、<=> 。不支持任何范围查询；
• 访问哈希索引的数据非常快，除非出现哈希冲突，此时存储引擎需要遍历链表中所有行指针，逐行进行比较；
  如果哈希冲突非常多的话，一些索引维护操作的成本也会非常高。

全文索引
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
在相同的列上同时创建全文索引和基千值的B-Tree索引不会有冲突， 全文索引适用于
MATCH AGAINST操作，而不是普通的WHERE条件操作。

索引的优点

• 索引大大减少了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机I/O变为顺序I/O。
  但索引并不适合所有的表， 对于非常小的表，大部分情况下简单地全表扫描更高效。对于中到大型表，索引就非常有效。但对于特大型的表，建立和使用索引的代价也随之增长。

高性能的索引策略

独立的列

如果查询中的列不是独立的，则MySql就不会使用索引。“独立的列” 是指索引列不能是表达式的一部分， 也不能是函数的参数。
例如， 下面这个查询无法使用actor_id列的索引：

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = s; 

前缀索引和索引的选择性

为了索引很长的字符列，避免索引大而且慢，通常会索引开始的部分字符，这样会节约索引的空间，提升索引的效率，但同时也会降低索引的选择性。即不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。
对于 BLOB、TEXT 或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
索引前缀长度的选择——计算法。例如：

SLELECT COUNT(DISTINCT city)/COUNT(*) AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel2, ...; 

如果前缀的选择性接近sel1就可以使用了。有时候只看平均选择型也不靠谱，还需要做进一步判断。
缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描；

多列索引

key(col1, col2, col3)；

• 当出现服务器对多个索引做相交操作时（多个AND），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引;
• 当服务器需要对多个索引做联合操作时（多个OR），通常需要耗费大量的CPU和内存在算法的缓存、排序和合并上。

选择合适的索引顺序

• 正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；
• 索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求；
• 索引列顺序的选择——在不考虑分组和排序的情况下，将选择性最高的列放到索引最前面（经验法则）；
• 避免随机I/O和排序；

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。
优点：

• 可以把相关数据保存在一起。
• 数据访问更快。 聚簇索引将索引和数据保存在同一个B-Tree中。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
  缺点：
• 聚簇数据最大限度地提高了1/0密集型应用的性能， 但如果数据全部都放在内存中，则访问的顺序就没那么重要了， 聚簇索引也就没什么优势了。
• 插入速度严重依赖千插入顺序。
• 更新聚簇索引列的代价很高， 因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行， 或者主键被更新导致需要移动行的时候， 可能面临“页分裂（page split）”的问题。
• 聚簇索引可能导致全表扫描变慢。
• 二级索引（非聚簇索引） 可能比想象的要更大， 因为在二级索引的叶子节点包含了引用行的主键列。