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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 前文一篇「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)让大家知道msql优化,究竟在优化什么,本篇为mysql优化专题的第二篇,主要先从增删改进行优化。

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

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

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

一、NSERT语句:

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

注意:

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

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

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

优化前例子:

优化策略:

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

优化后例子

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

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

(4)考虑使用replace 语句代替insert语句。(REPLACE语句请参考下文,有详细讲述)

二、DELETE语句:

DELETE FROM 表名[ 删除条件子句](没有条件子句,则会删除全部)

例子:

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

例子:

truncate table gag;

(1)truncate table删除速度更快,,但truncatetable删除后不记录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 字段名=新值[, 字段名=新值] [更新条件]

例子:

优化:更新多条记录(往后会结合MyBatics写个实例)

更新多条记录的多个值

(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优化的增删改优化就讲到这里,觉得有收获的朋友可以收藏关注,下两篇为查询部分的优化。大家可以关注一波。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
171
分享
相关文章
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
115 15
MySQL底层概述—7.优化原则及慢查询
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
80 9
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
66 23
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
38 3
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等