高性能mysql(第2版)中文版 高性能mysql第五版epub

索引优化是数据库查询性能优化最有效的手段，索引能够轻易地将查询性能提高几个数量级。这本书的第五章就在讨论如何建立一个“最优”索引。

一、索引类型

在MySql中，索引是在存储引擎层而不是服务器层实现的。

B-Tree索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VodbXBPN-1632586056831)(E:\study\个人学习笔记\高性能MySQL\6.png)]

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么值不存在。

B-Tree索引适用的查询类型：

• 全值匹配 —— 和索引中的所有列进行匹配。例如查找Cuba Allen、出生于1960-01-01的人
• 匹配最左前缀 —— 和索引中的第一列进行匹配，只涉及第一列。例如查找所有姓为Allen的人
• 匹配列前缀 —— 和索引中第一列的开头部分进行匹配，只涉及第一列。例如查找所有以J开头的姓的人。
• 匹配范围值 —— 例如查找所有姓在Allen和Barrymore之间的人，只涉及第一列。
• 精确匹配某一列并范围匹配另外一列 —— 例如查找所有姓为Allen，并且名字以字母K开头的人。第一列全匹配，第二列部分匹配。
• 只访问索引的查询 —— 即查询只需要访问索引，而无须访问数据行。

B-Tree索引当然也有限制的查询类型：

• 如果不是按照索引的最左列开始查找，则无法使用索引
• 不能跳过索引中的列 —— 无法用于查找姓为Smith并且在某个特定日期出生的人，如果不指定名（first_name），则MySQL只能使用索引的第一列。
• 如果某个查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。

这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

哈希索引

哈希索引基于哈希表实现。在MySQL中，只有Memory引擎显示支持哈希索引。在MySQL中哈希索引解决哈希冲突的方法是拉链法（链表法）。

哈希索引的限制：

• 哈希索引不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引不支持部分索引列匹配查找，因为哈希索引始终使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值查询，不支持任何范围查询。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

InnoDB引擎有一个特殊的功能：“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

二、索引的优点

总结起来索引有如下三个优点：

• 索引大大减少了服务器需要扫描的数据量
• 索引（B-Tree索引）可以帮助服务器避免排序和临时表（ORDER BY和GROUP BY操作）
• 索引可以将随机I/O变为顺序I/O（某些查询只使用索引就能够完成全部查询）

三、高性能索引策略

1.独立的列

独立的列 指的是索引列不能是表达式的一部分，也不能是函数的参数。

举例：

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

这里就是把索引列写成了表达式，应该改为actor_id=4。

2.前缀索引和索引选择性

这里先说下，什么是索引的选择性。索引的选择性是指，不重复的索引值(也称为基数)和数据表的记录总数(T)的比值，范围从1/T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多行。

一般情况下，将某个列部分前缀作为索引，它的选择性已经是足够高了，足以满足查询性能。**特别是BLOB、TEXT和很长的VARCHAR类型的列，必须使用前缀索引。**但到底选择前多少位作为索引呢？既要极可能少，又要保证较高的选择性。

方法一：

首先使用查询语句，查出最常见的城市列表，它们的值基本出现了45到65次，然后查询包含前三个字母的前缀，发现这时候最常见的前十个出现的次数比刚才多，这就说明了唯一前缀要比唯一城市要少的多。这时候就需要继续增加前缀的位数。

直到增加到选取前7位作为前缀，发现此时前缀选择性接近了完整列的选择性，所以选择city列的前7列作为索引比较合适。

方法二：

计算完整列的选择性，使得前缀的选择性接近于完整列的选择性。完整列的选择性为0.0312，然后通过sql去计算不同前缀的选择性，可以发现前7列作为前缀时的选择性最接近于完整列。

通过下面的sql建立前缀索引：

ALTER TABLE sakila.city_demo ADD KEY(city(7));

3.选择合适的索引列顺序

在多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，然后是第二列。所以怎么来按照索引列就是个值得研究的问题。索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT等子句的查询需求。

以下面的查询为例：

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

所以staff_id和customer_id索引顺序应该怎么设计？从全局来考虑，看整体的两个索引列的选择性。

可以看出customer_id选择性更高，所以索引第一列应该为customer_id。

4.聚簇索引

这一小节内容比较重要了，我将其归纳总结了一下。首先要弄清什么是聚簇索引？

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据与索引不存储在一块，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找（存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行），非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

这里重点说一下InnoDB引擎（下图）：

1.InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。因为这里的where中查询条件就涉及主键Id

2.若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）。

聚簇索引的优势

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

1. 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2. **辅助索引使用主键作为"指针"而不是使用地址值作为指针，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。**也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
3. 聚簇索引适合用在排序的场合，非聚簇索引不适合
4. 取出一定范围数据的时候，使用用聚簇索引
5. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

1.维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片

2.表如果使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢。

3.如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

主键索引和聚簇索引区别

首先明确这两个不等同，这个在我刚接触数据库知识的时候，常常误以为主键索引就是聚簇索引，这个认知是错误的。

主键索引

1.表通常具有包含唯一标识表中每一行的值的一列或一组列。

2.一个表只能有一个 PRIMARY KEY 约束，并且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束可保证数据的唯一性，因此经常对标识列定义这种约束。

3.所选的主键必须遵守创建唯一索引的规则。

	主键	聚集索引
用途	强制表的实体完整性	对数据行的排序，方便查询用
一个表多少个	一个表最多一个主键	一个表最多一个聚集索引
是否允许多个字段来定义	一个主键可以多个字段来定义	一个索引可以多个字段来定义
是否允许 null 数据行出现	如果要创建的数据列中数据存在null，无法建立主键。 创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL。	没有限制建立聚集索引的列一定必须 not null . 也就是可以列的数据是 null 参看最后一项比较
是否要求数据必须唯一	要求数据必须唯一	数据即可以唯一，也可以不唯一。看你定义这个索引的 UNIQUE 设置。 （这一点需要看后面的一个比较，虽然你的数据列可能不唯一，但是系统会替你产生一个你看不到的唯一列）
创建的逻辑	数据库在创建主键同时，会自动建立一个唯一索引。 如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引	如果未使用 UNIQUE 属性创建聚集索引，数据库引擎 将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎 将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。

5.覆盖索引

如果一个索引已经包含了（覆盖了）所有需要查询的字段的值，就称之为“覆盖索引”。这种情况下，索引的叶子节点已经包含要查询的数据了，就没有必要再回表查询了。

好处：

1.索引条目通常远小于数据行的大小，极大地减少了数据访问量。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。

2.因为索引按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机磁盘读取查询的IO要少的多。

3.MyISAM在内存中只缓存索引，数据依赖于操作系统来缓存，如果通过覆盖索引来避免这些系统调用，就可以节约这些资源调度。

4.由于InnoDB的聚簇索引，覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

具体说明一下：

SELECT * FROM products WHERE actor='SEAN CARREY' AND title like'%APOLLO%'\G

索引是actor，但是无法覆盖该查询。有两个原因：

1.因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。

2.MySql不能再索引中执行LIKE操作。MySql能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE查询，存储引擎就无法作比较匹配。这种情况下，MySQl服务器只能提取数据行的值而不是索引值来做比较。

为了解决上面无法覆盖的问题，需要重新设计索引。

1.先将索引扩展至覆盖三个数据列(artist,titile,prod_id)

2.将SQL语句改写成：

SELECT * FROM products JOIN（
SELECT prod_id
FROM products
WHERE actor = 'SEAN CARREY' AND title like'%APOLLO%'
）AS t1 ON(t1.prod_id=products.prod_id)

这种方式叫做延迟关联，延迟了对列的访问。在查询的第一阶段MySql可以使用覆盖索引，在FROM子句的子查询中找到匹配的prod_id，然后根据这些prod_id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但比完全无法利用索引覆盖的好。

6.使用索引扫描来做排序

MySql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如何查看，EXPLAIN出来的tpye列的值为"index"，则说明MySql使用了索引扫描来做排序。

以下情况不能使用索引做排序的查询：

1.使用了两种不同的排序方向，但是索引列都是正序排序的：

WHERE rental_date='2005-05-25'ORDER BY inventory_id DESC,customer_id ASC;

2.查询的ORDER BY 子句中引用了一个不在索引中的列：

WHERE rental_date='2005-05-25'ORDER BY inventory_id ,staff_id;

3.查询的WHERE和ORDER BY 中的列无法组合成索引的最左前缀：

WHERE rental_date='2005-05-25'ORDER BY customer_id;

4.查询在索引列的第一列上是范围条件，所以MySql无法使用索引的其余列：

WHERE rental_date > '2005-05-25'ORDER BY inventory_id ,customer_id;

5.查询在inventory_id列上有多个等于条件。

WHERE rental_date='2005-05-25'AND inventory_id IN（1,2）ORDER BY customer_id；

四.案例学习

略，之后有空将书中这一块总结。
列上是范围条件，所以MySql无法使用索引的其余列：

WHERE rental_date > '2005-05-25'ORDER BY inventory_id ,customer_id;

5.查询在inventory_id列上有多个等于条件。

WHERE rental_date='2005-05-25'AND inventory_id IN（1,2）ORDER BY customer_id；

四.案例学习

略，之后有空将书中这一块总结。