文中内容均为阅读前辈的文章所整理而来,参考文章已在最后全指明
本文分为上下两篇:
上篇:MySQL 的 SQL 执行分析下篇:MySQL 性能优化后端开发必然会接触到数据库,数据层的优劣会影响整个服务的响应时间。所以,数据库的优化技巧是必须掌握的,下面就是我在学习过程中整理的,备忘。
下面为上篇内容,分为以下部分:
一、sql 执行时间分析二、sql 执行情况分析一、sql 执行时间分析通过找到执行时间长的 sql 语句,可以直观的发现数据层的效率问题。
1.通过 show processList 来查看系统的执行情况MysqL> show processList;+----+------+-----------+------+---------+------+-------+------------------+| ID | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+-------+------------------+| 2 | root | localhost | NulL | query | 0 | init | show processList |+----+------+-----------+------+---------+------+-------+------------------+1 row in set (0.01 sec)
2.通过 profiling 来进行查看这个命令是查看 sql 的执行时间,能很直观的看出快慢。
2.1 查看 profiling 是否开启0 代表还是关闭着分析功能
MysqL> select @@profiling;+-------------+| @@profiling |+-------------+| 0 |+-------------+
2.2 打开工具MysqL> set profiling=1;query OK,0 rows affected,1 warning (0.01 sec)MysqL> select @@profiling;+-------------+| @@profiling |+-------------+| 1 |+-------------+
2.3 查看 sql 的执行时间MysqL> show profiles;+----------+------------+----------------------------+| query_ID | Duration | query |+----------+------------+----------------------------+| 1 | 0.00173700 | select * from ip || 2 | 0.00057500 | select porxy,port from ip |+----------+------------+----------------------------+
2.4 查看 sql 执行耗时详细信息语法:show profile for query query_ID
MysqL> show profile for query 1;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000073 || checking permissions | 0.000031 | ---检查是否在缓存中 | opening tables | 0.000207 | ---打开表| init | 0.000067 | ---初始化| System lock | 0.000040 | ---锁系统| optimizing | 0.000005 | ---优化查询| statistics | 0.000021 || preparing | 0.000015 | ---准备| executing | 0.000003 | ---执行| Sending data | 0.000993 || end | 0.000006 || query end | 0.000007 || closing tables | 0.000011 || freeing items | 0.000169 || cleaning up | 0.000089 |+----------------------+----------+
以上具体的信息都是从 informatION_SCHEMA.PROFIliNG 这张表中取得的。这张表记录了所有的各个步骤的执行时间及相关信息。语法:select * from informatION_SCHEMA.PROFIliNG where query_ID = query_ID;
MysqL 的慢查询日志,顾名思义就是把执行时间超过设定值(默认为10s)的 sql 记录到日志中。这项功能需要手动开启,但是开启后会造成一定的性能损耗。
3.1 查看慢日志是否开启默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,可以通过设置slow_query_log的值来开启。语法:set global slow_query_log=1
MysqL> show variables like '%slow_query_log%';+---------------------+------------------------------------------------------+| Variable_name | Value |+---------------------+------------------------------------------------------+| slow_query_log | OFF || slow_query_log_file | /usr/local/var/MysqL/xueweihandeMacBook-Air-slow.log |+---------------------+------------------------------------------------------+2 rows in set (0.11 sec)MysqL> set global slow_query_log=1;query OK,0 rows affected (0.03 sec)MysqL> show variables like '%slow_query_log%';+---------------------+------------------------------------------------------+| Variable_name | Value |+---------------------+------------------------------------------------------+| slow_query_log | ON || slow_query_log_file | /usr/local/var/MysqL/xueweihandeMacBook-Air-slow.log |+---------------------+------------------------------------------------------+
3.2 设置超时时间设置语法:set global long_query_time=4
查看语法:show variables like 'long_query_time'
注意:修改后,需要重新连接或新开一个会话才能看到修改值。
永久生效,修改 my.cnf
slow_query_log=1long_query_time=10slow_query_log_file=/path/MysqL_slow.log
3.3 其他参数3.3.1 log_output参数是指定日志的存储方式。log_output='file'表示将日志存入文件,默认值是'file'。log_output='table'表示将日志存入数据库,这样日志信息就会被写入到MysqL.slow_log表中。MysqL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='file,table'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
3.3.2 log-querIEs-not-using-indexes未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话,建议开启这个选项。另外,开启了这个参数,其实使用full index scan的sql也会被记录到慢查询日志。
3.3.3 log_slow_admin_statements表示是否将慢管理语句例如ANALYZE table和ALTER table等记入慢查询日志
3.4 分析工具 MysqLdumpslowMysqL 提供了慢日志分析工具 MysqLdumpslow。
-s
表示按照何种方式排序;c: 访问计数l: 锁定时间r: 返回记录t: 查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间-t
是top n的意思,即为返回前面多少条的数据;-g
后边可以写一个正则匹配模式,大小写不敏感的;3.4.1 命令示例得到返回记录集最多的 10 个 sql:MysqLdumpslow -s r -t 10 /database/MysqL/MysqL06_slow.log
得到访问次数最多的 10 个 sql:MysqLdumpslow -s c -t 10 /database/MysqL/MysqL06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句:MysqLdumpslow -s t -t 10 -g “left join” /database/MysqL/MysqL06_slow.log
另外建议在使用这些命令时结合 | 和 more 使用 ,否则有可能出现刷屏的情况:MysqLdumpslow -s r -t 20 /MysqLdata/MysqL/MysqL06-slow.log | more
使用 explain
分析 sql 执行情况。
explain select * from ip;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | ip | ALL | NulL | NulL | NulL | NulL | 400 | NulL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+
select_type | table | type | possible_keys | key | key_len | rows | Extra |
---|---|---|---|---|---|---|---|
表示查询的类型 | 输出结果集的表 | 表示表的连接类型 | 表示查询时,可能使用的索引 | 表示实际使用的索引 | 索引字段的长度 | 扫描出的行数(估算的行数) | 执行情况的描述和说明 |
以上是内存溢出为你收集整理的MySQL 的性能(上篇)—— SQL 执行分析全部内容,希望文章能够帮你解决MySQL 的性能(上篇)—— SQL 执行分析所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)