6.4索引优化排序

mysql获取顺序数据可以通过以下两种方式

• 通过排序操作

• 利用B-tree索引的数据结构特点，按照索引顺序扫描数据

如何判断获取顺序数据时使用的是什么方式？

当使用explain + 查询语句时，输出结果的type为index，则说明使用的是索引扫描

当使用explain + 查询语句时，输出结果的Extra不存在Using filesort，则也说明使用的是索引扫描

注意：输出结果的type不为index，并不能认为就没有使用索引扫描，需要综合第二条原则判断

按索引顺序扫描数据

由于B-tree索引使用B+树为数据结构，同时节点中数据是按键值大小排序存放的，所以可以使用B-tree索引来获取顺序数据，由于其只需要顺序读取数据，而不需要排序操作，速度是很快的。但想使用索引顺序扫描数据的条件十分苛刻，必须满足：

• 索引的列顺序和Order By子句的顺序完全一致

• 索引中所有列的方向（升序、降序）和Order by子句完全一致

• 在关联表查询中，Order by中的字段全部在关联表中的第一张表中

实验：

实验一：索引的列顺序和Order By子句的顺序完全一致，才可使用索引顺序扫描

不是特别理解，是指若存在联合索引，Order By子句使用多个字段综合排序时，字段的顺序必须和联合索引的字段顺序一致么?

觉得视频中用于讲解的例子也不是很合适，例子如下：

show create table rental;

UNIQUE KEY 'rental_date' ('rental_date','inventory_id','customer_id');

使用rental表来做测试，开始之前，你可以使用show create table rental;来查看表结构

rental是使用InnoDB存储引擎的表，对于InnoDB存储引擎的表数据的逻辑顺序和主键顺序是一致的。故当使用主键排序时，可以验证条件一，返回结果的type应该为index

explain select * from rental where rental_date>'2005-01-01' order by rental_id;\G

rental_myisam表和rental表结构一样，可使用show create table rental_myisam;查看

但rental_myisam是使用Myisam存储引擎的表，相同的查询语句，返回结果却是type:All和Extra:using where;Using filesort

explain select * from rental where rental_date>'2005-01-01' order by rental_id;\G

实验二：索引中所有列的方向（升序、降序）和Order by子句完全一致，才可使用索引顺序扫描

使用rental表来做测试，rental表中存在如下联合索引

UNIQUE KEY 'rental_date' ('rental_date','inventory_id','customer_id');

使用二级索引来进行排序

explain select * from rental where rental_date='2005-05-09' order by inventory_id,customer_id\G

输出的结果Extra不存在Using filesort，故使用的是索引扫描数据方式

将inventory_id改为降序排序

explain select * from rental where rental_date='2005-05-09' order by inventory_id desc,customer_id\G

输出的结果Extra:using where;Using filesort，使用的是排序算法

补充：在联合索引中，左边的列一旦使用可范围查找，右边的列索引就会失去作用

explain select * from rental where rental_date>'2005-05-09' order by inventory_id,customer_id\G

输出的结果Extra:using where;Using filesort，使用的是排序算法

实验三：在关联表查询中，Order by中的字段全部在关联表中的第一张表中

请自行设计实验验证

使用B-tree索引模拟Hash索引

mysql对于B-ree索引的长度是有限制的，如果想要在一个值为很长的字符串的字段上使用索引查看，就只能使用前缀索引，但使用前缀索引选择性会减小。

而想要不减小选择性，我们只能使用Hash索引或者使用B-tree索引模拟Hash索引

模拟Hash索引实例如下：

我们使用film表来做实验

alter table film add title_md5 varchar(32);
update film set title_md5=md5(title);
create index idx_md5 on film(title_md5);
explain select * from film where title_md5=md5('EGG IGBY') and title ='EGG IGBY'\G

提示：查询时还使用了title ='EGG IGBY'作为过滤条件，是为了防止Hash冲突

模拟Hash索引的局限性

• 只能处理键值的全匹配查找

• 所使用的Hash函数决定着索引键的大小

使用Hash函数生成的索引键值太大，就会造成索引比较大的情况；使用Hash函数生成的索引键值太小，又会造成索引冲突的情况；