插入优化

  • 批量插入

    insert into tb_user(id,name) values(1,'tom'),(2,'jack'),(3,'jerry');

  • 手动提交事务

    start transaction;

    insert into ...

    insert into ...

    insert into ...

    commit;

  • 主键顺序插入，减少 B+tree 页分裂。

    主键乱序插入：8 1 9 3 7 5

    主键顺序插入：1 2 3 4 5 6

  • 删除或禁用索引

    插入前临时删除非必要索引（或禁用），插入完成后重建。适合大批量数据加载。

  • LOAD DATA INFILE

大批量数据插入

load指令：用于数据迁移，一下子将磁盘文件插入到数据库。

image-20251225165226520

文件里面的数据主键顺序插入，也要比乱序高。

顺序插入高效原因

查询优化

索引优化

  1. 在 WHERE、JOIN、ORDER BY、GROUP BY 涉及的列上建立合适索引。
  2. 使用覆盖索引（索引包含查询的所有列），避免回表。
  3. 避免在索引列上使用函数或计算（如 WHERE DATE(col)=... → 应改为范围条件）。
  4. 区分度低的列（如性别）不单独建索引，可联合索引放在后面。
  5. 联合索引遵循最左前缀法则。
  6. 定期分析并重建索引（OPTIMIZE TABLE）。

SQL 语句优化

  1. 避免 SELECT *，只取需要的列。
  2. 用 EXISTS 代替 IN（子查询数据量大时），或者使用 JOIN 改写。
  3. 用 UNION ALL 代替 UNION（不需要去重时）。
  4. 大分页优化：
    • LIMIT 100000,10 → 改为 WHERE id > 上次最大id LIMIT 10（游标分页）。
    • 或先通过覆盖索引查出主键，再回表取数据。
  5. 避免 OR 导致索引失效，可改用 UNION 或优化为 IN。
  6. 使用 LIKE 时避免前置通配符 '%abc'，会索引失效。
  7. 合理使用 EXPLAIN 分析执行计划，关注 type（至少达到 range 或 ref）、rows、Extra 中是否出现 Using filesort / Using temporary。

表结构优化

  1. 字段类型尽量小且固定长度（如用 INT 不用 BIGINT，用 CHAR 代替变长等），减少 I/O。
  2. 适当反范式化（冗余常用字段）减少 Join。
  3. 拆分大表（水平分表/垂直分表）。
  4. 对只读或很少变动的历史数据使用归档表。

其他

  1. 查询缓存（MySQL 5.7 及之前，8.0 已废弃），若使用则避免不适合缓存的查询（带 NOW() 等）。
  2. 限制返回行数：LIMIT 子句尽早加。
  3. 避免在循环中执行 SQL，改成批量或 JOIN。

更新&删除优化

  1. 批量更新/删除 每次操作限定行数（如 LIMIT 1000），配合循环，避免长事务锁住大表。
  2. 利用索引 WHERE 条件必须命中索引，否则行锁升级为表锁（InnoDB 走索引才锁行）。
  3. 避免锁争用 更新高频热行时，考虑排队机制或减少并发直接写。
  4. 先查询后更新 如果更新逻辑复杂，可先查出主键集合，再按主键批量更新。
  5. 分区裁剪 对分区表进行 UPDATE/DELETE 时，WHERE 条件带上分区键，只锁定相关分区。