1. 全字段排序
疑问1: explain 后, 各个字段代表啥意思?
答: https://blog.csdn.net/bingguang1993/article/details/98313390
1.sort_buffer : mysql给每个线程分配一块内存用于排序
2.执行计划:
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_Y
6.对sort_buffer中的数据按照字段name 做快速排序
7.取快排结果前1000行返回客户端.
3.参数 sort_buffer_size ==> 为程序开辟的内存大小
数据量 < sort_buffer_size ==> 在内存中完成
数据量 > sort_buffer_size ==> 磁盘临时文件排序
4.有方法可以确定排序是否使用了临时文件:
1. number_of_tmp_files ==> 使用了几个临时文件 ==> 归并排序
疑问2 : 为什么 sort_buffer_size 越小, number_of_tmp_files 就越大?
难道 是总数据量 / sort_buffer_size = number_of_tmp_files ?
2.examined_rows ==> 检查行数 ==> 参与排序的行数
3. internal_tmp_disk_storage_engine 设置成 MyISAM (为了避免干扰)
4.因为查OPTIMIZER_TRACE 要用到临时表,如果使用innodb , 把数据从临时表取出来, 会让 innodb_rows_read 加1
疑问3 : 什么场景会用到临时表 ?
疑问4 : 内存临时表和磁盘临时表有什么不同 ?
灵光: 其实临时表就是临时文件, 因为表就是以文件形式储存的.
2.row id排序
1.全字段排序缺点:
1如果查询要返回的字段很多, sort_buffer中要放的字段太多, 需要很多临时文件, 性能差.
2单行长度太大 , 性能差
2.max_length_for_sort_data 如果单行长度大于该值 则使用row id 排序(单位为char)
3.执行计划:
1与全字段排序不同点:
1.只把 id , name 放入sort_buffer中
2. 最后多了一次回表, 取目标name, city , age
因为需要查询的字段长度太大,所以使用这个方式. 机智啊!
2. number_of_tmp_files 变成10 , 变小了. 因为每一行 从name ,city ,age 变成了 id , name, 长度小了
3. 全字段排序 VS row id 排序 :
1.优先选择全字段排序, 因为少一次回表.
设计思想: 如果内存够, 尽量使用内存, 不得已再使用磁盘.
2. 使用临时表的原因是 , 原来的数据是无序的.
==> 如果保证从city所以中取出的行, 是按照name排序的. 就不用再排序.
==> 所以可以使用联合索引
4更快的方法
1.联合索引
执行计划:
1.从索引(city, name)找到第一个满足 city = ' 杭州'条件的主键id
2.到主键id索引取出整行, 取name, city, age 三个字段的值, 返回
3. 从索引 (city, name) 去下一个记录主键id
4.重复步骤2, 3 , 直到查到1000条记录, 或者是不满足city = '杭州'条件.
4.覆盖索引 ==> 性能最高, 但是要权衡索引维护代价
==>建立索引(city, name, age)
执行计划:
1.从索引(city, name, age)找到第一个满足 city = ' 杭州'条件的主键id, 取name, city, age 三个字段的值, 返回
2. 从索引 (city, name, age) 取下一个记录, 从索引中取出三个值.
3.重复步骤2 , 直到查到1000条记录, 或者是不满足city = '杭州'条件.
