谈谈MySQL性能优化技巧

xuxiancheng
113 阅读11分钟

高并发场景下，业务服务器可通过水平扩展提高吞吐量；然而作为数据最终存储的数据库，由于需要保证数据安全和一致性等，往往成为了系统的性能瓶颈，（根据木桶效应，最短的木板决定了桶能装多少水），因此掌握数据库性能优化对应开发尤其是服务端开发人员尤为重要。

使用jmeter对MySQL进行压力测试

通过对使用jmeter对MySQL进行压力测试，让我们能对MySQL的性能有一个整体的认知，同时能够对自己实际的业务进行数据库层面的性能压测。

环境准备

  • MySQL 5.7
  • jemter
  • windows11（Linux类似的，我这里懒得去装虚拟机这些了，有兴趣的可以自己去试下）。
下载驱动

在测试计划中，需要添加JDBC驱动。这里我用的MySQL-5.7版本，JDBC驱动选择5.x版本。JDBC驱动在 MySQL官网下载，具体地址是： dev.mysql.com/downloads/f…

image.png

测试的过程

sql

select id from tb_seckill_goods where id=1;

1.配置数据库驱动

下载后解压文件夹，把文件夹中的mysql-connector-java-5.1.49.jar 复制到JMeter安装目录的bin文件下

image.png

2.配置线程组

image.png

3.配置JDBC连接池

添加JDBC Connection Configuration（JDBC连接池也有人叫连接组）

image.png

image.png

  • Variable Name ：数据库连接池的名称
  • JDBC Connection Configuration 算是一个数据库连接池配置
  • Variable Name：连接池唯一标识，后面JDBC Request需要用到。
  • Max Number of Connection： 池中允许的最大连接数，默认设置为20，压测可以将其设置为零 （0），这意味着没有线程池。
  • Max Wait：参数表示从连接池获取连接的超时等待时间，单位毫秒
  • Database URL 数据库连接
  • URL JDBC Driver class 数据库驱动
  • Username 数据库登录用户名
  • Password 数据库登录密码

*4.添加JDBC请求

右键点击“连接MySQL”，再添加一个采样器：JDBC request，在JMeter中request可以编辑select和 insert等不同的采样器类别。即通过不同的类别添加配置我们需要的对MySQL不同的操作。

image.png

5.添加结果监听器

  • 聚合报告
  • 查看结果树
  • 活动线程数Active Threads Over Time
  • 每秒事务数TPS
  • 平均响应时间RT
  • 服务端：内存、网络、CPU、磁盘io、网络io【单位mb】

image.png 6.查看测试结果

image.png

测试结论：本地测试，不考虑带宽和SQL语句性能，数据库能达到1.5W+的TPS。

客户端层面的优化技巧

连接池优化

使用Druid作为数据源，连接池相关参数配置如下：

# 初始化连接数 
spring.datasource.druid.initial-size=1 

# 最小空闲连接数，一般设置和initial-size一致 
spring.datasource.druid.min-idle=1 

# 最大活动连接数，数据库默认的最大连接数为151 
# 大多数场景下，20连接足够使用了，当然这个参数的配置还需要结合业务场景的特点 给与配置。一般标准是配置成为正常使用连接数的3-4倍即可！
# 20个连接connection，可以产生多大的TPS呢？
# RT-5ms，TPS=1000/5 x 20个=4000 
# RT-10ms，TPS=1000/10 x 20个=2000 
# RT-20ms，TPS=1000/20 x 20个=1000
spring.datasource.druid.max-active=20

# 参数表示从连接池获取连接的超时等待时间，单位毫秒, 设置为0时，表示不设置超时时间
# 配置建议，如果内网状态良好，获取连接等待时间800，网络状况不佳，推荐设置为1200。原因是TCP重连的时间一般是1秒。
spring.datasource.druid.max-wait=60000 

# 配置间隔多久启动一次销毁线程，对连接池内的空闲的connection进行检测，单位是毫秒。 
# 1.如果连接空闲并且超过minIdle以外的连接，如果空闲时间超过 minEvictableIdleTimeMillis设置的连接物理关闭。 
# 2.在minIdle以内的不处理。
spring.datasource.druid.time-between-eviction-runs-millis=60000 

# 配置一个连接在池中连接最小可清理的空闲时间，单位是毫秒 
spring.datasource.druid.min-evictable-idle-time-millis=300000

# 打开后，增强timeBetweenEvictionRunsMillis的周期性连接检查，minIdle内的空闲连接 
# 设置从连接池获取连接时是否检查连接有效性，true时，每次都检查;false时，不检查 spring.datasource.druid.test-on-borrow=false 

# 设置往连接池归还连接时是否检查连接有效性，true时，每次都检查;false时，不检查 spring.datasource.druid.test-on-return=false 

# 设置从连接池获取连接时是否检查连接有效性 
# 为true时，如果连接空闲时间超过minEvictableIdleTimeMillis进行检查，否则不检查 
# 为false时，不检查 
spring.datasource.druid.test-while-idle=true 

# 检验连接是否有效的查询语句 
# 如果数据库Driver支持ping()方法，则优先使用ping()方法进行检查，否则使用 validationQuery查询进行检查 spring.datasource.druid.validation-query=select 1 from dual 

# 每次检查强制验证连接有效性 
spring.datasource.druid.keep-alive=true

数据库连接池的配置和线程池配置类似，核心关注max-active、max-wait和最低的核心连接数。

SQL优化技巧

Explain分析SQL执行计划
  • id：SELECT识别符，这是SELECT查询序列号。
  • select_type：表示单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查 询等复杂查询。
  • table：表示查询的表
  • partitions：使用的哪些分区（对于非分区表值为null）。
  • type：显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：
    • system 表中只有一行数据或者是空表
    • const 使用唯一索引或者主键
    • eq_ref 唯一性索引扫描
    • ref 非唯一性索引扫描
    • fulltext 全文索引检索
    • ref_or_null 与ref方法类似，只是增加了null值的比较
    • unique_subquery 用于where中的in形式子查询，子查询返回不重复值唯一值
    • index_subquery 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复 值，可以使用索引将子查询去重
    • range 索引范围扫描。常见于使用>,<,is null,between ,in ,like等运算符的查询中。
    • index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使 用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所有索引，性能可能大 部分时间都不如range
    • index select结果列中使用到了索引，type会显示为index。
    • ALL 这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
  • possible_keys：此次查询中可能选用的索引
  • key：查询真正使用到的索引
  • key_len：显示MySQL决定使用的索引size
  • ref：哪个字段或常数与 key 一起被使用
  • rows：显示此查询一共扫描了多少行，这个是一个估计值，不是精确的值。
  • filtered: 表示此查询条件所过滤的数据的百分比
  • Extra：额外信息，这个列包含不适合在其他列中显示的，但十分重要的额外的信息，这个列可以显示的信息非常多，有几十种。经常遇到的有：
    • Using filesort 使用了文件排序，说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行 读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。这种操作需要优化sql。
    • Using index 表示相应的SELECT查询中使用到了索引，避免访问表的数据行，这种查询的效率很高
    • Using where 表示MySQL将对InnoDB提取的结果在SQL Layer层进行过滤，过滤条件字段无索引
    • Using join buffer 表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
Query Profiler诊断工具

Query Profiler是MySQL自带的一种Query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能 瓶颈在什么地方。通常我们是使用的explain，以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗 费的时间等。

使用navicat分析SQL每个环节执行耗时和耗时占比：

image.png

索引优化

什么情况下创建索引

  1. 频繁出现在where 条件字段，order排序，group by分组字段。
  2. select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）。
  3. 多表join关联查询，on两边的字段都要创建索引。

索引优化建议

1.尽量避免索引失效 2. 表记录很少不需创建索引 ：索引是要有存储的开销

  1. 一个表的索引个数不能过多
  2. 频繁更新的字段不建议作为索引
  3. 区分度低的字段，不建议建索引
  4. 在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段
  5. 不建议用无序的值作为索引
  6. 尽量创建组合索引，而不是单列索引
Limit优化

处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非 常差。

LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。

解决方案：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows

select * from (select * from tuser2 where id > 1000000 and id < 1000500 ORDER BY id) t limit 0, 20
子查询优化

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需 要多个步骤才能完成的SQL操作。子查询虽然很灵活，但是执行效率并不高。

那么问题又来了啊？ 为什么它效率不高？

把内层查询结果当作外层查询的比较条件的

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以子查询的速度会受到一定 的影响。这多了一个创建临时表和销毁表的过程。

优化方式：可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

其他查询优化
  • 小表驱动大表：建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描 的，以少关联多就可以减少这个扫描次数。
  • JOIN两张表的关联字段最好都建立索引，而且最好字段类型一致
  • 避免全表扫描：避免索引失效导致的全表扫描
  • 避免MySQL放弃索引：如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。
  • WHERE条件中尽量不要使用not in语句，建议使用not exists
    • NOT IN 在查询开始时构建一个包含排除值的临时列表。对于较小的表和少量值，这是一个简单且高效的方法。然而，对于大型表和大量值，NOT IN 可能会导致性能问题，因为它需要遍历整个列表来判断每个值是否匹配。
    • NOT EXISTS 使用子查询来执行条件筛选，避免了构建大型临时列表的开销，因此在处理大型表和大量值的情况下更加高效。此外，NOT EXISTS 通常可以使用索引来加快查询速度，而 NOT IN 则需要在字段和子查询结果之间进行比较，索引的利用率可能较低。

服务端层面的优化技巧

表结构的优化

  • 将字段很多的表分解成多个表（分表）
  • 增加中间表：对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询 的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。通常都是在统计当中使用，每次统计报表的时候都是离线统计，后台有有一个线程对你这统计查询结果 放入一个中间表，然后你对这个中间表查询。
  • 增加冗余字段：设计数据表时应尽量遵循关系数据库范式的规约，尽可能的减少冗余字段，让数据库设计看起来精致、 优雅。但是合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题
数据库配置
  • 开启慢查询日志
  • 合理调整连接数max_connections
  • 多线程配置
  • buffer pool配置
数据库架构
  • 主从复制架构 - 读写分离，提高读性能
avatar
文章
阅读
粉丝