W
e
l
c
o
m
e
: )

6.MySQL性能优化

参数

作用范围

  • 全局：对实例的所有会话起作用

  • 会话级：只对当前会话起作用

set session binlog_rows_query_log_events = on;
set global binlog_rows_query_log_events = on;
# 其中session是可以省略的

修改全局系统参数的生效时间

​ 修改全局系统参数直对修改后连接到MySQL的会话生效，在修改之前已经建立的会话还保持原来的参数值

其他会话的参数

select * 
from performance_schema.variables_by_thread 
where variable_name='transaction_isolation';



isolation

英 /ˌaɪsəˈleɪʃn/  美 /ˌaɪsəˈleɪʃn/
n. 隔离，孤立；孤独；绝缘；（尤指对混合物或微生物的）离析

静态参数和动态参数

​ mysql的系统参数还可以分为静态参数和动态参数，动态参数可以mysql运行中修改，静态参数在mysql启动后无法修改

系统参数设置为default

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

set @@session.max_join_size = default;
set @@session.max_join_size = @@global.max_join_size;

持久化参数设置

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

set persist max_connections = 1000;
或
set @@persist.max_connections = 1000;

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

set persist_only back_log = 100;
或
set @@persist_only.back_log =100;

持久化的系统参数以json格式保存

​ 持久化的参数以json格式保存在数据目录的mysqld-auto.cnf文件中。

cat /var/lib/mysql/mysql-auto.cnf

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

参数的来源

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

select 
	variable_name
	,variable_source as souce
	,variable_path
	,set_time
	,set_user as user
	,set_host
from performance_schema.variables_info
where variable_name = 'max_connections' or variable_name = 'socket'

查询参数文件

select 
	variable_path
	,variable_source,
	,count(*)
from 
	performance_schema.variables_info
where
	length(variable_path) != 0 
group by
	variable_path
	,variable_source;

计算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的总数据量（包括表和索引）

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的活跃数据就够了。

innodb缓冲池的命中率

​ 两个mysql的状态参数可以计算出它的命中率：（MySQL8才有）

（1）innodb_buffer_pool_read_resquests：表示想innodb缓冲池进行逻辑读的次数

（2）innodb_buffer_pool_reads：表示物理磁盘中读取数据的次数

​ innodb缓冲池的命中率的计算公式如下：

InnoDB缓存池的命中率=（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads）/ Innodb_buffer_pool_read_requests * 100%

状态参数 Innodb_buffer_pool_reads

​ 代表mysql不能从innodb缓冲池读到需要的数据而不得不从硬盘中进行读的次数，使用下面的命令查询mysql每秒从磁盘读的次数：

mysqladmin extended-status -ri1 | grep innodb_buffer_pool_reads

​ 把这个值和硬盘的I/O能力进行对比，如果接近了硬盘处理I/O的上限，那么从操作系统层查看到的CPU用于等

待I/O的时间（IO wait，例如vmstat中的cpu的wa或iostat中的%iowait）会变长，这时硬盘I/O就成了性能的

瓶颈，增大InnoDB缓存池可能会减少MySQL访问硬盘的次数，提高数据库的性能。

对设置innodb缓存大小的考虑

  • 太小的缓冲池可能会导致数据页被频繁的从磁盘读取到内存中，引起性能下降

  • 如果设置的过大，又可能或造成内存被交换到位于硬盘的内存交换分区，引起性能急剧下降

  • 这两种情况比较起来，把innodb缓冲池设置小一些对性能的负面影响并不特别大。实际生产中，mysql进程奔溃的一个常见原因是操作系统的内存耗尽，操作系统被迫把MySQL进程杀死

设置innodb缓存大小

​ 早期调整innodb_pool_size需要重新启动mysql，从mysql5.7后，这个参数可以动态进行调整，例如

set persist innodb_b_pool_size=256*1024*1024;

innodb_buffer_pool_instance系统参数

​ 一个和相关innodb_buffer_pool_size的参数是innodb_buffer_pool_instances，它设定把innodb缓冲池分成几个区，当innodb_buffer_pool_size大于1GB时，这个参数才会起作用，对于大的innodb缓冲池，建议把它设置得大一些，这样可以减少获取访问innodb缓冲池时需要上锁得粒度，以提高并发度。

mysqld -h10.72.1.230 -P3307 -uroot -p123456 --innodb_buffer_pool_instances=8

日志

​ innodb日志保存着已经提交得数据变化，用于在奔溃恢复时把数据库的变化恢复到数据文件，除了系统奔溃，其他时候都不会读日志文件。向日志文件写数据的方式是顺序的，这笔离散写的效率要高很多，而向数据文件写数据通常是离散写的比较多。

​ 日志缓冲区是一个内存缓冲区，innodb使用它来缓冲重做日志事件，然后在将器写入磁盘，日志缓冲区的大小由系统参数innodb_log_buffer_size控制，默认是16M，在大多数情况下是够用的。如果有大型事务或大量较小的并发事务，可以考虑增大innodb_log_buffer_size，这个参数在mysql8中可以动态设置

​ 默认在datadir下有两个48M的日志文件ib_logfile0和ib_logfile1

日志产生量

  • innodb的日志产生量是衡量数据库繁忙程度的重要指标，也是设置日志文件大小的依据，查询日志产生量的相关信息有两个方法。

  • 第一个方法是查询information_schema.innodb_metrics视图中的对应计量

  • 第二个方法是使用show engine innodb statu命令查询日志产生量的相关信息，这些信息在输出log部分，这种方法不需要激活innodb的相关计量

查询日志视图

# 使用下面的命令可以激活这些计量：
set global innodb_monitor_enable='log_lsn_%';

# 激活后，一个查询结果的列子如下：
select name,count,status 
from information_schema.innodb_metrics 
where name like 'log_lsn%';

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

lsn（log sequencd number)

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

show engine innodb status\G

​ 这 里 的l s n是4 2 5 6 4 0 6 5 2， 最 近 一 次 检 查 点 的l s n是4 0 6 8 4 1 4 2 3， 计 算 出 当 前 日 志 文 件 的 使 用 量 是 这 两 个 值 之 差 ：

log sequence number = 425640625
last checkpoint = 406841432
select round((425640625 - 406841432)/1024/1024) logsize_MB;

设置日志文件大小考虑

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
pager grep sequence

# 查询当前的lsn
show engine innodb status\G

# 休眠一分钟
select sleep(60)

# 再次查询当前的lsn
show engine innodb status\G

# 取消设置的pager
nopager

# 根据一分钟的采样，可以计算出一个小时的日志量：
select round((1455007613 - 1439955157)*60/1024/1024) "1 hour log(MB)";

决定日志文件的两个参数

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

（1）innodb_log_file_in_group：表示一个组里有多少个文件，默认2

（2）innodb_log_file_size：表示单个日志文件的大小，默认为48MB

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

修改日志文件大小的方法

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

参数innodb_dedicated_server

mysql8中引入一个参数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

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

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

内存大小 innodb_buffer_pool_size的值
小于1G 128MB
1GB到4GB 物理内存的50%
大于4GB 物理内存的75%

innodb_log_file_size设置策略

innodb_log_file_size和innodb_log_file_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_files_in_group
小于8GB 以GB为单位对innodb_buffer_pool_size取整数
8GB到128GB 以GB为单位对（innodb_buffer_pool_size*0.75)取整
大于128GB 64GB

现实设置的参数优先生效

​ 当参数innodb_dedicated_server为ON时，如果还显示设置了这些参数，则显示设置的这些参数会优先生效。

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

mysql的启动探测

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

硬盘读写参数

硬盘的读写通常对数据库性能影响最大的因素之一。

  • innodb_flush_log_trx_commit

  • sync_binlog

  • innodb_flush_method

  • innodb_io_capacity和innodb_io_capacity_max

innodb_flush_log_trx_commit

​ innodb_flush_log_trx_commit参数控制事务提交时写重做日志的行为方式，它有3个值：

  • 0：事务提交的时候不会触发写日志文件的操作，日志缓存中的数据以每秒一次的频率写道日志文件中，同时还会进行文件系统到磁盘的同步操作

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

  • 2：事务提交的时候会写日志文件，但文件系统到磁盘的同步是每秒进行一次

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

sync_binlog

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

  • 1：默认值为1，每次事务提交的时候都会把二进制文件刷新到磁盘，这种方式对数据是最安全的，但性能是最慢的

  • 0：设置为0时，事务提交的时候不会把二进制日志刷新到磁盘，刷磁盘的动作由操作系统控制

  • N：设置成N（N不等于1或0）时，每进行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_log_trx_commit设置成1对性能负面影响还要大得多

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_fsync，它记录着调用fsync()的次数，通常fsync()和O_DIRECT调用fsync()的次数差不多，O_DIRECT_NO_FSYNC的次数最少。

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处理能力得系统，可以设置一个较高得值来帮助服务器处理与数据快速变更相关关联得后台维护工作。

show varibales like 'innodb_io_capacity%';

​ 这两个参数的设定是基于系统的每秒能处理的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调大后，可以看到这个状态参数也相应得增加了。

max_connections

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

show status like 'max_used%';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| Max_used_connections      | 229                 |
| Max_used_connections_time | 2022-03-05 07:58:01 |
+---------------------------+---------------------+

binlog_order_commits

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

skip_name_resolve

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

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要小心，因为原来用主机名服务的权限不能再使用了

资源组

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

查询资源组

# 在information_schema.resource_groups视图中可以查询资源组中的信息，默认有两个资源组
select * frm information_schema.resource_groups\G

创建资源组

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

create resource group batch type =user vcpu=2-3 thread_priority=10;

修改资源组的属性

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

alter resource group batch vcpu=3 thread_priority=19;

# 在系统负载较轻的情况下，增加分配给组的cpu数量，并提高其优先级
alter resource group batch vcpu=0-3 thread_priority=0;

# 注意，用户线程的优先级不能小于0：
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]

使用资源组

# 激活batch资源组的命令如下：
alter resource group batch enable;

# 删除batch资源组的命令如下：
drop resource group batch;

# 要将线程分配给batch资源组，执行以下操作：
set resource group batch for thread_id;  # 当thread_id有多个时可以使用逗号隔开

# 如果要把当前线程设定到batch资源组中，在会话中执行以下语句：
set resource group batch; # 此后，会话中的语句将使用batch资源组的资源进行执行。

# 要使用batch组执行当个语句，请使用resource_group优化程序提示：
insert /* + resource_group(batch) */ into t2 values(2); 
在sql语句中设置提示的方法可以和MySQL的中间件结合起来使用，例如proxysql语句中增加提示。

查询线程使用的资源组

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

select thread_id, resource_group from performance_schema.threads where thread_id=10054;
+-----------+----------------+
| thread_id | resource_group |
+-----------+----------------+
| 10054 	| Batch 		 |
+-----------+----------------+

资源组的限制

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

\1. 如果安装了线程池插件，则资源组不可用。

\2. 资源组在macOS上不可用，因为它不提供用于将CPU绑定到线程的API。

\3. 在FreeBSD和Solaris上，忽略资源组线程优先级，尝试更改优先级会导致警告。实际上，所有线程都以优先级0运行。

\4. 在Linux上，需要对mysqld进程设置CAP_SYS_NICE功能，否则将忽略资源组线程优先级。

####　在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服务，设置线程优先级才能生效

Windows平台上的线程的优先级

线程优先级范围 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

找出top sql

​ 当要对MySQL进行优化时，找到top sql语句通常是第一步

  • 从操作系统层监控到的最繁忙线程找出top slq

  • 慢查询日志

  • 性能视图

  • sys数据库中的存储过程

    • diagnostics()存储过程
    • ps_trace_statement_digest()存储过程
    • statement_performationce_analyzer()存储过程
    • ps_trace_thread()存储过程
top -H

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

# 然后在mysql数据库中对pid进行查询
select * from performationce_schema.threads where thread_os_id= PID\G

# 查询的结果有
thread_id
processlist_id


# 如果有必要，可以使用processlist_id字段指定的线程好杀死这个线程或者正在执行的sql，不能使用thread_id字段执行kill命令，不能可能会杀错线程：
kill query 32;
或者
kill 32;

慢查询日志

开启慢查询日志

# my.cnf中配置
slow_query_log= on #开启
slow_query_log_file = /server/mysql/slow.log #文件位置
long_query_time= 2 #2秒以上的语被记录

  • long_query_time：响应时间超过这个参数值的sql语句被定义为慢sql，默认为10

  • slow_query_log：是否激活慢查询日志，默认是off

  • slow_query_log_file：慢查询日志文件的路径和文件名，默认/var/lib/mysql/localhost-slow.log

  • log_slow_extra：从MySQL8.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)"


mysqladmin extended-status | grep -i slow_q
| Slow_queries | 1

慢查询日志解析工具

​ 该工具是慢查询自带的分析慢查询工具，一般只要安装了mysql，就会有该工具

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  -- 后跟参数以及log文件的绝对地址;

  -s            what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                c: count
                l: lock time
                r: rows sent
                t: query time

  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

​ 使用案例

mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10条慢查询

mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查询时间最慢的3条慢查询

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log # 得到按照时间排序的前10条里面含有左连接的查询语句

 mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # 按照扫描行数最多的

性能视图

​ 在sys.statement_analysis中找出总计执行时间最长的sql语句：

select * from sys.statement_analysis limit 1\G

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

# 下面sql语句列出平均执行时间最长的语句，这类sql语句通常优化的空间最大
select * 
from performance_schema.events_statements_summary_by_digest 
order by avg_timer_wait desc limit 1\G

# 下面sql语句列出执行次数最多的sql语句，这类sql语句通常对整体系统性能影响最大
select * 
from performance_schema.events_statements_summary_by_digest 
order by count_star desc limit 1\G

# 下面的sql语句列出查询行数最多的sql语句，这类sql语句通常消耗最多的硬盘的读写
select * 
from performance_schema.events_statements_summary_by_digest 
order by sum_rows_examined desc limit 1\G

# 下面的SQL语句列出返回行数最多的SQL语句，这类SQL语句通常占用最多网络带宽
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语句：
select *
from sys.statements_with_runtimes_in_95th_percentile\G

sys存储过程

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

  • diagnostics()存储过程

  • ps_trace_statement_digest()存储过程

  • statement_performationce_analyzer()存储过程

  • ps_trace_thread()存储过程

# diagnostics()存储过程会生成一个关于当前MySQL实例整体性能的诊断报告
tee diagnostics.log
call sys.diagnostics(null,null,'current');
notee;

digest

英 /daɪˈdʒest/ 美 /daɪˈdʒest; dɪˈdʒest/
v. 理解，领悟；消化
n. 文摘，摘要；分解物，提炼物

# ps_trace_statement_digest()存储过程可以根据提供的SQL语句摘要哈希值跟踪收集这些SQL语句的执行过程中的性能诊断信息。
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秒收集一次性能信息，生成性能报告文件：
*/
call sys.ps_trace_thread(51,'/tmp/td_51.dot',null, null, true,false, false);

sql语句的执行计划

  • SQL语句只是告诉了数据库要干什么，并没有告诉数据库如何做，查看sql语句的执行计划可以使sql的执行过程从黑盒变成白盒

  • 在sql语句前面加上explain即可查看sql语句的执行计划，但不会实际执行这个SQL语句

  • 显示sql语句的执行计划的格式有三种，分别时：传统（traditional）、json和树形（tree）格式。可以使用format=trandition|json|tree来指定格式，默认是传统格式

  • 显示sql语句的执行计划不光支持select，还支持delete、insert、replace和update，但是explain analyze是例外

传统格式

  • 传统格式提供了执行计划的概况、索引的使用等基本信息
explain
select city
from city
where country_id = (select country_if from country where country='china')

# 若返回信息如下，则证明存在警告信息
2 rows in set, 1 warning (0.01 sec)

警告信息

  • explain语句执行完成后提示有一个警告信息，警告信息包括的是优化器重写的伪sql，这个sql不一定能执行，使用\W打开\w关闭警告信息
show warnings\G

json格式

  • json格式提供了以json格式显示的详细执行计划，这个格式核实与被程序调用，例如图形工具workbench显示的图形化的执行计划就是调用了json格式的接口。
explain
format=json
select city
from city
where country_id = (select country_if from country where country='china')

树形格式

  • 树形格式是从MySQL8.0.18开始引入格式，它提供的执行计划比传统的执行计划更加详细，输出格式是树形的
explain
format=tree
select city
from city
where country_id = (select country_if from country where country='china')

explain analyze

  • explain analyze实际上是树形的执行计划的扩展，它不但提供了执行计划，还检测并执行了SQL语句，提供了执行过程中的实际度量
explain analyze
select city
from city
where country_id = (select country_if from country where country='china')

explain for connection

  • 在实际工作中，如果发现一个正在执行的sql语句耗时很长，这时想查询它的执行计划，通常的做法是使用explain生成这个sql语句的执行计划，但因为统计信息等原因，生成的执行计划和正在执行计划可能不完全相同，更好的做法是使用explain for connection查询当前正在使用的执行计划
# 查询出当前的会话号
select connection_id();
# 或者使用show processlist查询会话号，在当前的会话中执行一个慢sql语句
select sleep(60),city 
from city
where where country_id = (select country_if from country where country='china')

# 根据会话号在其他会话里查询正在执行的sql语句的执行计划：
explain for connection 17\G

对于非select语句的支持

  • 抑制输出，并不真正执行sql语句
explain analyze
update actor
set first_name='a'
where first_name='a';

sql执行性能的评估

  • 查看sql执行性能的最简单方法是看sql执行完成的时间，除此之外还有：

    • 1、性能视图
    • 2、状态变量
    • 3、explain analyze
    • 4、操作系统层监控

执行时间对比

1 row in set (0.00 sec)

1 row in set (0.36 sec)

性能视图

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语句，注意观察其中与性能相关的字段：
select *
from events_statements_summary_by_digest
where schema_name != 'performance_schema' 
order by sum_timer_wait desc
limit 3\G

I/O性能

  • 首先重置performance_schema.file_summary_by_event_name视图
truncate table performance_schema.file_summary_by_event_name;

# 然后执行一个全表扫描的语句：
select count(*) 
from testdb.table_a 
where col2 <> 'a';

# 最后查询等待事件wait/file/innodb/innodb_data_file的性能信息，这些信息反映了全表扫描的性能
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/file/innodb/innodb_data_file\G

状态变量

  • MySQL的自带了479个状态变量（mysql8.0.22）用以反映mysql的运行状态，在mysql客户端里可以使用下面的命令查询会话和全局的状态变量
show session status;
show global status;

使用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_begjin
    • 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

查询单个sql的状态参数

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

# 然后执行一条sql语句
select * from table_a where col1=999;

# 在查询状态变量Handle_read_*的值
show session status like 'Handle_read%';

# 对比另一个sql语句
select * from table_a where col2='efc45f14c8bf7ced3121488ff7a70123';

last_query_cost状态变量

  • 查询最后执行的sql语句的估算成本（之一不是实际执行成本，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查询所有会话中的状态变量Handle_write
select * 
from performance_schema.status_by_thread
where variable_name='Handle_write';

+-----------+---------------+----------------+
● | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
● +-----------+---------------+----------------+
● | 60 			| Handler_write | 94 			|
● | 67 			| Handler_write | 477 			|-- 插入记录最多的线程
● | 69 			| Handler_write | 101 			|
● +-----------+---------------+----------------+

慢查询日志中的状态变量

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

explain analyze

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

操作系统层监控

  • 从操作系统层也可以监控到sql语句执行性能，mysql需要消耗操作系统的4中资源：cpu、磁盘、内存和网络，在linux系统上可以采用监控工具包括：top、free、vmstat、iostat、mpstat、sar和netstat等

mysql优化器

  • MySQL的优化器负责生成sql语句的执行计划

    • 优化器开关
    • 计算执行计划的成本
    • 优化器跟踪

优化器开关

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

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

改变优化器的开关可以控制优化器的行为

# 下面这个sql语句是取两个索引的交集进行数据访问：
explain
select *
from actor
where first_name='NICK' and last_name='WAHLBERG'\G

# 修改optimizer_switch禁止使用index_merge_intersection的命令如下
set optimizer_switch='index_merge_intersection=off';

# 然后执行同样的SQL语句，它的执行计划就变了，不再使用两个索引的交集进行数据访问：
explain
select *
from actor
where first_name='NICK' and last_name='WAHLBERG'\G

计算执行计划的成本

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

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

mysql.engine_cost

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

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很慢
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')

# 使用下面的命令把修改刷新到内存中
flush optimizer_costs;
  • 如果把mysql的临时表目录innodb_temp_tablespaces_dir和临时文件目录tempdir设置到内存中（例如：设备/dev/shm或者文件系统tempfs），可以提高对临时表和临时文件的处理速度，例如提高了10倍的处理速度，对应修改disk_temptable_create_cost和disk_temptable_row_cost成本如下
update mysql.server_cost
set cost_value=2,Comment = 'Temporary tables on memory'
where cost_name='disk_temptable_create_cost';

update mysql.server_cost 
set cost_value = 0.05,Comment = 'Stored on memory disk' 
where cost_name = 'disk_temptable_row_cost';

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

优化器跟踪

  • 优化器跟踪（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语句的优化过程，可以重复di2第3步

两种执行计划的对比

  • 举个例子
explain
select *
from payment
where customer_id<150\G

explain
select *
from payment 
where customer_id<200\G
  • 从执行计划可以看到，当查询payment表中的客户号小于150的记录时使用索引，查询客户号小于200的记录时走全表扫描。优化器为什么这样选择呢

生成优化器跟踪问价

set optimizer_trace="enabled=on";

select * from payment where customer_id<150\G

select trace into outfile 'payment_150' lines terminated by ''
from information_schema.optimizer_trace;

select * from payment where customer_id<200\G

select trace into outfile 'payment_200' lines terminated by ''
from information_schema.optimizer_trace;

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

使用hint改变执行计划

提示（hint）的作用

  • 优化器开关对全局或者当前会话的所有sql起作用，而提示（hint）只用于控制单个sql语句的执行计划。同时使用时，提示的优先级高于优化器开关

  • 优化器分两类

    • 一类是优化器提示，用于控制 优化器的行为
    • 另一类是索引提示，用于控制索引的使用

实现系统参数optimizer_switch的功能

  • 优化器提示（hint）可以在单个sql语句中实现系统参数optimizer_switch的功能，例如禁止使用索引的交集功能可以使用下面的优化器提示实现：
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/optimizerhints.html

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

select /*+ SET_VAR(sort_buffer_size = 16M) */ * from rental order by 1,2,3,4;

 insert /*+ SET_VAR(foreign_key_checks=OFF) */ into tba values('aaa');

hint的作用范围

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

# 发现当前会话中的参数unique_checks的值是1，在SQL语句中使用提示将unique_checks的值设置为0:
SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;

# 再次检查当前会话中的参数unique_checks的值：
SELECT @@unique_checks;

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

索引提示的作用

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

    • use index ：使用指定索引中的一个
    • use force index ：和use index 类似，而尽量避免全表扫描
    • ignore index：不使用指定的索引

  • 索引提示的适用语法和优化器提示不同，它们直接放在指定的表名后面

使用案例

  • 在没有使用索引的提示时，优化器从两个索引中选择了rental_date
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指定使用另一个索引：
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索引
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

主键和二级索引

  • 查看索引
show index from table_name;

show extended index from table_name;

系统参数sql_require_primary_key

  • 在绝大多数时候，显示的创建一个主键通常是正确的，从mysql8.0.13开始，可以设置系统参数sql_require_primary_key为on，强制创建有主键的索引
set sql_require_primary_key=on;

找出没有主键的表

select t.table_schema as database_name,t.table_name
from information_schema.tables t
left join information_schema.table_constraints c 
	on t.table_schema = c.table_schema
	and t.table_name = c.table_name
	and c.constraint_type = 'PRIMARY KEY'
where c.constraint_type is null
and t.table_schema not in('mysql', 'information_schema', 'performance_schema','sys')
and t.table_type = 'BASE TABLE';


select t1.table_schema, t1.table_name 
from information_schema.columns t1 
join information_schema.tables t2 
on t1.table_schema=t2.table_schema 
and t1.table_name=t2.table_name 
where t1.table_schema not in ('sys', 'mysql', 'information_schema', 'performance_schema') 
and t2.table_type='BASE TABLE' 
group by t1.table_schema, t1.table_name 
having group_concat(column_key) not regexp 'PRI|UNI';

优化索引

正确使用索引

  • 对于字符类型的字段，如果使用数字类型的值进行检索，就不会使用到索引
explain
select * from actor where last_name=123\G

# 同样的sql语句被检索的值改用字符类型时就会使用索引
explain
select * from actor where last_name='123'\G

# 字段类型不同造成索引失效的更多情况时出现表连接的时候，两个连接的字段名上看起来相同，但实际上字段的类型不同而造成无法使用索引
  • 不正确地使用运算符也可能造成索引失效
explain
select * from actor where actor_id=1+1\G

explain
select * from actor where actor_id-1=1\G

# 字段actor_id上是主键索引，在第一个SQL语句中，对这个字段过滤通过主键访问进行，但在第二个SQL语句中，由于要对actor_id进行运算后再过滤，因此只能进行全表扫描后，把所有的记录进行计算后才能进行过滤
  • 两个sql语句找出在2005年6月发生地租借电影地交易
explain
select * from rental where rental_date between '2005-06-01' and '2005-06-30'\G

explain
select * from rentalt where year(rental_date)=2005 and month(rental_date)=6\G

# 可以看到第一种SQL可以使用rental_date索引，而第二种SQL语句不能使用索引，这也是因为运算符使用错误从而造成索引失效
  • 对于创建在字符串字段上的B树索引，需要注意对最左边的字符子串进行匹配时可以使用索引，对中间或后边的字符子串进行匹配时无法使用索引，对比一下下面两个SQL语句的执行计划中的索引使用情况：
explain select * from actor where last_name like 'BALL%'\G

explain select * from actor where last_name like '%BALL%'\G

创建索引

  • 在一个需要被检索但没有索引的字段上创建索引通常是提高SQL语句执行效率的最简单、有效的方法。通过检查sys中的两个视图，可以找到需要创建的索引

  • 视图schema_tables_with_full_table_scans中包括所有没有使用高效索引的表，按扫描行数降序进行排列

ALTER TABLE  表名 ADD [KEY | INDEX] 索引名；


CREATE INDEX idx_name ON tab_name(col_name);

select * from sys.schema_tables_with_full_table_scans where object_schema='sand';

select * from sys.statements_with_full_table_scans where query like '%sbtest1%'\G
  • 一个SQL语句用了5秒钟扫描sbtest1表的65万行，如果在字段c上创建索引，这个SQL的执行效率将大大提高。

删除索引

  • 在同一个表上创建的索引并不是越多越好，索引除了占用额外的空间外，对DML语句的性能也有一定的影响，因为对字段的修改都要修改相应字段的索引，因此不要在同一个表上创建过多的索引。对于没有用和冗余的索引要删除，可以从视图sys.schema_unused_indexes中查询没有使用的索引，这些索引可能是被删除的对象，查询sbtest数据库中没有使用的索引的SQL语句和输出结果如下：
ALTER TABLE 表名  DROP [KEY | INDEX] 索引名;

select * from sys.schema_unused_indexes where object_schema='sbtest';
  • 查询视图schema_redundant_indexes中保存着重复地索引的sql语句和输出结果
 select * from sys.schema_redundant_indexes\G

  • 可以看到id_kc索引建立在k和c两个字段上，而k_2索引建立在k字段上，因此k字段上有两个索引，这里还给出了删除索引的SQL语句，但并不是所有的重复索引都需要删除，有些重复的索引可以提高查询的效率，还需要保留。

  • 在sys.schema_index_statistics视图里还记录着索引被SQL语句使用的频率和延时，查询actor表的索引的使用情况的SQL语句和输出结果如下

select * from sys.schema_index_statistics where table_name='actor'\G
  • 这个视图记录的索引使用情况也可以用来参考，和前面两个视图相结合，用来确定需要删除的索引

不可见索引

  • 在MySQL 8里，可以设置索引为不可见，这样优化器就不会使用这样的索引，带来的一个显而易见的优势是便于调试，当不能确定一个索引是否需要的时候，可以先把索引转换成不可见索引，运行一段时间，确定这个索引的确不需要再把索引删除，如果后来发现这个索引还是有用的，可以再把索引转换为可见索引，这样避免了成本高昂的创建、删除索引的动作。一个使用actor表的last_name字段上的SQL语句的执行计划如下：
 explain select * from actor where last_name='BALL'\G
 
 # 把这个字段上的索引改成不可见索引的sql语句如下
 alter table actor alter index idx_actor_last_name invisible;
 
 # 再次检查这个sql语句的执行计划如下：
 explain select * from actor where last_name='BALL'\G
 
 # 可以看到在索引被改成了不可见索引后，sql语句将不会使用这个索引，但这个索引仍然被维护着
  • 可以使用show index查询索引是否可见，也可以在视图information_schema.statistics中查询索引是否可见，查询某个表上的索引是否可见的SQL语句和输出结果如下：
select index_name, is_visible 
from information_schema.statistics 
where table_schema='sakila' and table_name='actor';

 +---------------------+------------+
 | index_name 		   | is_visible |
 +---------------------+------------+
 | idx_actor_last_name | NO 		|
 | PRIMARY 			   | YES 		|
 +---------------------+------------+
  • 对于不可见索引可以把优化器的开关use_invisible_indexes设置为on（默认是off），从而让优化器使用不可见索引。也可以使用提示SET_VAR将优化器开关use_invisible_indexes临时设置为on，从而使用不可见索引，下面是使用提示让SQL语句使用不可见索引的执行计划：
explain select * /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ 
from actor 
where last_name='BALL'\G

在长字符串上创建索引

  • 当在很长的字符串的字段上创建索引时，索引会变得很大而且低效，一个解决办法是crc32或md5函数对长字符串进行哈希计算，然后在计算的结果上创建索引。在MySQL 5.7以后的版本，可以创建一个自动生成的字段，例如可以创建下面一个表：
 create table website(
id int unsigned not null,
web varchar(100) not null,
webcrc int unsigned generated always as (crc32(web)) not null,
primary key (id)
);

# 字段webcrc是根据字段web进行crc32哈希计算后生成的字段。

# 向这个表中插入一条索引
insert into website(id,web) values(1,"<https://www.scutech.com>");

select * from website;

+----+---------------------------+------------+
| id | web 					     | webcrc     |
+----+---------------------------+------------+
| 1 | <https://www.scutech.com>  | 3014687870 |
+----+---------------------------+------------+

# 可以看到字段webcrc中自动生成了web字段的循环冗余校验值，在这个字段上创建索引，可以得到一个占用空间少，而且高效的索引。


# 在mysql8.0.13以后的版本，可以直接创建函数索引，例如创建下面一个表
 create table website8(
id int unsigned not null,
web varchar(100) not null,
primary key (id),
index ((crc32(web)))
);

# 在这个表中创建了一个函数索引，查询这个表上的索引的SQL和输出结果如下：
show index from website8\G

# 可以看到第一个索引是主键，第二个索引是函数索引

# 解决索引字段长的另一个办法是创建前缀索引（prefix index），前缀索引的创建语法中字段的写法是：col_name(length)，前缀索引是对字符串的前面一部分创建索引，支持的数据类型包括：char、varchar、binary和varbinary。创建前缀索引的关键是选择前缀的字符串的长度，长度越长，索引的选择性越高，但存储的空间也越大。



# sbtest2表中c字段是长度为120的字符串，查询在不同长度时索引的选择性的SQL语句和输出结果如下：
 select 
count(distinct(left(c,3)))/count(*) sel3,
count(distinct(left(c,5)))/count(*) sel5,
count(distinct(left(c,7)))/count(*) sel7,
count(distinct(left(c,9)))/count(*) sel9,
count(distinct c)/count(*) selectivity
from sbtest1;

+--------+--------+--------+--------+-------------+
| sel3 	 | sel5   | sel7   | sel9   | selectivity |
+--------+--------+--------+--------+-------------+
| 0.0120 | 0.6784 | 0.9959 | 1.0000 | 1.0000      |
+--------+--------+--------+--------+-------------+


# 可以看到在这个字段的前7位创建索引即可达到接近1的选择性，再增加这个索引的前缀位数，索引的选择性并不会提高，下面是创建索引的命令：
alter table sbtest2 add index (c(7));
# 前缀索引缺点是无法用在order by和group by情况下，且也无法执行覆盖扫描

使用索引减少锁

  • InnoDB在访问记录的时候会对它进行加锁，索引可以减少InnoDB访问的记录数量，从而减少锁的数量。如果没有使用索引进行过滤，对where条件里的字段的判断要到服务器层进行，InnoDB将对没有过滤的全部记录加锁。例如下面的SQL语句：
begin;
select * from city where city='Shaoguan' for share;

# 这个SQL语句对一条记录上共享锁，然后查询锁的情况如下：
select index_name, lock_type,lock_mode, count(*) 
from performance_schema.data_locks 
group by index_name, lock_type, lock_mode;

+------------+-----------+-----------+----------+
| index_name | lock_type | lock_mode | count(*) |
+------------+-----------+-----------+----------+
| NULL 		 | TABLE 	 | IS     	 | 1 		|
| PRIMARY 	 | RECORD 	 | S 		 | 602 		|
+------------+-----------+-----------+----------+

# 看一下这个sql语句的执行计划
explain select * from city where city='Shaoguan' for share\G
Extra: Using where

# 可以看到在Extra字段中有Using where的说明，这是说使用了where条件进行记录的过滤，而这个工作是服务器层做的

# 在这个字段上创建索引后再看看这个sql语句的执行计划
 explain select * from city where city='Shaoguan' for share\G
 Extra: NULL
 Extra字段中已经没有了Using where的说明，因为where条件里过滤工作由InnoDB存储引擎完成了。

# 再次查询锁的情况如下：
select index_name, lock_type,lock_mode, count(*) 
from performance_schema.data_locks 
group by index_name, lock_type, lock_mode;

+------------+-----------+---------------+----------+
| index_name | lock_type | lock_mode 	 | count(*) |
+------------+-----------+---------------+----------+
| NULL 		 | TABLE 	 | IS 		 	 | 1 		|
| id_city 	 | RECORD 	 | S 		 	 | 1 		|
| PRIMARY 	 | RECORD 	 | S,REC_NOT_GAP | 1 		|
| id_city  	 | RECORD 	 | S,GAP 		 | 1 		|
+------------+-----------+---------------+----------+

# 发现只有3个记录级别的锁，大大减少了锁的竞争，因为InnoDB访问的记录减少了，锁自然也减少了。



# 在实际工作中，如果发现大量的锁竞争，可以通过在适当字段上创建索引的方法减少锁。除了创建索引外，还有两个减少锁竞争的方法，一个是减少事务的粒度，也就是尽量避免一个事务修大量的记录，或者持续较长的时间不提交。还有一个方法是调整事务的隔离级别。

覆盖索引使分页查询性能提高30倍

覆盖索引

  • 覆盖索引（covering index）不是一种索引类型，是一种索引的访问方式，它是指一个sql语句只读取索引就可以获得需要的数据，不需要访问表，这样大大提高了I/O的效率，原因如下：

    • 不需要访问表减少了I/O的次数
    • 索引通常比表小很多
    • 由于索引是按照键值顺序存储的（至少在一个页内是这样），对于按照键值进行范围查询时使用的是顺序I/O，相对于离散I/O性能大大提高

  • 由于覆盖索引必须要存储列表的值，而hash索引、空间索引和全文索引等不存储索引列的值，所以mysql只能使用btree索引做覆盖索引

  • 在使用覆盖索引时，执行计划的extra字段中有using index的信息，下面是一个sql语句的执行计划：

explain
select customer_id,inventory_id,rental_date
from rental\G

rental_date索引的构成

select column_name
from information_schema.statistics
where index_name='rental_date';

+--------------+
| column_name |
+--------------+
| rental_date |
| inventory_id |
| customer_id |
+--------------+
  • 发现这个sql语句要查询的3个字段customer_id，inventory_id，rental_date都包含在这个索引中了，因此只要访问这个索引即可得到所需要的数据，就没有必要访问表了

增加对主键访问

  • 由于二级索引实质上都包含主键，因此如果再加上主键，一样可以使用覆盖索引，下面是再输出字段中加主键字段的sql语句的执行计划：
explain select rental_id,customer_id,inventory_id,rental_date from rental\G

需要访问的字段不在索引中不能使用覆盖索引

  • 如果在上面的查询字段中再增加任意一个其他字段就不能使用覆盖索引了，例如下面的SQL语句将无法使用覆盖索引：
explain
select *
from rental
where rental_date='2005-05-24 22:53:30' and inventory_id=367\G

延迟关联

  • 可以对这个SQL语句进行改写，先用一个可以使用覆盖索引的子查询查询出主键，再通过主键查找相应的记录，这种方法称之为延迟关联（deferred join）：
explain
select *
from rental_id in(
	select rental_id from rental where rental_date='2005-05-24 22:53:30' and inventory_id=367
)\G

分页查询

  • 下面的sql语句进行排序后从1000行开始查询5行，它的执行计划如下：
explain analyze select * from rental order by rental_date limit 1000,5\G

使用延迟关联改写分页查询的sql

explain analyze 
select * 
from rental r1 
inner join (select rental_id from rental order by rental_date limit 1000,5) r2 
on r1.rental_id=r2.rental_id\G

索引列顺序选择

​ 当创建的索引包含多个字段时，字段在索引中的顺序就非常重要，正确的顺序依赖于使用索引的查询语句。此要求仅限于B-Tree索引，其他类型的索引则不关注索引列顺序。通常的做法是基于全局基数和选择性来决定字段顺序，某个列的选择性高就意味着更能首先过滤掉大部分无用的数据，所以就将此列作为索引的第一列

统计信息

统计信息的作用

  • MySQL统计信息是指数据库通过采样，统计出来的表、索引的相关信息，例如：表的记录数、聚集索引的页数、字段值的基数（cardinality）等。MySQL在生成执行计划时，需要根据统计信息进行估算，计算出代价最低的执行计划。统计信息由存储引擎负责，MySQL的服务器层并不保存任何统计信息，这里描述的都是InnoDB的统计信息。

  • 准确的表和索引的统计信息是优化器生成正确执行计划的基础，InnoDB的统计信息分两种，一种是持久化的统计信息，将统计信息保存到表中，在MySQL重启后仍然有效；另一种是临时的统计信息，统计信息保存在缓存中，MySQL重启后丢失，后面这种方式现在用得越来越少，这里不做介绍。

统计信息的存放

  • 收集的表的统计信息存放在mysql数据库的innodb_table_stats表中

  • 索引的统计信息存放在mysql数据库的innodb_index_stats表中

  • 这两个表是普通表，不是视图。这两个表可以被update语句修改，但尽量不要这样做，因为这样通常会造成执行计划的恶化。

收集统计信息采用的隔离级别

  • Innodb有四个隔离级别，分别是：

    • 未提交读（read uncommitted）
    • 提交读（read committed）
    • 可重复读（repeatable read）
    • 序列读（serializable）

  • 收集统计信息采用的是未提交读，也就是未提交的数据（也叫脏数据）也会被统计。这背后的逻辑是实际生产中大部分未提交的数据最终会被提交

控制自动收集统计信息的参数

系统参数 表选项 默认值 说明
innodb_stats_persistent stats_persistent on 是否把统计信息持久化
innodb_stats_auto_recalc stats_auto_recalc on 当一个表的数据变化超过10%时是否自动收集统计信息，两次统计信息之间时间间隔不能少10秒
innodb_stats_persistent_sample_pages stats_sample_pages 20 统计索引时的抽样页数，这个值设置的越大，收集的统计信息越准确，但收集时消耗的资源越大

查询系统参数

show variables like 'innodb_stat%';

查询表属性

  • 在视图informantion_schema.tables的create_options字段中可以查询统计信息相关的属性，或者使用show create table查看非默认属性：
alter table t1 stats_auto_recalc=0;

show create table t1\G

应用案例

  • 当进行大批量数据导入时，可以把INNODB_STATS_AUTO_RECALC设置为OFF，避免在数据导入的过程中不断地收集不准确的统计信息，在数据导入完成后再手动收集统计信息并把这个参数设置为ON。

  • 对于数据量变化大的表，例如从其他数据库导入的报告表，可以将这类表的STATS_AUTO_RECALC设置为OFF，等数据量稳定后再手动进行统计信息的收集。

  • 对于数据分布不规则的表，可以通过增大表的STATS_SAMPLE_PAGES选项，提高收集的统计信息的准确性。

自动收集统计信息的例子

  • 检查当前的innodb_stats_auto_recalc参数
select @@innodb_stats_auto_recalc;

# 下面检查表tab_a的统计信息
select last_update,n_rows,clustered_index_size 
from mysql.innodb_table_stats
where table_name='tab_a';

# 下面检查tab_a的索引统计信息：
select last_update.stat_value,sample_size 
from mysql.innodb_index_stats
where table_name='tab_a';

自动收集统计信息的例子

# 把mysql客户端的提示符改成当前时间：
prompt \D>

# 下面的sql向表里面增加超过10%的记录：
Fri May 14 16:55:39 2021>insert into tab_a select * from tab_a limit 2700;

# 再次检查表tab_a的表统计信息，发现已经自动进行了统计信息的收集。


# 下面的SQL把这个表的属改成stats_auto_recalc=0和stats_sample_pages = 200
alter table tab_a stats_auto_recalc=0,stats_sample_pages=200;

# 再次向这个表里面新增超过10%的记录：
insert into tab_a select * from tab_a limit 3000;

# 第3次检查表tab_a的表统计信息，发现统计信息没有发生变化，也就是不会自动进行统计信息的收集了，因为表的属性STATS_AUTO_RECALC被设置成了0。


# 手工使用analyze table进行统计信息的收集：
analyze table tab_a;

# 第4次检查表tab_a的表统计信息。发现这时的统计信息很准，因为抽样页被改成了200（STATS_SAMPLE_PAGES = 200），而这个表只有78页，所有的页都被抽样扫描过，因此生成的统计信息就很精确

不准确的统计信息的例子

# 首先把表改成不自动收集统计信息：
alter table sbtest2 stats_auto_recalc=0;

# 然后把表中记录增加一倍：
insert into sbtest2(k,c,pad) select k,c,pad from sbtest2;

# 再检查information_schema.innodb_tablestats视图中的modified_counter字段：
select modified_counter 
from information_schema.innodb_tablestats 
where name like 'sbtest%';

# 这个字段记录自上次收集统计信息后修改的行数，对比一下这个表中的实际行数：
select count(*) from sbtest2;

# 再检查mysql数据库的innodb_table_stats表中记录的sbtest2的统计信息如下：
select last_update,n_rows,clustered_index_size 
from mysql.innodb_table_stats 
where table_name='sbtest2';

# 可以看到上次收集统计信息的时间距今已有一段时间了，记录的行数也只有大约真实行数的一半。这时可以使用analyze table命令手工收集统计信息如下：
 analyze table sbtest2;
 
# 再重新检查mysql数据库的innodb_table_stats表中记录的sbtest2的统计信息如下：
select last_update,n_rows,clustered_index_size 
from mysql.innodb_table_stats 
where table_name='sbtest2';

# 发现收集完统计信息后，统计信息中记录的行数和真实的数据已经很接近了。

手工收集统计信息

# 手工收集统计信息有两种方法，一种是analyze table命令，它可以同时收集多个表的统计信息，例如下面的命令收集两个表的统计信息和输出结果如下：
analyze local table actor,rental;

# 批量收集统计信息采用MySQL自带的工具mysqlcheck就更方便了，mysqlcheck还可以方便地被Linux的crontab或Windows的任务管理器调用，下面的命令收集sakila数据库中所有表的统计信息和输出结果如下
mysqlcheck --analyze sakila

# 也可以使用--all-databases收集所有表的统计信息。这种情况通常在进行了批量数据导入后进行
mysqlcheck --all-databases

# 可以通过手工修改mysql中的两个表innodb_table_stats和innodb_table_stats的对应字段来改变统计信息，然后通过flush table把修改后到统计信息刷新到内存中，但手工修改的统计信息通常不准，最好还是使用analyze table命令收集统计信息。

show index查询统计信息

# 一个常用的方法是的show index命令，这个命令输出的内容和视图information_schema.statistics差不多，例如查询表sakila.actor的统计信息的SQL和输出结果如下
select index_name,column_name,cardinality 
from information_schema.statistics 
where table_name='actor' and table_schema='sakila';

show table status查询统计信息

# 使用show table status可以查看相关表的统计信息，这个命令基本语法如下：
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

# 查询actor表的命令和输出结果如下：
show table status like 'actor'\G

# show table status的输出结果和视图information_schema.tables中的信息类似，在这个视图里查询actor表的命令和输出结果如下：
select * FROM information_schema.tables where table_name='actor'\G

基数

# 基数（cardinality）是索引字段的不重复值的个数。对于主键和不包含NULL值的唯一索引，表里的记录数就是索引的基数，因为索引中的所有值都是唯一的。表sakila.actor两个索引的基数分别是200和121，查询这两个索引字段的唯一值的SQL和输出结果如下：
select count(*) sum,
	   count(distinct actor_id) 'primary _cardinality',
	   count(distinct last_name) 'last_name cardinality' 
from sakila.ator;

+-----+---------------------+-----------------------+
| sum | primary cardinality | last_name cardinality |
+-----+---------------------+-----------------------+
| 200 | 200 				| 121 					|
+-----+---------------------+-----------------------+

索引的选择性

# 索引的选择性（selectivity）是指基数和表中的记录总数的比值，索引的选择性越高则查询效率越好，主键和唯一索引的选择性是1，这是最高的索引选择性。一个选择性差的索引的例子是性别，只有两个唯一值，基数是2：
select count(distinct actor_id)/count(*) 'primary selectivity'
	   ,count(distinct last_name)/count(*) 'last_name selectivity' 
from sakila.actor;

+---------------------+-----------------------+
| primary selectivity | last_name selectivity |
+---------------------+-----------------------+
| 1.0000 			  | 0.6050 				  |
+---------------------+-----------------------+

直方图拯救低效率的mysql

什么是直方图统计信息？

  • MySQL8里引进直方图统计信息，用于统计字段值的分布情况。它最典型的场景是估算where子句中过滤为此列的选择率，以便选择合适的执行计划。直方图的使用场景包括不是索引中第一列的列、值分布不均匀的列和在where子句中作为过滤条件的列。例如在IT公司工作的员工，男性远多于女性，如果在性别字段上没有直方图的统计信息，优化器可能会认为男女比例各占一半，从而生成低效率的执行计划。再如一个作业执行完成后的状态，成功的状态通常远大于失败的状态，这些信息没有直方图统计信息的时候，MySQL的优化器无法知道
过滤类型 过滤比列（%）
= 10
<> 或 != 90
< 或 > 33.33
between 11.11
in 字段数*10和50的最小值

查询在payment表amount字段大于10的记录

explain
select customer_id
from payment
where amount > 10\G

# 判断amount字段大于10的记录，由于这个字段上没有直方图的统计信息，优化器根据代码中内置的默认值估计有三分之一的记录属于这个范围。


# 大于100的记录呢
explain
select customer_id
from payment
where amount>100\G

# 优化器仍然估计有三分之一的记录属于这个范围，显然优化器在瞎猜。

在amount字段上创建直方图的统计信息

# 现在在amount字段上创建直方图的统计信息的命令和输出结果如下：
 analyze table payment update histogram on amount with 256 buckets\G
 
# 有直方图时候的执行计划
explain select customer_id from payment where amount>10\G

# 优化器根据直方图的统计信息估计符合这个条件的记录只占总数0.71%。

索引和直方图的对比

  • 索引和直方图都可以用于生成正确的执行计划，但它们之间的区别却很大：

    • 索引可以用来减少访问所需行，直方图不能。当使用直方图进行查询时，它不会直接减少检查的行数，但可以帮助优化器选择更优化的查询计划。
    • 直方图的维护成本远低于索引，对索引字段的DML操作要修改对应的索引，而直方图只在创建和修改的时候消耗资源。
    • 索引需要占用大量的存储空间，直方图对存储空间的占用基本是零
    • 在判断某个范围内的行数时，索引的成本要高得多，因为索引需要当时使用索引试探（index dive）进行收集和估算，而直方图在这方面的信息是现成的。

桶

  • 直方图的存放统计信息的单位是桶（bucket），默认100个，最多1024个。桶越多，收集统计信息的时间越长，统计信息越准确。MySQL的直方图分两类：

    • （1）等宽(singleton)直方图：每个桶只有一个值，保存该值和累积的频率。
    • （2）等高(equi-height)直方图：每个桶保存上下限，累积频率以及不同值的个数。

  • 用户不需要指定直方图的类型，MySQL会自动进行直方图类型的选择，当指定的桶数大于或等于桶所对应的值时，创建一个等宽直方图。否则创建一个等高直方图

创建一个直方图统计信息

# 例如在actor表的first_name字段上创建一个直方图统计信息的命令和输出结果如下：
analyze table actor update histogram on first_name\G

# 删除这个直方图统计信息的命令和输出结果如下：
analyze table actor drop histogram on first_name\G

# 可以在information_schema.column_statistics视图中查看，其中的histogram字段是json格式的文档：
select schema_name, table_name, column_name,
histogram->>'$."histogram-type"' as histogram_type,
cast(histogram->>'$."last-updated"'as datetime(6)) as last_updated,
cast(histogram->>'$."sampling-rate"'as decimal(4,2)) as sampling_rate,
json_length(histogram->'$.buckets')as number_of_buckets,
cast(histogram->'$."number-of-buckets-specified"'as unsigned) as number_of_buckets_specified
from information_schema.column_statistics\G

把桶的个数增加到200时创建直方图

analyze table actor update histogram on first_name with 200 buckets;

# 再次检查生成的统计信息
 select schema_name, table_name, column_name,
histogram->>'$."histogram-type"' as histogram_type,
cast(histogram->>'$."last-updated"'as datetime(6)) as last_updated,
cast(histogram->>'$."sampling-rate"'
as decimal(4,2)) as sampling_rate,
json_length(histogram->'$.buckets') as number_of_buckets,
cast(histogram->'$."number-of-buckets-specified"'as unsigned) as number_of_buckets_specified
from information_schema.column_statistics\G

# 发现此时创建的直方图变成了等宽的，分配的200个桶只用了130个。

# 查询一下这个字段的唯一值的个数
select count(distinct first_name) from actor;

+----------------------------+
| count(distinct first_name) |
+----------------------------+
| 130 					   |
+----------------------------+
1 row in set (0.00 sec)
# 发现正好也是130个，这说明了在等宽的直方图里一个值对应于一个桶。

直方图的使用场景

  • 直方图在某些场景下可以帮助优化器生成更优的执行计划，那么在什么样的字段上考虑使用直方图，建议符合下面4个条件字段可以考虑建立直方图统计信息：

    • 值分布不均匀，优化器很难估计值的分布的字段。
    • 选择性差的字段，否则索引更适合。
    • 用于where子句中过滤的字段或用于连接的字段。
    • 字段值分布规律不随时间变化的字段。因为直方图统计信息不会自动收集，如果字段值分布规律发生大的变化，统计信息会失真。

  • 实际工作中，可以使用explain analyze分析SQL语句的执行计划，如果估算的rows和实际的rows相差过大，可以考虑在过滤字段上创建直方图统计信息。

多表连接的优化

  • 表连接的顺序对性能的影响很大，n个表连接时不同的连接顺序的组合是N！（n的阶乘），当n是5时，这个组合是5!=432*1=120。不同的连接顺序的性能相差可能是数量级的

  • 连接优化的方法：

    • 选择小表作为驱动表
    • 增大连接缓冲

与连接顺序相关的提示

  • JOIN_FIXED_ORDER：按from子句中表的排序顺序进行表连接，这个提示和SELECT_STRAOGHT_JOIN功能一样

  • JOIN_ORDER：按提示指定的表顺序连接表

  • JOIN_PREFIX：把提示中指定的表作为连接时的第一个表，并按提示里的顺序进行连接

  • JOIN_SUFFIX：把提示中指定的表作为连接时的最后一个表，并按提示里的顺序进行连接

通过JOIN_ORDER提示设置表的连接顺序

explain analyze
select /*+ JOIN_ORDER(country,city) */ count(*) 
from city 
inner join country using(country_id)
where city.last_update='2006-02-15 04:45:25'\G

# 比较这两种连接方式的性能
 select left(sql_text,40), rows_examined,timer_wait/1000000000 ms 
 from performance_schema.events_statements_history 
 where sql_text like '%city inner join country%' 
 and thread_id!=ps_current_thread_id() 
 order by timer_start desc 
 limit 2;
 ● +------------------------------------------+---------------+--------+
● | left(sql_text,40) 						 |rows_examined  | ms 	  |
● +------------------------------------------+---------------+--------+
● | select count(*) from city inner join cou | 1200 		| 2.7704 |
● | select /*+ JOIN_ORDER(country,city) */ c | 709 			| 2.2053 |
● +------------------------------------------+---------------+--------+

增大连接缓冲

  • 连接缓存是MySQL用来在连接时进行数据缓存的区域。每次连接使用一个连接缓存，因此执行一个SQL语句可能用到多个连接缓存，连接缓存在SQL语句执行之前分配，执行完成后释放。每个连接缓存的大小由系统参数join_buffer_size决定，对于这个参数的设置，长期以来的建议是：初始值可以分配得小一些，对于需要大的连接缓存的会话和SQL语句可以单独进行调整，如果统一设置得很大，对于很多SQL语句实际上是浪费。但在MySQL 8.0.18以后的版本，连接缓存的分配是根据需要进行递增分配，join_buffer_size只是连接缓存的上限，但外连接要分配全部的连接缓存，从MySQL 8.0.20以后，包括外连接对连接缓存的需求也可以进行递增的分配了，因此设置一个较大的join_buffer_size已经不会有什么副作用了。

设置连接缓存

# 系统参数join_buffer_size默认值是256KB，下面分别是在会话级和全局级把它设置为1GB的例子：
set join_buffer_size=1024*1024*1024;

set global join_buffer_size==1024*1024*1024;

# 也可以使用set_var提示对单个SQL语句调节join_buffer_size的大小。

使用默认连接缓存时执行sql

# 当前会话的连接缓存是256kb，执行一个连接sql语句如下：
select count(*) from sbtest1 inner join sbtest2 using (c);
1 row in set (7.57 sec)

# 查看驱动表的状态
show table status like 'sbtest1'\G

增大连接缓存后在执行这个sql

# 在SQL语句里通过提示设置join_buffer_size的大小为60M，进行对比测试如下：
select /*+ set_var(join_buffer_size=60M)) */ count(*) from sbtest1 inner join sbtest2 using (c);
1 row in set (1.57 sec)

优化排序

  • mysql种对记录进行排序有两种实现方式：

    • 一种是使用索引进行排序，在执行计划的Extra字段中会有Using index的信息。
    • 另一种是不使用索引进行排序，称之为文件排序（filesort），在执行计划的Extra字段中会有Using filesort的信息。在多表连接的时候，如果需要保存中间排序结果进行连接，Extra字段中会有“Using temporary; Using filesort“ 的信息
# 例如actor表的last_name字段上有索引，而first_name字段上没有索引，对这两个字段分别进行排序，两个执行计划如下：
 explain select last_name from actor order by last_name\G
  Extra: Using index
  
 explain select first_name from actor order by first_name\G
 Extra: Using filesort

系统参数 sort_buffer_size

  • 文件排序使用的内存大小由系统参数sort_buffer_size决定，默认是256K，这对大数据量的排序是不够用的。在早期的版本里，sort_buffer_size设定的是固定的排序缓存大小。从MySQL 8.0.12开始，sort_buffer_size设定的是排序缓存的上限，对于排序过程中使用的内存是根据需要递增分配的，因此把它设置成一个较大的值并没有副作用。排序时需要的排序缓存可能比预计的大得多，因为MySQL会给每条记录都会分配一个能容纳最大记录的内存，例如varchar类型的字符串是按照它的完整长度分配空间，对于变长的UTF字符集也是按最长的字节分配空间。如果排序缓存不够，MySQL会将数据分块排序，然后进行合并，这个过程中会在磁盘上生成临时文件，因此效率会大大下降，可以从状态参数Sort_merge_passes中看到合并的次数，这个值最好是0，如果过大，建议增加sort_buffer_size的设置。
# 首先重置状态变量：
 flush status;
# 执行一个排序的SQL语句如下：
select * from sbtest2 order by c;

#  SHOW STATUS LIKE 'sort%';
+-------------------+--------+
| Variable_name 	| Value |
+-------------------+--------+
| Sort_merge_passes | 193 	 |
| Sort_range 		| 0      |
| Sort_rows 		| 666666 |
| Sort_scan 		| 1      |
+-------------------+--------+


# 减少查询的字段
flush status;

# 第二次执行这个排序的SQL语句如下：
select c from sbtest2 order by c;

# 第二次查询与排序相关的状态变量如下：
SHOW STATUS LIKE 'sort%';
+-------------------+--------+
● | Variable_name | Value |
● +-------------------+--------+
● | Sort_merge_passes | 165 	|
● | Sort_range 		  | 0 		|
● | Sort_rows 		  | 666666 	|
● | Sort_scan 		  | 1 		|
● +-------------------+--------+

增大排序缓存

select /*+ set_var(sort_buffer_size=1G) */ c from sbtest2 order by c;

show status like 'sort%';
sort_merge_passes=0

# 比较3个SQL语句的执行性能
select left(SQL_TEXT,40), sort_merge_passes,TIMER_WAIT/1000000000 MS 
from performance_schema.events_statements_history 
where SQL_TEXT like '%order by%' and THREAD_ID!=PS_CURRENT_THREAD_ID() 
order by TIMER_START desc 
limit 3;

优化索引排序

# 对需要排序的字段增加索引通常可以让优化器使用索引排序，但不是绝对的，因为优化器要考虑总体的性能，例如下面SQL语句：
 explain select * from sbtest2 order by k\G
 
 extra: Using filesort
# 虽然k字段上有索引，优化器仍然采用了filesort，因为这个SQL语句查询了表里的所有字段，如果只查询k字段：
explain select k from sbtest2 order by k\G

Extra:  using index
# 这个时候优化器就采用了索引排序。因此在写SQL语句时，不要简单地使用一个星号查询所有的字段，应该把需要的字段在select后面一个一个地列出来，即使的确需要查询所有的字段，严谨的做法也是把字段一个一个地都列出来，因为将来表结构的变化，可能会增加、修改或删除字段。

表空间碎片整理

表空间碎片的产生

  • MySQL的表在进行了多次delete、update和insert后，表空间会出现碎片。定期进行表空间整理，消除碎片可以提高访问表空间的性能

没有碎片的表

# 收集一个有100万记录表的统计信息的命令和输出结果如下：
+----------------+---------+----------+-----------------------------+
| Table 		 | Op 	   | Msg_type | Msg_text 					|
+----------------+---------+----------+-----------------------------+
| sbtest.sbtest1 | analyze | status | Table is already up to date |
+----------------+---------+----------+-----------------------------+

# 查询这个表的状态信息如下
 show table status like 'sbtest1'\G

从操作系统层查询对应数据文件

system ls -l /var/lib/mysql/sbtest/sbtest1.*
-rw-r----- 1 mysql mysql 729000000 May 31 08:24 /var/lib/mysql/sbtest/sbtest1.MYD
-rw-r----- 1 mysql mysql 20457472 May 31 08:25 /var/lib/mysql/sbtest/sbtest1.MYI
# 命令show table status和从操作系统层看到的数据文件大小一致，这时的Data_free为零。

可以看到这次Data_free不再是0了，计算Data _free和Data_length的比率如下

整理表空间

alter table sbtest1 force;

# 再次查看

注意事项

  • 用InnoDB表，速度的提高没有这么明显，因为InnoDB的数据会缓存到InnoDB缓存中，MyISAM表的数据MySQL不进行缓存，OS可能会缓存，因此要得到准确的测试结果，在Linux系统上每次测试前要使用下面的命令释放系统的缓存：
echo 3 > /proc/sys/vm/drop_caches
  • 使用alter table force进行表空间整理和optimize table命令的作用一样，这个命令适用于InnoDB, MyISAM和ARCHIVE三种引擎的表。但对于InnoDB的表，不支持optimize table命令，例如：
mysql> optimize table sbtest2\G

Msg_text: Table does not support optimize, doing recreate + analyze instead

# 可以使用命令替换engine
alter table sbtest1 engine=innodb

检查表空间的碎片

# 下面是找出表空间中可释放空间超过2M的最大10个表的SQL命令和输出结果：
select TABLE_SCHEMA,
		table_name,
		round(data_length/1024/1024) as data_length_mb, 
		round(data_free/1024/1024) as data_free_mb 
from information_schema.tables 
where round(data_free/1024/1024) > 2 
and table_schema not in ('mysql') 
order by data_free_mb 
desc limit 10;

mysqlcheck工具

  • 可以使用MySQL自带的工具mysqlcheck的-o选项进行表空间优化，这个工具适合于在脚本中进行批量处理，可以被Linux中的crontab或Windows中的计划任务调用。

  • 先使用-a进行统计信息收集，再使用-o进行表空间优化。

# 对单个表进行表空间优化的命令如下：
mysqlcheck -o sbtest sbtest1
# 也可以使用下面的命令对某个数据库中的所有表进行表空间优化：
mysqlcheck -o sbtest
# 对整个实例中对所有数据库进行表空间优化的命令如下：
 mysqlcheck -o --all-databases
# 注意这种方式不支持InnoDB的表，可以用alter table tb engine=innodb代替。

让sql优雅且高效的CTE

CTE 简介

  • MySQL从8.0开始支持CTE（Common Table Expression），CTE是一个命名的临时结果集，它存在于单个语句的范围内，并且可以在该语句中多次引用，而且CTE还可以相互引用。在MySQL 8之前，进行复杂查询时需要使用子查询来实现，造成SQL语句复杂、性能低，而且可读性差。CTE的出现简化了复杂查询语句的编写，提高了SQL性能。
# CTE的基本语法如下：
WITH cte_name (column_list) AS (
    query
)
SELECT * FROM cte_name;
# 查询（query）中的字段数必须与column_list中的字段数相同。如果省略column_list，CTE将使用查询中的列。
posted @ 2024-12-16 16:59  水一RAR  阅读(4)  评论(0)  编辑  收藏  举报