MySQL 优化专题
首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我

「mysql优化专题」90%程序员都会忽略的增删改优化

转
 2023-03-28  阅读(1)
©版权
原文作者： javaobject 原文地址：https://cloud.tencent.com/developer/article/1185462

前文一篇「mysql优化专题」这大概是一篇最好的mysql优化入门文章（1）让大家知道msql优化，究竟在优化什么，本篇为mysql优化专题的第二篇，主要先从增删改进行优化。大家可以收藏关注一波，若是有更好的优化方式，也可留言讨论。

补充知识点：操作数据语句优化的认识

通常情况下，当访问某张表的时候，读取者首先必须获取该表的锁，如果有写入操作到达，那么写入者一直等待读取者完成操作（查询开始之后就不能中断，因此允许读取者完成操作）。当读取者完成对表的操作的时候，锁就会被解除。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞（block），因为默认的调度策略是写入者优先于读取者。当第一个读取者完成操作并解放锁后，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。因此：要提高MySQL的更新/插入效率，应首先考虑降低锁的竞争，减少写操作的等待时间。 （本专题在后面会讨论表设计的优化）本篇，要讲的优化是增删改。

一、NSERT语句：

基本：INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]

注意：

如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。

可同时插入多条数据记录！

REPLACE 与 INSERT 完全一样，可互换。

优化前例子：

202303282258066461.png

优化策略：

（1）当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。上述所说的同时插入多条就是一种优化。（经测试，大概10条同时插入是最高效的）

优化后例子：

202303282258079822.png

（2）将进程/线程数控制在2倍于CPU数目相对合适

（3）采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）

（4）考虑使用replace 语句代替insert语句。（REPLACE语句请参考下文，有详细讲述）

二、DELETE语句：

DELETE FROM 表名[ 删除条件子句]（没有条件子句，则会删除全部）

例子：

202303282258096773.png

补充：Mysql中的truncate table和delete语句都可以删除表里面所有数据，但是在一些情况下有些不同！

例子：

truncate table gag;

（1）truncate table删除速度更快，，但truncate table删除后不记录mysql日志，不可以恢复数据。（谨慎使用）

（2）如果没有外键关联，innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。（思考：删除百万级数据的时候是否可用truncate table）

（3）如果使用innodb_file_per_table参数，truncate table 能重新利用释放的硬盘空间,在InnoDB Plugin中，truncate table为自动回收，如果不是用InnoDB Plugin,那么需要使用optimize table来优化表，释放空间。

truncate table删除表后，optimize table尤其重要，特别是大数据数据库，表空间可以得到释放！

（4）表有外键关联，truncate table删除表数据为逐行删除，如果外键指定级联删除(delete cascade)，关联的子表也会会被删除所有表数据。如果外键未指定级联(cascde),truncate table逐行删除数据，如果是父行关联子表行数据，将会报错。

注意：

一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。因此，我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。

另外，扩展下删除和索引的联系（关于索引优化，后面的查询优化也会讲解），由于索引需要额外的维护成本；因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟），然后删除其中无用数据，此过程需要不到两分钟，删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

三、UPDATE语句：

UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

例子：

202303282258110844.png

优化：更新多条记录（往后会结合MyBatics写个实例）

202303282258122095.png

更新多条记录的多个值

202303282258132806.png

(1). 尽量不要修改主键字段。（废话，反正我就从没改过..）

(2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。

(3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。

(4). 避免UPDATE将要复制到其他数据库的列。

(5). 避免UPDATE建有很多索引的列。

(6). 避免UPDATE在WHERE子句条件中的列。

四、REPLACE语句：

根据应用情况可以使用replace 语句代替insert/update语句。例如：如果一个表在一个字段上建立了唯一索引，当向这个表中使用已经存在的键值插入一条记录，将会抛出一个主键冲突的错误。如果我们想用新记录的值来覆盖原来的记录值时，就可以使用REPLACE语句。

使用REPLACE插入记录时，如果记录不重复（或往表里插新记录），REPLACE功能与INSERT一样，如果存在重复记录，REPLACE就使用新记录的值来替换原来的记录值。使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑同时使用DELETE和INSERT时添加事务等复杂操作了。

在使用REPLACE时，表中必须有唯一有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。

用法：

（1）同INSERT

含义一：与普通INSERT一样功能

REPLACE INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);

含义二：找到第一条记录，用后面的值进行替换

REPLACE INTO score (id,change_type,score,user_id) VALUES (1,'吃饭',10,1)

此语句的作用是向表table中插入3条记录。如果主键id为1或2不存在就相当于插入语句：

INSERTINTO score (change_type,score,user_id) VALUES (‘吃饭’,10,1),(‘喝茶’,10,1),(‘喝茶’,10,1);

如果存在相同的值则不会插入数据。

（2）replace(object, search, replace)，把object中出现search的全部替换为replace。

用法一：并不是修改数据，而只是单纯做局部替换数据返还而已。

SELECT REPLACE('喝茶','茶','喝')//结果： 喝喝123

用法二：修改表数据啦，对应下面就是，根据change_type字段找到做任务的数据，用bb来替换

UPDATE score SET change_type=REPLACE(change_type,'做任务','bb')1

在此，做下对比：UPDATE和REPLACE的区别：

1）UPDATE在没有匹配记录时什么都不做，而REPLACE在有重复记录时更新，在没有重复记录时插入。

2）UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的字段都更新了。

其实REPLACE更像INSERT与DELETE的结合。

结尾

今天mysql优化的增删改优化就讲到这里，觉得有收获的朋友可以收藏关注，下两篇为查询部分的优化。大家可以关注一波。

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

  • 大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
  • 原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
  • 持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
  • 覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
  • 不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
  • 宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
  • 宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
  • 宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】 即可免费领取。

阅读全文
文章标签： MySQL 调优MySQL 优化