调优维度

当咱们评论对数据库进行优化时，很多人第一反响想到的便是SQL优化，怎么创立索引，怎么改写SQL，他们把数据库优化与SQL优化划上了等号。

那今天咱们站在架构的角度来聊聊这一问题，数据库优化能够从哪些维度入手？

MySQL性能调优：提升性能的关键步骤

正如上图所示，数据库优化能够从架构优化，硬件优化，DB优化，SQL优化四个维度入手。

架构优化

一般来说在高并发的场景下对架构层进行优化其作用最为显着，常见的优化手法有：分布式缓存，读写别离，分库分表等，每种优化手法又适用于不同的运用场景。

分布式缓存

有句老话说的好，功能不够，缓存来凑。当需求在架构层进行优化时咱们第一时刻就会想到缓存这个神器，在运用与数据库之间添加一个缓存服务，如Redis。

读写别离

一主多从，读写别离，主动同步，是一种常见的数据库架构优化手法。

MySQL性能调优：提升性能的关键步骤

水平切分

水平切分，也是一种常见的数据库架构优化手法。

当你的运用业务数据量很大，单库容量成为功能瓶颈后，选用水平切分，能够下降数据库单库容量，进步数据库写功能。

MySQL性能调优：提升性能的关键步骤

架构优化总结

1.读写别离首要是用于处理 “数据库读功能问题”
2.水平切分首要是用于处理“数据库数据量大的问题”
3.分布式缓存架构或许比读写别离更适用于高并发、大数据量大场景。

硬件优化

对硬件设备和操作系统设置进行优化，例如调整操作系统参数、禁用 swap、添加内存、升级固态硬盘等等。

  • 保证MySQL服务器的硬件资源足够，例如内存、磁盘空间和CPU等。
  • 调整MySQL的装备参数，如缓冲区巨细、衔接数约束等，以习惯运用程序的需求。

咱们运用数据库，不管是读操作仍是写操作，最终都是要拜访磁盘，所以说磁盘的功能决定了数据库的功能。一块PCIE固态硬盘的功能是一般机械硬盘的几十倍不止。这儿咱们能够从吞吐率、IOPS两个维度看一下机械硬盘、一般固态硬盘、PCIE固态硬盘之间的功能指标。

吞吐率：单位时刻内读写的数据量

机械硬盘：约100MB/s ～ 200MB/s
一般固态硬盘：200MB/s ～ 500MB/s
PCIE固态硬盘：900MB/s ～ 3GB/s

IOPS：每秒IO操作的次数

机械硬盘：100 ～200
一般固态硬盘：30000 ～ 50000
PCIE固态硬盘：数十万

经过上面的数据能够很直观的看到不同规格的硬盘之间的功能差距非常大，当然功能更好的硬盘价格会更贵，在资金足够而且迫切需求进步数据库功能时，测验替换一下数据库的硬盘不失为一个非常好的举措，你之前遇到SQL履行缓慢问题在你替换硬盘后很或许将不再是问题。

DB优化

经过InnoDB架构概述对于DB优化首要优化各种buffer、log。

SQL履行慢有时分不必定完全是SQL问题，手动装置一台数据库而不做任何参数调整，再怎么优化SQL都无法让其功能最大化。要让一台数据库实例完全发挥其功能，首要咱们就得先优化数据库的实例参数。

数据库实例参数优化遵从三句口诀：日志不能小、缓存足够大、衔接要够用。

数据库业务提交后需求将业务对数据页的修改刷（ fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，功能较低，假如每次业务提交都要刷盘，会极大影响数据库的功能。数据库在架构规划中都会选用如下两个优化手法：

先将业务写到日志文件RedoLog（WAL），将随机写优化成次序写
加一层缓存结构Buffer，将单次写优化成次序写
所以日志跟缓存对数据库实例特别重要。而衔接假如不够用，数据库会直接抛出反常，系统无法拜访。

接下来咱们看下MySQL参数该怎么装备。

MySQL性能调优：提升性能的关键步骤

SQL优化

SQL优化很简单理解，便是经过树立适宜的表结构、给查询字段添加索引或许改写SQL进步其履行功率，一般来说，SQL编写有以下几个通用的技巧： 重点来看前两个纬度，要点如下图所示。

MySQL性能调优：提升性能的关键步骤

表结构及索引优化

  1. 合理运用索引

    • 索引少了查询慢；
    • 索引多了占用空间大，履行增修改句子的时分需求动态维护索引，影响功能
    • 挑选率高（重复值少）且被where频频引用需求树立B树索引；
    • JOIN字段建议树立索引；
    • 杂乱文档类型查询选用全文索引功率更好；
    • 索引的树立要在查询和DML功能之间取得平衡；
    • 复合索引创立时要留意基于非前导列查询的情况；
    • 创立联合索引时要依据最左准则考虑索引的复用能力，不要重复创立索引；要为保证数据不能重复的字段创立仅有索引等等。不过要留意索引对刺进、更新等写操作是有代价的，不要滥用索引，比如像性别这样仅有很差的字段就不适合树立索引。

  2. 表拆分

    • 要在规划表结构时，考虑数据库的水平与垂直扩展能力，提前规划好未来1年的数据量、读写量的增长，规划好分库分表方案。比如规划用户信息表，估计1年后用户数据10亿条，写QPS约5000，读QPS30000，能够规划按UID纬度进行散列，分为4个库每个库32张表，单表数据量控制在KW级别。
    • 能够将字段多的表分解成多个表，必要时添加中间表进行相关。假设一张表有40～50个字段显然不是一个好的规划。
    • 一般来说，规划联系数据库时需求满意第三范式，但为了满意第三范式，咱们或许会拆分出多张表。而在进行查询时需求对多张表进行相关查询，有时为了进步查询功率，会下降范式的要求，在表中保存必定的冗余信息，也叫做反范式。但要留意反范式必定要适度。

  3. 挑选适宜数据类型

    • 要为字段挑选适宜的数据类型，在保留扩展能力的前提下，优先选用较小的数据结构。例如保存年龄的字段，要运用TINYINT而不要运用INT。
    • 尽或许运用not null。MySQL难以对运用null的列进行查询优化，答应null会使索引、索引计算和值更加杂乱，答应null值的列需求更多的存储空间，还需求MySQL内部进行特殊处理。

sql句子优化

  1. 确认适宜的索引：

    • 经过运用恰当的索引，能够加快查询速度。剖析查询句子，确认最常用的查询条件和衔接条件，并为这些字段创立适宜的索引。

    • 运用单列索引或联合索引来掩盖查询条件和排序操作，以防止额定的表扫描和排序过程。

    • 运用最左前缀准则

      假如运用联合索引，要恪守最左前缀规矩。即要求运用联合索引进行查询，从索引的最左前列开端，不越过索引中的列而且不能运用范围查询(>、<、between、like)。

      MySQL性能调优：提升性能的关键步骤

  2. 保证查询句子运用最有效的语法结构。例如，运用JOIN来替代子查询，或运用UNION ALL来替代UNION等。

    • UNION ALL的履行功率比UNION高，UNION履行时需求排重；UNION需求对数据进行排序

  3. 运用适宜的JOIN类型（如INNER JOIN、LEFT JOIN等），以保证获取正确的结果集。

    • 在多表查询中，挑选适宜的JOIN操作（如INNER JOIN、LEFT JOIN等），以防止产生笛卡尔积和不必要的数据量。
    • 保证衔接条件上有索引，以进步衔接操作的功能。

  4. 不用select *，只挑选需求的列

    • 更多耗费CPU、内存、IO、网络带宽
    • 防止查询出不需求运用的字段
    • select/join削减磁盘暂时表的生成，特别是有text/blob时
    • 每次查询都要回表，不能走掩盖索引。

  5. 防止运用负向查询和%前缀模糊查询

    • 比如NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE %前缀等
    • 原因：B+树无法运用索引，导致全表扫描

  6. MySQL子查询

    • 子查询大部分情况比较差，子查询嵌套在主查询中，每次履行主查询时都需求履行子查询。这会导致在履行过程中进行屡次查询操作，添加了数据库的负载和查询时刻。
    • 防止杂乱SQL句子 ：进步可阅读性；防止慢查询的概率；能够转换成多个短查询，用业务端处理

  7. 小表驱动大表

    • 小表一般包含的数据量相对较少，经过将小表作为驱动表，能够削减参加查询的数据量。这样能够削减对大表的扫描和匹配操作，然后进步查询功率。
    • 小表上的查询一般能够更有效地运用索引，而大表上的查询或许需求进行全表扫描。经过以小表为根底进行查询，能够充分运用索引供给的快速数据拜访能力。
    • 在进行小表驱动查询时，能够先对小表进行筛选和过滤操作，然后再与大表进行相关。这样能够削减参加相关的数据量，提前过滤掉不符合条件的数据，削减了大表的数据处理量。

  8. 控制in中的个数，建议个数小于200

    • IN操作符将多个值进行匹配，假如IN列表中的值较多，会导致数据库履行更杂乱的查询操作，或许会添加查询的履行时刻。较小的IN列表能够更快地完成查询操作。
    • 较大的IN列表或许占用较多的内存，特别是当数据库服务器装备较低或内存资源有限时，很多的IN值或许导致内存不足的问题。
    • 数据库查询优化器在处理IN操作时，或许会依据IN列表的巨细做出不同的优化决议计划(eq_range_index_dive_limit)。较大的IN列表或许导致优化器挑选不理想的查询计划，然后影响查询功能。

  9. 防止全表扫描：

    • 运用索引来加快数据检索，防止全表扫描。
    • 保证表的计算信息是最新的，以协助优化器挑选适宜的履行计划。

  10. order by索引收效

    • order by排序应该遵从最佳左前缀查询，假如是运用多个索引字段进行排序，那么排序的规矩有必要相同（同是升序或许降序），不然索引同样会失效。

  11. 运用LIMIT约束结果集

    • 在需求回来很多数据的查询中，运用LIMIT来约束结果集的巨细，防止不必要的资源耗费。

剖析和优化慢查询

  • 要么是运用最频频的句子，要么是优化后进步最显着的句子，能够经过查询 MySQL的慢查询日志来发现需求进行优化的SQL句子；

    // 能够在装备文件中设置慢查询日志
log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2
// 设置慢查询开启
set global slow_query_log='ON';

    慢查询日志会在查询完毕今后才记载，所以在运用反响履行功率出现问题的时分慢查询日志并不能定位问题，此时应该运用show processlist指令检查当前 MySQL 正在进行的线程。包含线程的状况、是否锁表等，能够实时的检查 SQL 履行情况。同样，运用mysqladmin processlist句子也能得到此信息。

  • 要学会运用 MySQL 供给的剖析工具。例如运用 Explain 来剖析句子的履行计划，看看是否运用了索引，运用了哪个索引，扫描了多少记载，是否运用文件排序等等。或许运用 Profile 指令来剖析某个句子履行过程中各个分步的耗时。

SQL优化小结

这儿给大家总结一下SQL优化的套路：

  1. 检查履行计划 explain sql
  2. 假如有告警信息，检查告警信息 show warnings;
  3. 检查SQL触及的表结构和索引信息
  4. 依据履行计划，考虑或许的优化点
  5. 按照或许的优化点履行表结构变更、添加索引、SQL改写等操作
  6. 检查优化后的履行时刻和履行计划
  7. 假如优化作用不显着，重复第四步操作

索引及Explain剖析参阅上篇文章MySQL索引探究：解锁高效数据拜访的秘密