加速你的数据库：公司中SQL优化的八大绝招

前言

????????SQL优化这个问题也是老生常谈了，很多都还是在八股文中了解到，那么公司里的SQL都是咋优化的呢？和八股文一样吗？下面，我将与大家分享我在公司里学到的SQL优化知识。SQL优化是提高数据库性能和减少资源消耗的重要一环，在我工作的过程中，我深刻体会到了SQL优化的重要性，并积累了一些实用的技巧和经验，我希望通过这篇博客，将这些知识分享给大家。

LIKE的左模糊查询优化

不足点

使用 LIKE 进行左模糊查询可能导致索引失效的原因主要有两点：

1. LIKE 模糊匹配规则：左模糊查询中，使用 % 通配符开头的模式，例如 %phone，在搜索过程中需要对索引进行全索引扫描，因为索引是按照顺序存储的，无法利用索引的 B-Tree 属性进行快速定位。这将导致查询时需要遍历整个索引，从而降低查询效率。
2. 索引列顺序：如果索引列的顺序与查询条件的顺序不一致，也会导致索引失效。例如，如果索引是 (name, price)，但是查询条件是 LIKE '%phone' AND price < 1000，这样的查询无法充分利用索引，因为索引的第一列 name 并没有在查询条件中使用，导致索引无法高效匹配查询条件。

优化点

????????优先考虑使用全文搜索来进行模糊查询，可以大幅提高查询效率。因为全文搜索使用了特殊的索引类型（如InnoDB全文索引或MyISAM全文索引），而左模糊查询则使用普通的B树索引。全文索引能够以文本内容的方式存储数据，并使用一定的算法构建索引，从而快速准确地找到文本数据中的匹配项，有效利用了索引。但左模糊查询需要扫描整个索引目录才能找到符合条件的记录，效率较低。因此，在适合的情况下，使用全文搜索可以提高查询效率。

优化实现

1. 添加全文索引：首先，需要为需要进行模糊查询的列添加全文索引。假设我们以 products 表中的 name 列为例，可以使用以下语句添加全文索引：

ALTER TABLE products ADD FULLTEXT(name);

2. 使用 MATCH AGAINST 进行全文搜索：接下来，使用 MATCH AGAINST 语句来进行全文搜索。使用 MATCH 子句指定要搜索的列，并使用 AGAINST 子句指定要搜索的模糊关键字。

SELECT * FROM products WHERE MATCH(name) AGAINST ('phone' IN NATURAL LANGUAGE MODE);

????????在上述示例中，使用 MATCH(name) 来指定要搜索的列 name，并使用 AGAINST ('phone' IN NATURAL LANGUAGE MODE) 来指定要搜索的关键字，其中 phone 是模糊匹配的关键字。

---

NULL值处理优化

不足点

????????对于 null 的判断可能会导致数据库引擎放弃使用索引而进行全表扫描。在数据库中，索引是用于加快数据检索的结构，它可以帮助数据库引擎快速定位需要的数据行。然而，当对一个列进行 null 值的判断时，由于 null 不属于实际的数据值，数据库引擎无法使用索引来加速查找，只能对整个表进行扫描来判断是否满足条件，此外，除了性能问题，对于业务而言，当遇到为NULL的数据，需要给他一个默认值，而非返回一个空数据，但是如果直接在表结构中声明该字段的默认值，那么就难以满足不同业务点所需不同默认值的情况。

优化点

1. 可以通过 COALESCE 函数，COALESCE 函数可以用于检查多个列的空值，然后返回第一个非空的值（有点EXIST关键字的感觉）。这可以减少对每个列进行单独的 NULL 检查，简化查询语句的逻辑
2. 使用 IFNULL 函数，它的作用似于Map集合的 getOrDefault 函数，用于判断一个表达式是否为NULL，当达式为NULL时返回一个指定的替代值。
3. 使用索引，如果你经常需要检查一个列是否为 NULL，可以考虑为该列添加一个索引。索引可以帮助数据库更高效地定位包含 NULL 值的行，从而提高查询性能。

优化实现

使用 IS NULL 进行判空的 SQL 示例：

SELECT * FROM table WHERE column IS NULL;

上述示例中的 table 是表名，column 是要判断是否为空的列名。它将返回表中列值为空的所有行。

使用 COALESCE 进行优化的 SQL 示例：

SELECT COALESCE(column1, column2, column3) AS result FROM table; # COALESCE 函数将按照参数的顺序检查 column1、column2、column3 是否为空，并返回第一个非空的值作为结果。

使用 IFNULL 进行优化的 SQL 示例：

SELECT IFNULL(column, '替换的数据，注意数据类型') AS column_value FROM table; # IFNULL 函数将检查 column1 是否为 NULL，如果是，则将其替换为指定数据。

---

大表查询优化

不足点

????????当一张表的数据非常多的时候，比如单个.myd文件都达到10G，这时，必然读取起来效率降低。深分页问题，数据检索慢等问题都比较频繁，哪怕加上索引都难以满足预期需求，

优化点

????????对于这种情况可以考虑将数据按某字段数值进行分区切割，常见的有两种方案，通过数据库中间件MyCat进行分库分表，或者通过数据库自带的 PARTITION 函数进行数据分片。PARTITION 函数更加适合在单一数据库上进行数据的分区管理，而 MyCat 则适合将数据分布到多个物理服务器上进行横向扩展和提高性能。选择哪种分表方式应根据实际的需求、架构和使用场景来确定。

优化实现

（这里主要介绍PARTITION分区函数的优化，MyCat中间件的优化操作请参考我的MyCat 2全套学习笔记）

1. 添加分区键：在表创建之后，通过 ALTER TABLE 语句添加分区键。

ALTER TABLE mytable ADD PARTITION BY RANGE(id)(
 ? PARTITION p0 VALUES LESS THAN (100),
 ? PARTITION p1 VALUES LESS THAN (200),
 ? PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

????????当执行插入操作后，mysql将会根据指定的规则，把数据放在不同的表文件上，相当于在文件上，被拆成了小块

2. 查询数据：使用查询语句可以按照分区键的规则查询分区表的数据。

SELECT * FROM mytable; ? ? ? ? ? ? ? -- 查询所有分区的数据
SELECT * FROM mytable PARTITION(p0); ?-- 查询指定分区（p0）的数据
SELECT * FROM mytable WHERE id < 200; -- 查询符合条件的分区数据

3. 修改分区规则：如果需要修改分区规则，可以使用 ALTER TABLE 语句进行更改。

ALTER TABLE mytable REORGANIZE PARTITION p2 INTO (PARTITION p2 VALUES LESS THAN (300));

4. 删除分区：如果需要删除分区，可以使用 ALTER TABLE 语句进行删除。

ALTER TABLE mytable DROP PARTITION p2;

---

避免列上函数和运算

不足点

????????在 WHERE 子句中，如果索引列是计算或者函数的一部分，DBMS 的优化器将不会使用索引而使用全表扫描。同样的，不能在索引列上使用函数，因为函数也是一种计算，会造成全表扫描。索引失效的原因是索引是针对原值建的二叉树，将列值计算后，原来的二叉树就用不上了，从而导致全表扫描；

优化点

????????其实本质而言是针对列值计算后，原二叉树失效问题，那么我们优化方案就要避免改动列值，以下主要方式有：1.通过模糊查询等价转换函数效果 2. 运算操作移到查询条件内

优化实现

-- 错误示例：函数应用于列上
SELECT * FROM users WHERE YEAR(name) = 2022;
?
-- 优化示例：将函数应用于查询条件的常量值
SELECT * FROM users WHERE name LIKE '2022%';

-- 错误示例：在列上进行运算
SELECT * FROM users WHERE age + 10 = 30;
?
-- 优化示例：将运算操作移到查询条件内
SELECT * FROM users WHERE age = 30 - 10;

?

---

大数据量操作EXISTS替代IN / WHERE

不足点

????????数据量较大时，使用 IN 或 WHERE 子句可能导致查询性能下降。这是因为 IN 或 WHERE 子句需要分别检查每个值是否存在于目标数据集中，这会导致大量的比较操作和索引查找，增加查询的时间复杂度。并且会将目标数据集一次性加载到内存中进行比较，这可能导致内存不足或者大量的内存交换操作，从而影响整个系统的性能。

优化点

????????在某些情况下，使用 EXISTS 可以更高效地执行查询，特别是当子查询的结果集很大时。这是因为 EXISTS 子查询只需找到符合条件的第一个匹配行即可停止，而 IN 或 WHERE 子查询需要扫描整个结果集并进行比较。并且EXISTS 只需要判断是否存在满足条件的行，不需要保存整个结果集

实际上，据库查询优化器有时候可能会在执行计划中进行自动转换。例如，当存在合适的索引时，优化器可能会将 IN 或 WHERE 子查询转换为 EXISTS 形式，以提高查询性能。

优化实现

使用 IN 子查询的查询示例：

SELECT column1, column2, column3
FROM table1
WHERE column1 IN (SELECT column1 FROM table2);

使用 EXISTS 替代的优化示例：

SELECT column1, column2, column3
FROM table1 AS t1
WHERE EXISTS (SELECT 1 FROM table2 AS t2 WHERE t2.column1 = t1.column1);

• 对于表格 table1 的每一行，都执行 EXISTS 子查询的判断。
• EXISTS 子查询会在 table2 中查找是否存在满足条件的记录。如果存在，返回结果为真，当前行被包含在查询结果中。
• 如果不存在，返回结果为假，当前行不会被包含在查询结果中

---

深分页优化

不足点

????????MySQL深分页问题是指在查询大量数据时，使用OFFSET和LIMIT来实现分页，当偏移量较大时（例如偏移量超过百万级别），查询的性能会明显下降，导致查询时间变长。OFFSET和LIMIT实际上是在获取查询结果之后再进行偏移和截取，这意味着数据库需要扫描和跳过大量的数据。并且MySQL需要将查询到的结果排序，然后丢弃前面的偏移量部分，这对于大量数据来说，可能需要进行大量的硬盘IO操作，降低了性能。

优化点

????????其实根据以上说法，可以知道深分页产生原因在于MySQL在查询结果中跳过大量行时需要花费大量时间。例如，如果我们想获取第10000页的数据，MySQL需要先读取并跳过前9999页的数据，这将消耗大量时间。那么我们可以从两个方面进行入手，拿到上次查询的位置直接加1获取目标位置，或者使用覆盖索引 + 子查询优化查询。在进行分页查询时，我们可以记录上次查询的最后一个ID，然后在下次查询时，直接从这个ID开始查询，避免检索起始位置之前的数据。而覆盖索引是一种可以直接从索引中获取查询结果，而无需访问数据表的索引。通过使用覆盖索引，我们可以减少数据表的访问次数，从而提高查询效率。

优化实现

优化方式一：其实深分页问题的业务很多都与分页栏相关，那么我们就能够基于当前页码数据优化目标查询页码

SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT 10;

优化方式二：既然速度慢在查询到目标行需要略过很多数据，那可以通过索引来进行优化速率，我快点越过其他的id，结果获取就快一点

SELECT * FROM mysql.test WHERE id > (SELECT a.id FROM mysql.test a WHERE a.create_time > '2014-01-01 00:00:00' LIMIT 100000, 1) LIMIT 10; 

优化方式三：对于经常被访问的页面或者数据，可以将其缓存在本地或者Redis等缓存中，在第二次及以后访问时直接返回缓存结果，避免重复计算和数据库查询。

优化方式四：对于经常被访问的页面或者数据，可以将其缓存在本地或者Redis等缓存中，在第二次及以后访问时直接返回缓存结果，避免重复计算和数据库查询。

---

使用UNION ALL代替UNION

不足点

????????UNION 会自动去重，这意味着如果不需要去掉重复的行，使用 UNION会进行额外去重操作，从而降低查询性能。此外，当使用 UNION 时，数据库需要将所有的结果集合并并去重，这种操作可能会破坏每个结果集的排序，从而导致最终的结果集排序错误

优化点

????????UNION ALL 不会去重，这意味着如果不需要去掉重复的行，使用 UNION ALL 可以避免进行额外的排序和去重操作，总之，如果需要去重，或者结果集已经是有序的，则应使用 UNION；否则，如果结果集不需要去重并且具有良好的排序，则应使用 UNION ALL。

优化实现

# UNION 
SELECT column1, column2, column3
FROM table1
UNION
SELECT column1, column2, column3
FROM table2;
?
# UNION ALL
SELECT column1, column2, column3
FROM table1
UNION ALL
SELECT column1, column2, column3
FROM table2;

---

批量处理避免频繁交互

不足点

????????当需要更新的数据量特别大、SQL语句运行时间超长时。每次执行 SQL 都会涉及磁盘 I/O 操作，包括读取数据、日志写入等，特别是针对大量数据的操作，频繁的磁盘交互会导致性能的降低。并且每条 SQL 都会有网络传输的开销，包括发送 SQL 请求和接收响应，对于大量数据的处理，会产生较大的网络延迟。而且逐条执行 SQL 可能会导致频繁的加锁和解锁操作，增加了系统资源竞争的概率，可能导致锁冲突和阻塞，影响并发性能

优化点

????????在做系统优化的时候，我们总是想尽可能的减少数据库查询的次数，以减少资源占用，同时可以提高系统速度。将多个 SQL 操作合并为一个批量操作，减少了磁盘交互次数和网络通信开销。通过一次性加载和处理多个数据，减少了多次查询的次数，提高了效率，并且结合批量操作，将一系列 SQL 操作放在一个事务中执行，保证数据的一致性和完整性，同时批量提交事务可以减少事务提交的次数，减小锁竞争的范围。

优化实现

# 批量插入
INSERT INTO table_name (column1, column2, column3)
VALUES
 ? ?(value1, value2, value3),
 ? ?(value4, value5, value6),
 ?  ...
 ? ?(value7, value8, value9);

# 批量更新
Update users Set status=1 Where account IN ('xx1', 'xx2');
?
update users
 set job = case id
 ?when 1 then 'job11'
 ?when 2 then 'job12'
 end,
  age = case id
 ?when 1 then 11
 ?when 2 then 12
 end;

总结

????????通过本篇博客，我们对SQL优化进行了深入的探讨和学习。我们首先了解了SQL优化的重要性和意义，它可以提高数据库的性能和减少资源消耗，从而提升系统的效率和响应速度。但是我们也要注意，SQL优化是一个复杂而细致的工作，需要结合具体的业务需求和数据库结构进行分析和优化。每个业务场景都可能存在不同的瓶颈和问题，因此，我们需要不断学习和实践，不断优化数据库性能，以满足不断增长的业务需求。