工作中遇到的99%SQL优化,这里都能给你解决方案(…
2019-09-17 10:21:59来源:博客园 阅读 ()
工作中遇到的99%SQL优化,这里都能给你解决方案(二)
-- 示例表
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(20) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=136326 DEFAULT CHARSET=utf8 COMMENT='员工表'
Order by与Group by优化
EXPLAIN select * from employees WHERE name='LiLei' and position='dev' order by age;
利用最左前缀法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序的过程中,因为Extra字段里没有using filesort。
EXPLAIN select * from employees WHERE name='LiLei' order by position;
从explain的执行结果来看:key_len=74, 查询使用name索引,由于用了position进行排序,跳过了age,出现了Using filesort。
EXPLAIN select * from employees WHERE name='LiLei' order by age,position;
查找只用到了name索引,age和position用于排序,无Using filesort。
EXPLAIN select * from employees WHERE name='LiLei' order by position,age;
和上一个case不同的是,Extra中出现了Using filesort,因为索引的创建顺序为name,age,position,但是排序的时候age和position颠倒了位置。
EXPLAIN select * from employees WHERE name='LiLei' order by age asc, position desc;
虽然排序的字段和联合索引顺序是一样的,且order by是默认升序,这里position desc是降序,导致与索引的排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
EXPLAIN select * from employees WHERE name in('LiLei', 'zhuge') order by age, position ;
对于排序来说,多个相等条件也是范围查询。
EXPLAIN select * from employees WHERE name > 'a' order by name;
可以用覆盖索引优化
EXPLAIN select name,age,position from employees WHERE name > 'a' order by name;
filesort排序
EXPLAIN select * from employees where name='LiLei' order by position;
查看这条sql对应trace结果(只展示排序部分):
set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
select * from employees where name = 'LiLei' order by position;
select * from information_schema.OPTIMIZER_TRACE;
{
"join_execution": { --sql执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --文件排序信息
"rows": 1, --预计扫描行数
"examined_rows": 1, --参与排序的行
"number_of_tmp_files": 0, --使用临时文件的个数,这个值为0代表全部使用sort_buffer内存排序,否则使用磁盘文件排序
"sort_buffer_size": 200704, --排序缓存的大小
"sort_mode": "<sort_key, additional_fields>" --排序方式,这里用的单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
修改max_length_for_sort_data=10
set max_length_for_sort_data = 10; --employees表所有字段长度总和肯定大于10字节
select * from employees where name = 'LiLei' order by position;
select * from information_schema.OPTIMIZER_TRACE;
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 1,
"examined_rows": 1,
"number_of_tmp_files": 0,
"sort_buffer_size": 53248,
"sort_mode": "<sort_key, rowid>" --排序方式为双路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
对比这两个排序模式,单路排序会把所有的需要查询的字段数据都放到sort_buffer中,而双路排序只会把主键id和需要排序的字段放到sort_buffer中进行排序,然后再通过主键id 回到原表 查询需要的字段数据。MySQL通过max_length_for_sort_data这个参数来控制排序,在不同场景下使用不同的排序模式,从而提升排序效率。
优化总结
- Mysql支持两种方式的排序filesort和index,using index是指Mysql扫描索引本身完成排序。index效率高,filesort效率低。
- order by满足两种情况会使用using index。
order by语句使用索引最左前列。
使用where子句和order by子句 条件列组合满足索引最左前列。 - 尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时候的最左前缀法则。
- 如果order by 的条件不在索引列上,就会产生using filesort。
还没关注我的公众号?
- 扫文末二维码关注公众号【小强的进阶之路】可领取如下:
- 学习资料: 1T视频教程:涵盖Javaweb前后端教学视频、机器学习/人工智能教学视频、Linux系统教程视频、雅思考试视频教程;
- 100多本书:包含C/C++、Java、Python三门编程语言的经典必看图书、LeetCode题解大全;
- 软件工具:几乎包括你在编程道路上的可能会用到的大部分软件;
- 项目源码:20个JavaWeb项目源码。
原文链接:https://www.cnblogs.com/xiaoqiang-code/p/11495359.html
如有疑问请与原作者联系
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- 面试遇到“一问三不知”的,真替他老东家捏把汗 2020-06-05
- Java 给PDF文档添加页码 2020-05-29
- “JVM性能调优解析”,查缺补漏JVM进阶面试题77问.你都遇到过 2020-05-25
- 实现多线程的同步 2020-04-19
- 记录一个在工作中遇到的replace和replaceall的问题:java.ut 2020-04-07
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash