跳至主要內容

14.调优

12a2024/10/3大约 17 分钟数据库后端sql

14.调优

14.1 优化表设计

14.2 查询优化

  • 虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块

    • 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点在于索引的使用
    • 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高

  • 索引优化：尽可能使用上索引，加速查询，具体可见索引设计原则及之后的选择规则和失效原因

  • 关联查询优化：驱动表先查询，驱动被驱动表。左外连接中，左表为驱动表，右表为被驱动表；右外连接中，右表为驱动表，左表为被驱动表；内连接由优化器选择驱动表，一般为小表驱动大表，无索引的驱动可用上索引的表。驱动表使用Explain查看时出现在执行计划最上面

    • 优化器可能改造外连接，修改驱动表
    • 小表驱动大表，方便建立小的hash索引，使用hash join每张表访问一遍
    • 无索引驱动有索引的表，如果表内容很多，无法使用hash join，可以根据索引查找减少查找成本
    • 除此之外，优化器还会考虑选择合适的join buffer、删除不必要的字段，加快查询的速度

  • 子查询优化：MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：

    • 执行子查询时，需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询
    • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
    • 对于返回结果比较大的子查询，其对查询性能的影响也就越大

    提示

    • 可以使用分步查询代替子查询。分步查询可以利用索引
    • 可以使用连接查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。许多进行多表集合操作的查询都可以使用子查询完成
    使用连接查询代替子查询
    使用连接查询代替子查询

  • 排序优化：MySQL中有两种排序方法，即Index和FileSort

    • 基于索引，不需要额外排序，性能好，但数据量过大时，排序包含非索引字段需要大量回表，可能不会使用

    • 基于文件，需要额外使用CPU和内存完成，在大多数场景上性能差，有双路排序(先读取排序列和标识，排序之后在读取需要的数据)和单路排序(将所有数据都读取到内存中再排序)两种

    • 优化建议：

      • 在WHERE和ORDER BY中的字段应该创建索引，如果字段不同，最好是联合索引
      • 使用limit进行数据量限制、确保索引覆盖，避免大量数据行需要排序时却放弃索引
      • 优化器对排序方法会有影响。在limit且有排序字段在最左侧联合索引时，优化器可能修改排序和筛选的操作顺序。如果筛选结果集较小，优化器会考虑放弃索引
      • ORDER By排序顺序和索引顺序一致、排序方向应该一致

    • 无法使用索引排序时进行FileSort调优

      • 如果筛选结果集非常小，不需要对排序添加索引速度也很快。如果出现范围筛选时，为范围筛选字段和排序字段索引是需要二选一的，如果范围筛选之后数据很少，可以对范围筛选字段添加索引，而不需要在联合索引中添加排序字段，虽然使用了FileSort，但是速度很快
      • 提高sort_buffer_size，避免文件排序时空间不够，排序内容需要从内存保存回磁盘，增加开销；修改max_length_for_sort_data，对应最大的进行单路排序的行数据长度
      • 避免使用SELECT * order by，提高单路排序的效率
      -- 查询
SHOW VARIABLES LIKE '%sort_buffer_size%';
SHOW VARIABLES LIKE '%max_length_for_sort_data%';

-- 修改
SET GLOBAL innodb_sort_buffer_size = 1048576;
SET GLOBAL max_length_for_sort_data = 1024;

  • 分组优化

    • GROUP BY也可能使用索引，基于优化器选择
    • 一般查询是先排序再分组，遵照索引最左前缀法则
    • 能写在where ...中的筛选，不应该写在having ...中
    • order by、group by、distinct操作很费时间，where过滤结果行过多，可能影响性能，尽量少做

  • 分页优化：分页时，limit 20000, 10获取了很多行，但只取最后10行

    • 数据库中为索引分页做了优化(行数据更少，减少磁盘访问)，考虑转换为索引分页，再用子查询回表(延迟回表)
    • 分页实际上是返回不重复的数据，如果确保索引自增，可以使用索引筛选，再要求返回10条数据，避免访问无效数据
    select * from t limit 20000, 10;

-- 考虑单独对主键或索引键排序，可以使用索引，避免回表
select * from tbl_name a, (select id from tbl_name order by id LIMIT 20000, 10) b where a.id = b.id;

-- 如果索引是自增的，可以考虑转换为索引比较
-- 但要注意，数据行可能删除，需要确保每一页的内容不缺失不重复
-- 20000是不对的，应该填入上一页最后数据的id
select * from tbl_name where id > 20000 limit 10;

  • 其他优化：

    • 避免SELECT *，解析时会通过查询数据字典获取所有字段，耗费时间，同时无法索引覆盖
    • 确定结果只有一条时，LIMIT 1可以优化性能，如果有唯一索引等值查询时，可以不加
    • 多使用COMMIT，能够释放redo/undo/log空间，管理三种空间的内部花费，释放锁资源，释放用于回滚的信息

14.3 选择内存数据库作为缓存

  • 对于查询响应要求高(响应时间要短、吞吐量大)的业务，可以考虑使用Redis这样的内存数据库

14.4 库级优化

  • 读写分离：主从复制
  • 数据分片：数据库分库分表

如果我们的目的在于提升数据库高并发访问的效率，那么首先考虑的是如何优化SQL和索引，这种方式简单有效；其次才是采用缓存的策略，比如使用Rdis将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用主从架构，进行读写分离

14.4.1 主从复制

  • 主从同步可以提高数据库吞吐量，也可以提高数据库的并发处理能力

    • 主库写入，从库读取，可以均衡负载，同时减少了锁的影响，让读取更流畅
    • 是一种热备份机制，同时确保了高可用性

  • 原理

    • 从服务器读取主服务器的日志来进行数据同步，不同数据库使用不同的日志
      • MySQL：binlog(二进制日志)
      • Oracle：redo log(重做日志)
      • PostgreSQL：WAL(预写日志)
      • SQL Server：Oplog(操作日志)
    • 每个从服务器Slave只有一个主服务器Master，每个从服务器只有一个服务器ID
    • 具体操作
    MySQL
    MySQL主从复制示意图
    MySQL主从复制示意图
    • 相关线程
      • 二进制日志转储线程Binlog dump thread：主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件(Event)的时候，会在Binlog上加锁，读取完成之后，再将锁释放掉
      • 从库I/O线程：会连接到主库，向主库发送请求更新Binlog。这时从库的I/O线程就可以读取到主库的二进制日志转储线程发送的Binlog更新部分，并且拷贝到本地的中继日志(Relay log)
      • 从库SQL线程：会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步
    MySQL主从复制流程图
    MySQL主从复制流程图
    • 按日志数据流分析
      • Master将写操作记录到二进制日志(binlog)。这些记录叫做二进制日志事件(binary log events)
      • Slave将Master的binary log events拷贝到它的中继日志(relay log);
      • Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的，而且重启后从接入点开始复制

      在进行主从复制之前，要确认变量log_bin开启

  • 搭建

    • 确保服务器之间可以正常通信，防火墙确定可通过

      如果是复制得到的主机，需要确保MAC地址、主机名、IP地址、主机UUID和数据库服务，如：MySQL Server的UUID修改了

    • 修改配置

      MySQL
      my.cnf
      # 修改后记得重启 mysqld 服务
[mysqld]
# [必须] 服务器需要唯一ID
server-id = 1
# [必须] 启用二进制日志
log-bin = 文件路径
# [选择] 主机为0，从机为1
read-only = 0
# [选择] 日志保留时间
binlog_expire_logs_seconds = 6000
# [选择] 控制二进制文件大小，默认为1GB
max_binlog_size = 200M
# [可选] 不需要复制的数据库
binlog-ignore-db = db_name
# [可选] 需要复制的数据库
binlog-do-db = db_name
# [可选] binlog格式
binlog_format = ROW

# 从机
[mysqld]
server-id = 2
# [可选] 启用 relay log
relay-log = 文件路径

    • 手动为从机创建需要复制的数据库，同步数据

    • 启动复制

      MySQL
      -- 1. 在主机上创建账户并授权 slave
CREATE USER '用户名'@'从机ip' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从机ip';
-- 8.0 为避免在从机执行 SHOW slave status 时 LAST_IO_ERROR 出现
-- Authentication requires secure connection 错误，需要把认证插件改成 mysql_native_password
-- 或 CHANGE MASTER TO GET_MASTER_PUBLIC_KEY = 1
ALTER USER '用户名'@'从机ip' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;

-- 2. 查看binlog状态
SHOW MASTER STATUS;
-- 记录下当前的文件名和 pos 值

-- 3. 在从机上配置复制通道
CHANGE MASTER TO
  MASTER_HOST='主机ip',
  MASTER_USER='主机用户名',
  MASTER_PASSWORD='主机用户名的密码',
  MASTER_LOG_FILE='日志文件名',
  MASTER_LOG_POS=当前pos值;

-- 也可以设置延迟，作为备份库，单位为秒
CHANGE MASTER TO MASTER_DELAY=3600;

-- 4. 启动复制
START SLAVE;

-- 如果启动时遇到初始化 relay log 文件结构失败
-- 删除原有 relay log 日志，并重新创建日志
RESET SLAVE;

-- 5. 查询复制的状态
SHOW SLAVE STATUS;
-- 确认 SLAVE_IO_RUNNING = "Yes"
-- 确认 SLAVE_SQL_RUNNING = "Yes"

STOP SLAVE; -- 停止复制

  • 双主双从：两个主机互相复制，并各带一个从机，主机1负责日常数据写入，其他都负责读操作，在主机1宕机之后，主机2负责写入，访问通过MyCat等中间件控制

  • 数据同步的一致性：有多种复制方式，按复制的一致性由弱到强为

    • 异步复制：主机提交日志后不需要等待从机回复，直接返回客户端，从机可能缺失服务器中已提交的数据
    • 半同步复制：主机提交日志后等待到至少一个从机回复，再返回客户端
    • 组复制：MySQL 5.7.17引入名叫Paxos的分布式一致性算法实现的一种新复制技术，将NN个节点组成一个复制组，在执行读写事务时，提交需要确保大多数节点同意(N2+1\frac{N}{2}+1)，每个复制节点维护了自己的数据副本，并且在协议层实现了原子消息和全局有序消息；只读事务不需要组内同意
    MySQL
    -- 相关配置
-- 半同步复制需要等待的从机回复数量
SHOW VARIABLES LIKE 'rpl_semi_sync_master_wait_for_slave_count';

14.5 服务器优化

14.5.1 硬件优化

服务器的硬件性能直接决定着数据库的性能。硬件的性能瓶颈直接决定数据库的运行速度和效率。针对性能瓶颈提高硬件配置，可以提高数据库查询、更新的速度

  • 配置较大的内存。足够大的内存是提高数据库性能的方法之一。内存的速度比磁盘I/O快得多，可以通过增加系统的缓冲区容量使数据在内存中停留的时间更长，以减少磁盘I/0
  • 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度
  • 合理分布磁盘I/O，把磁盘IO分散在多个设备上，以减少资源竞争，提高并行操作能力
  • 配置多处理器，对于多线程的数据库，多处理器可同时执行多个线程

14.5.2 参数优化

修改参数记得重启数据库

MySQL

  • 查询执行
    • innodb_buffer_pool_size：这个参数是MySQL最重要的参数之一，表示InnoDB类型的表和索引的最大缓存。它不仅仅缓存索引数据，还会缓存表的数据。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
    • key_buffer_size：表示索引缓冲区的大小。索引缓冲区是所有的线程共享。增加索引缓冲区可以得到更好处理的索引(对所有读和多重写)。当然，这个值不是越大越好，它的大小取决于内存的大小。如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。对于内存在4GB左右的服务器该参数可设置为256M或384M
    • table_cache：表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。物理内存越大，设置就越大。默认为2402，调到512-1024最佳。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能
    • sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2097144字节(约2MB)。对于内存在4GB左右的服务器推荐设置为6-8M,如果有100个连接，那么实际分配的总共排序缓冲区大小为100×6=600MB。
    • join_buffer_size：默认为8M，表示联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
    • read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的字节大小。当线程从表中连续读取记录时需要用到这个缓冲区。可以临时在会话级设置该参数的值。默认为64K,可以设置为4M
  • 查询缓冲区
    • query_cache_size：表示查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，就要增加Query_cache_size的值；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓存：
    • Qcache_free_blocks：如果该值非常大，则表明缓冲区中碎片很多。MySQL8.0之后失效。该参数需要和uery_cache_type配合使用
    • query_cache_type：值是0时，所有的查询都不使用查询缓存区。但是query_cache_type=0并不会释放query_cache_size所配置的缓存区内存。1表示查询都使用缓冲区，2表示只对SELECT SQL_CACHE查询使用缓冲区
  • 日志
    • innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。该参数的默认值为1
      • 0：表示每秒将日志写入磁盘
      • 1：表示每提交一个事务时将日志写入磁盘
      • 2：表示每提交一个事务时将日志写入内存日志文件，并每秒将日志写入磁盘
    • innodb_log_buffer_size：这是InnoDB的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入Innodb Log Buffer中，当满足写入磁盘的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件(或者同步到磁盘)中。
  • 数据库连接
    • max_connections：表示允许连接到MySQL的最大数量，默认值是151。如果状态变量connection_errors_max_connections不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections的值。在Linux平台下，性能好的服务器，支持500-1000个连接不是难事，需要根据服务器性能进行评估设定。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
    • back_log：用于控制MySQL监听TCP端口时设置的积压请求栈大小。如果数据库连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源，将会报错。MySQL 5.6.6版本之前默认值为50，之后的版本默认为50+(max_connections/5)50+(max\_connections/5),对于Linux系统推荐设置为小于512的整数，最大不超过900。如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log的值
    • thread_cache_s1ze：线程池缓存线程数量的大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为120
    • wait_timeout：指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10
    • interactive_timeout：表示服务器在关闭连接前等待行动的秒数

14.6 其他优化方法

  • 除了简单的分库分表，还可以对表设计优化
    • 冷热数据分表，减小大部分查询操作的表大小
    • 增加中间表
    • 增加冗余字段
    • 设置非空约束，避免判断，少量减少存储空间
  • 使用分析、检查、优化表命令
    • MySQL
      • analyze table：扫描表，并更新统计信息，比如索引区分度，索引选择的标准
      • check table：检查表，并修复错误
      • optimize table：优化表，并清理删除数据或更新Varchar、Blob、Text带来的文件碎片。实际上是创建新表，复制数据，对InnoDB(提示信息虽然显示无效)，MyISAM，Archive有效

      mysqlcheck --optimize database table -u username -p可以对特定的数据库表进行优化，table不给出会对数据库中所有表进行优化，--all-databases参数可以对所有数据库操作

  • 创建多个表共享的表空间create tablespace name add datafile 'file_name.ibd' size 10M;，提供给需要的表create table table_name tablespace name;
最近更新