打开慢查询日志
在my.cnf置文件中修改
log-slow-queries = 日志文件路径
(注:log-slow-queries在未来的版本将被删除,尽量使用slow-query-log-file 重启服务后会出现warning警告 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.)
打开慢查询日志
long_query_time
设一个阀值,要大于这个值才会记录,等于该值时不记录。
log_queries_not_using_indexes
如果运行的SQL语句没有使用索引,则MySQl数据库同样会将这条SQL语句记录到慢查询日志文件
show variables like "%slow%";
查看是否开启慢查询
show variables like "%long%";
慢查询时间为几秒
查看慢查询日志
默认情况下,在数据库目录下,在数据库运行时,可动态观察
tail -f slow.log
……
# Time: 110523 9:58:35 时间
# User@Host: grid[grid] @ [203.100.192.66] 连接信息
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 4815
查询时间 锁时间 返回行数 总共查询行数
select count(*) from table_name where ……
利用mysqldumpslow分析慢查询日志
mysqldumpslow -s c -t 10 输出记录次数最多的10条SQL语句
mysqldumpslow -s r -t 10 返回记录集最多的10个查询
mysqldumpslow -s t -t 10 -g 'left join' 按照时间排序的前10条里面含有左连接的查询语句
-s, 是表示按照何种方式排序
c 记录次数、t 时间、l 查询时间、r 返回的记录数,ac、at、al、ar,表示相应的倒叙;
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
-s t | 按总query time排序 | -s at | 按平均query time排序 |
-s l | 按总locktime排序 | -s al | 按平均lock time排序 |
-s s | 按总row send排序 | -s as | 按平均row send排序 |
-s c | 按count排序 |
注意:在默认情况下
mysqlslowdump的输出结果会使用N和S代替SQL中出现的数字和字符串
mysqlslowdump输出结果是按照count(SQL出现的次数)排序的
mysqldumpslow结果分析
# mysqldumpslow -s c -t 10 slowquery.log
Reading mysql slow query log from slowquery.log
Count: 147973 Time=4.64s (686449s) Lock=0.34s (51032s) Rows=1.0 (147687), grid[grid]@[203.100.192.66]
select count(*) from table_name where ((newsT = 'S' and …………用S代表字符串
平均执行147973次,每次耗时4.64秒
分析问题
show create table table_name 查看表结构
分析问题 查看当期表都有哪些索引
mysql> show index from t \G
*************************** 1. row ***************************
Table: t 索引所在的表名
Non_unique: 0 非唯一索引,0代表唯一,可以看到主键名字是PRIMARY,因此必须唯一。
Key_name: PRIMARY 索引的名称,可以通过这个名称来DROP INDEX
Seq_in_index: 1 索引中该列的位置(注意理解是“索引中”),参考联合索引就容易理解了。
Column_name: a 索引的列
Collation: A 列以什么方式存储在索引中,可以是A或者NULL。B+树索引总是A,即排序的。如果使用了heap存储引擎,并建立了hash索引,这里就会显示NULL。因为hash根据hash桶来存放数据,而不是对数据进行排序。
Cardinality: 5 非常关键的值!!!表示索引中唯一值的数据的估计值。Cardinality值/表的行数,应尽可能接近1,如果非常小,那么考虑是否还需要这个索引???
Sub_part: NULL 是否是列的部分被索引,如果是整个列,则该字段为NULL
Packed: NULL 关键字如何被压缩,如果没有被压缩,则为NULL
Null: 是否索引的列含有NULL值。
Index_type: BTREE 索引的类型。
Comment: 注释
Index_comment:
Cardinality值(大概的值)非常关键,优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的,并非每次索引的更新都会更新该值,因为代价太大。
更新索引的Cardinality信息
mysql> analyze table t \G
*************************** 1. row ***************************
Table: test.t
Op: analyze
Msg_type: status
Msg_text: OK
1 row in set (0.04 sec)
注意:不是每个系统上都得到同样的结果,目前(MySQL5.1),analyze table还存在一些问题。
建议:在非高峰时间,对应用程序下的几张核心表做analyze table操作,这能使优化器和索引更好的工作。
转载于:https://blog.51cto.com/chen0825/1604042