【MySQL】为什么不要问我DB极限QPS/TPS

为什么不要问我DB极限QPS/TPS

背景 相信很多开发都会有这个疑问，DB到底可以支撑多大的业务量，如何去评估？对于这个很专业的问题，DBA也没有办法直接告诉你，更多的都是靠经验提供一个看似靠谱的结果，这里主要说明数据库容量评估的难点。

定性分析

借用学校时候做物理题的一个思考方法 -- 极限法；我们假设两种极限场景: 极限场景一，所有SQL 都是主键等值查询。极限场景二，所有SQL 都是走不上索引的全表扫描。这两种场景下大家都能够一眼看出数据库的支撑能力，在场景一和场景二下会有很大的差别。当然，我们现实的业务场景，位于两种极限场景之间，这个时候很难简单粗暴的说当前实例可以支撑多少业务量，因为缺少信息输入。DBA 同学一定会和研发同学进行详细的沟通，确认数据库运行SQL 的类型，以及不同类型SQL 的执行频率，所涉及表的数据量情况，综合评估一个可以支撑的性能区间，作为上线前的基本容量建设模型。

随着系统上线后，数据库系统就会一直处于一种变化的状态。变化一，SQL 类型，随着业务逻辑不断丰富，运行SQL 开始逐渐变得更复杂，从最开始设计的几类SQL 到 十几类SQL 甚至 几十类SQL，这就要求我们对新上线的SQL 书写质量有一个保障机制，就是大家熟悉的SQL Review，当前有人工Review 和 IDB 自动Review 两种保障机制。变化二，业务表的数据量也在不断增长，针对小表很高效的SQL，随着数据量的增长，运行效率会逐步下降，出现大促业务流量突增时，就是一个稳定性风险。DBA 针对这几类问题都有相应的处理策略，这不是今天的重点，会在后续的系列文章中逐一和大家介绍。

常见影响性能案例

大规模数据导出功能

相信很多业务都遇到过数据导出，明细展示这方面的需求，sql基本上都是先求一个数据的总和然后，limit n,m分页查询，这样的问题就在于，在扫描前面的数据时是不会有性能问题的，当n值越大，偏移量越多，扫描的数据就越多，这个时候就会产生问题，一个本来不的sql就会变成慢sql，导致DB性能下降。针对这种问题DBA都会建议开发将limit n,m改为id范围的查询，或者进行业务改造对于一些不必要的场景只展示前几百条，只需要进行一次分页即可。

类似sql模式：

   select count(*) from table_name_1;
   select * from table_name_1 limit n,m;(n值越大性能越差)
   建议改造成：
   select * from table_name_1 where id>? and id<?

ERP类系统使用聚合函数或者分组排序

类似仓库内管理系统会需要展示很多统计信息，很多开发会选择在DB端计算出结果直接展示，问题在于sum，max，min类的聚合函数在DB端执行会消耗到CPU资源，如果这个时候还遇到索引不合理的情况，往往会带来灾难性的后果。这种情况DB端除了增加索引，对CPU的消耗是无法优化的，所以DB性能必然下降。一般这种情况DBA会建议能在程序端计算的就不要放在DB端，或者直接接搜索引擎。

类似sql模式：

    select sum(column_name) as column_1 from table_name_1;
    or
    select distinct cloumn_name  from table_name_1 group by column_name_1 order by column_name_1;

错误使用子查询

在DB端执行去重，join以及子查询等操作的时候，mysql会自动创建临时表。

DB自动创建临时表的情况有如下几种

1. Evaluation of UNION statements.
2.  Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.
3. Evaluation of derived tables (subqueries in the FROM clause).（这个是本节关注的重点）
4. Tables created for subquery or semi-join materialization (see Section 8.2.1.18, “Subquery Optimization”).
5. Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.
6. Evaluation of DISTINCT combined with ORDER BY may require a temporary table.
7. For queries that use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
8. Evaluation of multiple-table UPDATE statements.
9. Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.

在mysql中，对于子查询，外层每执行一次，内层子查询要重复执行一次，所以一般建议用join代替子查询。

下面举一个子查询引起DB性能问题的例子

Query1：select count(*) from wd_order_late_reason_send wrs left join wd_order_detail_late_send wds on wrs.store_code = wds.store_code;

下面是执行计划：

*************************<strong> 1. row </strong>***********************<strong>
       id: 1
       select_type: SIMPLE
       table: wrs
       type: ALL
       possible_keys: NULL
       key: NULL
       key_len: NULL
       ref: NULL
       rows: 836846
       Extra: NULL
 </strong>***********************<strong> 2. row </strong>*************************
       id: 1
       select_type: SIMPLE
       table: wds
       type: ALL
       possible_keys: NULL
       key: NULL
       key_len: NULL
       ref: NULL
       rows: 670612
       Extra: Using where; Using join buffer (Block Nested Loop)

Query2：select count(*) from (select wrs.store_code from wd_order_late_reason_send wrs left join wd_order_detail_late_send wds on wrs.store_code = wds.store_code) tb；

执行计划如下

  *************************<strong> 1. row </strong>***********************<strong>
       id: 1
       select_type: PRIMARY
       table: <derived2>
       type: ALL
       possible_keys: NULL
       key: NULL
       key_len: NULL
       ref: NULL
       rows: 561198969752
       Extra: NULL
 </strong>***********************<strong> 2. row </strong>***********************<strong>
       id: 2
       select_type: DERIVED
       table: wrs
       type: ALL
       possible_keys: NULL
       key: NULL
       key_len: NULL
       ref: NULL
       rows: 836846
       Extra: NULL
</strong>***********************<strong> 3. row </strong>*************************
       id: 2
       select_type: DERIVED
       table: wds
       type: ALL
       possible_keys: NULL
       key: NULL
       key_len: NULL
       ref: NULL
       rows: 670612
       Extra: Using where; Using join buffer (Block Nested Loop)

这两个sql结果相同，唯一不同的是第二条sql使用了子查询。通过执行计划可以看出（排除没有索引部分）两个sql最大的差别就是第二个sql有derived table并且rows是561198969752，出现这个数值是因为在select count（*）每次计数的时候子查询的sql都会执行一遍，所以最后是子查询join的笛卡尔积。因为内存中用于进行join操作的空间有限，这个时候就会使用磁盘空间来创建临时表，所以当第二种sql频繁执行的时候会有磁盘被撑爆的风险。 想要了解更多关于子查询的优化可以参考下面这个链接link

慢sql

这里我们所说的慢sql主要指那些由于索引使用不正确或没有使用索引产生的，一般可以通过增加索引。一个合理的索引对一条sql性能的影响是非常巨大的。索引的主要目的是为了减少读取的数据块，也就是我们常说的逻辑读，读取的数据块越少，sql效率越高。另外索引在一定程度上也可以减少CPU的消耗，例如排序，分组，因为索引本来就是有序的。

说到逻辑读，对应的就会有物理读，在mysql服务端是有buffer pool来缓存硬盘中的数据，但是这个buffer pool的大小跟磁盘中数据文件的大小是不等的，往往buffer pool会远远小于磁盘中数据的大小。buffer pool会有一个LRU链表，当从磁盘中加载数据块到内存中（这个就是物理读）发现没有空间的时候会优先覆盖LRU链表中的数据块。当一条sql没有合理的索引需要扫描大量的数据的时候，不光要扫描内存中的许多数据块，还可能需要从磁盘中加载不同不存在的数据块到内存中进行判断，当这种情况频繁发生的时候，sql性能就会急剧下降，因而也影响了DB实例的性能。

以下表格是访问不同存储设备的rt，由此可见一个合理的索引的重要性。

类别	吞吐量	响应时间
访问L1	Cache	0.5ns
访问L2	Cache	7ns
内存访问	800M/s	100ns
机械盘	300M/s	10ms
SSD	300M/s	0.1~0.2ms

日志刷盘策略不合理

目前集团mysql大部分使用的都是innodb存储引擎，因此在每条DML语句执行时不光会记如binlog还有记录innodb特有的redo log和undo log。这些日志文件都是先写入内存中然后在刷新到磁盘中。在server端有两个参数分别控制他们的写入速度。innodb_flush_log_at_trx_commit控制redo log写入模式，sync_binlog控制binlog写入模式。

通过以上表格可以了解到，在使用线上默认配置的情况下每次commit都会刷redo log到磁盘，也就是说每次写入都会伴随着日志刷盘的操作，需要消耗磁盘IO，所以在高TPS或者类似业务大促情况下，DBA可以调整这个参数，来提升DB支撑TPS的能力。

BP设置过小

前面已经提到sql在读写数据的时候不会直接跟磁盘交互，而是先读写内存数据，因为这样最快。但是考虑到成本问题BP（buffer pool）大小是有限的，不可能跟数据文件同等大小，所以如果BP设置不合理就会导致DB的QPS TPS始终上不去。下面我们具体分析一下。

mysql buffer pool中包含undo page，insert buffer page，adaptive hash index，index page，lock info，data dictionary等等DB相关信息，但是这些page都可以归为三类free page,clean page,dirty page.buffer pool中维护了三个链表：free list,dirty list,lru list

• free page:此page未被使用，此种类型page位于free链表中
• clean page:此page被使用，对应数据文件中的一个页面，但是页面没有被修改，此种类型page位于lru链表中
• dirty page:此page被使用，对应数据文件中的一个页面，但是页面被修改过，此种类型page位于lru链表和flush链表中

当BP设置过小的时候，比如BP 10g 数据文件有200g 这个时候有大量的select或者dml语句，mysql就会频繁的刷新lru list或者dirty list 到磁盘，大部分时间消耗在刷磁盘上，而不是业务sql处理上，这个时候就会导致业务TPS QPS始终上不去，伴随着DB内存命中率降低。通常这个时候的解决办法是需要DBA调整一下实例BP的大小。

硬件问题

就像生活中会有意外一样，在排除了之前那些因素之后，还会存在因为硬件故障或者参数设置不合理导致DB性能抖动的情况，如果不能立即修复，DBA一般只能通过迁移实例的方式来消除影响。

写在后面

经过上面几个情景的描述，我们可以把影响线上DB性能的因素归为三类：1、业务逻辑问题 2、DB端设置问题 3、硬件问题。因为硬件问题属于小概率事件，所以影响线上DB性能的主要是前面两类因素，也因此不同的业务场景下，DB的表现是天差地别的。