(四)SQL MODE变化和优化online操作.pdfVIP

（四）SQL MODE变化和优化online操作 本⽂是基于MySQL-5.7 .7-rc版本，未来可能 还会发⽣更多变化。 1、SQL MODE变化 a. 默认启⽤ STRICT_TRANS_TABLES 模式； b . 对 ONLY_FULL_GROUP_BY 模式实现了更 杂的特性⽀持，并且也被默认启⽤； c . 其他被默认启⽤的sq mode还有 NO_ENGINE_SUBSTITUTION ； iMySQL建议 对⼴⼤MySQL使⽤者⽽⾔，以往不是那么严格的模式还是很⽅便的，在5.7版本下可 能会觉得略为不适，慢慢习惯吧。⽐如向⼀个20字符长度的VARCHAR列写⼊30个字 符，在以前会⾃动截断并给个提⽰告警，⽽在5.7版本下，则直接抛出错误了。个⼈认 为这倒是⼀个好的做法，避免各种奇葩的写法。 新特性实践 -- 查看默认的 sql_mode [yejr@] select @@sql_mode; + | @@sql_mode | + | ONLY_FULL_G OUP_BY,ST ICT_T ANS_TABLES,NO_AUTO_C EATE_USE ,NO_EN + -- 插⼊50个字符 [yejr@] insert into t_char select 0, repeat(x,50); E O 1406 (22001): Data too long for column uname at row 1 -- 修改本 session 的 sql_mode [yejr@] set sql_mode = ONLY_FULL_G OUP_BY,NO_AUTO_C EA Query OK, 0 rows affected (0.00 sec) -- 去掉 ST ICT_T ANS_TABLES 模式后 [yejr@] select @@sql_mode; ++ | @@sql_mode | ++ | ONLY_FULL_G OUP_BY,NO_AUTO_C EATE_USE ,NO_ENGINE_SUBSTITUTION | ++ [yejr@] insert into t_char select 0, repeat(x,50); Query OK, 1 row affected, 1 warning (0.00 sec) -- 提示有告警信息 ecords: 1 Duplicates: 0 Warnings: 1 [yejr@] show warnings; ++++ | Level | Code | Message | ++++ | Warning | 1265 | Data truncated for column uname at row 1 | ++++ 因为 uname 字段的长度为 40 个字符。 2、优化online操作 例如修改buffer poo 、修改索引名 （⾮主键）、修改REPLICATION FILTER 、修改 MATER⽽⽆需关闭SLAVE线程 等众多特性。 可以在线修改buffer poo 对DBA来说实在太⽅便了，实例运⾏过程中可以动态调整， 避免事先分配不合理的情况，不过 innodb_buffer_poo _instances 不能修改，⽽且 在 innodb_buffer_poo _instances ⼤于 1 时，也不能将 buffer poo 调整到 1GB 以内，需 要稍加注意。 如果是加⼤buffer poo ，其过程⼤致是： 1、以innodb_buffer_pool_chunk_size为单位， 配新的内存pages； 2、扩展buffer pool的AHI(adaptive hash index)链表，将新 配的pages包含进 3、将新 配的pages添加到free list中； 如果是缩减buffer poo ，其过程则⼤致是： 1、重整buffer pool，准备回收pages； 2、以innodb_buffer_pool_chunk_size为单位，释放删除这些pages （这个过程会有 3、调整AHI链表，使⽤新的内存地址。 实际测试时，发现在线修改 buffer poo 的代价并不⼤，SQL命令提交完毕后都是瞬间 完成，⽽后台进程的耗时也并不太久。在⼀个并发128线程跑tpcc压测的环境中，将 buffer poo 从32G扩展到48G ，后台线程耗时 3秒，⽽从 48G 缩减回 32G 则耗时 18 秒，期间压测的事务未发⽣任何