插入数据

建议

使用批量插入：

手动控制事务：

主键顺序插入：

大批量插入数据

如果一次性插入上万数据，此时insert语句性能过低，建议使用Mysql的load指令进行插入：

使用步骤

主键优化

数据组织方式

InnoDB的逻辑存储结构

页分裂

页可以为空，也可以为50%,100%,每页包含了2-N行数据（如果一行数据过大，就会溢出），根据主键排列。

主键顺序插入

主键乱序插入

出现了页分裂现象

页合并

主键设计原则

order by优化

ORDER BY 优化的核心思路是尽量让数据库使用索引完成排序（Using index），避免文件排序（Using filesort），因为文件排序需要在内存 / 磁盘中临时处理数据，性能远低于索引排序。下面我会从多个维度详细讲解优化方法，并结合示例说明。

索引的顺序决定了是否能被 ORDER BY 利用，遵循 “最左前缀原则”，如果查询包含 WHERE + ORDER BY，索引需满足：WHERE 筛选的列在前，ORDER BY 的列在后。如果只有 ORDER BY，直接对排序列创建索引即可。

会触发filesort:

-- 假设表 t_user 只有单索引 idx_age (age)
SELECT id, name, age FROM t_user WHERE age > 20 ORDER BY create_time;
-- 原因：索引只包含 age，create_time 无索引，需文件排序

不会触发filesort:

-- 创建联合索引：WHERE 列(age) + ORDER BY 列(create_time)
CREATE INDEX idx_age_create_time ON t_user(age, create_time);

-- 查询语句不变，但数据库会用索引完成排序
SELECT id, name, age FROM t_user WHERE age > 20 ORDER BY create_time;

group by优化

GROUP BY 依赖数据的有序性，索引的最左前缀原则同样适用，且索引需覆盖 WHERE 筛选列 + GROUP BY 分组列（必要时包含聚合列）。

就是where和group by里面包含的字段满足最左前缀法则即可。

-- 正例：创建分组列索引
CREATE INDEX idx_dept_id ON t_employee(dept_id);
-- 执行计划 Extra 列显示 Using index（覆盖索引），无临时表/文件排序
SELECT dept_id, COUNT(*) FROM t_employee GROUP BY dept_id;

-- 正例：创建联合索引（WHERE列 + GROUP BY列）
CREATE INDEX idx_age_dept_id ON t_employee(age, dept_id);
-- 利用索引有序性，直接分组，无临时表
SELECT dept_id, AVG(salary) FROM t_employee WHERE age > 30 GROUP BY dept_id;

-- 创建覆盖索引：age(WHERE) + dept_id(GROUP BY) + salary(聚合列)
CREATE INDEX idx_age_dept_salary ON t_employee(age, dept_id, salary);
-- Extra 列显示 Using index，性能最优
SELECT dept_id, AVG(salary) FROM t_employee WHERE age > 30 GROUP BY dept_id;

limit优化

当 LIMIT offset, size 中 offset 很大时，MySQL 需要扫描并丢弃前 offset 条数据，导致性能急剧下降。优化的核心是减少扫描行数和避免回表。

select * from tb_user limit 100000,10;

1.建议使用覆盖索引 + 子查询：

SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 100000, 10
) AS tmp ON o.id = tmp.id;

2.书签式分页：

-- 已知上一页最后一条 id = 100000
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id LIMIT 10;

count优化

update优化

InnoDB 表锁的本质是：当数据库无法通过索引精准定位到要更新的行时，会放弃行锁，转而使用表锁（或大范围的间隙锁）

-- 反例：name 无索引，触发全表扫描 + 表锁
UPDATE user SET age = 30 WHERE name = '张三';

-- 执行 EXPLAIN 会看到 type=ALL（全表扫描），rows=表总行数
EXPLAIN UPDATE user SET age = 30 WHERE name = '张三';

解决办法：

-- 正例1：给 name 建索引，并且索引不能失效，避免全表扫描
CREATE INDEX idx_user_name ON user(name);
UPDATE user SET age = 30 WHERE name = '张三';