我对 SQL 优化的一点经验

我对 SQL 优化的一点经验

以 Mysql Innerdb 存储引擎为例，从 B+Tree 存储特性、执行计划、查询优化、架构优化等几方面列举对 SQL 优化的一点经验

Innerdb B+Tree

假设有以下表，表中一共有6条数据和2个索引，其中 ID 为主键索引（聚集索引），Name 为普通索引（非聚集索引）

+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 张三   | 男   |
|  2 | 李四   | 男   |
|  3 | 王五   | 男   |
|  4 | 赵六   | 女   |
|  5 | 许七   | 女   |
|  6 | 刘八   | 男   |
+----+--------+------+

Innodb主键索引为聚集索引，聚集索引指以主键索引来聚集组织数据的存储，看看 Innodb 是如何组织数据的

在主键索引中，非叶子节点存储主键 + 指针，叶子节点存储真实的数据，在通过主键索引检索的时候会直接命中叶子节点，然后从叶子节点中把数据取出来，其查询过程如下：

当查询 id = 1 的数据时：

主键索引叶子节点存储了数据，那么普通二级索引呢？

在二级索引中，非叶子结点存储的索引 + 指针，叶子节点存储的是主键ID，并没有存储数据

当查询 name = ‘张三’ 时：

第一步

• 先通过二级索引查询出来张三的主键ID，得到 ID = 1，Name = 张三

第二步

• 得到主键ID后在主键索引中根据查询数据，得到 ID = 1，Name = 张三，Sex = 男

主键索引和二级索引的存储关系为

因此主键索引比二级索引的效率高，应该尽量使用主键索引作为查询条件

这里有个疑问，在第一步根据二级索引查询主键ID时，已经得到了 ID = 1（索引数据）和 Name = 张三（索引值） 的数据了，为什么还要执行第二步？其实这个跟 select 语句需要查询的数据列有关，例如：

当查询的数据列为

select name, sex from table where name = '张三'

由于二级索引中没有 sex 字段的数据，因此这个查询会先通过第一步得到主键ID再通过第二步得到 name，sex 数据，这种情况叫做回表

当查询的数据列为

select name from table where name like '张%'

由于二级索引中已经存储了查询列所需的 name 字段，因此这个查询执行第一步查找，相比查询 name, sex 少了一次查询，在 innerdb 中这个叫**覆盖索引，**就是 select 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

因此查询数据列应该优先使用覆盖索引，尽量避免使用 select *

一个节点能存多少数据

如果主键索引的叶子节点存储的是数据，非叶子节点存储的是指针，那么一个节点能存多少数据呢？答案是一页，页是mysql的单位，在 Innerdb 中一页默认的大小是 16k，使用以下命令可以查询页大小。

SHOW GLOBAL STATUS like 'Innodb_page_size'

一页又能存多少数据？如果是叶子节点假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；如果是非叶子节点，假设主键ID为 bigint 类型，那么长度为8B，指针大小为6B，一共就是14B，那么一页里就可以存储 16 * 1024 / 14 = 1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为 1170(主键+指针) * 16(每个节点存储16条) = 18720条，一颗高度为3的B+树能存储的数据为 1170 * 1170 * 16 = 21902400（千万级条），因此在 Innerdb 中 B+树高度一般为1-3层，通过主键索引查询通常只需要3次以下的 io 就能查到数据。

因此在设计表结构时需要控制字段长度，字段越大每页能存储的数据就越少，查询所需要的 io 次数就越多

执行计划

mysql 使用 explain sql 查看执行计划，熟悉执行计划可以合理的去创建索引优化查询

EXPLAIN 
select name from aaa where name like '张三%'

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | aaa   | NULL       | range | idx_name      | idx_name | 83      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

• id：id包含一组数字，表示查询中执行SELECT子句或操作表的顺序
• select_type：表示查询中每个SELECT子句的类型(是简单还是复杂)
• type：type表示MySQL在表中找到所需行的方式，又称“访问类型”，常见的类型如下：**

+-----+-------+-------+-----+------+----------------+------+
| ALL | index | range | ref | eq_ref | const,system | NULL |
+-----+-------+-------+-----+-----------------------+------+

以下类型，从上到下，性能从最差到最好

• 1）ALL：Full Table Scan，MySQL将遍历全表以找到匹配的行。

• 2）index：Full Index Scan，index与ALL区别为index类型只遍历索引树，如 select id from table

• 3）range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

• 4）ref：非唯一性索引扫描，将返回匹配某个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找

• 5）eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

• 6）const、system：当MySQL对查询的某部分进行优化，并转换为一个常量时，可使用这些类型进行访问

• 7）NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

当执行计划 type 中出现 ALL、index 时一般需要优化

• possible_keys：possible_keys将指出MySQL能使用哪个索引在表中找到行，查询涉及的字段上若存在索引，则该索引将被列出，但不一定
• key：key将显示MySQL在查询中实际使用到的索引，若没有使用索引，则显示为NULL。查询中若使用了覆盖索引，则该索引仅
• 出现在key列表中
• key_len：key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• ref：ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
• rows：rows表示MySQL根据表统计信息及索引选用的情况，估算地找到所需的记录所需要读取的行数
• Extra：Extra包含不适合在其他列中显示但十分重要的额外信息。常见类型如下：**

+-------------+-------------+-----------------+----------------+
| Using index | Using where | Using temporary | Using filesort |
+-------------+-------------+-----------------+----------------+

• 1）Using index。该值表示相应的SELECT操作中使用了覆盖索引
• 2）Using where。该值表示MySQL服务器在存储引擎收到记录后进行“后过滤”
• 3）Using temporary。该值表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
• 4）Using filesort。Using filesort即文件排序，MySQL中将无法利用索引完成的排序操作称为“文件排序”，利用索引进行排序，则可以优化掉文件排序

当执行计划 Extra 中出现 Using filesort、Using temporary 时一般需要优化

查询优化

优先使用主健索引查询

在 Innerdb 中，主键索引叶子节点存储的是数据，二级索引存储的是主健ID，使用二级索引查询数据会先找到主键ID，然后在通过主键索引查询数据，相比主键索引二级索引多了一次查找

尽量使用覆盖索引

覆盖索引，即当索引本身包含查询所需全部数据时，不再访问数据文件本身，减少不必要的 io，控制 select 所需的数据列

主键长度不宜过长

主键长度越长每页能存储的数据就越少，一次 io 能够加载的数据就会越少

查询条件不要出现强制类型转换

强制类型转换不能命中索引，如 name 为 varchar 类型时，使用下面 sql 无法命中索引

select * from table where name = 张三

查询条件不要使用函数

使用函数不能命中索引，如

select ... where year(date) > 2020

不能命中 date 索引，可以更改为

select ... where date_timestamp > 1577808000000

使用 hash 索引

在明确不会使用范围查询的情况下可以使用 hash 索引，hash 索引在等值比较方面比 B+Tree 索引效率高

大字段尽量独立建表

字段越长每页能存储的数据就越少，一次 io 能够加载的数据就会越少，text、longtext 等大字段尽量作为拓展表独立存储，在查询主表时不会因为拓展表数据过大影响性能

大表分页查询先定位 id 在分页

MySQL 并不是跳过 offset 行，而是取 offset + N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下

注意索引的有序性

在使用 where、 order by、group by 的场景中需要注意联合索引的顺序，对于如下联合索引

create index idx_a_b on table(a, b)

这么使用不会走索引

where b = ? and a = ?

应该改为

where a = ? and b = ?

最左前缀匹配

在使用模糊查询 like ‘%str’ 或者 like ‘%str%’ 时不会使用索引，只用当使用 like ‘str%’ 时才会使用索引，原因很简单，B+Tree 总是从左边到右边依次对比，如果要查找内容的左边无法确定，那就无法走索引，只能全表扫描了

把计算放到应用层

只从数据库中查询数据，对数据的计算和处理放到应用层来做，比如在查询中使用了md5、substr等函数对数据的处理可以放到应用层中去做。

架构优化

使用全文检索系统满足多条件过滤和检索场景

在 mysql 表数据量比较大的情况下尽量不要使用3张以上的 join 和多维度的条件查询，这种情况基本都是根据多条件对数据进行过滤和全文检索，应该使用更有优势的 ES、Solr 等全文检索系统

使用分库分表满足超大表查询

上面说到的通常来说 Innerdb 中 B+树高度一般为1-3层，通过主键索引查询通常只需要3次以下的 io 就能从千万级的表中查到数据，但当数据量比较大且查询条件比较多的时候，io 次数会急剧增加导致影响查询性能，因此应该采用垂直或者水平的方式来进行分库分表来缓解大表带来的查询压力

使用 NewSQL

mysql 不是分布式架构，无法进行水平拓展，一般的水平拓展是基于读写分离的主从架构且有局限性，因此根据特定场景可以采用 NewSQL 方案，如 TiDB

独享数据库模式

在微服务架构中，每个服务应该是独享数据库模式（非独享实例）避免跨库 join 查询，对 mysql 的查询应该是简单高效的 sql 语句，不同微服务中的数据应该采用接口的方式交换而非直接跨库查询，在架构上也可以实现随时对压力过大的服务进行独享实例的拆分

性能监控报警

在应用程序中应该对慢 sql 进行监控和报警，不是所有的 sql 一开始都是慢 sql，随着时间的推移和数据不断膨胀，具有性能瓶颈的 sql 会逐渐浮现，慢 sql 可以通过调用链日志、Druid 连接池慢日志等方式收集用以写入到监控系统

参考

• 《MySQL DBA修炼之道》
• 数据结构在线画图