高性能Mysql读书笔记#第五章

创建高性能索引

5.1索引的基础

B-树索引

有效的查询类型：
1、全值匹配。(包含索引的所有信息)
2、匹配最左前缀。（包含索引最左的属性信息）
3、匹配列前缀。（满足2）
4、匹配范围值。（满足2）
5、精确匹配某一列并范围匹配另外一列。（满足2）
6、只访问索引的查询：无需访问数据行，只需访问生成的索引（B+树）；

限制：
1、如果不是按照索引的最左列开始查找，则无法使用索引。
2、不能跳过索引中的列。比如索引(lastname,firstname,birthday)，若使用lastname+birthday，其实仅使用了索引的第一列。
3、若查询中某个列的范围查询，则其右边所有列无法使用索引优化查询

B-树与B+树的区别

1、B-树结点上存储了键值，指向data的地址
2、B+树叶子结点可能是单链表还是双向链表的形式链接在一起，方便排序和查找范围

B+ 树的优点在于：
由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的内部节点，也就是key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
B+树的叶子结点都是相链的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
但是B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每一个数据行的指针。
哈希索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

哈希索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
哈希索引数据并不是按照索引值顺序排序的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
哈希索引只支持等值比较查询，包含IN、=、<=>（注意<>和<=>是不同的操作，<=>左值可以等于NULL，<>和=左值若等于NULL，则结果均为控），也不支持任何的范围查询。
访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值可能会有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历该哈希值对应的链表中的所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些所有维护操作的代价也很高。例如，如果在某个选择性很低的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

5.2索引的优点

1、索引大大减少了服务器需要扫描的数据量：存储引擎不用进行全表扫描。
2、索引可以帮助服务器避免排序和临时表：B树索引内的数据已经排好了序。
3、索引可以将随机I/O变为顺序I/O：B树索引中相关数据行存储在一个磁盘页面中。

注意：索引并不总是会提升性能，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效，对于中到大型的表，索引的就非常有效。但是对于特大型的表，建立和使用索引的代价将随之增加。

5.3高性能的索引策略

5.3.1独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
例如，下面这个查询无法使用actor_id列的索引：
select actor_id from sakila.actor where actor_id + 1 = 5;

5.3.2前缀索引和索引选择性

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高，则查询的效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性时1，这是最好的索引选择性，性能也是最好的。
有些时候需要索引很长的字符列，这会让索引变得大且慢。这种情况下通常可以索引索引列的开始部分字符，这样可以大大节约索引空间，从而提高索引效率，但是这样会降低索引的选择性。
前缀索引是可以让索引更小、更快的有效办法。但是无法使用前缀索引做order by和group by操作，以及覆盖扫描

5.3.3 多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫做索引合并（index merge）的策略（需要注意的是，索引合并如果使用不当会很容易造成慢查询），一定程序上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而很多情况下没有哪一个独立的单列索引是非常有效的。
使用explain语句分析sql时，可以在Extra列中看到是否使用了index merge。索引合并有时候是一种优化的结果，但是实际上更多时候说明了表中索引建的很糟糕：
当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
优化器不会将这些计算到查询成本中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。
如果在explain中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimization switch来关闭索引合并功能。也可以使用ignore index提示让优化器忽略掉某些索引。

5.3.4 选择合适的索引列顺序

在如何选择索引的列顺序有一个经验法则：将选择性最高的列放在索引的最前面。这个经验法则在某些时候有用，但通常不如避免随机I/O和排序那么重要。场景不同则选择不同，没有一个放之四海而皆准的法则。但是当不需要考虑排序和分组的情况下，将选择性最高的列放在前面通常是很好的。

5.3.5 聚集索引（聚簇索引）

聚集索引并不是一种单独的索引类型，而是一种数据存储方式。在Oracle中也叫做索引组织表（index organized table）。和聚集索引相对于的叫非聚集索引，在Oracle中也叫堆组织表（heap organized table）。当表有聚集索引时，他的数据行实际上存放在索引的叶子页。《高性能MySQL》中术语“聚集”，就是表示数据行和相邻的键值紧凑地存储在一起。说的通俗一点，就是数据行在磁盘上是按照索引顺序存放在一起，当然这只是定义，实际中例如Innodb存储引擎，只聚集在同一个磁盘页面中的记录，包含相邻键值的页面可能相距很远。也就是说Innodb中，同一个磁盘页面中的记录是因为在同一个页面中，所以是存储在一起的，但是逻辑相邻的磁盘页面由链表连接，实际上可能物理上相隔很远。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。
以下以Innodb存储引擎做说明。到目前为止，Innodb存储引擎并不支持选择哪个索引作为聚集索引，在定义了主键的情况下，在Innodb中将通过主键聚集数据，如果没有定义主键，Innodb会选择一个唯一的非空索引代替。如果没有这样的索引，Innodb会隐式定义一个主键来作为聚集索引。

聚集索引的优点：

1、可以把相关数据保存在一起。
2、数据访问更快
3、使用聚集索引扫描的查询可以直接使用叶节点中的主键值。

聚集索引缺点：

聚集数据能最大限度地提高了I/O密集型应用的性能，但如果数据全部都存放在内存中，则访问的顺序就没有那么重要了，聚集索引也就没什么优势了。
插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到Innodb表中速度最快的方式（所以在Innodb存储引擎中，主键不推荐使用UUID，这是因为UUID型主键会造成大量的随机I/O操作，推荐使用和数据行没有关系的auto increment整形主键是因为整形存储空间比较少，而且插入数据时，都是按照主键顺序插入，速度会比较快）。
更新聚集索引列的代价会很高，因为会强制Innodb将每个被更新的行移动到新的位置。
基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。所谓的页分裂问题就是指当行的主键值要求必须将这一行插入到某个已满的磁盘页中时，存储引擎会将该页分裂成两个页面来容纳该行。页分裂会导致表占用更多的磁盘空间以及为了维护B+树索引移动元素导致的开销。
非聚集索引的叶子结点中包含了对应数据行的主键列。这可能会导致非聚集索引占用比较大的空间。
非聚集索引访问需要两次索引查询，而不是一次。这是因为非聚集索引保存的行指针并不是指向数据行的物理位置的指针，而是行的主键值。这也就意味着通过非聚集索引查找行时，存储引擎需要找到非聚集索引的叶子结点获取对应的主键值，然后根据这个值去聚集索引中查找对应的行。在MySQL中之所以这样设计是为了避免数据行因为更新、插入等操作导致数据行需要移动时也同时需要维护非聚集索引。