使用pt-query-digest分析慢查询

pt-query-digest是Percona工具包的一部分，用于对查询进行分析。可以通过以下任何方式收集查询：

• 慢查询日志
• 通用查询日志
• 进程列表
• 二进制日志
• TCP转储

慢查询日志

首先要开启慢查询日志并收集查询，然后就可以通过传递慢查询日志来运行pt-query-digest。

通过修改配置文件，打开慢查询日志

[root@www mysql]# vim /etc/my.cnf
[mysqld]
#slow_log
slow_query_log=on   #开启
slow_query_log_file=/var/lib/mysql/www-slow.log  #日志文件
long_query_time = 0.001  #为了测试，定义超过0.001秒的就记录
log_queries_not_using_indexes = on   #设置对未使用索引的SQL进行记录

做几次查询，生成慢查询日志后,使用工具进行分析

[root@www mysql]# pt-query-digest /var/lib/mysql/www-slow.log > /root/query_digest

摘要报告（digest report）中的查询按照查询执行的次数与查询时间的乘积排列。所有查询的详细信息，例如查询校验和（每种查询类型有一个唯一的值）、平均时间、百分比时间和执行次数等都会显示出来。可以通过搜索查询校验和来深入研究特定查询。

摘要报告的内容如下所示：

[root@www ~]# cat query_digest 
# 40ms user time, 420ms system time, 26.11M rss, 224.43M vsz
# Current date: Sun Oct  6 16:28:28 2019
# Hostname: www.lnmp.com
# Files: /var/lib/mysql/www-slow.log
# Overall: 2 total, 1 unique, 0.03 QPS, 0.00x concurrency ________________
# Time range: 2019-10-06T08:21:13 to 2019-10-06T08:22:31
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          247ms    73ms   174ms   123ms   174ms    72ms   123ms
# Lock time          331us   124us   207us   165us   207us    58us   165us
# Rows sent         38.74k   5.53k  33.21k  19.37k  33.21k  19.57k  19.37k
# Rows examine     585.98k 292.99k 292.99k 292.99k 292.99k       0 292.99k
# Query size           146      73      73      73      73       0      73
#对此查询的分析如下所示：
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x8D07475FE113B1C0 0.2466 100.0%     2 0.1233  0.04 SELECT employees
#对于#1查询（0x8D07475FE113B1C0），其所有的执行累计响应时间为0.2466秒，
#占全部查询的累计响应时间的100%。执行次数为2次
#平均查询时间为0.1233秒
# Query 1: 0.03 QPS, 0.00x concurrency, ID 0x8D07475FE113B1C0 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2019-10-06T08:21:13 to 2019-10-06T08:22:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       2
# Exec time    100   247ms    73ms   174ms   123ms   174ms    72ms   123ms
# Lock time    100   331us   124us   207us   165us   207us    58us   165us
# Rows sent    100  38.74k   5.53k  33.21k  19.37k  33.21k  19.57k  19.37k
# Rows examine 100 585.98k 292.99k 292.99k 292.99k 292.99k       0 292.99k
# Query size   100     146      73      73      73      73       0      73
# String:
# Databases    employees
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `employees` LIKE 'employees'\G
#    SHOW CREATE TABLE `employees`.`employees`\G
# EXPLAIN /*!50100 PARTITIONS*/
select first_name,last_name from employees where hire_date > '1998-01-01'\G

通用查询日志

可以使用pt-query-digest通过传递参数–type genlog来分析通用查询日志。由于通用日志不报告查询的次数，因此只显示累计的数字：

mysql> set global general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%general_log%';
+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| general_log      | ON                     |
| general_log_file | /var/lib/mysql/www.log |
+------------------+------------------------+
2 rows in set (0.00 sec)

做几次查询后，生成日志并使用工具进行分析

[root@www mysql]# pt-query-digest --type genlog /var/lib/mysql/www.log > /root/general_query_digest

结果如下：

[root@www ~]# cat general_query_digest 
# 110ms user time, 10ms system time, 25.58M rss, 220.06M vsz
# Current date: Sun Oct  6 16:58:45 2019
# Hostname: www.lnmp.com
# Files: /var/lib/mysql/www.log
# Overall: 7 total, 4 unique, 0 QPS, 0x concurrency ______________________
# Time range: 2019-10-06T08:55:42.298155Z to 2019-10-06T08:57:26.314967Z
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time              0       0       0       0       0       0       0
# Query size           489      29     209   69.86  202.40   57.22   34.95
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x7DD5F6760F2D2EBB  0.0000  0.0%     3 0.0000  0.00 SHOW VARIABLES
#    2 0x8D07475FE113B1C0  0.0000  0.0%     2 0.0000  0.00 SELECT employees
#    3 0x956C52D06C06E02E  0.0000  0.0%     1 0.0000  0.00 SET
#    4 0xE71D7852C493A0A8  0.0000  0.0%     1 0.0000  0.00 SET
# Query 1: 0 QPS, 0x concurrency, ID 0x7DD5F6760F2D2EBB at byte 955 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-10-06T08:55:42.298155Z to 2019-10-06T08:57:26.314967Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         42       3
# Exec time      0       0       0       0       0       0       0       0
# Query size    21     105      35      35      35      35       0      35
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
show variables like '%general_log%'\G
# Query 2: 0 QPS, 0x concurrency, ID 0x8D07475FE113B1C0 at byte 483 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-10-06T08:55:52.258705Z to 2019-10-06T08:56:06.280872Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         28       2
# Exec time      0       0       0       0       0       0       0       0
# Query size    29     146      73      73      73      73       0      73
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'employees'\G
#    SHOW CREATE TABLE `employees`\G
# EXPLAIN /*!50100 PARTITIONS*/
select first_name,last_name from employees where hire_date > '1985-01-01'\G
# Query 3: 0 QPS, 0x concurrency, ID 0x956C52D06C06E02E at byte 700 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-10-06T08:57:15.264670Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14       1
# Exec time      0       0       0       0       0       0       0       0
# Query size    42     209     209     209     209     209       0     209
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
set global general_log = 'off'
/usr/sbin/mysqld, Version: 8.0.16 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument\G
# Query 4: 0 QPS, 0x concurrency, ID 0xE71D7852C493A0A8 at byte 629 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-10-06T08:56:48.591288Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14       1
# Exec time      0       0       0       0       0       0       0       0
# Query size     5      29      29      29      29      29       0      29
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
set global general_log = 'ON'\G

进程列表

可以使用pt-query-digest而非日志文件从进程列表（process list）中读取查询：

shell> pt-query-digest --processlist h=localhost --iterations 10 --run-time 1m -u<user> -p<password>

run-time指定每次迭代应该运行多长时间。以上例子中该工具会每分钟生成一份报告，并持续10分钟。

二进制日志

要使用pt-query-digest分析二进制日志，应该先用mysqlbinlog工具将其转换为文本格式：

[root@www binlogs]# mysqlbinlog /data/mysql/binlogs/server1.000040 > /root/binlog.000040
[root@www binlogs]# pt-query-digest --type binlog /root/binlog.000040 > binlog_digest

TCP转储

可以使用tcpdump命令捕获TCP流量，并将其发送给pt-query-digest进行分析：

shell> tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
shell> pt-query-digest --type tcpdump mysql.tcp.txt> tcpdump_digest

在pt-query-digest中有很多选项可供选择，例如用特定时间窗口筛选查询，筛选某个特定的查询以及生成报告。更多信息请参阅Percona文档。

优化数据类型

应该这样定义表，它既能保存所有可能值，同时在磁盘上占用的空间又最小。如果表占用的存储空间越小，则：

• 向磁盘写入或读取的数据就越少，查询起来就越快；
• 在处理查询时，磁盘上的内容会被加载到主内存中。所以，表越小，占用的主存空间就越小；
• 被索引占用的空间就越小。

如何操作

1. 如果要存储员工编号，而其可能的最大值为500000，则最佳数据类型为 MEDIUMINT UNSIGNED（3个字节）。如果将它存储为4个字节的INT类型，则每一行都浪费了一个字节；
2. 如果要存储员工名字（first_name）,由于其长度不等，可能的最大长度为20个字符，则最好将其声明为varchar(20)类型。如果将员工名字存储为char(20)类型，但是只有几个人的名字长为20个字符，其余的长度不到10个字符，就会浪费10个字符的空间。
3. 在声明类型为varchar的列时，应该考虑其长度。尽管类型varchar在磁盘上进行了优化，但这个类型的数据被加载到内存时却会占用全部的长度空间。例如，如果将first_name存储在类型varchar(255)中，并且其实际长度为10，则在磁盘上它占用10+1（用于存储长度的一个附加字节）个字节；但在内存中，它会占用全部的255个字节。
4. 如果类型为varchar列的长度超过255个字符，则需要用2个字节来存储长度。
5. 如果不允许存储空值，则应将列声明为NOT NULL。这样做就避免了测试每个值是否为空的开销，并且还节省了一些存储空间–每列能节省1位。
6. 如果字符串的长度是固定的，请存储为char而非varchar类型，因为类型varchar需要一个或两个字节来存储字符串的长度。
7. 如果这些值是固定的，则使用ENUM而非varchar类型。例如，如果要存储可能处于等待状态，或者已批准、已拒绝、已部署、尚未部署，以及已失效或被删除的值，则可以使用ENUM类型。它需要1或2个字节即可，不像类型char(10)那样占用10个字节。
8. 优先选择使用整数类型而非字符串类型。
9. 尝试利用前缀索引。
10. 尝试利用InnoDB压缩。

了解更多关于每一种数据类型的存储需求内容，可以参阅https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

了解每一种整数类型的范围，可以参阅https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

使用performance_schema

可以使用performance_schema在运行时检查服务器的内部执行情况。

performance_schema中有许多影响服务器计时的事件消费者，例如函数调用、对操作系统的等待、SQL语句执行中的某个阶段（例如解析或排序）、一条语句或一组语句。所有收集的信息都存储在performance_schema中，不会被复制。

默认情况下，performance_schema是启用的；如果要禁用它，可以在my.cnf文件中设置performance_schema = OFF。默认情况下，并非所有的消费者和计数器都处于启用状态；可以通过更新performance_schema.setup_instruments和performance_schema.setup_consumers表来关闭/打开它们。

如何操作

如何使用performance_schema呢？

启用/禁用performance_schema

要禁用performance_schema，可以修改配置文件

[root@www ~]# vim /etc/my.cnf
[mysqld]
performance_schema = 0

启用/禁用消费者和计数器

可以在setup_consumers表中看到可用的消费者列表，如下所示：

mysql> select * from performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | YES     |
| events_transactions_history      | YES     |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+
15 rows in set (0.00 sec)

如果要启用events_waits_current：

mysql> UPDATE performance_schema.setup_consumers SET ENABLED='YES' \
    -> WHERE NAME='events_waits_current';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

同样，可以从setup_instruments表启用/禁用计数器，大约有1182种计数器（视MySQL版本而定）：

mysql> SELECT NAME, ENABLED, TIMED FROM performance_schema.setup_instruments LIMIT 10;
+---------------------------------------------------------+---------+-------+
| NAME                                                    | ENABLED | TIMED |
+---------------------------------------------------------+---------+-------+
| wait/synch/mutex/pfs/LOCK_pfs_share_list                | NO      | NO    |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc               | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit         | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue   | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done           | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue    | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index          | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log            | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync           | NO      | NO    |
+---------------------------------------------------------+---------+-------+
10 rows in set (0.00 sec)

performance_schema表

performance_schema中有5种主要的表类型。它们是当前事件表、事件历史表、事件摘要表、对象实例表和设置（配置）表：

mysql> show tables like '%current%';
+------------------------------------------+
| Tables_in_performance_schema (%current%) |
+------------------------------------------+
| events_stages_current                    |
| events_statements_current                |
| events_transactions_current              |
| events_waits_current                     |
+------------------------------------------+
4 rows in set (0.00 sec)
mysql> show tables like '%history%';
+------------------------------------------+
| Tables_in_performance_schema (%history%) |
+------------------------------------------+
| events_stages_history                    |
| events_stages_history_long               |
| events_statements_history                |
| events_statements_history_long           |
| events_transactions_history              |
| events_transactions_history_long         |
| events_waits_history                     |
| events_waits_history_long                |
+------------------------------------------+
8 rows in set (0.00 sec)
mysql> show tables like '%summary%';
+------------------------------------------------------+
| Tables_in_performance_schema (%summary%)             |
+------------------------------------------------------+
| events_errors_summary_by_account_by_error            |
| events_errors_summary_by_host_by_error               |
| events_errors_summary_by_thread_by_error             |
| events_errors_summary_by_user_by_error               |
| events_errors_summary_global_by_error                |
| events_stages_summary_by_account_by_event_name       |
| events_stages_summary_by_host_by_event_name          |
| events_stages_summary_by_thread_by_event_name        |
| events_stages_summary_by_user_by_event_name          |
| events_stages_summary_global_by_event_name           |
| events_statements_summary_by_account_by_event_name   |
| events_statements_summary_by_digest                  |
| events_statements_summary_by_host_by_event_name      |
| events_statements_summary_by_program                 |
| events_statements_summary_by_thread_by_event_name    |
| events_statements_summary_by_user_by_event_name      |
| events_statements_summary_global_by_event_name       |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
| events_waits_summary_by_user_by_event_name           |
| events_waits_summary_global_by_event_name            |
| file_summary_by_event_name                           |
| file_summary_by_instance                             |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                 |
| memory_summary_by_thread_by_event_name               |
| memory_summary_by_user_by_event_name                 |
| memory_summary_global_by_event_name                  |
| objects_summary_global_by_type                       |
| socket_summary_by_event_name                         |
| socket_summary_by_instance                           |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
+------------------------------------------------------+
41 rows in set (0.00 sec)
mysql> show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_threads                          |
+----------------------------------------+
5 rows in set (0.00 sec)

如果要找出被访问最多的文件：

mysql> SELECT EVENT_NAME, COUNT_STAR from file_summary_by_event_name \
    -> ORDER BY count_star DESC LIMIT 10;
+--------------------------------------+------------+
| EVENT_NAME                           | COUNT_STAR |
+--------------------------------------+------------+
| wait/io/file/innodb/innodb_data_file |       3249 |
| wait/io/file/sql/query_log           |        183 |
| wait/io/file/innodb/innodb_log_file  |         92 |
| wait/io/file/innodb/innodb_temp_file |         80 |
| wait/io/file/sql/binlog_index        |         21 |
| wait/io/file/sql/binlog              |         20 |
| wait/io/file/sql/casetest            |         15 |
| wait/io/file/csv/metadata            |         12 |
| wait/io/file/sql/slow_log            |         12 |
| wait/io/file/csv/data                |          6 |
+--------------------------------------+------------+
10 rows in set (0.05 sec)

或者想知道哪一个文件的写入时间最长：

mysql> SELECT EVENT_NAME, SUM_TIMER_WRITE FROM file_summary_by_event_name \
    -> ORDER BY SUM_TIMER_WRITE DESC LIMIT 10;
+--------------------------------------+-----------------+
| EVENT_NAME                           | SUM_TIMER_WRITE |
+--------------------------------------+-----------------+
| wait/io/file/innodb/innodb_data_file |      8598755323 |
| wait/io/file/innodb/innodb_log_file  |      4102550995 |
| wait/io/file/sql/query_log           |      1764069288 |
| wait/io/file/innodb/innodb_temp_file |      1454021094 |
| wait/io/file/sql/slow_log            |       140477337 |
| wait/io/file/sql/binlog              |        27573957 |
| wait/io/file/sql/pid                 |         9900549 |
| wait/io/file/sql/binlog_cache        |               0 |
| wait/io/file/sql/relaylog            |               0 |
| wait/io/file/sql/relaylog_cache      |               0 |
+--------------------------------------+-----------------+
10 rows in set (0.00 sec)

可以使用events_statements_summary_by_digest表来获取查询报告，就像对pt-query-digest所做的那样。按所花费的时间列出排名靠前的查询：

mysql> SELECT SCHEMA_NAME,digest, digest_text, round(sum_timer_wait/1000000000000,6) \
    -> as avg_time, count_star FROM events_statements_summary_by_digest \
    -> ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
SCHEMA_NAME: employees
     digest: e7099bc0313e71566cdcbf0f4fedd9dee2da121c3d2c30762979ce49fad7da64
digest_text: SELECT `first_name` , `last_name` FROM `employees` WHERE `hire_date` > ?
   avg_time: 0.444007
 count_star: 4
1 row in set (0.00 sec)

按执行次数列出排名靠前的查询：

mysql> SELECT SCHEMA_NAME,digest, digest_text, round(sum_timer_wait/1000000000000,6) \
    -> as avg_time, count_star FROM events_statements_summary_by_digest \
    -> ORDER BY count_star DESC LIMIT 1\G
*************************** 1. row ***************************
SCHEMA_NAME: employees
     digest: 230e50b71157a47e34c64f86d958d304e42cc8bad0d5a345f7f60672da38222c
digest_text: SHOW VARIABLES LIKE ?
   avg_time: 0.013246
 count_star: 10
1 row in set (0.00 sec)

假设要查找特定查询的统计信息，可以使用performance_schema检查所有统计信息，而不是依赖于mysqlslap基准数据：

mysql> SELECT * FROM events_statements_summary_by_digest \
    -> WHERE DIGEST_TEXT LIKE '%SELECT%employees%' LIMIT 1\G
*************************** 1. row ***************************
                SCHEMA_NAME: employees
                     DIGEST: e7099bc0313e71566cdcbf0f4fedd9dee2da121c3d2c30762979ce49fad7da64
                DIGEST_TEXT: SELECT `first_name` , `last_name` FROM `employees` WHERE `hire_date` > ?
                 COUNT_STAR: 4
             SUM_TIMER_WAIT: 444006679000
             MIN_TIMER_WAIT: 65428428000
             AVG_TIMER_WAIT: 111001669000
             MAX_TIMER_WAIT: 173988118000
              SUM_LOCK_TIME: 787000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 373688
          SUM_ROWS_EXAMINED: 1200096
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 4
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 4
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2019-10-06 16:21:13.627844
                  LAST_SEEN: 2019-10-06 16:56:06.412642
                QUANTILE_95: 181970085861
                QUANTILE_99: 181970085861
               QUANTILE_999: 181970085861
          QUERY_SAMPLE_TEXT: select first_name,last_name from employees where hire_date > '1985-01-01'
          QUERY_SAMPLE_SEEN: 2019-10-06 16:56:06.412642
    QUERY_SAMPLE_TIMER_WAIT: 131864014000
1 row in set (0.00 sec)

使用sys schema

sys schema以一种更简单和更易理解的形式解释从performance_schema收集来的数据。为了使sys schema能工作，应该启用performance_schema。如想最大限度地使用sys schema，需要启用performances_schema上的所有消费者和计数器，但这样会影响服务器的性能。所以，仅启动你在寻找的消费者。

带有x$前缀的视图以皮秒为单位显示数据，供其他工具做进一步的处理；其他表是人类可阅读的。

如何操作

从sys schema中启用一个计数器：

mysql> CALL sys.ps_setup_enable_instrument('statement');
+------------------------+
| summary                |
+------------------------+
| Enabled 22 instruments |
+------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

如果要重置为默认值，执行以下操作：

mysql> CALL sys.ps_setup_reset_to_default(TRUE)\G

sys schema中有许多表，下面只是展示了其中一些最常用的表。

按类型列出每个主机的语句（INSERT和SELECT）

mysql> SELECT statement, total, total_latency, rows_sent, rows_examined, \
    -> rows_affected, full_scans FROM sys.host_summary_by_statement_type \
    -> WHERE host='localhost' ORDER BY total DESC LIMIT 5;
+------------+-------+---------------+-----------+---------------+---------------+------------+
| statement  | total | total_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-------+---------------+-----------+---------------+---------------+------------+
| set        |  4920 | 8.41 ms       |         0 |             0 |             0 |          0 |
| freturn    |  2460 | 1.33 ms       |         0 |             0 |             0 |          0 |
| Field List |   246 | 152.92 ms     |         0 |             0 |             0 |          0 |
| stmt       |    39 | 23.60 ms      |        33 |          7515 |           559 |          0 |
| select     |    21 | 514.21 ms     |    373761 |       1200320 |             0 |         14 |
+------------+-------+---------------+-----------+---------------+---------------+------------+
5 rows in set (0.00 sec)

按类型列出每个用户的语句

mysql> SELECT statement, total, total_latency, rows_sent, rows_examined, \
    -> rows_affected, full_scans FROM sys.user_summary_by_statement_type \
    -> ORDER BY total DESC LIMIT 5;
+-------------+-------+---------------+-----------+---------------+---------------+------------+
| statement   | total | total_latency | rows_sent | rows_examined | rows_affected | full_scans |
+-------------+-------+---------------+-----------+---------------+---------------+------------+
| set         |  4920 | 8.41 ms       |         0 |             0 |             0 |          0 |
| freturn     |  2465 | 1.34 ms       |         0 |             0 |             0 |          0 |
| Field List  |   246 | 152.92 ms     |         0 |             0 |             0 |          0 |
| jump_if_not |    42 | 43.94 us      |         0 |             0 |             0 |          0 |
| stmt        |    39 | 23.60 ms      |        33 |          7515 |           559 |          0 |
+-------------+-------+---------------+-----------+---------------+---------------+------------+
5 rows in set (0.00 sec)

冗余索引

mysql> SELECT * FROM sys.schema_redundant_indexes \
    -> WHERE table_name='employees' \G
*************************** 1. row ***************************
              table_schema: employees
                table_name: employees
      redundant_index_name: name_desc
   redundant_index_columns: first_name,last_name
redundant_index_non_unique: 1
       dominant_index_name: name
    dominant_index_columns: first_name,last_name
 dominant_index_non_unique: 1
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `employees`.`employees` DROP INDEX `name_desc`
1 row in set (0.00 sec)

未使用的索引

mysql> SELECT * FROM sys.schema_unused_indexes \
    -> WHERE object_schema='employees';
+---------------+--------------+----------------+
| object_schema | object_name  | index_name     |
+---------------+--------------+----------------+
| employees     | dept_emp     | dept_no        |
| employees     | dept_manager | dept_no        |
| employees     | employees    | hire_date      |
| employees     | employees    | last_name      |
| employees     | employees    | name           |
| employees     | employees    | name_desc      |
| employees     | employees    | hire_date_year |
+---------------+--------------+----------------+
7 rows in set, 1 warning (0.00 sec)

每个主机执行的语句

mysql> SELECT * FROM sys.host_summary ORDER BY statements DESC LIMIT 1\G
*************************** 1. row ***************************
                  host: localhost
            statements: 7829
     statement_latency: 794.13 ms
 statement_avg_latency: 101.43 us
           table_scans: 39
              file_ios: 1346
       file_io_latency: 136.09 ms
   current_connections: 1
     total_connections: 2
          unique_users: 1
        current_memory: 1.32 MiB
total_memory_allocated: 42.29 MiB
1 row in set (0.01 sec)

对表的统计

mysql> SELECT * FROM sys.schema_table_statistics LIMIT 1\G
*************************** 1. row ***************************
     table_schema: employees
       table_name: employees
    total_latency: 443.36 ms
     rows_fetched: 1200096
    fetch_latency: 443.36 ms
    rows_inserted: 0
   insert_latency: 0 ps
     rows_updated: 0
   update_latency: 0 ps
     rows_deleted: 0
   delete_latency: 0 ps
 io_read_requests: 1300
          io_read: 20.31 MiB
  io_read_latency: 129.43 ms
io_write_requests: 0
         io_write: 0 bytes
 io_write_latency: 0 ps
 io_misc_requests: 5
  io_misc_latency: 41.88 us
1 row in set (0.02 sec)

对带缓冲区（buffer）的表的统计：

mysql> SELECT * FROM sys.schema_table_statistics_with_buffer LIMIT 1\G
*************************** 1. row ***************************
              table_schema: employees
                table_name: employees
              rows_fetched: 1200096
             fetch_latency: 443.36 ms
             rows_inserted: 0
            insert_latency: 0 ps
              rows_updated: 0
            update_latency: 0 ps
              rows_deleted: 0
            delete_latency: 0 ps
          io_read_requests: 1300
                   io_read: 20.31 MiB
           io_read_latency: 129.43 ms
         io_write_requests: 0
                  io_write: 0 bytes
          io_write_latency: 0 ps
          io_misc_requests: 5
           io_misc_latency: 41.88 us
   innodb_buffer_allocated: 19.62 MiB
        innodb_buffer_data: 18.31 MiB
        innodb_buffer_free: 1.32 MiB
       innodb_buffer_pages: 1256
innodb_buffer_pages_hashed: 0
   innodb_buffer_pages_old: 88
 innodb_buffer_rows_cached: 180523
1 row in set (0.21 sec)

语句分析

此输出类似于performance_schema.events_statements_summary_by_digest和pt-query-digest的输出。

根据查询的执行次数，排在前几位的查询如下：

mysql> SELECT * FROM sys.statement_analysis ORDER BY exec_count DESC LIMIT 1\G
*************************** 1. row ***************************
            query: SHOW VARIABLES LIKE ?
               db: employees
        full_scan: *
       exec_count: 10
        err_count: 0
       warn_count: 0
    total_latency: 13.25 ms
      max_latency: 2.32 ms
      avg_latency: 1.32 ms
     lock_latency: 1.74 ms
        rows_sent: 53
    rows_sent_avg: 5
    rows_examined: 5780
rows_examined_avg: 578
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 10
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 230e50b71157a47e34c64f86d958d304e42cc8bad0d5a345f7f60672da38222c
       first_seen: 2019-10-06 16:53:22.542062
        last_seen: 2019-10-06 17:22:18.985453
1 row in set (0.01 sec)

消耗了最大的tmp_disk_tables的语句为：

mysql> SELECT * FROM sys.statement_analysis ORDER BY tmp_disk_tables DESC LIMIT 1\G
*************************** 1. row ***************************
            query: SELECT IF ( ( `locate` ( ? , ` ... ` . `COMPRESSED_SIZE` ) ) DESC
               db: sys
        full_scan: *
       exec_count: 2
        err_count: 0
       warn_count: 0
    total_latency: 391.11 ms
      max_latency: 215.87 ms
      avg_latency: 195.55 ms
     lock_latency: 1.23 ms
        rows_sent: 3
    rows_sent_avg: 2
    rows_examined: 267623
rows_examined_avg: 133812
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 16
  tmp_disk_tables: 2
      rows_sorted: 4328
sort_merge_passes: 6
           digest: bf060aa9a5df456317f07a0159bcd8e91c9fd9a588a89d87d42e89f3767147b8
       first_seen: 2019-10-06 20:54:43.243844
        last_seen: 2019-10-06 20:55:26.335113
1 row in set (0.00 sec)

更多sys schema对象信息，参阅https://dev.mysql.com/doc/refman/8.0/en/sys-schema-object-index.html

MySQL配置优化

Mysql 参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况，以及机器硬件配置都有关系，优化不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。

下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量。

连接请求的变量

1、max_connections

MySQL 的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。数值过小会经常出现 ERROR 1040: Too many connections 错误，可以过mysql> show status like
‘connections’;查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数)，以定夺该值的大小。

#最大连接数
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1024  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)
#响应的连接数
mysql> show status like '%max_used_connections%';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| Max_used_connections      | 1                   |
| Max_used_connections_time | 2019-10-06 16:14:21 |
+---------------------------+---------------------+
2 rows in set (0.00 sec)

如果 max_used_connections跟max_connections相同那么就是max_connections 设置过低或者超过服务器负载上限了，低于10%则设置过大。

如何设置 max_connections?

[root@www ~]# vim /etc/my.cnf
[mysqld]
max_connections = 1024

2、back_log

MySQL 能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，它就会起作用。如果MySQL的连接数据达到max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

back_log 值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它。

当观察你主机进程列表（mysql> show full processlist），发现大量
xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log的值了或加大 max_connections 的值。

mysql> show variables like '%back_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 1024  |
+---------------+-------+
1 row in set (0.00 sec)

设置方法：

[root@www ~]# vim /etc/my.cnf
[mysqld]
back_log = 1024

3、wait_timeout和interactive_timeout

wait_timeout – 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数

interactive_time —指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上进入mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动断开。默认数值是 28800，可调优为 7200。

对性能的影响

• wait_timeout
  • 如果设置大小，那么连接关闭的很快，从而使一些持久的连接不起作用
  • 如果设置太大，容易造成连接打开时间过长，在 show processlist 时，能看到太多的 sleep状态的连接，从而造成 too many connections 错误
  • 一般希望 wait_timeout 尽可能地低
• interactive_timeout 的设置将要对你的 web application 没有多大的影响

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| mysqlx_wait_timeout      | 28800    |
| wait_timeout             | 28800    |
+--------------------------+----------+
4 rows in set (0.00 sec)
mysql> show variables like '%interactive_timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| interactive_timeout        | 28800 |
| mysqlx_interactive_timeout | 28800 |
+----------------------------+-------+
2 rows in set (0.00 sec)

如何设置

[root@www ~]# vim /etc/my.cnf
[mysqld]
wait_timeout = 7200
interactive_timeout = 7200

缓冲区变量

4、key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 设置是否合理。比例 key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000 更好（上述状态值可以使用 SHOW STATUS LIKE ‘key_read%’获得）。

mysql> show variables like '%key_buffer_size%';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.02 sec)
mysql> show status like '%key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 0     |
| Key_reads         | 0     |
+-------------------+-------+
2 rows in set (0.00 sec)

key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值 created_tmp_disk_tables 得知详情。

mysql> show status like '%created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

默认配置数值是 8388608(8M)，主机有 4GB 内存，可以调优值为 268435456(256MB)。

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

key_buffer_size=268435456 或 key_buffer_size=256M

重启 MySQL Server 进入后，查看设置已经生效。

5、max_connect_errors

是一个 MySQL 中与安全有关的计数器值，它负责阻止过多尝试失败
的客户端以防止暴力破解密码的情况,当超过指定次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts 命令清空此host的相关信息。max_connect_errors 的值与性能并无太大关系。

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容:

max_connect_errors=20

6、sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速 ORDER BY 或 GROUP BY操作。

Sort_Buffer_Size 是一个 connection 级参数，在每个 connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。

Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500 个连接将会消耗 500*sort_buffer_size(2M)=1G 内存

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.01 sec)

设置sort_buffer_size,修改/etc/my.cnf文件，在[mysqld]下面添加如下内容:

sort_buffer_size = 2M

7、max_allowed_packet

MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败。最大值是1GB，必须设置 1024 的倍数。

mysql> show variables like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_allowed_packet        | 67108864   |
| mysqlx_max_allowed_packet | 67108864   |
| slave_max_allowed_packet  | 1073741824 |
+---------------------------+------------+
3 rows in set (0.00 sec)

8、join_buffer_size

用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

mysql> show variables like '%join_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

9、thread_cache_size

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能。

mysql> show variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 18    |
+-------------------+-------+
1 row in set (0.00 sec)

通过比较 Connections和Threads_created状态的变量，可以看到这个变量的作用。设置规则如下：
1GB 内存配置为 8，2GB 配置为 16，3GB 配置为 32，4GB 或更高内存，可配置更大。

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 8     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

• Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
• Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
• Threads_created :代表从最近一次服务启动，已创建线程的数量，如果发现 Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中 thread_cache_size 值。
• Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

配置InnoDB的几个变量

10、innodb_buffer_pool_size

对于 InnoDB 表来说，innodb_buffer_pool_size 的作用就相当于 key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的 80%。根据 MySQL 手册，对于 2G 内存的机器，推荐值是1G（50%）。如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.00 sec)

设置 innodb_buffer_pool_size,修改/etc/my.cnf文件，在[mysqld]下面添加如下内容:

innodb_buffer_pool_size = 2048M

11、innodb_flush_log_at_trx_commit

主要控制了InnoDB将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点，取值分别为0、1、2三个。

• 0，表示当事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中的数据写入日志文件并flush磁盘一次；
• 1，则在每秒钟或是每次事务的提交都会引起日志文件写入、
  flush 磁盘的操作，确保了事务的 ACID；
• 2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次 flush 磁盘操作。

实际测试发现，该值对插入数据的速度影响非常大，设置为 2 时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要 229 秒。因此，MySQL 手册也建议尽量
将插入操作合并成一个事务，这样可以大幅提高速度。
根据 MySQL 手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为 0 或 2。

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

12、innodb_thread_concurrency

此参数用来设置 innodb 线程的并发数量，默认值为 0 表示不限制，若要设置则与服务器的CPU 核数相同或是cpu的核数的 2 倍，建议用默认设置，一般为 8。

mysql> show variables like '%innodb_thread_con%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

13、innodb_log_buffer_size

此参数确定写日志文件所用的内存大小。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

14、innodb_log_file_size

此参数确定数据日志文件的大小，更大的设置可以提高性能。

mysql> show variables like '%innodb_log_file_size%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.00 sec)

15、innodb_log_files_in_group

为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为 3。

mysql> show variables like '%innodb_log_files_in_group%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
1 row in set (0.00 sec)

16、read_buffer_size

MySql 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql 会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size 一样，该参数对应的分配内存也是每个连接独享。

mysql> show variables like '%read_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)

17、read_rnd_buffer_size

MySql 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

mysql> show variables like '%read_rnd_buffer_size%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set (0.00 sec)

18、bulk_insert_buffer_size

批量插入数据缓存大小，可以有效提高插入效率，默认为 8M。

mysql> show variables like '%bulk_insert_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
+-------------------------+---------+
1 row in set (0.01 sec)

19、binlog相关参数

mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /data/mysql/binlogs/server1       |
| log_bin_index                   | /data/mysql/binlogs/server1.index |
| log_bin_trust_function_creators | OFF                               |
| log_bin_use_v1_row_events       | OFF                               |
+---------------------------------+-----------------------------------+
5 rows in set (0.00 sec)

• binlog_cache_size: 为每个session分配的内存，在事务过程中用来存储二进制日志
  的缓存,提高记录bin-log的效率。没有什么大事务，DML也不是很频繁的情况下可以设置小一点，如果事务大而且多，DML操作也频繁，则可以适当的调大一点。前者建议是1M，后者建议是：2-4M;
• max_binlog_cache_size: 表示的是 binlog 能够使用的最大 cache 内存大小;
• max_binlog_size: 指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于 1GB 或小于4096 字节。默认值是1GB。在导入大容量的sql文件时，建议关闭 sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
• expire_logs_days: 定义了mysql清除过期日志的时间。二进制日志自动删除的天数。默认值为 0,表示“没有自动删除”。mysqladmin flush-logs 也可以重新开始新的binary log。

相关优化参数总结：

[mysqld]
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/slow-query.log
long_query_time = 1
log-queries-not-using-indexes
max_connections = 1024
back_log = 128
wait_timeout = 60
interactive_timeout = 7200
key_buffer_size=256M
max_connect_errors=20
sort_buffer_size = 2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size = 2048M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
log-error = /usr/local/mysql/data/mysqld.err

优化总结

基本思路如下：

性能瓶颈定位

• show 命令

可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：
查看 MySQL服务器配置信息mysql> show variables;
查看 MySQL服务器运行的各种状态值 mysql> show global status;
mysqladmin variables -u username -ppassword——显示系统变量
mysqladmin extended-status -u username -ppassword——显示状态信息

• 慢查询日志
• explain 分析查询
• profiling 分析查询

索引及查询优化

配置优化

MySQL 数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar 和 vmstat 来查看系统的性能状态。

除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整 MySQL的内部配置。