当表中有百万数据的时候，我们要怎么去查询数据，平时写的sql也许就会很慢了。

SQL的执行顺序

SELECT
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE<where_condition>
GROUP BY <cgroup_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

执行顺序:

• FROM <表名># 选取表，将多个表数据通过笛卡尔积变成一个表。

• ON <筛选条件> # 对笛卡尔积的虚表进行筛选

• JOIN <join, left join, right join...>

• <join 表> #指定join，用于添加数据到on之后的虚表中，例如leftjoin会将左表的剩余数据添加到虚表中

• WHERE <where条件> #对上述虚表进行筛选

• GROUP BY <分组条件> # 分组

• <SUM() 等聚合函数> #用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的

• HAVING <分组筛选> # 对分组后的结果进行聚合筛选

• SELECT <返回数据列表> #返回的单列必须在group by子句中，聚合函数除

• DISTINCT 数据除重

• ORDER BY 排序条件> # 排序

• LIMIT <行数限制>

几个SQL优化技巧

这里总结了几种优化sql的小技巧

我准备了一百万的数据，表如下：

CREATE TABLE `test_import_export` (
  `seckill_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(51) NOT NULL COMMENT '订单id',
  `user_id` int(20) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `msg` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`seckill_id`),
  UNIQUE KEY `uidx_so_oid` (`order_id`) USING BTREE,
  UNIQUE KEY `uidx_so_id` (`seckill_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.写清楚需要查询的字段

select * from test_import_export;  -- 消耗2.006s

这个语句会查询所有字段，如果我们不需要字段比较长，比如msg这个字段，那这种写法就会增加查询时长。使用select *可能造成以下问题：

• 性能问题

• 维护问题

• 查询结果不确定：当查询中存在多个表时，使用SELECT * 很容易造成列名冲突，导致查询结果不确定。

• 冗余数据

select seckill_id,order_id,user_id,goods_id from test_import_export;  -- 0.712s

少查了msg字段后，可以看出快了不少。

2.避免索引失效的场景

2.1 使用in范围太大

in的范围太大会导致索引失效，not in 也会导致索引失效。

EXPLAIN select seckill_id,order_id,user_id,goods_id from test_import_export where order_id in (
    "share_of_sean_240001","share_of_sean_240002", ... -- 后面跟10000万条
)

EXPLAIN select seckill_id,order_id,user_id,goods_id from test_import_export where order_id not in (
    "share_of_sean_999982","share_of_sean_999983","share_of_sean_999984","share_of_sean_999985","share_of_sean_999986"
)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0jJIViOD-1693295196299)(file://d:\Pictures\mdpic\2023-08-28-16-46-14-image.png?msec=1693267693120)]

分析上面两个语句不会使用到索引，使用范围不大的in可以使用索引

EXPLAIN select seckill_id,order_id,user_id,goods_id from test_import_export where order_id in (
"share_of_sean_999982","share_of_sean_999983","share_of_sean_999984","share_of_sean_999985","share_of_sean_999986"
)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JcOiwc3I-1693295196300)(file://d:\Pictures\mdpic\2023-08-28-16-46-31-image.png?msec=1693267693120)]

如果使用in，需要限制in的数量。

但是使用>,<,BETWEEN ... AND ...这样的范围查找是可以使用索引的（无论范围多大），因为B+数的叶子节点已经排好序了，这样可以很容易的锁定范围。但是in里边的数据不确定，如果范围太大，优化器会分析，如果觉得全表扫描更快，就直接使用全表扫描。

解决办法将大量的in条件进行分页。然后再将结果组合起来。

2.2 前缀模糊查询

EXPLAIN 
select seckill_id,order_id,user_id,goods_id from test_import_export where order_id like '%_11';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WyXuWy8n-1693295196300)(file://d:\Pictures\mdpic\2023-08-28-20-41-43-image.png?msec=1693267693120)]

前缀模糊查询会导致索引失效，导致全表扫描

EXPLAIN 
select seckill_id,order_id,user_id,goods_id from test_import_export where order_id like '11_%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nA7FnqFL-1693295196300)(file://d:\Pictures\mdpic\2023-08-28-20-39-52-image.png?msec=1693267693120)]

非前缀模糊查询则会使用到索引

2.3 数据区分度不大

select seckill_id,order_id,user_id,goods_id from test_import_export where `status` = 1 

如果在status字段上加索引，没有效果，因为索引是B+树，本来就是通过索引来进行排序增加检索速度，只有几个枚举值，自然也不需要索引就能很快检索。

2.4 在属性上计算（或使用函数，或隐式类型转换）不能使用索引

以下我就不贴出运行结果，直接解释原因。

EXPLAIN
select seckill_id,order_id,user_id,goods_id from test_import_export where seckill_id + 1 = 10;

因为索引保存的是索引字段的原始值，而不是 id+1（包括其他函数和表达式计算）计算之后的值，所以无法走索引。

这个解释似乎有些牵强，那为什么MySql不转换成 id = 10-1，我查过这个问题，觉得最好的解释是因为MySql不太想去实现这个逻辑，因为表达式和函数太多了，如果需要全部实现，显得代码太过臃肿。就需要程序员自己去注意这个问题。

对索引隐式类型转换

EXPLAIN
select seckill_id,order_id,user_id,goods_id from test_import_export where seckill_id = "71222593";

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Il1539OZ-1693295196300)(file://d:\Pictures\mdpic\2023-08-28-22-06-33-image.png?msec=1693267693121)]

数字 = 字符串，这种比较方式会使用索引。

EXPLAIN
select seckill_id,order_id,user_id,goods_id from test_import_export where order_id = 17;

字符串 = 数字，这种不会使用到索引。

原因是MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较

相当于对order_i使用了函数：

EXPLAIN
select seckill_id,order_id,user_id,goods_id from test_import_export where CAST(order_id AS signed int)  = 17;

而第一种方式，相当于在右测使用了函数，自然能用到索引。

2.5 联合索引非最左匹配

联合索引在B+树中的存储方式如下图

其中c1是主键，联合索引c2,c3,c4。它首先根据联合索引第一列的值排序，如果第一列的值相等再根据第二列的值排序，以此类推。

联合索引需要遵循最左匹配原则，也就是按照最左优先的方式进行匹配。

如果创建了(a, b, c)三个联合索引

第一种情况：以下查询可以用到索引：

• where a=1；

• where a=1 and b=2 and c=3；

• where a=1 and b=2；

其中a,b,c的顺序不重要，MySql会进行优化。

第二种情况：但是以下查询，则不会走索引：

• where b=2；
• where c=3；
• where b=2 and c=3；

第三种情况：比较特殊：

• where a = 1 and c = 3

  这种情况属于索引截断，a能使用到索引，c不能。

  在5.5版本，前面a会走索引，在联合索引找到主键值后，回表查询出数据，然后对比c字段的值。

  5.6版本后，有一个索引下推功能，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

  什么是索引下推：五分钟搞懂MySQL索引下推

  大概原理是：截断的字段会被下推到存储引擎层进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

第一，第二种情况体现了最左匹配原则，因为联合索引是按a,b,c依此排序，在a相同的情况下，对b排序，在b相同的情况下，对c排序。如果直接找b，根据树的特点就不能依据顺序快速定位b的位置。

2.6 使用OR

or两边，只要有一个不是索引，就会导致索引失效，原因很简单，如果有一边不是索引，那另一天即使有索引还是需要去匹配没有索引的一边是否符合条件，任然需要全表扫描。

解决办法是可以使用in代替or。或者分别查询在应用层面拼接。

3.慎用UNION关键字

在使用UNION执行完SQL后，会帮我们获取所有数据并去掉重复的数据，性能的损耗就在这里，而UNION ALL和UNION相反，帮我们获取所有数据但会保留重复的数据。

-- 2.5s
EXPLAIN
select seckill_id, order_id, user_id, goods_id from test_import_export where status = '0'
UNION
select seckill_id, order_id, user_id, goods_id from test_import_export where status = '1';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yg2w9TkO-1693295196301)(file://d:\Pictures\mdpic\2023-08-29-14-39-09-image.png?msec=1693291149492)]

UNION会产生一张临时表，大致是用来去重的

-- 1.2s
EXPLAIN
select seckill_id, order_id, user_id, goods_id from test_import_export where status = '0'
UNION ALL
select seckill_id, order_id, user_id, goods_id from test_import_export where status = '1';

更换成union all执行时间上能快很多。

解决方法可以把两个语句拆开分别查（这里可以用多线程），然后将查询结果组合到一起。避免用union或 union all

4.小表驱动大表

加入表user_info，为小表（数据量不大），如下：

CREATE TABLE `user_info` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

有几种小表驱动大表的形式：

select user_id from test_import_export where user_id in 
(select user_id from user_info where user_id = 20)

5.分页查询优化

分页查询优化的方式主要有两种：

• 最大id查询法。将上一次查询出的最大id传入，取每页数目的数据量。查询速度在ms级别

  select * from test_import_export where seckill_id > 44212577 ORDER BY seckill_id ASC
  limit 20;
  

• 子查询方式。先分页查询出符合条件和分页的第一条id，然后取id>=的数据

  
  -- 子查询方式，1千万秒级别
  
  select * from test_import_export 
  where seckill_id >= ( select seckill_id from test_import_export limit 990000,1) limit 10;
  

以上两种方式都只适合不排序的情况。如果需要排序，则需要给排序加上索引，用索引字段来代替id字段比较：

select * from test_import_export 
where user_id >= ( select user_id from test_import_export limit 990000,1) limit 10;

6.优化select count

优化select count，只能从应用层面优化。

• 加缓存（redis）

参考博客：

面试官：聊聊索引失效？失效的原因是什么？ - 小林coding - 博客园

五分钟搞懂MySQL索引下推