MYSQL SQL优化总结

MYSQL SQL优化总结

目录

• MYSQL SQL优化总结
  • 优化十大原则
  • 优化案例解析
    • 1.隐式转换
    • 2、WHERE子查询
    • 3、OR语句
    • 4、GROUP/ORDER BY
    • 5、LIMIT偏移量过大
      • 案例：
    • 6、模糊查询
    • 7、覆盖索引
    • 8、表/字段 别名
    • 9、字符集不同
    • 10、left join

MySQL日常维护中，性能瓶颈是每个运维人员都头疼的问题之一，那么该如何有效解决这个问题呢？慢SQL问题则是重中之重。因此本文重点讲解的是从慢日志的抓取、执行计划的解读、优化的原则到各种类型的案例解析等方面来全方位的讲解慢SQL优化。

优化十大原则

1 、严禁使用SELECT*方式查询语句，必须明确查询字段，INSERT语句必须明确要插入的字段。
2、严禁单条SQL关联表超过3张，关联字段必须有索引且数据类型一致。
3、严禁单条SQL子查询超过2层。
4、严禁在SQL中进行计算或嵌套判断逻辑。
5、严禁查询条件中字段无索引，表的索引数量不要超过6个。
6、严禁在where条件中字段使用函数或者表达式(例如wherecol/3>=100)。
7、严禁负向查询条件(!=、<>、not...)、单表行数大于5万的禁止左模糊、全模糊查询(例如：colA like ‘%服务’)。
8、严禁传入变量类型与查询条件中字段类型不匹配。
9、严禁表无主键或使用复合索引作为主键，严禁使用无序数据作为主键内容。
10、严禁使用外键、视图、触发器、存储过程、自定义函数和分区表。

优化案例解析

1.隐式转换

原则：禁止隐式转换，保持变量类型与字段类型一致
eg:dept_no类型为char时

SQL1（正确）：selectemp_no,from_date from dept_emp1 where dept_no='404838';
SQL2（错误）：selectemp_no,from_date from dept_emp1 where dept_no=404838;

2、WHERE子查询

优化子查询原则：使用连接代替子查询，效率更佳
**A.使用连接方式改写子查询，案例如下 **

例1: SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROMt2);
改写: SELEC TDISTINCT t1.column1 FROM t1, t2
WHEREt1.column1 = t2.column1;

或：SELECT DISTINCT t1.column1 FROM t1 JOIN ON t1.column1 = t2.column1;

例2: SELECT *FROM t1 WHERE id NOT IN (SELECT id FROM t2);

改写:  SELECT* FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

或LEFT JOIN：
SELECT table1.*   FROM table1 LEFT JOIN table2
ON table1.id=table2.id   WHERE table2.id IS NULL; 

B.对于只返回一行的无关联子查询用‘=’代替‘in’

例:  SELECT *FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);
改写:  SELECT* FROM t1 WHERE t1.col_name= (SELECT a FROM t2 WHERE b = some_const);

总结：对于数据库来说，在绝大部分情况下， 连接会比子查询更快。使用连接的方式，MySQL优化器一般可以生成更佳的执行计划，更高效地处理查询。而子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引， 因此效率会更低。

3、OR语句

原则：有关or的优化，A.建立相关索引，B.将or转化为in或union

A.or子句全部相同，则改为in

示例：select* from t1 where a=1 or a=3;
改为：select* from t1 where a in(1,3);

B.or子句具有公共子序列前缀的，请在or公共部分建立索引

示例：（如下需要在a列上创建索引）
select * from t1 where (a=1 and b=2) or (a=3 and c=4);

C.若无公共，则建议改为unionall，并为每部分建立索引

示例  
select* from t1 where a=1 or b=2;
可以使用 Indexmerge

或者转换（效率更高）：     
select * from t1 where a=1
union all
select * from t1 where b=2;

4、GROUP/ORDER BY

4.1、orderby子句，尽量使用Index方式排序，在索引列上遵循索引的最佳左前缀原则。如下：

Key(a,b,c)，Orderby 能使用索引情况
--order  by  a
--order  by  a, b
--order  by  a, b,c
--order  by  a desc ,b desc ,c desc

4.2、如果where使用索引的最左前缀定义为常量，则orderby能使用索引

--wherea=const  order by b,c
--wherea=const   and b=const  order by c
--wherea=const  and b>const order by b,c

总结：分组统计可以禁止排序，默认情况下，有分组必排序，如果想避免排序结果的消耗，可以指定orderby null禁止排序。

5、LIMIT偏移量过大

禁止分页查询偏移量过大，如limit100000,10
优化方法一：
A.limit查询转换成某个位置的查询，即把limitm,n转换成limitn；
B.利用自增主键，避免offset使用;
C. 限制用户翻页。

eg:

调整LIMIT1000

优化方法二：分页查询尽可能地使用索引覆盖扫描，而不是所有的列，然后再做一次关联操作再返回所需的列。

#优化前
select film_id,description from film order by title limit 50,5

#优化后
select a.film_id, a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id =b.film_id

案例：

原sql:

SELECT
	id,
	relation_id,
	head_id,
	item_id,
	to_els_account,
	item_number,
	order_item_number,
	order_number,
	publish_status,
	confirm_status,
	original_require_date,
	require_date,
	quantity,
	receive_quantity,
	on_way_quantity,
	delivery_quantity,
	return_quantity,
	plan_to_delivery_quantity,
	original_plan_delivery_date,
	original_plan_delivery_quantity,
	performance_base_date,
	plan_delivery_date,
	plan_delivery_quantity,
	purchase_remark,
	supplier_remark,
	responsible_party,
	is_close AS CLOSE,
	is_freeze AS freeze,
	is_delete_plan AS deletePlan,
	is_performance,
	fbk1,
	fbk2,
	fbk3,
	fbk4,
	fbk5,
	fbk6,
	fbk7,
	fbk8,
	fbk9,
	fbk10,
	fbk11,
	fbk12,
	fbk13,
	fbk14,
	fbk15,
	fbk16,
	fbk17,
	fbk18,
	fbk19,
	fbk20,
	extend_field,
	manufacturer,
	manufacturer_code,
	pig_breeds,
	pig_breeds_code,
	demand_degree,
	material_name,
	material_spec,
	material_model,
	base_unit,
	goods_flow,
	goods_flow_code,
	delivery_status,
	els_account,
	create_by,
	create_time,
	update_by,
	update_time,
	is_deleted AS deleted 
FROM
	purchase_order_delivery_plan 
WHERE
	is_deleted = 0 
	AND is_performance = '0' 
	AND confirm_status = 1 
	AND els_account = '30712082' 
	LIMIT 200900,
	100

优化后的sql:

SELECT
	`id`,
	`relation_id`,
	`head_id`,
	`item_id`,
	`to_els_account`,
	`item_number`,
	`order_item_number`,
	`order_number`,
	`publish_status`,
	`confirm_status`,
	`original_require_date`,
	`require_date`,
	`quantity`,
	`receive_quantity`,
	`on_way_quantity`,
	`delivery_quantity`,
	`return_quantity`,
	`plan_to_delivery_quantity`,
	`original_plan_delivery_date`,
	`original_plan_delivery_quantity`,
	`performance_base_date`,
	`plan_delivery_date`,
	`plan_delivery_quantity`,
	`purchase_remark`,
	`supplier_remark`,
	`responsible_party`,
	`is_close` AS `close`,
	`is_freeze` AS `freeze`,
	`is_delete_plan` AS `deletePlan`,
	`is_performance`,
	`fbk1`,
	`fbk2`,
	`fbk3`,
	`fbk4`,
	`fbk5`,
	`fbk6`,
	`fbk7`,
	`fbk8`,
	`fbk9`,
	`fbk10`,
	`fbk11`,
	`fbk12`,
	`fbk13`,
	`fbk14`,
	`fbk15`,
	`fbk16`,
	`fbk17`,
	`fbk18`,
	`fbk19`,
	`fbk20`,
	`extend_field`,
	`manufacturer`,
	`manufacturer_code`,
	`pig_breeds`,
	`pig_breeds_code`,
	`demand_degree`,
	`material_name`,
	`material_spec`,
	`material_model`,
	`base_unit`,
	`goods_flow`,
	`goods_flow_code`,
	`delivery_status`,
	`els_account`,
	`create_by`,
	`create_time`,
	`update_by`,
	`update_time`,
	`is_deleted` AS `deleted` 
FROM
	`purchase_order_delivery_plan`
	INNER JOIN ( SELECT `id` FROM `purchase_order_delivery_plan` WHERE `is_deleted` = 0 AND `is_performance` = '0' AND `confirm_status` = 1 AND `els_account` = '30712082' LIMIT 200900, 100 ) `tmp_0` USING (`id`)

说明：
这两个 SQL 语句在语义上不完全一致，尽管它们看起来都试图从 purchase_order_delivery_plan 表中选出满足相同过滤条件的记录，并限制返回 100 条记录，从第 200900 条开始（分页）。

✅ 相同点：
过滤条件完全一致：
返回字段一致（尽管第二个 SQL 显式加了表前缀，但字段内容一致）

• LIMIT 200900, 100 表示从第 200901 条记录开始，取 100 条记录（分页语义）

⚠️ 不同点（语义差异）：
1. 执行方式不同：

• 第一个 SQL 是直接在主表上执行过滤 + 分页。
• 第二个 SQL 是先分页选出 ID，再回表 JOIN 获取完整字段。
  2. 性能与行为差异（可能导致语义不一致）：
• 第二个 SQL 的子查询只选了 id，然后通过 JOIN 回表获取完整字段。
• 如果 id 不是唯一（虽然通常是主键），或者 LIMIT 的行为在子查询中受索引/优化器影响，可能选出不同的 ID 集合。
• 更重要的是：LIMIT 在没有 ORDER BY 的情况下是非确定性的（MySQL 可能返回不同的 100 行，除非排序字段唯一且稳定）。

✅ 结论：

• 在 id 是主键，且 LIMIT 行为稳定的前提下，这两个 SQL 的语义可以认为是等价的。
  但从严格语义上讲，它们并不完全一致，因为：
• 第二个 SQL 是“先分页 ID，再回表”，而第一个是“直接分页完整记录”。
• 如果 id 不唯一或 LIMIT 行为不稳定，可能返回不同的结果集。
• 第二个 SQL 更像是一种性能优化手段（减少回表数据量），但在语义上不能完全等价于第一个。

✅ 建议：
如果你希望语义完全一致，请：
1.加上 ORDER BY id（或唯一字段）；
2.确保两个 SQL 都使用相同的 ORDER BY；
3.或者统一使用第一种写法（更直接、语义明确）。

推荐：

-- 明确排序，语义一致
SELECT ...
FROM purchase_order_delivery_plan
WHERE is_deleted = 0
  AND is_performance = '0'
  AND confirm_status = 1
  AND els_account = '30712082'
ORDER BY id
LIMIT 200900, 100;

-- 子查询方式也需加 ORDER BY
SELECT ...
FROM purchase_order_delivery_plan
JOIN (
  SELECT id
  FROM purchase_order_delivery_plan
  WHERE is_deleted = 0
    AND is_performance = '0'
    AND confirm_status = 1
    AND els_account = '30712082'
  ORDER BY id
  LIMIT 200900, 100
) tmp USING (id);

✅ 总结：

项目	第一个 SQL	第二个 SQL	是否一致
过滤条件	✅ 相同	✅ 相同	✅
返回字段	✅ 相同	✅ 相同（带前缀）	✅
分页方式	直接 LIMIT	子查询 LIMIT + JOIN	⚠️ 不一致
确定性	❌ 无 ORDER BY	❌ 无 ORDER BY	❌
语义一致性	✅ 直接明了	⚠️ 可能优化，但语义不完全等价	❌
结论：不加 ORDER BY 时，这两个 SQL 的语义不一致。

6、模糊查询

全模糊或左模糊不使用索引
优化建议：全模糊查询改为dsps_staff_id like ’HE12065%’，优化后效率提升百倍。

7、覆盖索引

包含所有满足查询需要的数据的索引成为覆盖索引，也就是平时所说的不需要回表操作,对于一个索引覆盖查询，显示为usingindex。

这里最主要看Extra，它的值为Usingindex，它在这句查询中含义就是直接访问film_id这个索引就足已获取到所需要的数据，不需要再通过索引回表查询了。

使用覆盖索引的前提条件是，查询返回的字段数足够少，select* 类不可以。

8、表/字段 别名

A：原sql基表是t5，大量使用临时表、排序，效率低下
B：order bycrttime 使用别名，导致索引失效

建议：orderby crt_time 使用字段名代替别名

9、字符集不同

关联字段的字符集不一致，导致索引不可用。
例如：knowledge_rel的字符集及校验规则，与关联表knowledge不一致，数据关联时，影响SQL执行效率。

注：utf8mb4_bin比较方法就是直接将所有字符看作二进制串，然后从最高位往最低位比对，所以它是区分大小写的。

10、left join

使用leftjoin一定要注意：
A：条件中尽量有强过滤，将驱动表为小
B：右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）
C：无视以上两点，一般不要用leftjoin~~！

原SQL：存在强过滤，但是在所有数据join后的结果集上过滤，差！

优化后：调整在where后，将驱动表实行强过滤后变小，再与其它表leftjoin，提高效率。