《高性能MySQL》学习笔记三

创建高性能的索引

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要，但不恰当的索引对性能的影响也会很大，所以创建高性能的索引很重要。

索引基础

什么是索引？
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引好比一本书的目录，我们可以依据目录先来查找需要的信息在哪个位置，然后在那个位置范围内查找信息。索引大大提高了数据检索速度。
## 索引的类型 ##
1.B-Tree索引
大多数的索引都是B-Tree索引，使用的是B+Tree数据结构来存储数据。

使用这个结构来存储数据的特点是：存储引擎不需要进行全表扫描来获取需要的数据，而是从索引的根节点开始进行搜索，通过比较节点页的值和要查找的值可以找到合社的指针进入下层子节点，最终存储引擎要么找到对应的值，要么记录不存在。
对于多个列的组合索引，在建立索引结构时依据最左原则，即先对最左的那个索引列进行从小到大的划分，若遇到相同的值，则依据次之的列进行排序划分，以此类推，建立索引结构。
可以使用B-Tree索引的查询类型
全值匹配：对建立索引的所有列进行匹配。
匹配最左前缀：对于建立索引的列，在查询时，可以只指定最左的一列或几列，即使用索引的前几列。
匹配列前缀：在匹配最左前缀的基础上，可以只匹配某一列的值的开头部分。
匹配范围值：在匹配最左前缀的基础上，可以匹配某一列值范围内的数据。
精确的匹配某一列并范围匹配另外一列：即可以第一列全匹配，第二列范围匹配。
只访问索引的查询：B-Tree通常支持“只访问索引的查询”，即查询只需要访问索引，不需要访问数据行。
（因为索引树中的节点是有序的，还可以对索引进行order by排序操作）
B-Tree索引的限制
1.如果不是按照索引的最左列开始查找，则无法使用索引，即一定要满足最左匹配原则。
2.不能跳过索引中的列。
3.如果查询中有某个列范围查询，则其右边的所有列都无法使用索引优化查找。
（综上：索引建立的顺序至关重要）

2.哈希索引
哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。（在MySQL中，只有Memory引擎显示支持哈希索引，也是其默认索引类型，当然，Memory引擎也支持B-Tree索引）
哈希索引的限制：
1.哈希索引只包含了哈希值和行指针，而不存储字段值，所以不能使用索引的值来避免读取行。
2.哈希索引并不是按照索引值顺序存储的，所以无法用于排序。
3.哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。
4.哈希索引只支持等值比较（=、in()、<=>），不支持任何的范围查询。
5.访问哈希索引的数据非常快，除非出现hash冲突，当出现冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，知道找到所有符合条件的行。
6.如果哈希冲突很多的话，一些索引维护操作的代价会很高。
B-Tree与哈希索引结合：
我们可以创建自定义的哈希索引，在B-Tree上创建一个伪哈希索引，这时还是使用B-Tree进行查找，只不过使用的是哈希值而不是键本身进行索引查找，这样能够加快查询的速度。（在索引值比较长时，可以采用这种方法）

select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com")

上述语句就是使用了自定义的哈希索引，在B-Tree索引基础上，将url的值利用CRC32哈希函数计算其哈希值。虽然上述where语句中含有两个条件，但MySQL优化器会选择使用这个选择性很高且体积很小的基于url_crc列的索引来完成查找。这样就实现了B-Tree与哈希索引结合使用的优化性能。
当上述情况出现哈希冲突时，我们不能仅仅使用url_crc索引来进行查询，必须在where子句之后包含其索引的常量值，方便哈希冲突之后的比较。

索引的优点

1.索引能大大减少服务器需要扫描的数据量；
2.索引可以帮助服务器避免排序和临时表；
3.索引可以将随机I/O变为顺序I/O。

高性能的索引策略

独立的列
“独立的列”是指索引列不能是表达式的一部分。

select actor_id from sakila.actor where actor_id + 1 = 5;

上述语句中where后的表达式mysql并不能解析“actor_id+1”是什么，所以就无法使用在actor_id上建立的索引，所以在写条件表达式时，应该始终将索引列单独放在比较符号的一侧。
前缀索引和索引选择性
当一个需要索引一个很长的字符列时，我们可以使用前面说过的哈希索引来解决，还有一种方法是使用前缀索引。
前缀索引就是索引列开始的部分字符，这样可以大大节约索引空间，从而提升索引效率。但往往会降低索引的选择性（索引的选择性即不重复的索引值与数据表中记录总数的比值，唯一索引的选择性是1，也是最优的索引选择性）。
如何在前缀索引中保证高的索引选择性？（即选择合适的前缀索引长度）
书中介绍的方法我简单总结下：即首先计算完整列的选择性（完整列中不同的索引值除以完整列的记录值），这样我们可以得到一个标准选择值，然后通过计算不同的前缀索引下的索引选择性，与标准值相比较，接近标准值的前缀索引长度即为最优的前缀索引长度。
多列索引
多列索引与创建多个单独的索引是不同的。
依据实际情况选择建立多个单独索引还是多列索引，书中简单介绍了创建独立索引进行查询时合并优化的情况（我觉得不能说哪个好，看具体情况建立索引吧）
选择合适的索引列顺序
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。
聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方法。
当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。
聚簇与非聚簇的区别：
聚簇索引的存储顺序就是数据的物理存储顺序，即将数据行与索引键值存储在一起了，因为无法同时将数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
非聚簇索引的叶子节点存储的不是数据行，而是指向数据行的指针，所以其存储的顺序与实际数据的物理存储顺序是无关的，也即可以有多个非聚簇索引。
在InnoDB引擎中，InnoDB通过主键来聚集数据，如果没有定义主键，InnoDB会隐式的定义一个主键来作为聚簇索引。
聚簇索引的优点：
1.可以把相关的数据保存在一起。
2.访问速度更快，只需要一次索引。
3.使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。
其缺点：
1.极大提高I/O密集型应用的性能，但如果数据都在内存中，那么聚簇索引就没什么优势。
2.插入的速度严重依赖于插入顺序，因为聚类索引的数据是有序的，当一个数据插入可能会影响之后的数据位置。
3.更新聚簇索引的代价很高，每个被更新的行都需要移动到新的位置。
4.插入新行时可能会导致“页分裂”的问题。（页分裂导致占用更多的磁盘空间）
5.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏或由于页分裂导致数据存储不连续时。
6.二级索引可能比想象的要大，因为二级索引的叶子节点包含了引用行的主键列。
7.二级索引需要两次索引查找，而不是一次。（因为其保存了指向行的行指针，指向行的主键值）。
InnoDB与MyISAM的数据分布
**MyISAM的数据分布：**MyISAM是按照数据插入的顺序存储在磁盘上的。在每行旁边可以认为有个从0增长，标识行号的序列，有点类似于我们常常定义的_id属性。MyISAM的主键（唯一的非空索引）与其他索引没有太大的区别，在建立索引时，在B-Tree树中的叶子节点上按顺序存储主键值及其主键数据所对应的“行号”。
InnoDB的数据分布：因为InnoDB支持聚簇索引，其叶子节点存储原始数据。
在InnoDB的聚簇索引叶子节点中有以下内容存储：
主键列：当建立主键索引时，会按照主键值的顺序创建叶子节点，其是查找数据的关键
事务ID（TID）：用户标明此行所进行事务的ID，便于事务的控制。
回滚指针（RP）：用户事务和MVCC的回滚指针。
非主键列：因为聚簇索引的关系，叶子节点包含了完整的表数据，所以其他的非主键列也存储在叶子节点中。
InnoDB的二级索引：与MyISAM的二级索引有明显的不同，InnoDB的二级索引的叶子节点中存储的不是“行指针”,而是主键值，并以此作为指向行的“指针”。（这样二级索引会占用更多的空间，但避免了行移动或数据页分裂时二级索引的维护工作）
注：InnoDB由于其聚簇的特性，其整个表存放在主键索引之下；而MyISAM是非聚簇表分布，其表数据与索引的关系依赖指针。
InnoDB建立聚簇索引的选择：
要避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。一般使用Auto-Increment自增列作为主键。
对于不连续且值的分布范围很大的聚簇索引，在新行插入其中时，可能并不是依次插入的，插入的新行会打乱原本的行位置，造成频繁的页分裂操作，增加很多额外的工作。
注：对于高并发工作负载，InnoDB中按主键顺序插入可能会造成明显的争用（间隙锁的竞争），以及在Auto-Increment锁机制的影响会造成顺序主键造成坏的结果。
覆盖索引（explain的Extra列显示“Using index”时，就可以成为覆盖索引）
什么是覆盖索引？
一个索引的叶子节点中包含（或者说覆盖）所需要查询的字段的值，我们就称之为“覆盖索引”。（查询只需要扫描索引，无序回表）
1.索引的条目通常要远小于数据行的大小，所以可以更容易的全部放入内存中（对于MyISAM尤其正确）；
2.因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘中读取每一行数据的I/O要少得多。
3.一些引擎（MyISAM）在内存中只缓存索引，数据则是依赖于操作系统来缓存，因此要访问数据需要一次系统调用。覆盖索引不用这一次系统调用。
4.覆盖索引对InnoDB的聚簇索引特别有用，在InnoDB中二次索引中保存主键值，需要进行二次查询，但如果二级索引主键能覆盖查询，则可以避免二次查询。
覆盖索引解决了以上的问题，其使得索引更加高效。
在MySQL中只能使用B-Tree索引做覆盖索引，而其他的如哈希索引，空间索引，全文索引等都不能存储索引列的值。
当发起一个被索引覆盖的查询时，这个索引就成为了一个覆盖索引。
可以使用索引扫描来排序（explain出来的type列的值为“index”时，说明使用了索引扫描来做排序，注意与覆盖索引作区别）
在利用order by来对数据来排序时，order by的子句顺序必须和索引列的顺序保持一致，否则无法利用索引对结果排序。当进行多表关联查询时，order by子句引用字段全部为第一个表时，才能使用索引作排序，即必须满足最左前缀的要求。
（前缀）压缩索引
通过压缩索引可以来减少索引的大小，从而使得更多的索引可放入内存中。
MyISAM的压缩索引块的方法：先完全保存索引块中的第一个值，然后将其他的值和第一个值进行比较得到相同前缀的字节数和剩余的不同的后缀部分，把这部分存储起来即可。
压缩索引在使用更少的空间的同时，也会使得某些操作（如查询等）变得更慢。
冗余和重复索引
重复索引：是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免创建重复索引。（主键其实就是一个索引，应该避免重复的在主键 上创建其他索引）
冗余索引：如创建了索引（A,B），再创建索引（A）就属于冗余索引，因为索引（A）是索引（A,B）的一个前缀索引。我们同时也应该避免冗余索引。
未使用的索引
顾名思义，我们也应当删除未使用的索引，减少数据库索引的维护工作。
索引和锁
索引可以让查询锁定更少的行。
在InnoDB中，只有在访问行的时候才会对行进行加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。
避免使用多个范围条件查询
对于多个范围条件查询，MySQL无法再使用范围列后面的其他索引，但对于“多个等值条件的查询”则没有这个限制。

最后，选择索引和编写利用这些索引的查询时，有三大原则：
1.单行访问时很慢的。最好读取的块中能包含尽可能多的所需要的行，这使用索引可以创建位置引用来提升效率。
2.按顺序访问范围数据是很快的。因为顺序的I/O不需要多次磁盘寻道，并且顺序的读取数据后就不需要额外的排序操作。
3.索引覆盖查询是很快的。覆盖索引不需要回表查找行，避免了大量的单行访问。

拖了很久才看完这章，期间各种各样的事很烦，但并没有影响学习，这一章就学到这，知识点真的很多，可见索引对数据库来说真的挺重要的。