MySQL系统调优及问题查找

性能优化相关状态参数

SHOW STATUS LIKE 'value';

connections 连接数

uptime 启动时间

show_queries 慢查询次数

com_select 查询操作次数

com_insert 插入操作次数

com_update 更新操作次数

com_delete 删除操作次数

www.2cto.com

分析查询语句

EXPLAIN/DESC SELECT;

禁用/启用索引

ALTER TABLE table DISABLE/ENABLE KEYS;

禁用唯一索引

SET UNIQUE_CHECK=0/1

分析、检查和优化表

ANALYZE TABLE table1 [,table2 ...]

CHECK TABLE table1[,table2 ...]

OPTIMIZE TABLE table1[,table2...]

分析SQL语句

explain select count(*), max(id), min(id) from user\G

通过explain分析SQL语句，获知执行情况

Profiling的使用

打开profiling 参数

>set profiling=1;

执行一些SQL语句后就可以查看query的profile 信息

>show profiles;

>show profiles cpu, blockio for query 6;

#查看query 6所使用的CPU IO资源情况

www.2cto.com

索引中的限制

1、MyISAM引擎索引长度总和不能超过1000字节

2、BLOB和TEXT类型的列只能创建前缀索引

3、MySql不支持函数索引

4、使用不等于( != 或 <>) 的时候无法使用索引

5、过滤字段使用了函数后（如: abs(column)) 无法使用索引

6、Join语句中Join条件字段类型不一致时，无法使用索引

7、使用Like操作的时候如果条件以通配符开始 ( '%abc...')无法使用索引

8、使用非等值查询时，无法使用hash索引

查询效率测试工具 mysqlslap

$ mysqlslap --create-schema=example --query="select * from group_message where user_id=3 AND subject like 'weiurezs%' --iterations=10000

#用于测试query的执行效率，给出平均、最大、最小执行时间。

FORCE INDEX(索引名称) 强制使用索引

EXPLAIN select * from group_message

FORCE INDEX(idx_group_message_author_subject)

where user_id=3 AND author='3' AND subject like 'weiurazs%'\g

性能调优——log设置

Mysql的log项有：错误日志、更新日志、二进制日志、查询日志、慢查询日志

Binlog

>show variables like '%binlog%';

慢查询

>show veriables like 'log_slow%';

>show variables like 'long_query%';

long_query_time最小值为1秒，如果需要进一步缩短慢查询的时间限制，可以使用Percona提供的microslow-path(http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-path-installation-walk-through/)

www.2cto.com

性能调优——Query Cache

查看Query Cache系统变量

>show variables like '%query_cache%';

了解Query Cache的使用情况

>show status like 'Qcache%';

cache命中率= Qcache_hits / (Qcache_hits + Qcache_inserts)

Qcache_hits / (Qcache_hits + Com_select) 应该更准确些

弊端： 1、Query语句的hash运算和查找资源增加CPU资源的消耗

2、Query Cache失效问题（当表的更新频繁时会造成非常高的失效率

3、Query Cache中缓存的Result Set, 而不是页面，可能造成内存的过度消耗，以及因内存不足造成过多的换入换出导致命中率的下降。

应对措施：

1、对那些经常更新的记录指定SQL_NO_CACHE的SQL Hint，强制MySQL不缓存。

2、对那些大部分时候都是静态的数据指定SQL_CACHE，使用CACHE。

3、对那些Result Set较大的的Query要么使用SQL_NO_CACHE,强制不使用CACHE，或者通过设置query_cache_limit参数来控制query中cache的最大Result Set，系统默认为1M，大于此设定值的Result Set将不会Cache。

Query Cache的限制

1、 5.1.17 之前的版本不能 Cache 帮定变量的 Query ，但是从 5.1.17 版本开始， Query Cache 已经开始支持帮定变量的 Query 了；

2、所有子查询中的外部查询 SQL 不能被 Cache ；

3、在 Procedure ， Function 以及 Trigger 中的 Query 不能被 Cache ；

4、包含其他很多每次执行可能得到不一样结果的函数的 Query 不能被 Cache 。

www.2cto.com

性能调优——其他常用优化

max_connections(最大连接数)：一般设置为 500—800左右

max_user_connections(每个用户允许的最大连接数)：一般不做限制

net_buffer_length(网络传输缓存)：默认16KB基本够用

thread_cache_size(Thread Cache池应该存放的连接线程数): 不应该小于应用系统对数据库实际并发请求数，一般50-100之间。对短连接效果很好。

MySQL系统调优及问题查找(一)