首页 › Celinf › 双枪老太婆电视剧 (sql优化篇)

双枪老太婆电视剧 (sql优化篇)

🏷️ Celinf ✍️ 搬运工 📅 2026-03-18T18:47:59+00:00 

SELECT语句 - 执行顺序：
FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...>
<join表> # 指定join，用于添加数据到on之后的虚表中，
//例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> 
//用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

explain关键字

explain 是非常重要的关键字,要善于运用它. 通过explain我们可以获得以下信息：

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

sql什么意思,sql优化篇

Explain图

type “访问类型”

ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

Select_type 说明查询中使用到的索引类型，如果没有用有用到索引则为all

  1. ALL： Full Table Scan， MySQL将遍历全表以找到匹配的行
  2. index: Full Index Scan，index与ALL区别为index类型只遍历索引树
  3. range: 只检索给定范围的行，使用一个索引来选择行
  4. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  5. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
  6. const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
  7. NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

sql什么意思,sql优化篇

Explain执行计划

select_type 类型

  • (1) SIMPLE (简单SELECT,不使用UNION或子查询等)
  • (2) PRIMARY (查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
  • (3) UNION (UNION中的第二个或后面的SELECT语句)
  • (4) DEPENDENT UNION (UNION中的第二个或后面的SELECT语句，取决于外面的查询)
  • (5) UNION RESULT (UNION的结果)
  • (6) SUBQUERY (子查询中的第一个SELECT)
  • (7) DEPENDENT SUBQUERY (子查询中的第一个SELECT，取决于外面的查询)
  • (8) DERIVED (派生表的SELECT, FROM子句的子查询)
  • (9) UNCACHEABLE SUBQUERY (一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

sql什么意思,sql优化篇

sql优化

1. 避免使用select * 

select * from user where id=1;
  1. 在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。
  2. 多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。
  3. 还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。
//如何优化呢？
select name,age from user where id=1;

2. 用union all代替union

  • 我们都知道sql语句使用union关键字后，可以获取排重后的数据。而如果使用union all关键字，可以获取所有数据，包含重复的数据。
(select * from user where id=1)
union
(select * from user where id=2);
  • 排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。所以如果能用union all的时候，尽量不用union。
(select * from user where id=1)
union all
(select * from user where id=2);
  • 除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。

3 小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。这时如果想查一下，所有有效的用户下过的订单列表。

//可以使用in关键字实现：
select * from order
where user_id in (select id from user where status=1)
//也可以使用exists关键字实现：
select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)
  1. 因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。
  2. 而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

总结一下：

  • in 适用于左边大表，右边小表。
  • exists 适用于左边小表，右边大表。
  • 不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

4 批量操作 

//每次远程请求数据库，是会消耗一定性能的。
insert into order(id,code,user_id)values(123,'001',100);
//提供一个批量插入数据的方法。
insert into order(id,code,user_id)
values(123,'001',100),(124,'002',100),(125,'003',101);
//这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。

【注】不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

5 多用limit 

//普通查询它的效率非常不高，需要先查询出所有的数据，有点浪费资源。
select id, create_date  from order
where user_id=123  order by create_date asc
limit 1;
//使用limit 1，只返回该用户下单时间最小的那一条数据即可。

6 in中值太多 

//in里面的值太多  查询出非常多的数据，很容易导致接口超时。
select id,name from category where id in (1,2,3...100000000);
// In里面最好不超过500  如果太多可以分批
select id,name from category where id in (1,2,3...100) limit 500;

7 增量查询 

//直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非
//常大的问题，数据很多的话，查询性能会非常差。
select * from user
//通过这种增量查询的方式，能够提升单次查询的效率。
select * from user
where id>#{lastId} and create_time >= #{lastCreateTime}
limit 100;
//按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，
//保存这100条数据中最大的id和时间，给同步下一批数据的时候用。

8 高效的分页 

//mysql会查到1000020条数据，然后丢弃前面的1000000条，
//只查后面的20条数据，这个是非常浪费资源的。
select id,name,age from user limit 1000000,20;
//解决方案一
//先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。
select id,name,age from user where id > 1000000 limit 20;
//解决方案二
//使用between优化分页。
select id,name,age from user where id between 1000000 and 1000020;
//【注】需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。

9 用连接查询代替子查询 

//子查询 优点是简单，结构化，如果涉及的表数量不多的话。
//缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。
select * from order
where user_id in (select id from user where status=1)
//解决方案
select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

10 join的表不宜过多 

//join表的数量不应该超过3个
select a.name,b.name.c.name,a.d_name
from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id

11 join时要注意

  • left join：求两个表的交集外加左表剩下的数据。
  • inner join：求两个表交集的数据。
//使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，
//所以性能上不会有太大的问题。
select o.id,o.code,u.name
from order o
inner join user u on o.user_id = u.id
where u.status=1;
//用left join关联查询时，左边要用小表，右边可以用大表。
//如果能用inner join的地方，尽量少用left join。
select o.id,o.code,u.name
from order o
left join user u on o.user_id = u.id
where u.status=1;

12 控制索引的数量

  • 新增数据时，需要同时为它创建索引，而索引是需要额外的 存储空间的 ，而且还会有一定的性能消耗。
  • 单表的索引数量应该尽量控制在 5个以内 ，并且单个索引中的字段数不超过5个。
  • mysql使用的B+树的结构来保存索引的，在insert、update和delete操作时，需要更新B+树索引。如果 索引过多 ，会 消耗很多额外的性能 。

如果表中的索引太多，超过了5个该怎么办？

  1. 这个问题要辩证的看，如果你的系统并发量不高，表中的数据量也不多，其实超过5个也可以，只要不要超过太多就行。
  2. 但对于一些高并发的系统，请务必遵守单表索引数量不要超过5的限制。
  3. 那么，高并发系统如何优化索引数量？
  4. 能够建联合索引，就别建单个索引，可以删除无用的单个索引。
  5. 将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase等，在业务表中只需要建几个关键索引即可。

13 选择合理的字段类型

  1. 用bit存布尔值，用tinyint存枚举值等。
  2. 长度固定的字符串字段，用char类型。
  3. 长度可变的字符串字段，用varchar类型。
  4. 金额字段用decimal，避免精度丢失问题。

14 提升group by的效率

group by关键字，它主要的功能是去重和分组。

思路:缩小数据范围，然后分组。不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能。

15 索引优化

sql语句，走了索引，和没有走索引，执行效率差别很大。

查看sql走了索引没？可以使用 explain 命令，查看mysql的执行计划。

sql什么意思,sql优化篇

索引常见失效

学习记录，如有侵权请联系删除。