MySQL 调优

如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住如何来处理?

是周期性的变化还是偶尔问题?是服务器整体性能的问题, 还是某单条语句的问题?

具体到单条语句, 这条语句是在等待上花的时间,还是查询上花的时间?

1.1. 监测并观察服务器的状态.

观察服务器状态, 一般用如下 2 个命令

Show status;
Show processlist;

例: mysql> show status;

   #mysqladmin ext

1.2. MySQL 的每秒请求数周期性变化,随着缓存失效,有短时间的高峰

解决办法:

1: 减少无关请求(业务逻辑层面,暂不讨论,但其实是最有效的手段)

2: 如果请求数是一定的,不可减少的. 我们要尽量让请求数平稳,不要有剧烈波动.

很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.

1.3.对于不规则的延迟现象的观察

不规则的延迟现象往往是由于效率低下的语句造成的,如何抓到这些效率低的语句.

可以用 show processlist 命令长期观察,或用慢查询.

Show processlist;

这个命令是显示当前所有连接的工作状态.

#!/bin/bash
while true
do
mysql -uroot -e 'show processlist\G'|grep State:|uniq -c|sort -rn
echo '---'
sleep 1
Done

如果观察到以下状态,则需要注意

converting HEAP to MyISAM     查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)

create tmp table                         创建临时表(如 group 时储存中间结果,说明索引建的不好)

Copying to tmp table on disk   把内存临时表复制到磁盘 (索引不好,表字段选的不好)

locked                                         被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生)

logging slow query                   记录慢查询

1.4. mysql 5.5 以后加了一个 profile 设置,可以观察到具体语句的执行步骤.

查看 profile 是否开启

Show  variables like ‘profiling’

 开启 profile

 set profiling=on;

查看 profiles;

show profiles;

查看 profile;

show profile for query 1;

2. 如何定位到有问题的语句?

• 开启服务器慢查询

• 了解临时表的使用规则      

2.1. MySQL 如何使用内部临时表

在处理请求的某些场景中,服务器创建内部临时表. 即表以 MEMORY 引擎在内存中处理,或以 MyISAM 引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上.

用户不能直接控制服务器内部用内存还是磁盘存储临时表

2.2. 临时表在如下几种情况被创建:

如果 group by 的列没有索引,必产生内部临时表,

如果 order by 与 group by 为不同列时,或多表联查时 order by ,group by 包含的列不是第一张表的列,将会产生临时表

distinct 与 order by 一起使用可能会产生临时表

如果使用 SQL_SMALL_RESULT,MySQL 会使用内存临时表,除非查询中有一些必须要把临时表建立在磁盘上.

union 合并查询时会用到临时表

某些视图会用到临时表,如使用 temptable 方式建立,或使用 union 或聚合查询的视图

想确定查询是否需要临时表,可以用 EXPLAIN 查询计划,并查看 Extra 列,看是否有 Using temporary.

如果一开始在内存中产生的临时表变大,会自动转化为磁盘临时表. 内存中临时表的最大值为 tmp_table_size 和 max_heap_size 中较小值.

这和 create table 时显示指定的内存表不一样:这些表只受 max_heap_table_size 系统参数影响.

当服务器创建内部临时表(无论在内存还是在磁盘),create_tmp_tables 变量都会增加.

如果创建了在磁盘上内部临时表(无论是初始创建还是由 in-memory 转化),

create_tmp_disk_tables 变量都会增加.

一些情况下限制了内存临时表的使用,而使用磁盘临时表:

(使用了内部临时表的前提下) 语句中存在 BLOB 或 TEXT 列

在 GROUP BY 或 DISTINCT 子句中有大于 512 字节的 string 列

在 UNION 或 UNION ALL 时,SELECT 语句里有大于 512 字节的 string 列.