个人中心
退出
只要心中有 B-树，SQL 优化就不怵！
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
为什么存在性能问题？
为什么是 B-树索引？
优化查询条件
等值查找
复合条件
范围查找
LIKE 运算符
使用函数
空值查找
多表连接查询
排序和分组
DML
总结
大家好，我是只谈技术不剪发的 Tony 老师。SQL 语句优化是数据库优化的重要内容；无论开发人员还是 DBA，在工作中都不可避免需要解决一些 SQL 性能问题。
SQL 优化的方法有多种，其中最有效、最常用的方法就是索引（Indexing）。因此，我们就来详细讨论一下索引（B-树）的原理，以及如何利用索引编写高性能的 SQL 语句。
本文内容适用于各种数据库，包括但不限于 MySQL、Oracle、SQL Server 、PostgreSQL 以及 SQLite。
为什么存在性能问题？
由于 SQL 是一种关注结果（what）的语言，具体过程（how）由数据库系统来实现，这就往往导致我们容易编写出低性能的 SQL 语句。我们先来看看数据库为什么存在性能问题。
目前，数据库管理系统主要还是使用磁盘作为数据的存储介质；因为磁盘的访问性能虽然比内存慢几个数量级，但是具有价格便宜、大容量和断电不丢失的特性。对于交易类型的数据库（OLTP），一般都是小型的事务，操作很少的随机数据，但是并发很高。这种 IO 密集型数据库操作使用磁盘存储数据文件时存在两个问题：
直接查找数据时，每次都需要扫描表中的全部数据；
随着数据量的增长，扫描磁盘数据的性能急剧下降。
下面是一个简单的查询语句，查找 emp_id 等于 5 的员工（数据来源）：
select *
from employee
where emp_name = '张三';
那么数据库如何找到我们需要的数据呢？如果没有索引，那就只能扫描整个 employee 表，然后依次判断 emp_name 字段是否等于“张三”并返回满足条件的数据。这种方式一个最大的问题就是当数据量逐渐增加时，全表扫描的性能也就随之明显下降。
因此，数据库的性能优化首先就是需要减少磁盘的访问；其中主要的方法就是利用索引技术。
📝另一个减少磁盘 IO 的常用方法就是利用内存进行数据缓存，在数据库中称为缓冲区（Buffer Pool、Buffer Cache）。
那么为什么索引能够提高查询的性能？为什么几乎所有的数据库默认使用 B-树索引（B+树、B*树）？这就需要先来了解一下 B-树。
为什么是 B-树索引？
为了解决查询的性能问题，数据库引入了一个新的数据结构：索引。索引就像书籍后面的关键字索引，按照关键字进行排序，并且提供了指向具体内容的页码。如果我们在 id 字段上创建了索引（例如 B-树索引），数据库查找的过程大概如下图所示：
B-树（平衡树、Balanced Tree）索引就像是一棵倒立的树，其中的节点按照顺序进行组织；节点左侧的数据都小于该节点的值，节点右侧的数据都大于节点的值。
数据库查找时，首先通过索引找到工号为 5 的节点，再通过该节点上的指针（通常是数据的物理地址）访问数据所在的磁盘位置。举例来说，假设每个索引分支节点可以存储 100 个记录，100 万（1003）条记录只需要 3 层 B-树即可完成索引。通过索引查找数据时需要读取 3 次索引数据（每次磁盘 IO 读取整个分支节点），加上 1 次磁盘 IO 读取数据即可得到查询结果。
相反，如果采用全表扫描，需要执行的磁盘 IO 可能高出几个数量级。当数据量增加到 1 亿时，B-树索引只需要再增加 1 次索引 IO 即可；而全表扫描则需要再增加几个数量级的 IO。
📝全表扫描并不一定比索引查找更慢，当表中的记录较少，或者查询需要返回表中大部分的数据时，直接通过全表扫描可能更快。
不仅如此，数据库在实现上还对 B-树索引进行了改进，在索引节点之间增加互相连接的指针（B+树、B*树），能够提供更好的范围查询性能。例如，以下是 Oracle 中的 B*树索引示意图：
其中，叶子节点之间存储了双向链表。
聚集索引与非聚集索引
了解 MySQL 的同学一定听说过聚集索引（Clustered index）和非聚集索引。聚集索引将表中的数据按照索引的结构（通常是主键）进行存储；也就是说，索引的叶子节点中直接存储了表的数据。聚集索引的结构如下图所示：
聚集索引其实是一种特殊的表，MySQL（InnoDB）和 SQL Server 将这种结构的表称为聚集索引，Oracle 中称为索引组织表（IOT）。这种索引方式下，通过主键查找数据非常快。
非聚集索引就是普通的索引，索引的叶子节点中存储了指向数据所在磁盘位置的指针，数据在磁盘上随机分布。MySQL（InnoDB）称之为二级索引（Secondary index），叶子节点存储的是聚集索引的键值（主键）；通过二级索引查找时需要先找到相应的主键值，再通过主键索引查找数据。因此，创建聚集索引的主键字段越小，索引就越小；一般采用自增长的数字作为主键。
SQL Server 如果使用聚集索引创建表，非聚集索引的叶子节点存储的也是聚集索引的键值；否则，非聚集索引的叶子节点存储的是指向数据行的地址。
从具体实现来说，B-树索引可以被分为不同的类型：
唯一索引（UNIQUE）中的索引值必须唯一，可以确保被索引的数据不会重复，从而实现数据的唯一性约束。
非唯一索引允许被索引的字段存在重复值，仅仅用于提高查询的性能。
升序索引，按照索引数据升序排序的方式建立索引，通过索引返回数据可以避免额外的排序操作。
降序索引，按照索引数据降序排序的方式建立索引，通过索引返回数据可以避免额外的排序操作。
单列索引是基于单个字段创建的索引。例如，员工表的主键使用 emp_id 字段创建，就是一个单列索引。
多列索引是基于多个字段创建的索引，也叫复合索引。创建多列索引的时候需要注意字段的顺序，查询条件中最常出现的字段放在最前面，这样可以最大限度地利用索引优化查询的性能。
函数索引，基于函数或者表达式的值创建的索引。例如，员工的 email 不区分大小写并且唯一，可以基于 UPPER(email) 创建一个唯一的函数索引。
总而言之，索引是一个单独的数据库对象，需要占用额外的磁盘空间，用于存储索引结构以及被索引的数据。B-树（B+树、B*树）索引实现了稳定且快速的数据查找（O(log n) 对数时间复杂度），适用于 =、<、<=、>、>=、BETWEEN、IN 以及字符串的前向匹配（‘abc%’）查询的优化，因而成为了数据库主要的索引方式。
既然索引可以优化查询的性能，那么我们是不是遇到性能问题就创建一个新的索引，或者直接将所有字段都进行索引？显然并非如此，因为索引在提高查询速度的同时也需要付出一定的代价：
首先，索引需要占用磁盘空间。索引独立于数据而存在，过多的索引会导致占用大量的空间。
其次，进行 DML 操作时，也需要对索引进行维护；维护索引有时候比修改数据更加耗时。
一般来说，可以考虑为以下情况创建索引：
经常出现在 WHERE 条件或者 ORDER BY 中的字段创建索引，可以避免全表扫描和额外的排序操作；
多表连接查询的关联字段或者外键涉及的字段，可以避免全表扫描和外键级联操作导致的锁表；
查询中的 GROUP BY 分组操作字段。
📝索引一般通过create index语句创建。数据库通常会自动为主键和唯一约束增加一个唯一索引。但是除了 MySQL InnoDB 存储引擎之外，外键约束通常不会自动创建索引，不要忘记手动创建相应的索引。
接下来我们具体分析索引对不同查询操作的优化原理和注意事项。
优化查询条件
SQL 语句中的 WHERE 子句用于执行一个查询的过滤条件，只有满足条件的数据才需要返回。因此，这是索引发挥作用的主要途径；如果只需要返回很少记录，通过索引就可以快速找到这些数据。
等值查找
等值查找大概是我们最常见的 SQL 查询，例如上文中按照主键 id 查找员工的信息。我们可以通过执行计划查看数据库的实现过程，以下是该语句在 MySQL 中的执行计划：
-- MySQL 执行计划
explain
select *
from employee
where emp_id = 5;
id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra|
--|-----------|--------|----------|-----|-------------|-------|-------|-----|----|--------|-----|
1|SIMPLE |employee| |const|PRIMARY |PRIMARY|4 |const| 1| 100.0| |
结果显示，MySQL 通过主键进行查找（key = PRIMARY），并且最多返回一条记录（type= const）。
实际上这是通过索引唯一扫描（INDEX UNIQUE SCAN）找到唯一的索引项，然后直接读取数据（聚集索引），或者通过物理地址指针（非聚集索引）读取表中的数据。其他数据库对此也采用了类似的实现，这是一种非常快速的查找方式，而且几乎不会受到数据量增加的影响。
📝关于不同数据库中的执行计划的查看方式，可以参考这篇文章。
当查询条件上的索引非唯一时，情况有所不同。例如：
-- MySQL 执行计划
explain
select *
from employee
where dept_id = 5;
id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
--|-----------|--------|----------|----|-------------|------------|-------|-----|----|--------|-----|
1|SIMPLE |employee| |ref |idx_emp_dept |idx_emp_dept|4 |const| 8| 100.0| |
结果显示，MySQL 通过索引 idx_emp_dept 进行查找，并且最多返回多条记录（type= ref）。
实际上这是通过索引范围扫描（INDEX RANGE SCAN）找到所有的索引项，然后通过物理地址指针（非聚集索引）读取表中的数据。这种情况下可能需要扫描多个索引叶子节点，而且每次对表的访问都可能是随机 I/O，因此导致性能下降。这也就是为什么我们应该对选择性强的字段（例如手机号）进行索引，而不是重复性高的字段（例如性别）。
📝PostgreSQL 针对以上语句选择了 Seq Scan on employee，也就是全表扫描；因为它的优化器认为这种方法速度更快。
复合条件
有些时候，查询条件可能同时涉及多个字段。与此对应，我们可以创建基于多个字段的组合索引（多列索引、复合索引）。对于组合索引，建立索引树时首先考虑第一个字段的排序，第一个字段顺序相同时再使用第二个字段，依次类推。因此，查询条件中最常出现的列应该放在最左边，这个称为组合索引最左前缀原则。
我们创建以下示例表：
create table test (
id int not null primary key,
col1 int,
col2 int,
col3 int
);
insert into test
with recursive t(id,c1,c2,c3) as (
select 1 id,1 c1,1 c2,1 c3
union all
select id+1,c1+1,mod(c2+1,10),id+1 from t where id < 10000
)
create unique index idx_test_col1_col2 on test(col1, col2);
analyze table test;
我们使用字段 col1 和 col2 创建了一个组合唯一索引，并且字段 col2 上只有 10 种不同的数值。
以下是同时使用这两个字段作为查询条件时的执行计划：
-- MySQL 执行计划
explain
select *
from test
where col2 = 9 and col1 = 6669;
id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra|
--|-----------|-----|----------|-----|------------------|------------------|-------|-----------|----|--------|-----|
1|SIMPLE |test | |const|idx_test_col1_col2|idx_test_col1_col2|10 |const,const| 1| 100.0| |
即使 col2 在查询条件中出现在 col1 前面，查询仍然能够通过索引唯一扫描获取数据。
此时，如果将查询条件修改为只包含 col1 字段：
-- MySQL 执行计划
explain
select *
from test
where col1 = 6669;
id|select_type|table|partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra|
--|-----------|-----|----------|----|------------------|------------------|-------|-----|----|--------|-----|
1|SIMPLE |test | |ref |idx_test_col1_col2|idx_test_col1_col2|5 |const| 1| 100.0| |
由于字段 col1 出现在索引的最左端，此时仍然可以通过索引范围扫描获取数据。此时的 key_len 从 10 变成了 5，也就是只利用了其中的 col1 字段。
如果将查询条件修改为只包含 col2 字段：
-- MySQL 执行计划
explain
select *
from test
where col2 = 9;
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra |
--|-----------|-----|----------|----|-------------|---|-------|---|-----|--------|-----------|
1|SIMPLE |test | |ALL | | | | |10173| 10.0|Using where|
此时，type 列显示为 ALL，执行计划变成了全表扫描（TABLE ACCESS FULL），通常意味着需要进行优化。
📝对于以上情况，Oracle 可能会通过索引跳跃扫描（INDEX SKIP SCAN）获取数据，但是只有在优化器认为这种方式更有效时才会使用。在上面的示例中，Oracle 仍然会选择使用全表扫描。
显然，如果我们把索引的顺序调整为 (col2, col1)，可以解决使用 col2 单独查询的性能；但是又会导致使用 col1
查询时无法使用索引。还有一个方法就是为 col2
单独再创建一个索引，当然这会导致索引数量增加，维护成本更高。因此，我们不但需要了解索引的工作方式，也需要了解应用程序对数据的查询方式。
范围查找
除了等值查找之外，查询条件中的 <、<=、>、>= 和 BETWEEN 等运算符通常用于返回某个范围之内的数据。这些运算符也可以利用索引提高查询的性能。例如，以下语句查找 manager 位于某个范围之内的员工：
-- MySQL 执行计划
explain
select *
from employee
where manager between 10 and 15;
id|select_type|table |partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--|-----------|--------|----------|-----|---------------|---------------|-------|---|----|--------|---------------------|
1|SIMPLE |employee| |range|idx_emp_manager|idx_emp_manager|5 | | 1| 100.0|Using index condition|
结果显示，MySQL 通过索引 idx_emp_manager 查找范围值（type= range）。
对于索引范围扫描（INDEX RANGE SCAN），需要注意过多的叶子节点遍历可能导致性能下降。因此，尽量减少索引扫描的范围。对于上面的示例，使用单个字段作为查询条件，索引扫描的范围完全取决于具体的数据。
我们来考虑一个复合查询条件的示例：
select *
from test
where col1 between 5000 and 5001
and col3 = 5555;
显然，为了优化查询，我们需要创建一个复合索引。问题在于哪个字段在前，哪个字段在后。一般来说，等值条件字段应该出现在范围条件字段之前，这样能够减少索引扫描的范围。
对于上面的示例，我们创建两个复合索引：
create index idx_test_col1_col3 on test(col1, col3);
create index idx_test_col3_col1 on test(col3, col1);
然后查看数据库的执行计划：
-- MySQL 执行计划
explain
select *
from test
where col1 between 5000 and 5001
and col3 = 5555;
id|select_type|table|partitions|type
|possible_keys
|key |key_len|ref|rows|filtered|Extra |
--|-----------|-----|----------|-----|--------------------------------------------------------|------------------|-------|---|----|--------|---------------------|
1|SIMPLE |test |
|range|idx_test_col1_col2,idx_test_col3_col1,idx_test_col1_col3|idx_test_col3_col1|10
| | 1| 100.0|Using index condition|
通过结果可以看出，MySQL 最终选择了索引 idx_test_col3_col1，而不是
idx_test_col1_col3。因为根据索引的组织结构，col3 在前面的话可以更快确定索引扫描的范围。如果 col1
的范围更大一点的话，通过索引 idx_test_col3_col1 进行范围查找的优势会更明显。
LIKE 运算符
对于 LIKE 运算符，如果查找的模式不是以通配符开头，可以像范围查找条件一样使用索引。例如，以下语句通过 email 进行模糊查找：
-- MySQL 执行计划
explain
select *
from employee
where email like 'zhang%';
id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--|-----------|--------|----------|-----|-------------|------------|-------|---|----|--------|---------------------|
1|SIMPLE |employee| |range|uk_emp_email |uk_emp_email|302 | | 2| 100.0|Using index condition|
与此相反，如果通配符出现在左侧无法使用索引。例如以下语句会导致全表扫描：
-- MySQL 执行计划
explain
select *
from employee
where email like '%fei%';
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----------|
1|SIMPLE |employee| |ALL | | | | | 25| 11.11|Using where|
总之，LIKE 运算符只能使用通配符之前的字符进行索引查找，通配符之后的字符只能作为过滤谓词（filter predicate）使用。
📝对于这种以通配符开始的模式匹配，如果存在性能问题，可以考虑数据库中提供的全文索引或者专用的全文搜索引擎。
使用函数
在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效。例如，以下语句用于实现不区分大小写的 email 查询：
-- MySQL 执行计划
select *
from employee
where lower(email) = lower('ZhangFei@shuguo.com');
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----------|
1|SIMPLE |employee| |ALL | | | | | 25| 100.0|Using where|
虽然 email 字段上已经创建了索引 uk_emp_email，但是数据库仍然使用了全表扫描。因为索引中并没有存储 lower(email) 的数据，相当于没有任何索引。另一个常见的案例就是对日期时间字段进行函数运算。
类似的情况还包括对索引字段进行算术运算，例如：
-- MySQL 执行计划
explain
select *
from employee
where emp_id + 1 = 10;
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----------|
1|SIMPLE |employee| |ALL | | | | | 25| 100.0|Using where|
针对这类问题的解决方法首先就是避免对数据库的字段进行运算和函数；如果无法避免，可以考虑使用函数索引（表达式索引）。
我们为 email 字段创建一个函数索引：
-- MySQL 8.0
create unique index uk_emp_email_lower on employee( (lower(email)) );
analyze table test;
再次查看执行计划：
-- MySQL 执行计划
explain
select *
from employee
where lower(email) = lower('ZhangFei@shuguo.com');
id|select_type|table |partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra|
--|-----------|--------|----------|-----|------------------|------------------|-------|-----|----|--------|-----|
1|SIMPLE |employee| |const|uk_emp_email_lower|uk_emp_email_lower|303 |const| 1| 100.0| |
此时，MySQL 选择了通过索引 uk_emp_email_lower 进行唯一扫描。
📝另一种更加隐蔽的情况是隐式类型转换。例如数据库使用字符类型的字段存储数字，然后使用where string_col = 100作为查询条件，也会导致索引失效。
空值查找
在数据库中，空值（NULL）是一个特殊的值，通常用于表示缺失值或者不适用的值。关于这方面的详细介绍，可以参考这篇文章。
索引可以被用于空值的查找，但是 Oracle 中存在一些例外。例如：
-- MySQL 执行计划
explain
select *
from test
where col1 is null;
id|select_type|table|partitions|type|possible_keys
|key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|----|-------------------------------------|------------------|-------|-----|----|--------|---------------------|
1|SIMPLE |test | |ref
|idx_test_col1_col2,idx_test_col1_col3|idx_test_col1_col2|5
|const| 1| 100.0|Using index condition|
MySQL 选择了通过索引 idx_test_col1_col2 进行范围查找。
对于 Oracle 而言，情况有所不同：
-- Oracle 执行计划
explain plan for
select *
from test
where col1 IS null;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT |
--------------------------------------------------------------------------|
Plan hash value: 1357081020 |
|
--------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 11 | 9 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| TEST | 1 | 11 | 9 (0)| 00:00:01 ||
--------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("COL1" IS NULL) |
显然，Oracle 选择了全表扫描 TABLE ACCESS FULL，而不是索引 idx_test_col1_col2。这是因为 Oracle 对于索引字段全部为 NULL 的数据，不会创建相应的索引项。
解决的方法就是在索引中增加一个不会为空的字段或者常量。例如：
-- 方法一
create index idx_test_col1 on test(col1, 0);
-- 方法二
alter table test modify col2 not null;
使用方法一创建索引之后，Oracle 的执行计划如下：
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------------------------|
Plan hash value: 178088924 |
|
-----------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 11 | 1 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | IDX_TEST_COL1 | 1 | | 1 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("COL1" IS NULL) |
多表连接查询
连接查询用于从两个或者多个表中获取相关的数据。对于数据库的实现而言，主要有三种算法：嵌套循环连接（Nested Loop Join）、哈希连接（Hash Join）以及排序合并连接（Sort Merge Join）。关于这些实现算法的详细内容，可以参考这篇文章。
对于嵌套循环连接，WHERE 条件上的索引可以加速外部表的访问，内部表连接字段上的索引可以提高连接的性能；哈希连接以及排序合并连接不需要基于连接字段创建索引，不过 WHERE 条件中其他字段上的索引同样可以优化查询的性能。
排序和分组
排序是一个非常消耗资源的操作，需要占用很多 CPU 时间和临时存储空间。对于大型的数据集而言，可能需要使用磁盘作为临时存储，从而导致性能明显下降。
索引按照顺序存储数据，就像ORDER BY子句的效果一样，因此我们可以通过索引来避免排序操作。
以下查询使用 dept_id 作为条件，同时按照 hire_date 进行排序：
-- MySQL 执行计划
explain
select *
from employee
where dept_id = 3
order by hire_date;
id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
--|-----------|--------|----------|----|-------------|------------|-------|-----|----|--------|--------------|
1|SIMPLE |employee| |ref |idx_emp_dept |idx_emp_dept|4 |const| 2| 100.0|Using filesort|
查询虽然可以通过索引 idx_emp_dept 进行查找，但是仍然需要执行排序操作（Extra 显示 Using filesort）。为了避免这个排序，可以将 hire_date 字段添加到索引中：
create index idx_emp_dept_hiredate on employee(dept_id, hire_date);
由于外键 fk_emp_dept 依赖于索引 idx_emp_dept，我们这里增加一个新的索引作为演示。然后再次查看执行计划：
-- MySQL 执行计划
explain
select *
from employee
where dept_id = 3
order by hire_date;
id|select_type|table
|partitions|type|possible_keys |key
|key_len|ref |rows|filtered|Extra|
--|-----------|--------|----------|----|----------------------------------|---------------------|-------|-----|----|--------|-----|
1|SIMPLE |employee| |ref
|idx_emp_dept,idx_emp_dept_hiredate|idx_emp_dept_hiredate|4
|const| 2| 100.0| |
显然，此时 MySQL 选择了索引 idx_emp_dept_hiredate，同时避免了排序。
需要注意的是，并非所有按照 hire_date 排序的查询都可以利用该索引优化。例如：
-- MySQL 执行计划
explain
select *
from employee
where dept_id > 3
order by hire_date;
id|select_type|table
|partitions|type|possible_keys
|key|key_len|ref|rows|filtered|Extra |
--|-----------|--------|----------|----|----------------------------------|---|-------|---|----|--------|---------------------------|
1|SIMPLE |employee| |ALL
|idx_emp_dept,idx_emp_dept_hiredate| | | | 25| 68.0|Using
where; Using filesort|
以上语句查找 dept_id 大于 4 的部门员工，此时执行计划选择了全表扫描，而不是 idx_emp_dept_hiredate，同时需要执行排序操作。这是因为索引 idx_emp_dept_hiredate 中 dept_id 大于 3 的多个索引项之间并不是按照 hire_date 排序，而且通过二级索引访问还需要再次扫描数据表，优化器评估认为全表扫描性能更好。
因此，数据库执行排序操作的原因有两种：执行排序操作代价更低；索引扫描时的顺序和 order by 子句的顺序不一致。
大部分数据库都支持索引的双向扫描，意味着于索引顺序相反的排序操作也可以利用索引进行优化。例如：
-- MySQL 执行计划
explain
select *
from employee
where dept_id = 3
order by hire_date desc;
id|select_type|table
|partitions|type|possible_keys |key
|key_len|ref |rows|filtered|Extra |
--|-----------|--------|----------|----|----------------------------------|---------------------|-------|-----|----|--------|-------------------|
1|SIMPLE |employee| |ref
|idx_emp_dept,idx_emp_dept_hiredate|idx_emp_dept_hiredate|4
|const| 2| 100.0|Backward index scan|
其中，Extra 字段显示的 Backward index scan 表明 MySQL 支持反向索引扫描。
不仅ORDER BY可以利用索引，GROUP BY同样可以利用索引进行分组优化。例如：
-- MySQL 执行计划
explain
select hire_date,count(*)
from employee
where dept_id = 3
group by hire_date;
id|select_type|table
|partitions|type|possible_keys |key
|key_len|ref |rows|filtered|Extra |
--|-----------|--------|----------|----|----------------------------------|---------------------|-------|-----|----|--------|-----------|
1|SIMPLE |employee| |ref
|idx_emp_dept,idx_emp_dept_hiredate|idx_emp_dept_hiredate|4
|const| 2| 100.0|Using index|
从结果可以看出，MySQL 选择了索引 idx_emp_dept_hiredate；同时利用索引进行分组，避免了在临时表中执行的分组操作。我们可以将该索引删除后再看看执行计划：
drop index idx_emp_dept_hiredate on employee;
explain
select hire_date,count(*)
from employee
where dept_id = 3
group by hire_date;
id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
--|-----------|--------|----------|----|-------------|------------|-------|-----|----|--------|---------------|
1|SIMPLE |employee| |ref |idx_emp_dept |idx_emp_dept|4 |const| 2| 100.0|Using temporary|
其中，Extra 字段显示为 Using temporary，意味着分组在临时表中实现。
DML
索引不仅会对查询产生影响，对数据进行插入、更新和删除操作时也需要同步维护索引结构。
INSERT 语句
对于 INSERT 语句而言，索引越多执行越慢。插入数据必然导致增加索引项，这种操作的成本往往比插入数据本身更高，因为索引必须保持顺序和 B-树的平衡（索引节点拆分）。因此，优化插入语句的最好方法就是减少不必要的索引。
📝没有任何索引时的插入性能是最好的，因此在加载大量数据时，可以临时删除所有的索引并在加载完成后重建。
谨慎而谨慎地使用索引，并尽可能避免使用冗余索引。这对于删除和更新语句也是有益的。
UPDATE 语句
UPDATE 语句如果指定了 WHERE 条件，可以通过索引提高更新操作的性能，因为通过索引可以快速找到需要修改的数据。
另一方面，UPDATE 语句如果修改了索引字段的值，需要删除旧的索引项并增加新的索引项。因此，更新操作的性能通常也取决于索引的数量。为了优化 UPDATE 语句，频繁更新的字段不适合创建索引；同时应该尽量避免修改过多的字段，尤其是使用一些 ORM 框架时。
DELETE 语句
对于 DELETE 语句而言，如果指定了 WHERE 条件，可以通过索引提高删除操作的性能。因为它和 UPDATE 语句一样，需要先执行一个 SELECT 语句找到需要删除的数据。
删除操作涉及的索引更新和插入操作类似，只不过它是删除一些索引项并确保索引树的平衡。因此，索引越多删除性能越差。不过有一个例外就是没有任何索引，这个时候性能会更差，因为数据库需要执行全表扫描才能找到需要删除的数据。
总结
B-树（B+树、B*树）索引极大地减少了随机磁盘的访问，同时具有对数时间复杂度，几乎不受数据量的影响；因而是我们进行 SQL 优化的最有效、最常用的方法。索引可以用于优化各种查询条件、连接查询、排序和分组以及 DML 语句，但是也会占用额外的存储空间和索引维护操作，我们在创建索引和编写 SQL 语句时需要遵循一定的原则，才能有效地利用索引实现优化。
如果觉得文章对你有用，请不要白嫖！欢迎关注❤️、评论📝、点赞👍！
分类导航