SQL优化：让你的数据库飞起来

欢迎大家来到IT世界,在知识的湖畔探索吧!

SQL 优化有多重要？

欢迎大家来到IT世界,在知识的湖畔探索吧!

在数字化时代，数据量呈爆发式增长，数据库作为数据存储和管理的核心，其性能直接关系到整个系统的稳定运行和用户体验。而 SQL 优化，正是提升数据库性能的关键所在。

想象一下，你在电商平台上购物，点击搜索按钮后，页面却迟迟没有响应，等待的时间让人烦躁。这很可能是因为数据库的查询语句没有经过优化，导致响应迟缓。对于电商平台来说，每一次搜索都是一次潜在的交易机会，查询响应时间的延长可能会让用户失去耐心，转而选择其他平台，从而导致客户流失和业务损失。据研究表明，网页加载时间每增加一秒，用户流失率可能会上升 7%。对于数据库驱动的应用程序来说，SQL 查询的性能直接影响着网页的加载速度和响应时间。

再比如，在金融系统中，每天都要处理海量的交易数据。如果 SQL 查询效率低下，不仅会影响日常的交易处理速度，还可能导致在统计报表、风险评估等关键业务时出现延迟，给金融机构带来巨大的风险。在高并发的场景下，低效的 SQL 甚至可能导致数据库服务器负载过高，出现死机或崩溃的情况，造成严重的业务中断。

从技术层面来看，优化后的 SQL 查询能够显著减少数据库的资源消耗。在数据库中，查询的执行需要消耗 CPU、内存、磁盘 I/O 等资源。一条未经优化的 SQL 语句可能会导致全表扫描，大量的数据从磁盘读取到内存，占用大量的 I/O 资源和内存空间，同时 CPU 也需要花费大量的时间来处理这些数据。而优化后的 SQL 可以通过合理使用索引、优化查询语句结构等方式，减少数据的读取量和处理量，从而降低 CPU 和内存的使用率，提高磁盘 I/O 的效率 。这不仅可以提升单个查询的执行速度，还能让数据库在有限的硬件资源下处理更多的并发请求，提高系统的整体吞吐量。

优化前的准备工作

在对 SQL 进行优化之前，我们需要先找到那些 “拖后腿” 的 SQL 语句，并了解它们的执行情况 。这就好比医生在治病之前，需要先通过各种检查手段来确定病因。在 SQL 优化中，我们也有一系列的 “检查工具”。

慢查询日志

慢查询日志是 MySQL 提供的一个非常实用的工具，它可以记录执行时间超过指定阈值的 SQL 语句。通过分析慢查询日志，我们可以很容易地找出那些执行效率低下的 SQL。在 MySQL 中，我们可以通过修改配置文件来开启慢查询日志，并设置阈值。例如，在 my.cnf 文件中添加以下配置：

slow_query_log = 1

欢迎大家来到IT世界,在知识的湖畔探索吧!

slow_query_log_file = /var/log/mysql/slow-query.log

long_query_time = 2

这表示开启慢查询日志，日志文件路径为
/var/log/mysql/slow-query.log，执行时间超过 2 秒的 SQL 语句将被记录下来。日志记录格式如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!# Time:  10:23:45

# User@Host: root[root] @ localhost []

# Query_time: 3. Lock_time: 0.000123 Rows_sent: 100 Rows_examined:

SELECT * FROM users WHERE age > 30;

从日志中我们可以清楚地看到查询的时间、锁时间、返回的行数以及扫描的行数等信息，这些信息对于我们分析 SQL 性能非常有帮助。

EXPLAIN

EXPLAIN 关键字可以让我们查看 SQL 语句的执行计划，它会展示 MySQL 如何执行查询，包括表的连接顺序、使用的索引、扫描的行数等信息。例如，对于以下查询：

EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';

执行结果可能如下：

通过分析执行计划，我们可以看到 type 为 ALL，表示全表扫描，这可能是导致查询效率低下的原因。我们可以进一步优化，比如在 order_date 列上添加索引，以提高查询效率。

SHOW PROFILE

SHOW PROFILE 可以更详细地分析 SQL 语句的执行资源消耗情况，包括 CPU、I/O、内存等。首先，我们需要开启 profiling 功能：

欢迎大家来到IT世界,在知识的湖畔探索吧!SET profiling = 1;

然后执行需要分析的 SQL 语句，例如：

SELECT * FROM products WHERE category = 'electronics';

最后，使用 SHOW PROFILE 命令查看分析结果：

欢迎大家来到IT世界,在知识的湖畔探索吧!SHOW PROFILE FOR QUERY 1;

这里的 1 表示查询语句的 ID，可以通过 SHOW PROFILES 查看所有查询的 ID。结果会展示 SQL 执行过程中各个阶段的状态和耗时，帮助我们精准定位性能瓶颈。比如，如果发现某个阶段的 CPU 或 I/O 消耗过高，我们就可以针对性地进行优化。

trace

MySQL 5.6 及以上版本引入了 optimizer trace 功能，它可以提供更深入的查询优化器执行信息。通过开启 trace，我们可以了解查询优化器在选择执行计划时的决策过程。首先，设置 trace 相关参数：

SET optimizer_trace = "enabled=on",end_markers_in_json=on;

然后执行 SQL 语句，执行完成后，查询
information_schema.optimizer_trace 表来获取 trace 信息：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM information_schema.optimizer_trace\G

这些信息以 JSON 格式展示，包含了查询优化器考虑的各种执行计划以及最终选择的原因，对于我们深入理解 SQL 执行和优化非常有帮助。

SQL 优化技巧大揭秘

在了解了 SQL 优化的重要性以及如何找到需要优化的 SQL 语句后，接下来就进入关键的优化环节。SQL 优化涉及多个方面，包括索引优化、查询语句优化、数据库设计优化以及数据库配置优化等。下面我们将详细介绍这些优化技巧，并结合实际案例进行分析。

索引优化

索引就像是数据库的 “目录”，能够大大加快数据的检索速度 。它的原理是通过特定的数据结构（如 B 树、哈希表等）对数据进行排序和存储，使得数据库在查询时可以快速定位到所需的数据行，而无需扫描整个表。例如，在一个包含百万条记录的用户表中，如果要查询某个特定用户的信息，没有索引的情况下，数据库可能需要逐行扫描这百万条记录，而有了索引，就可以直接定位到该用户所在的行，查询效率大幅提升。

在创建索引时，有一些最佳实践需要遵循：

• 选择合适的列：应选择那些在查询条件（WHERE 子句）、连接条件（JOIN 子句）或排序条件（ORDER BY 子句）中经常出现的列创建索引。比如在订单表中，如果经常根据订单日期查询订单，那么在订单日期列上创建索引会很有帮助。同时，要选择选择性高的列，即列中不同值的数量相对较多的列。例如，用户表中的身份证号列，每个用户的身份证号都是唯一的，选择性非常高，适合创建索引；而性别列，只有男、女两种值，选择性低，一般不单独创建索引。

• 避免过多索引：虽然索引能提高查询性能，但并不是越多越好。过多的索引会占用大量的磁盘空间，因为每个索引都需要额外的存储空间来存储索引数据结构。同时，在进行数据插入、更新和删除操作时，数据库需要同时更新索引，这会增加操作的时间开销，降低写入性能。例如，在一个频繁进行数据更新的表中，如果创建了过多不必要的索引，可能会导致更新操作变得非常缓慢。

• 使用复合索引遵循最左前缀原则：复合索引是包含多个列的索引。在创建复合索引时，列的顺序非常重要，需要遵循最左前缀原则。例如，创建了一个复合索引（col1, col2, col3），那么这个索引可以用于查询条件为 col1 = value1、col1 = value1 AND col2 = value2、col1 = value1 AND col2 = value2 AND col3 = value3 的情况，但不能用于仅查询 col2 = value2 或 col3 = value3 的情况。这是因为数据库在使用复合索引时，是从左到右依次匹配查询条件的。

• 利用覆盖索引：如果索引包含了查询所需的所有列，那么就可以避免回表操作，这种索引称为覆盖索引。回表操作是指数据库通过索引找到数据的主键后，再根据主键去数据表中查询其他列的数据，这会增加查询的时间开销。例如，查询语句为 SELECT col1, col2 FROM table WHERE col1 = value，若在 col1 和 col2 上创建了复合索引，那么这个索引就是覆盖索引，数据库可以直接从索引中获取所需数据，而无需回表查询，从而提高查询效率。

以一个电商订单表为例，假设表结构如下：

CREATE TABLE orders (

order_id INT PRIMARY KEY,

user_id INT,

order_date DATE,

order_amount DECIMAL(10, 2),

status VARCHAR(20)

);

如果经常执行这样的查询：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM orders WHERE user_id = 12345 AND order_date > '2023-01-01';

可以在 user_id 和 order_date 列上创建复合索引：

CREATE INDEX idx_user_date ON orders (user_id, order_date);

优化前，查询可能需要全表扫描，扫描行数为表中的总行数；优化后，通过索引可以快速定位到符合条件的数据，扫描行数大幅减少，查询性能得到显著提升 。

查询语句优化

查询语句的优化是 SQL 优化的核心部分，通过合理编写查询语句，可以避免不必要的计算和数据检索，从而提高查询性能。以下是一些常见的查询语句优化方法：

• 避免使用 SELECT *：在查询时，尽量只选择需要的列，而不是使用 SELECT *。使用 SELECT * 会返回表中的所有列，这不仅会增加网络传输的数据量，还会增加数据库的处理负担。例如，在一个包含大量列的用户表中，如果只需要查询用户的姓名和年龄，使用 SELECT * 会返回所有列的数据，包括一些不必要的列，如用户地址、电话号码等，而使用 SELECT name, age FROM users 则只返回所需的两列数据，大大减少了数据传输和处理的开销。

• 减少子查询：子查询是指在一个查询语句中嵌套另一个查询语句。子查询虽然可以实现复杂的查询逻辑，但通常会降低查询性能。因为子查询需要先执行内部查询，再将结果用于外部查询，这会增加查询的执行时间和资源消耗。例如，以下查询：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE city = 'Beijing');

可以改写为 JOIN 查询：

SELECT orders.* FROM orders

JOIN users ON orders.user_id = users.user_id

WHERE users.city = ‘Beijing’;

JOIN 查询通常比子查询更高效，因为它可以在一次扫描中完成数据的匹配和筛选。

• 用 JOIN 替代子查询：如上述例子所示，JOIN 操作可以将多个表的数据根据关联条件进行连接，避免了子查询的多次查询操作。JOIN 有多种类型，包括内连接（INNER JOIN）、左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN） 。选择合适的 JOIN 类型取决于具体的业务需求。例如，内连接只返回两个表中满足连接条件的行，而左外连接则返回左表中的所有行以及右表中满足连接条件的行。在使用 JOIN 时，要确保连接条件的正确性和高效性，避免出现笛卡尔积（CROSS JOIN），即没有连接条件的 JOIN 操作，这会导致结果集的行数是两个表行数的乘积，数据量会急剧增加，严重影响查询性能。

• 合理使用 IN 和 EXISTS：IN 和 EXISTS 都可以用于子查询中进行条件判断，但它们的执行方式和性能有所不同。IN 子查询是先执行子查询，然后将结果与主查询进行比较；而 EXISTS 子查询则是先执行主查询，然后根据主查询的每一行去判断子查询是否存在匹配的行。一般来说，当子查询返回的结果集较小时，IN 和 EXISTS 的性能差异不大；但当子查询返回的结果集较大时，EXISTS 可能会更高效，因为它不需要将子查询的结果全部存储起来进行比较。例如：

欢迎大家来到IT世界,在知识的湖畔探索吧!-- 使用IN

SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE quantity > 10);

— 使用EXISTS

SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM order_items WHERE order_items.order_id = orders.order_id AND quantity > 10);

在实际应用中，可以通过 EXPLAIN 分析两种方式的执行计划，选择性能更优的方式。

• 避免在 WHERE 子句中使用函数和表达式：在 WHERE 子句中对字段进行函数操作或表达式计算会导致数据库无法使用索引，从而进行全表扫描。例如：

-- 无法使用索引，全表扫描

SELECT * FROM users WHERE YEAR(birth_date) = 1990;

应改为：

欢迎大家来到IT世界,在知识的湖畔探索吧!-- 可以使用索引

SELECT * FROM users WHERE birth_date >= ‘1990-01-01’ AND birth_date < ‘1991-01-01’;

同样，避免在 WHERE 子句中对字段进行算术运算、类型转换等操作，以保证索引的正常使用。

• 避免 OR 条件：OR 条件可能会导致数据库无法使用索引，尤其是当 OR 连接的两个条件分别涉及不同的索引列时。例如：

-- 可能无法使用索引

SELECT * FROM products WHERE category = ‘electronics’ OR price < 100;

可以改为使用 UNION ALL：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM products WHERE category = 'electronics'

UNION ALL

SELECT * FROM products WHERE price < 100;

这样可以分别使用 category 和 price 列上的索引，提高查询效率。但要注意，UNION ALL 会保留重复的行，如果不需要重复行，可以使用 UNION，但 UNION 会对结果进行去重操作，性能会略低于 UNION ALL。

• 使用 UNION ALL 替代 UNION：如前所述，UNION ALL 只是简单地将两个结果集合并在一起，不会进行去重操作，因此性能比 UNION 更高。只有在确实需要对结果集去重时才使用 UNION。例如，有两个查询分别返回不同条件下的用户数据，需要将它们合并：

-- 使用UNION ALL

SELECT user_id, username FROM users WHERE age > 30

UNION ALL

SELECT user_id, username FROM users WHERE gender = ‘female’;

• 优化 LIKE 语句：LIKE 语句用于模糊查询，但如果使用不当，也会影响性能。例如，以通配符 % 开头的 LIKE 查询（如 LIKE ‘% keyword’）无法使用索引，会导致全表扫描。可以尽量避免这种情况，或者使用全文索引来实现高效的模糊查询。如果必须使用以 % 开头的 LIKE 查询，可以考虑对数据进行预处理，例如将常用的查询关键词建立反向索引，通过反向查询来提高效率。例如，对于查询以 “abc” 结尾的字符串，可以将所有字符串反转后存储，查询时将 “cba” 作为关键词进行查询，然后再将结果反转回来。

• 用批量操作替代循环操作：在进行数据插入、更新或删除时，如果需要对多条数据进行相同的操作，使用批量操作比循环操作更高效。例如，使用 INSERT INTO… VALUES (value1), (value2), (value3) 一次性插入多条数据，比多次执行 INSERT INTO… VALUES (value) 要快得多。这是因为批量操作可以减少数据库的事务开销和网络传输次数，提高操作效率。在更新和删除操作中，也可以使用类似的批量操作方式，通过条件筛选出需要操作的多条数据，一次性进行处理。

以一个员工信息查询为例，假设员工表结构如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!CREATE TABLE employees (

employee_id INT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

department VARCHAR(50),

salary DECIMAL(10, 2)

);

原始查询语句：

SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;

优化后，避免使用 SELECT *，只选择需要的列：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT employee_id, first_name, last_name, salary FROM employees WHERE department = 'HR' AND salary > 50000;

假设该查询还涉及一个子查询，用于获取部门为 “HR” 的员工所在的项目信息：

SELECT * FROM projects WHERE project_id IN (SELECT project_id FROM employee_projects WHERE employee_id IN (SELECT employee_id FROM employees WHERE department = 'HR'));

优化为 JOIN 查询：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT projects.* FROM projects

JOIN employee_projects ON projects.project_id = employee_projects.project_id

JOIN employees ON employee_projects.employee_id = employees.employee_id

WHERE employees.department = ‘HR’;

通过这些优化，查询的性能得到了显著提升，减少了数据传输量和查询执行时间。

数据库设计优化

数据库设计是 SQL 性能的基础，合理的数据库设计可以减少数据冗余，提高查询效率。在数据库设计层面，可以采取以下优化策略：

• 合理规范化和反规范化：规范化是指将数据分解为多个表，通过关系来维护数据的完整性和一致性，遵循第一范式（1NF）、第二范式（2NF）和第三范式（3NF）等范式规则。规范化的优点是数据冗余少，更新操作简单，数据一致性容易维护；缺点是查询时可能需要进行多个表的连接操作，增加查询复杂度和性能开销。反规范化则是在一定程度上打破范式规则，通过增加数据冗余来减少表连接，提高查询性能。例如，在一个订单系统中，订单表和客户表是两个独立的表，按照规范化设计，订单表中只存储客户 ID，通过外键关联客户表获取客户信息。但如果在查询订单时经常需要同时获取客户信息，为了减少表连接，可以在订单表中冗余一些客户的基本信息，如客户姓名、地址等。在实际应用中，需要根据业务需求和查询场景来平衡规范化和反规范化，在保证数据完整性的前提下，提高查询性能。

• 使用分区表：对于数据量非常大的表，可以使用分区表将数据按照某个规则（如时间、地域等）进行划分，存储在不同的物理文件中。例如，一个订单表每年的数据量都很大，可以按照年份进行分区，每个分区对应一年的数据。这样在查询时，如果查询条件包含分区字段（如查询某一年的订单），数据库可以直接定位到对应的分区进行查询，而无需扫描整个表，大大提高了查询效率。同时，分区表也便于数据的管理和维护，如数据的备份、删除等操作可以在单个分区上进行，而不影响其他分区的数据。

• 选择合适的数据类型：在设计表结构时，要根据数据的实际范围和精度选择合适的数据类型。例如，对于整数类型，如果数据范围较小，可以选择 TINYINT 或 SMALLINT，而不是使用 INT，这样可以节省存储空间，提高查询性能。对于字符串类型，要根据实际存储的字符串长度选择合适的 VARCHAR 或 CHAR 类型，避免使用过长的字符串类型浪费空间。同时，要注意避免使用 TEXT 或 BLOB 类型，因为这些类型的数据存储和检索效率较低，如果确实需要存储大量文本或二进制数据，可以考虑将其存储在文件系统中，在数据库中只存储文件路径或引用。

• 设置字段为 NOT NULL：尽量将表中的字段设置为 NOT NULL，除非该字段确实允许为空。这是因为数据库在处理 NULL 值时需要额外的存储空间和处理逻辑，会增加查询和更新的复杂度。例如，在一个用户表中，如果用户名字段不允许为空，将其设置为 NOT NULL 可以提高数据的完整性和查询效率。同时，在插入数据时，如果字段设置为 NOT NULL，数据库可以更快地进行数据验证和插入操作。

• 避免使用 TEXT 类型：TEXT 类型用于存储大量的文本数据，但它的存储和检索方式与普通字符类型不同，会导致查询性能下降。如果需要存储较长的文本，可以考虑使用 VARCHAR (MAX) 来代替 TEXT 类型，VARCHAR (MAX) 在一定程度上可以提供更好的性能。如果文本数据确实非常大，并且查询操作较少，可以考虑将其存储在文件系统中，在数据库中存储文件路径，通过文件系统来管理和访问这些文本数据。

• 适当冗余字段：如前面提到的反规范化，适当冗余一些字段可以减少表连接，提高查询效率。但要注意冗余字段的更新一致性问题，确保在数据更新时，冗余字段也能得到正确的更新。例如，在一个博客系统中，文章表和作者表是两个独立的表，文章表中存储作者 ID。如果在查询文章列表时经常需要显示作者姓名，可以在文章表中冗余作者姓名字段，这样在查询文章列表时就不需要连接作者表，提高了查询速度。但在作者姓名发生变化时，需要同时更新文章表和作者表中的姓名字段，以保证数据的一致性。

以一个电商商品表为例，假设最初的表设计如下：

CREATE TABLE products (

product_id INT PRIMARY KEY,

product_name VARCHAR(255),

description TEXT,

price DECIMAL(10, 2),

category_id INT,

supplier_id INT,

created_at DATETIME,

updated_at DATETIME

);

存在的问题是 description 字段使用了 TEXT 类型，查询性能可能较低；同时，category_id 和 supplier_id 需要通过连接其他表获取具体信息，增加了查询复杂度。

优化后的设计：

欢迎大家来到IT世界,在知识的湖畔探索吧!CREATE TABLE products (

product_id INT PRIMARY KEY,

product_name VARCHAR(255),

description VARCHAR(MAX),

price DECIMAL(10, 2),

category_name VARCHAR(50), — 冗余类别名称

supplier_name VARCHAR(50), — 冗余供应商名称

created_at DATETIME,

updated_at DATETIME

);

将 description 字段改为 VARCHAR (MAX)，提高查询性能；同时冗余了 category_name 和 supplier_name 字段，减少了表连接操作，在查询商品信息时可以直接获取类别和供应商名称，提高了查询效率。

数据库配置优化

数据库服务器的配置对 SQL 性能也有着重要影响，合理的配置可以充分发挥硬件资源的优势，提高数据库的处理能力。以下是一些数据库配置优化的要点：

• 调整内存参数：数据库在运行过程中需要使用内存来缓存数据和索引，因此合理调整内存参数非常关键。例如，在 MySQL 中，innodb_buffer_pool_size 参数用于设置 InnoDB 存储引擎的缓冲池大小，它决定了可以缓存多少数据和索引到内存中。如果该值设置过小，数据库可能需要频繁从磁盘读取数据，导致 I/O 性能下降；如果设置过大，可能会占用过多系统内存，影响其他进程的运行。一般来说，可以根据服务器的物理内存大小和数据库的负载情况来调整该参数，通常建议将其设置为物理内存的 50% – 80%。另外，还有 query_cache_size 参数用于设置查询缓存的大小，查询缓存可以缓存查询结果，当相同的查询再次执行时，可以直接从缓存中获取结果，提高查询速度。但要注意，查询缓存会占用内存空间，并且在数据更新频繁的情况下，缓存的维护成本较高，可能会降低性能，因此需要

优化案例实战

为了更直观地展示 SQL 优化的效果，我们来看一个具体的案例。假设我们有一个电商数据库，其中包含订单表（orders）、订单详情表（order_items）和产品表（products）。

优化前的问题分析

业务需求是查询某个时间段内销售额最高的前 10 个产品及其销售数量和销售额。原始的 SQL 查询如下：

SELECT p.product_name, 

SUM(oi.quantity) AS total_quantity,

SUM(oi.quantity * oi.price) AS total_amount

FROM orders o

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.product_id

WHERE o.order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’

GROUP BY p.product_name

ORDER BY total_amount DESC

LIMIT 10;

使用 EXPLAIN 分析该查询的执行计划：

从执行计划可以看出，三个表都进行了全表扫描（type 为 ALL），这意味着数据库需要扫描大量的数据行，导致查询效率低下。同时，由于没有使用索引，在连接表和过滤数据时，需要进行大量的比较和计算，进一步增加了查询的时间开销。

执行计划解读

• id：表示查询中各个操作的顺序，这里只有一个查询，所以 id 为 1。

• select_type：SIMPLE 表示这是一个简单查询，不包含子查询或 UNION 操作。

• table：显示查询涉及的表，依次为 orders、order_items 和 products。

• type：ALL 表示全表扫描，数据库会逐行扫描整个表来获取数据，这是最耗时的访问类型。

• possible_keys：显示可能使用的索引，这里都为 NULL，说明没有可以使用的索引。

• key：实际使用的索引，同样为 NULL，表明没有使用索引。

• key_len：如果使用索引，该字段表示索引使用的字节长度，这里没有索引，所以为 NULL。

• ref：表示哪些列或常量被用于和索引进行比较，由于没有索引，这里为 NULL。

• rows：优化器预估需要扫描的行数，orders 表预估扫描 行，order_items 表预估扫描 行，products 表预估扫描 20000 行，这些大量的扫描行数是导致查询缓慢的主要原因。

• filtered：表示通过条件过滤后预计返回的行数百分比，这里 orders 表和 order_items 表都只有 10% 的过滤率，说明大部分数据都需要扫描和处理。

• Extra：Using where 表示使用了 WHERE 子句进行过滤，但由于没有索引，过滤效率较低。

优化措施

针对上述问题，我们采取以下优化措施：

1. 索引优化：在 orders 表的 order_date 列、order_items 表的 order_id 和 product_id 列、products 表的 product_id 列上创建索引。

欢迎大家来到IT世界,在知识的湖畔探索吧!CREATE INDEX idx_order_date ON orders (order_date);

CREATE INDEX idx_order_items_order_id ON order_items (order_id);

CREATE INDEX idx_order_items_product_id ON order_items (product_id);

CREATE INDEX idx_products_product_id ON products (product_id);

2. 查询语句优化：避免在 WHERE 子句中对字段进行函数操作或表达式计算，确保索引能够正常使用。这里的查询条件o.order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’符合要求，无需修改。同时，尽量只选择需要的列，减少数据传输和处理的开销。原始查询中已经只选择了必要的列，无需进一步优化。

3. 数据库设计优化：检查数据库表的设计是否合理，是否存在数据冗余或不合理的字段类型。在这个案例中，假设表结构已经经过合理设计，无需进行大的调整。但可以考虑对数据量较大的表（如 order_items 表）进行分区，按照订单日期进行分区，这样在查询特定时间段的订单时，可以直接定位到对应的分区，减少数据扫描范围。

CREATE TABLE order_items (

order_item_id INT PRIMARY KEY,

order_id INT,

product_id INT,

quantity INT,

price DECIMAL(10, 2),

INDEX idx_order_id (order_id),

INDEX idx_product_id (product_id)

)

PARTITION BY RANGE (YEAR(order_date)) (

PARTITION p2023 VALUES LESS THAN (2024),

PARTITION p2024 VALUES LESS THAN (2025),

— 可以根据实际情况添加更多分区

);

4. 数据库配置优化：根据服务器的硬件资源和数据库的负载情况，调整数据库的配置参数。例如，增加 innodb_buffer_pool_size 的值，以提高 InnoDB 存储引擎的缓存能力，减少磁盘 I/O 操作。假设服务器有足够的内存，将 innodb_buffer_pool_size 设置为物理内存的 70%。

欢迎大家来到IT世界,在知识的湖畔探索吧!SET GLOBAL innodb_buffer_pool_size = 70 * (SELECT @@innodb_buffer_pool_pages_total * @@innodb_page_size / 1024 / 1024 / 1024);

优化后的执行计划

再次使用 EXPLAIN 分析优化后的查询执行计划：

优化后的执行计划有了显著改善：

• orders 表：type 变为 range，表示使用了索引范围扫描，通过 order_date 索引快速定位到 2023 年的订单数据，扫描行数从 减少到 1000，大大提高了查询效率。

• order_items 表：type 变为 ref，使用了 idx_order_items_order_id 索引，根据 orders 表的 order_id 快速关联到对应的订单详情，扫描行数从 减少到 5，进一步减少了数据处理量。

• products 表：type 也变为 ref，使用了 idx_products_product_id 索引，根据 order_items 表的 product_id 快速获取产品信息，扫描行数从 20000 减少到 1 。

性能提升效果

经过优化后，查询的执行时间从原来的几十秒甚至几分钟缩短到了几秒钟，性能得到了显著提升。具体的数据对比可能因数据库的实际数据量和硬件环境而有所不同，但从执行计划和实际测试结果来看，优化效果非常明显。这不仅提高了系统的响应速度，也提升了用户体验，使得电商平台能够更快速地为用户提供所需的销售数据报表，为业务决策提供有力支持。

总结与展望

SQL 优化是数据库管理和应用开发中不可或缺的一环，它对于提升数据库性能、提高系统响应速度、节省资源成本以及增强用户体验都具有重要意义。通过合理运用索引优化、查询语句优化、数据库设计优化和数据库配置优化等技巧，可以显著提升 SQL 查询的执行效率，让数据库在面对海量数据和高并发请求时依然能够稳定、高效地运行。

在实际工作中，我们要养成良好的 SQL 编写习惯，从查询语句的编写、索引的设计到数据库表结构的规划，都要充分考虑性能因素。同时，要善于利用各种工具和技术来分析和诊断 SQL 性能问题，如慢查询日志、EXPLAIN、SHOW PROFILE 和 optimizer trace 等，通过这些工具深入了解 SQL 的执行过程，找出性能瓶颈所在，并针对性地进行优化。

随着数据量的不断增长和业务需求的日益复杂，SQL 优化的挑战也在不断增加。新的数据库技术和架构不断涌现，如分布式数据库、云数据库等，这些新技术在带来更高的扩展性和可用性的同时，也对 SQL 优化提出了新的要求。我们需要持续关注数据库领域的发展动态，学习和掌握新的优化技术和方法，不断提升自己的 SQL 优化能力，以适应不断变化的业务需求和技术环境。希望大家在今后的工作中，能够将所学的 SQL 优化知识运用到实际项目中，打造出高效、稳定的数据库系统 。

20 道 SQL 优化的面试题和答案

在数据库领域，SQL 优化是一个绕不开的话题，无论是数据库管理员还是开发工程师，都需要具备扎实的 SQL 优化知识。以下整理了 20 道常见的 SQL 优化面试题及答案，希望能帮助大家更好地准备面试。

1. 索引是什么？它的作用是什么？

索引是一种特殊的数据结构，它包含了表中一列或多列的值以及指向这些值所在行的物理位置的指针。索引的作用是加快数据的检索速度，减少数据库在查询时需要扫描的数据量。例如，在一个包含大量用户记录的表中，通过在用户 ID 列上创建索引，当查询某个特定用户 ID 的记录时，数据库可以直接通过索引定位到对应的行，而无需逐行扫描整个表。

2. 索引有哪些类型？

常见的索引类型有：

• B 树索引：最常用的索引类型，适用于范围查询和排序操作。它通过 B 树的数据结构来组织索引数据，使得查询可以快速定位到目标数据。

• 哈希索引：基于哈希表实现，适用于等值查询，查询速度非常快。但不支持范围查询和排序操作，因为哈希值是无序的。

• 全文索引：用于全文搜索，适用于对文本字段进行模糊查询。它可以对文本内容进行分词和索引，从而提高文本搜索的效率。

• 空间索引：用于存储和查询空间数据，如地理坐标等。它可以快速定位到某个区域内的空间对象。

3. 什么情况下不适合创建索引？

• 数据量小的表：对于数据量较小的表，全表扫描的效率可能比使用索引更高，因为索引本身也需要占用存储空间和维护成本。

• 频繁更新的表：如果表中的数据频繁更新，每次更新都需要同时更新索引，这会增加数据库的负担，降低更新性能。

• 选择性低的列：选择性是指列中不同值的数量与总行数的比例。如果列的选择性很低，如性别列只有男、女两种值，创建索引的意义不大，因为索引无法有效减少数据扫描范围。

• 在 WHERE 子句中很少使用的列：如果某列在查询条件中很少出现，为其创建索引不会对查询性能有明显提升，反而会浪费索引空间和维护成本。

4. 如何选择合适的索引列？

• 选择在查询条件中经常出现的列：这样可以确保在查询时能够利用索引快速定位数据。例如，在订单表中，如果经常根据订单日期查询订单，那么在订单日期列上创建索引会很有帮助。

• 选择选择性高的列：选择性高意味着列中不同值的数量相对较多，这样索引能够更有效地过滤数据，减少扫描行数。例如，身份证号列每个值都唯一，选择性高，适合创建索引；而性别列选择性低，一般不单独创建索引。

• 考虑复合索引的顺序：在创建复合索引时，列的顺序应根据查询条件的使用频率和选择性来确定。遵循最左前缀原则，将最常用且选择性高的列放在前面。例如，复合索引（col1, col2, col3），如果查询条件经常是 col1 = value1 AND col2 = value2，那么 col1 和 col2 的顺序就很重要。

5. 什么是覆盖索引？

覆盖索引是指索引包含了查询所需的所有列，这样在查询时，数据库可以直接从索引中获取数据，而无需回表查询。回表操作是指数据库通过索引找到数据的主键后，再根据主键去数据表中查询其他列的数据，这会增加查询的时间开销。例如，查询语句为 SELECT col1, col2 FROM table WHERE col1 = value，若在 col1 和 col2 上创建了复合索引，那么这个索引就是覆盖索引，数据库可以直接从索引中获取所需数据，提高查询效率。

6. 如何避免全表扫描？

• 创建合适的索引：在查询条件涉及的列上创建索引，使数据库能够利用索引快速定位数据，避免全表扫描。例如，在 WHERE 子句中经常使用的列，或者 JOIN 条件中的列，都可以考虑创建索引。

• 优化查询语句：避免在 WHERE 子句中使用函数和表达式，因为这会导致数据库无法使用索引。例如，将 SELECT * FROM users WHERE YEAR (birth_date) = 1990 改为 SELECT * FROM users WHERE birth_date >= ‘1990-01-01’ AND birth_date < ‘1991-01-01’，可以使用索引，避免全表扫描。

• 合理使用分区表：对于数据量非常大的表，使用分区表将数据按照某个规则进行划分，存储在不同的物理文件中。这样在查询时，如果查询条件包含分区字段，数据库可以直接定位到对应的分区进行查询，而无需扫描整个表。

7. 子查询和 JOIN 有什么区别？性能上哪个更好？

• 子查询：是在一个查询语句中嵌套另一个查询语句。子查询先执行内部查询，再将结果用于外部查询。例如，SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE city = ‘Beijing’)，先查询出北京用户的 user_id，再根据这些 user_id 查询订单。

• JOIN：是将多个表的数据根据关联条件进行连接。例如，SELECT orders.* FROM orders JOIN users ON orders.user_id = users.user_id WHERE users.city = ‘Beijing’，通过 JOIN 将订单表和用户表连接起来，直接获取北京用户的订单。

• 性能比较：一般情况下，JOIN 比子查询更高效。因为 JOIN 可以在一次扫描中完成数据的匹配和筛选，而子查询需要多次查询，增加了查询的执行时间和资源消耗。但在某些复杂查询场景下，子查询可能更符合逻辑，具体性能还需要根据实际数据量和查询条件进行测试和分析。

8. 如何优化 LIKE 查询？

• 避免以通配符 % 开头：以 % 开头的 LIKE 查询（如 LIKE ‘% keyword’）无法使用索引，会导致全表扫描。如果必须进行这种查询，可以考虑使用全文索引，全文索引可以提供更高效的模糊查询功能。

• 使用前缀匹配：如果只是需要匹配字符串的前缀，可以将通配符放在后面，如 LIKE ‘keyword%’，这样可以使用索引，提高查询效率。

• 对数据进行预处理：例如将常用的查询关键词建立反向索引，通过反向查询来提高效率。对于查询以 “abc” 结尾的字符串，可以将所有字符串反转后存储，查询时将 “cba” 作为关键词进行查询，然后再将结果反转回来。

9. 什么是索引覆盖扫描？

索引覆盖扫描是指查询所需的数据都可以从索引中获取，而不需要访问数据表本身。当查询的列都包含在索引中时，数据库可以直接从索引树中读取数据，而无需通过索引找到主键后再回表查询数据。这种方式可以大大减少 I/O 操作，提高查询性能。例如，对于查询 SELECT col1, col2 FROM table WHERE col1 = value，如果在 col1 和 col2 上创建了复合索引，那么这个查询就可以利用索引覆盖扫描，直接从索引中获取数据。

10. 如何优化子查询？

• 用 JOIN 替代子查询：如前面所述，JOIN 操作通常比子查询更高效，因为它可以在一次扫描中完成数据的匹配和筛选。例如，将 SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE city = ‘Beijing’) 改写为 SELECT orders.* FROM orders JOIN users ON orders.user_id = users.user_id WHERE users.city = ‘Beijing’。

• 使用临时表：对于复杂的子查询，可以将子查询的结果存储在临时表中，然后再与主查询进行关联。这样可以减少子查询的重复执行，提高查询性能。例如，CREATE TEMPORARY TABLE temp_users AS SELECT user_id FROM users WHERE city = ‘Beijing’; SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM temp_users)。

• 优化子查询内部的查询语句：确保子查询内部的查询语句本身是优化过的，如创建合适的索引、避免全表扫描等。

11. 什么是执行计划？如何查看执行计划？

• 执行计划：是数据库查询优化器为 SQL 语句生成的执行方案，它展示了数据库如何执行查询，包括表的连接顺序、使用的索引、扫描的行数等信息。通过分析执行计划，可以了解 SQL 语句的执行效率，找出性能瓶颈所在。

• 查看执行计划的方法：在 MySQL 中，可以使用 EXPLAIN 关键字来查看执行计划。例如，EXPLAIN SELECT * FROM orders WHERE order_date > ‘2023-01-01’; 执行结果会展示查询的详细执行信息，帮助我们分析和优化 SQL 语句。

12. 如何优化 ORDER BY 操作？

• 创建合适的索引：如果 ORDER BY 子句中的列创建了索引，数据库可以利用索引的有序性直接进行排序，而无需进行额外的排序操作。例如，在查询 SELECT * FROM products ORDER BY price 时，在 price 列上创建索引可以提高排序效率。

• 避免在 ORDER BY 中使用函数和表达式：在 ORDER BY 子句中对字段进行函数操作或表达式计算会导致数据库无法使用索引进行排序，从而增加排序的时间开销。例如，将 SELECT * FROM users ORDER BY YEAR (birth_date) 改为 SELECT * FROM users ORDER BY birth_date，避免在 ORDER BY 中使用函数。

• 合理使用 LIMIT：如果只需要返回部分排序结果，可以使用 LIMIT 限制返回的行数，这样可以减少排序的数据量，提高查询效率。例如，SELECT * FROM orders ORDER BY order_amount DESC LIMIT 10，只对前 10 条数据进行排序。

13. 如何优化 INSERT 操作？

• 使用批量插入：使用 INSERT INTO… VALUES (value1), (value2), (value3) 一次性插入多条数据，比多次执行 INSERT INTO… VALUES (value) 要快得多。这是因为批量操作可以减少数据库的事务开销和网络传输次数，提高操作效率。

• 禁用索引：在插入大量数据之前，可以暂时禁用表上的索引，插入完成后再重新启用索引。这样可以避免在插入过程中频繁更新索引，提高插入速度。但要注意，禁用索引期间，数据的完整性和一致性可能会受到一定影响，需要谨慎操作。

• 合理设置事务：将多个插入操作放在一个事务中，可以减少事务的提交次数，提高插入性能。但事务的大小要适中，过大的事务可能会导致回滚时间过长，影响数据库的正常运行。

14. 如何优化 UPDATE 操作？

• 使用索引：在 UPDATE 语句的 WHERE 子句中涉及的列上创建索引，可以加快定位需要更新的行，提高更新效率。例如，UPDATE users SET age = age + 1 WHERE city = ‘Shanghai’，在 city 列上创建索引可以使更新操作更快。

• 避免全表更新：尽量缩小 UPDATE 操作的范围，只更新必要的行和列。如果需要更新大量数据，可以考虑分批进行更新，避免长时间锁定表，影响其他操作。

• 合理使用事务：与 INSERT 操作类似，将多个 UPDATE 操作放在一个事务中，可以减少事务的提交次数，提高更新性能。但要注意事务的一致性和隔离性，避免出现数据冲突和错误。

15. 如何优化 DELETE 操作？

• 使用索引：在 DELETE 语句的 WHERE 子句中涉及的列上创建索引，能够快速定位需要删除的行，提高删除效率。例如，DELETE FROM orders WHERE order_date < ‘2023-01-01’，在 order_date 列上创建索引可以加快删除操作。

• 分批删除：对于需要删除大量数据的情况，分批进行删除可以避免长时间锁定表，影响数据库的正常运行。可以使用 LIMIT 和 OFFSET 来实现分批删除，例如，DELETE FROM orders WHERE order_date < ‘2023-01-01’ LIMIT 1000; 然后不断调整 OFFSET 的值，继续删除下一批数据。

• 合理使用事务：将多个 DELETE 操作放在一个事务中，减少事务的提交次数，提高删除性能。但要注意事务的回滚机制，确保在出现错误时能够正确回滚事务，保证数据的完整性。

16. 什么是数据库的锁？如何优化锁的使用？

• 数据库的锁：是一种用于控制并发访问的机制，它可以防止多个事务同时对同一数据进行修改，从而保证数据的一致性和完整性。常见的锁类型有共享锁（S 锁）和排他锁（X 锁），共享锁允许其他事务对数据进行读操作，但不允许写操作；排他锁则不允许其他事务对数据进行任何操作，直到锁被释放。

• 优化锁的使用方法：

• 减少锁的粒度：尽量使用行级锁而不是表级锁，行级锁只锁定需要操作的行，而表级锁会锁定整个表，行级锁可以减少锁冲突，提高并发性能。

• 缩短锁的持有时间：在事务中，尽量减少对锁的持有时间，尽快完成操作并释放锁。例如，将一些不必要的计算和逻辑放在事务外执行，避免在持有锁的情况下进行长时间的操作。

• 优化事务隔离级别：根据业务需求选择合适的事务隔离级别，不同的隔离级别对锁的使用和并发性能有不同的影响。例如，读未提交（READ UNCOMMITTED）隔离级别下，事务不会对读取的数据加锁，可能会出现脏读、不可重复读和幻读等问题，但并发性能较高；而串行化（SERIALIZABLE）隔离级别下，事务会对所有读取的数据加锁，保证了数据的一致性，但并发性能较低。一般情况下，可根据业务对数据一致性的要求选择合适的隔离级别，如读已提交（READ COMMITTED）或可重复读（REPEATABLE READ）。

17. 如何优化复杂查询？

• 分解复杂查询：将一个复杂的查询分解为多个简单的查询，通过中间结果集或临时表来逐步完成查询。这样可以降低单个查询的复杂度，提高查询的可读性和可维护性，同时也有助于优化每个子查询的性能。例如，对于一个涉及多个表连接和复杂条件的查询，可以先分别查询出各个表中符合部分条件的数据，存储在临时表中，然后再对临时表进行连接和进一步筛选。

• 使用索引和覆盖索引：确保在复杂查询中使用合适的索引，特别是在 WHERE 子句、JOIN 条件和 ORDER BY 子句中涉及的列上创建索引。同时，尽量利用覆盖索引，减少回表操作，提高查询效率。

• 优化子查询和 JOIN 操作：如前面所述，用 JOIN 替代子查询，优化 JOIN 的类型和连接条件，确保 JOIN 操作的高效性。避免出现笛卡尔积，合理选择内连接、左外连接、右外连接或全外连接，根据业务需求确定最佳的连接方式。

• 分析执行计划：使用 EXPLAIN 等工具分析复杂查询的执行计划，找出性能瓶颈所在，如全表扫描、索引未使用等问题，并针对性地进行优化。根据执行计划的分析结果，调整查询语句的结构、索引的设计或数据库的配置参数。

18. 如何优化数据库的存储结构？

• 合理规范化和反规范化：根据业务需求和查询场景，平衡数据库的规范化和反规范化程度。规范化可以减少数据冗余，提高数据的一致性，但可能会增加查询的复杂度；反规范化则通过适当增加数据冗余来减少表连接，提高查询性能。例如，在一个订单系统中，可以在订单表中冗余一些客户的基本信息，减少订单表和客户表的连接操作，但要注意冗余字段的更新一致性问题。

• 使用分区表：对于数据量非常大的表，使用分区表将数据按照某个规则（如时间、地域等）进行划分，存储在不同的物理文件中。这样可以提高查询效率，便于数据的管理和维护。例如，一个订单表每年的数据量都很大，可以按照年份进行分区，每个分区对应一年的数据，在查询某一年的订单时，可以直接定位到对应的分区进行查询。

• 选择合适的数据类型：在设计表结构时，根据数据的实际范围和精度选择合适的数据类型，避免使用过大或过小的数据类型。例如，对于整数类型，如果数据范围较小，可以选择 TINYINT 或 SMALLINT，而不是使用 INT，这样可以节省存储空间，提高查询性能。对于字符串类型，要根据实际存储的字符串长度选择合适的 VARCHAR 或 CHAR 类型，避免使用过长的字符串类型浪费空间。

• 定期清理和重组数据：定期清理数据库中过期或无用的数据，避免数据堆积导致性能下降。同时，对于频繁更新和删除的表，可以定期进行数据重组，如 MySQL 中的 OPTIMIZE TABLE 操作，以减少数据碎片，提高数据的存储和查询效率。

19. 如何优化数据库的配置参数？

• 调整内存参数：根据服务器的物理内存大小和数据库的负载情况，合理调整数据库的内存参数。例如，在 MySQL 中，innodb_buffer_pool_size 参数用于设置 InnoDB 存储引擎的缓冲池大小，一般建议将其设置为物理内存的 50% – 80%，以提高数据和索引的缓存能力，减少磁盘 I/O 操作。query_cache_size 参数用于设置查询缓存的大小，但在数据更新频繁的情况下，查询缓存的维护成本较高，可能会降低性能，需要根据实际情况进行调整。

• 优化线程参数：根据数据库的并发访问量，调整线程相关的参数。例如，在 MySQL 中，thread_cache_size 参数用于设置线程缓存的大小，适当增大该参数可以减少线程创建和销毁的开销，提高并发性能。但也要注意不要设置过大，以免占用过多系统资源。

• 调整 I/O 参数：根据服务器的磁盘 I/O 性能，调整数据库的 I/O 参数。例如，innodb_flush_log_at_trx_commit 参数用于控制 InnoDB