MySQL23 - SQL优化
SQL优化
插入数据
-
批量插入
-
插入多条数据时,执行批量插入,但批量插入也不建议插入超过1k条
-
几百万数据时,多次批量插入
-
INSERT INTO table_name VALUES (..) (..) (..) ...;
-
-
手动事务提交
-
默认每一条DML语句都会开启\提交一次事务
-
手动开启事务,将多次插入进行一次提交
-
START TRANSACTION; INSERT .. INSERT .. ... COMMIT;
-
-
主键顺序插入
-
load
- 将本地磁盘文件的数据直接加载到数据库中,常用于万级、百万级数据
主键优化
- 在InnoDB存储引擎中,表数据是根据主键顺序组织存放的,称为索引组织表(IOT index organized tabel)
- 尽量减少页分裂、增多页合并 -- 尽量按序插入
- 应尽量降低主键的长度,因为二级索引的叶子节点存放主键的值
- 插入数据时,应尽量选择主键顺序插入,如选择使用 AUTO_INCREMENT 自增主键
- 尽量不要使用UUID做主键,或其他自然主键,如身份证号,会乱序插入,且长度过长
ORDER BY 排序优化
-
Using filesort:通过表的所有或全表扫描,读取满足条件的数据行,然后在排序缓存区 sort buffer 中完成排序,所有不是通过索引直接返回排序结果的排序都叫做 FileSort 排序
-
Using index:通过有序所有顺序扫描直接返回有序数据,不需要额外排序,操作效率高
-
尽量使用 Using index => 对有索引的字段进行与其索引相同顺序的排序
-
根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
-
尽量使用覆盖索引
-
多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则 (ASC\DESC)
-
如果不可避免的出现FileSort,大量数据排序时,可以适当增大排序缓冲区大小(默认为256k)
GROUP BY 分组优化
- 可以通过索引提高效率
- 分组操作时,索引的使用也是满足最左前缀法则
LIMIT 分页优化
- 当执行 LIMIT 200_0000, 10时,此时 MySQL 需要排序前 200_0010条记录,仅仅返回 200_0000 ~ 200_0010 的记录,其他记录丢弃,查询排序代价大,性价比低
- 可以先查询数据的主键,在与查询到的主键数据进行多表联查,返回完整记录
COUNT 求和优化
-
在MyISAM引擎中,表的总行数被存储在磁盘中,因此执行 COUNT(*) 的时候会直接返回这个数,效率很高
-
InnoDB引擎执行 COUNT(*) 时,需要吧数据一行行得读取,再进行累计计数
-
优化思路:自己计数存储数据量 。。。
-
COUNT 的主要用法:COUNT( * ) , COUNT( 主键 ) , COUNT( 字段 ) , COUNT( 1 )
-
**COUNT( 主键 ) **
- InnoDB 引擎会遍历整张表,将 主键id 值都取出来,返回给服务层,直接按行进行累加,主键不为 null ,因此不用判断值是否为 null
-
COUNT( 字段 )
- 如果没有 NOT NULL 约束,InnoDB 引擎会遍历整张表,将每一行的字段值都取出来,返回给服务层,服务层判断是否为 NULL,不为 NULL,计数累加
- 有 NOT NULL 约束,InnoDB 引擎会遍历整张表,将每一行的字段值都取出来,返回给服务层,直接按行进行累加
-
COUNT ( 1 )
- InnoDB 引擎会遍历整张表,但不取值,服务层对于返回的每一行,放一个数字 1 进去,直接按行进行累加
-
**COUNT( * ) **
- InnoDB 引擎并不会把全部字段取出,而是专门做了优化,不取值,服务层直接按行累加
-
**效率:COUNT( * ) = COUNT ( 1 ) ** > 其他
UPDATE 更新优化
- Kakfa 避免行锁升级为表锁 ???
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析