更新时间:2024-03-01 GMT+08:00
分享

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操作都有效。

      开启rewriteBatchedStatements后的批量INSERT/UPDATE/DELETE操作要注意设置合理的batch size,batch size过大可能造成性能下降。如无特殊需求,建议batch size不超过1000。

    • 拆分字段的值:不建议使用函数、表达式、子查询等,推荐使用常量值。
    • 普通字段的值:不建议使用子查询,推荐使用常量、函数、表达式。
  • 大批量数据导入

    推荐使用loaddata local infile来实现大批量数据导入。

    这个地方只需要开一个session窗口导入即可,DDM内部会自动进行并行导入。

  • 数据迁移场景

    建议使用mysqldump导出sql文件,再使用mysql source命令导入。具体操作请参考数据从DDM实例导出

  • auto_increment字段
    • DDM使用Sequence来实现auto_increment语义,并保证全局唯一。
    • 赋值:若使用auto_increment字段,建议不要在values子句中赋值,否则容易造成主键冲突。如果在values中赋了值,建议使用alter sequence语句更改,具体操作请参考全局序列
    • 步长: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算法的选择,无法使用最高效的算法。

      这里的大表和小表的意思不是指原始表的规模,指的是经过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秒)。

      “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彻底执行完。

      threadId为物理层RDS的线程id,host和port分别为物理层RDS的ip和端口。

  • DDL长时间卡死

    在业务低峰期执行DDL时如果遇到长时间卡死情况,请另开会话执行xa recover命令查看是否有慢事务存在,如存在慢事务挂起请及时联系值班人员解决。

相关文档