SQL 优化

1.插入数据

• 批量插入数据

insert into table values(col1,col2),(col1,col2);

• 手动控制事务

start transaction;
...
commit

• 主键顺序插入

1,2,3,4,5,78,99

• 大批量插入

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n';

2.主键优化

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过 InnoDB 的逻辑结构图：

在 InnoDB 引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认 16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

2.1 页分裂

页可以为空，也可以填充一半，也可以填充 100%。每个页包含了 2-N 行数据 (如果一行数据过大，会行溢出)，根据主键排列。

1.插入50

2.过程

3.但是 47 所在的 1# 页，已经写满了，存储不了 50 对应的数据了。 那么此时会开辟一个新的页 3#

4.但是并不会直接将 50 存入 3# 页，而是会将 1# 页后一半的数据，移动到 3# 页，然后在 3# 页，插入 50。

5.调换页

2.2 页合并

1.如果逻辑删除（仅仅标记为删除，不是物理删除），达到阈值，默认页的 50%

2.当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用

知识小贴士：

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

2.3 主键设计原则

1.在满足业务下，使用最低主键长度

2.插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键

3.尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。

4.业务操作时，避免对主键的修改。

3 order by 优化

MySQL 的排序，有两种方式：

Using filesort

通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sortbuffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index

通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高 。

1.对于没有索引情况下的排序，一定是全表扫描，为 index 级速度 (慢)

2.默认情况下的联合索引都是升序的。也就是

create index index_name on table(age asc,phone asc) #asc可以省略

正例 (索引有效)

select age from table order by age

select age,phone from table order by age,phone

select age from table order by age desc

反例

select * from table order by ... #不管安排什么字段来排序，只有用*一定索引失效。
select age,name from table order by age
# 由于name字段是不包含在idx_age_name索引中所以无法使用索引，此时需要进行文件排序，即Extra: Using filesort
select age,name from table order by age,name desc #一升一降，部分失效

select age from table order by phone #需filesort辅助

总之

• 大前提，select 中的字段要求在索引中出现。
• 借联合索引时，要遵循最左匹配原则，当然可以先用 where age 后 order by name，也是遵循的。
• 借联合索引，order by 的要求字段，要么同时升序要么同时降序。
• 额外信息中出现 Using index 和 filesort，性能都是不太达标的！

想一升一降的排序，可以这样创建索引

create index index_name on table(age ,name desc)

如此一来，一升一降就不会导致索引 (部分) 失效

4.group by 优化

与 order by 一致，有索引时候会显著提升分组速度。

• Using temporary：使用临时表来分组，效率低

• Using index：使用索引分组

同样的，

1.select 应该只出现索引有的字段

2.最左匹配，允许先 where 匹配字段，后 group by

5 limit 优化

select * from table limit 100w,10;

这样的越往后的分页，效率越低。

MySQL 官方建议：

覆盖索引

select id from table order by id limit 100w,10;

• 如果没有 order by id 默认下是按照物理磁盘的存储顺序来显示数据的！而不是安排自增 id，其实不存在页合并的话，那么查出来的就是 id 升序

使用连表查询出 id 的行数据

select * from tb_sku t , (select id from table order by id limit 100w,10) a where t.id = a.id;

6.count 优化

上结论

count(*)≈count(1)>count(主键)>count(字段)

尽量使用count(*), MySQL专门优化了性能！

7.update 优化

使用 update 会使用到 InnoDB 的锁机制。

锁是针对索引的，如果 where 中没有索引或索引失效，那么将会升级为表锁，性能大大降低

update ... where name='Jack'
#若name没有索引，则是表锁；有索引，则是行锁

另外，如果 name='Jack’有多条记录，这么这多条记录都会施加行锁！

8.汇总

3~7 本质还是针对索引优化，因此掌握索引优化，就掌握了 SQL 优化的 80%！！！

• 插入数据：批量插入、手动控制事务、主键顺序插入

• 主键优化：长度短、顺序插入，用 auto_incremrnt 而不是 UUID

• order by：using index 直接索引返回而不是 Using filesort

• group by：多字段分组要满足最左前缀匹配

• limit：覆盖索引 (无需回表)+ 子查询

• count：count(字段)<count(主键 id) < count(数字) ≈ count(*)

• update：尽量根据 (where) 主键 / 索引字段进行数据更新

EXPLAIN

EXPLAIN 是 MySQL 提供的用于分析查询执行计划的核心工具，通过它可以直观看到数据库如何执行一条 SQL 查询（如是否使用索引、扫描了多少行数据、是否需要临时表等），是优化慢查询的关键手段。

一、EXPLAIN 基本用法

在任意 SELECT 语句前添加 EXPLAIN 关键字即可生成执行计划：

EXPLAIN SELECT * FROM user WHERE age > 20 AND gender = '男' ORDER BY create_time;

二、EXPLAIN 输出字段详解

执行后会返回一个结果表，包含多个关键字段，以下是最核心的 10 个字段及其含义：

1. id（查询序号）

• 含义：表示 SQL 中 SELECT 语句的执行顺序（id 越大越优先执行；id 相同则按顺序执行）。
• 示例：
• 单表查询：id=1（只有一个 SELECT）。
• 子查询：嵌套的子查询会生成多个 id（如 id=1 主查询，id=2 子查询）。

2. select_type（查询类型）

表示查询的性质（是否是普通查询、子查询、联合查询等），常见值：

• SIMPLE：简单查询（无嵌套/联合）。
• PRIMARY：主查询（包含子查询时，最外层的 SELECT）。
• SUBQUERY：子查询（嵌套在 SELECT 中的子查询）。
• UNION：联合查询（如 SELECT ... UNION SELECT ... 中的第二个 SELECT）。

3. table（当前操作的表）

表示当前执行步骤所操作的表名（可能是实际表名，或临时表别名）。

4. type（访问类型，最核心字段！）

表示数据库如何访问表中的数据（从全表扫描到索引精确匹配，性能从差到好）。常见值按性能排序：

ALL（全表扫描） < index（索引全扫描） < range（索引范围扫描） < ref（索引匹配等值查询） < eq_ref（唯一索引匹配） < const（常量匹配） < system（系统表，极少见）

• ALL：全表扫描（无索引或索引失效），性能最差，需重点优化。
• range：索引范围查询（如 WHERE id BETWEEN 100 AND 200 或 WHERE age > 20）。
• ref：索引等值匹配（如 WHERE user_id = 123，且 user_id 有普通索引）。
• const：通过唯一索引（如主键）精确匹配一条记录（性能最好）。

5. possible_keys（可能使用的索引）

MySQL 优化器认为可能用于当前查询的索引（但实际可能未使用）。若该列为 NULL，说明没有可用索引。

6. key（实际使用的索引）

MySQL 优化器最终选择使用的索引。若为 NULL，说明未使用索引（可能是 possible_keys 中的索引未生效，或优化器认为全表扫描更快）。

7. key_len（索引的有效长度）

表示实际使用的索引的字节长度，可用于判断索引是否完全利用。

• 示例： 若索引是 (age, gender)（age 是 INT 类型占 4 字节，gender 是 VARCHAR(1) 占 1+1=2 字节（长度+内容）），则：

• 当查询条件为 age=20 时，key_len=4（仅使用 age 部分）。

• 当查询条件为 age=20 AND gender='男' 时，key_len=4+2=6（完全利用索引）。

8. ref（索引匹配的列或常量）

表示当前索引键与哪些值或列进行匹配。

• 示例： ref=const 表示索引匹配的是常量（如 WHERE id=123，id 是主键）； ref=user.id 表示索引匹配的是另一张表的列（如 JOIN 操作）。

9. rows（估算扫描的行数）

MySQL 优化器估算要扫描的行数（非精确值，但可用于对比优化效果）。行数越少，性能越好。

10. Extra（额外信息，关键优化点！）

包含查询执行的额外细节，常见值：

• Using where：需在结果集中进一步过滤数据（即使有索引，可能未完全覆盖查询条件）。
• Using index：覆盖索引（查询的字段都在索引中，无需回表，性能高）。
• Using filesort：需额外排序（如 ORDER BY 未使用索引，需临时文件排序，性能差）。
• Using temporary：需临时表存储中间结果（如 GROUP BY 或 DISTINCT 未使用索引，性能差）。

三、通过 EXPLAIN 优化查询的实战步骤

以一个慢查询为例，演示如何通过 EXPLAIN 定位问题并优化：

场景：

用户反馈查询 SELECT name, age FROM user WHERE city='北京' AND gender='男' ORDER BY create_time; 很慢。

步骤 1：执行 EXPLAIN 获取执行计划

EXPLAIN SELECT name, age FROM user WHERE city='北京' AND gender='男' ORDER BY create_time;

步骤 2：分析关键字段

假设输出如下（关键字段）：

步骤 3：定位问题

• type=ALL：全表扫描（无索引）。
• key=NULL：未使用任何索引。
• Extra=Using filesort：需额外排序，性能差。

步骤 4：优化方案

1. 添加复合索引：根据查询条件 (city, gender) 和排序字段 create_time，创建索引 (city, gender, create_time)（最左匹配原则）。
2. 覆盖索引：若查询字段 name, age 也包含在索引中（如 (city, gender, create_time, name, age)），可避免回表（Extra=Using index）。

步骤 5：验证优化效果

再次执行 EXPLAIN，理想输出应为：

四、EXPLAIN 的扩展用法

MySQL 5.6+ 支持 EXPLAIN FORMAT=JSON，输出更详细的执行计划（包含成本估算、索引使用细节等），适合高级优化：

EXPLAIN FORMAT=JSON SELECT * FROM user WHERE id=1;

五、注意事项

• EXPLAIN 显示的是优化器估算的执行计划，可能与实际执行有差异（如统计信息过时）。
• 若 rows 估算值与实际差异大，需执行 ANALYZE TABLE 表名 更新统计信息。
• 优先关注 type（是否全表扫描）、key（是否用索引）、Extra（是否有文件排序/临时表）这三个字段。

掌握 EXPLAIN 的输出分析，是 MySQL 优化的核心技能。通过它可以快速定位查询的瓶颈（如索引缺失、排序效率低），并针对性优化，显著提升数据库性能。