数据库运维管理基础知识.pptVIP

大数据量DML操作的技巧 大数据量INSERT INSERT INTO … VALUES(),(),()…. LOAD DATA …. 写入临时表，再INSERT … SELECT … 大数据量DML操作的技巧 大数据量UPDATE SELECT 唯一字段，变更字段 查询结果集写入临时表 给临时表创建组合索引 循环临时表的每条记录 依据临时表的唯一字段值，更新目标表的相关字段值 大数据量DML操作的技巧 大数据量DELETE 若是定期且特殊规则删除大批量的数据，则建议采用分区表 若是定期且无特殊规则删除大批量的数据，则分区表+UPDATE 若是上述2种方式无法使用，则可以主备轮流删除+数据服务切换 若是上述3种方式无法使用 调整参数…，直接执行DELETE批量语句 先查询出要删除数据的唯一标识写入临时表，然后游标循环删除 数据库版本选择 数据库监控体系 数据库备份恢复策略 大数据量DML操作的技巧 大表DDL变更注意事项和变更技巧 在线数据库迁移的技巧 目录 大表DDL变更注意事项和变更技巧 变更注意事项 表级别锁 InnoDB存储引擎的数据字典锁 DDL加速技巧 大表DDL变更注意事项和变更技巧 变更注意事项 增加字段必须要有默认值 例如: ALTER TABLE tmp_test ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’; 增加的字段不能指定字段顺序，必须默认尾部增加模式 例如：增加一个字段到表对象的头部 ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0 FIRST; 这是我们强烈反对的行为，可能导致需要更多时间用于数据重整， 以及对复制可能造成数据错乱，应该写成： ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0; 大表DDL变更注意事项和变更技巧 变更注意事项 建议表设计都设置为NOT NULL 且默认值 非NULL且有默认值的字段属性，有利于提高数据比对检索的性能， 更加有利于索引结构的优化 同一表增加多个字段，不分多条语句，而应只用一条变更语句 例如： ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0; ALTER TABLE tmp_test ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT ‘0000-00-00’; 应该改写成： ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0, ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT 0000-00-00; 大表DDL变更注意事项和变更技巧 表级别锁 InnoDB存储引擎的数据字典锁 DDL变更注意事项 DDL加速技巧 DDL操作的过程 ① 申请表对象加锁，只允许读不允许更新 ② 复制一份变更对象表的结构代码 ③ 原数据结构基础之上修改，新建一组临时文件，MyISAM引擎和InnoDB引擎 的文件有所不同，分别为：#***.frm、#***.MYD、#***.MYI, #***.frm、#***.ibd ④ 变更对象表中的数据写入到临时表中 ⑤ 速度读写完毕的情况下，完全锁定被变更的表，并且删除被变更表 ⑥ 对临时文件执行RENAME操作 ⑦刷新数据字典信息 ⑧ 释放锁 DDL变更注意事项 DDL加速技巧 调整的SESSION变量 ① 建议增加SESSION级别的sort_buffer_size 值，以加速DDL过程 需要做的排序操作； ② 建议增加SESSION级别的read_buffer_size 值，增加顺序读的速度； ③ 建议增加SESSION级别的tmp_table_size 和max_heap_table_size 值，增加内存缓存数据的能力； ④ 若是存在InnoDB引擎转MyISAM的操作，建议在线增加 key_buffer_size的值，增加索引数据的缓存能力； DDL变更技巧 大表删除 创建被删除表的软链接 执行DROP