SQL优化
1.查看各种SQL执行的频率
mysql> show status like 'Com_select';--Com_insert,Com_delete,connections(试图连接mysql服务的次数),uptime(mysql工作时间),slow_queries(慢查询次数)等等
2.定位执行效率较低的SQL语句
通过慢查询日志，定位查询效率低下的SQL语句，然后分析语句进行优化
3.通过explain或desc分析SQL语句的执行计划，如要查看所访问的分区使用explain partitions
mysql> desc select email from users\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
type: index //扫描方式，效率由低到最好 all(全表)->index(索引全扫描)->range(索引范围扫描)->ref(非唯一索引)->eq_ref(唯一索引)->const/system->null
possible_keys: NULL
key: email
key_len: 153
ref: NULL
rows: 59
Extra: Using index
1 row in set (0.00 sec)
4.使用profile分析SQL，profile就是详细地列出SQL语句执行过程
查看是否开启/支持profile
mysql> show variables like 'profiling'; --使用select @@have_profiling也可以 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ mysql> set profiling=on --开启 OFF--关闭
查看所有查询分别执行的时间
mysql> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00079925 | show variables like 'profiling' | | 2 | 0.00050700 | select * from users | | 3 | 0.36104925 | select * from tb_5 | +----------+------------+---------------------------------+
查看某个查询语句执行过程每个状态以及消耗的时间
mysql> show profile for query 3; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000059 | | checking permissions | 0.000010 | | Opening tables | 0.042742 | | System lock | 0.000018 | | init | 0.000017 | | optimizing | 0.000004 | | statistics | 0.000162 | | preparing | 0.000010 | | executing | 0.000003 | | Sending data | 0.317929 | --主要时间花费在将数据发送到客户端 | end | 0.000011 | | query end | 0.000007 | | closing tables | 0.000013 | | freeing items | 0.000058 | | logging slow query | 0.000005 | | cleaning up | 0.000004 | +----------------------+----------+
5. 使用optimizer_trace分析优化器查看SQL语句执行计划(5.6版本以上)
mysql> show variables like 'optimizer_trace'; +-----------------+--------------------------+ | Variable_name | Value | +-----------------+--------------------------+ | optimizer_trace | enabled=off,one_line=off | +-----------------+--------------------------+
开启trace分析器，并调整最大可用内存
mysql> set optimizer_trace='enabled=on',end_markers_in_json=on; --以JSON格式显示 Query OK, 0 rows affected (0.05 sec) mysql> set optimizer_trace_max_mem_size=1000000; Query OK, 0 rows affected (0.16 sec)
通过information_schema库的optimizer_trace视图查看trace信息
mysql> select query,trace from optimizer_trace\G
*************************** 1. row ***************************
query: select * from test.stu
trace: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `test`.`stu`.`sno` AS `sno`,`test`.`stu`.`sname` AS `sname`,`test`.`stu`.`sclass` AS `sclass` from `test`.`stu`"
-------------------只截取了部分信息，通过分析这些信息可用对SQL语句进行相应优化---------------------
6.使用索引优化查询性能
索引是数据库优化中最常用的最要种的手段，索引可以分为以下4中：
HASH索引：只有MEMORY引擎支持； B-TREE索引：平衡树索引，最常用的索引 R-TREE索引：MyISAM引擎特殊索引 FULL-TREE全文索引：MyISAM特殊索引。
创建索引：
mysql> create index idx_name on stu(sname); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
查看某表的所有索引
mysql> show index from stu\G
*************************** 2. row ***************************
Table: stu
Non_unique: 1
Key_name: idx_name
Seq_in_index: 1
Column_name: sname
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
MySQL能够使用索引的一些情况：匹配索引全值，匹配值的范围查询，匹配最左前缀等等。
查看索引使用情况，如果handle_read_key值很高，说明查询效率很高，如Handler_read_rnd_next值很高，说明查询效率并不理想。
mysql> show status like 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 25 | +-----------------------+-------+
7.定期检查表和分析表，以及优化表
检查表就是检查一个或多个表是否有错误
mysql> check table stu; +--------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+-------+----------+----------+ | zz.stu | check | status | OK | +--------+-------+----------+----------+
分析表主要作用是让SQL生成正确的执行计划
mysql> analyze table stu; +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | zz.stu | analyze | status | OK | +--------+---------+----------+----------+
优化表的作用主要是对表空间的碎片进行合并以及回收删除或更新造成浪费的空间
mysql> optimize table t1;
--对于InnoDB的表，会有Table does not support optimize, doing recreate + analyze instead提示，可以在启动mysql服务时，指定--skip-new或--safe-mode即可 +---------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+----------+ | test.t1 | optimize | status | OK | +---------+----------+----------+----------+ 1 row in set (0.04 sec)
8.常用SQL优化
加载大量数据时，关闭非唯一索引，取消唯一性检查，以及取消自动提交以提高插入速度
set unique_checks=0 alter table stu disable keys set autocommit=0 load load infile........ alter table stu enable keys set unique_checks=1 set autocommit =1
insert语句优化，一次性插入多条记录
mysql> insert into stu values (4010409,'钟小兆','A1114',22,0),(4010408,'肖小杰','A1114',21,1).....;
order by语句排序优化，优化思路就是尽可能的减少额外的排序(filesort)，通过索引直接返回有序数据,例如
mysql> explain select sno from stu order by sname desc; +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | stu | index | NULL | idx_name | 93 | NULL | 6 | Using index | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
where条件和order by 字段使用相同的索引，并且order by的顺序和索引顺序相同，还有order by的字段都是降序或者升序。例如：
以下情况会使用索引，前提(key-part1,key_part2)为联合索引
select * from tbl_name order by key_part1,key_part2....; select * from tbl_name where key_part1=xxx order by key_part1,key_part2....; select * from tbl_name order by key_part1 asc,key_part2 asc....;
以下情况则不会使用索引，(key1,key2分别建立索引)
select * from tbl_name order by key1,key2....; select * from tbl_name where key1=xxx order by key2; select * from tbl_name order by key_part1 asc,key_part2 desc....;
SELECT查询时最好指定具体的字段名，SELECT * 会选择所有字段，会增加排序区的使用，降低SQL性能
group by语句优化
MySQL默认情况下对group by col1,col2..的字段进行排序，可以通过指定order by null来消除这种排序
or条件优化
用到OR的查询，如果要使用索引，那OR之间的每个条件必须是索引，并且要分别建立索引，不能使用联合索引。
一.数据库性能评测关键指标
1.IOPS：每秒处理的IO请求次数，这跟磁盘硬件相关，DBA不能左右，但推荐使用SSD。
2.QPS：每秒查询次数，可以使用show status或mysqladmin extended-status命令来查看QPS值，如下所示。
mysql> show global status like 'Questions';--QPS=Questions/Uptime（show status like 'Uptime'）这是一个全局的平均值 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Questions | 80 | +---------------+-------+
也可以自动获取这个，每隔1S获取一次，使用mysqladmin -r -i参数来实现，还可以同时获取每秒查询，更新的次数，如下所示。
root@zhumuxian-machine:/# mysqladmin -u root -p extended-status -r -i 1 | grep -E 'Questions|Com_select|Com_update' Enter password: | Com_select | 1 | | Com_update | 0 | | Com_update_multi | 0 | | Questions | 81 |
--这个命令会每隔一秒获取当前的Questions,Com_select,Com_update的值，并且将当前的输出值减去上次获取的值。grep命令的E参数指定正则表达式
使用mysqlslap(系统自带压力测试工具)命令来模拟多个并发客户端访问Mysql服务，接着查看QPS值（不要关闭上面会话中执行的mysqladmin）
root@zhumuxian-machine:/home/zhumuxian# mysqlslap --no-defaults -p --query='select * from test.stu' --number-of-queries=100000 -c 10 -i 10 --create-schema=test --模拟在test库下，10个客户端同时连接mysql服务器，总计进行100万次查询 | Questions | 3305 | | Questions | 45405 | | Questions | 40648 | | Questions | 27976 | | Questions | 41340 | | Questions | 40961 | | Questions | 35475 | | Questions | 41397 | | Questions | 38478 |
由上面的结果可以看出QPS还是可以的，毕竟我的机子才2G内存2.0GHZ CPU啊。其实这么高的QPS，是因为stu表的数据才几条而已，查询都是在查询缓存里面了，不过还可以使用mysqlslap命令的其它参数来自动生成SQL语句，然后再查询测试，如下所示。
root@zhumuxian-machine:/home/zhumuxian# mysqlslap --no-defaults -p --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=100000 --auto-generate-sql-unique-query-number=10000 -c 20 --commit=10000 --create-schema=test --模拟20个客户端连接，总计执行200万次查询
| Questions | 27 | | Questions | 21 | | Questions | 748 | | Questions | 2715 | | Questions | 1653 | | Questions | 26013 | | Questions | 24173 | | Questions | 24295 | | Questions | 21250 | | Questions | 15643 |
3.TPS：每秒事务数，官方没有给出TPS参数，但一般都是使用以下公式进行计算的：
TPS = (Com_commit+Com_rollback)/seconds --Seconds是由我们自己定义的，如果使用Uptime就是MYSQL该生命周期的一个平均值
使用mysqladmin和mysqlslap组合来计算和查看TPS，如下所示。
root@zhumuxian-machine:/home/zhumuxian# mysqlslap --no-defaults -p --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=10000 --auto-generate-sql-unique-query-number=1000 -c 10 --commit=1 --create-schema=test --模拟进行了10万次提交 Com_commit | 32 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_commit | 190 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_commit | 192 |
二.TPCC测试
TPCC是一套基准测试程序，由TPC推出的。我使用的是TPCC-MYSQL ，由Percona基于TPCC开发出来的，是模拟一个电商业务，安装步骤如下。
--因为tpcc-mysql源码放在Bazaar上，它是一个分布式的版本控制系统，需要先安装bzr命令 apt-get install bzr --下载源码 bzr branch lp:~percona-dev/perconatools/tpcc-mysql --编译 cd tpcc-mysql make --如果这时候出现/bin/sh: 1: mysql_config: not found错误，可以使用apt-get install libmysqlclient-dev解决，接着make --如果又出现/usr/bin/ld: cannot find -lz错误，安装apt-get install zlib1g-dev即可，继续make，成功之后在TPCC-MYSQ目录下会生成两个可执行文件， root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# ls tpcc* tpcc_load tpcc_start
接着按照说明文档创建数据库和导入预定脚本，如下所示。
--创建tpcc_test库
root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# mysqladmin -p create tpcc_test
--导入创建表SQL文件
root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# mysql -p tpcc < create_table.sql
--导入创建索引SQL文件
root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# mysql -p tpcc < add_fkey_idx.sql
--初始化数据,使用tpcc_load命令,具体用法可参考tpcc_load --help
root@zhumuxian-machine:/# /home/zhumuxian/tpcc-mysql/tpcc_load 127.0.0.1 tpcc_test root 7758520 10
*************************************
*** ###easy### TPC-C Data Loader ***
*************************************
<Parameters>
[server]: 127.0.0.1
[port]: 3306
[DBname]: tpcc_test
[user]: root
[pass]: 7758520
[warehouse]: 10
TPCC Data Load Started...
Loading Item
---只截取部分信息，太多了--
使用tpcc_start进行测试，如下：
root@zhumuxian-machine:/# /home/zhumuxian/tpcc-mysql/tpcc_start -h localhost -d tpcc_test -u root -p 7758520 -w 10 -c 10 -r 100 -l 200 -f /home/zhumuxian/tpcc_test.log
--注释：-w(warehouse) -c(connections) -r(warntime预热时间) -l(run time)
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value 'localhost'
option d with value 'tpcc_test'
option u with value 'root'
option p with value '123456'
option w with value '10'
option c with value '10'
option r with value '100'
option l with value '200'
option f with value '/home/zhumuxian/tpcc_test.log'
<Parameters>
[server]: localhost
[port]: 3306
[DBname]: tpcc_test
[user]: root
[pass]: 123456
[warehouse]: 10
[connection]: 10
[rampup]: 100 (sec.)
[measure]: 200 (sec.)
RAMP-UP TIME.(100 sec.) --只截取部分信息---
测试结果信息输出如下，截取部分：
10, 15(7):7.971|8.304, 22(0):1.602|1.693, 3(0):0.741|0.764, 2(0):9.421|9.553, 2(2):19.999|27.620
20, 25(17):8.037|8.375, 22(0):1.405|1.620, 2(0):0.518|0.574, 1(0):0.000|6.744, 3(3):19.999|25.810
30, 16(9):7.792|7.855, 17(0):1.479|1.643, 2(0):0.420|0.655, 3(0):7.372|7.753, 1(1):0.000|24.702
--这个结果以逗号分开，分为6项，依次为操作时间，创建订单，支付订单，查询订单，发货以及查询库存。
--每一项都有4个属性值，分别为成功执行的事务数，延迟执行的事务数，90%事务的响应时间，最大响应时间
--拿第一条分析，创建订单，共操作15次，延迟7次，90%事务时间7.971S，最大响应时间8.304S
---------------------各线程数据量汇总-------------------------
<Raw Results>
[0] sc:256 lt:288 rt:0 fl:0 --sc 成功数量 lt 延迟数量 rt 重试数量 fl 失败数量
[1] sc:547 lt:0 rt:0 fl:0
[2] sc:55 lt:0 rt:0 fl:0
[3] sc:53 lt:0 rt:0 fl:0
[4] sc:12 lt:44 rt:0 fl:0
in 200 sec.
--------------------不同业务事务数占比-----------------------
[transaction percentage]
Payment: 43.59% (>=43.0%) [OK]
Order-Status: 4.38% (>= 4.0%) [OK]
Delivery: 4.22% (>= 4.0%) [OK]
Stock-Level: 4.46% (>= 4.0%) [OK]
---------------------TmpC指标----------------------
<TpmC>
163.200 TpmC --每分钟处理的事务数
三.MySQL SERVER参数配置优化
MySQL实例是由一组后台线程，一些内存块和若干个服务线程组成。默认情况下，mysql后台有八个线程，1个主线程，4个IO线程，1个锁线程，1个错误监控线程，1个PURGE线程，可以通过show engine innodb status查看这些线程的状态。
1.调整连接相关参数
调整max_connections，提高并发连接，根据mysql服务器的配置和性能来设置，在linux平台下，设置为500-1000问题不大，默认值为151。设置方法如下：
mysql> set global max_connections=1000; --临时设置 Query OK, 0 rows affected (0.06 sec) ------在my.cnf配置文件中添加---- 【mysqld】 max_connections = 1000
调整max_connect_errors，增大允许连接不成功的最大尝试次数，如果尝试连接的错误次数大于这个变量的值，mysql服务将拒绝新的连接，除非在会话中执行flush hosts或者mysqladmin flush-host，但这样的代价比较大，因此这个参数值一般要设置大点，建议为10万级以上，系统默认为100。设置方法与上面max_connections设置类似。
调整back_log，连接请求队列中存放的最大连接请求数量，默认值为50+（max_connections/5），如果短时间内有大量的连接请求，可以适当增加该参数的值。
禁止mysql服务的逆向解析SKIP-NAME-RESOLVE，默认为禁止。
2.调整文件相关参数
调整SYNC_BINLOG，该参数指定同步二进制日志到磁盘的频率，默认为0，由自身的缓存机制决定何时同步。设置为1，性能最低，安全最高，即提交一个事务同步一次。
调整EXPIRE_LOG_DAYS，该参数指定二进制日志的生命周期，超过了这个时间，将会自动删除二进制日志，默认为0，即从不删除二进制日志，须手动清理。一般情况下，该值设置为1-2周。设置方法都一样，在my.cnf文件中添加该参数并指定值即可。
调整MAX_BINLOG_SIZE，该参数指定二进制日志文件的大小，默认为1G。
3.调整缓存相关参数
binlog_cache_size：指定二进制日志事务缓冲区的大小，默认值为32KB，最好不要超过64MB，8M-16M即可满足绝大多数场景(不是本人得出)。
max_binlog_cache_size：指定二进制日志事务缓存区能够使用的最大内存大小，建议设置为binlog_cache_size的两倍即可。
binlog_stmt_cache_size：指定二进制日志非事务缓存区的大小，默认大小也为32KB，意见为binlog_cache_size一样。
thread_cache_size：指定缓存的线程数量，建议值300-500。
query_cache_size：指定用于缓存查询结果集的内存区大小，默认为1MB，最好不要超过256MB。
query_cache_limit：控制查询缓存，能够缓存单条SQL生成的最大结果集，默认值为1M，采用默认值就好。
query_cache_type：0 不使用查询缓存 1 缓存除SELECT SQL_NO_CACHE外的查询结果 2 只缓存SELECT SQL_CACHE的查询结果。
sort_buffer_size：指定单个session内存排序区的大小，默认为256KB，一般设置为1-4MB即可。
max_heap_table_size：指定内存表，即memory存储引擎表，最大可以占用内存的大小，默认为16MB。
4.InnoDB内存优化
innodb_buffer_pool_szie：指定InnoDB引擎专用缓存区大小，用来存储表对象数据和索引信息，默认为128MB，当然这个值越大越好，在OS和内存允许的情况下。
查看innodb buffer pool的使用情况，如下所示。
mysql> show global status like 'innodb_buffer%'; +---------------------------------------+-------------+ | Variable_name | Value | +---------------------------------------+-------------+ | Innodb_buffer_pool_dump_status | not started | | Innodb_buffer_pool_load_status | not started | | Innodb_buffer_pool_pages_data | 447 | | Innodb_buffer_pool_bytes_data | 7323648 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 1 | | Innodb_buffer_pool_pages_free | 7745 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 8192 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 2945 | | Innodb_buffer_pool_reads | 448 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 1 | +---------------------------------------+-------------+ -----------------InnoDB缓存命中率计算公式------------ (1-(innodb_buffer_pool_reads/innodb_buffer_pool_read_request))*100
该值越小，说明命中率越低，是时候扩充内存，增加innodb_buffer_pool_size的大小
innodb_buffer_pool_instances：指定InnoDB缓存区分为几个区域来使用，默认为8。
innodb_thread_concurrency：指定InnoDB内部最大线程数，默认为0，由InnoDB自行管理。
innodb_flush_method：指定InnoDB刷新数据文件和日志文件的方式，默认调用fsync()函数。
innodb_log_buffer_size：指定InnoDB日志缓存区的大小。默认为8MB。
innodb_flush_log_at_trx_commit：指定InnoDB刷新日志缓存区中的数据到文件的方式，默认值为1，即只要提交事务或回滚事务，就将缓存区的数据刷新到日志文件中，并同步到文件系统中，若值为0，则每秒向日志文件写入一次并写入磁盘，若值为2，遇到事务提交时，将数据写入日志文件中，但并不立即触发文件系统的同步写入。
innodb_flush_log_at_timeout ：指定每隔N秒刷新日志。
5.MyISAM内存优化
key_buffer_size：指定MyISAM索引缓存区大小，默认为8MB。
key_cache_block_size ：指定索引缓存的块大小，默认为1KB。
read_buffer_size：指定顺序读时数据缓存的大小，默认为128KB，如果经常要进行顺序读取时，可以适当加大该值。
read_rnd_buffer_size ：指定随机读取时数据缓存区的大小。默认为256KB。
四.慢查询日志分析
1.使用mysql自带的mysqldumpslow工具分析，如下所示。
root@zhumuxian-machine:/data/mysql# mysqldumpslow mysql-slow.log Reading mysql slow query log from mysql-slow.log Count: 2 Time=2.49s (4s) Lock=0.00s (0s) Rows=50.0 (100), root[root]@localhost select * from customer where c_id=N
该工具提供的参数很少，最核心的参数就是-s，用来排序的，-r按照规则倒叙输出，-t用来控制输出的SQL数量。
2.使用第三方工具pt-query-digest进行分析
pt-query-digest来自于Percona Toolkit，安装方法如下：
root@zhumuxian-machine:/home/zhumuxian# wget percona.com/get/percona-toolkit.deb -----下载 root@zhumuxian-machine:/home/zhumuxian# dpkg -i percona-toolkit_2.2.14_all.deb -----安装 root@zhumuxian-machine:/home/zhumuxian# whereis pt-query-digest pt-query-digest: /usr/bin/pt-query-digest /usr/bin/X11/pt-query-digest /usr/share/man/man1/pt-query-digest.1p.gz ----查看安装是否成功
使用pt-query-digest分析慢查询日志，如下所示：
root@zhumuxian-machine:/data/mysql# pt-query-digest mysql-slow.log
输出结果如下：
# 360ms user time, 0 system time, 17.94M rss, 25.71M vsz # Current date: Sun Apr 26 11:43:57 2015 # Hostname: zhumuxian-machine # Files: mysql-slow.log # Overall: 2 total, 1 unique, 0.02 QPS, 0.04x concurrency ________________ # Time range: 2015-04-25 17:02:26 to 17:04:17 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 5s 1s 4s 2s 4s 2s 2s # Lock time 508us 216us 292us 254us 292us 53us 254us # Rows sent 100 0 100 50 100 70.71 50 # Rows examine 585.94k 292.97k 292.97k 292.97k 292.97k 0 292.97k # Query size 78 38 40 39 40 1.41 39 //以上时概要信息，包括了总共有多少条查询，多少条不同的语句，QPS等信息 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x4E195A4D8816B149 4.9706 100.0% 2 2.4853 1.59 SELECT customer //对各类查询的执行情况进行分析，结果按总执行时长，从大到小排序 # Query 1: 0.02 QPS, 0.04x concurrency, ID 0x4E195A4D8816B149 at byte 0 __ # This item is included in the report because it matches --limit. # Scores: V/M = 1.59 # Time range: 2015-04-25 17:02:26 to 17:04:17 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 100 2 # Exec time 100 5s 1s 4s 2s 4s 2s 2s # Lock time 100 508us 216us 292us 254us 292us 53us 254us # Rows sent 100 100 0 100 50 100 70.71 50 # Rows examine 100 585.94k 292.97k 292.97k 292.97k 292.97k 0 292.97k # Query size 100 78 38 40 39 40 1.41 39 //列出1号查询详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计
如果要分析某个时间段产生的慢查询日志，可以使用--since和--until参数。
root@zhumuxian-machine:/data/mysql# pt-query-digest --since 2d mysql-slow.log ----分析2天之内产生的慢查询日志 ----since 值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。 --until 截止时间，配合—since可以分析一段时间内的慢查询。
将分析结果导入其它文件中，不显示在终端上
root@zhumuxian-machine:/data/mysql# pt-query-digest --since 2d mysql-slow.log > slow.txt
分析2015-02-22到2015-03-22之间产生的慢查询
root@zhumuxian-machine:/data/mysql# pt-query-digest --since '2015-02-22' --until '2015-03-22' mysql-slow.log
使用--filter参数过滤条件只分析特定的慢查询
root@zhumuxian-machine:/data/mysql# pt-query-digest --filter '($event->{user}||"")=~m/root/i' mysql-slow.log
-----只分析root用户下的慢查询
root@zhumuxian-machine:/data/mysql# pt-query-digest --filter '($event->{fingerprint}||"")=~m/select/i' mysql-slow.log
-----只分析SELECT语句的慢查询
还有很多的参数可以使用，有用到的同学可自行参考官方文档。
五.获取当前MySQL服务所有的连接线程列表
使用show [full] processlist命令可以获取当前所有的线程列表，如下所示：
mysql> show processlist; +-----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+-------+------------------+ | 135 | root | localhost | NULL | Query | 0 | init | show processlist | +-----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec)
使用kill命令，可以杀掉正在运行线程，如下所示：
mysql> kill 137; Query OK, 0 rows affected (0.00 sec)
也可以不杀掉线程，只停止它的查询，
mysql> show processlist; +-----+------+-----------+------+---------+------+------------------------------+-----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+------------------------------+-----------------------------------------------+ | 135 | root | localhost | test | Query | 0 | init | show processlist | | 139 | root | localhost | test | Query | 7 | Waiting for table level lock | insert into t1 values(100,4010404,'dsdsffef') | +-----+------+-----------+------+---------+------+------------------------------+-----------------------------------------------+ 2 rows in set (0.00 sec) mysql> kill query 139; Query OK, 0 rows affected (0.00 sec) mysql> show processlist; +-----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+-------+------------------+ | 135 | root | localhost | test | Query | 0 | init | show processlist | | 139 | root | localhost | test | Sleep | 99 | | NULL | +-----+------+-----------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)
六.应用层优化
1.尽量使用连接池，因为创建新的连接代价比较大，可以预先创建好适当的连接保存起来，应用需要时可以直接分配，当应用释放连接后，该连接返回给连接池。
2.尽量避免对同一数据做重复检索，比如你现在需要用户的年龄，就直接查询年龄，等会儿你有要性别，又去检索性别，其实都可以一次性获得，不要做重复的查询。
3.使用查询缓存，对相同的数据做检索时，mysql会直接从缓存中获取，查看是否开启缓存，如下所示：
mysql> show variables like '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+
4.在应用层增加CACHE层，比如将经常用到的数据放到文本中，或者弄个二级数据库，同步机制自己制定。
5.负载均衡，利用复制特性，将查询操作放到slave端进行，减轻主库的压力。
