MySQL数据库生产环境维护

MySQL数据库生产环境维护 需要使用、维护MySQL的可以看下表对象的DDL变更增加字段的注意事项DDL加速技巧MM架构的DDL变更技巧其他建议www.mysglops.com增加字段注意事项增加字段必须要有默认值例如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 O FIRST备注:这是我们强烈发对的行为,可能导致需要更多时间用于数据重整,以及对复制可能造成数据错乱应该写成: ALTER TABLE tmp test ADD COLUMN age T| NYINT UNS| GNED NOT NULL DEFAULT0;建议表设计都设置为 NOT NULL且默认值非NULL且有默认值的字段属性,有利于提高数据比对检索的性能,更加有利于索引结构的优化同一表增加多个字段,不分多条语句,而应只用一条变更语句例如ALTER TABLE tmp test ADD COLUMn age TINYINT UNSIGNED NOT NULL DEFAULT OALTER TABLE tmp test ADD COLUMN gmt Create Date TIMESTAMP NOT NULL DEFAULT 0000-00-00 00: 00: 00应该改写成:ALTER TABLE tmp testADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT OADD COLUMn gmt Create Date TIMESTAMP NOT NULL DEFAULT 0000-00-00 00: 00: 00DDL加速技巧●DDL操作的过程①申请表对象加锁,只允许读不允许更新复制一份变更对象表的结构代码原数据结构基础之上修改,新建一组临时文件, MyISAM引擎和 nodE引擎的文件有所不同,分别为:#**frm、#**MYD、#**MY,#**,frm、#*bd④变更对象表中的数据写入到临时表中速度读写完毕的情况下,完全锁定被变更的表,并且删除被变更表对临时文件执行 RENAME操作⑦刷新数据字典信息⑧释放锁●调整的SESS|ON变量①建议增加 SESSION级别的 sort buffer size值,以加速DDL过程需要做的排序操作;②建议增加SESS|ON级别的 read buffer size值,增加顺序读的速度:建议增加 SESSION级别的 tmp table size和 max heap table size值,增加内存缓存数据的能力;若是存在 nodE引擎转 MyISAM的操作,建议在线增加 key buffer_size的值,增加索引数据的缓存能力;www.mysglops.comMM架构的DDL变更技巧●M-M复制架构,只有一台提供全部或主数据服务推荐DDL语句,在当下MM架构中属于备用数据库服务器上优先执行,有四项优点:①不立即影响数据库提供的数据服务不阻塞主备之间数据复制的日志恢复操作DDL语句执行过程,若出问题只影响备库的稳定性,而很少会影响主库若有数据服务无缝切换功能(例如: heartbeat、自主数据层等),可以在备库执行完成后,进行数据服务提供的主备库切换,尤其是数据容量大的表变更时,可以减少对业务影响程度及减少停机维护时间和次数●M-M- multis复制架构a1Ma西tdb⊥adb⊥b1心.1p.1.1dbnEI是工上rerdbnamernd-replndAname nd Tdb≥axxX Icx×x_rnd1cp1c.112dB≥pdbo⊥⊥.11之∈1.1.11之针对双主复制,外加多从模式,就要分操作类型进行了,一般原则:线上环境不删除字段,修改为兼容性的属性,为此针对此类操作我们建议:P:10.101.124也即中转兼备用服务器上,先执行 SET Sq|log_bin=0;然后执行DDL变更;待上一步骤完成,再在对应的只读性质的丛库上执行DDL变更;待上述步骤都正常完成,再在|P:10.10.1.123服务器上执行 SET sql log_bin=0;然后执行DDL变更;www.mysglops.com其他事项索引优化命令执行顺序和坚持的原则:①优先执行创建索引的语句;确保增加了对应的索引结构之后,再删除多余的索引用户行为触发而执行频率越髙的SαL命令,性能确保其性能最佳;④4数据变更类型( UPDATE OR DELETE)的SQL命令,索引可选择性优先;数据库对象表结构的变更①若是增加字段或字段属性值域扩大等类似操作,必须优先于应用程序发布;②若是字段的删除操作,则等应用程序成功发布之后再执行;③若是字段类型变更,不兼容的情况下,则与版本发布同一时间段内完成(注:一般是停机维护类型);④若是涉及索引的变更,则先于应用程序发布进行增加,等应用程序成功发布上线之后,再删除多余的索引。www.mysglops.com数据库对象表数据的DMLDML语句书写建议大数据量的 DELETE OR UPDATE定期规律性清理数据的 DELETEMM架构的大数据量DML技巧www.mysglops.comDML语句书写建议DML语句不允许出现@ number方式替代字段名称不合理的写法: UPDATE table name Set@1 NOWO WHERE@2=1;正确的写法: UPDATE table name set column name1=NOW( WHERE column name2=1;UPDATE OR DELETE禁用LM|T子句不合理的写法: UPDATE table name set column name1NOW( WHERE column name2=1LMT1;正确的写法: UPDATE table name set column name1= NOWO WHERE column name2=1;INSERT语句需要写清楚值和字段对应关系不合理的写法:NSERT iNto table name VALUES(NOWO,DATE ADD(NOWO, INTERVAL +1 DAY);正确的写法:INSERT INTO table name(gmt create, gmt modify) VaLUES(NOWo, DATE ADD(NOW(, INTERVAL +1 DaY)DML语句少用不确定性函数常见被大家使用的不确定性函数:UUID()、RAND()、 SYSDATE()等函数,若无特殊用处之外,请以确定性函数替代之,推荐阅读的技术文章:曾用于内部培训的PPT内容: MYSQL开发规范与实用技术交流www.mysqlops.com大数据量的 DELETE OR UPDATE可能出于某些原因和运营目的,需要对数据库中的数据进行大量的清理或更改某字段的值,分别举个示例:①网络专项整治的时期,需要删除大量含某些关键词的内容;给符合某一条件(例如:等级,在线时长)的游戏玩家,赠送100-1000不等数量的游戏币;给岀的2个数据修改需求示例,若是直接根据相关要求去做,一个是需要用到模糊查询,另一个数据更新条件也没有合理索引可用,为此可能造成表对象表级锁被长时间锁住,而且阻塞其他更改类型数据操作服务,所以我们不得不采用更合理的办法,建议如下步骤解决:①设计并创建一张表 tmp_ pk data,用于记录将要被修改记录的主键,及需要的相关信息优先考虑在备库上跑一条SQL命令或存储过程的方式,把主键及相关数据写到表tmp_ pk data中;编写一个存储过程,使用游标循环控制获得τmρ_ pd data的信息,根据主键更新或删除目标表的数据,且建议此操作在备库上完成(注释:必须是双主复制模式,才可在备库上执行);www.mysglops.com