江苏龙网

  1. 主页 > 生活百科 > >

MySQL性能优化实践

MySQL性能优化

一 题记
最近公司项目添加新功能，上线后发现有些功能的列表查询时间很久 。原因是新功能用到旧功能的接口，而这些旧接口的 SQL 查询语句关联5,6张表且编写不够规范，导致 MySQL 在执行 SQL 语句时索引失效，进行全表扫描 。原本负责优化的同事有事请假回家，因此优化查询数据的问题落在笔者手中 。笔者在查阅网上 SQL 优化的资料后成功解决了问题，在此从全局角度，记录和总结 MySQL 查询优化相关技巧 。
 
二、优化思路
数据查询慢，不代表 SQL 语句写法有问题 。 首先，我们需要找到问题的源头才能“对症下药” 。笔者用一张流程图展示 MySQL 优化的思路：

MySQL性能优化实践

文章插图
 
无需更多言语，从图中可以清楚地看出，导致数据查询慢的原因有多种，如：缓存失效，在此一段时间内由于高并发访问导致 MySQL 服务器崩溃；SQL 语句编写问题；MySQL 服务器参数问题；硬件配置限制 MySQL 服务性能问题等 。
三、查看 MySQL 服务器运行的状态值
如果系统的并发请求数不高，且查询速度慢，可以忽略该步骤直接进行 SQL 语句调优步骤 。
执行命令：
show status由于返回结果太多，此处不贴出结果 。其中，在返回的结果中，我们主要关注 “Queries”、“Threadsconnected” 和 “Threadsrunning” 的值，即查询次数、线程连接数和线程运行数 。
我们可以通过执行如下脚本监控 MySQL 服务器运行的状态值
#!/bin/bashwhile truedomysqladmin -uroot -p"密码" ext | awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %dn",q,c,r)}' >> status.txtsleep 1done执行该脚本 24 小时，获取 status.txt 里的内容，再次通过 awk 计算==每秒请求 MySQL 服务的次数==
awk '{q=$1-last;last=$1}{printf("%d %d %dn",q,$2,$3)}' status.txt复制计算好的内容到 Excel 中生成图表观察数据周期性 。
如果观察的数据有周期性的变化，如上图的解释，需要修改缓存失效策略 。
例如：
通过随机数在[3,6,9] 区间获取其中一个值作为缓存失效时间，这样分散了缓存失效时间，从而节省了一部分内存的消耗 。
当访问高峰期时，一部分请求分流到未失效的缓存，另一部分则访问 MySQL 数据库，这样减少了 MySQL 服务器的压力 。
 
四、获取需要优化的 SQL 语句
4.1 方式一：查看运行的线程执行命令：
show processlist返回结果：
mysql> show processlist;+----+------+-----------+------+---------+------+----------+------------------+| Id | User | Host      | db   | Command | Time | State    | Info             |+----+------+-----------+------+---------+------+----------+------------------+|  9 | root | localhost | test | Query   |    0 | starting | show processlist |+----+------+-----------+------+---------+------+----------+------------------+1 row in set (0.00 sec)从返回结果中我们可以了解该线程执行了什么命令/SQL 语句以及执行的时间 。实际应用中，查询的返回结果会有 N 条记录 。
其中，返回的 State 的值是我们判断性能好坏的关键，其值出现如下内容，则该行记录的 SQL 语句需要优化：
Converting HEAP to MyISAM # 查询结果太大时，把结果放到磁盘，严重Create tmp table #创建临时表，严重Copying to tmp table on disk  #把内存临时表复制到磁盘，严重locked #被其他查询锁住，严重loggin slow query #记录慢查询Sorting result #排序4.2 方式二：开启慢查询日志在配置文件 my.cnf 中的 [mysqld] 一行下边添加两个参数：
slow_query_log = 1slow_query_log_file=/var/lib/mysql/slow-query.loglong_query_time = 2log_queries_not_using_indexes = 1其中，slowquerylog = 1 表示开启慢查询；slowquerylogfile 表示慢查询日志存放的位置；longquerytime = 2 表示查询 >=2 秒才记录日志；
logqueriesnotusing_indexes = 1 记录没有使用索引的 SQL 语句 。


推荐阅读


上一篇：茶油树开花时的图片,桂花的品种及图片

下一篇：花茶是什么茶,桂花茶的功效与作用是什么