MySQL SQL 优化指南 SQL 四句真言（优化系列 3）

❝
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

上期说完MSSQL的优化后，相信科技的力量让SQL优化更加简单深入人心，但如果你用的是MySQL,瞬间一种天堂到地狱的赶脚。咱们今天就说说磨练人心性的MySQL SQL优化。

咱们也有四句真言，给MySQL SQL优化

参数调整挺重要，查询方式很重要，

大表你的要会拆，架构优化少不了，

善用SQL的改写，避免中间大结果，

尽量升级8.0 ，更好引擎效率高。

熟悉MySQL的同学对于SQL优化中第一个问题并不是SQL本身的这个问题应该很清楚，早期玩MySQL的都是现在参数的调整里面先玩一顿，然后在转向表的拆分，最后才是SQL本身。

第一个参数与你的CPU有关，innodb_buffer_pool_instances，提高并发度使用内存的灵活性，有效的将buffer pool分片，在高并发写的情况下，减少latch的竞争。CPU 核心越多，内存越大，这个部分分的就越多，具体优化这个参数的文档很多，可以去读一读。

剩下的就是 join_buffer_size, read_buffer_size,read_rnd_buffer_size，这些都是主导读取数据时的线程的缓存大小，对于order by group by 顺序扫描以及大表的join等都有提升效率的功能。具体与每个数据库承载的业务和撰写的SQL复杂度和调取数据量有关。

同时对于optimizer_switch ，里面那一堆的参数也要有所取舍，尤其对于group by 语法的参数在5.X 升级到8的兼容性要进行测试，否则升级版本在这块可能会失败，导致升级回退，sort_buffer_size, tmp_table_size等在需要排序和临时表的时候，也要设置对应的内存帮助加速查询。

在MySQL优化中，更多的是拆表，且集中在逻辑业务拆分，而不是使用MySQL的分区表，到现在我还是这个观点，MySQL不要用分区表，原因之前的文章有写过。

同时老的MySQL的DBA 还会思考主键的问题，主键越小越好，这与MySQL的原理息息相关，更小的主键会让数据更多在Btree的上层，而不是下层，分表的原因也是这个，所以那时我们会纠结主键大小的问题。

关于改写SQL的问题，在MySQL是家常便饭，尤其是一些军规和什么SQL撰写的要求。列两条让传统的MySQL DBA回忆一下。

避免 SELECT *，SELECT * 会取所有列，可能让中间结果变大。改写成只取需要的列：SELECT id, name FROM users。

EXISTS 优于 IN（大子查询时）

IN (SELECT …) 在结果集很大时会生成临时表。改写：EXISTS (SELECT 1 FROM … WHERE condition)。

JOIN 代替子查询

有时子查询会产生巨大的中间结果。改写成 JOIN 并配合索引，减少中间表

避免大范围 OR，用 UNION ALL

WHERE col = 'a' OR col = 'b' 可能导致索引失效。

比如下面的SQL

SELECT u.user_id, u.user_name
FROM users u
WHERE u.user_id IN (
    SELECT o.user_id
    FROM orders o
    WHERE o.order_date >= '2024-01-01'
    GROUP BY o.user_id
    HAVING SUM(o.amount) > 1000
);

可以改写成

SELECT u.user_id, u.user_name
FROM users u
JOIN (
    SELECT o.user_id
    FROM orders o
    WHERE o.order_date >= '2024-01-01'
    GROUP BY o.user_id
    HAVING SUM(o.amount) > 1000
) t ON u.user_id = t.user_id;

还可以改写成

SELECT u.user_id, u.user_name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
      AND o.order_date >= '2024-01-01'
    GROUP BY o.user_id
    HAVING SUM(o.amount) > 1000
);

留一个小问题，这两个方案有什么不同，第二个方案适合什么场景？？

最后一句，建议升级到8.0版本的原因，我简单说几个

1 Hash join

2 倒序索引

3 窗口函数提供

4 CTE通用表达式的提供

5 虚拟列和JOIN 的执行计划的完善

6 使用更好的直方图统计方式

这里举几个简单的例子

1 Hash Join

EXPLAIN FORMAT=tree
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

在8.018这个版本后，支持Hash join，当有两个大表JOIN的时候，可以使用hash join,而不是nested loop 的方式

2 倒序排序早先MYSQL不支持倒序索引，全部都是正序，倒序SQL执行效率低

CREATE INDEX idx_order_date_desc ON orders(order_date DESC);

EXPLAIN SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

3 窗口函数的提供

在MYSQL8提供了一些先进的函数，比如下面的SQL，你看看

SELECT customer_id, order_date,
       @rn := IF(@prev_customer = customer_id, @rn + 1, 1) AS rn,
       @prev_customer := customer_id
FROM (
    SELECT customer_id, order_date
    FROM orders
    ORDER BY customer_id, order_date
) t,
(SELECT @rn := 0, @prev_customer := NULL) vars;

如果到了MYSQL8 可以写成

SELECT customer_id, order_date,
       ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders;

当然最后还有我们熟悉的虚拟列，这个部分文章较多就不赘述了。

在早先的MySQL DBA值钱的原因之一，是要动一些架构的知识，也就是会逻辑业务分库，在懂得业务，配合架构知识，对于整体数据的一个拆分，这是早先 MSSQL ,ORACLE DBA 不具备的知识，所以 MySQL DBA本身值钱的部分是对于整体知识的把控，而到了现在这些被架构师拿走了，MySQL DBA的辉煌也随着MySQL本身的迭代和本身的数据处理能力的增强，成为了过去式。

这里举一个例子，

比如电商网站中的