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 > 20UNIONSELECT * 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字段允许NULLCREATE TABLE users ( id INT PRIMARY KEY, name CHAR(32) DEFAULT NULL -- NULL会增加索引维护成本);-- 推荐：用空字符串作为默认值，避免NULLCREATE 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操作，拖慢整个表的查询速度。将大字段拆分到独立表，只在需要时关联查询。

代码示例：

-- 不推荐：主表包含大字段profileCREATE 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 uJOIN user_profiles p ON u.id = p.user_idWHERE 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_coldSELECT * 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 uINNER JOIN orders o ON u.id = o.user_idWHERE u.age > 20;-- 不推荐：大表作为左表，小表作为右表-- 执行时会以orders为驱动表，用1000万行数据匹配users表，次数更多SELECT u.name, o.order_no FROM orders oINNER JOIN users u ON o.user_id = u.idWHERE 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 uINNER 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和慢查询日志持续迭代，针对性选择优化方法，即可让数据库性能实现质的飞跃。