一、慢查询日志 开启撒网模式开启了MySQL慢查询日志之后,MySQL会自动将执行时间超过指定秒数的SQL统统记录下来,这对于搜罗线上慢SQL有很大的帮助。SHOW VARIABLES LIKE 'slow%' 以我刚安装的mysql5.7为例 查询结果是这样子的: slow_launch_time:表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加 slow_query_log:是否开启慢查询日志 ON开启,OFF关闭 默认没有开启 slow_query_log_file:日志保存路径 SHOW VARIABLES LIKE 'long%' long_query_time:达到多少秒的sql就记录日志 客户端可以用set设置变量的方式让慢查询开启,但是个人不推荐,因为真实操作起来会有一些问题,比如说,重启MySQL后就失效了,或者是开启了慢查询,我又去改变量值,它就不生效了。 编辑MySQL的配置文件: vim /etc/my.cnf 加入如下三行: slow_query_log=ON 我这里设置的是3秒 重启MySQL systemctl restart mysqld; 服务器开一个监控: tail -f /var/lib/mysql/localhost-centos-slow.log 客户端走一条SQL: SELECT SLEEP(3) 此时发现sql已经被记录到日志里了。(有时候不一定,我看到很多博客讲的是超过指定秒数,但我实验得出的结果是达到指定秒数) 二、EXPLAIN 点对点分析你explain是一个神奇的命令,可以查看sql的具体的执行计划。以一条联查sql为例: SELECT a.id,a.cn_name,a.role_id,r.name 查询结果是: 加上explain命令来执行: EXPLAIN 查询结果是: 这就是这条SQL的执行计划,下面来说明一下这个执行计划怎么看
三、优化争议无数的count( )统计列与统计行?COUNT( )是一个特殊的函数,有两种不同的作用,它可以统计某个列值的数量,也可以统计行数。 在统计列值的时候要求列值是非空的,也就是不统计null。 当我们统计行的时候,常见的是COUNT(*),这种情况下,通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数 解密MyiSAM的‘快’这是一个容易产生误解的事情:MyiSAM的count( )函数总是非常快。 不过它是有前提条件的,条件是没有任何where条件的count(*)才非常快,因为此时无须实际的去计算表的行数,mysql可以利用存储引擎的特性直接获得这个值,如果mysql知道某列不可能有null值,那么mysql内部会将count(列)表达式优化为count(*)。 当统计带有where条件的查询,那么mysql的count( )和其他存储引擎就没有什么不同了。 COUNT(1)、COUNT(*)、COUNT(列)(先提前申明,本人是在innodb库里做的实验。)
四、优化order by 语句MySQL的排序方式优化order by语句就不得不了解mysql的排序方式。
filesort的优化了解了MySQL排序的方式,优化目标就清晰了:尽量减少额外的排序,通过索引直接返回有序数据。where条件和order by使用相同的索引。
五、优化group by 语句为什么order by后面不能跟group by ?事实上,MySQL在所有的group by 后面隐式的加了order by ,也就是说group by语句的结果会默认进行排序。 如果你要在order by后面加group by ,那结果执行的SQL是不是这样:select * from tb order by ... group by ... order by ... ? 这不是搞笑吗? 禁止排序既然知道问题了,那么就容易优化了,如果查询包括group by但又不关心结果集的顺序,而这种默认排序又导致了需要文件排序,则可以指定order by null 禁止排序。 例如: select * from tb group by name order by null; 六、优化limit 分页一个非常常见又非常头痛的场景:‘limit 1000,20’。 这时MySQL需要查询1020条记录然后只返回最后20条,前面的1000条都将被抛弃,这样的代价非常高。如果所有页面的访问频率都相同,那么这样的查询平均需要访问半个表的数据。 第一种思路 在索引上分页在索引上完成分页操作,最后根据主键关联回原表查询所需要的其他列的内容。例如: SELECT * FROM tb_user LIMIT 1000,10 可以优化成这样: SELECT * FROM tb_user u 第二种思路 将limit转换成位置查询这种思路需要加一个参数来辅助,标记分页的开始位置: SELECT * FROM tb_user WHERE id > 1000 LIMIT 10 七、优化子查询子查询,也就是查询中有查询,常见的是where后面跟一个括号里面又是一条查询sql 尽可能的使用join关联查询来代替子查询。 当然 这不是绝对的,比如某些非常简单的子查询就比关联查询效率高,事实效果如何还要看执行计划。 只能说大部分的子查询都可以优化成Join关联查询。 八、改变执行计划提高索引优先级use index 可以让MySQL去参考指定的索引,但是无法强制MySQL去使用这个索引,当MySQL觉得这个索引效率太差,它宁愿去走全表扫描。。。 SELECT * FROM tb_user USE INDEX (user_name) 注意:必须是索引,不能是普通字段,(亲测主键也不行)。 忽略索引ignore index 可以让MySQL忽略一个索引 SELECT * FROM tb_user IGNORE INDEX (user_name) WHERE user_name='张学友' 强制使用索引force index 使用了force index 之后 尽管效率非常低,MySQL也会照你的话去执行 SELECT * FROM tb_user FORCE INDEX (user_name) WHERE user_name='张学友' 九、个人分享查看执行计划时建议依次观察以下几个要点:
PS:一定要养成查看执行计划的习惯,这个习惯非常重要。
完 |
|