【MySQL】项目中常用的 18 条 MySQL 优化打造高性能 SQL 语句

关注

原创最新推荐文章于 2026-05-01 05:47:03 发布 3.7w 阅读 AI 写同款· GEO 优化 ›

倒立摆入门详解+pid调参倒立摆制作全过程最近在练习pid的控制算法，就选用了2013年的电子设计国赛题目：倒立摆。首先给你们看看我的成果注意哦：倒立摆不是靠上面黄色的传感器保持平衡的，而是通过下面的电机左右扭动使得摆杆不掉下来，难度可不小了。材料准备 1.机械部分如图所示选好电机和角位移传感器进行组装就好了，这个部分是我直接网上购买的，如果要自己装也是可以的，不过会比较麻烦，结构差不多的。 2.主控部分这个部分的话完全是我自己设计的，作用和网上买的一样，因为网上买的话太贵了，有需要的话可以联系我，有pcb文件阅读详情

1. 执行计划EXPLAIN

要想做MySQL优化，首先必须知道如何善用执行计划EXPLAIN。下图做个简单的示例并标注需要重点关注的数据。
在这里插入图片描述

type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式
key_len列，索引长度
rows列，扫描行数。该值是个预估值
Extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary
关于EXPLAIN的详细分析，可以阅读另一篇文章：
【MySQL】执行计划EXPLAIN详解

2. SELECT语句务必指明字段名称

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

3. 如果排序字段没有用到索引，就尽量少排序

4. 当只需要一条数据的时候，使用LIMIT 1

这是为了使EXPLAIN中type列达到const类型

5. SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

6. 如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

7. 尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

8. 区分in和exists， not in和not exists

select * from 表A where id in (select id from 表B)

上面sql语句相当于

select * from 表A where exists
(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的sql语句？
原sql语句

select colname … from A表
where a.id not in (select b.id from B表)

高效的sql语句

select colname … from A表 Left join B表 on
where a.id = b.id where b.id is null

取出的结果集如下图表示，A表不在B表中的数据
在这里插入图片描述

9. 分页查询时的优化

select id,name from table_name limit 86789520, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
优化的思路如下：我们知道主键是有索引的，根据主键先快速定位到大概位置，可以大大节约分页查询的效率。实际开发中，我们可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此例中，上一页最大的id是866612。sql可以采用如下的写法：

select id,name from table_name where id> 86789500 limit 20

10. 分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。
如下图这个sql语句，扫描的行数成百万级以上的时候就可以使用分段查询，如果不使用分段查询，数十亿条数据，即使有索引，查询的结果也十分缓慢。
在这里插入图片描述

11. 避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

12.不建议使用%前缀进行模糊查询

例如LIKE "%keyword"或者LIKE "%keyword%"，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE "keyword%"。如下图的执行计划结果，sku_name字段是有索引的，但是第二条语句的type是ALL，显然没有走索引。
在这里插入图片描述

那么对于模糊查询LIKE "%keyword%"如何走索引呢？答案是使用全文索引。
在我们使用

SELECT * FROM cps_commodity_info c WHERE c.sku_name LIKE '%辣%';

这样的查询语句时，普通索引是无法满足查询需求的，所幸在MySQL中，有FULLTEXT(全文索引)可以满足这个需求，但是需要注意的是使用全文索引的时候，SQL语句也有所不同：

SELECT * FROM cps_commodity_info c WHERE MATCH(c.sku_name) AGAINST ('辣' IN boolean MODE);

执行计划如图：
在这里插入图片描述
Tips:
需要特别注意的是，在创建FULLTEXT之前，请联系DBA确定是否能创建。同时需要注意查询语句的不同。另外，笔者不建议在开发中使用全文索引。

13. 避免在where子句中对字段进行表达式操作

比如：
在这里插入图片描述
可以看到，由于对主键进行了取余运算，导致引擎放弃了索引走了全表扫描。

14. 避免隐式类型转换

WHERE子句中出现列字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定WHERE中的参数类型。如下图所示，sku_id是varchar类型，查询条件是bigint类型时候就不会走索引，但是改成varchar类型就可以走索引了。
在这里插入图片描述

附上建表的SQL语句以供参考，可以看到sku_id字段是varchar类型。

CREATE TABLE `cps_commodity_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sku_id` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '商品skuId',
  `sku_name` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '商品名称',
  `price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '商品价格',
  `is_on_top` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否置顶，0：不置顶，1：置顶',
  `created_date` datetime NOT NULL COMMENT '创建时间',
  `modified_date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `ldelete_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志，0：未删除，2：已删除',
  PRIMARY KEY (`id`),
  KEY `idx_sku_name` (`sku_name`) USING BTREE COMMENT '商品名称索引',
  KEY `idx_sku_id` (`sku_id`) USING BTREE COMMENT '商品skuId索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='cps商品信息';

15. 对于联合索引来说，要遵守最左前缀法则

举列来说索引含有字段id,sku_id,sku_name，可以直接用id字段，也可以采用id,sku_id这样的顺序，但是sku_id、sku_name就都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，一般的原则是最常用的查询字段放在最前面。

16. 注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。

17. 必要时可以使用FORCE INDEX来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索sql语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。

18. 关于连接查询JOIN的优化

如下图所示：

LEFT JOIN A表为驱动表
INNER JOIN MySQL会自动找出那个数据少的表作用驱动表
RIGHT JOIN B表为驱动表

在这里插入图片描述
尽量使用INNER JOIN ，避免LEFT JOIN。
参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是INNER JOIN ，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是LEFT JOIN 在驱动表的选择上遵循的是左表驱动右表的原则，即LEFT JOIN左边的表为驱动表。
合理利用索引；被驱动表的索引字段作为ON的限制字段；利用小表去驱动大表。

19. 建表的时候字段尽可能使用NOT NULL

非NULL字段的处理要比NULL字段的处理高效些并且不需要判断是否为NULL。
NULL在MySQL中，不好处理，存储需要额外空间，运算也需要特殊的运算符。如SELECT NULL= NULL和SELECT NULL <> NULL（<>为不等号）有着同样的结果，只能通过IS NULL和IS NOT NULL判断字段是否为NULL。
MySQL中每条记录都需要额外的存储空间，表示每个字段是否为NULL。

收录于