尽管咱们不是DBA,但是,面试中被问到MySQL慢查询的概率还是非常高的。
说你没有经历过就是理由吗?显然不是。
一般来说一句SQL
语句执行超过5s
就能够算是慢SQL
,需要进行优化了。
1.为何要对慢SQL进行治理
每一个SQL都需要消耗一定的I/O资源,SQL执行的快慢直接决定了资源被占用时间的长短。假设业务要求每秒需要完成100条SQL的执行,而其中10条SQL执行时间过长,从而导致每秒只能完成90条SQL,所有新的SQL将进入排队等待,直接影响业务,然后用户就各种投诉来了。
2.治理的优先级
- master数据库->slave数据库 采用读写分离架构,读在从库slave上执行,写在主库master上执行。但由于从库的数据都是在主库复制过去的,主库如果等待较多的情况,会加大从库的复制延时
- 执行SQL次数多的优先治理
- 某张表被高并发集中访问的优先治理
3.MySQL执行原理
为了更好的优化慢SQL,我们来简单了解下MySQL的执行原理
SQL实际执行部分,主要分为两步:
- 解析:词法解析->语法解析->逻辑计划->
查询优化
->物理执行计划,过程中会检查缓存是否可用,如果没有可用缓存则进入下一步mysql_execute_command执行
- 执行:检查用户、表权限->表加上共享读锁->取数据到query_cache->取消共享读锁
4.如何发现慢查询SQL
除了sql的方式,我们也可以在配置文件(my.ini)中修改,加入配置时必须要在[mysqld]后面加入
特别注意:开启慢查询会带来CPU损耗与日志记录的IO开销,所以建议间断性的打开慢查询日志来观察MySQL运行状态
5.慢查询分析示例
假设我们有一条SQL
执行时间为1.163s,而我们设置的慢查询时间为1s,这时我们可以打开慢查询日志进行日志分析:
如果我们的慢SQL很多,人工分析肯定分析不过来,这时候我们就需要借助一些分析工具,MySQL自带了一个慢查询分析工具mysqldumpslow,以下是常见使用示例
6.SQL语句常见优化
只要简单了解过MySQL内部优化机制,就很容易写出高性能的SQL
不使用子查询
在MySQL5.5版本中,内部执行计划器是先查外表再匹配内表,如果外表数据量很大,查询速度会非常慢。
再MySQL5.6中,有对内查询做了优化,优化后SQL如下
但也仅针对select语句有效,update、delete子查询无效,所以生成环境不建议使用子查询
避免函数索引
即使d字段有索引,也会全盘扫描,应该优化为:
使用IN替换OR
非聚簇索引走了3次,使用IN之后只走一次:
LIKE双百分号无法使用到索引
应优化为右模糊
增加LIMIT M,N 限制读取的条数
避免数据类型不一致
应优化为:
分组统计时可以禁止排序
默认情况下MySQL会对所有GROUP BY co1,col2 …的字段进行排序,我们可以对其使用
禁止排序,避免排序消耗资源
去除不必要的ORDER BY语句
7.总结
总的来说,我们知道曼查询的SQL后,优化方案可以做如下尝试:
- SQL语句优化,尽量精简,去除非必要语句
- 索引优化,让所有SQL都能够走索引
- 如果是表的瓶颈问题,则分表,单表数据量维持在1000W(理论上)以内
- 如果是单库瓶颈问题,则分库,读写分离
- 如果是物理机器性能问题,则分多个数据库节点