高性能MySQL第五章-创建高性能的索引

5.1 索引的类型

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
  ===== 多列索引中的排序规则是先按左一排序，再按左二排序，依次排序，如果索引非最左侧数据，其数据排序是混乱无序的，二分查找的基础是数据是有序的，所以无法通过索引优化查询====
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name ），则MySQL只能使用索引的第一列。
  ==== 理由同上，如果中间的列没有指定，则右侧的索引是无序的====
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE last_name=‘Smith’ AND frst_nameLIKE ‘J％’ AND dob=‘1976-12-23’ ，这个查询只能使用索引的前两列，因为这里LIKE 是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。
  ==== 理由同上，相当于排除了不以J开头的数据，但是剩余的右侧的索引还是无序的====

• 哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
这里说的结构紧凑，可能是B树的索引结构中存了索引列的数据，待考证。。。。
==== 在不考虑内存的情况下，哈希索引 优于 B+树索引，B+树索引需要在内存中多次二分查找，而哈希索引通过一次计算就可以找到对应的key,哈希底层是通过数组和链表实现的，所以可以直接通过下标计算出对应key值的内存地址，拿到行数据指针====

5.3 高性能的索引策略

5.3.1　独立的列

我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
例如，下面这个查询无法使用actor_id 列的索引：

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

凭肉眼很容易看出WHERE中的表达式其实等价于actor_id=4 ，但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化WHERE 条件的习惯，始终将索引列单独放在比较符号的一侧。

5.3.2　前缀索引和索引选择性

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

5.3.3　多列索引

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

• 当出现服务器对多个索引做相交操作时（通常有多个AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时（通常有多个OR 条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。

5.3.4　选择合适的索引列顺序

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗?在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。
然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。

5.3.5　聚簇索引

图5-3展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。

5.3.6　覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。
MySQL不能在索引中执行LIKE 操作。这是底层存储引擎API的限制，MySQL 5.5和更早的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于）。MySQL能在索引中做最左前缀匹配的LIKE 比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE 查询，存储引擎就无法做比较匹配。这种情况下，MySQL服务器只能提取数据行的值而不是索引值来做比较。

5.3.7　使用索引扫描来做排序

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。
MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。
==== 查询结束之后，如果排序用索引列，则不需要回表查询，所以如果索引也覆盖排序效果最好 ====