MySQL索引和查询优化

1 索引

1.1 索引的分类

1.存储形式

• 聚簇索引
• 非聚簇索引

2.数据约束

• 主键索引
• 唯一索引
• 非唯一索引

3.索引列的数量

• 单列索引
• 组合索引

4.innoDB可以创建的索引

• 主键索引
• 唯⼀索引
• 普通索引

1.2 索引的数据结构

1.二叉查找树
理想状态：

最坏情况：

2.平衡二叉树
旁边的小数字代表深度

3.B-树

4.B+树
与B-树不同，B+树的所有数据都存储在叶子节点中，内部节点只包含索引键和子节点的指针。且所有叶子节点按照顺序链接，形成一个双向链表，这使得范围查询更加高效。

5.聚簇索引

6.非聚簇索引

1.3 回表查询

回表查询是指在数据库查询过程中，先使用非聚簇索引（也称为辅助索引）来定位数据行的位置，然后再通过找到的索引数据行中的主键值或指针，再次访问主数据表（聚集索引）来获取完整的数据行。回表查询常常发生在数据库中使用非聚簇索引来加速数据查找的情况下。

回表查询的成本取决于磁盘 I/O，因为它需要两次访问磁盘：一次是定位到索引，另一次是回到主数据表获取完整的数据行。为了避免频繁的回表操作，数据库设计中通常需要根据查询的需求来选择适当的索引、表结构和查询优化策略。

覆盖索引是一种优化手段，可以避免回表查询，通过在索引中包含查询所需的列，从而提高查询性能。

1.4 覆盖索引

覆盖索引（Covering Index）是指在查询过程中，索引包含了查询所需的所有数据列，无需回表查询主索引或数据页。换句话说，覆盖索引能够直接提供查询所需的数据，而不需要再去访问主索引或数据页，从而提高查询性能和效率。这样可以避免回表操作，减少了磁盘 I/O，提高了查询速度。

覆盖索引将所需的查询列直接存储在索引中，因此查询可以在索引上完成，而不需要回表。

覆盖索引在以下情况可以很好的适用：

1. 当查询只需要检索几个列而不是整行数据时，覆盖索引可以避免回表操作，从而加速查询。
2. 当查询需要排序、分组或进行聚合操作时，覆盖索引可以减少排序和聚合的数据量，提高性能。
3. 当查询中的列包含在覆盖索引中，数据库可能会选择直接使用索引来满足查询，而不必加载整个数据行。

覆盖索引的优点：

1. 提高查询性能：由于覆盖索引能够直接提供查询所需的数据，减少了磁盘的随机访问和额外的回表查询操作，从而加快了查询的执行速度。
2. 减少磁盘 I/O：回表查询需要进行额外的磁盘读取操作，而覆盖索引可以减少磁盘 I/O 操作，降低系统的磁盘负载。
3. 减少内存消耗：覆盖索引可以减少需要加载到内存中的数据量，节省了内存的使用，提高了查询的效率。

覆盖索引也有一些考虑因素：

1. 覆盖索引会占用更多的存储空间，因为它存储了额外的列数据。
2. 当表的数据更新时，覆盖索引也需要维护更新，可能会导致索引维护的开销增加。
3. 使用过多的覆盖索引可能会导致维护开销过大，影响插入、更新和删除操作的性能。

覆盖索引在某些查询场景下能够显著提高性能，但在设计时需要权衡存储和维护成本。

2 查询优化

2.1 MySQL查询优化器简介

MySQL 查询优化器是数据库管理系统中的一个关键组件，它负责分析和决定如何执行查询以获得最佳性能。查询优化器的目标是在给定查询的情况下，选择最优的查询执行计划，以最小化资源消耗（例如 CPU、内存、磁盘 I/O）并在最短时间内返回结果。

查询优化器的工作流程：
(1) 查询解析： 首先，查询文本被解析器解析成语法树（Parse Tree）。这个阶段涉及语法和语义分析，将查询语句转化为数据库理解的内部表示形式。
(2) 查询重写： 在这个阶段，优化器可能会对查询进行重写，以便更好地优化。它可能会将子查询转换成连接操作，移动谓词（WHERE 子句）等。
(3) 查询优化： 优化器根据统计信息、索引和其他相关信息，生成不同的执行计划，并评估每个执行计划的代价。
(4) 选择最优执行计划： 优化器选择代价最低的执行计划，并生成执行计划的执行指令。
(5) 查询执行： 最终选择的执行计划被传递给查询执行引擎，引擎根据执行计划执行查询操作。这个阶段实际执行查询，返回结果给用户。

2.2 查询优化主要手段

1.优化查询语句

• 使用恰当的SQL语句：根据查询需求选择合适的SQL语句，避免冗余或复杂的查询操作。
• 减少数据返回量：只选择需要的列，避免返回不必要的数据，减少网络传输和结果集处理开销。

2.创建适当的索引
MySQL索引是⼀种用于加快数据检索速度和提高查询性能的数据结构。它类似于书籍的目录，通过按照某个或多个列的值进行排序和存储，使得数据库可以更快地定位和访问特定的数据行。

其中索引的类型有以下几类：

• B-Tree索引：B-Tree（平衡树）是MySQL最常用的索引类型。它将索引数据以树形结构存储，支持快速的范围查找和精确查找。
• 全文索引：全文索引用于对文本字段进行全文搜索，提供更高级的文本搜索功能。

3.优化数据模型和表结构

• 正规化数据模型：遵循数据库设计的规范，消除数据冗余，提高查询效率。
• 合理划分表和分区：将大表划分为更小的表或使用分区技术，提高查询效率和数据维护性能。

4.监测和分析查询性能

• 使用性能监控工具：监测数据库的性能指标，如查询响应时间、锁等待时间等，及时发现性能瓶颈。
• 分析执行计划：使⽤EXPLAIN语句分析查询的执行计划，查看索引使用情况和性能瓶颈，优化查询语句和索引设计。

5.定期维护和优化

• 定期收集统计信息：通过收集表的统计信息，优化查询优化器的决策，提⾼查询计划的准确性和性能。
• 定期重建索引：当索引碎片化严重时，定期重建索引，提⾼索引的效率。

2.3 EXPLAIN

2.3.1 EXPLAIN 介绍

EXPLAIN 是 MySQL 中用于查询计划分析的关键字。通过在 SQL 查询前加上 EXPLAIN，您可以获取关于查询执行计划的详细信息，从而了解优化器如何选择执行计划以及查询如何在数据库中执行。

当执行 EXPLAIN 查询时，MySQL 会返回一个解释执行计划的结果集，其中包含了查询的各个阶段、访问的表、使用的索引、表之间的连接方式、数据的读取方式等信息。这些信息对于优化查询和定位性能问题非常有帮助。

示例：
EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 EXPLAIN 就可以了。

2.3.2 参数说明

EXPLAIN 查询的结果通常包括以下关键列：
id：查询中操作的标识符，表示查询执行的顺序。
select_type：表示查询的类型，如简单查询、子查询、联合等。
table：表示查询访问的表。
type：表示数据访问方式，包括全表扫描、索引扫描、范围查找等。
possible_keys：表示可能使用的索引。
key：表示实际使用的索引。
key_len：表示索引字段的长度。
ref：表示连接的参考对象，如常数值或其他表的列。
rows：表示预计要检查的行数。
Extra：额外的信息，如是否使用临时表、是否使用文件排序等。

id

每个单位查询的SELECT语句都会⾃动分配的⼀个唯一标识符，表示查询中操作表的顺序，有三种情况：

• id相同：执行顺序由上到下
• id不同：如果是子查询，id号会自增，id越大，优先级越高。
• id相同的不同的同时存在

select_type

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。

1. simple
表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。

2. primary
一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。

3. union
union连接的两个select查询，第⼀个查询是dervied派生表，除了第⼀个表外，第⼆个以后的表select_type都是union。

• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中。

• UNION 若第⼆个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

• UNION RESULT 从UNION表获取结果的SELECT

4. dependent union
与union⼀样，出现在union 或 union all语句中，但是这个查询要受到外部查询的影响。

5. union result
包含union的结果集，在 union 和 union all 语句中,因为它不需要参与查询，所以id字段为null。

6. subquery
除了from字句中包含的子查询外，其它地方出现的子查询都可能是subquery 。

7. dependent subquery
与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。

8. derived
from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。

table

显示的单位查询的表名，有以下几种情况：

• 如果查询使用了别名，那么这里显示的是别名
• 如果不涉及对数据表的操作，那么这显示为null
• 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产⽣。
• 如果是尖括号括起来的<union M,N>，与类似，也是⼀个临时表，表示这个结果来自于union查询的
  id为M,N的结果集。
  

partitions

使用的哪些分区（对于非分区表值为null）。

5.7之后的版本默认会有 partitions 和 filtered两列，但是5.6版本中是没有的。

• 需要使用explain partitions select ……来显示带有partitions 的列
• 使用explain extended select ……来显示带有filtered的列。

什么是分区表？
mysql内部实现的表的水平拆分，所有数据还在⼀个表中，但物理存储根据⼀定的规则放在不同的文件中。这个是mysql⽀持的功能，业务代码⽆需改动。

type（重要）

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：

1. system
表中只有一行数据或者是空表。等于系统表，这是const类型的特列，平时不会出现，这个也可以忽略不计。
2. const（重要）
使用唯一索引或者主键，返回记录⼀定是1⾏记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

3. eq_ref（重要）
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

4. ref（重要）
非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然⽽，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

• 组合索引
  
• 非唯一索引
  

5. fulltext
全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，MySQL不管代价，优先选择使⽤全文索引。
6. ref_or_null
与ref方法类似，只是增加了null值的比较，实际用的不多。
7. unique_subquery
⽤于where中的in形式子查询，子查询返回不重复值唯一值。
8. index_subquery
⽤于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
9. range（重要）
索引范围扫描，常见于使⽤>,<,is null,between ,in ,like等运算符的查询中。

10. index_merge
表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能⼤部分时间都不如range。
11. index（重要）
select结果列中使用到了索引，type会显示为index。

全部索引扫描：把索引从头到尾扫⼀遍，常见于使用索引列就可以处理不需要读取数据⽂件的查询、可以使用索引排序或者分组的查询。

12. ALL（重要）
这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

注意：
除了all之外，其他的type都可以使用到索引
除了index_merge之外，其他的type只可以⽤到⼀个索引
最少要使⽤到range级别

possible_keys

此次查询中可能选用的索引，一个或多个。

key

查询真正使用到的索引，select_type为index_merge时，这⾥可能出现两个以上的索引，其他的select_type这里只会出现⼀个。

key_len

• 用于处理查询的索引⻓度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。
• 留意下这个列的值，算一下你的多列索引总长度就知道有没有使⽤到所有的列了。
• 另外，key_len只计算where条件⽤到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

• 如果是使用的常数等值查询，这里会显示const。
• 如果是连接查询，被驱动表的执⾏计划这⾥会显示驱动表的关联字段。
• 如果是条件使用了表达式或者函数，或者条件列发⽣了内部隐式转换，这⾥可能显示为func。

rows（重要）

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里⾯使用了MVCC并发机制）。

filtered

filtered列指示将由mysql server层需要对存储引擎层返回的记录进行筛选的估计百分比，也就是说存储
引擎层返回的结果中包含有效记录数的百分比。最大值为100，这意味着没有对行进行筛选。值从100减小表示过滤量增加。rows显示检查的估计行数，rows×filtered显示将与下表联接的行数。例如，如果rows为1000，filtered为50.00（50%），则要与下表联接的行数为1000×50%=500。

extra（重要）

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多。
1. Using filesort
说明mysql会对数据使⽤⼀个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“⽂件排序”。需要优化sql。
2. Using temporary
使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3. using index（重要）
查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。

• 表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不
  错。
• 如果同时出现Using Where ，说明索引被用来执行查找索引键值。
• 如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作。

4. using where（重要）
表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引。

5. impossible where
where子句的值总是false ，不能用来获取任何元组。

3 查询优化实战

准备工作

在进行实战案例演示前，我们需要准备相关数据，在电商平台中，最核心的数据为：用户、商品、订单，因此，我们需要创建了对应三张表，以及批量初始化大量数据，其中，表结构简单设计如下：
my_customer：

CREATE TABLE `my_customer` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(3) DEFAULT '20' COMMENT '年龄',
 `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别 0-⼥ 1-男',
 `phone` varchar(20) DEFAULT '' COMMENT '地址',
 `address` varchar(100) DEFAULT NULL,
 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `my_customer_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户';

my_order：

CREATE TABLE `my_order` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) NOT NULL,
 `product_id` int(11) NOT NULL,
 `quantity` int(11) NOT NULL DEFAULT '1' COMMENT '数量',
 `total_price` int(11) NOT NULL DEFAULT '1' COMMENT '总价',
 `order_status` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '订单状
态 0-未⽀付 1-已⽀付 2-派送中 3-已签收',
 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单';

my_product：

CREATE TABLE `my_product` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL COMMENT '商品名',
 `type` int(11) NOT NULL DEFAULT '1' COMMENT '类型 1-⾐服 2-⻝品 3-书籍',
 `brand` varchar(100) DEFAULT '' COMMENT '品牌',
 `shop_id` int(11) NOT NULL DEFAULT '1' COMMENT '店铺ID',
 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品';

执行测试案例初始化测试数据，其中，用户数据量为100万，商品数据量10万，订单数据量近千万。
接下来, 我们根据实际电商平台常见查询场景进行分析和优化。

场景1：用户搜索

1.不使用索引查询
电商后台管理系统通常需要根据用户名称、手机号、地址搜索相关用户信息，SQL查询语句：

select * from `my_customer` where phone like '%157%'

查询结果：

再通过 EXPLAIN 分析输出结果

explain select * from `my_customer` where phone like '%157%';

可以看到该SQL语句的执行计划中，type字段为ALL , 表示全表扫描，这会导致查询效率过低，耗时过长。

2.使用索引查询
首先应该考虑为查询字段加上索引，例如phone字段。

CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer(phone);

这里要注意，模糊匹配查询使用%在开头会导致索引失效。可以尝试将查询条件改为以 %结尾的模糊匹配，例如:

select * from `my_customer` where phone like '157%';

接下来使用 EXPLAIN 命令再次查看执行计划：

explain select * from `my_customer` where phone like '157%';

可以看到SQL执行过程中实际用到了 my_customer_phone_IDX 索引 , 相比全表扫描，这里预计扫描函数仅10w多行。

3. 覆盖索引和回表查询
在实际开发过程中，应该避免使用 SELECT * ：只选择需要的字段，而不是使用通配符*。只选择必要的字段可以减少数据传输和内存开销，提高查询性能。
例如，仅需根据用户手机号查询用户id和姓名, 那么，SQL应该改写为：

select id, name from `my_customer` where phone like '157%';

那么到这里，当前SQL语句能否进⼀步优化呢？答案是肯定的。
重新创建 my_customer_phone_IDX 索引，执行如下：

DROP INDEX my_customer_phone_IDX on store.my_customer;
CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer(phone,name);

重新使用 EXPLAIN 命令再次查看执行计划：

explain select * from `my_customer` where phone like '157%';

explain select id, name from `my_customer` where phone like '157%';

可以看到 Extra 字段的值包含 Using index , 表明触发了索引覆盖，也进行了回表查询，查询时间大大减少。查询时间如下：

如果SQL如下：

select count(name) from `my_customer` where phone like '157%';

可以看到覆盖索引也会生效。

4.联合索引
基于多个字段创建的索引我们称为联合索引，比如我们创建索引create index idx on table(A,B,C) 我们称在字段A,B,C上创建了⼀个联合索引。

联合索引和单个索引对比来讲，联合索引的所有索引项都会出现在索引上，存储引擎会先根据第一个索引项排序，如果第一个索引项相同的话才会去看第⼆个，所以在查询的时候，如果不带头索引的话，联合索引就会失效，因为在根节点它就不知道怎么往下走。

例如：

select * from user u where u.age=20 and u.money=30;

这个SQL查询首先在根节点上age>1并且<60,那么读下⼀个节点，依次类推读到叶子节点上取出主键id回表查询所有的字段值。

5. 最左前缀法则
如果索引了多个列，要遵循最左前缀法则，查询从索引的最左前列开始，并且不能跳过索引中的列。
例如：
组合索引（quantity，total_price，order_status）

select * from user where total_price=770 and order_status=3;

该SQL查询语句跳过了age这列，导致索引失效。

创建组合索引：

DROP INDEX my_order_price_IDX ON store.my_order;
CREATE INDEX my_order_price_IDX USING BTREE ON store.my_order(quantity,total_price,order_status);

用到组合索引的情况：

explain select * from my_order where quantity = 10;
explain select * from my_order where quantity = 10 and total_price=770;
explain select * from my_order where quantity = 10 and total_price=770 and order_status=3;
explain select * from my_order where quantity = 10 and order_status=3;

索引失效的情况：

explain select * from my_order where total_price=770 and order_status=3;
explain select * from my_order where order_status=3;

使用部分索引的情况：

explain select * from my_order where quantity = 10 and order_status=3;

场景2：订单查询

1. 子查询
不管是用户App端还是在电商后台，都存在订单查询的场景，例如我们需要根据品牌查询对应品牌下商品的订单，先给商品表加个以品牌字段作为索引：

CREATE INDEX my_product_brand_IDX USING BTREE ON store.my_product (brand);

⼀条常见的查询SQL:

select * from my_order mo where product_id in (select id from my_product mp where brand ='Apple');

SQL查询耗时7900ms:

使用 EXPLAIN 命令查看执行计划：

可以看到有两条执行计划，其中订单表的查询使用了全表扫描。

再给订单表的 prodcut_id 字段加上索引：

CREATE INDEX my_order_product_id_IDX USING BTREE ON store.my_order(product_id);

使用 EXPLAIN 命令再次查看执行计划：

可以看到用到了覆盖索引，加快了查询效率。虽然子查询在当前情况下实现了查询需求，但使用子查询可能会导致⼀些性能问题，因此在优化查询时，通常不建议过度依赖子查询。

以下是⼀些原因：

执行多次查询：效率太差，执行子查询时，MySQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到⼀定的影响，这里多了一个创建和销毁临时表的过程。
可读性和维护性差：复杂的嵌套子查询可能会使查询语句变得难以理解和维护。子查询通常需要理解嵌套层次和各个子查询之间的关系，使查询语句变得冗长且难以阅读。
缺乏优化灵活性：数据库优化器在处理⼦查询时的优化能力相对较弱。优化器很难对复杂的嵌套子查询进行全⾯的优化，可能无法选择最佳执行计划，导致性能下降。
可能引发性能问题：子查询可能导致全表扫描或临时表的创建，增加系统的 I/O负担和内存消耗。 特别是当⼦查询涉及⼤量数据或涉及多表关联时，性能问题可能更加明显。

对于能够使用连接查询（JOIN）或其他更有效方法替代的子查询，通常建议使用更简洁和⾼效的查询方式。连接查询可以更好地利用索引和优化执行计划，同时提供更好的可读性和维护性。

然而，并非所有情况下都不推荐使用子查询。在某些特定的场景下，子查询是合理的选择，例如需要进行存在性检查或在查询中嵌套聚合函数等情况。在使用子查询时，需要根据实际情况综合考虑性能、可读性和维护性的权衡，确保达到最佳的查询效果。

2.连接查询
将SQL语句改写为连接查询（JOIN)

SELECT mo.id as orderId, mo.customer_id as customerId, mp.name as productName, mo.order_status as orderStatus FROM my_order mo JOIN my_product mp ON mo.product_id = mp.id WHERE mp.brand = 'Apple';

虽然多表连接查询（多表 JOIN）是常见的查询方式之一，但是一旦join涉及到的数据量很大效率就很难保证，这种情况下强烈推荐分别根据索引单表取数据，然后在应用层里面做join，merge数据。

在应用层关联的优势如下：

• 提高缓存效率：应用程序可以方便地缓存单表查询的结果对象。通过拆分关联查询，当关联表中的数据发生变化时，不会影响到查询缓存，从而提高缓存的效率。
• 减少锁竞争：拆分查询可以减少锁的竞争。执行单个查询时，只涉及到单个表，减少了锁的冲突， 提高了并发性能。
• 易于数据库拆分：在应用层进行关联查询，更容易实现数据库的拆分，提供高性能和可扩展性的能力。
• 提升查询效率：使用IN()替代关联查询时，MySQL可以按照 ID 的顺序进行查询，这可能比随机的关联查询更高效。
• 减少冗余记录查询：应用层关联查询意味着每条记录只需要查询一次，而在数据库中进行关联查询可能需要重复访问部分数据。因此，这种重构还可以减少网络和内存的开销。
• 哈希关联效率更高：应用层关联相当于在应⽤中实现了哈希关联，而不是使用MySQL的嵌套循环关 联。在某些场景下，哈希关联的效率要高得多。

不推荐使用JOIN 的原因：

• 大规模表的性能压力：当表的数据量达到百万级别时，使⽤ JOIN 可能导致性能下降。
• 分布式分库分表：跨库 JOIN 不推荐使用，因为目前MySQL的分布式中间件对跨库 JOIN 的支持不佳。
• 表结构修改的复杂性：修改单表查询相对容易，⽽修改 JOIN 的 SQL 语句较为复杂，维护成本较高。

JOIN 在部分场景使用也有好处：例如分页查询：JOIN 查询可以方便地进行分页，可以使用副表的字段作为查询条件，在查询时将副表的匹配字段作为结果集，使⽤主表进行 IN() 查询。

场景3：分页查询

⼀般典型分页查询语句如下：

SELECT mo.id AS orderId, mo.customer_id AS customerId, mo.order_status AS orderStatus FROM my_order mo WHERE mo.order_status = 1 ORDER BY mo.id ASC LIMIT 1000000, 10;

查询时间为2200ms：

使用 EXPLAIN 命令查看执行计划：

limit是最常用的分页方法,它在执行过程中，相当于先遍历了前1000000个，然后取了第1000000到
1000010个，舍弃了前1000000个, limit越大查询性能越低，limit仅适用于小数据范围内的分页查询。

可以利用索引来进行优化，例如我们分页查询到第1000000条数据，订单ID为9397780，那么下个分页的所有订单ID都是大于9397780。
改写SQL语句为：

SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus FROM my_order mo inner join (select id from my_order where id >9397780 and order_status = 1 limit 10) mo2 on mo.id = mo2.id order by mo.id asc;

查询时间几乎0ms：

使用 EXPLAIN 命令查看执行计划：

从查询计划我们看到，首先子查询根据主键索引，获取最多10条订单ID, 然后再根据这10条ID获取数据详情。不需要再查询上百万条数据后排序取所需几行数据。

场景4：订单统计

假设需要查询不同商品的订单数和订单总额，SQL语句：

select mo.product_id , count(*) as num , sum(mo.total_price) from my_order mo group by mo.product_id;

查询时间：

可以看到查询100000个商品耗时1分半，效率极低。

对于分组统计查询，以下是一些优化思路：
1.使用合适的索引：为支持分组和统计操作，可以考虑创建合适的索引。优化思路包括：

• a. 为分组字段和统计字段创建索引，以提高分组和聚合操作的效率。
• b. 考虑覆盖索引，即索引包含所有需要的字段，避免回表查询。
• c. 针对不同的查询场景和条件，选择适当的索引类型（如B-tree索引、哈希索引等）。

2. 缓存结果集：对于频繁进行的分组统计查询，可以考虑缓存结果集，避免每次都重新计算。优化思路包括：

• a. 使用缓存技术（如Redis）存储结果集，以便快速获取统计数据。
• b. 设置合适的缓存失效策略，根据数据的更新频率进行定期更新或⼿动更新。

3. 预聚合数据：对于大数据量和复杂的统计查询，可以考虑预先计算和存储聚合结果，以减少查询时的计算量。优化思路包括：

• a. 创建定期或实时的预聚合任务，将统计结果存储到特定的表中。
• b. 在查询时直接从预聚合表中获取结果，避免重复的计算和分组操作。

4. 合理设置分组字段：对于分组统计查询，分组字段的选择会影响查询性能。优化思路包括：

• a. 尽量选择具有⾼基数（不同取值较多）的字段作为分组字段，以减少分组的数量和计算量。
• b. 避免在查询中使用过多复杂的表达式或函数作为分组字段，以减少计算的开销。

5. 考虑并行计算：对于大规模数据的分组统计查询，可以考虑使用并行计算来提⾼查询效率。优化思路包括：

• a. 将查询任务拆分为多个并行的子任务，每个子任务处理不同的数据子集。
• b. 使用并行计算框架或数据库引擎支持并行查询，以加快查询速度和提⾼吞吐量。

因为在之前的场景案例里，已经对product_id 字段加了索引，可以根据第三条和第五条优化建议：并行计算，在应用层聚合数据，考虑每条SQL仅对部分商品进行统计，例如：

select mo.product_id , count(*) as num , sum(mo.total_price) from my_order mo where mo.product_id between 1000 and 2000 group by mo.product_id;

这里仅对商品ID在（1000,2000）范围内的订单进行统计，我们可以分多次查询不同的数据。
查询时间：

使用 EXPLAIN 命令查看执行计划：

可以看到这里采用了my_order_product_id_IDX索引加快查询，另外由于数据量的减少，进行排序和统计的耗时也大大减少。