一、SQL 调优基础概念

（一）索引的重要性与结构

在 SQL 性能优化中，索引起着至关重要的作用。它可以大大提高数据库查询的效率，减少查询时间和资源消耗。MySQL 中索引一般使用 B + 树结构，这是有其深刻原因的。

B + 树是一种平衡的多路查找树，具有很多优点。首先，B + 树的非叶子节点只存储键值，不存储真实数据，这样可以在每个节点存储更多的键值，从而降低树的高度。较低的树高度意味着在进行查询时需要的磁盘 I/O 次数更少，从而提高查询速度。例如，假设一个叶子节点可以存放 100 条记录，存放目录项的节点可以放 1000 条记录，那么 B + 树有 3 层时，最多可放 100×100×100 = 1000000 条记录。这样在查询数据时，最多可能只需要进行 3 次磁盘 IO 操作（2 个目录项页和 1 个用户记录页），大大提高了查询效率。

在数据存储方式上，B + 树的所有数据都存储在叶子节点，非叶子节点仅存储键值和子节点的指针。叶子节点之间通过指针相互连接，形成一个有序链表，便于范围查询。例如，当需要检索所有年龄在 20 到 30 岁之间的用户时，B + 树可以快速定位到这个范围内的叶子节点，然后通过链表依次遍历这些节点，高效地完成范围查询。

此外，InnoDB 存储引擎中，B + 树作为数据库索引结构，可以显著提高数据检索的速度。它允许数据库快速定位到数据所在的磁盘块。在 InnoDB 中，每个表都有一个聚簇索引，基于主键构建，如果没有显式指定主键，则基于第一列（不包含 NULL 值）构建。二级索引的非叶子节点存储的是键值和对应的主键值，叶子节点存储的是主键值和指向聚集索引的指针。这样，通过二级索引可以快速定位到主键，然后再通过主键在聚集索引中检索到完整的数据记录。

二、SQL 调优具体方法

（一）避免全表扫描

在 SQL 查询中，为了提高查询效率，应尽量避免全表扫描。首先考虑在 where 及 order by 涉及的列上建立索引。例如，当查询一个包含大量数据的表时，如果没有在合适的列上建立索引，数据库将不得不进行全表扫描，这会消耗大量的时间和资源。假设一个拥有百万条记录的表，如果没有索引，查询可能需要数秒甚至更长时间，而通过在常用查询条件的列上建立索引，可以将查询时间缩短到毫秒级别。

（二）慎用特殊操作符

在 where 子句中应避免使用 !=、<>、or、is null、in 和 not in 等操作符，因为这些操作符可能会导致索引失效，从而引发全表扫描。例如，使用 != 或 <> 操作符时，即使比较的字段上有索引，SQL 中也会限制索引，引起全表扫描。对于 is null 查询，在 Oracle 9i 中，查询字段 is null 时单索引失效，引起全表扫描。一般数据量大的表不要用 is null 查询。而 or 语句使用不当也会引起全表扫描，当 where 子句中比较的两个条件，一个有索引，一个没索引，使用 or 则会引起全表扫描。in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in。

（三）避免函数和表达式操作

在 where 子句中避免对字段进行函数和表达式操作，以免引擎放弃索引进行全表扫描。例如，select id from t where num/2 = 100 应改为 select id from t where num = 100*2；select id from t where substring(name,1,3) = 'abc' 应改为 select id from t where name like 'abc%'。因为数据库在处理查询时，对列进行函数或表达式操作会导致无法使用索引，从而不得不进行全表扫描。

（四）合理使用索引字段

使用索引字段作为条件时，若为复合索引需注意第一个字段的使用及字段顺序一致性。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。同时，应尽可能的让字段顺序与索引顺序相一致，因为在 MySQL 5.6 官方文档中提到，索引的目的是允许在一个大表中查找特定的值或值的范围，从而在实际操作时避免全表扫描。如果索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

（五）存在替代方案的选择

在某些情况下，可以使用替代方案来提高查询效率。例如，很多时候用 exists 代替 in 是一个好的选择。select num from a where num in(select num from b) 可以用 select num from a where exists(select 1 from b where num = a.num) 替换。对于 or 操作，可以通过把不等于操作符改成 or 的情况，改成 column<’aaa’ or column>’aaa’，就可以使用索引了，避免全表扫描。对于模糊查询，左模糊 like‘%...’ 无法直接使用索引，但可以利用 reverse + function index 的形式，变化成 like ‘…%’；全模糊是无法优化的，一定要的话考虑用搜索引擎。

（六）控制索引数量

索引虽然可以提高 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。所以，一个表的索引数不宜过多，最好不要超过 6 个。如果索引太多，在进行数据插入或更新操作时，会耗费大量的时间和资源来维护索引，从而降低数据库的性能。

（七）字段类型选择

尽量使用数字型和 varchar 类型字段，避免使用 char。数字型字段在查询和连接时性能更高，因为引擎在处理查询和连接时，对于数字型而言只需要比较一次就够了，而对于字符型，会逐个比较字符串中每一个字符。同时，尽可能的使用 varchar/nvarchar 代替 char/nchar，因为变长字段存储空间小，可以节省存储空间，并且在一个相对较小的字段内搜索效率显然要高些。

（八）避免不必要的查询和返回

不使用 select *，避免写无意义查询和返回用不到的字段，减少资源消耗。任何地方都不要使用 select * from t，用具体的字段列表代替 “*”，不要返回用不到的任何字段。例如，如果只需要查询表中的几个特定字段，使用 select field1, field2 from t 而不是 select * from t，这样可以减少数据传输量和查询时间。同时，不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0，这类代码不会返回任何结果集，但是会消耗系统资源，应改成 create table #t(...)。

（九）临时表的使用

临时表在特定场景下可以提高查询效率，但要注意使用场景和避免频繁创建和删除。临时表分为本地临时表和全局临时表，本地临时表只能在当前查询页面使用，全局临时表在所有查询页面均可使用。临时表的优化一般使用在子查询较多的情况下，也称为嵌套查询。例如，在一个比较简单的两层嵌套子查询中，如果逻辑读取较高，可以将第一二层的查询结果插入到临时表中，然后从临时表中查询结果，这样可以成倍的减少逻辑读取次数，提高查询效率。但是要避免频繁创建和删除临时表，以减少系统表资源的消耗。

（十）游标使用需谨慎

游标效率较差，如果游标操作的数据超过一万行，那么就应该考虑改写。尽量寻找基于集的解决方案来解决问题，基于集的方法通常更有效。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括 “合计” 的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

（十一）大事务操作与数据量控制

尽量避免大事务操作，提高系统并发能力。避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。因为大事务操作会占用大量的系统资源，降低系统的并发性能。而向客户端返回大数据量会增加网络负担，降低查询速度。如果数据量过大，可以考虑分页查询或者只返回必要的字段，以提高系统性能。

三、高级 SQL 调优策略

（一）根据执行计划调整

通过 explain 命令可以查看 SQL 的执行计划，其中 type 字段显示了查询的访问类型，常见的有 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。一般来说，应尽量避免全表扫描，优先选择更高效的索引扫描或范围扫描。例如，如果 type 显示为 ALL，则需要考虑在相关列上创建索引或优化查询条件以减少数据量。

key 字段显示了实际使用的索引，如果为 NULL，则表示没有使用索引。例如，在查询条件中使用了合适的索引列，key 字段应该显示对应的索引名称。

Extra 字段提供了额外的信息，如 Using index 表示使用了覆盖索引，即查询只需要从索引中就能获取所需的数据，无需回表查询。如果出现 Using temporary，则表示使用了临时表，可能会影响性能，需要进一步分析原因。

（二）海量数据存储索引优化

B + 树在海量数据存储中具有显著优势。B + 树的非叶子节点只存储键值，不存储真实数据，这样可以在每个节点存储更多的键值，从而降低树的高度。例如，在处理海量数据时，较低的树高度意味着在进行查询时需要的磁盘 I/O 次数更少，从而提高查询速度。

在进行数据迁移时，需要注意关闭索引。如文章中提到，在 SQL Server 进行数据迁移时，有先建索引再全表插入数据、先插入数据再建索引、先建索引再批次插入数据三种方式。其中，先建索引再全表插入数据风险极高，整个事务大小级别可能非常恐怖，耗费大量系统资源；先插入数据再建索引性能较好，但风险点在于当表的数据量达到一定量级时，回滚成本较高；先建索引，再批次插入数据虽然性能可能不是最优，但风险小。所以，在进行数据迁移时，可以根据实际情况选择合适的方式，并且在适当的时候关闭索引，以提高迁移效率。

同时，要注意避免回表和索引覆盖等问题。如果查询只需要从索引中就能获取所需的数据，那么可以使用覆盖索引，避免回表查询带来的额外开销。

（三）下推更多计算至存储层

以 PolarDB-X 为例，将更多计算下推到存储层 MySQL 可以提高性能。可以通过调整表结构，如合理选择分区方式，来优化数据存储和查询。例如，PolarDB-X 中有 Hash 分区和 Range 分区两种方式。Hash 分区数据随机散落在不同分区，只要分区键的区分度高，数据一定能打散，但范围查询低效；Range 分区数据在分区内是连续的，范围查询高效，但可能会发生数据倾斜。

增加索引也可以提高下推计算的效率。在 PolarDB-X 中，外键的操作可以下推到存储层，从而达到提前过滤数据、减少网络传输、并行计算等目的。但外键下推与否是由表的形态决定的，下推外键会提升性能。当外键中涉及的表是分区表时，外键是不下推的，称为逻辑外键。

（四）执行计划调优

在统计信息存在缺失或误差时，可以通过 Hint 干预优化器行为，生成更好的执行计划。例如，在 GaussDB 200 中，如果统计信息陈旧或缺失，往往会造成执行计划严重劣化，从而导致性能问题。大约 10% 左右的性能问题都是没有收集统计信息导致的。

在这种情况下，可以通过分析统计信息，查看是否存在计算偏斜着手。如果同一个执行算子在各个 DataNode 上执行的时间差距较大，我们就可以认为存在计算倾斜。可以通过 SQL 改写、guc 参数干预执行计划等手段来调整执行计划，提高性能。同时，在 PolarDB-X 中，也可以通过 SQL 改写等方式实现执行计划下推，避免计算资源不能充分利用的问题。