共计 2084 个字符,预计需要花费 6 分钟才能阅读完成。
问题
查问城市是“杭州”的所有人名字,并且依照姓名排序返回前 1000 集体的姓名、年龄。
建表语句:
CREATE TABLE `t` (`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
查问语句:
select city,name,age from t where city='杭州' order by name limit 1000 ;
全字段排序
1、初始化 sort_buffer,确定放入 name、city、age 这三个字段;
2、从索引 city 找到第一个满足 city=’ 杭州’条件的主键 id,也就是图中的 ID_X;
3、到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中;
4、从索引 city 取下一个记录的主键 id;
5、反复步骤 3、4 直到 city 的值不满足查问条件为止,对应的主键 id 也就是图中的 ID_Y;
6、对 sort_buffer 中的数据依照字段 name 做疾速排序;依照排序后果取前 1000 行返回给客户端。
rowid 排序
1、初始化 sort_buffer,确定放入两个字段,即 name 和 id;
2、从索引 city 找到第一个满足 city=’ 杭州’条件的主键 id,也就是图中的 ID_X;
3、到主键 id 索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中;
4、从索引 city 取下一个记录的主键 id;
5、反复步骤 3、4 直到不满足 city=’ 杭州’条件为止,也就是图中的 ID_Y;
6、对 sort_buffer 中的数据依照字段 name 进行排序;
7、遍历排序后果,取前 1000 行,并依照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
应用联结索引
alter table t add index city_user(city, name);
1、从索引 (city,name) 找到第一个满足 city=’ 杭州’条件的主键 id;
2、到主键 id 索引取出整行,取 name、city、age 三个字段的值,作为后果集的一部分间接返回;
3、从索引 (city,name) 取下一个记录主键 id;
4、反复步骤 2、3,直到查到第 1000 条记录,或者是不满足 city=’ 杭州’条件时循环完结。
应用笼罩索引
alter table t add index city_user_age(city, name, age);
1、从索引 (city,name,age) 找到第一个满足 city=’ 杭州’条件的记录,取出其中的 city、name 和 age 这三个字段的值,作为后果集的一部分间接返回;
2、从索引 (city,name,age) 取下一个记录,同样取出这三个字段的值,作为后果集的一部分间接返回;
3、反复执行步骤 2,直到查到第 1000 条记录,或者是不满足 city=’ 杭州’条件时循环完结。
小知识点
1、MySQL 会为每个线程调配一个内存(sort_buffer)用于排序该内存大小为 sort_buffer_size
如果排序的数据量小于 sort_buffer_size,排序将会在内存中实现
如果排序数据量很大,内存中无奈存下这么多数据,则会应用磁盘临时文件来辅助排序,也称内部排序
在应用内部排序时,MySQL 会分成好几份独自的临时文件用来寄存排序后的数据,而后在将这些文件合并成一个大文件
2、mysql 会通过遍历索引将满足条件的数据读取到 sort_buffer,并且依照排序字段进行疾速排序
如果查问的字段不蕴含在辅助索引中,须要依照辅助索引记录的主键返回汇集索引取出所需字段
该形式会造成随机 IO,在 MySQL5.6 提供了 MRR 的机制,会将辅助索引匹配记录的主键取出来在内存中进行排序,而后在回表
依照状况建设联结索引来防止排序所带来的性能损耗,容许的状况下也能够建设笼罩索引来防止回表
3、全字段排序
通过索引将所需的字段全副读取到 sort_buffer 中
依照排序字段进行排序
将后果集返回给客户端
长处:
MySQL 认为内存足够大时会优先选择全字段排序,因为这种形式比 rowid 排序防止了一次回表操作
毛病:
造成 sort_buffer 中寄存不下很多数据,因为除了排序字段还寄存其余字段,对 sort_buffer 的利用效率不高
当所需排序数据量很大时,会有很多的临时文件,排序性能也会很差
4、rowid 排序
通过管制排序的行数据的长度来让 sort_buffer 中尽可能多的存放数据,max_length_for_sort_data
只将须要排序的字段和主键读取到 sort_buffer 中,并依照排序字段进行排序
依照排序后的程序,取 id 进行回表取出想要获取的数据
将后果集返回给客户端
长处:
更好的利用内存的 sort_buffer 进行排序操作,尽量减少对磁盘的拜访
毛病:
回表的操作是随机 IO,会造成大量的随机读,不肯定就比全字段排序缩小对磁盘的拜访