高性能MySQL第五章 读书笔记

第5章 创建高性能的索引

• 并不是所有的存储引擎都用的B+数，B数能提高查询速度，但是B+树可以方便叶子节点的范围查询。
• 多列索引，不仅可以精确匹配最左列的数据，还能模糊匹配最左列前缀数据。
• 如果有某些列模糊查询了多列索引的其中一个，其后面的索引都不再生效。
• 哈希索引不支持范围查询也不支持排序。只支持精确查询。
• innodb引擎有个特殊的功能叫“自适应哈希索引”，当innodb发现某些索引值被使用的非常频繁时，就会在内存中基于B-tree索引之上再建立一个哈希索引。
• 虽然存储引擎不支持哈希索引，但是我们可以自己实现。
• 索引对于中到大型的表，效果是最好的，对非常小的表，全表扫描更快，对超大表，建立索引和维护索引的代价随之增长。

索引的优点

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机IO变成顺序IO。（在只查询索引中的值的时候）

• 使用索引的时候，索引列不能是表达式的一部分，也不能是函数的参数。
• 前缀索引是一种使索引更小更快的方法，但是无法使用前缀索引进行ORDER BY 和GROUP BY操作，也无法用前缀索引做覆盖扫描。
• MySQL使用一种叫索引合并的策略，一定程度可以使用表上的多个单列索引来定位指定的行。
• 多个OR条件的时候，通常需要耗费大量的CPU和内存资源在算法的缓存排序和合并操作上。这些都不会计算到查询成本中。有时还不如全表扫描。可以用IGNORE INDEX来忽略某些索引
• 选择索引的列顺序有个经验法则：将选择性最高的列放在索引的最前列。但是在考虑排序和分组的时候可能就不是这样了。这种操作只是用来优化WHERE条件的查找。
• 有时候EXPLAIN的结果中，确实是用使用索引，但是要是索引出来的结果非常大，那么索引也没有什么用了，用SELECT COUNT(*),SUM(aaa=???),SUM(bbb= ???) FROM ccc;来看一下命中索引的查询列有多大。
• 聚簇索引中，在叶子页包含了行的全部数据，节点页只包含了索引列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。

聚集的数据有一些重要的优点：

1. 可以把相关数据保存在一起，通过主键id就能直接查询到相关数据。
2. 数据访问更快，相比非聚簇索引。
3. 使用覆盖索引扫描的查询可以直接使用页节点的主键值。

聚簇索引也有一些缺点

1. 虽然提高了IO密集型引用的性能，但是如果数据都在内存中，那么访问的顺序就没有那么重要了，聚簇索引也没有什么优势了。
2. 插入速度严重依赖插入顺序。
3. 更新聚簇索引列的代价非常高，会让该列移动到新的索引位置。
4. 二级索引访问需要两次索引查找，而不是一次。而innodb的自适应哈希索引能减少这样的重复工作。

• 对于非聚簇索引，主键索引就是一个名称为PRIMARY的唯一非空索引。
• Innodb的二级索引存储的是主键值，这样能在当主键值发生变化是，无须更新二级索引。
• 最好避免随机的聚簇索引，特别是对于IO密集型应用，比如使用了UUID作为聚簇索引。
• 使用随机主键索引时，写入目标页的缓存可能已经消失了，要重新从磁盘读取，频繁地做页分裂操作，因为页分裂，会导致页稀疏且不规则导致碎片。
• 顺序的主键在高并发的情况下可能会导致更坏的结果。
• 覆盖索引只有在B-Tree索引支持，是只查询结果通过索引的叶子节点就能直接获得的情况。可以有效的提高效率，减少缓存的负载。
• 如果使用了覆盖索引，EXPLAIN中的Extra中显示的是Using index。
• 扫描索引本身是很快的，如果索引不能覆盖查询所需的全部列，那么在排序的时候，就不得不每扫描一条索引记录就都回表查询一次对应的行。
• 建议删除重复索引和未使用的索引，减少冗余索引的使用。
• 尽可能地将需要做范围查询的列放到多列索引的后面。
• 使用多个等值条件查询是依然可以使用范围列后面的索引的。