mysql

MySQL 系列 查询优化

MySQL 技术内幕：InnoDB存储引擎

Posted by lichao modified on October 12, 2023

SQL 语句经过优化器的优化后会生成查询计划，查询计划分为若干个步骤，每一个步骤都包含一个或者多个sql算子。

查询性能分析

SQL 算子介绍

SQL算子可以理解为SQL语句执行过程中各个步骤的具体动作。

常用SQL算子分类

  • scan算子

    Scan 算子是用于扫描操作的算子，作用于表和视图。Scan 算子根据扫描的执行方式可以分为 TableScan 算子和 IndexScan 算子，分别执行顺序扫描和基于索引扫描。

  • filter算子

    顾名思义，Filter 算子是条件过滤算子，用于在 SQL 语句中根据一定的条件过滤表或视图中的数据。Filter 算子中至少包含一个过滤条件，且Filter算子中可以包含非常复杂的过滤条件。

  • join算子

    join 算子是用于完成连接操作的所有算子的统称，Join 算子本身包含了连接条件。在具体的 SQL 语句中，Join 算子可以隐式表达，也可以表达为join…on…形式。

  • Top N算子

    Top N 算子是完成限定操作的算子的统称，限定操作指的是基于结果集并在结果集上完成某种行为的操作，Top N算子的最典型的应用场景是分页。TopN算子具体可以表达为Top、Limit、Offset、RowNum等关键字。

  • sort算子

    sort 算子是 SQL 语句中用于执行排序操作的算子。排序操作是 SQL 语句中的常见操作，也是重要操作，而排序算子的具体实现，在不同的数据库中经常不同，同一种数据库中也会提供多种算法用于完成排序。

  • 分组算子

    分组算子是完成分组操作的算子，最常用的是 Group 算子和 Having 算子，还包括 Cube 算子、Rollup 算子等。Group 算子是完成分组操作的核心算子，而Having算子则提供了分组筛选条件。

  • 投影算子

    投影是 SELECT 语句中的必要组成部分，表达了 SELECT 语句的输出内容的结构。投影算子可以有多种形式，甚至投影列本身就是一个独立的 SQL 语句（子查询），最常见的表现形式是表或视图中的列，此外还可以表现为Scalar算子、Aggregation算子或Window算子。

  • Scalar 算子是完成标量运算的算子，都涉及到标量函数运算。

  • Aggregation 算子是聚合运算算子，包括 COUNT、MAX/MIN、SUM/AVG 等，通常结合分组算子出现。

  • Window 算子是窗口操作算子，以窗口函数形式出现。Window 算子是个复合结构，可以进一步细分为窗口函数算子、Over 算子、Partition 算子和Order算子。

  • Value 算子

    Value 算子是完成非投影部分的求值运算的算子，一般表现为标量函数，通常出现于其它多种算子的各种条件表达式中，比如出现在 Filter 算子或 Join 算子中完成过滤条件表达式或连接条件表达式的求值运算。

explain内容介绍

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。

explain命令介绍

索引示例 同时建立两个一模一样的表s1和s2，表结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE single_table (
        id INT NOT NULL AUTO_INCREMENT,
        key1 VARCHAR(100),
        key2 INT,
        key3 VARCHAR(100),
        key_part1 VARCHAR(100),
        key_part2 VARCHAR(100),
        key_part3 VARCHAR(100),
        common_field VARCHAR(100),
        PRIMARY KEY (id),
        KEY idx_key1 (key1),
        UNIQUE KEY idx_key2 (key2),
        KEY idx_key3 (key3),
        KEY idx_key_part(key_part1, key_part2, key_part3)
    ) Engine=InnoDB CHARSET=utf8;
  • Id: 查询语句中每出现一个select，MySQL就会为它分配一个唯一的id。
    • 对于连接查询，由于每个select关键字后边的from子句中可以跟随多个表，每个表在查询计划中都会对应一条记录，但是这些记录的id值都是相同的。出现在前面的表表示驱动表，出现在后面的表是被驱动表。 索引示例
    • 包含子查询的查询语句可能涉及多个select关键字，所以包含子查询的查询语句的执行计划中，每一个select都会对应一个唯一的id值。 索引示例
  • table: 不管查询语句有多复杂，最后都会对应到每个表的单表访问。explain语句输出的每条记录都对应着一个单表访问方法。table列代表该表的表名。
  • select_type: 可能的值有：SIMPLE，PRIMARY，UNION，UNION RESULT，SUBQUERY，DEPENDENT SUBQUERY，DEPENDENT UNION，DERIVED，MATERIALIZED，UNCACHEABLE SUBQUERY，UNCACHEABLE UNION。
    • SIMPLE：查询语句中不包含UNION或者子查询。 索引示例
    • PRIMARY：包含UNION/UNION ALL或者子查询的大查询是由几个小查询组成的，最左边的查询就是PRIMARY。
    • UNION：对于包含 UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION。
    • UNION RESULT ：MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT 。 索引示例
    • SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT关键字代表的那个查询的select_type就是SUBQUERY。 索引示例
    • DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是 DEPENDENT SUBQUERY
    • 索引示例
    • DEPENDENT UNION ：在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是 DEPENDENT UNION 索引示例
    • DERIVED：对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED 索引示例
    • MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED 索引示例
  • type：单表访问方法。
    • const ：根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const 索引示例
    • eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是eq_ref。 索引示例
    • ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
    • ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null。 索引示例
    • index_merge：一般情况下对于某个表的查询只能使用到一个索引，但在某些场景下可 以使用Intersection 、Union 、Sort-Union这三种索引合并的方式来执行查询。 索引示例
    • unique_subquery：类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery。 索引示例
    • index_subquery：index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引。 索引示例
    • range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法。 索引示例
    • index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。 索引示例
    • ALL ：全表扫描。 索引示例

  • possible_keys和key:

    在 EXPLAIN 语句输出的执行计划中， possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用 到的索引有哪些， key 列表示实际用到的索引有哪些 。 索引示例 上述执行计划的 possible_keys 列的值是 idx_key1,idx_key3 ，表示该查询可能使用到idx_key1,idx_key3 两 个索引，然后 key 列的值是 idx_key3 ，表示经过查询优化器计算使用不同索引的成本后，最后决定使用 idx_key3 来执行查询比较划算。

  • key_len:

    key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的:

    • 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。
    • 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。
    • 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

  • ref

    当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、 unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的是什么。 索引示例 可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数。

  • rows

    如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行 数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

  • filtered

    对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered值。 索引示例 从执行计划中可以看出来，查询优化器打算把 s1 当作驱动表， s2 当作被驱动表。我们可以看到驱动表 s1 表的 执行计划的 rows 列为 9688 ， filtered 列为 10.00 ，这意味着驱动表 s1 的扇出值就是 9688 × 10.00% = 968.8 ，这说明还要对被驱动表执行大约 968 次查询。

  • extra

    Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何 执行给定的查询语句。常见的信息有：

    • Using index

    当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息。

    • Using index condition

    如果在查询语句的执行过程中将要使用 索引条件下推 这个特性，在 Extra 列中将会显示 Using index condition。

    • Using where

    当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在Extra 列中会提示上述额外信息。

    • Using intersect(…) 、 Using union(…) 和 Using sort_union(…)

    如果执行计划的 Extra 列出现了 Using intersect(…) 提示，说明准备使用 Intersect 索引合并的方式执 行查询，括号中的 … 表示需要进行索引合并的索引名称;如果出现了 Using union(…) 提示，说明准备 使用 Union 索引合并的方式执行查询;出现了 Using sort_union(…) 提示，说明准备使用 Sort-Union 索 引合并的方式执行查询。

    • Using filesort

    如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的 Extra 列中显示 Using filesort 提示。

    • Using temporary

    在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在 执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划 的 Extra 列将会显示 Using temporary 提示。

实际案例

假设存在一下查询计划： 索引示例 发现使用了文件sql查询过程中使用了文件索引。此时如果查询的记录非常多，使用file sort的方式很费性能。我们可以考虑将文件排序改为使用索引进行排序。例如： 索引示例 这个查询语句可以利用 idx_key1 索引直接取出 key1 列的10条记录，然后再进行回表操作就好了。

再比如存在一下查询计划： 索引示例 上述查询中的搜索列表中只有 key_part2 一个列，而且搜索条件中也只有 key_part3一个列，这两个列又恰 好包含在 idx_key_part 这个索引中，可是搜索条件 key_part3 不能直接使用该索引进行 ref 或者 range 方 式的访问，只能扫描整个 idx_key_part 索引的记录，所以查询计划的type列的值就是index 。 如果我们希望加速这个查询过程可以建立索引（key_part3，key_part2 ）。这里需要注意的是可能使用的索引越多，查询优化器计算查询成 本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

常用的性能相关参数

MySQL与性能相关参数非常多，参数越细致代表可以控制的地方越多，随之带来也就是复杂度越高，掌控难度越大。篇幅的原因，不能详解每个性能参数，再一个性能参数没有银弹，也没有通用的参数调整表，都是要根据当前的资源（硬件）的配置，不同的业务场景还有使用方式等进行case by case的调优化，本章主要讲讲优化的思路以及介绍部份优化参数。

可以简单的把MySQL的优化分为几大类，内存层、IO层以及SQL层。

内存层

数据库之所以能够快速度返回SQL所需要的数据，一个很大的因素是通过内存进行数据缓存，也就是MySQL的Buffer_pool。MySQL实例可以通过参数innodb_buffer_pool_size来控制InnoDB Buffer Pool的大小，例如：

1

innodb_buffer_pool_size=8G

目前通过较为优秀的管理手段，Buffer_pool的内存可以占用到整个MySQL实例内存的75%。MySQL实例规格越大，可以缓存的数据越多，但需要的成本也越高。所以为了在成本与性能之间取得平衡，就需要合理的设置Buffer_pool。首先要判断当前Buffer_pool大小是否合适，这里主要是通过内存命中率来判断。数据库的所有数据，都是先从磁盘读取到Buffer_pool，再返回到用户，越热的数据，驻留在Buffer_pool的时间越长，所以简单来说内存命中率就是统计一段时间内，SQL请求的数据块有多少是直接在Buffer_pool返回，有少是需要从磁盘读取上来之后再返回，稳定运行态的数据库，从buffer_pool上返回的数据越多，内存命中率越高，统计公式如下： 索引示例

  • Innodb_buffer_pool_read_requests : 表示从缓冲池中读取页的次数
  • Innodb_buffer_pool_reads : 表示从物理磁盘读取页的次数

这些状态值可以通过show global status 获取；

通常较为健康的内存命中率应该保持到98%以上，如果低于此值侧需要检查当前使用是否合理或者进行实例规格扩容。

若如果hit长期是在100%的时候，侧需要检查是否存在内存浪费，分配的Buffer_pool可能用不完。我们可以通过show engine innodb status或者查询information_schema.innodb_buffer_pool_status表来查看MySQL的内存监控信息： 索引示例

里面要留意的是Free buffers 这个值，其表示没使用内存块的个数，一个内存块大小等于MySQL数据块（page）的大小，默认为16K，如果Free buffers * 16K大于buffer_pool的10%，同时数据库整体负载不高的情况下，就可以考虑选择更小规格的MySQL实例，避免成本的浪费。

另外由于MySQL数据存放的机制，delete等操作会造成大量的空间空洞碎片，一个数据块存储的有效行数不多，所以我们还可以通过定期optimaze table的方式来收缩表，重新组织数据的排布，让MySQL存放的数据更紧密，一来可以减少表对物理磁盘空间的使用，同时也可以提高Buffer_pool的利用率。

从MySQL 5.7开始，对于buffer_pool的大小支持动态调整，但这里面有较大的风险，调高太多有可能导致MySQL其他要用的内存不足而引起OOM。调低buffer_pool侧需要把当前部份缓存的数据刷出内存，有可能会导致实例hang住，所以非必要的情况，不太建议直接在线调整buffer_pool大小。

IO层

MySQL对IO控制的参数也比较多，以下列举几个关键的并且可动态调整的IO参数：

  • innodb_io_capacity & innodb_io_capacity_max

    innodb_io_capacity用于控制MySQL InnoDB后台任务每秒可用的IO次数，其中后台任务包括数据块的刷脏页、写redo、写binlog以及写临时表等一切IO操作。设置太低会限制MySQL的性能，但设置太高又会导致IO阻塞，这一切都取决于主机的IOPS能力。MySQL官方建议若使用SSD磁盘或者有多个HDD磁盘组成的RAID，可以使用innodb_io_capacity的默认值2000。但对于目前主流硬件来说，还是太低。目前主流存储介质多使用PCIe NVMe SSD卡，其各种能力都是数倍于SATA SSD盘，例如： 索引示例 索引示例 主流PCIe 3.0 NVMe 的随机写已达到100K，最新的PCIe 5.0 NVMe更是高达250K，所以对于新的硬件需要进行参数调配以发挥其最大性能，同时我们的机器上都是装有多个NVMe盘的，若把多个NVMe盘组成LVM并画好条带再提供使用，IOPS能力可以继续翻倍。

    当确认好主机的配置，并做到16K写的IO测试，我们可以适当调整innodb_io_capacity的值，若使用NVMe盘，并且独占主机的情况下，建议innodb_io_capacity=MAX IOPS的50%。多实例共用主机的情况，可以按实例个数按比例调小。

    对于innodb_io_capacity_max，其目的是在大写入压力下，最高可达到的写入能力。官方建议一般为innodb_io_capacity的两倍，但为保证MySQL不受系统其他应用的IO能力影响，建议最大调整为MAX IOPS的80%。例如：

    1
2

    innodb_io_capacity=50000
innodb_io_capacity_max=80000

  • innodb_flush_log_at_trx_commit

    innodb_flush_log_at_trx_commit参数用于控制事务提交时，MySQL对日志落盘的不同操作方式。其可以设置的值为 0，1，2。此参数作为数据库严格遵守ACID特性与高性能之间的取舍，必要时可以临时牺牲ACID特性提高性能。 索引示例

    1. 设置为0时：当事务commit时，MySQL把数据写到log_buffer之后就返回成功。之后再由MySQL内部的机制每秒批量把log_buffer的内容通过fsync()的方式写入磁盘。若此时MySQL进程异常crash，则最多可能会丢1秒已提交的数据。
    2. 设置为1时：此值为innodb引擎的默认值，当事务commit时，为保证redo日志不丢失，会直接调用fsync()确保日志写入磁盘后再返回成功。若出现宕机等情况均会导致提交失败，不会出现数据错误。
    3. 设置为2时：当事务commit后，会要求日志必须写入OS的buffer再返回成功，至于数据的实际落盘操作则为OS层每秒调用fsync完成。

从性能方面看，设置为0和2时，基本是接近的。其最大区别是，若设置为0时，无论是宕机还是MySQL进程crash都有丢数据的风险；设置为2时，哪怕MySQLcrash只要主机不宕机，不存在丢数据的风险，相对来说会比设置0多一层保险。

  • sync_binlog

    MySQL事务控制除了redo之外还有一个binlog日志，用于MySQL的原生主从数据同步，所以为保证主从之间的数据一致性，在主库事务提交时，除了要保证写入redo，还要保证写入binlog，两者的关系就造成了MySQL的二阶段提交： 索引示例 sync_binlog可设置的值为0至4294967295

    当设置为0时，binlog不强制落盘，写入到OS的文件系统cache即返回成功，什么时候落盘由OS自己控制，此时可以获取最大性能。但如果这时候宕机，binlog会产生丢失。

    当设置为1时，也是MySQL的默认值，binlog的写入严格保证落盘。此设置加上innodb_flush_log_at_trx_commit=1，就是常说的双1设置，可以严格保证已提交的数据不会丢失。

    设置为N时，MySQL会起个计数次，当收集到N次binlog提交的事务之后，调用一次fsync()把binlog保证落盘。此值在TPS>N的情况下会比设置为0多一点点保护，但同样也无法保证数据完全不丢失。

    结合innodb_flush_log_at_trx_commit和sync_binlog两个参数，当系统TPS压力大，而且出现了磁盘性能瓶颈，可以设置2，0或者是双0来临时提高性能，按之前的经验值来看，设置后写入性能会有2到5倍的提升，等待业务高峰过后再调整为双1。对于金融类的业务场景，建议是一直保持双1的设置。

SQL层

其实通过SQL层的参数优化是比较难达到理想结果的，主要的效果还得通过优化SQL或者索引结构来达成，以下只是简单列举几个可以优化场景及其对应的参数：

  • 排序及聚合相关参数优化

    当无法通过索引天然的有序性进行排序时，例如：

    1

    SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

    这时候就会使用到sort_buffer_size分配的空间进行排序，如果返回的数据比较多，sort_buffer无法全部装载要排序的内容，则会把数据写入到外部的临时文件做转存，再通过分批读入sort_buffer进行排序，若发生此种情况，也可以show global status里看到sort_merge_passes值在增加。所以通过增加sort_buffer_size的分配，减少需要通过文件排序的可能性，可以提高order by 及group by 的速度。其默认值是256K，可以是全局设置，也可以在session级设置。一般建议只在session级别设置，并且最好不要超过2M，太大的设置容易导致OOM的风险。另外当真的发生了文件排序，除了加大sort_buffer_size可以提升排序速度外，还可以通过增加read_rnd_buffer_size的大小，以提高一次性可以从sortfile里读取更多的内容，同样的read_rnd_buffer_size也只建议在session级别设置，并不能设置过大。

  • 小批量频繁DML优化

    前面也提到，在做DML时，MySQL需要记录binlog，那binlog的产生首先会记录在一个线程级的内存空间，并通过binlog_cache_size控制，其默认值是32K。当事务产生的binlog超过32K，会把binlog内容转存到一个外部的临时文件（临时文件的大小受max_binlog_cache_size限制，超过限制后事务会失败），当事务提交后，再把临时文件的所有binlog真正写入binlog文件。若经常有小批量的频繁DML操作，而且表里单行内容长度较大，可以适当加大binlog_cache_size，以避免通过磁盘文件来转存从而提升DML速度。但binlog_cache_size是全局型参数，一旦加大，所有要执行DML操作的会话都会分配同样的内存，所以经验值建议不超过1M，避免造成大量内存消耗。

典型慢查询及优化

SQL优化的核心思想，就是通过改变SQL的写法或者表、索引的结构，来减少执行SQL所需要的逻辑读。这里的逻辑读，是指为查找SQL要返回的数据时，所需要访问的内存块数量。较有明显的case就是例如：

1

select * from tab1 where user_id in ('aaa','bbb','ccc');

当 tab1表没有索引的时，为了返回数据，就需要进行对tab1表全表扫描，那当表很大的时候，16K一个数据块，那这次访问的逻辑读就是巨大的，若user_id有索引，使用二级索引定位加回表访问，通常情况下只需要10个逻辑读以内就能完成。所以如何减少逻辑读才是优化的关键。

下次说几个减少逻辑读的案例：

数据存储优化

索引示例

MySQL是的通过row模式存储数据，我们通常一个数据块的大小是16K，除了MySQL自身块管理要占用一定的空间，里面90%以上的空间可以用于实际数据的存储，如果单行长度较小，那一块就可以存放更多的行。但往往为了方便我们一个表上有很多字段，甚至把一部份非高频使用的字段也加入其中，例如memo等，如果这些非高频使用的字段占用大量空间，就会导致一个块能存储的行数减少，访问多行就要更多的逻辑读才有完成。所以在追求更高性能的情况下，我们可以把表进行拆分，高频字段放一个表，非高频的放另一个表，需要时再进行两表关联。

拆表还有一个好处是，在更新的时候可以减少 binlog日志的大小，由于目前binlog都是row模式，就是会严格记录一个行记录的前后镜像，哪怕你只更新一个时间截，都会把完整的行信息被写到binlog导致日志过大降低性能，特别是当表里还有text、blob、clob等字段时，更是一种灾难。所以我们在表结构设计时要合理考虑不同字段的访问频率，通过高低频字段拆表的方式来提升性能。

索引并不是越多越好

索引示例

上图是一个反面教材，估计当时的很简单，就是希望各种SQL的where条件都能走上索引，但数据库不是搜索引擎，这样的建索引方式对性能其实影响很大，主要有几个问题：

  • 写放大问题
    • 当执行DML语句时，除了维护正常的表数据之外，还要维护索引数据，索引越多，要维护的数据就越多，那么就会造成更多的写入大问题。
    • 另外索引为了维护其自身的有序性，索引的更新是先删除旧值再在新的排序位置插入数据，这里也很容易造成空间碎片和索引块分裂。索引块分裂是新插入的位置不够空间，需要把原来块的内容分成两个块存放，并更新分枝块，这将进一步加剧写放大问题。

  • possible_key问题

    1
2
3
4
5

    +----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows  | Extra                              |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
|  1 | SIMPLE      | t     | range | a,b           | b   | 5       | NULL | 50128 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
    • MySQL的优化器相对于商业数据库来说还处于较为初级的阶段，每次SQL执行时都会重新生成执行计划，而在生成执行计划时，如果有多个索引可被使用时，这些索引就会进入possible_key，优化器会对每一个possible_key进行采样并计算数据分布情况，再根据计算后的结果选择最终使用的索引(key)去执行。这样带来的问题就是，对possible_key的采样计算是要额外消耗更多的逻辑读和CPU的，possible_key越多，额外开销越多，性能更差。
    • 所以我们要尽可能避免重复索引的创建；当where有多个条件时，尽可能的使用组合索引，而不是每个条件都创建单列索引，避免产生更多的possible_key。

分页优化

分页查询越到后面，速度越慢，那主流的分页优化有：

  • 关联优化
    • 原SQL：
    1

    select something from order_table where user_id='1234' order by id limit 23000,100;
    • 我们可以改写为：
    1
2
3
4
5

    select something 
From table a,
(select id from order_table where user_id ='1234' order by id limit 23000,100) b
Where a.id=b.id
Order by a.id;
  • 程序缓存前次最大id
    • 当我们对面类似按时间排序分页时，同时我们也有自增的id，那我们可以通过缓存上一次的最大ID再做分页加速。
    • 例如
    1

    select id, something from tab1 order by gmt_create limit 200,10;
    • 当返回数据时，我们可以在应用端记录此次返回记录的最大id值，假如是200，那请求下一次时，就可以把SQL改写为：
    1

    select id , something from tab1 where id > 200 order by gmt_create limit 10;

业务逻辑优化

除了在SQL本身优化之外，我们对于一些业务逻辑，也可以适当的做些优化 索引示例

这种显示总条数以及具体有多少页的逻辑，总行数要执行count(*)操作，当数据较多时，这个SQL也不会快，再上每次打开或者下一页都要重新执行，成本很高，用户体验也较差，而且对于绝大部份场景来说，客户对这些数据并不敏感。

如果业务允许，我们可以做成以下修改，当数据量少时，精确显示，当数据超过一定值后，我们用1000+等方式进行模糊展示，减少不必要的逻辑读。

原SQL：

1

select count(*) from table;

修改后:

1

select count(*) from (select id from table limit 1000);

再在前端做个判断，当返值到1000时，显示为1000+。

此方式也可适合于消息盒子之类的应用场景。

MRR 能够提升性能的核心在于，查询语句在索引上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

CATALOG
    FEATURED TAGS
    redis go mysql algorithm distribute jvm network spring java kafka mongo mq concurrent index design_pattern io rocket_mq store netty os concurrency nosql mesh etcd graph hbase kitex lang zookeeper
    FRIENDS