有哪些方式优化慢 SQL？

针对慢 SQL 查询的优化，可以从两个方面入手：SQL 语句本身的优化和数据库设计的优化。

避免不必要的列

避免查询不需要的列是一个常见的优化策略。在编写 SQL 查询时，应仅查询所需的列，而不是包含额外的列。尽量避免使用类似于 SELECT * 的写法。

分页优化

在处理大数据量和深度分页的情况下，需要考虑分页的优化。

例如：

SELECT * FROM table WHERE type = 2 AND level = 9 ORDER BY id ASC LIMIT 190289, 10;

以下是一些分页优化的方案：

延迟关联

首先通过条件提取出主键，在将该表与原数据表关联，通过主键 ID 提取数据行，而不是通过原来的二级索引提取数据行。

例如：

SELECT a.* FROM table a,
(SELECT id FROM table WHERE type = 2 AND level = 9 ORDER BY id ASC LIMIT 190289, 10) b
WHERE a.id = b.id

书签方式

书签方式是找到 LIMIT 第一个参数对应的主键值，然后根据该主键值进行过滤和限制。

例如：

SELECT * FROM table WHERE id > (SELECT id FROM table WHERE type = 2 AND level = 9 ORDER BY id ASC LIMIT 190)

索引优化

合理设计和使用索引是优化慢 SQL 查询的关键。

利用覆盖索引

当使用非主键索引查询数据时，InnoDB 存储引擎会进行回表操作。但是，如果索引的叶节点已经包含所需查询的字段，就没有必要再回表查询，这就是覆盖索引。

例如，对于以下查询：

SELECT name FROM test WHERE city = '上海';

可以将被查询的字段添加到联合索引中，这样查询结果就可以直接从索引中获取：

ALTER TABLE test ADD INDEX idx_city_name (city, name);

避免低版本中使用 OR 查询

在 MySQL 5.0 之前的版本中，尽量避免使用 OR 查询，可以使用 UNION 或子查询来替代。因为早期的 MySQL 版本中，OR 查询可能导致索引失效，而高版本引入了索引合并来解决这个问题。

避免使用 != 或 <> 操作符

在 SQL 中，不等于操作符可能导致查询引擎放弃使用索引而进行全表扫描，即使比较的字段上有索引。

解决方法是将不等于操作符改为 OR 运算符，以便使用索引并避免全表扫描。

例如，将 column <> 'aaa' 改为 column > 'aaa' OR column < 'aaa'，这样就可以使用索引了。

适当使用前缀索引

适当地使用前缀索引可以减少索引占用的空间并提高查询效率。

例如，如果邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引。

ALTER TABLE test ADD INDEX index2(email(6));

需要注意的是，前缀索引也有一些缺点。MySQL 无法利用前缀索引进行 ORDER BY 和 GROUP BY 操作，并且无法作为覆盖索引使用。

避免在列上进行函数运算

避免在列字段上进行算术运算或其他表达式运算，否则可能导致存储引擎无法正确使用索引，从而影响查询效率。

SELECT * FROM test WHERE id + 1 = 50;
SELECT * FROM test WHERE MONTH(updateTime) = 7;

正确使用联合索引

使用联合索引时要注意最左匹配原则。

JOIN 优化

优化子查询

尽量使用 JOIN 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会创建临时表，并且创建和销毁临时表会占用系统资源和时间。对于返回结果集较大的子查询，其对查询性能的影响更大。

小表驱动大表

在关联查询中，要使用小表去驱动大表，因为 MySQL 内部会遍历驱动表，再进行连接被驱动表的操作。

例如，在 LEFT JOIN 中，左表是驱动表，如果表 A 小于表 B，则建立连接的次数较少，查询速度会加快。

SELECT name FROM A LEFT JOIN B;

适当增加冗余字段

增加冗余字段可以减少大量的连接查询。由于多个表的连接查询性能较低，可以适当增加冗余字段，以减少多个表的关联查询。这是以空间换时间的优化策略。

避免使用 JOIN 关联过多的表

《阿里巴巴 Java 开发手册》规定不要 JOIN 超过三个表。首先，过多的 JOIN 会降低查询速度；其次，JOIN 操作会占用更多的内存缓冲区。

如果无法避免 JOIN 多个表，可以考虑使用异构数据的方式将数据异构到 Elasticsearch 中进行查询。

排序优化

利用索引扫描进行排序

MySQL 有两种生成有序结果的方式：一种是对结果集进行排序操作，另一种是根据索引顺序扫描得出的结果自然有序。

然而，如果索引无法覆盖查询所需的列，就必须在扫描过程中每条记录回表查询一次，这种读取操作是随机 IO，通常比顺序全表扫描更慢。

因此，在设计索引时，应尽可能使用同一个索引既满足排序又用于查找行。

例如：

-- 建立索引（date, staff_id, customer_id）
SELECT staff_id, customer_id FROM test WHERE date = '2010-01-01' ORDER BY staff_id, customer_id;

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都相同时，才能使用索引进行排序。

UNION 优化

条件下推

MySQL 在处理 UNION 时会创建临时表，并将各个查询结果填充到临时表中，最后再进行查询。许多优化策略在 UNION 查询中失效，因为它无法利用索引。

最好手动将 WHERE、LIMIT 等子句下推到 UNION 的各个子查询中，以便优化器可以充分利用这些条件进行优化。

此外，除非确实需要服务器去重，一定要使用 UNION ALL。如果不加 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表进行唯一性检查，代价很高。

有哪些方式优化慢 SQL？