MySQL:性能优化

206 阅读13分钟

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

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

  • 连接——配置优化

第一个环节是客户端连接到服务端,连接这一块有可能会出现什么样的性能问题? 有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error 1040: Too many connections 的错误。

  1. 从服务端来说,我们可以增加服务端的可用连接数。

    修改配置参数增加可用连接数,修改 max_connections 的大小:

    show variables like 'max_connections'; 
    -- 修改最大连接数,当有多个应用连接的时候
    

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

    show global variables like 'wait_timeout'; 
    --及时释放不活动的连接,注意不要释放连接池还在使用的连接
    
  2. 从客户端来说,可以减少从服务端获取的连接数,如果我们想要不是每一次执行 SQL 都创建一个新的连接,应该怎么做?

    这个时候我们可以引入连接池,实现连接的重用。

    连接池的大小应该怎么设置呢?

    连接池并不是越大越好,只要维护一定数量大小的连接池, 其他的客户端排队等待获取连接就可以了。有的时候连接池越大,效率反而越低。 Druid 的默认最大连接池大小是 8。Hikari 的默认最大连接池大小是 10。

    为什么默认值都是这么小呢?

    在 Hikari 的 github 文档中,给出了一个 PostgreSQL 数据库建议的设置连接池大小的公式:地址

    它的建议是机器核数乘以 2 加 1。也就是说,4 核的机器,连接池维护 9 个连接就 够了。这个公式从一定程度上来说对其他数据库也是适用的。这里面还有一个减少连接 池大小实现提升并发度和吞吐量的案例。

    为什么有的情况下,减少连接数反而会提升吞吐量呢?为什么建议设置的连接池大 小要跟 CPU 的核数相关呢?

    每一个连接,服务端都需要创建一个线程去处理它。连接数越多,服务端创建的线程数就会越多。

    CPU 是怎么同时执行远远超过它的核数大小的任务的?时间片。

    上下文切换。 而 CPU 的核数是有限的,频繁的上下文切换会造成比较大的性能开销。

  • 缓存

    在应用系统的并发数非常大的情况下,如果没有缓存,会造成两个问题:一方面是 会给数据库带来很大的压力。另一方面,从应用的层面来说,操作数据的速度也会受到 影响。

    我们可以用第三方的缓存服务来解决这个问题,例如 Redis。

  • 主从复制

    如果单台数据库服务满足不了访问需求,那我们可以做数据库的集群方案。

    主从复制是怎么实现的呢?更新语句会记录 binlog,它是一种逻辑日志。

    有了这个 binlog,从服务器会获取主服务器的 binlog 文件,然后解析里面的 SQL 语句,在从服务器上面执行一遍,保持主从的数据一致。

    这里面涉及到三个线程,连接到 master 获取 binlog,并且解析 binlog 写入中继日 志,这个线程叫做 I/O 线程。

    Master 节点上有一个 log dump 线程,是用来发送 binlog 给 slave 的。

    从库的 SQL 线程,是用来读取 relay log,把数据写入到数据库的。

    image

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

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

    主从复制的方案之后,我们只把数据写入 master 节点,而读的请求可以分担到 slave 节点。我们把这种方案叫做读写分离。读写分离可以一定程度低减轻数据库服务器的访问压力,但是需要特别注意主从数 据一致性的问题。

    slave 的 SQL单线程执行

    slave 的 SQL 却只能单线程排队执行,在主库并发量很大的情况下,同步数据肯定会出现延迟。 为什么从库上的 SQL Thread 不能并行执行呢?举个例子,主库执行了多条 SQL 语句,首先用户发表了一条评论,然后修改了内容,最后把这条评论删除了。这三条语句 在从库上的执行顺序肯定是不能颠倒的。

    insert into user_comments (10000009,'nice');
    update user_comments set content ='very good' where id =10000009; 
    delete from user_comments where id =10000009;
    

如何解决不同的节点之间数据一致性的问题。

异步复制

在主从复制的过程中,MySQL 默认是异步复制的。也就是说, 对于主节点来说,写入 binlog,事务结束,就返回给客户端了。对于 slave 来说,接收 到 binlog,就完事儿了,master 不关心 slave 的数据有没有写入成功。

全同步复制

如果要减少延迟,是不是可以等待全部从库的事务执行完毕,才返回给客户端呢? 这样的方式叫做全同步复制。从库写完数据,主库才返会给客户端。

事务执行的时间会变长,它会导致 master 节点性能下降。

半同步复制

主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库 接收到 binlog 并写到 relay log 中才返回给客户端。master 不会等待很长的时间,但是 返回给客户端的时候,数据就即将写入成功了,因为它只剩最后一步了:就是读取 relay log,写入从库。

如果我们要在数据库里面用半同步复制,必须安装一个插件,这个是谷歌的一位工 程师贡献的。这个插件在 mysql 的插件目录下已经有提供:

cd /usr/lib64/mysql/plugin/

主库和从库是不同的插件,安装之后需要启用:

-- 主库执行
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_enabled=1; show variables like '%semi_sync%';

-- 从库执行
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; set global rpl_semi_sync_slave_enabled=1;
show global variables like '%semi%';

异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延 迟,它需要等待一个 slave 写入中继日志,这里多了一个网络交互的过程,所以,半同步 复制最好在低延时的网络中使用。

多库并行复制

如果 3 条语句是在三个数据库执行,操作各自的 数据库,是不是肯定不会产生并发的问题呢?执行的顺序也没有要求。当然是,所以如 果是操作三个数据库,这三个数据库的从库的 SQL 线程可以并发执行。这是 MySQL 5.6 版本里面支持的多库并行复制。

但是在大部分的情况下,我们都是单库多表的情况,在一个数据库里面怎么实现并 行复制呢?或者说,我们知道,数据库本身就是支持多个事务同时操作的

为什么这些 事务在主库上面可以并行执行,却不会出现问题呢?

因为他们本身就是互相不干扰的,比如这些事务是操作不同的表,或者操作不同的 行,不存在资源的竞争和数据的干扰。那在主库上并行执行的事务,在从库上肯定也是 可以并行执行。

异步复制之 GTID 复制

我们可以把那些在主库上并行执行的事务,分为一个组,并且给他们编号, 这一个组的事务在从库上面也可以并行执行。这个编号,我们把它叫做 GTID(Global Transaction Identifiers),这种主从复制的方式,我们把它叫做基于 GTID 的复制。

如果我们要使用 GTID 复制,我们可以通过修改配置参数打开它,默认是关闭的:

show global variables like 'gtid_mode';

无论是优化 master 和 slave 的连接方式,还是让从库可以并行执行 SQL,都是从数据库的层面去解决主从复制延迟的问题。

分库分表

垂直分库,减少并发压力。水平分表,解决存储瓶颈。

垂直分库的做法,把一个数据库按照业务拆分成不同的数据库。

慢查询日志 slow query log

show variables like 'slow_query%';

除了这个开关,还有一个参数,控制执行超过多长时间的 SQL 才记录到慢日志,默 认是 10 秒。

set @@global.slow_query_log=1; 
-- 1 开启,0 关闭,重启后失效
set @@global.long_query_time=3;
 -- mysql 默认的慢查询时间是 10 秒,另开一个窗口后才会查到最新值 ​
show variables like '%long_query%';

修改配置文件 my.cnf。

slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log

查询用时最多的20条数据

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

EXPLAIN 执行计划 官网地址

  • id

    id 值不同的时候,先查询 id 值大的(先大后小)

    id 值相同时,表的查询顺序是从上往下顺序执行。

  • select_type

    image

    SIMPLE:简单查询,不包含子查询,不包含关联查询 union。

    PRIMARY:子查询 SQL 语句中的主查询,也就是最外面的那层查询。

    SUBQUERY:子查询中所有的内层查询都是 SUBQUERY 类型的。

    DERIVED:衍生查询,表示在得到最终查询结果之前会用到临时表。

    UNION:用到了 UNION 查询。

    UNION RESULT:主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询 存在 UNION。

  • table

    查询所用的多的表

  • type

    上面的最好,越往下越差。system > const > eq_ref > ref > range > index > all

    以上访问类型除了 all,都能用到索引。

    system:system 是 const 的一种特例,只有一行满足条件。例如:只有一条数据的系统表。

    EXPLAIN SELECT * FROM mysql.proxies_priv;
    

    const:主键索引或者唯一索引,只能查到一条数据的 SQL。

    DROP TABLE IF EXISTS single_data;
    CREATE TABLE single_data(
        id int(3) PRIMARY KEY,
        content varchar(20) 
    );
    insert into single_data values(1,'a');
    EXPLAIN SELECT * FROM single_data a where id = 1;
    

    eq_ref:通常出现在多表的 join 查询,表示对于前表的每一个结果,,都只能匹配到后表的 一行结果。一般是唯一性索引的查询(UNIQUE 或 PRIMARY KEY)。eq_ref 是除 const 之外最好的访问类型。

    以上三种 system,const,eq_ref,都是可遇而不可求的,基本上很难优化到这个 状态。

    ref:查询用到了非唯一性索引,或者关联操作只使用了索引的最左前缀。

    range:索引范围扫描。如果where后面是 betweenand 或 <或 > 或 >= 或 <=或in这些,type类型就为 range。

    不走索引一定是全表扫描(ALL),所以先加上普通索引。

    Index:Full Index Scan,查询全部索引中的数据(比不走索引要快)。

    ALL:Full Table Scan,如果没有索引或者没有用到索引,type 就是 ALL。代表全表扫描。

    一般来说,需要保证查询至少达到 range 级别,最好能达到 ref。 ALL(全表扫描)和 index(查询全部索引)都是需要优化的。

  • possible_keys

    可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。

  • key

    实际用到的索引

  • key_len

    索引的长度(使用的字节数)。跟索引字段的类型、长度有关。

  • ref

    使用哪个列或者常数和索引一起从表中筛选数据。

  • rows

    MySQL 认为扫描多少行才能返回请求的数据,是一个预估值。一般来说行数越少越好。

  • Extra

    执行计划给出的额外的信息说明。

    • using index:用到了覆盖索引,不需要回表。

    • using where:使用了 where 过滤,表示存储引擎返回的记录并不是所有的都满足查询条件,需要 在 server 层进行过滤(跟是否使用索引没有关系)。

    • Using index condition(索引条件下推):索引下推

    • using filesort:不能使用索引来排序,用到了额外的排序(跟磁盘或文件没有关系)。需要优化。

    • using temporary:用到了临时表。例如(以下不是全部的情况)

      distinct 非索引列

          EXPLAIN select DISTINCT(tid) from test;
      

模拟优化器执行 SQL 查询语句的过程,来知道 MySQL 是怎么处理一条 SQL 语句的。 通过这种方式我们可以分析语句或者表的性能瓶颈。分析出问题之后,就是对 SQL 语句的具体优化。

SQL与索引优化

当我们的 SQL 语句比较复杂,有多个关联和子查询的时候,就要分析 SQL 语句有没 有改写的方法。

存储引擎

为不同的业务表选择不同的存储引擎,例如:查询插入操作多的业务表,用 MyISAM。 临时数据用 Memeroy。常规的并发大更新多的表用 InnoDB。

字段类型

  • INT 有 8 种类型,不同的类型的最大存储范围是不一样的。

    性别?用 TINYINT,因为 ENUM 也是整型存储。

  • 变长情况下,varchar 更节省空间,但是对于 varchar 字段,需要一个字节来记录长度。

    固定长度的用 char,不要用 varchar。

  • 非空:

    非空字段尽量定义成 NOT NULL,提供默认值,或者使用特殊值、空串代替 null。NULL 类型的存储、优化、使用都会存在问题。

  • 不要用外键、触发器、视图:

    降低了可读性; 影响数据库性能,应该把把计算的事情交给程序,数据库专心做存储; 数据的完整性应该在程序中检查。

  • 大文件存储:

    不要用数据库存储图片(比如 base64 编码)或者大文件;把文件放在 NAS 上,数据库只需要存储 URI(相对路径),在应用中配置 NAS 服 务器地址。

  • 表拆分:

  • 将不常用的字段拆分出去,避免列数过多和数据量过大。

    比如在业务系统中,要记录所有接收和发送的消息,这个消息是 XML 格式的,用 blob 或者 text 存储,用来追踪和判断重复,可以建立一张表专门用来存储报文。