mysql性能优化之慢查询日志分析

34r2312

打开慢查询日志
在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语句记录到慢查询日志文件

查看慢查询日志
默认情况下，在数据库目录下，在数据库运行时，可动态观察
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操作，这能使优化器和索引更好的工作。