前言
最新的 Java 面试题,技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等,会持续更新。
如果对老铁有帮助,帮忙免费点个赞,谢谢你的发财手!
Mysql支持两种方式的排序
一、索引排序
索引排序Using index是指MySql扫描索引本身完成排序,效率比文件排序高。
Order by 满足两种情况会使用Using index:
- 1、order by语句使用索引最左前列;
- 2、使用where和order by语句,条件列组合满足索引最左前列。
二、文件排序
1、单路排序
是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;
select * from employees where name = 'zhuge' order by age;
详细过程:
- 1、从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id;
- 2、根据主键 id 取出要查询字段的值,存入 sort_buffer 中;
- 3、从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id;
- 4、重复步骤 2、3 直到不满足 name = ‘zhuge’;
- 5、对 sort_buffer 中的数据按照字段 age进行排序;
- 6、返回结果给客户端。
2、双路排序
又叫回表排序模式,只取出满足条件行的相应排序字段和主键 ID,然后在 sort buffer 中进行排序,排序完后,再通过回表获取其它需要的字段;
详细过程:
- 1、从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id;
- 2、根据主键 id 取出整行,只把排序字段 age和主键 id 这两个字段放到 sort buffer 中;
- 3、从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id;
- 4、重复 3、4 直到不满足 name = ‘zhuge’;
- 5、对 sort_buffer 中的字段 age和主键 id 按照字段 age进行排序;
- 6、遍历排序好的 id 和字段 age,按照 id 的值回到原表中取出 要查询的字段的值返回给客户端。
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式:
如果 字段的总长度小于max_length_for_sort_data ,那么使用 单路排序模式;
如果 字段的总长度大于max_length_for_sort_data ,那么使用 双路排序模∙式。
- 其实对比两个排序模式,单路排序会把所有需要查询的字段都放到 sort buffer 中,而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序,然后再通过主键回到原表查询需要的字段。
- 如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了,可以适当把max_length_for_sort_data 配置小点,让优化器选择使用双路排序算法,可以在sort_buffer 中一次排序更多的行,只是需要再根据主键回到原表取数据。
- 如果 MySQL 排序内存有条件可以配置比较大,可以适当增大 max_length_for_sort_data 的值,让优化器优先选择全字段排序(单路排序),把需要的字段放到 sort_buffer 中,这样排序后就会直接从内存里返回查询结果了。
- 所以,MySQL通过 max_length_for_sort_data 这个参数来控制排序,在不同场景使用不同的排序模式,从而提升排序效率。
如果全部使用sort_buffer内存排序,一般情况下效率会高于磁盘文件排序,但不能因为这个就随便增大sort_buffer(默认1M),mysql很多参数设置都是做过优化的,不要轻易调整。
三、索引设计原则
- 1、代码先行,索引后上
一般应该等到主体业务功能开发完毕,把涉及到该表的相关sql都要拿出来分析之后,再建立索引。 - 2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引),让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段,还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。 - 3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值,比如性别字段,其值不是男就是女,那么该字段的基数就是2。
一般建立索引,尽量使用那些基数比较大的字段,就是值比较多的字段(除以总行数>75%),那么才能发挥出B+树快速二分查找的优势来。 - 4、长字符串我们可以采用前缀索引
对于这种varchar(255)的大字段可能会比较占用磁盘空间,可以稍微优化下,比如针对这个字段的前20个字符建立索引,就是说,对这个字段里的每个值的前20个字符放在索引树里,类似于 KEY index(name(20),age,position)。
此时你在where条件里搜索的时候,如果是根据name字段来搜索,那么此时就会先到索引树里根据name字段的前20个字符去搜索,定位到之后前20个字符的前缀匹配的部分数据之后,再回到聚簇索引提取出来完整的name字段值进行比对。
但是假如你要是order by name,那么此时你的name因为在索引树里仅仅包含了前20个字符,所以这个排序是没法用上索引的,group by也是同理。 - 5、where与order by冲突时优先where
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据,接着再进行排序。
因为大多数情况基于索引进行where筛选出最少的数据,然后做排序的成本可能会小很多。 - 6、基于慢sql查询做优化
可以根据监控后台的一些慢sql,针对这些慢sql查询做特定的索引优化。
总结
都已经看到这里啦,赶紧收藏起来,祝您工作顺心,生活愉快!