首页  »   MySQL

mysql 优化学问备注

网友分享于:2018-05-16  浏览:0次
mysql 优化知识备注

1.批量插入数据

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

2.truncate table

   比delete速度要更快一些,但truncate删除后不记录mysql日志,不可以恢复数据

  如果没有外键关联,innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。

  如果表有外键关联,truncate table将会报错。如果外键指定级联删除(delete cascade),使用delete关联的子表也会会被删除所有表数据。

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

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

  TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。

  DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

  TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。

  如果要删除表定义及其数据,请使用 DROP TABLE 语句。

auto_increment计数器在truncate table后会重置为0

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

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

4.更新多条记录 

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)  

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

使用REPLACE插入记录时,如果记录不重复(或往表里插新记录),REPLACE功能与INSERT一样,如果存在重复记录,REPLACE就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一,形成一个原子操作。这样就可以不必考虑同时使用DELETE和INSERT时添加事务等复杂操作了。

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

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

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

参考文献:MySQL优化系列(一)--库与表基本操作以及数据增删改

相关解决方案

最新解决方案