SQL优化：表设计优化、SQL语句优化
1. 表的设计优化
① 设置合适的数值类型（tinyint, int, bigint）
选择合适的数据类型可以节省存储空间和提高查询效率：
TINYINT：占用1字节，适合存储范围在-128到127之间的整数。INT：占用4字节，适合存储范围在-2,147,483,648到2,147,483,647之间的整数。BIGINT：占用8字节，适合存储范围在-9,223,372,036,854,775,808到9,223,372,036,854,775,807之间的整数。
选择适当的数值类型不仅节省空间，还能提高索引的效率和查询速度。
② 设置合适的字符串类型（char和varchar）
选择适当的字符串类型可以优化存储和检索效率：
CHAR(n)：固定长度，适合存储定长字符串。虽然浪费了一些空间，但由于定长，检索速度较快。VARCHAR(n)：可变长度，适合存储变长字符串。虽然节省了空间，但检索速度略低于CHAR。
具体使用时，应根据字段内容的长度和变化情况选择合适的类型。
2. SQL语句优化
① SELECT语句务必指明字段
避免使用SELECT *，明确指定所需字段可以减少返回数据量，降低I/O压力，提升查询速度。
② 避免造成索引失效的写法
索引可以大幅提升查询速度，但某些写法会导致索引失效，如：
- 在索引字段上使用函数或计算（如
WHERE YEAR(date) = 2023）。 - 使用
!=、<>、IS NULL、IS NOT NULL等。 - 在复合索引中未使用最左前缀。
为了保持索引有效，应尽量避免上述写法。
具体的索引失效情况，参见上一篇博客[MySQL八股]创建索引的规则、索引失效-CSDN博客
③ 在不需要去重的场景下，尽量使用UNION ALL代替UNION
UNION会自动进行去重操作，而UNION ALL不去重。去重操作会增加额外的计算开销，所以在确定结果集不需要去重时，使用UNION ALL可以提升效率。UNION和UNION ALL的具体对比如下：
UNION
- 功能：
UNION用于合并两个或多个结果集，并且会自动去重，即删除重复的行。 - 操作：在执行
UNION时，数据库会先执行两个查询，然后将结果集进行合并，并检查每一行是否重复。如果有重复行，会去除这些重复行，只保留唯一的行。 - 性能影响：去重操作会增加计算开销，尤其是当结果集很大时，性能影响会更加显著，因为数据库需要对整个结果集进行排序和比较，以确定哪些行是重复的。
UNION ALL
- 功能：
UNION ALL也用于合并两个或多个结果集，但不会去重。它会将所有结果直接合并，即使有重复行也会保留。 - 操作：数据库直接合并两个查询的结果集，不进行去重操作。
- 性能影响：由于没有去重操作，
UNION ALL的计算开销较小，执行速度更快。对于不需要去重的场景，使用UNION ALL可以显著提高查询性能。
这里注意，使用UNION ALL的场景，一定是
- 结果集不需要去重：如果确定结果集中的数据没有重复，或者即使有重复也不需要去重，那么使用
UNION ALL可以避免不必要的去重操作，从而提升查询性能。 - 性能需求较高：在大数据量的情况下，
UNION的去重操作可能会显著影响查询性能。此时使用UNION ALL可以减少计算开销，加快查询速度。
④ 避免在WHERE子句中对字段进行表达式操作
如在WHERE子句中对字段进行计算、函数调用或类型转换会导致索引失效，因为索引通常是在原字段上建立的，任何改变字段值的操作都可能使索引失效。尽量将计算放在常量一侧或预处理数据。
⑤ Join优化
Ⅰ. 在进行表连接时，尽量使用INNER JOIN而不是LEFT JOIN或RIGHT JOIN，因为INNER JOIN通常更高效。
INNER JOIN
- 功能：
INNER JOIN返回两个表中满足连接条件的匹配记录。如果记录在任一表中没有匹配，则不会出现在结果集中。 - 用法：常用于需要两个表中都有相应记录的情况。
- 性能：通常比
LEFT JOIN或RIGHT JOIN更高效，因为它只返回匹配的记录，不需要处理未匹配的记录。
下面举个例子，方便理解。
假设有两个表employees和departments：
employees:
+----+--------+-------------+
| id | name | department_id|
+----+--------+-------------+
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie| 3 |
+----+--------+-------------+
departments:
+----+------------+
| id | department |
+----+------------+
| 1 | HR |
| 2 | Engineering|
+----+------------+
INNER JOIN查询：
SELECT e.name, d.department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
结果：
+--------+------------+
| name | department |
+--------+------------+
| Alice | HR |
| Bob | Engineering|
+--------+------------+
只有department_id在两个表中都匹配的记录才会被返回。
LEFT JOIN
- 功能：
LEFT JOIN返回左表中的所有记录，以及右表中匹配的记录。如果右表没有匹配的记录，则结果集中右表的字段将为NULL。 - 用法：常用于需要返回左表中的所有记录，无论右表是否有匹配记录的情况。
- 性能：因为需要处理未匹配记录，性能可能比
INNER JOIN低。
LEFT JOIN查询：
SELECT e.name, d.department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
结果：
+--------+------------+
| name | department |
+--------+------------+
| Alice | HR |
| Bob | Engineering|
| Charlie| NULL |
+--------+------------+
所有员工都会被返回，即使department_id在departments表中没有匹配的记录。
RIGHT JOIN
- 功能：
RIGHT JOIN返回右表中的所有记录，以及左表中匹配的记录。如果左表没有匹配的记录，则结果集中左表的字段将为NULL。 - 用法：常用于需要返回右表中的所有记录，无论左表是否有匹配记录的情况。
- 性能：因为需要处理未匹配记录，性能可能比
INNER JOIN低。
RIGHT JOIN查询：
SELECT e.name, d.department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
结果：
+--------+------------+
| name | department |
+--------+------------+
| Alice | HR |
| Bob | Engineering|
| NULL | Marketing |
+--------+------------+
所有部门都会被返回，即使department_id在employees表中没有匹配的记录。
总结
INNER JOIN：返回匹配记录。高效，适合只需要匹配记录的情况。LEFT JOIN：返回左表的所有记录和右表的匹配记录。适合需要所有左表记录的情况，即使没有匹配。RIGHT JOIN：返回右表的所有记录和左表的匹配记录。适合需要所有右表记录的情况，即使没有匹配。
选择合适的连接方式不仅能满足业务需求，还能优化查询性能。通常在不需要未匹配记录的情况下，优先使用INNER JOIN。
另外，在进行JOIN操作时，尽量把小表放在驱动表的位置，这样可以减少中间结果集的大小，提高效率。
Ⅱ. 在进行JOIN操作时，将小表放在驱动表的位置可以减少中间结果集的大小，从而提高查询效率。驱动表是用于扫描和匹配其他表的表，通常是JOIN子句中先被扫描的表。
为什么小表作为驱动表更高效呢？
- 扫描效率：小表的数据量较小，扫描和匹配速度较快。
- 内存占用：小表占用内存较少，减少了内存压力。
- 临时结果集：在连接操作中，临时结果集的大小决定了查询的整体性能。小表作为驱动表可以减少临时结果集的大小，提高效率。
假设table1是小表，table2是大表，优化后的JOIN操作如下：
SELECT a.*, b.*
FROM table1 a
INNER JOIN table2 b ON a.id = b.id;
这种写法将小表table1作为驱动表，首先扫描和匹配小表的数据，再与大表进行连接。
⑥ 读写分离
在读多写少的场景下，采用读写分离的架构可以有效提升性能。具体做法是将数据库分为主库（写操作）和从库（读操作），通过复制将数据从主库同步到从库。这样，读操作和写操作分别由不同的服务器处理，减少了写操作对读操作的影响，提高了整体性能。
1335
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
