但行好事
莫论前程❤

MySQL优化系列(一)–库与表基本操作以及数据增删改

转载自:https://blog.csdn.net/jack__frost/article/details/71194208

本系列:demo下载
这个系列将系统化MySQL一些高级用法。打算先讲很多人关注的使用方式(增删改查以及其优化),然后就讲数据库和表的操作(很多我们学习忽略的地方),接着就是引擎还有更高级的查询等等。

本文章结构:

(一)操作数据操作语句优化认识;
(二)INSERT语句;
(三)DELETE语句;
(四)UPDATE语句;
(五)REPLACE语句;
(六)库与表基本操作

一、操作数据语句优化认识:

虽然通过锁机制,可以实现多线程同时对某个表进行操作,但当某个线程作更新操作时,首先要获得独占的访问权。在更新的过程中,所有其它想要访问这个表的线程必须要等到其更新完成为止。此时就会导致锁竞争的问题,从而导致用户等待时间的延长。

因此:要提高MySQL的更新/插入效率,应首先考虑降低锁的竞争,减少写操作的等待时间。 (本系列在后面才讨论表设计的优化)

使用前说明:偷点懒,用之前写出来的数据库-INNODB存储引擎。(请导入数据库文件)。数据库说明数据库文件

CREATE TABLE `score` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `change_type` VARCHAR(255) NULL DEFAULT NULL,
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `score` INT(11) NULL DEFAULT NULL,
    `user_id` BIGINT(20) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
//DEFAULT CHARSET=utf8 
COLLATE='utf8_general_ci';
//外键在以后再聊
CREATE TABLE `gag` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `gag_time` DATE NULL DEFAULT NULL,
    `user_id` BIGINT(20) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci';

二、INSERT语句:

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

注意:

如果要插入的值列表包含所有字段并且顺序一致,则可以省略字段列表。
可同时插入多条数据记录!

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

例子:

INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1);

优化:

(1)当我们需要批量插入数据的时候,这样的语句却会出现性能问题。

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

例子:

INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);   
(2)将进程/线程数控制在2倍于CPU数目相对合适
(3)采用顺序主键策略(例如自增主键,或者修改业务逻辑,让插入的记录尽可能顺序主键)
(4)考虑使用replace 语句代替insert语句。(REPLACE语句请参考下文,有详细讲述)

三、DELETE语句:

DELETE FROM 表名[ 删除条件子句]
#没有条件子句,则会删除全部 
DELETE FROM gag;         # 删除全部
DELETE FROM gag WHERE id=1;

补充:

Mysql中truncate table和delete语句都可以删除表里面所有数据,但是在一些情况下有些不同!
truncate table gag;
(1)truncate table速度要更快一些,但truncate删除后不记录mysql日志,不可以恢复数据。
(2)如果没有外键关联,innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。
(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逐行删除数据,如果是父行关联子表行数据,将会报错。

(5)auto_increment计数器在truncate table后会重置为0.与是否有外键关联没有关系。

注意:

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

MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以在超大型数据库中,删除时处理好索引关系非常重要。推荐的折中方法:在删除数据之前删除这那几个索引,然后删除其中无用数据,删除完成后重新创建索引。

四、UPDATE语句:

UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]
# 例子
UPDATE score SET change_type='洗澡' WHERE id=2;

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

Update score  
  SET change_type = CASE id  
    WHEN 1 THEN 'value1'  
    WHEN 2 THEN 'value2'  
    WHEN 3 THEN 'value3'  
  END  
WHERE id IN (1,2,3) 

更新多条记录的多个值

Update score  
  SET change_type = CASE id  
    WHEN 1 THEN 'value1'  
    WHEN 2 THEN 'value2'  
    WHEN 3 THEN 'value3'  
  END , 
    score = CASE id
     WHEN 1 THEN 1
    WHEN 2 THEN 2
    WHEN 3 THEN 3
  END
WHERE id IN (1,2,3)  
(1). 尽量不要修改主键字段。
(2). 当修改VARCHAR型字段时,尽量使用相同长度内容的值代替。
(3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
(4). 避免UPDATE将要复制到其他数据库的列。
(5). 避免UPDATE建有很多索引的列。
(6). 避免UPDATE在WHERE子句条件中的列。

五、REPLACE语句:

根据应用情况可以使用replace语句代替insert/update语句。

例如:如果一个表在一个字段上建立了唯一索引,当向这个表中使用已经存在的键值插入一条记录,将会抛出一个主键冲突的错误。如果我们想用新记录的值来覆盖原来的记录值时,就可以使用REPLACE语句。

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

在使用REPLACE时,表中必须有唯一有一个PRIMARY KEYUNIQUE索引,否则,使用一个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不存在就相当于插入语句:

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

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

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

# 用法一:并不是修改数据,而只是单纯做局部替换数据返还而已。
SELECT REPLACE('喝茶','茶','喝')
# 结果:  喝喝

用法二:

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

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

在此,做下对比:UPDATE和REPLACE的区别:

  • UPDATE在没有匹配记录时什么都不做,而REPLACE在有重复记录时更新,在没有重复记录时插入。
  • UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除,再插入新的记录。也就是说,将所有的字段都更新了。

其实REPLACE更像INSERTDELETE的结合。

六、库与表基本操作:

详解:[MySQL数据库系列(7)—–数据库管理语句(查看、修改)](https://www.liupeng.mobi/archives/1946)

补充

库的基本操作

# 查看当前数据库
select database();
# 显示当前时间、用户名、数据库版本
select now(), user(), version();
# 创建库
create database[ if not exists] 数据库名 数据库选项
    数据库选项:
        CHARACTER SET charset_name
        COLLATE collation_name
# 查看当前库信息
show create database 数据库名
# 修改库的选项信息
alter database 库名 选项信息
# 删除库
drop database[ if exists] 数据库名
        同时删除该数据库相关的目录及其目录内容

表的基本操作:

# 创建表
 create [temporary] table[ if not exists] [库名.]表名 ( 表的结构定义 )[ 表选项]
        每个字段必须有数据类型
        最后一个字段后不能有逗号
        temporary 临时表,会话结束时表自动消失
        对于字段的定义:
            字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']

# 删除列
ALTER TABLE 【表名字】 DROP 【列名称】
# 增加列
ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT ‘注释说明’
# 修改列的类型信息
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称(这里可以用和原来列同名即可)】 BIGINT NOT NULL COMMENT ‘注释说明’
# 重命名列
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT ‘注释说明’
# 重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
# 删除表中主键
Alter TABLE 【表名字】 drop primary key
# 添加主键
ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
# 添加索引
ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
# 添加唯一限制条件索引
ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
# 删除索引
Alter table tablename drop index emp_name;
# 查看所有表
SHOW TABLES;
# 查看表机构:
 SHOW CREATE TABLE 表名    (信息更详细)
    DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
    SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
# 修改表:对表进行重命名
         RENAME TABLE 原表名 TO 新表名
        RENAME TABLE 原表名 TO 库名.表名    (可将表移动到另一个数据库)
        -- RENAME可以交换两个表名
# 删除表
DROP TABLE[ IF EXISTS] 表名
# 清空表数据
TRUNCATE [TABLE] 表名
# 复制表结构
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
赞(0) 打赏
未经允许不得转载:刘鹏博客 » MySQL优化系列(一)–库与表基本操作以及数据增删改
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏