mysql优化

数据采集分析工具

profile

set profiling=1;
show profiles;

performance_scheme

show processlist

执行计划

命令

/* explain sql语句*/
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c2;

输出内容

列	含义
id	语句标识
select_type	语句类型
table	输出行引用的表
partitions	匹配的分区
type	join类型
possible_keys	候选索引
key	实际使用的索引
key_len	实际使用的索引键值长度
ref	与索引列比较的列或常量
rows	估计行数
filtered	通过条件筛选行的占比
Extra	额外信息

type

类型	含义
system	表只有一条记录
const	最多只有一条记录，一般为基于主键的查询
eq_ref	对于每个行连接，只有一行记录读取，一般为主键索引或唯一非空索引，并且索引全部列被使用
ref	与索引值匹配的所有行都被读取
fulltext	执行全文索引
ref_or_null	同ref,但包括NULL的查找
index_merge	索引合并优化,合并多个针对单表的range查询
unique_subquery	使用索引查找函数替换in子查询,value in(select primary_key from t where condition)
index_subquery	同unique_subquery，但是索引是非唯一索引
range	范围查询
index	全索引树扫描
all	全表扫描

Extra

可以重点关注下面这些值：

• Using filesort 使用了文件排序
• Using temporary 使用了临时表
• Using index condition 索引下推
• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) 使用了join buffer，关注join buffer大小配置

优化

设计优化

合理设计表，使表的数据尽可能小，不仅可以占用更少的磁盘空间和更小的内存，还能减少读写次数，提高处理速度.这些设计考虑包括表的存储引擎、列的数据类型、行的格式以及索引方法等

• 在满足业务的情况下，尽量使用小的数据类型
• 如果可以，不允许列为NULL，但是如果需要则用，不建议为NULL值列设置默认值
• 设置合理的ROW_FORMAT，Innodb默认为DYNAMIC
• 主键列尽可能小，在Innodb 中，会自动对非聚族索引进行扩展（每个索引项都会存储主键列的值），小的主键列能更节省空间
• 只在需要的情况下创建索引
• 如果一个长字符串列前缀具有唯一性，如果需要可以使用该前缀建立索引
• 需要join的列使用相同的列定义
• 合理使用范式和反范式

数据类型

整型

• 能使用numeric类型，尽量使用数字类型

字符串

• 如果不需要特定语言的比较或排序，尽量使用二进制类型，包括比较或排序
• 如果不同列需要比较，尽量使用相同的字符集和排序类型
• 如果列的数据比较大（text/varchar/blob），但是又不经常使用，尽量把该列放在单独的表中
• 合理的选择字符集,比如数据类容只有拉丁字符，选择latin1就可以了
• InnoDB存储引擎中，如果使用随机值作为主键，建议使用日期时间作为前缀，从而使连续的主键值相邻存储，插入和检索速度更快
• 如果字符串是可变长度，建议使用varchar，而不是char

日期时间

• 不要使用字符串存储日期时间类型，通常日期时间类型比字符串节省空间，并且可以利用日期函数进行计算
• datetime具有时区无关性，timestamp时区相关性

枚举类型

使用枚举类型代替字符串，枚举比使用字符串占用更少的空间

主键选择

代理主键是与业务无关的实体唯一标识，而自然主键是实体的属性，可以唯一标识实体，比如身份证号，推荐使用代理主键

数据引擎选择

合理的选择数据存储引擎

	MyISAM	InnoDB
事务	不支持	支持
索引	非聚族索引	聚族索引
锁	表锁	表锁、行锁
全文索引	支持	>5.6
适用场景	读	读写

适当冗余

被频繁引用的字段，只能通过join 2张及以上的大表才能获得

索引优化

索引数据结构

Hash（用于memory存储引擎）

• 只能用于等值比较（=、<=、>=）
• 不会提升排序的速度
• 只能整个hash key进行匹配，不能部分匹配

B+tree

• 可用于 =, >, >=, <, <=, or BETWEEN操作
• like 参数以常量打头，也会使用索引
• 如果列定义了索引，列的IS NULL查询也会使用索引

索引存储

• 聚族索引:数据与索引键值存在一起
• 非聚族索引:数据与索引键值分开存储

索引类型

• 主键索引、唯一索引、普通索引、全文索引、组合索引

索引匹配方式

• 全值匹配
• 最左匹配
• 匹配列前缀
• 匹配范围值
• 精确匹配列及范围匹配组合
• 只访问索引查询

名词

• 覆盖索引:检索的数据直接可以从索引中取得

  create table t(id int ,index_col1 varchar(10) not null,index_col2 varchar(10) not null,index_col2 varchar(10) not null,index(index_col1,index_col2,index_col3));
  select index_col2 from t where index_col1='33';
  

• 回表: 先查询非聚族索引，找到行主键值，再根据行主键值在聚族索引中检索出行记录
• 索引下推:索引遍历过程中,直接使用索引中的列数据进行部分条件计算过滤掉部分行，从而减少从表中获取行的数量

  /*基于邮编建立索引，但是在建立索引时，包含了name列的数据，或者name列也是索引的一部分*/
  SELECT * FROM people WHERE zipcode='95054' AND name LIKE '%etrunia%';
  
  

优化建议

• 查询条件尽量不使用列计算表达式
• 尽量使用主键查询
• 对于字符串类型字段，如果前缀具有较高的唯一性，可以基于前缀建立索引，使用前缀查询提升速度

  create table t (id int ,name varchar(255),index idx_name(name(6)));
  select * from t where name='dkk';	
  

• 使用索引排序
• union all/in/or 推荐使用in
• <、>范围查询可以使用索引
• 强制数据类型转换会全表扫描
• 更新频繁、数据区分度不明显的列不易建索引
• 索引列不允许为空
• 表连接不要超过3张表
• 如果知道返回结果数量，limit能提高效率
• 单表索引不超过5个
• 单索引字段不超过5个字段
• 索引对于小表或者查询大表中的绝大部分数据不会带来效率上的提升
• 通过少量合适的组合索引代替在每个列上都建索引
• 组合索引中，采用最左匹配原则，条件顺序与定义顺序是相关的，最左前缀必须位于AND组

  create table t(id int ,index_col1 varchar(10) not null,index_col2 varchar(10) not null,index_col2 varchar(10) not null,index(index_col1,index_col2,index_col3));
  /*不会使用索引*/
  select * from t where index_col1='kkk' or index_col2='33';
  /*不会使用索引*/
  select * from t where index_col2='kkk' and index_col1='33';
    /*会使用索引*/
  select * from t where index_col1='kkk' and index_col2='33';
    /*会使用索引*/
  select * from t where index_col1='kkk' and index_col2='33' or index_col3='90kk';
  
  

索引监控

show status like 'Handler_read%';

• Handler_read_key——通过索引读取行的次数，值越高，说明建立了合适的索引
• Handler_read_first——第一条索引被读取的次数，值越大，说明全索引扫描的次数越多，比如select index_col from a,index_col列建立了索引
• Handler_read_rnd——固定位置读取行的次数，对查询结果排序越多，值越高，说明存在大量的全表扫描查询或者表连接使用了不合适的索引
• Handler_read_rnd_next——数据文件中读取下一行的次数，表扫描越多，值越大，说明索引不合适，或者查询语句没有充分利用索引

查询优化

• 避免为了调优而使语句难以理解和维护
• 考虑是否添加了合适的索引
• 调优语句的每个部分
• 尽量避免对大表的全表扫描
• 及时更新表的统计信息
• 考虑锁引起的性能问题
• 研究语句的执行计划

where

• 去掉不必要的括号
• 无group by或聚合函数的情况下，合并having条件到where

join

hash join（mysql版本>=8.0.18）

针对无索引列的表间等值连接

create t1(a int,b int);
create t2(a int,b int);
select * from t1 join t2 on t1.b=t2.b;

Nested-Loop Join Algorithm

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

Block Nested-Loop Join Algorithm

使用join buffer存储外层循环使用到的列，减少内层循环读取外层行的次数

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

• join buffer 存储的是join列，而不是整行数据
• 每个join都会分配一个join buffer
• 该算法在8.0.20之后使用hash join替换

索引下推

• 只适用于range, ref, eq_ref, and ref_or_null表关联
• InnoDB 和 MyISAM有效
• 适用于非聚族索引，并且索引列是非虚拟列（由其他列的值生成）
• 用了子查询、函数的条件不能下推

常数折叠（Constant-Folding）

常数与列值比较时，当常数超出列数据类型的数据范围，条件将会重写。但是如果比较操作为between、in或者列为二进制列、日期时间列则不会进行折叠

整型列

1. 常量整型值超出列范围，条件将被折叠为true或者is not null
2. 常量为浮点数，先根据符号进行取整处理，再按照规则1处理
3. 常量为字符串，则先转换成整型（浮点型），在按规则1处理

DECIMAL（REAL)列

1. 整型常量则检查是否超出列定义的整型部分，如果超出则折叠
   
2. decimal\real常量，如果常量的整型部分超出列整型部分的定义，则折叠，如果小数部分超出列的小数部分定义，则截断，根据比较操作（=<>），对应调整操作(>=、<=)，如果数太小，则转换为小数0
3. 字符串常量，则先解析为整型或小数类型按前面规则处理

FLOAT(DOUBLE)列

基本同decimal\real

order by

使用索引排序

mysql有时会使用索引排序，即使索引并不完全匹配，但是以下情况，不会使用索引排序：

create table t(a int,b int,c int,d int,index idx_bcd(b,c,d),index idx_a(a));

• 排序列属于不同的索引
• 使用了索引的非连续部分

• where条件使用的索引与order by 排序列的索引不同
  
• order by使用了表达式

  select * from t where b=3 order by abs(c)
  

• group by 和order by 使用的不同的表达式
• 索引的只是排序列的前缀
• hash索引
• 排序列的名称与 select 列的别名同名
  

filesort排序

无法使用索引排序的情况下，则使用filesort排序，filesort会根据需要动态增加需要的buffer,直到达到sort_buffer_size系统变量设置大小，如果还是不能满足需求，则会使用临时磁盘文件

优化

• 尽量能使用索引排序
• 根据sort_merge_passes 状态变量的值合理调整max_sort_length、sort_buffer_size 系统参数
• 增加**read_rnd_buffer_size ** 系统变量值，以使每次读取更多的行
• 确保tmpdir变量指定的目录有足够的磁盘空间

group by

通常情况下，通过扫描整个表并建立临时表，然后基于临时表进行分组和函数计算，但是在某些情况下，可通过索引扫描来进行分组，是否使用索引，依赖于查询使用到的索引哪些部分以及对应的条件和聚合函数，索引扫描分为松散索引扫描和紧密索引扫描

松散索引扫描（loose index scan）

通常来说，处理分组最有效的方式是直接从索引中检索分组列，为了达到此目的，通常会使用有序索引（比如b+tree），但是在使用索引时，不会使用满足所有where条件的所有索引列，因此该方法称为松散索引扫描。当没有where条件时，将从索引中读取尽可能多的分组列；当where条件中包括范围查询（查询计划type 为range）,则只取满足范围条件的每个分组的第一列,在下列情况下使用松散索引扫描：

create table t(a int,b int,c int,d int,index idx_bcd(b,c,d),index idx_a(a));

• 单表查询
  

• 分组列与索引最左前缀匹配，比如索引列为（c1,c2,c3），group by c1,c2能使用，但是group by c2,c3,则不能使用索引
  

• select中唯一聚合函数min、max、sum、avg、count引用列与分组列位于同一索引，并且分组列采用最左匹配索引
  

• 索引中除了group by引用到的列之外，其余必须是常量查询
  
  以上最优的select c,d from t where b=3 group by c,d;

• 索引必须是列的全值索引，而不是列值前缀索引

紧密索引扫描（tight index scan）

紧密索引扫描根据查询条件进行全索引扫描或范围索引扫描,为了能使用索引扫描，查询条件的所有涉及列必须是常量等值条件，并且能与分组列满足索引最左匹配

distinct

distinct可以认为是特殊的group by ,因此，用于group by 的优化也可用于distinct

函数

函数分为确定性函数（固定参数返回固定值）、非确定函数（固定参数返回不同的值），非确定函数固定参数每次执行的结果都不一样，mysql需要对每一行进行计算，优化器不能对其进行优化，影响查询性能，对于非确定函数我们可以进行如下优化：

• 采用变量或临时表替换函数
• 添加其他条件，过滤掉尽可能多的数据

锁优化

内部锁

内部锁分为行锁和表锁，通常情况下，行锁具有更少的锁冲突、回滚时数据修改更少等优点，而表级锁则在内存使用更低、对表进行大多数数据操作（查询、更新、分组、全表扫描等）时会更快。但是，以下情况将优先使用表锁：

• 表上大多数语句都是读取语句
• 读写混合，并且写都是通过键单行更新或删除
• select 混合insert ,但是几乎没有delete或update
• 非常多的全表扫描或则分组

外部锁

使用外部文件系统锁，主要在MyISAM存储引擎

元数据锁（metadata lock）

用于管理数据库对象，表结构、表空间、存储过程等

Buffer Pool

Innodb存储引擎的Buffer Pool采用分页链表结构，包括年轻代和老年代链表，页淘汰采用LRU算法，年轻代表头是最近使用的页，而老年代的尾部是最近最少使用的页，新加入的页会放在老年代的头部（如果加入时要使用会直接放入年轻代头部），当老年代的页被访问时，会移入年轻代的页头，随着不断的数据库操作，最近最少使用的，会被淘汰掉，年轻代与老年代的比列为5:3。

Innodb监控命令SHOW ENGINE INNODB STATUS 输出中的 buffer pool and memory 能够查看buffer pool的情况，通过监控数据我们能对buffer pool配置进行优化。

• 根据实际情况，尽可能大的配置buffer pool(innodb_buffer_pool_size)
• 64位系统上，配置多实例buffer pool实例(innodb_buffer_pool_instances)，从而减少buffer pool竞争
• 通过设置old区的占缓存的百分比（innodb_old_blocks_pct）和第一次访问后待在老年代的时间（innodb_old_blocks_time）两个参数来使热点数据尽量保留在buffer中，减少大表或索引扫描带来的干扰
• 可以通过设置innodb_read_ahead_threshold 或 innodb_random_read_ahead两个参数进行数据预取
• 通过参数innodb_max_dirty_pages_pct 和 innodb_max_dirty_pages_pct_lwm 调整把脏数据写入磁盘的频率
• 通过设置参数innodb_buffer_pool_dump_pct减少server启动后buffer的预热时间

内存使用

• buffer pool
• 客户端连接管理包括thread stack(thread_stack)、connection buffer/result buffer (net_buffer_length、max_allowed_packet)
• 临时表（如果内存临时表太大，将使用磁盘临时表）
• Performance Schema 动态增量分配内存
• 对表执行顺序扫描将会分配read buffer(read_buffer_size),当随机读取行时，则分配随机读buffer(read_rnd_buffer_size)
• 大多数排序请求会依据结果集大小分配sort buffer和两个以内的临时文件

磁盘I/O

• 索引、数据、日志放在不同的物理磁盘上
• 增加buffer pool大小
• 通过innodb_flush_method参数调整flush 方法,如果存在写入瓶颈
• 通过** innodb_fsync_threshold**参数调整数据刷新到磁盘的频率
• 根据情况（如无法控制写请求数量，导致读请求饿死），linux上关闭使用系统异步IO调用(innodb_use_native_aio)
• 设置合适的IO容量参数（innodb_io_capacity）,在不造成积压的情况下，尽量低
• 在确定 zlib压缩算法版本一致的情况下，关闭innodb_log_compressed_pages减少redo log

事务（Innodb）

• 对于只有select语句的事务打开autocommit有助于事务优化，但是对于比较繁忙的数据库打开autocommit，大量的小事务则会影响性能，尽可能把相关的几个数据更改放在同一个事务中
• 避免进行大量数据更改后进行回滚操作，大事务本生会降低服务器性能，对其进行回滚将使性能更糟，甚至数倍的时间，即使杀掉服务器线程，重启后回滚操作仍然会启动，为了避免出现该问题，可以1)、把大事务拆封成多个小事务分步提交；2)、增加buffer pool的大小，避免频繁的刷新磁盘；3)、设置* innodb_change_buffering=all*使删除和更改都会被缓存
• 如果能够承受少部分最新事务数据丢失，可以设置* innodb_flush_log_at_trx_commit=0*，以使innodb能尝试每秒刷新一次日志
• 修改或删除的行及关联的重做日志并不会立即进行物理修改，而是会保留到较早或并发启动的事务完成后才会被物理修改
• 在长事务中修改或删除行，其他使用read committed或repeatable read 隔离级别的事务涉及这些行，就必须重构旧数据；如果在长事务中修改了表，则使用到该表的查询就不会使用覆盖索引技术

redo log

• 尽量保持redo log 文件同buffer pool一样大
• 配置innodb_log_write_ahead_size 同操作系统或文件系统的缓存块大小一致
• 如果有比较多的的大事务，考虑增加log buffer大小
• 根据并发情况优化等待flush redo log的自旋延迟时间(innodb_log_wait_for_flush_spin_hwm、innodb_log_spin_cpu_abs_lwm、innodb_log_spin_cpu_pct_hwm)

参考文献

mysql官方手册.