# DDM SQL使用规范 #### insert操作 - **insert写法规范** - 不建议逐条insert，推荐使用insert into values (),()..();语法。 - MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现批量插入。MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把预计批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。只有把rewriteBatchedStatements参数置为true, 驱动才会批量执行SQL。另外这个选项对INSERT/UPDATE/DELETE操作都有效。 ![](https://support.huaweicloud.com/bestpractice-ddm/public_sys-resources/caution_3.0-zh-cn.png) 开启rewriteBatchedStatements后的批量INSERT/UPDATE/DELETE操作要注意设置合理的batch size，batch size过大可能造成性能下降。如无特殊需求，建议batch size不超过1000。 - 拆分字段的值：不建议使用函数、表达式、子查询等，推荐使用常量值。 - 普通字段的值：不建议使用子查询，推荐使用常量、函数、表达式。 - **大批量数据导入** 推荐使用loaddata local infile来实现大批量数据导入。 ![](https://support.huaweicloud.com/bestpractice-ddm/public_sys-resources/caution_3.0-zh-cn.png) 这个地方只需要开一个session窗口导入即可，DDM内部会自动进行并行导入。 - **数据迁移场景** 建议使用mysqldump导出sql文件，再使用mysql source命令导入。具体操作请参考[数据从DDM实例导出](https://support.huaweicloud.com/usermanual-ddm/ddm_03_0029.html)。 - **auto_increment字段** - DDM使用Sequence来实现auto_increment语义，并保证全局唯一。 - 赋值：若使用auto_increment字段，建议不要在values子句中赋值，否则容易造成主键冲突。如果在values中赋了值，建议使用alter sequence语句更改，具体操作请参考[全局序列](https://support.huaweicloud.com/usermanual-ddm/ddm_03_0030.html)。 - 步长：auto_increment字段步长建议不要设置成1，会导致性能低下。默认设置成1000。 #### update与delete操作 - **普通更新** - 进行update/delete操作时，where条件建议带上拆分字段； - 无法带上拆分字段的场景，建议控制并发度，控制更新/删除涉及的数据条数。建议先用select查出相应的数据，double check确保数据范围无误后再实行update/delete操作。 - **拆分字段更新** - 条数限制：DDM拆分字段更新有数据量限制，一般不能超过10000条数据，数据量越少越好。若超过10000条数据，建议用改重建表的方式来做，或者是拆分成多次update操作来等价实现。 - 操作的时机：建议选择在业务低谷期做。 - **关联操作** 不建议进行表关联更新、表关联删除操作，即不建议进行多张表同时进行update/delete操作。 - **子查询及limit操作** 不建议update/delete语句中含有子查询。不建议update/delete语句中含有limit或order by limit语句。 - **case when函数** case when分支数量在不同规格实例下不同，在4U8G中不超过2500个，8U16G中不超过5000个，32U64G中20000个。 #### select操作 - **Order by 及 Limit函数** - "order by limit offset, count"场景，禁止给offset赋大数值，即禁止深度翻页。 - 如遇到临时表超限（Temp table limit exceeded）报错，说明排序中间数据产生了临时表且超限，考虑联系DDM值班进行SQL调优。 - **Group by函数** - 不建议select_list部分含有非group by列。 - 不支持不可下推的group_concat聚合函数内含有order by子句。 - 不建议distinct、group by字段多于3项。 - 不建议join、或者子查询操作之后含有group by操作。 - 不建议使用count(distinct ),sum(distinct )操作。 - 如遇到临时表超限（Temp table limit exceeded）报错，说明聚合操作中间数据产生了临时表且超限，考虑联系DDM值班进行SQL调优。 - 带GROUP BY分组查询的SQL语句，如果所有分组或单分组查询结果超过100万条（默认值），此时会有临时表超限约束（Temp table limit exceeded）。 - **Join函数** - select场景，建议join条件是每个表的拆分字段或使用广播表，或者是驱动表是一个小表（inner/left join驱动表是左表，right join驱动表是右表）。 - 不建议两个大表直接进行join操作。 - 不建议join on condition中含有非等值操作。 - 如遇到临时表超限（Temp table limit exceeded）报错，说明JOIN中间数据产生了临时表且超限，考虑联系DDM值班进行SQL调优。 - 不建议5张表以上进行join操作。 - join查询操作建议不要开启事务。 - 不建议在事务中进行join查询，开启事务会影响DDM对join算法的选择，无法使用最高效的算法。 ![](https://support.huaweicloud.com/bestpractice-ddm/public_sys-resources/caution_3.0-zh-cn.png) 这里的大表和小表的意思不是指原始表的规模，指的是经过where条件过滤之后的数据规模。 - **子查询** - 不建议子查询包含在OR表达式中，或者是子查询的关联条件包含在OR表达式中 - 不建议使用含有limit的标量子查询，如 select (select x from t2 where t2.id= t.id limit 1),a,b from t。 - 如果子查询和主表都路由到同一分片，建议在SQL前加/\*+db=xxx\*/来精准路由。 - 不建议子查询内部含有join语句。 - 不支持嵌套子查询。 - 不建议ROW表达式跟子查询做比较操作，如 select \* from t where (a,b,c)=(select x,y,z from t2 where ...)。 - 不建议select_list里面含有超过2个以上的子查询 。 #### DDL - **DDL执行时机** 对已有表进行DDL操作时建议放在业务低峰期进行。 - **分片数** 创建新拆分表时建议结合实际数据量进行合理预估，总分片数满足需求即可。不建议使用超出实际需求的分片数，拆分表分片数并非越多越好 - **高危DDL** 进行高危DDL时请仔细校验SQL后谨慎操作，如DROP TABLE, TRUNCATE TABLE等操作。 - **DDL失败修复** DDL命令如遇报错，可以使用"check table 表名"命令对各个分片表结构进行校验，识别出失败的分片进行针对性修复。如ALTER TABLE命令遭遇失败可以在命令前添加/\*+allow_alter_rerun=true/，开启ALTER语句的幂等可重入执行后重试，直到check table 命令提示各个分片表结构达到一致则可认为执行成功。 - **MDL锁导致执行DDL报错** - **背景** ：为保证DDL的可用性，DDM内部在执行DDL前会检查底层RDS相关表是否存在MDL锁。 若存在MDL锁,，则DDL会提前报错退出。 ``` metadata lock exists, one of MDL is [%s],DDL operation can not proceed, please use 'show metadata lock' to check current mdl, and use 'kill physical threadId@host:port' to clean it ``` - **可能出现的问题**：若系统中存在慢SQL，执行时间为几分钟不等，那么可能被MDL锁所阻拦，无法执行DDL。 - **解决方案1** ：在DDM控制台提高参数"ddl_precheck_mdl_threshold_time"的大小, 如提高到30分钟(1800秒)。 ![](https://support.huaweicloud.com/bestpractice-ddm/public_sys-resources/note_3.0-zh-cn.png) "ddl_precheck_mdl_threshold_time"表示DDL允许MDL锁持有的最大时长。持有超过这个时间长度的MDL锁，DDL才会报错，默认值为120秒。 - **解决方案2** ：执行show metadata lock查看是否因为持有慢事务的MDL锁阻塞了DDL的执行。若存在阻塞, 可以使用kill physical threadId@host:port 来关闭底层慢事务。配合/\*+allow_alter_rerun=true\*/的hint, 以及check table来查看和执行，直到DDL彻底执行完。 ![](https://support.huaweicloud.com/bestpractice-ddm/public_sys-resources/note_3.0-zh-cn.png) threadId为物理层RDS的线程id，host和port分别为物理层RDS的ip和端口。 - **DDL长时间卡死** 在业务低峰期执行DDL时如果遇到长时间卡死情况，请另开会话执行xa recover命令查看是否有慢事务存在，如存在慢事务挂起请及时联系值班人员解决。