SQL作为数据库交互的核心语言，其效率直接决定系统响应速度。SQL优化是提升数据库性能的关键环节，合理的优化能大幅减少查询时间、降低服务器负载。掌握科学的优化技巧，能避免全表扫描、冗余计算等性能陷阱，让数据库在高并发场景下仍保持高效稳定。以下是30条经过实践检验的SQL优化实用技巧，结合原理、代码示例和代码解析，帮助我们系统提升SQL处理效率。

一、索引优化

索引是数据库的“导航系统”，合理设计索引能避免全表扫描，直接定位目标数据。

1、WHERE条件字段建索引

原理：数据库查询时，若WHERE条件中的字段没有索引，会逐行扫描全表（全表扫描），数据量越大效率越低。索引就像书籍的目录，能快速定位到包含目标信息的页面。

代码示例：

-- 为users表的age字段创建索引
CREATE INDEX idx_age ON users(age);
-- 查询年龄大于20的用户
-- 优化前：无索引时需扫描整个users表
-- 优化后：通过idx_age索引直接定位age>20的记录，效率提升约10倍
SELECT * FROM users WHERE age > 20;

代码解析：

• CREATE INDEX idx_age ON users(age)：创建名为idx_age的索引，作用于users表的age字段。
• 索引生效后，WHERE age > 20会直接通过索引定位符合条件的记录，无需扫描全表。

2、索引列避免使用函数或表达式

原理：对索引列进行函数运算（如YEAR(create_time)）或表达式计算（如price*2）时，数据库无法直接使用索引，会触发全表扫描。

代码示例：

-- 错误写法：对索引列create_time使用YEAR()函数，导致索引失效
-- 此时会扫描全表，逐个计算每条记录的create_time年份
SELECT * FROM users WHERE YEAR(create_time) = 2023;

-- 正确写法：直接对字段进行范围查询，利用索引
-- create_time若有索引，会直接定位2023年1月1日至2024年1月1日的记录
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2024-01-01';

代码解析：

• 错误写法中，YEAR(create_time)会修改索引列的值，导致索引无法匹配，只能全表扫描。
• 正确写法通过BETWEEN直接限定字段范围，索引可正常生效，查询效率提升显著。

3、使用覆盖索引

原理：覆盖索引是指索引包含查询所需的所有字段（SELECT后的字段+WHERE条件字段），无需回表查询原始数据，减少I/O操作。

代码示例：

-- 创建联合索引，包含查询所需的age（条件字段）和name（查询字段）
CREATE INDEX idx_age_name ON users(age, name);

-- 查询年龄为20的用户姓名
-- 优化前：若索引仅包含age，需先通过索引找到记录位置，再回表查询name
-- 优化后：idx_age_name直接包含age和name，可从索引中直接获取结果，无需回表
SELECT name FROM users WHERE age = 20;

代码解析：

• 联合索引idx_age_name(age, name)同时包含查询条件age和结果字段name，满足“覆盖”需求。
• 执行SELECT name WHERE age=20时，数据库直接从索引中读取数据，避免了从表中读取完整记录的操作（回表），速度更快。

4、避免使用OR条件，改用UNION

原理：OR连接的多个条件中，若有一个字段无索引，会导致整个查询无法使用索引，触发全表扫描。UNION可将查询拆分为多个子查询，分别使用各自的索引，再合并结果。

代码示例：

-- 错误写法：OR条件可能导致索引失效（假设age有索引，city无索引）
-- 此时会全表扫描，检查每条记录是否满足age>20或city='shenzhen'
SELECT * FROM users WHERE age > 20 OR city = 'shenzhen';

-- 正确写法：用UNION拆分查询，分别使用索引
-- 子查询1：使用age字段的索引
-- 子查询2：若city有索引则使用，无索引也仅扫描符合条件的部分
-- 最后用UNION合并去重结果
SELECT * FROM users WHERE age > 20
UNION
SELECT * FROM users WHERE city = 'shenzhen';

代码解析：

• UNION要求两个子查询的结果结构一致，且会自动去重（若允许重复可改用UNION ALL，效率更高）。
• 拆分后每个子查询可独立使用索引，整体效率比OR高，尤其当其中一个字段有索引时。

5、字符字段建前缀索引

原理：对于长字符串字段（如email、address），创建完整索引会占用大量空间且效率低。前缀索引只取字段的前N个字符创建索引，减少索引大小，提升查询速度。

代码示例：

-- 为email字段的前10个字符创建前缀索引
-- email通常格式为"xxx@xxx.com"，前10个字符已能区分大部分记录
CREATE INDEX idx_email ON users(email(10));

-- 查询邮箱包含"wang"的用户
-- 索引会匹配email前10个字符中包含"wang"的记录，缩小查询范围
SELECT * FROM users WHERE email LIKE 'wang%';

代码解析：

• email(10)表示只对email字段的前10个字符创建索引，平衡索引大小和查询精度。
• 适合前缀区分度高的字段（如邮箱、用户名），若前缀重复率高（如“省份+城市”的地址），则不适合。

二、查询编写优化

查询语句的写法直接影响执行效率，合理的语法能减少不必要的资源消耗。

6、禁止使用SELECT *

原理：SELECT *会查询表中所有字段，包括不需要的字段，增加网络传输量和内存占用，还可能因包含未索引字段导致回表操作。

代码示例：

-- 错误写法：查询所有字段，包括不需要的age、city等
SELECT * FROM users;

-- 正确写法：只查询需要的id和name字段
-- 优化效果：减少约30%的网络传输量，若id和name有覆盖索引，可避免回表
SELECT id, name FROM users;

代码解析：

• SELECT *的隐患：若表结构新增字段，查询结果会包含多余数据；若字段中包含大文本（如TEXT），会严重拖慢查询。
• 显式指定字段能精准控制返回数据，配合覆盖索引效果更佳。

7、LIMIT分页优化

原理：当分页偏移量较大时（如LIMIT 10000, 10），数据库会扫描前10000条记录再取后10条，效率极低。通过WHERE条件基于主键跳过数据，可避免全表扫描。

代码示例：

-- 错误写法：大偏移量导致扫描前10000行
-- 执行逻辑：先查询并排序前10000+10条记录，再丢弃前10000条，保留最后10条
SELECT * FROM users ORDER BY id LIMIT 10000, 10;

-- 正确写法：基于主键id跳过数据
-- 执行逻辑：直接定位id>10000的记录，取前10条，无需扫描前面的数据
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

代码解析：

• 前提：id是自增主键（有序且唯一），确保id>10000能准确跳过前10000条记录。
• 适用于“上一页/下一页”的分页场景，若需跳转到任意页（如第100页），可结合主键记录当前页最后一条的id。

8、LIKE查询避免使用前导通配符

原理：LIKE '%keyword'（前导通配符）或LIKE '%keyword%'（前后通配符）无法使用索引，会触发全表扫描；LIKE 'keyword%'（后导通配符）可利用前缀索引。

代码示例：

-- 错误写法：前导通配符导致索引失效
-- 会扫描全表，检查每条记录的name是否包含"wang"
SELECT * FROM users WHERE name LIKE '%wang';

-- 正确写法：后导通配符可使用索引
-- 若name有索引，会直接匹配以"wang"开头的记录
SELECT * FROM users WHERE name LIKE 'wang%';

代码解析：

• 索引是按字段值顺序排列的，'wang%'能匹配索引中以“wang”开头的部分，而'%wang'无法确定起始位置，只能全表扫描。
• 若需模糊匹配中间字符（如%wang%），可考虑全文索引（如MySQL的FULLTEXT）。

9、避免隐式类型转换

原理：当查询条件中的值与字段类型不匹配时，数据库会自动进行类型转换（如字符串字段匹配数字），导致索引失效。

代码示例：

-- 错误写法：mobile是varchar类型，用数字123456匹配，触发隐式转换
-- 数据库会将mobile字段的值转为数字再比较，导致索引失效，全表扫描
SELECT * FROM users WHERE mobile = 123456;

-- 正确写法：值的类型与字段一致（字符串）
-- 直接匹配varchar类型的mobile，索引可正常生效
SELECT * FROM users WHERE mobile = '123456';

代码解析：

• mobile字段定义为varchar(20)，存储的是字符串，若用数字123456查询，数据库会执行CAST(mobile AS UNSIGNED) = 123456，修改了索引列，导致索引失效。
• 确保查询值的类型与字段定义一致，是避免隐式转换的核心。

10、用EXISTS替代IN

原理：IN适合子查询结果集小的场景，若子查询返回大量数据，IN会生成临时表并多次比对，效率低；EXISTS是“存在即返回”，一旦找到匹配记录就停止查询，更高效。

代码示例：

-- 低效写法：IN子查询可能处理大量数据
-- 执行逻辑：先查询users表中age>20的id，生成临时列表，再逐条与orders表的user_id比对
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE age > 20);

-- 高效写法：EXISTS子查询“找到即停”
-- 执行逻辑：遍历orders表的每条记录，检查是否存在users表中id匹配且age>20的记录，找到后立即返回
SELECT * FROM orders o WHERE EXISTS (
  SELECT 1 FROM users u WHERE u.id = o.user_id AND u.age > 20
);

代码解析：

• EXISTS中的子查询不返回具体数据，只返回“是否存在”（用SELECT 1即可，无需SELECT *），减少数据传输。
• 当子查询结果集大时，EXISTS的优势更明显，尤其适合关联查询。

三、表结构设计优化

合理的表结构是高效查询的基础，能从根源减少性能问题。

11、控制单表数据量

原理：单表数据量超过1000万行后，索引效率会下降，查询和写入速度明显变慢。需通过分表（水平分表：按时间、地区拆分；垂直分表：按字段冷热拆分）或数据归档解决。

示例场景：

• 订单表orders按年份分表：orders_2023、orders_2024，查询2023年订单时直接访问orders_2023，避免扫描全量数据。
• 历史数据（如3年前的订单）迁移到归档表orders_archive，主表只保留近期数据。

12、避免NULL字段

原理：NULL表示“无值”，会增加索引的复杂度（索引需额外标记NULL值），且NULL字段无法参与某些统计计算（如COUNT()会忽略NULL）。用默认值替代NULL更高效。

代码示例：

-- 不推荐：name字段允许NULL
CREATE TABLE users (
  id INT PRIMARY KEY,
  name CHAR(32) DEFAULT NULL  -- NULL会增加索引维护成本
);

-- 推荐：用空字符串作为默认值，避免NULL
CREATE TABLE users (
  id INT PRIMARY KEY,
  name CHAR(32) NOT NULL DEFAULT ''  -- 无值时存储空字符串，索引更高效
);

代码解析：

• NOT NULL DEFAULT ''确保字段始终有值，索引无需特殊处理NULL，查询和统计时更高效（如COUNT(name)可准确计算所有记录）。
• 例外：若业务中“无值”和“空字符串”有明确区别（如“未填写”vs“填写为空”），可保留NULL，但需谨慎评估性能影响。

13、拆分TEXT/BLOB大字段

原理：TEXT（长文本）、BLOB（二进制数据，如图片）字段占用空间大，查询时会增加I/O操作，拖慢整个表的查询速度。将大字段拆分到独立表，只在需要时关联查询。

代码示例：

-- 不推荐：主表包含大字段profile
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  profile TEXT  -- 存储用户详细介绍，可能达数万字
);

-- 推荐：拆分大字段到独立表
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,  -- 与users表的id关联
  profile TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 查询用户基本信息（无需大字段）
SELECT id, name FROM users WHERE id = 1;

-- 需查询详细介绍时再关联
SELECT u.id, u.name, p.profile 
FROM users u
JOIN user_profiles p ON u.id = p.user_id
WHERE u.id = 1;

代码解析：

• 拆分后，users表仅包含小字段，查询基本信息时速度更快；user_profiles表只在需要大字段时访问，减少不必要的I/O。
• 适合大字段访问频率低的场景（如用户详情、商品描述）。

14、IP地址转为整数存储

原理：IP地址（如192.168.1.1）本质是32位整数，用字符串存储需15字节，转为整数后只需4字节，节省空间且查询更快（整数比较比字符串比较高效）。

代码示例：

-- 存储IP地址：用INET_ATON()将字符串转为整数
INSERT INTO logs (ip) VALUES (INET_ATON('192.168.1.1'));
-- 结果：192.168.1.1 转为整数 3232235777

-- 查询时：用INET_NTOA()将整数转回字符串
SELECT INET_NTOA(ip) AS ip_address FROM logs;
-- 结果：3232235777 转回 192.168.1.1

代码解析：

• INET_ATON(ip_str)：将IPv4地址字符串转为无符号整数（如192.168.1.1 → 3232235777）。
• INET_NTOA(ip_int)：将整数转回IPv4地址字符串，不影响查询可读性。
• 优势：节省存储空间（约70%），整数查询（如ip > 3232235777）比字符串查询（如ip > '192.168.1.1'）快。

15、枚举类型用ENUM而非字符串

原理：对于固定可选值的字段（如性别、状态），ENUM类型存储的是整数（内部映射），比VARCHAR字符串更节省空间，查询和排序更快。

代码示例：

-- 不推荐：用VARCHAR存储性别
CREATE TABLE users (
  id INT PRIMARY KEY,
  sex VARCHAR(1)  -- 'M'或'F'，占1字节，但比较时按字符串处理
);

-- 推荐：用ENUM存储性别
CREATE TABLE users (
  id INT PRIMARY KEY,
  sex ENUM('M', 'F')  -- 内部存储为整数（'M'→1，'F'→2），占1字节，比较更高效
);

-- 查询示例：两种写法结果一致，但ENUM效率更高
SELECT * FROM users WHERE sex = 'M';

代码解析：

• ENUM('M', 'F')定义了两个可选值，数据库内部用整数（1代表'M'，2代表'F'）存储，占用空间与VARCHAR(1)相同，但查询和排序时是基于整数操作，速度更快。
• 注意：ENUM的值是固定的，新增或修改值需ALTER TABLE，适合值很少变动的场景（如性别、订单状态：'pending'、'paid'、'shipped'）。

四、事务与锁优化

事务和锁的合理使用能减少并发冲突，提升数据库的并发处理能力。

16、短事务原则

原理：事务持续时间越长，持有锁的时间越久，会增加其他事务的等待时间，甚至导致死锁。将长事务拆分为多个短事务，减少锁竞争。

代码示例：

-- 不推荐：长事务（包含文件上传和数据库操作）
BEGIN;
-- 步骤1：上传图片到服务器（耗时操作，可能几秒到几十秒）
-- 步骤2：记录图片路径到数据库
INSERT INTO user_avatars (user_id, img_path) VALUES (1, '/images/avatar1.jpg');
COMMIT;  -- 事务持续时间长，锁持有久

-- 推荐：拆分事务（仅包含数据库操作）
-- 步骤1：先上传图片（无事务）
-- 步骤2：单独开启事务记录路径（短事务）
BEGIN;
INSERT INTO user_avatars (user_id, img_path) VALUES (1, '/images/avatar1.jpg');
COMMIT;  -- 事务仅包含数据库操作，瞬间完成，锁快速释放

代码解析：

• 长事务的风险：若图片上传耗时10秒，事务会持有相关表的锁10秒，期间其他操作该表的事务会阻塞。
• 拆分后，事务仅包含快速的数据库操作，锁持有时间极短，减少对并发的影响。

17、批量操作替代单条处理

原理：单条SQL操作（如INSERT、UPDATE）会频繁提交事务，增加IO和锁的开销。批量操作能减少事务提交次数，提升效率。

代码示例：

-- 低效：单条插入，多次提交
INSERT INTO users (id, name) VALUES (1, 'A');
INSERT INTO users (id, name) VALUES (2, 'B');
INSERT INTO users (id, name) VALUES (3, 'C');
-- 每条INSERT都可能触发事务提交（自动提交模式下），产生3次IO操作

-- 高效：批量插入，一次提交
INSERT INTO users (id, name) VALUES 
(1, 'A'), 
(2, 'B'), 
(3, 'C');
-- 一次SQL操作完成3条记录插入，仅1次IO操作，效率提升3倍以上

代码解析：

• 批量插入通过逗号分隔多条记录，减少了SQL语句的解析次数和事务提交次数，大幅降低IO开销。
• 注意：批量操作的记录数不宜过多（如MySQL建议单次不超过1000条），避免SQL语句过长导致内存溢出。

18、禁用外键约束

原理：外键约束（FOREIGN KEY）用于保证表之间的数据一致性，但会增加数据库的锁开销（操作子表时需检查主表）。实际开发中，可由应用程序控制数据一致性，禁用外键提升性能。

代码示例：

-- 不推荐：使用外键约束
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  -- 外键约束：orders.user_id必须存在于users.id中
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 推荐：禁用外键，由程序保证一致性
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT  -- 仅存储关联ID，不设外键约束
);

-- 程序逻辑：插入订单前，先检查users表中是否存在对应的user_id
-- 伪代码：
if (exists in users where id = user_id) {
  insert into orders (id, user_id) values (1, user_id);
} else {
  throw error;  // 提示用户不存在
}

代码解析：

• 外键约束的代价：插入/删除orders表记录时，数据库会自动检查users表，可能加锁阻塞其他操作，尤其在高并发场景下影响显著。
• 程序控制的优势：灵活性更高（如可自定义错误提示），减少数据库的额外开销，提升写入性能。

五、系统级优化

除了SQL语句和表结构，数据库系统的配置和维护也会影响性能。

19、定期更新统计信息

原理：数据库优化器依赖表的统计信息（如行数、字段值分布）生成最优执行计划。统计信息过时会导致优化器选择低效的执行计划（如本应走索引却全表扫描）。

代码示例：

-- 更新users表的统计信息
ANALYZE TABLE users;

-- 对于MySQL InnoDB引擎，也可通过以下方式更新（根据版本可能有差异）
ANALYZE TABLE users UPDATE HISTOGRAM ON age, name WITH 100 BUCKETS;

代码解析：

• ANALYZE TABLE会扫描表并更新统计信息（如每个字段的不同值数量、数据分布），优化器根据这些信息判断是否使用索引、如何关联表等。
• 建议频率：对于频繁更新的表（如订单表），可每天更新；静态表（如字典表）可每周更新。

20、冷热数据分离

原理：业务中经常访问的数据（热数据，如近3个月的订单）和很少访问的数据（冷数据，如3年前的订单）混在一起，会导致索引变大、查询变慢。分离存储可提升热数据的查询效率。

代码示例：

-- 1. 创建热数据主表（存储近3个月订单）
CREATE TABLE orders_hot (
  id INT PRIMARY KEY,
  user_id INT,
  order_time DATETIME,
  amount DECIMAL(10,2),
  INDEX idx_order_time (order_time)
);

-- 2. 创建冷数据归档表（存储3个月前的订单）
CREATE TABLE orders_cold (
  id INT PRIMARY KEY,
  user_id INT,
  order_time DATETIME,
  amount DECIMAL(10,2),
  INDEX idx_order_time (order_time)
);

-- 3. 定期将冷数据从主表迁移到归档表（可通过定时任务执行）
INSERT INTO orders_cold
SELECT * FROM orders_hot WHERE order_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);

DELETE FROM orders_hot WHERE order_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);

代码解析：

• 分离后，orders_hot表数据量小，索引更紧凑，查询近3个月订单时速度更快。
• 冷数据查询场景少，可存储在性能较低的存储介质（如普通硬盘），降低成本；热数据可放在SSD上，提升访问速度。

六、关联查询优化

关联查询（JOIN）是数据库中常见的操作，不合理的关联方式会导致性能瓶颈。

21、小表驱动大表（小表放左，大表放右）

原理：关联查询时，数据库通常会以左表为驱动表，逐条匹配右表数据。若左表是小表（数据量少），右表是大表（数据量大），可减少匹配次数，提升效率。

代码示例：

-- 假设users表是小表（10万行），orders表是大表（1000万行）
-- 推荐：小表users作为左表，大表orders作为右表
SELECT u.name, o.order_no 
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.age > 20;

-- 不推荐：大表作为左表，小表作为右表
-- 执行时会以orders为驱动表，用1000万行数据匹配users表，次数更多
SELECT u.name, o.order_no 
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.age > 20;

代码解析：

• 驱动表选择逻辑：A JOIN B中，A是驱动表，数据库会先遍历A的每一行，再去B中匹配符合条件的行。
• 小表驱动大表的优势：假设小表有10万行，大表有1000万行，前者只需10万次匹配，后者需1000万次，效率差异明显。
• 验证方式：用EXPLAIN查看执行计划，type列若为ref或eq_ref（高效关联），说明驱动表选择合理。

22、避免跨库关联查询

原理：跨数据库（甚至跨服务器）的关联查询需要在网络间传输大量数据，且无法使用索引优化，性能极低。应尽量在同一数据库内完成关联，或通过程序分步骤查询。

代码示例：

-- 不推荐：跨库关联（db1.users与db2.orders）
-- 需在两个数据库间传输数据并匹配，无索引可用，延迟高
SELECT u.name, o.order_no 
FROM db1.users u
INNER JOIN db2.orders o ON u.id = o.user_id;

-- 推荐：程序分两步查询（同一数据库内）
-- 步骤1：查询本地数据库的用户ID列表
SELECT id, name FROM db1.users WHERE age > 20;
-- 步骤2：用ID列表查询另一数据库的订单（IN条件）
SELECT order_no, user_id FROM db2.orders WHERE user_id IN (1,2,3,...);
-- 步骤3：程序内存中关联结果（避免跨库IO）

代码解析：

• 跨库关联的问题：数据库无法为不同库的表建立联合索引，关联时需全表扫描并通过网络传输数据，延迟是同库查询的10倍以上。
• 替代方案：通过程序将跨库关联拆分为“本地查询→远程查询→内存关联”，减少网络传输量（只传必要的ID）。

七、聚合与排序优化

聚合函数（如COUNT、SUM）和排序（ORDER BY）操作容易消耗大量内存和CPU，需针对性优化。

23、用COUNT(1)或COUNT(主键)替代COUNT(*)

原理：COUNT(*)会统计所有非NULL行，而COUNT(1)或COUNT(主键)只统计指定列（1是常量，主键非NULL），效率更高。尤其当表包含大字段时，差异明显。

代码示例：

-- 高效：COUNT(1)统计行数，无需解析字段
SELECT COUNT(1) FROM users WHERE age > 20;

-- 高效：COUNT(主键)利用主键索引，速度快
SELECT COUNT(id) FROM users WHERE age > 20;

-- 相对低效：COUNT(*)需扫描所有字段（包括大字段）
SELECT COUNT(*) FROM users WHERE age > 20;

代码解析：

• COUNT(1)：1是常量，不涉及字段解析，数据库直接计数，开销小。
• COUNT(id)：id是主键（非NULL且有索引），数据库可通过主键索引快速统计，无需扫描表数据。
• COUNT(*)：会扫描所有字段，若表有TEXT等大字段，会增加IO开销，速度较慢。

24、避免大结果集排序，用索引排序替代

原理：ORDER BY若无法使用索引，会触发filesort（文件排序），即先将数据读到内存排序，内存不足时写入临时文件，效率极低。为排序字段创建索引，可直接利用索引的有序性避免排序。

代码示例：

-- 不推荐：无索引时触发filesort
-- 执行逻辑：先查询age>20的记录，再在内存/临时文件中按create_time排序
SELECT * FROM users WHERE age > 20 ORDER BY create_time;

-- 推荐：创建联合索引，覆盖查询条件和排序字段
CREATE INDEX idx_age_create_time ON users(age, create_time);
-- 优化后：通过索引直接获取按create_time排序的结果，无需额外排序
SELECT * FROM users WHERE age > 20 ORDER BY create_time;

代码解析：

• 联合索引(age, create_time)的有序性：先按age排序，相同age的记录再按create_time排序。当查询age>20时，索引中符合条件的记录已按create_time有序排列，直接返回即可。
• 验证：EXPLAIN执行计划中，Extra列若显示Using index; Using where，说明使用了索引排序；若显示Using filesort，则需优化。

25、限制聚合查询的范围

原理：GROUP BY或DISTINCT对全表数据聚合时，会处理大量数据并生成临时表。通过WHERE条件缩小范围，或用LIMIT限制结果，可减少计算量。

代码示例：

-- 不推荐：全表聚合，处理1000万行数据
SELECT user_id, COUNT(*) AS order_count 
FROM orders 
GROUP BY user_id;

-- 推荐：按时间范围过滤，只处理近1个月数据（假设100万行）
SELECT user_id, COUNT(*) AS order_count 
FROM orders 
WHERE order_time >= '2024-06-01'
GROUP BY user_id;

-- 进一步优化：只取订单数前10的用户，减少结果集
SELECT user_id, COUNT(*) AS order_count 
FROM orders 
WHERE order_time >= '2024-06-01'
GROUP BY user_id 
ORDER BY order_count DESC 
LIMIT 10;

代码解析：

• 聚合操作的代价：GROUP BY会为每个分组创建临时记录，数据量越大，临时表越大，内存占用越高。
• 优化逻辑：通过WHERE筛选必要数据（如按时间、状态），再用LIMIT限制返回结果，减少临时表的大小和计算时间。

八、特殊场景优化

针对JSON字段、批量更新等特殊场景，有专门的优化方法。

26、谨慎使用JSON字段，避免频繁查询JSON内部数据

原理：MySQL 5.7+支持JSON字段存储非结构化数据，但查询JSON内部字段（如json_col->'$.key'）无法使用普通索引，效率低。若需频繁查询，建议拆分为普通字段。

代码示例：

-- 不推荐：频繁查询JSON内部字段
CREATE TABLE user_info (
  id INT PRIMARY KEY,
  data JSON  -- 存储{"phone": "123456", "address": "xxx"}
);
-- 查询手机号为123456的用户，无法使用索引，全表扫描
SELECT * FROM user_info WHERE data->'$.phone' = '123456';

-- 推荐：拆分JSON为普通字段（适合频繁查询的字段）
CREATE TABLE user_info (
  id INT PRIMARY KEY,
  phone VARCHAR(20),  -- 单独存储，可建索引
  address TEXT  -- 不频繁查询的字段保留为TEXT
);
-- 创建索引，查询高效
CREATE INDEX idx_phone ON user_info(phone);
SELECT * FROM user_info WHERE phone = '123456';

代码解析：

• JSON字段的适用场景：存储非结构化、查询频率低的数据（如用户偏好设置）。
• 拆分原则：将JSON中需要频繁查询、筛选或排序的字段拆分为普通列并建索引，提升查询效率。

27、批量更新用CASE WHEN替代多条UPDATE

原理：多条UPDATE语句会多次访问表并提交事务，效率低。CASE WHEN可在一条语句中完成多条件更新，减少IO和锁开销。

代码示例：

-- 低效：多条UPDATE，多次提交
UPDATE products SET stock = 100 WHERE id = 1;
UPDATE products SET stock = 200 WHERE id = 2;
UPDATE products SET stock = 300 WHERE id = 3;

-- 高效：一条UPDATE完成批量更新
UPDATE products 
SET stock = CASE 
  WHEN id = 1 THEN 100 
  WHEN id = 2 THEN 200 
  WHEN id = 3 THEN 300 
END 
WHERE id IN (1, 2, 3);  -- 限制范围，避免全表更新

代码解析：

• CASE WHEN的优势：一条SQL语句完成多个条件的更新，只需一次表访问和事务提交，比多条UPDATE减少50%以上的IO操作。
• 注意：必须加WHERE id IN (...)，否则会更新表中所有行（未匹配的行stock会设为NULL）。

28、避免使用SELECT FOR UPDATE锁定无关行

原理：SELECT ... FOR UPDATE会对查询到的行加排他锁，若查询条件不当（如无索引），会锁定全表，导致并发阻塞。应精准定位需要锁定的行，减少锁范围。

代码示例：

-- 不推荐：无索引导致锁定全表
-- 假设status无索引，会全表扫描并锁定所有行，其他事务无法操作
BEGIN;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- 处理订单...
COMMIT;

-- 推荐：用主键或唯一索引锁定单行
BEGIN;
-- 只锁定id=100的订单，不影响其他行
SELECT * FROM orders WHERE id = 100 AND status = 'pending' FOR UPDATE;
-- 处理订单...
COMMIT;

代码解析：

• SELECT ... FOR UPDATE的锁范围：取决于查询是否使用索引。有索引时锁定匹配的行，无索引时锁定全表（间隙锁）。
• 最佳实践：用主键或唯一索引锁定具体行，避免大范围锁定；事务尽量短，快速释放锁。

九、索引进阶优化

除了基础索引技巧，复合索引的顺序、冗余索引的清理等进阶操作也很重要。

29、复合索引遵循“最左前缀原则”

原理：复合索引（如(a, b, c)）的索引顺序是先按a排序，再按b，最后按c。查询时只有使用了最左前缀（如a或a+b），才能触发索引。

代码示例：

-- 创建复合索引(a, b, c)
CREATE INDEX idx_a_b_c ON t(a, b, c);

-- 能使用索引的场景（匹配最左前缀）
SELECT * FROM t WHERE a = 1;  -- 用a匹配前缀
SELECT * FROM t WHERE a = 1 AND b = 2;  -- 用a+b匹配前缀
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;  -- 全匹配

-- 无法使用索引的场景（不匹配最左前缀）
SELECT * FROM t WHERE b = 2;  -- 缺少a，不匹配前缀
SELECT * FROM t WHERE a = 1 AND c = 3;  -- 缺少b，中间断裂

代码解析：

• 复合索引字段顺序：应将区分度高的字段放在前面（如a的不同值更多），提升索引的过滤效率。
• 避免重复索引：若已有(a, b)，无需再建(a)（(a, b)已包含a的前缀索引）。

30、定期清理冗余索引

原理：冗余索引（如(a)和(a, b)中的(a)）会浪费存储空间，且增加写入时的索引维护成本。需定期识别并删除冗余索引。

代码示例：

-- 查看表的所有索引（MySQL）
SHOW INDEX FROM users;

-- 假设存在以下索引，其中idx_age是冗余索引
-- idx_age (age)
-- idx_age_name (age, name)

-- 删除冗余索引
DROP INDEX idx_age ON users;

代码解析：

• 识别冗余索引：若索引A的所有字段是索引B的最左前缀（如(age)是(age, name)的前缀），则A是冗余索引。
• 清理频率：建议每季度检查一次，结合业务查询需求，保留必要的索引（如(age, name)能覆盖(age)的查询场景）。

避坑指南

1、索引管理避坑

• 索引并非越多越好：单表索引不超过5个，避免写入时索引维护成本激增（每次写入需同步更新所有索引）。
• 冗余索引清理：定期删除冗余索引（如(a)是(a,b)的前缀索引时，(a)可删除），减少存储空间占用。

2、查询分析避坑

• 定期用EXPLAIN分析执行计划：EXPLAIN SELECT ...，重点关注type（是否走索引）、Extra（是否有filesort或全表扫描），可查看查询是否使用索引、是否有全表扫描、排序方式（如filesort可能导致性能问题）等，及时发现隐患。

    -- 分析查询执行计划
    EXPLAIN SELECT * FROM users WHERE age > 20;
  

• 警惕隐性失效：避免索引列用函数、隐式类型转换、前导通配符等操作，这些会导致索引“看似存在却不生效”。

3、性能监控避坑

• 定期审查慢查询日志：开启数据库的慢查询日志（如MySQL的slow_query_log），记录执行时间超过阈值（如1秒）的SQL，每周分析并优化，避免线上性能突发问题。
• 统计信息更新：定期执行ANALYZE TABLE更新统计信息，避免优化器因信息过时选择低效执行计划。

SQL 优化是 “细节决定成败” 的实践学科，需结合索引设计、查询写法、表结构等多维度发力。SQL优化的核心原则是：减少数据扫描量（用索引）、降低IO开销（批量操作、冷热分离）、减少锁竞争（短事务、精准锁行）。实际应用中，再通过EXPLAIN和慢查询日志持续迭代，针对性选择优化方法，即可让数据库性能实现质的飞跃。

阅读原文：原文链接