SQL优化

目录1. 插入数据优化2.主键优化3.order by 排序优化4.group by 分组优化5.limit优化count 的几种用法count主键count字段count (1)count (*)7.update语句优化1. 插入数据优化1.1 批量插入数据如果数据量很多循环单条插入频繁事务、频繁IO而批量一次插入仅1次磁盘IO1.2 手动控制事务批量提交1.3主键有序自增避免页分裂自增 INT 主键新增数据永远追加到 B 树末尾不会中间拆分数据页 若用 UUID 无序主键插入会频繁挪动页数据写入暴跌。1.4大批量导入用 LOAD DATA INFILE百万级数据导入比 INSERT 快几十倍适合数据同步场景。优势交互开销极低多条 INSERT 需要客户端和 MySQL 反复网络交互、多次事务刷盘LOAD DATA 是一次性读取本地文件单次 IO 批量写入减少大量网络往返。事务与日志优化INSERT 每条 / 每批都会频繁刷新 redo/undo 日志LOAD DATA 底层做批量页写入日志合并刷盘磁盘 IO 次数大幅降低。索引延迟构建导入时可以临时关闭唯一索引校验导入完成后统一构建索引避免插入一行维护一次 B 树。解析开销小直接解析文本文件csv/txt不需要解析多条 INSERT SQL 语法MySQL 解析器压力极小。2.主键优化无序主键插入可能导频繁页分裂禁止 UUID、字符串做主键UUID 无序插入频繁页分裂字符串主键占用空间大二级索引膨胀。每张表必须显式主键无主键时 InnoDB 自动生成隐藏 6 字节 rowid无索引优化联表、查询变慢。主键不要频繁修改主键是聚簇索引排序依据改主键会挪动整行数据 所有二级索引同步更新。优先自增 INT/BIGINT 做主键有序自增插入不会页分裂3.order by 排序优化mysql中的两种排序方式①. Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区 sort buffer 中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。②. Using index通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高。如果索引没有覆盖order by的字段就会走filesort,性能很低业务需求查询 1 班学生按总分降序sql-- 原始慢SQL SELECT * FROM student WHERE class_id1 ORDER BY total_score DESC;联合索引覆盖 whereorder by消除 filesort建索引idx_class_score(class_id, total_score)索引内数据天然按 class_id 分组、同班级按 score 有序筛选后无需额外排序。sqlCREATE INDEX idx_class_score ON student(class_id,total_score);排序方向统一索引升降序要和 SQL 一致否则无法利用有序性where class_id1 order by total_score desc索引也要按 score 降序定义。*不要 select用覆盖索引sql-- 覆盖索引无回表无排序 SELECT class_id,stu_name,total_score FROM student WHERE class_id1 ORDER BY total_score;避免排序字段使用函数、运算ORDER BY YEAR(create_time)索引失效强制文件排序。限制排序结果集大数据量先缩小 where 过滤范围再排序不要全表排序。一条联合索引里包含当前这条 SQL 查询需要用到的所有字段MySQL 只需要扫描索引树就能拿到全部数据不需要回表访问聚簇索引完整行数据这种索引就叫覆盖索引。4.group by 分组优化无索引分组出现Using temporary创建临时表分组聚合消耗内存 / 磁盘。分组字段建立前置联合索引索引idx_class_score(class_id,total_score)索引中相同 class_id 数据连续存放MySQL 扫描索引时直接边扫边聚合不用临时表。group by 使用完整注意事项MySQL5.7/8.0 默认开启only_full_group_bySELECT 后所有非聚合字段必须全部写在 GROUP BY 后面5.limit优化一个常见又非常头疼的问题就是 limit 2000000,10 此时需要 MySQL 排序前 2000010 记录仅仅返回 2000000-2000010 的记录其他记录丢弃查询排序的代价非常大。优化思路一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化。子查询先定位偏移主键再关联查详情6.count优化MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count (*) 的时候会直接返回这个数效率很高InnoDB 引擎就麻烦了它执行 count (*) 的时候需要把数据一行一行地从引擎里面读出来然后累积计数。count 的几种用法count主键InnoDB 引擎会遍历整张表把每一行的主键 id 值都取出来返回给服务层。服务层拿到主键后直接按行进行累加 (主键不可能为 null)。count字段没有 not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务判断是否为 null不为 null计数累加。有 not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。count (1)InnoDB 引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字 “1” 进去直接按行进行累加。count (*)InnoDB 引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。按照效率排序的话count (字段) count (主键 id) count (1) ≈ count (*)所以尽量使用 count (*)。count (字段)读字段 判 NULL → 最多 IOCPU最慢count (主键)读主键 id → 有读取开销中等速度count(1) / count(*)不读取任何字段只统计行数 → 最少 IO、无数据拷贝速度最快7.update语句优化更新字段时一定要根据索引字段更新值此时是行锁如果不走索引的话InnoDB 行锁升级为表锁性能下降。