Mysql 性能优化_在查询时减少读取的block数

Mysql性能优化

一：死锁

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务或者说一个线程持有锁的时候，会阻止其他的线程获取锁，这个时候 会造成阻塞等待，如果循环等待，会有可能造成死锁。

1.1、锁什么时候释放?

• 事务结束(commit, rollback)
• 事务竞争锁超时（50s）
• 客户端连接断开

1.2、如果一个事务一直未释放锁，其他事务会被阻塞多久？

如果一直等下去，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占 用大量计算机资源，造成严重性能问题，甚至拖跨数据库。

MySQL有一个参数'innodb_lock_wait_timeout'来控制获取锁的等待时间，默认是50秒。

1.3、死锁的发生

那么死锁需要满足什么条件？死锁的产生条件，因为锁本身是互斥的

• 互斥：同一时刻只能有一个事务持有这把锁；
• 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;
• 不可以强行剥夺：其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺；
• 形成环路：当多个事务形成等待环路的时候，即发生死锁

表锁是不会发生死锁的原因，因为表锁的资源都是一次性获取的。

如果锁一直没有释放，就有可能造成大量阻塞或者发生死锁，造成系统吞吐量下降, 这时候就要查看是哪些事务持有了锁

1.4、查看锁信息（日志）

show status like 'innodb_row_lock_%';
1

• lnnodb_row_lock_current_waits：当前正在等待锁定的数量；
• lnnodb_row_lock_time :从系统启动到现在锁定的总时间长度，单位ms；
• lnnodb_row_lock_time_avg:每次等待所花平均时间；
• lnnodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；
• lnnodb_row_lock_waits :从系统启动到现在总共等待的次数。

InnoDB还提供了三张表来分析事务与锁的情况:

select * from information schema.INNODB_TRX;—当前运行的所有事务，还有具体的语句
select * from information schema.INNODB_LOCKS; 当前出现的锁
select * from information schema.INNODB_LOCK_WAITS;锁等待的对应关系


1
2
3
4
5

通过分析锁日志，找出持有锁的事务之后呢?
如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID,也就是INNODB_TRX 表中的 trx_mysqI_thread_id,例如执行 kill 4, kill 7, kill 8.

当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。我们应该尽量在应用端也就是code层面避免。

1.5死锁的避免
1、程序中操作多张表，尽量相同的顺序访问，避免形成环路；
2、批量操作单表数据的时候，先对数据进行排序，避免形成环路；
3、申请足够级别的锁，如果要操作数据，就申请排它锁；
4、尽量为表添加合理的索引，使用索引访问数据，避免没有where条件的操作，避免锁表；
5、如果可以，大事务化成小事务，占有的资源锁越多，越容易出现死锁。；
6、使用等值査询而不是范围査询査询数据，命中记录，避免间隙锁对并发的影响；
7、降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁；

二：性能优化

我们说到性能调优，大部分时候想要实现的目标是让我们的査询更快。一个査询的流程又是由很多个环节组成的，每个环节都会消耗时间。

• 客户端、查询缓存、解析器、预处理器、查询优化器、执行计划、执行引擎 、存储引擎

我们要减少查询所消耗的时间，就要从每一个环节入手。

2.1、连接—配置优化

第一个环节是客户端连接到服务端，连接这一块有可能是服务端连接数不够导致应用程序获取不到连接。

Mysql: error 1040: Too many connections的错误。这个是超过了服务端设置的最大并发连接数
1

2.1.1、解决连接数不够的问题:

从服务端来说：

• 增加可用连接数，修改max connections的大小

 show variables like 'max connections';#修改最大连接数，当有多个应用连接的时候
1

• 及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小

show global variables like '#及时释放不活动的连接，注意不要释放连接池还在使用的连接
1

从客户端来说：

• 可以减少从服务端获取的连接数。可以使用连接池，实现连接的重用
  常见的DBCP和C3P0、阿里的Druid、Hikari (Spring Boot 2.x版本默认的连接池)

连接池并不是越大越好，只要维护一定数量大小的连接池，其他的客户端排队等待 获取连接就可以了。有的时候连接池越大，效率反而越低。Druid的默认最大连接池大小是8。Hikari的默认最大连接池大小是10。建议是机器核数乘以2加1。也就是说，4核的机器，连接池维护9个连接就够了。

2.1.2、数据库的参数配置

说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更 好地发挥硬件本身的性能，包括CPU、内存、磁盘、网络。在不同的硬件环境下，操作系统和MySQL的参数的配置是不同的，没有标准的配置。

Mysql大多数参数都提供了一个默认值，比如默认的页大小、连接数、缓冲区、日志大小等等，这些默认配置可以满足大部分情况的需求，除非有特殊的需求。

在清楚参数的含义的情况下再去修改它。修改配置的工作一般由专业的DBA完成。也有一些工具可以给出推荐值。

2.1.3、 缓存

除了合理设置服务端的连接数和客户端的连接池大小之外，我们还有哪些减少客户 端使用的连接数的方案呢？我们可以引入缓存。

在系统里面有一些很慢的查询，要么是数据量大，要么是关联的表多，要么是计算 逻辑非常复杂，这样的查询每次会占用连接很长的时间。所以为了减轻数据库的压力，和提升查询效率，我们可以把数据放到内存缓存起来, 比如使用redis，运行独立的缓存服务，属于架构层面的优化。

2.1.4、 主从集群

为了减少单台数据库服务器的读写压力，在架构层面我们还可以创建集群。

集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时 读写多台数据库节点，怎么让所有的节点数据保持一致？

这个时候我们需要用到复制技术(replication),被复制的节点称为master,复制 的节点称为slave。slave本身也可以作为其他节点的数据来源，这个叫做级联复制。

MySQL所有更新语句都会记录到Server层的binlog，有了这个binlog，slave服务器会不断获取master服务器的binlog文件，然后解析里面的SQL语句，在slave服务器上面执行一遍，保持主从的数据一致。

这里面涉及到三个线程

• 连接到master获取binlog,并且解析binlog写入中继日 志，这个线程叫做I/O线程。
• Master节点上有一个log dump线程，是用来发送binlog给slave的。
• 从库的SQL线程，是用来读取relay log,把数据写入到数据库的。

做了主从复制配置案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点，这种方案叫做读写分离

2.1.5、 分库分表

如果单张表存储的数据过大的时候，比如一张表有上亿的数据，每天以百万的量级增加，单表的查询性能还是会大 幅下降。这个时候我们应该怎么办呢？

这个时候就要用到分布式架构中的第二个重要的手段，叫做分片。把单个节点的数据分散到多个节点存储，减少存储和访问压力，这个就是限定数据范围、分库分表。

限定数据的范围 ：
务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

库分表总体上可以分为两类：

• 垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：减少并发压力
  垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂
  直分区可以简化表的结构，易于维护。
  垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行
  Join来解决。此外，垂直分区会让事务变得更加复杂；
• 水平分库分表的做法，单张表保持数据表结构不变，通过某种策略存储数据分片：解决存储瓶颈

但是尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。以上是架构层面的优化，可以用缓存，读写分离，分库分表。这些措施都可以减轻服务端的访问压力，提升客户端的响应效率。

2.2、优化器—SQL语句分析与优化

优化器的作用就是对我们的SQL语句进行优化分析，生成执行计划。

主要有两个切入点：

• 数据库慢SQL查询

• EXPLAIN 执行计划分析

2.2.1、慢查询日志slow query log

• 打开慢日志开关，因为开启慢查询日志是有代价的（跟binlog、optimizer-trace —样），所以它默认是关闭的，

show variables like 'slow_query%';
1

• 还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL

show variables like '%long_query';
1

参数的两种修改方式：

• 1、set动态修改参数（重启后失效）
• 2、修改配置文件my.cnf

MySQL提供了 mysqldumpslow的工具，在MySQL的bin目录下。

 mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost- slow.log
1

• Count代表这个SQL执行了多少次；
• Time代表执行的时间，括号里面是累计时间；
• Lock表示锁定的时间，括号是累计；
• Rows表示返回的记录数，括号是累计。

当然，有的时候查询慢，不一定是SQL语句的问题，也有可能是服务器状态的问题。 所以我们也要掌握一些查看服务器和存储引擎状态的命令。

#运行线程
show full processlist;
#显示用户运行线程。可以根据id号kill线程。
select * from infbrmation schema.processlist;
#查看MySQL服务器运行状态
SHOW GLOBAL STATUS;
#存储引擎的当前运行信息，包括事务持有的表锁、行锁信息;事务的锁等待情况；线程信号量等待；文件10请求；buffer pool统计信息。
show engine innodb status;
1
2
3
4
5
6
7
8

现在我们已经知道哪些SQL慢了，为什么慢呢？慢在哪里？

MySQL提供了一个执行计划的工具。通过EXPLAIN我们可以模拟服务端执行SQL 查询语句的过程。通过这种方式我们可以分析语句或者表的性能瓶颈。

2.2.2、EXPLAIN执行计划

参考： 官方文档

在Explain中可以看到很多的数据列，下面分别来说明一下每个数据列的含义

• id：id是查询序列编号，每张表都是单独访问的，一个SELECT就会有一个序号，
  • 在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表，小表驱动大表
• select_type表示查询类型，它的常用取值类型如下：
  • SIMPLE, 简单查询，不包含子查询，不包含关联查询union
  • PRIMARY, 表示SQL语句中的主查询，也就是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的所有内查询，都是subquery类型
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询.。即子查询依赖于外层查询的结果.
• type连接类型：type 字段比较重要, 它提供了判断查询是否高效的重要依据依据
  • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可。
  • system: 表中只有一条数据. 这个类型是特殊的 const 类型
  • eq_ref：在多表连接查询中，被驱动表通过唯一索引(UNIQUE或PRIMARY KEY)进行访问的时候，被驱动表的访问方式就是eq_ref
  • ref：查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀，则连接类型是ref
  • range：索引范围扫描
  • index：索引扫描，index 和all最大的区别是，index类型只扫描索引树即可，所以它要比all
    的查询效率要快
  • all：Full Table Scan，如果没有索引或者没有用到索引，type就是all。代表全表扫描。
  • null：不用访问表或者索引就能得到结果
• possible_key、key:possible_key表示可能用到的索引，key表示实际用到的索引。如果这一列为
  空，表示没有用到索引
• key_len:索引的长度（使用的字节数）。跟索引字段的类型、长度有关
• rows:认为扫描多少行才能返回请求的数据，是一个预估值，一般来说行数越少越好
• filtered:Filtered表示返回结果的行数占需读取行数的百分比 ，它只对index和all的扫描有效
• ref:使用哪个列或者常数和索引一起从表中筛选数据
• Extra:EXplain 中的很多额外的信息会在 Extra 字段显示
  • Using filesort: 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉
  • Using index:“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化

三：优化总结

3.1、服务端状态分析:
如果出现连接变慢，查询被阻塞，无法获取连接的情况

• 1、 重启
• 2、 show processlist查看线程状态，连接数数量、连接时间、状态
• 3、 查看锁的状态
• 4、 kill有问题的线程

3.2、对于具体的慢SQL:

3.2.1、分析SQL语句基本情况

• 表的表结构

• 字段的索引情况

• 每张表的数据量

• 查询的业务含义

这个非常重要，因为有的时候你会发现SQL根本没必要这么写，或者表设计是有问题的

3.2.1、分析SQL找出慢的原因

• 1、 查看执行计划，分析SQL的执行情况，了解表访问顺序、访问类型、索引、扫 描行数等信息
• 2、 如果总体的时间很长，不确定哪一个因素影响最大，通过条件的增减，顺序的调整，找出引起查询慢的主要原因，不断地尝试验证。

找到原因：比如是没有走索引引起的，还是关联查询引起的，还是order by问题。

3.2.1、具体问题具体解决

• 1、 创建索引或者联合索引
• 2、 改写SQL,这里需要平时积累经验，例如：
  • 1） 使用小表驱动大表
  • 2） 用join来代替子查询
  • 3） not exist 转换为 left join IS NULL
  • 4） or 改成 union
  • 5） 如果结果集允许重复的话，使用UNION ALL代替UNION
  • 6） 大偏移的limit,先过滤再排序。

如果SQL本身解决不了了，就要上升到表结构和架构

• 表结构（冗余、拆分-分库分表、not null等）
• 架构优化（缓存、主从集群读写分离）
• 业务层代码的优化

掌握正确的调优思路，才是解决数据库性能问题的根本。