做木工的网站,soso搜搜网站收录提交入口,上海优化网站,做辅食网站整理自互联网 补充知识点#xff1a;操作数据语句优化的认识 通常情况下#xff0c;当访问某张表的时候#xff0c;读取者首先必须获取该表的锁#xff0c;如果有写入操作到达#xff0c;那么写入者一直等待读取者完成操作#xff08;查询开始之后就不能中断#xff0c;…整理自互联网 补充知识点操作数据语句优化的认识 通常情况下当访问某张表的时候读取者首先必须获取该表的锁如果有写入操作到达那么写入者一直等待读取者完成操作查询开始之后就不能中断因此允许读取者完成操作。当读取者完成对表的操作的时候锁就会被解除。如果写入者正在等待的时候另一个读取操作到达了该读取操作也会被阻塞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; 1truncate 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 字段名新值 [字段名新值] [更新条件] 例子 优化更新多条记录往后会结合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); 如果存在相同的值则不会插入数据。 2replace(object, search, replace)把object中出现search的全部替换为replace。 用法一并不是修改数据而只是单纯做局部替换数据返还而已。 SELECT REPLACE(喝茶,茶,喝) //结果 喝喝123 用法二修改表数据啦对应下面就是根据change_type字段找到做任务的数据用bb来替换 UPDATE score SET change_typeREPLACE(change_type,做任务,bb)1 在此 做下对比UPDATE和REPLACE的区别 1UPDATE在没有匹配记录时什么都不做而REPLACE在有重复记录时更新在没有重复记录时插入。 2UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除再插入新的记录。也就是说将所有的字段都更新了。 其实REPLACE更像INSERT与DELETE的结合。转载于:https://www.cnblogs.com/liulei-LL/p/7800127.html
