MYSQL系统参数调整（一）
一、概述
在做性能测试中经常会遇到一些sql的问题，其实做性能测试这几年遇到问题最多还是数据库这块，要么就是IO高要么就是cpu高，所以对数据的优化在性能测试过程中占据着很重要的地方，下面我就介绍一些msyql性能调优过程中经常用到的三件利器：
1、慢查询 （分析出现出问题的sql）
2、EXPLAIN （显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句）
3、Profile（查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.）
二、mysql监控方案
1、 开启慢查询
列出MySQL服务器运行各种状态值：
mysql> show global status;
查询mysql服务器配置信息：
mysql> show variables;
通过上面的结果可以发现，log_output的值为“FILE”,证明是输出到日志文件，
如果值为“TABLE”则将日志信息输出到默认“mysql”数据库中的相应日志表中（具体
为：mysql.slow_log），该表的默认引擎为CSV。
mysql> show variables like 'log_output';
--------------- -------
| Variable_name | Value |
--------------- -------
| log_output | FILE |
--------------- -------
1 row in set (0.00 sec)
查看状态：
mysql> show variables like '%slow%
开启慢查询日志：
mysql> set global slow_query_log=on;
设置慢查询的阀值为4秒，超过4秒的查询都将记录到慢查询日志中：
mysql> set global long_query_time=4;
也可以设置得更低，例如：
mysql> set global long_query_time=0.1;
设置慢查询日志的名称和存储位置：
mysql> set global slow_query_log_file="/apps/logs/mysql/slow_query.log";
运行一个超过4秒的查询：
mysql>select count(*) from po;
查询出现慢查询次数的累计值（值由0变为1）：
mysql> show global status like 'Slow_queries';
--------------- -------
| Variable_name | Value |
--------------- -------
| Slow_queries | 1 |
--------------- -------
1 row in set (0.00 sec)
打开生成的slow_query.log文件，发现新增了如下信息：
# Time: 140509 17:39:32
# User@Host: root[root] @ [127.0.0.1]
# Query_time: 13.025654 lock_time: 0.738594 Rows_sent: 1 Rows_examined: 1187860
use database1;
SET timestamp=1399628372;
select count(*) from aa;
其中：
“Time: 140509 17:39:32”为执行这条慢查询sql语句的日期和时间。
“Query_time: 13.025654”为这条慢查询sql语句的执行时间（大约执行了13秒）。
其它可选的配置：
第二种方式开启慢查询
1，配置开启
在mysql配置文件my.cnf中增加
log-slow-queries=/apps/logs/mysql/slow_query.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)
long_query_time=1 (记录超过的时间，默认为10s)
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
log-long-format (如果设置了，所有没有使用索引的查询也将被记录)
2、查看慢查询
使用mysql自带命令mysqldumpslow查看
常用命令
-s ORDER what to sort by (t, at, l, al, r, aretc), 'at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that includethis string
eg:
s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序 -t，是top n的意思，即为返回前面多少条的数据 -g，后边可以写一个正则匹配模式，大小写不敏感的
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “LEFT JOIN” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。
3、 Explain计划分析
使用方法：
执行EXPLAIN SELECT * FROM `pop_order_info` LIMIT 0, 1000; 得到如下结果：
显示结果分析：
table | type | possible_keys | key |key_len | ref | rows | Extra
EXPLAIN列的解释：
table
显示这一行的数据是关于哪张表的
type
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len
使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
rows
MYSQL认为必须检查的用来返回请求数据的行数
Extra
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
extra列返回的描述的意义
Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists
MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，
就不再搜索了
Range checked for each
Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题
不同连接类型的解释（按照效率高低的顺序排序）
system
表只有一行：system表。这是const连接类型的特殊情况
const
表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref
在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range
这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index
这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL
这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
4、 Profile分析
我们可以先使用
mysql> SELECT @@profiling;
-------------
| @@profiling |
-------------
| 0 |
-------------
1 row in set (0.00 sec)来查看是否已经启用profile，如果profilng值为0，可以通过
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@profiling;
-------------
| @@profiling |
-------------
| 1 |
-------------
1 row in set (0.00 sec)
来启用。启用profiling之后，我们执行一条查询语句，比如：
SELECT * FROM res_user ORDER BY modifiedtimeLIMIT 0,1000
mysql> show profiles;
---------- ------------ -------------------------------------------------------------
| Query_ID | Duration | Query |
---------- ------------ -------------------------------------------------------------
| 1| 0.00012200 | SELECT @@profiling |
| 2| 1.54582000 | SELECT res_id FROM res_user ORDER BY modifiedtime LIMIT 0,3 |
---------- ------------ -------------------------------------------------------------
2 rows in set (0.00 sec) 注意：Query_ID表示刚执行的查询语句
mysql> show profile for query 2;
-------------------------------- ----------
| Status | Duration |
-------------------------------- ----------
| starting | 0.000013 |
| checking query cache for query | 0.000035 |
| Opening tables | 0.000009 |
| System lock | 0.000002 |
| Table lock | 0.000015 |
| init | 0.000011 |
| optimizing | 0.000003 |
| statistics | 0.000006 |
| preparing | 0.000006 |
| executing | 0.000001 |
| Sorting result | 1.545565 |
| Sending data | 0.000038 |
| end | 0.000003 |
| query end | 0.000003 |
| freeing items | 0.000069 |
| storing result in query cache | 0.000004 |
| logging slow query | 0.000001 |
| logging slow query | 0.000033 |
| cleaning up | 0.000003 |
-------------------------------- ----------
19 rows in set (0.00 sec)
结论：可以看出此条查询语句的执行过程及执行时间，总的时间约为1.545s。这时候我们再执行一次。
mysql> SELECT res_id FROM res_user ORDERBY modifiedtime LIMIT 0,3;
---------
| res_id |
---------
| 1000305 |
| 1000322 |
| 1000323 |
---------
3 rows in set (0.00 sec)
mysql> show profiles;
---------- ------------ -------------------------------------------------------------
| Query_ID | Duration | Query |
---------- ------------ -------------------------------------------------------------
| 1 | 0.00012200 | SELECT @@profiling |
| 2 | 1.54582000 | SELECT res_id FROM res_userORDER BY modifiedtime LIMIT 0,3 |
| 3 | 0.00006500 | SELECT res_id FROMres_user ORDER BY modifiedtime LIMIT 0,3 |
---------- ------------ -------------------------------------------------------------
3 rows in set (0.00 sec)
mysql> show profile for query 3;
-------------------------------- ----------
| Status | Duration |
-------------------------------- ----------
| starting | 0.000013 |
| checking query cache for query | 0.000005|
| checking privileges on cached | 0.000003 |
| sending cached result to clien | 0.000040|
| logging slow query | 0.000002 |
| cleaning up | 0.000002 |
-------------------------------- ----------
6 rows in set (0.00 sec) (注意红色标记的地方)
结论：可以看出此次第二次查询因为前一次的查询生成了cache，所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取，结果查询时间比第一次快多了（第一次查询用了1.5秒而本次用了不到5毫秒）。
三、慢查询实例
select count(*) from aa;
其中：“Time: 140509 17:39:32”为执行这条慢查询sql语句的日期和时间。
“Query_time: 13.025654”为这条慢查询sql语句的执行时间（大约执行了13秒）。
其它可选的配置：set global log_queries_not_using_indexes = true;
通过这个配置，可以将所有没有使用到索引的sql语句都记录到慢查询日志中
（即使执行时间小于long_query_time所指定的值！）。
测试：
select count(*) from bb;
查看慢查询日志：
# Query_time: 0.032001 Lock_time: 0.031001 Rows_sent: 1 Rows_examined: 88
SET timestamp=1399949174;
select count(*) from bb;
我们可以看到这条sql语句的执行时间虽然只有0.03秒，但由于没有使用到索引，因此
也被记录到了慢查询日志中。
===========================================================================================================
继续测试：
select * from aa where id=634786438;
aa表有上百万条记录，id列上建有主键索引，由于这条sql语句使用到了索引，因此没有被记录到慢查询日志中。
===========================================================================================================
在官方文档中还描述了一种比较特殊的情况：
This option does not necessarily mean that no index is used. For example, a query that uses
a full index scan uses an index but would be logged because the index would not limit the
number of rows.
文档位置：
http://mysql.cdpa.nsysu.edu.tw/doc/refman/5.1/en/server-options.html
继续测试：
select id from aa;
select id from aa limit 5;
查看慢查询日志：
# Query_time: 0.631036 Lock_time: 0.000000 Rows_sent: 1187860 Rows_examined:
1187860
SET timestamp=1399949756;
select id from aa;
......
# Query_time: 0.001000 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 5
SET timestamp=1399953250;
select id from aa limit 5;
这两条sql语句虽然也都使用到了索引，但依然被记录到慢查询日志中。
通过执行计划可以看出这两条sql语句使用了“覆盖索引”（在执行计划中显示为：“Extra: Using index”）。
所谓“覆盖索引”（Covering Index）指的是：只需读取（扫描）索引中的数据就能完成一个查询，而不需要再读取表中的行数据。
上面这两条sql语句由于只读取了主键索引列（id）的数据，因此称为“覆盖索引”。
但是，这两条sql语句并没有通过索引来限定（过滤）行数据，所以都被记录到了慢查询日志中。
如果慢查询日志中的记录内容很多，可以使用mysqldumpslow工具，对慢查询日志进行分类汇总。
mysqldumpslow是mysql数据库自带的工具，在安装mysql数据库软件时，点击
“Developer Components”旁边的按钮，
选择“this feature,and all subfeatures will be Install”(安装所有子项)，就可以安装
mysqldumpslow工具了。
进入mysql的basedir目录下的scripts目录，就可以找到mysqldumpslow.pl了。
在执行mysqldumpslow.pl脚本之前，
还需要下载并安装ActivePerl（linux系统没试过，至少window系统需要安装ActivePerl），
最新版下载地址：
http://www.activestate.com/activeperl
=======================================================================================
进入mysql的basedir目录下的scripts目录：
cd D:\Program Files (x86)\MySQL\MySQL Server 5.1\scripts
执行mysqldumpslow.pl --help命令了解mysqldumpslow用法：
mysqldumpslow.pl --help
=======================================================================================
执行：
mysqldumpslow.pl -s t -t 7 e:\mysql_slow_query.log
这条命令会输出执行时间最长的7条sql语句。
这条命令中各个参数的意义：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照语句query次数、query time、lock time、rows sent的总数来排序，
at、al、ar则是以query time、lock time、rows sent的平均值来排序；
-t, 是top n的意思，即返回排序在最前面的多少条sql语句。
此语句的显示结果如下：
Reading mysql slow query log from e:\mysql_slow_query.log
Count: 6 Time=2301.70s (13810s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127
.0.0.1]
update shuzhi set shuzhi='S' where name='S'
Count: 6 Time=2085.78s (12514s) Lock=0.00s (0s) Rows=7.3 (44), root[root]@[12
7.0.0.1]
select * from shuzhi lock in share mode
Count: 1 Time=6668.59s (6668s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.
0.0.1]
update shuzhi set name='S' where id=N
Count: 2 Time=40.28s (80s) Lock=0.01s (0s) Rows=0.0 (0), root[root]@[127.0.0.1]
insert into shuzhi values(N,'S',N)
Count: 10 Time=7.27s (72s) Lock=0.00s (0s) Rows=1.0 (10), root[root]@[127.0.0.1]
select count(*) from aaaaaa
where aaaaaaTime>='S' and aaaaaaTime<='s'< span="">
Count: 3 Time=20.76s (62s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1]
insert into aaaaaa select * from aaaaaa limit N
Count: 3 Time=17.83s (53s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1]
insert into aaaaaa select * from aaaaaa
--------------------------------------------------------------------
其中Count即query次数，Time即query time、Lock即lock time，Rows即rows sent。
前4条sql语句是发生严重锁等待的4条sql语句。
--------------------------------------------------------------------
请注意输出结果中的最后一条sql语句：
“insert into aaaaaa select * from aaaaaa”，
可以明显看出此语句执行了3次（Count: 3），
平均每次的执行时间为17.83秒(Time=17.83s)，
这3次执行的总执行时间是53秒（Time=17.83s后面的“(53s)”）。
---------------------------------------------------------------------
如果多条sql语句仅仅只是参数值不同，那么这些sql语句会被汇总成一条sql语句显示。
例如：
insert into aaaaaa select * from aaaaaa limit 500000;
insert into aaaaaa select * from aaaaaa limit 600000;
insert into aaaaaa select * from aaaaaa limit 700000;
会被汇总为：
“insert into aaaaaa select * from aaaaaa limit N”。 也就是输出结果中的倒数第2条sql
语句。
其中“limit N”中的“N"代表不同的值（数字）。如果参数变量显示为：“S”，则代表不
同的字符串。
从上面的输出结果“Count: 3 Time=20.76s (62s)”可以看出：
这样的sql语句被执行了3次，平均每条sql语句执行了20.76秒，这3条sql语句执行
时间的总和是62秒。
如果不希望做这样的汇总（即：不希望将多条相似的sql语句（仅仅只是参数变量的值
不同）汇总为一条显示），
可以在命令中加入“-a”选项：
mysqldumpslow.pl -a -s t -t 7 e:\mysql_slow_query.log
===================================================================================
上面的输出结果是按照执行时间的总和排名的。
我们还可以按照平均执行时间进行排名（将“t”改成“at”）：
mysqldumpslow.pl -s at -t 5 e:\mysql_slow_query.log
输出结果如下：
Reading mysql slow query log from e:\mysql_slow_query.log
Count: 1 Time=6668.59s (6668s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.
0.0.1]
update shuzhi set name='S' where id=N
Count: 6 Time=2301.70s (13810s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127
.0.0.1]
update shuzhi set shuzhi='S' where name='S'
Count: 6 Time=2085.78s (12514s) Lock=0.00s (0s) Rows=7.3 (44), root[root]@[12
7.0.0.1]
select * from shuzhi lock in share mode
Count: 2 Time=40.28s (80s) Lock=0.01s (0s) Rows=0.0 (0), root[root]@[127.0.0.1]
insert into shuzhi values(N,'S',N)
Count: 3 Time=20.76s (62s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1]
insert into aaaaaa select * from aaaaaa limit N
请注意比较两次输出结果中的前2条sql语句。
===================================================================================
其它用法举例：
mysqldumpslow.pl -s t -t 3 -g "left join" e:\mysql_slow_query.log
这条命令会输出执行时间最长的、并且包含“left join”的前3条sql语句。
注：-g, 后边可以写一个匹配模式（一个字符串），例如：“left join”。
官方文档的描述：
http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html
确保数据库系统在重启后配置依然生效，修改my.ini(Linux系统为my.cnf)文件，
在“[mysqld]”之后添加：
slow-query-log
slow_query_log_file = "e:\mysql_slow_query.log"
long_query_time = 4
如果需要记录没有使用到索引的sql语句，还可以添加：
log-queries-not-using-indexes = true
【云贝教育】 Mysql认证课程上线，有不定期公开课
*禁止转载，可转发（转发文章请注明出处）
