美文网首页
8.0优化专题(2023)

8.0优化专题(2023)

作者: 这货不是王马勺 | 来源:发表于2023-07-20 11:57 被阅读0次

    概述

    1.数据库优化的重要性

    数据库服务器的瓶颈通常在磁盘的读写。

    • 纵向扩展(增加网络、硬盘、内存和 CPU 的处理能力)数据库服务器可以线性增 加性能。
    • 水平扩展(增加服务器数量)难度很大，例如将数据库服务器数量从一台增加到两台，运维的难度可能增加了 10 倍，因为涉及多台服务器之间的并发时的锁和数据同步的问题。水平扩展可能不会带来性能的线性增加，例如数据库服务器数量增加 1 倍，性能可能只会增加 0.5 到 0.8 倍。

    应用服务器扩展容易，而数据库服务器扩展困难。因此当系统遇到性能瓶颈时，数据库的性能优化就显得非常重要，并且回报丰厚。
    目前在项目的开发设计阶段研发人员主要关注功能的实现，对性能缺乏足够的重视，等投产后，随着业务量和数据量的增加，性能瓶颈就暴露出来了。因此这个时候数据库优化的空间都很大，很多系统经过优化后，性能可以提高 10 倍到 100 倍。其中 SQL 语句的优化特别明显，一些需要运行数天或数小时的 SQL 语句， 经过优化后，可以在几分钟甚至几秒钟内完成。

    实例优化和参数设置

    2.系统参数

    2.1系统参数概述

    MySQL的系统参数当然是对MySQL性能影响最大的因素。
    MySQL的系统参数保存在参数文件中，不同平台的参数文件位置不一样，RedHat Linux中的默认参数文件位置是 /etc/my.cnf，Debian Linux中的默认参数文件位置是/etc/mysql/my.cnf。
    MySQL 8一共有六百多个系统参数，但对系统性能影响大的参数只有几十个。
    参数说明参见：

    https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html

    2.2全局级和会话级参数

    MySQL的系统参数根据作用范围可以分为全局级和会话级，全局级的系统参数对实例的所有会话起作用，会话级的系统参数只对当前会话起作用，在这两个级别修改系统参数的例子如下：

    set session binlog_rows_query_log_events=on;
set global binlog_rows_query_log_events=on;

    其中session可以省略。

    修改全局系统参数的生效时间
    修改全局系统参数只对修改后连接到MySQL的会话生效，在修改之前已经建立的会话还保持原来的参数值，例如下面的命令修改一个全局系统参数：

    set global sort_buffer_size=16*1024*1024;

    下面的命令检查这个系统参数的全局级和会话级的值：

    mysql> select @@global.sort_buffer_size,@@session.sort_buffer_size;
+---------------------------+----------------------------+
| @@global.sort_buffer_size | @@session.sort_buffer_size |
+---------------------------+----------------------------+
|                  16777216 |                     262144 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)

    发现全局级的参数值已经改过来了，而会话级的参数值没有变。

    一些系统参数只能是全局级的
    有一些系统参数只能是全局级的，在会话级修改这类参数会出错，例如：

    mysql> set log_error_verbosity=2;
ERROR 1229 (HY000): Variable 'log_error_verbosity' is a GLOBAL variable and should be set with SET GLOBAL

    参见参数说明

    https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html

    中的Var Scope字段。

    查询其他会话的参数
    如果要查询其他会话的参数可以在performance_schema.variables_by_thread视图中查询，例如下面的SQL语句查询所有会话的事务隔离级别如下：

    mysql> select * from performance_schema.variables_by_thread where variable_name='transaction_isolation';
+-----------+-----------------------+-----------------+
| THREAD_ID | VARIABLE_NAME         | VARIABLE_VALUE  |
+-----------+-----------------------+-----------------+
|        60 | transaction_isolation | REPEATABLE-READ |
|        67 | transaction_isolation | SERIALIZABLE    |
+-----------+-----------------------+-----------------+
2 rows in set (0.00 sec)

    2.3静态参数和动态参数

    MySQL的系统参数还可以分为静态参数和动态参数，动态参数可以MySQL运行中进行修改，静态参数在MySQL启动后无法修改，例如：

    mysql> set auto_generate_certs=on;
ERROR 1238 (HY000): Variable 'auto_generate_certs' is a read only variable

    2.4系统参数设置为default

    如果把会话级系统参数设置为default，对应的是全局级系统参数值。下面两个设置会话级参数的语句效果是一样的：

    mysql> SET @@SESSION.max_join_size = DEFAULT;
mysql> SET @@SESSION.max_join_size = @@GLOBAL.max_join_size;

    如果把全局级系统参数设置default，将把系统参数恢复为MySQL内置的默认值，而不是像很多人认为的是参数文件里面的设置值。

    2.5持久化参数设置

    在系统参数设置时，一个容易犯的错误是在MySQL运行时修改了参数值，但没有同时修改参数文件里面的配置，当MySQL重新启动后，参数文件里的旧值生效，之前的修改丢掉了。在MySQL 8里，MySQL推出了让参数持久化的命令，可以让在联机时修改的系统参数在重新启动后仍然生效，例如：

    mysql> set persist max_connections = 1000;

    或者：

    mysql> set @@persist.max_connections = 1000;

    如果想让系统参数在本次MySQL运行时不生效，只是在下次启动时生效，可以使用下面的命令：

    mysql> set persist_only back_log = 100;

    或者：

    mysql> set @@persist_only.back_log = 100;

    持久化的系统参数以JSON格式保存
    持久化的系统参数以JSON格式保存在数据目录的mysqld-auto.cnf文件中，例如：

    $ cat /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "1000" , "Metadata" : { "Timestamp" : 1624532357790912 , "User" : "root" , "Host" : "localhost" } } , "mysql_server_static_options" : { "back_log" : { "Value" : "100" , "Metadata" : { "Timestamp" : 1624533604896754 , "User" : "root" , "Host" : "localhost" } } } } }

    可以通过reset persist命令来清除mysqld-auto.cnf文件中的所有配置，也可以通过reset persist 接参数名的方式来清除某个指定的配置参数。

    2.6参数值的来源

    现在系统参数可以从多个来源进行设置，有时分不清参数值到底来自哪里，到底那种方式的设置在起作用，这时可以查询视图performance_schema.variables_info找到相关信息，例如：

    mysql> select variable_name, variable_source as source, 
variable_path, set_time, set_user as user, set_host 
from performance_schema.variables_info 
where variable_name='max_connections' or variable_name='socket'\G
*************************** 1. row ***************************
variable_name: max_connections
       source: DYNAMIC
variable_path: 
     set_time: 2021-08-06 14:30:07.128393
         user: root
     set_host: localhost
*************************** 2. row ***************************
variable_name: socket
       source: GLOBAL
variable_path: /etc/my.cnf  -- 参数文件名和路径
     set_time: NULL
         user: NULL
     set_host: NULL
2 rows in set (0.01 sec)

    2.7查询参数文件

      MySQL  localhost  SQL > select variable_path,variable_source,count(*) from performance_schema.variables_info where length(variable_path)!=0 group by variable_path,variable_source;
+--------------------------------------------------------+-----------------+----------+
| variable_path                                          | variable_source | count(*) |
+--------------------------------------------------------+-----------------+----------+
| /root/mysql-sandboxes/3320/my.cnf                      | EXPLICIT        |       23 |
| /root/mysql-sandboxes/3320/sandboxdata/mysqld-auto.cnf | PERSISTED       |        1 |
+--------------------------------------------------------+-----------------+----------+

    3.内存的分配

    3.1计算MySQL在负载高峰时占用的总内存

    mysql> select ( @@key_buffer_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@binlog_cache_size+ @@max_connections *( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@thread_stack + @@tmp_table_size ) ) / (1024 * 1024 * 1024) as max_memory_gb;

    在实际工作中，这里算出来的数值通常偏大，因为所有的线程都同时用到设定内存分配 的最大值的情况几乎不会出现，每个线程如果只是处理简单的工作，大约只需要 256KB 的 内存。通过查询 sys.memory_global_total 视图可以得到当前 MySQL 实例使用内存总和。

    系统参数 key_buffer_size
    系统参数 key_buffer_size 从字面上理解是指定索引缓存的大小，需要注意的是它只对 MyISAM 表起作用，对 InnoDB 表无效。这个参数在字面上并没有明确加上 MyISAM，是因 为它是在 MyISAM 作为 MySQL 默认存储引擎的时代产生的。由于现在通常用的是 InnoDB 表，因此通常不需要调整这个参数。

    系统参数 innodb_buffer_pool_size
    MySQL 的默认配置是针对内存为512MB 的虚拟机设计的，innodb_buffer_pool_size 默认值是128MB，这个值在生产中通常都太小。
    当一台服务器被一个 MySQL 实例独占 时，通常 innodb_buffer_pool_size 可以设置为内存的 70%左右。
    如果在同一台服务器上还有 其他的 MySQL 或别的应用，设置 innodb_buffer_pool_size 的大小就要考虑更多因素，一个重要的因素是 InnoDB 的总数据量(包括表和索引)。

    InnoDB 的总数据量(包括表和索引)

    mysql> SELECT count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') num_row,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size
FROM information_schema.TABLES WHERE engine='InnoDB';

    把参数 innodb_buffer_pool_size 设置成超过 InnoDB 的总数据量是没有意义的，通常设置到能容纳 InnoDB 的活跃数据就够了。

    3.2InnoDB缓存池的命中率

    两个MySQL的状态参数可以计算出它的命中率：
    （1）Innodb_buffer_pool_read_requests：表示向InnoDB缓存池进行逻辑读的次数。
    （2）Innodb_buffer_pool_reads：表示从物理磁盘中读取数据的次数。
    InnoDB缓存池的命中率的计算公式如下：
    InnoDB缓存池的命中率=（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads）/ Innodb_buffer_pool_read_requests * 100%。

    InnoDB缓存池的命中率的计算例子

    mysql> show status like  'Innodb_buffer_pool_read%s';
+----------------------------------+---------+
| Variable_name                    | Value   |
+----------------------------------+---------+
| Innodb_buffer_pool_read_requests | 1059322 |
| Innodb_buffer_pool_reads         | 6091    |
+----------------------------------+---------+
2 rows in set (0.00 sec)

mysql> select (1059322-6091)/1059322*100 'InnoDB buffer pool hit';
+------------------------+
| InnoDB buffer pool hit |
+------------------------+
|                99.4250 |
+------------------------+

    状态参数Innodb_buffer_pool_reads
    代表MySQL不能从InnoDB缓存池读到需要的数据而不得不从硬盘中进行读的次数，使用下面的命令查询MySQL每秒从磁盘读的次数：

    $ mysqladmin extended-status  -ri1 | grep Innodb_buffer_pool_reads
| Innodb_buffer_pool_reads                              | 1476098323     |
| Innodb_buffer_pool_reads                              | 734            |
| Innodb_buffer_pool_reads                              | 987            | 
| Innodb_buffer_pool_reads                              | 595            |

    把这个值和硬盘的I/O能力进行对比，如果接近了硬盘处理I/O的上限，那么从操作系统层查看到的CPU用于等待I/O的时间（IO wait，例如vmstat中的cpu的wa或iostat中的%iowait）会变长，这时硬盘I/O就成了性能的瓶颈，增大InnoDB缓存池可能会减少MySQL访问硬盘的次数，提高数据库的性能。

    3.3设置InnoDB缓存大小

    太小的缓冲池可能会导致数据页被频繁地从磁盘读取到内存，引起性能下降。
    但如果设置得过大，又可能会造成内存被交换到位于硬盘的内存交换分区，引起性能急剧下降。
    这两种情况比较起来，把InnoDB缓存池设置小一些对性能的负面影响并不特别大。实际生产中，mysqld进程崩溃的一个常见原因是操作系统的内存耗尽，操作系统被迫把mysqld进程杀死。

    早期调整innodb_buffer_pool_size需要重新启动MySQL，从MySQL 5.7后，这个参数可以动态地进行调整，例如下面的命令把这个参数设置成256MB：

    mysql> set persist innodb_buffer_pool_size =256*1024*1024;

    在MySQL的错误日志中可以看到MySQL调整InnoDB缓存池的过程:

    2021-06-26T06:58:26.824890Z 6042 [Note] [MY-012398] [InnoDB] Requested to resize buffer pool. (new size: 268435456 bytes)
    2021-06-26T06:58:26.866619Z 0 [Note] [MY-011880] [InnoDB] Resizing buffer pool from 134217728 to 268435456 (unit=134217728).
    2021-06-26T06:58:26.892090Z 0 [Note] [MY-011880] [InnoDB] Disabling adaptive hash index.
    2021-06-26T06:58:26.899180Z 0 [Note] [MY-011885] [InnoDB] disabled adaptive hash index.
    2021-06-26T06:58:26.899225Z 0 [Note] [MY-011880] [InnoDB] Withdrawing blocks to be shrunken.
    2021-06-26T06:58:26.899251Z 0 [Note] [MY-011880] [InnoDB] Latching whole of buffer pool.
    2021-06-26T06:58:26.899294Z 0 [Note] [MY-011880] [InnoDB] buffer pool 0 : resizing with chunks 1 to 2.
    2021-06-26T06:58:26.918099Z 0 [Note] [MY-011891] [InnoDB] buffer pool 0 : 1 chunks (8192 blocks) were added.
    2021-06-26T06:58:26.920019Z 0 [Note] [MY-011894] [InnoDB] Completed to resize buffer pool from 134217728 to 268435456.
    2021-06-26T06:58:26.920066Z 0 [Note] [MY-011895] [InnoDB] Re-enabled adaptive hash index.
    2021-06-26T06:58:26.920136Z 0 [Note] [MY-011880] [InnoDB] Completed resizing buffer pool at 210626 4:28:26.

    3.4系统参数innodb_buffer_pool_instances

    一个和相关innodb_buffer_pool_size的参数是innodb_buffer_pool_instances，它设定把InnoDB缓存池分成几个区，当innodb_buffer_pool_size大于1GB时，这个参数才会起作用，对于大的InnoDB缓存池，建议把它设置得大一些，这样可以减少获取访问InnoDB缓存池时需要上锁的粒度，以提高并发度。
    (不要超过服务器CPU核心数)

    4.InnoDB日志的配置

    4.1InnoDB日志概述

    InnoDB日志保存着已经提交的数据变化，用于在崩溃恢复时把数据库的变化恢复到数据文件，除了崩溃恢复，其他时候都不会读日志文件。向日志文件写数据的方式是顺序写，这比离散写的效率要高很多，而向数据文件写数据通常是离散写比较多。
    日志缓冲区是一个内存缓冲区，InnoDB使用它来缓冲重做日志事件，然后再将其写入磁盘。日志缓冲区的大小由系统参数innodb_log_buffer_size控制，默认是16 MB，在大多数情况下是够用的。 如果有大型事务或大量较小的并发事务，可以考虑增大innodb_log_buffer_size，这个参数在MySQL 8中可以动态设置。
    默认在datadir下有两个48MB的日志文件ib_logfile0和ib_logfile1。

    4.2日志产生量

    InnoDB的日志产生量是衡量数据库繁忙程度的重要指标，也是设置日志文件大小的依据。查询日志产生量的相关信息有两个方法。
    第一个方法是查询information_schema.innodb_metrics或sys.metrics视图中的对应计量值。
    第二个方法是使用show engine innodb statu命令查询日志产生量的相关信息，这些信息在输出的LOG部分，这种方法不需要激活InnoDB中的相关计量。

    查询日志视图
    使用下面的命令可以激活这些计量：

    mysql> set global innodb_monitor_enable = 'log_lsn_%';

    激活后，一个查询结果的例子如下：

    mysql> select name,count,status from information_schema.innodb_metrics where name like 'log_lsn%';
+--------------------------------+-----------+---------+
| name                           | count     | STATUS  |
+--------------------------------+-----------+---------+
| log_lsn_last_flush             | 421908956 | enabled |
| log_lsn_last_checkpoint        | 404648325 | enabled |
| log_lsn_current                | 421909901 | enabled |
| log_lsn_archived               |         0 | enabled |
| log_lsn_checkpoint_age         |  17261576 | enabled |
| log_lsn_buf_dirty_pages_added  | 421909901 | enabled |
| log_lsn_buf_pool_oldest_approx | 408201960 | enabled |
| log_lsn_buf_pool_oldest_lwm    | 406104808 | enabled |
+--------------------------------+-----------+---------+
8 rows in set (0.00 sec)

    这里的log_lsn_checkpoint_age是当前日志量减去最近一次检查点的日志量，等于log_lsn_current减去log_lsn_last_checkpoint，也就是日志文件的使用量，因为对日志文件的写入是循环覆盖的，检查点之前的日志都已经写入数据文件了，不再需要了，可以被覆盖。这里看到的日志文件的使用量大约是17MB。

    使用show engine innodb statu查询日志产生量

    mysql>  show engine innodb status\G
...
---
LOG
---
Log sequence number          425640652
Log buffer assigned up to    425640652
Log buffer completed up to   425640652
Log written up to            425640652
Log flushed up to            425639974
Added dirty pages up to      425640652
Pages flushed up to          407036166
Last checkpoint at           406841423
252823 log i/o's done, 608.37 log i/o's/second
...

    这里的lsn是425640652，最近一次检查点的lsn是406841423，计算出当前日志文件的使用量是这两个值之差：

    mysql> select round((425640652-406841423)/1024/1024) logsize_MB;
+------------+
| logsize_MB |
+------------+
|         18 |
+------------+
1 row in set (0.00 sec)

    当前日志文件的使用量大约18MB。

    4.3设置日志文件大小

    MySQL默认在数据目录下有两个48MB的日志文件，ib_logfile0和ib_logfile1。对于繁忙的数据库，这样的日志文件通常太小，因为当日志文件写满时，会触发检查点，把内存中的数据写入磁盘，小的日志文件会频繁地触发检查点，增加写磁盘频率，引起系统性能下降。
    大的日志文件能容纳的数据变化量大，会造成数据库在崩溃恢复时耗时较长，但新的MySQL版本的崩溃恢复速度已经很快了，因此把日志文件设置得大一些通常不会错，甚至可以设置得和InnoDB缓存池一样大。
    另外一些备份工具要备份在备份过程中产生的重做日志， 如果日志文件过小，备份工具备份日志的速度跟不上日志产生的速度时，需要备份的日志可能已经被覆盖了，例如XtraBackup工具可能会遇到下面的错误：

    xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or log files being too small.

    计算日志产生量
    一个合理大小的日志文件应该可以容纳数据库在高峰时1到2个小时的数据变化。下面的例子是查询一分钟产生的日志量：
    设置pager只显示lsn：

    mysql> pager grep sequence
PAGER set to 'grep sequence'

    查询当前的lsn：

    mysql> show engine innodb status \G
Log sequence number          1439955157
1 row in set (0.00 sec)

    休眠一分钟：

    mysql> select sleep(60);

    再次查询当前的lsn：

    mysql> show engine innodb status \G
Log sequence number          1455007613
1 row in set (0.01 sec)

    取消设置的pager：

    mysql> nopager
PAGER set to stdout

    根据一分钟的采样，可以计算出一个小时产生的日志量：

    mysql> select round((1455007613-1439955157)*60/1024/1024) "1 hour log(MB)";
+----------------+
| 1 hour log(MB) |
+----------------+
|            861 |
+----------------+
1 row in set (0.00 sec)

    这里一个小时的日志量是861MB。

    决定日志文件的两个参数
    日志文件的大小有两个参数决定：

    • （1）innodb_log_files_in_group：表示一个组里有多少个文件，默认为2。
    • （2）innodb_log_file_size：表示单个日志文件的大小，默认为48MB。

    因此如果保持innodb_log_files_in_group为2 不变，把innodb_log_file_size设置为860MB，可以容纳高峰期两个小时的日志。

    修改日志文件大小的方法
    修改日志文件大小的方法很简单，只需要修改参数文件中的innodb_log_file_size的设置，然后重新启动MySQL即可。不需要删除当前的日志文件，在启动过程中，MySQL会发现参数值和当前日志文件的大小不一样，然后自动删除旧的日志文件，并创建新的日志文件，在MySQL的错误日志里会有如下记录：

    1 [Note] [MY-013041] [InnoDB] Resizing redo log from 250331648 to 2901775360 bytes, LSN=1457612031
    1 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
    1 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite log files.
    1 [Note] [MY-013575] [InnoDB] Creating log file ./ib_logfile101
    1 [Note] [MY-013575] [InnoDB] Creating log file ./ib_logfile1
    1 [Note] [MY-012892] [InnoDB] Renaming log file ./ib_logfile101 to ./ib_logfile0
    1 [Note] [MY-012893] [InnoDB] New log files created, LSN=1457612300
    1 [Note] [MY-013083] [InnoDB] Log background threads are being started...

    5.innodb_dedicated_server

    MySQL 8 中新引进了一个参数innodb_dedicated_server，这个参数的默认值是off，就像这个参数名所建议的一样，当MySQL独占当前服务器资源的时候，可以把这个参数设置为on，这时MySQL会自动探测当前服务器的内存大小并设置下面4个参数：

    • （1）innodb_buffer_pool_size
    • （2）innodb_log_file_size
    • （3）innodb_log_files_in_group
    • （4）innodb_flush_method

    其中前面3个参数是根据当前服务器的内存大小计算出来的，这样对运维在虚拟机或云上运行的MySQL很方便，当调整了内存的大小后，MySQL会在启动时自动调整这3个参数，省去了每次手工修改参数的工作。

    innodb_buffer_pool_size根据物理内存的设置策略

    内存大小 innodb_buffer_pool_size值
    <1GB 128MB
    1GB至4GB 物理内存×0.5
    >4GB 物理内存×0.75

    innodb_log_file_size设置策略
    innodb_log_file_size和innodb_log_files_in_group两个参数是根据innodb_buffer_pool_size计算出来的。

    innodb_buffer_pool_size值 innodb_log_file_size值
    <8GB 512MB
    8GB至16GB 1024MB
    >16GB 2GB

    innodb_log_file_in_group设置策略

    innodb_buffer_pool_size值 innodb_log_file_in_group值
    <8GB 以GB为单位对innodb_buffer_pool_size取整
    8GB至128GB 以GB为单位对(innodb_buffer_pool_size×0.75)取整
    >128GB 64

    显式设置的参数优先生效
    当参数innodb_dedicated_server为ON时，如果还显式设置了这些参数，则显式设置的这些参数会优先生效，并且在MySQL的错误日志中会记录如下内容：

    0 [Warning] [MY-012360] [InnoDB] Option innodb_dedicated_server is ignored for innodb_log_file_size because innodb_log_file_size=2073034752 is specified explicitly.

    显式指定某一个值，并不会影响另外3个参数值的自动设定。

    MySQL的启动探测
    当参数innodb_dedicated_server为ON时，MySQL每次启动时会自动探测服务器的内存并自动调整上述几个参数值。在任何时候MySQL都不会将自适应值保存在持久配置中，利用这个参数就可以保证服务器（包括虚拟机或者容器）扩展以后，MySQL能“自动适应”，以尽量利用更多的服务器资源

    6.硬盘读写参数

    硬盘的读写通常是对数据库性能最大的因素之一。这里介绍几个影响硬盘读写的重要参数。
    innodb_flush_log_at_trx_commit
    sync_binlog
    innodb_flush_method
    innodb_io_capacity和innodb_io_capacity_max

    6.1 innodb_flush_log_at_trx_commit

    innodb_flush_log_at_trx_commit参数控制事务提交时写重做日志的行为方式，它有三个值：0、1和2。

    • （1）默认值为1，每次事务提交的时候都会将日志缓存中的数据写入到日志文件，同时还会触发文件系统到磁盘的同步，如果发生系统崩溃，数据是零丢失，这种方式对数据是最安全的，但性能是最慢的，因为把数据从缓存同步到磁盘的成本很高。这种方式适用于对数据安全性要求高的行业，如银行业。但很多互联网的应用，对数据的安全性要求不太高，而对性能的要求很高，设置成0或2会更合适。
    • （2）设置成0时，事务提交的时候不会触发写日志文件的操作，日志缓存中的数据以每秒一次的频率写入到日志文件中，同时还会进行文件系统到磁盘的同步操作。
    • （3）设置成2时，事务提交的时候会写日志文件，但文件系统到磁盘的同步是每秒进行一次。

    0和2都是每秒进行一次文件系统到磁盘的同步，因此这两种方式的性能都差不多，当系统崩溃时，最多丢失1秒的数据。但0和2还有细微的不同，当设置成2时，每次事务提交都写日志文件，因此数据已经从MySQL的日志缓存刷新到了操作系统的文件缓存，如果只是MySQL崩溃，而操作系统没有崩溃，将不会丢失数据。因此0和2比较起来，通常设置为2比较好。

    6.2 sync_binlog

    sync_binlog参数控制事务提交时写二进制日志的行为方式，它有三个值：0、1和N。

    • （1）默认值为1，每次事务提交的时候都会把二进制日志刷新到磁盘，这种方式对数据是最安全的，但性能是最慢的。
    • （2）设置成0时，事务提交的时候不会把二进制日志刷新到磁盘，刷磁盘的动作由操作系统控制。
    • （3）设置成N（N不等于0或1）时，每进行N事务提交后会进行一次把二进制日志刷新到磁盘的动作。

    没有备库和使用二进制日志进行时间点恢复的需求时，可以把sync_binlog参数设置为0或N，设置为0是把刷新二进制日志文件的操作交给操作系统决定，但操作系统可能会在二进制日志文件写满进行切换时才刷新磁盘文件，这样会造成数秒的延迟，在这期间事务无法提交，因此把这个参数设置成100或1000之类的一个合理数值比设置成0好。

    如果使用二进制日志进行主库和备库之间的数据同步，或者使用二进制日志进行时间点恢复，并且对数据一致性要求高时，把sync_binlog参数设置为1，同时要把innodb_flush_log_trx_commit参数也设置为1。把这两个参数都设置成1对性能的负面影响很大，为了提高性能，这时使用的存储应该是带缓存的，并且设置成Write-back，而不是Write-through，这样数据只写入到存储的缓存中即返回。但存储的缓存应该是带电池的，如果缓存不带电池，或者电池没有电，突然发生掉电的时候，不仅数据会丢失，而且会造成数据库损坏，无法启动，这种情况要比丢失一秒钟的数据要糟糕得多。
    写二进制日志的成本比写重做日志的成本要高得多，因为重做日志的大小和文件名是固定的，重做日志循环写入日志文件。而每次写二进制日志时，文件都会进行扩展，如果写满了还要新建文件，这样每次写二进制日志不但要写数据，还要修改二进制日志文件的元数据，因此把sync_binlog设置成1比把innodb_flush_log_trx_commit设置成1对性能负面影响还要大得多。

    6.3 innodb_flush_method

    innodb_flush_method参数控制MySQL将数据刷到InnoDB的数据文件和日志文件的动作。在Windows系统上有两个选项：unbuffered是默认和推荐的选项，另外一个是normal。Linux系统上，常用的选项有一下几种：

    • fsync：是默认值，使用fsync()系统调用刷新数据文件和日志文件，数据会在操作系统的缓存中保存。
    • O_DSYNC：InnoDB使用O_SYNC打开和刷新日志文件，使用fsync()刷新数据文件。
    • O_DIRECT：使用O_DIRECT打开数据文件，使用fsync()系统调用刷新数据文件和日志文件，数据不会在操作系统的缓存中保存。
    • O_DIRECT_NO_FSYNC：使用O_DIRECT刷新I/O，但写磁盘时不执行fsync()。

    通常对于硬盘性能好的服务器，可以设置成O_DIRECT，这样避免在InnoDB缓存和操作系统缓存中存有两份数据，而且InnoDB缓存比操作系统缓存效率要高，因为InnoDB缓存是专门针为InnoDB的数据设计的，而操作系统缓存是为通用的数据设计的。
    设置成O_DIRECT_NO_FSYNC时，因为写磁盘时不执行fsync()，速度可能会快，但突然断电时可能会丢失数据。
    对于读操作大大多于写操作的应用，设置成fsync会比设置成O_DIRECT性能略好。
    但如何选择这些参数最终需要经过测试才能确定，测试时要注意观察状态参数Innodb_data_fsyncs，它记录着调用fsync()的次数。通常fsync和O_DIRECT调用fsync()的次数差不多，O_DIRECT_NO_FSYNC调用fsync()的次数最少。

    6.4 innodb_io_capacity和innodb_io_capacity_max

    InnoDB后台线程会进行一些I/O操作，例如把缓冲池中的脏页刷新到磁盘，或将更改从更改缓冲区写入到对应的二级索引。InnoDB试图以不影响服务器正常工作的方式执行这些I/O操作，这需要它知道系统的I/O的处理能力，它根据参数innodb_io_capacity评估系统的I/O带宽。参数innodb_io_capacity_max值定义了系统I/O能力的上限，防止在I/O的峰值时消耗服务器的全部I/O带宽。
    通常可以把innodb_io_capacity设置得低一些，但不要低到后台I/O滞后的程度。如果该值太高，数据将很快从缓冲池中被移除，不能充分发挥缓存的优势。但对于繁忙而且具有较高I/O处理能力的系统，可以设置一个较高的值来帮助服务器处理与数据快速变更相关联的后台维护工作。

    这两个参数的默认值如下：

    mysql> show variables like 'innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+

    这两个参数的设定是基于系统的每秒能处理的I/O数量（IOPS），可以把innodb_io_capacity_max设置成极限的IOPS，innodb_io_capacity设置成它的一半左右。目前业界有很多I/O测试软件可以测出系统的IOPS，也可以通过硬盘配置进行估算，例如一块15K转速的传统硬盘的IOPS的参考值大约是200，高端SSD盘可以达到60万。
    状态参数Innodb_data_fsyncs记录着数据刷新到磁盘的次数，把innodb_io_capacity调大后，可以看到这个状态参数也相应的增加了。

    7.其他参数

    7.1 max_connections

    系统参数max_connections设置了允许的服务器最多连接数，防止服务器因为连接数过多而造成资源耗尽，默认是151，这在生产环境通常偏小。这个参数应当设置为经过压力测试验证后系统能承受的最多连接数。可以参考状态参数Max_used_connections和Max_used_connections_time，它们记录了系统连接数曾经达到的最大值和发生时间。

    mysql> show status like 'max_used%';

    7.2 binlog_order_commits

    系统参数binlog_order_commits默认为on，如果把这个参数设置为off将不能保证事务的提交顺序和写入二进制日志的顺序一致，这不会影响到数据一致性，在高并发场景下还能提升一定的吞吐量。

    7.3 skip_name_resolve

    系统参数skip_name_resolve默认为off，这时MySQL每收到一个连接请求，都会进行正向和反向DNS解析，建议设置成on，禁止域名解析，这样会加快客户端连接到MySQL服务器的速度。当DNS服务器运行正常时，这个优势并不明显，如果DNS服务器出故障，或者变慢，进行域名解析的时间可能会很长，甚至会拒绝连接。如果解析不成功，在错误日志里面会有类似下面的提示：

    40162 [Warning] [MY-010055] [Server] IP address '192.168.87.178' could not be resolved: Name or service not known

    把这个参数设置成on也有弊端，就是只能使用IP进行grant赋权，不能使用主机名，通常主机名不会变，而IP改变的可能性比主机名大。因此在一个生产主机上把skip_name_resolve从off改成on要小心，因为原来用主机名赋予的权限不能用了。

    8.资源组

    8.1概述

    MySQL 8中引入了资源组（Resource Groups）的概念，它可以设定某一类SQL语句所允许使用的资源（目前只包括CPU）。在高并发的系统中，资源组可以保证关键交易的性能，例如可以设定市场统计类的交易在白天使用较少的资源，以免影响客户的交易，在晚上可以使用较多的资源。

    8.2查询资源组

    在information_schema.resource_groups视图中可以查询资源中的信息，默认有两个资源组：

    mysql> select * from information_schema.resource_groups\G
*************************** 1. row ***************************
   RESOURCE_GROUP_NAME: USR_default
   RESOURCE_GROUP_TYPE: USER
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 0-3
       THREAD_PRIORITY: 0
*************************** 2. row ***************************
   RESOURCE_GROUP_NAME: SYS_default
   RESOURCE_GROUP_TYPE: SYSTEM
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 0-3
       THREAD_PRIORITY: 0
2 rows in set (0.03 sec)

    8.3创建资源组

    使用create resource group语句可以创建资源组，创建一个batch用户资源组的例子如下：

    mysql> create resource group Batch  type = user  vcpu = 2-3  thread_priority = 10;

    创建完成后查看这个用户资源组的信息如下：

    mysql> select * from information_schema.resource_groups where resource_group_name = 'Batch'\G  
*************************** 1. row ***************************   
   RESOURCE_GROUP_NAME: Batch   
   RESOURCE_GROUP_TYPE: USER
RESOURCE_GROUP_ENABLED: 1              
              VCPU_IDS: 2-3       
       THREAD_PRIORITY: 10

    8.4修改资源组的属性

    在系统高负载的时间段，减少分配给资源组的CPU数量，并降低其优先级：

    mysql> alter resource group Batch  vcpu = 3 thread_priority = 19;

    在系统负载较轻的情况下，增加分配给组的CPU数量，并提高其优先级：

    mysql> alter resource group Batch  vcpu = 0-3  thread_priority = 0;

    注意，用户线程的优先级不能小于0：

    mysql> alter resource group Batch  vcpu = 3   thread_priority = -9;
ERROR 3654 (HY000): Invalid thread priority value -9 for User resource group Batch. Allowed range is [0, 19].

    8.5使用资源组

    激活Batch资源组的命令如下：

    mysql> alter resource group Batch enable;

    删除Batch资源组的命令如下：

    mysql> drop resource group Batch;

    要将线程分配给Batch资源组，执行以下操作：

    mysql> set resource group Batch for thread_id;

    当thread_id有多个时，中间用逗号隔开。
    如果要把当前线程设定到 Batch资源组中，在会话中执行以下语句：

    mysql> set resource group batch;

    此后，会话中的语句将使用Batch资源组的资源进行执行 。
    要使用Batch组执行单个语句 ，请使用 resource_group优化程序提示：

    mysql> insert /*+ resource_group(Batch) */ into t2 values(2);

    在SQL语句里设置提示的方法可以和MySQL的中间件结合起来使用，例如ProxySQL支持在SQL语句中增加提示。

    8.6查询线程使用的资源组

    可以在performance_schema.threads视图中的resource_group字段查询线程使用的资源组，相应的命令和输出结果如下：

    mysql> select thread_id, resource_group from performance_schema.threads where thread_id=10054;
+-----------+----------------+
| thread_id | resource_group |
+-----------+----------------+
|     10054 | Batch          |
+-----------+----------------+
1 row in set (0.00 sec)

    8.6资源组的限制

    资源组目前在使用中还是一些限制：

    • 如果安装了线程池插件，则资源组不可用。
    • 资源组在macOS上不可用，因为它不提供用于将CPU绑定到线程的API。
    • 在FreeBSD和Solaris上，忽略资源组线程优先级，尝试更改优先级会导致警告。实际上，所有线程都以优先级0运行。
    • 在Linux上，需要对mysqld进程设置CAP_SYS_NICE功能，否则将忽略资源组线程优先级。

    8.7 在Linux上设置CAP_SYS_NICE功能

    CAP_SYS_NICE可以使用setcap命令手动设置该功能，使用getcap检查功能。相应命令和输出结果如下：

    # setcap cap_sys_nice+ep /usr/sbin/mysqld
# getcap /usr/sbin/mysqld
/usr/sbin/mysqld = cap_sys_nice+ep

    或者使用sudo systemctl edit mysql在MySQL服务里增加下面的内容：

    [Service] AmbientCapabilities=CAP_SYS_NICE

    然后重新启动MySQL服务，设置线程优先级才能生效。

    8.8Windows平台上的线程的优先级

    线程优先级范围 Windows优先级
    -20到-10 THREAD_PRIORITY_HIGHEST
    -9到-1 THREAD_PRIORITY_ABOVE_NORMAL
    0 THREAD_PRIORITY_NORMAL
    1到10 THREAD_PRIORITY_BELOW_NORMAL
    10到19 THREAD_PRIORITY_LOWEST

    SQL优化

    9.查找TOP SQL

    9.1概述

    当要对MySQL进行优化时，找到TOP SQL语句通常是第一步。这里介绍的使用不需要另外安装工具的找出需要优化的SQL的方法。
    从操作系统层监控到的最繁忙线程找出TOP SQL

    • 慢查询日志
    • 性能视图
    • sys数据库中的存储过程：
      diagnostics()存储过程
      ps_trace_statement_digest()存储过程
      statement_performance_analyzer()存储过程
      ps_trace_thread()存储过程

    9.2繁忙的线程执行TOP SQL

    例如使用linux系统中的top加上-H参数可以按查看按繁忙程度排序的线程：

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    29345 mysql 20 0 2828020 512200 18624 R 22.9 13.2 0:24.10 mysqld
    26089 mysql 20 0 2828020 512200 18624 D 6.0 13.2 1:57.51 mysqld

    可以看到最繁忙的线程号是29345，根据线程号可以从performance_schema.threads视图中找到正在执行的SQL语句：

    mysql> select * from performance_schema.threads where thread_os_id=29345\G
*************************** 1. row ***************************
          THREAD_ID: 75
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 32
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: mysqlslap
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: waiting for handler commit
   PROCESSLIST_INFO: insert into table_a(col2) values(md5(rand()))
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 29345
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

    如果有必要，可以使用PROCESSLIST_ID字段指定的线程号杀死这个线程或正在执行的SQL，不能使用THREAD_ID字段值执行kill命令，不然可能会杀错了线程：

    mysql> kill query 32;

    或者

    mysql> kill 32;

    9.3慢查询日志

    相关参数：
    long_query_time：响应时间超过这个参数值的SQL语句被定义为慢SQL，默认10秒。
    slow_query_log：是否激活慢查询日志，默认为off。
    slow_query_log_file：慢速查询日志文件的路径和文件名，默认/var/lib/mysql/localhost-slow.log。
    log_slow_extra：从MySQL 8.0.14版本开始才有的，当它为true时，将记录与执行的SQL语句相关的额外信息

    下面是设置long_query_time = 0时记录的一条SQL语句的默认内容如下：

    # Time: 2021-01-22T16:22:21.177507+08:00
# User@Host: root[root] @ localhost []  Id:   115
# Query_time: 0.000781  Lock_time: 0.000361 Rows_sent: 1  Rows_examined: 1
SET timestamp=1611303741;
SELECT s_quantity, s_data, s_dist_01 FROM stock WHERE s_i_id = 48241 AND s_w_id = 3;

    当参数log_slow_extra 设置为on时，执行同样的SQL语句，记录的信息如下：

    # Time: 2021-01-22T17:13:08.765664+08:00
# User@Host: root[root] @ localhost []  Id:   117
# Query_time: 0.000558  Lock_time: 0.000330 Rows_sent: 1  Rows_examined: 1 Thread_id: 117 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 265 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2021-01-22T17:13:08.765106+08:00 End: 2021-01-22T17:13:08.765664+08:00
SET timestamp=1611306788;
SELECT s_quantity, s_data, s_dist_01 FROM stock WHERE s_i_id = 48241 AND s_w_id = 3;

    在slow_query_log为on时，记录慢查询语句的个数。

    # mysql -e "select 1 where 0=sleep(11)"
+---+
| 1 |
+---+
| 1 |
+---+
#  mysqladmin  extended-status  |grep -i slow_q
| Slow_queries                                          | 1

    9.4性能视图

    在视图sys.statement_analysis中找出总计执行时间最长的SQL语句：

    mysql> select * from sys.statement_analysis limit 1\G

    视图sys.statement_analysis已经按照总延迟时间降序排序，因此第一条记录就是总计用时最长的SQL。还可以在视图sys.statements_with_runtimes_in_95th_percentile中可以查询到运行时间最长的5%的语句。

    下面的SQL语句列出平均执行时间最长的语句，这类SQL语句通常优化的空间最大：

    mysql> select * from performance_schema.events_statements_summary_by_digest order by avg_timer_wait desc limit 1\G

    下面的SQL语句列出执行次数最多的SQL语句，这类SQL语句通常对整体系统性能影响最大：

    mysql> select * from performance_schema.events_statements_summary_by_digest order by count_star desc limit 1\G

    下面的SQL语句列出检查行数最多的SQL语句，这类SQL语句通常消耗最多硬盘的读写：

    mysql> select * from performance_schema.events_statements_summary_by_digest order by sum_rows_examined desc limit 1\G

    下面的SQL语句列出返回行数最多的SQL语句，这类SQL语句通常占用最多网络带宽：

    mysql> select * from performance_schema.events_statements_summary_by_digest order by sum_rows_sent desc limit 1\G

    sys.statements_with_runtimes_in_95th_percentile视图中包括了最慢的5%的SQL语句：

    mysql> select * from sys.statements_with_runtimes_in_95th_percentile\G

    9.5 sys数据库中的存储过程

    性能视图记录的信息是自系统启动以来的所有信息，当前的性能问题可能在视图里被稀释了，使用sys的存储过程可以收集当前的信息信息。

      1. diagnostics()存储过程
      1. ps_trace_statement_digest()存储过程
      1. statement_performance_analyzer()存储过程
      1. ps_trace_thread()存储过程

    diagnostics()存储过程会生成一个关于当前MySQL实例整体性能的诊断报告，例如：

    mysql> tee diagnostics.log
mysql> call sys.diagnostics(null,null,'current');
mysql> notee;

    具体可参考：

    https://blog.csdn.net/ActionTech/article/details/116496833

    ps_trace_statement_digest()存储过程可以根据提供的SQL语句摘要哈希值跟踪收集这些SQL语句的执行过程中的性能诊断信息。
    下面的例子是在60秒内跟踪指定的SQL语句，每0.1秒收集一次性能诊断信息：

    mysql> call sys.ps_trace_statement_digest(@digest, 60, 0.1, true, true);

    statement_performance_analyzer()存储过程可以生成当前MySQL实例中正在运行的SQL语句的两个快照，并对比这两个快照，生成增量报告。

    ps_trace_thread()存储过程可以跟踪某个线程的执行过程，把这个线程执行的所有SQL语句的性能信息都记录下来，并输出报告。这个存储过程适合用于执行存储过程或多个SQL语句的线程。
    例如：采用当前的配置启动对51号线程的跟踪，跟踪60秒，每1秒收集一次性能信息，生成性能报告文件：

    mysql> call sys.ps_trace_thread(51,'/tmp/td_51.dot',null, null, true,false, false);

    10.执行计划

    10.1概述

    SQL语句只是告诉了数据库要做什么，并没有告诉数据库如何做，查看SQL语句的执行计划可以使SQL的执行过程从黑盒变成白盒。
    在SQL语句前面加上EXPLAIN即可查看SQL语句的执行计划，但不会实际执行这个SQL语句。
    显示SQL语句的执行计划的格式有三种，分别是：传统（TRADITIONAL）、JSON和树形（TREE）格式。可以使用FORMAT=TRADITIONAL|JSON|TREE来指定格式，默认是传统格式。
    显示SQL语句的执行计划不光支持select，还支持delete、insert、replace和update，但explain analyze是例外。

    10.2传统格式

    传统格式提供了执行计划的概况、索引的使用等基本信息，下面是一个查询SQL语句的执行计划的例子：

    mysql> explain select city from city where country_id=(select country_id from country where country='China') ；

    在MySQL官方文档中的第8章Optimization中有关于explain的详细介绍。
    https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html

    执行计划中有两个ref：
    type：连接类型，ALL是全表扫描，这是成本最高的访问方式，ref是使用非唯一索引访问，const是使用主键或唯一索引访问。
    ref：索引过滤的字段，const代表常量。

    EXPLAIN语句执行完成后提示有一个警告信息，警告信息里包括的是优化器重写的伪SQL，这个SQL不一定是能执行的，使用\W打开\w关闭警告信息。
    下面的命令显示前面的SQL执行计划的警告信息：

    mysql> show warnings\G

    10.3 JSON格式

    JSON格式提供了以JSON格式显示的详细执行计划，这个格式适合于被程序调用，例如图形工具workbench显示的图形化的执行计划就是调用了JSON格式的接口。下面是前面SQL语句的JSON格式的执行计划：

    mysql> explain format=json select city from city where country_id=(select country_id from country where country='China') \G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
...

    在JSON格式中cost_info元素提供了估算的执行成本。

    MySQL的图形工具MySQL Workbench可以以图形方式显示JSON格式的执行计划。在MySQL Workbench里显示执行计划有两种方法：

    • （1）在SQL语句没有执行时，在SQL语句的输入框的上方有个闪电上面带放大镜的图标，点击这个图标就会生产这个SQL语句的执行计划。这种方式适合于SQL语句执行时间长，或者SQL语句将修改数据的情况下使用。
    • （2）执行SQL语句后，在输出结果的右边有个“Execution Plan”的图标，点击这个图标也会以图形方式显示SQL语句的执行计划。

    在这个图形里，每个方框代表一个执行步骤，方框的上面左边数字是估算的执行成本，右边数据是估算的输出行数，下边是表名和索引名，其中索引名用加粗的方式显示。方框的颜色和相对执行成本相关，从低到高依次是：蓝、绿、黄、橘、红。把鼠标停留在方框上还可以显示更加详细的信息，例如这个图里鼠标就停留在最下面的一步。

    10.4树形格式

    树形格式是从MySQL 8.0.18开始引入格式，它提供的执行计划比传统的执行计划更详细，输出格式是树形的，例如：

    mysql> explain format=tree select city from city where country_id=(select country_id from country where country='China') \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (city.country_id = (select #2))  (cost=7.55 rows=53)
    -> Index lookup on city using idx_fk_country_id (country_id=(select #2))  (cost=7.55 rows=53)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: (country.country = 'China')  (cost=11.15 rows=11)
            -> Table scan on country  (cost=11.15 rows=109)

    EXPLAIN ANALYZE
    EXPLAIN ANALYZE实际上是树形的执行计划的扩展，它不但提供了执行计划，还检测并执行了SQL语句，提供了执行过程中的实际度量，例如：

    mysql> EXPLAIN ANALYZE select city from city where country_id=(select country_id from country where country='China') \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (city.country_id = (select #2))  (cost=7.55 rows=53) (actual time=23.970..23.989 rows=53 loops=1)
    -> Index lookup on city using idx_fk_country_id (country_id=(select #2))  (cost=7.55 rows=53) (actual time=23.967..23.981 rows=53 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: (country.country = 'China')  (cost=11.15 rows=11) (actual time=0.450..0.515 rows=1 loops=1)
            -> Table scan on country  (cost=11.15 rows=109) (actual time=0.405..0.467 rows=109 loops=1)

    10.5 explain for connection

    在实际工作中，如果发现一个正在执行的SQL语句耗时很长，这时想查询它的执行计划，通常的做法是使用EXPLAIN生成这个SQL语句的执行计划，但因为统计信息等原因，生成的执行计划和正在执行的执行计划可能不完全相同，更好的做法是使用explain for connection查询当前正在使用的执行计划。

    下面的SQL查询出当前的会话号：

    mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              17 |
+-----------------+
1 row in set (0.00 sec)

    或者使用show processlist查询会话号。在当前的会话中执行一个慢SQL语句：

    mysql> select sleep(60), city from city where country_id=(select country_id from country where country='China') \G

    根据会话号在其他会话里查询正在执行的SQL语句的执行计划：

    mysql> explain for connection  17\G

    11.SQL执行性能的评估

    11.1概述

    查看SQL执行性能的最简单方法是看SQL执行完成的时间，除此之外还有：性能视图、状态变量、Explain analyze、操作系统层监控

    11.2性能视图

    MySQL自带的performance_schema和sys数据库的很多性能视图记录了SQL语句的执行性能:

    $ mysqlshow performance_schema|grep events_statements_
| events_statements_current                            |
| events_statements_histogram_by_digest                |
| events_statements_histogram_global                   |
| events_statements_history                            |
| events_statements_history_long                       |
| 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       |

    等待时间最长的3个SQL语句
    下面语句找出等待时间最长的3个SQL语句,注意观察其中与性能相关的字段：

    mysql>  select * from events_statements_summary_by_digest  where schema_name!='performance_schema'   order by sum_timer_wait desc limit 3\G
*************************** 1. row ***************************
                SCHEMA_NAME: mysqlslap
                     DIGEST: 81ad8cba9e3d47205f6b7d87d577bfed61357053ed281c7cbe9a0c21e42adf45
                DIGEST_TEXT: INSERT INTO `table_b` ( `col2` ) VALUES ( `md5` ( `rand` ( ) ) )
                 COUNT_STAR: 999815
             SUM_TIMER_WAIT: 2934990225631000
             MIN_TIMER_WAIT: 518036000
             AVG_TIMER_WAIT: 2935533000
             MAX_TIMER_WAIT: 83531551000
              SUM_LOCK_TIME: 56119158000000
                 SUM_ERRORS: 20
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 999853...

    I/O性能
    首先重置performance_schema.file_summary_by_event_name视图：

    mysql> truncate table performance_schema.file_summary_by_event_name;

    然后执行一个全表扫描的语句：

    mysql> select count(*) from testdb.table_a where col2<>'a';

    最后查询等待事件wait/io/file/innodb/innodb_data_file的性能信息，这些信息反映了全表扫描的性能：

    mysql> select event_name, count_read, avg_timer_read/1000000000.0 "Avg Read Time (ms)", sum_number_of_bytes_read/1024/1024 "MB Read" from performance_schema.file_summary_by_event_name where event_name='wait/io/file/innodb/innodb_data_file'\G
*************************** 1. row ***************************
        event_name: wait/io/file/innodb/innodb_data_file
        count_read: 4368 
Avg Read Time (ms): 0.5880
           MB Read: 68.25000
1 row in set (0.00 sec)

    11.3状态变量

    MySQL的自带了479个状态变量（MySQL 8.0.22）用以反映MySQL的运行状态，在mysql客户端里可以使用下面的命令查询会话和全局的状态变量：

    mysql> show session status;
mysql> show global status;

    关于具体状态变量的说明，参见：

    https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html。

    11.4使用mysqladmin监控性能

    也可以使用mysqladmin extended-status查询全局的状态变量。如果要查看一段时间状态变量的变化情况，可以使用下面的命令：

    $ mysqladmin extended-status -ri60 -c3|tee my_status

    其中：-i60表示每60秒重复执行一次，-r表示显示相邻两次查询的差值，-c3表示重复查询3次，tee命令表示把输出结果同时保存到文件。

    SQL语句的计数器
    Com_XXX是SQL语句的计数器，其中Com是command的缩写，XXX是指的SQL语句类型，这些计数器包括：
    Com_begin
    Com_commit
    Com_delete
    Com_insert
    Com_select
    Com_update
    查询这些SQL语句的计数器可以了解当前实例执行SQL语句的情况，可以使用下面的命令查询这些计数器：

    $ mysqladmin extended-status | grep Com_ |grep -E 'begin|commit|delete|insert|select|update'

    查询这些计数器在10秒间隔的变化值：

    $ mysqladmin extended-status -ri10 -c9| grep Com_ |grep -E 'begin|commit|delete|insert|select|update'

    处理InnoDB表的行数的计数器
    Innodb_rows_XXX是对应SQL语句处理InnoDB表的行数的计数器，XXX是指的SQL语句类型
    Innodb_rows_deleted
    Innodb_rows_inserted
    Innodb_rows_read
    Innodb_rows_updated
    查询这些行数的计数器可以了解当前实例处理行数的情况，可以使用下面的命令查询这些计数器：

    $ mysqladmin extended-status | grep Innodb_rows

    查询这些计数器在10秒间隔的变化值：

    $ mysqladmin extended-status -ri10 -c9 | grep Innodb_rows

    11.5查询单个SQL的状态参数

    Handler_*计数器统计了句柄操作，句柄API是MySQL和存储引擎之间的接口，其中Handler_read_*对调试SQL语句的性能很有用，在执行SQL语句之前，可以先使用flush status将当前会话的状态变量重置为零：

    mysql> flush status;

    然后执行一条SQL语句：

    mysql> select * from table_a where col1=999;

    再查询状态变量Handler_read_*的值：

    mysql> show session status like 'Handler_read%';

    对比另外一个SQL语句：

    mysql> select * from table_a where col2='efc45f14c8bf7ced3121488ff7a70123';

    last_query_cost状态变量
    查询最后执行的SQL语句的估算成本（注意不是实际执行成本，MySQL里没有实际执行成本）：

    mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

    观察状态变量值，可以看到最后一个SQL语句执行了一次索引访问，估计执行成本是1。

    如果要查询其他会话的状态变量值
    可以查询视图performance_schema.status_by_thread，例如下面的SQL查询所有会话中的状态变量Handler_write：

    mysql> select * from performance_schema.status_by_thread where variable_name='Handler_write';
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|        60 | Handler_write | 94             |
|        67 | Handler_write | 477            |-- 插入记录最多的线程
|        69 | Handler_write | 101            |
+-----------+---------------+----------------+
2 rows in set (0.01 sec)

    慢查询日志中的状态变量
    打开查询慢查询日志后，如果同时将系统参数log_slow_extra设置为true，也会记录和慢SQL语句相关的状态变量。

    11.6 Explain analyze

    使用explain analyze既可以看到估计成本，也能看到实际执行用时和访问的行数，而且每一步都可以看到这些计量信息，这样对精确定位SQL语句执行瓶颈很有帮助。

    11.7操作系统层监控

    从操作系统层也可以监控SQL语句的执行性能，MySQL需要消耗操作系统的4种资源：CPU、磁盘、内存和网络，在Linux系统上可以采用监控工具包括：top、free、vmstat、iostat、mpstat、sar和netstat等。

    12.MySQL的优化器

    12.1优化器开关

    系统变量optimizer_switch可以控制优化器的行为，它的值是一组标志，每个标志有on或者off两个值，以指示相应的优化器行为是否被启用或禁用。使用下面的命令查看当前优化器的值：

    mysql>  select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

    官方文档：8.9.2 Switchable Optimizations

    https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html

    改变优化器的开关可以控制优化器的行为
    下面这个SQL语句是取两个索引的交集进行数据访问：

    mysql> explain select * from actor where first_name='NICK' and last_name='WAHLBERG'\G
...

    修改optimizer_switch禁止使用index_merge_intersection的命令如下：

    mysql> SET optimizer_switch='index_merge_intersection=off';

    然后执行同样的SQL语句，它的执行计划就变了，不再使用两个索引的交集进行数据访问：

    mysql> explain select * from actor where first_name='NICK' and last_name='WAHLBERG'\G

    12.2计算执行计划的成本

    为了计算执行计划的成本，优化器使用了一个成本计算模型进行成本计算。MySQL把各种操作的估计成本放在mysql数据库的两个表中：

    • engine_cost表用于保存与特定引擎相关的操作成本，不同的引擎执行这些操作的成本不同。
    • server_cost表用于保存与服务相关的操作的成本，不同的服务器执行这些操作的成本不同，但跟引擎没有关系。

    mysql.engine_cost

    mysql> select engine_name,cost_name,cost_value,default_value  from mysql.engine_cost;
+-------------+------------------------+------------+---------------+
| engine_name | cost_name              | cost_value | default_value |
+-------------+------------------------+------------+---------------+
| default     | io_block_read_cost     |       NULL |             1 |
| default     | memory_block_read_cost |       NULL |          0.25 |
+-------------+------------------------+------------+---------------+

    mysql.server_cost

    mysql> select cost_name,cost_value,default_value  from mysql.server_cost;
+------------------------------+------------+---------------+
| cost_name                    | cost_value | default_value |
+------------------------------+------------+---------------+
| disk_temptable_create_cost   |       NULL |            20 |
| disk_temptable_row_cost      |       NULL |           0.5 |
| key_compare_cost             |       NULL |          0.05 |
| memory_temptable_create_cost |       NULL |             1 |
| memory_temptable_row_cost    |       NULL |           0.1 |
| row_evaluate_cost            |       NULL |           0.1 |
+------------------------------+------------+---------------+
6 rows in set (0.00 sec)

    修改成本计算值
    下面的例子是向mysql.engine_cost插入InnoDB引擎的成本计量，因为这个MySQL是安装在虚拟机上，硬盘IO很慢：

    mysql> insert into mysql.engine_cost (engine_name, device_type, cost_name,cost_value, comment) values ('InnoDB', 0, 'io_block_read_cost',2, 'Disk on virtual machine');
Query OK, 1 row affected (0.06 sec)

    使用下面到命令把修改刷新到内存中：

    mysql> flush optimizer_costs;

    如果把MySQL的临时表目录innodb_temp_tablespaces_dir和临时文件目录tmpdir设置到内存中（例如：设备/dev/shm或者文件系统tmpfs），可以提高对临时表和临时文件的处理速度，例如提高了10倍的处理速度，对应修改disk_temptable_create_cost和disk_temptable_row_cost成本如下：

    mysql> update mysql.server_cost set cost_value = 2,Comment = 'Temporary tables on memory' where cost_name = 'disk_temptable_create_cost';
mysql> update mysql.server_cost set cost_value = 0.05,Comment = 'Stored on memory disk' where cost_name = 'disk_temptable_row_cost';

    使用下面到命令把修改刷新到内存中：

    mysql> flush optimizer_costs;

    12.3优化器跟踪

    优化器跟踪（optimizer trace）功能可以跟踪优化器生成执行计划的过程，准确地知道优化器选择执行路径的原因，使用优化器跟踪分4步：
    （1）打开优化器跟踪功能：SET optimizer_trace="enabled=on"。
    （2）执行需要跟踪的SQL语句。
    （3）查询视图information_schema.optimizer_trace，重点关注trace字段中以JSON格式记录的优化器跟踪信息。
    （4）关闭优化器跟踪功能：SET optimizer_trace="enabled=off"。
    如果需要跟踪多个SQL语句的优化过程，可以重复第2和第3步

    两种执行计划的对比
    这里举一个例子，先查看下面两个SQL语句的执行计划：

    mysql> explain select * from payment where customer_id<150\G
mysql> explain select * from payment where customer_id<200\G

    从执行计划里可以看到，当查询payment表中的客户号小于150的记录时使用索引，查询客户号小于200的记录时走全表扫描。优化器为什么要这样选择呢？

    生成优化器跟踪文件

    mysql> set optimizer_trace="enabled=on";
mysql> select * from payment where customer_id<150\G
mysql> select trace into outfile 'payment_150'  lines terminated by '' from information_schema.optimizer_trace;
mysql> select * from payment where customer_id<200\G
mysql> select trace into outfile 'payment_200'  lines terminated by '' from information_schema.optimizer_trace;
mysql> set optimizer_trace="enabled=off";

    对比分析成本
    全表扫描的成本是1635，而使用idx_fk_customer_id索引扫描customer_id < 150的成本是1429.3，索引的成本低，chosen属性值是true表示优化器选择了走索引。
    而使用idx_fk_customer_id索引扫描customer_id < 200的成本是1896.2，索引的成本高，chosen属性值是flase表示优化器没有选择索引，而是全表扫描。
    关于优化器跟踪的详细信息参见MySQL的内部文档：

    https://dev.mysql.com/doc/internals/en/optimizer-tracing.html。

    13.改变执行计划

    13.1提示（hint）的作用

    优化器开关会对全局或者当前会话的所有SQL语句起作用，而提示（hint）只用于控制单个SQL语句的执行计划。同时使用时，提示的优先级高于优化器开关。
    优化器分两类:
    一类是优化器提示，用于控制优化器的行为；
    另一类是索引提示，用于控制索引的使用。

    13.2实现系统参数optimizer_switch的功能

    优化器提示（hint）可以在单个SQL语句中实现系统参数optimizer_switch的功能，例如禁止使用索引的交集的功能可以使用下面的优化器提示实现：

    mysql> explain  select /*+ NO_INDEX_MERGE(actor) */  * from actor where first_name='NICK' and last_name='WAHLBERG'\G

    关于优化器提示的详细信息参见：

    https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html。

    13.3临时在一个SQL语句中设置系统变量的值

    mysql> select /*+ SET_VAR(sort_buffer_size = 16M) */ * from rental order by 1,2,3,4;
mysql> insert /*+ SET_VAR(foreign_key_checks=OFF) */ into tba values('aaa');

    13.4hint的作用范围

    下面的例子是通过提示临时停止二级索引的唯一性检查，首先检查当前会话中的参数unique_checks的值：

    mysql> select @@unique_checks;

    发现当前会话中的参数unique_checks的值是1，在SQL语句中使用提示将unique_checks的值设置为0:

    mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;

    再次检查当前会话中的参数unique_checks的值：

    mysql> SELECT @@unique_checks;

    可以看到只是在提示起作用的SQL语句中停止了二级索引的唯一性检查，之前和之后都没有影响。

    13.5索引提示的使用

    索引提示控制优化器对索引的使用，常用类型如下：

    • USE INDEX：使用指定索引中的一个。
    • USE FORCE INDEX：和USE INDEX类似，而且尽量避免全表扫描。
    • IGNORE INDEX：不使用指定的索引。
      索引提示的使用语法和优化器提示不同，它们直接放在指定的表名后面。
      关于索引提示的详细信息参见：
    https://dev.mysql.com/doc/refman/8.0/en/index-hints.html。

    13.6使用案例

    在没有使用索引提示时，优化器从两个索引中选择了rental_date：

    mysql> explain select inventory_id from rental where rental_date between  '2005-05-27' AND  '2005-05-28' AND customer_id IN (433, 274, 319, 909)\G

    如果可以确定使用另外一个索引效率更高的时候，可以使用USE INDEX指定使用另外一个索引：

    mysql> explain select inventory_id from rental use index(idx_fk_customer_id) 
     where rental_date between  '2005-05-27' and  '2005-05-28'
     and customer_id IN (433, 274, 319, 909)\G

    也可以使用IGNORE INDEX强制不使用rental_date索引：

    mysql> explain select inventory_id from rental ignore index(rental_date)
     where rental_date between  '2005-05-27' and  '2005-05-28'
     and customer_id IN (433, 274, 319, 909)\G

    相关文章

      网友评论

          本文标题：8.0优化专题(2023)

          本文链接：https://www.haomeiwen.com/subject/anfaudtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|8.0优化专题(2023)|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！