数据库日常使用优化

Posted on | Edited on

大表操作

在数据量特别少的时候，如何操作都不会影响数据库的使用。但是当表的数据量特别大时，就要万分注意，任何操作可能对数据库服务造成的阻塞都要考虑到。

删除大表

如果想删除一张有十几亿条数据的大表，不能直接drop table，因为有可能内存缓冲池里也有该表数据，删除表会去遍历内存缓冲区。会导致内存缓冲区被锁住，以至于整个服务hang住，所有读写都会被堵塞。并且从磁盘中删除几百G的大文件，磁盘IO也会瞬间打满，会对性能造成很大影响。比较安全的操作就是批量的删除数据，最后再drop回收空间。

在线DDL

线上环境的DDL一般会用到一些专用的工具，避免直接执行造成服务使用异常。

Mysql5.5的DDL工具

copy数据的过长需要耗费额外的存储空间，并且执行过程耗时较长。copy数据过程中有锁，堵塞了写操作

mysql5.6、5.7原生Online-DDL

MYSQL 5.6之后，支持更多的ALTER TABLE类型避免copy data操作，并且支持在DDL的过程中不阻塞DML操作，即实现ONLINE特性。

1
ALTER TABLE  TABLE_NAME  ADD COLUMN _new_column  ALGORITHM = inplace, LOCK = default;

ALGORITHM选项

  • INPLACE
  • COPY
  • DEFAULT

LOCK选项

  • NONE
  • SHARED
  • DEFAULT
  • EXCLUISIVE

  • 仍然存在排他锁，有锁等待的风险。
  • 跟5.6一样，增量日志大小是有限制的，由innodb_online_alter_log_max_size参数决定大小
  • 有可能造成主从延迟
  • 无法暂停，只能中断
pt-online-schema-change

整个过程，只有第五步的时候会进行锁表，阻塞服务，时间极短

Online DDL与PT-OSC对比

  • 1.原表不能存在触发器。
  • 2.原表必须存在主键 PRIMARY KEY 或者 UNIQUE KEY
  • 3.外键的处理需要指定 alter-foreign-keys-method 参数，存在风险
  • 4.在 pt-osc 的执行过程中，如果有对主键的更新操作则会出现重复的数据