MySQL开发规约实战

里里MySQL开发规约实战芦火阿里云运维专家C-可里云前言/topic/java2020C-可里云语句规范要建立在结构规范的基础上字符集统一排序规则常用字符集描述默认校对规则最大长度备注latin1cp1252WestEuropeanlatin1_swedish_ci1早期官方默认字符集GBKSimplifiedChinesegbk_chinese_ci2非国际标准utf8_general_ci3utf8mb4utf8mb4_0900_ai_ci4官方8.0默认字符集字段解决业务歧义、解决业务歧义、隐式转换问题字段长度C-可里云语句规范要建立在结构规范的基础上索引80%的语句性能问题都可以靠索引解决单列索引要充分评估定期review索引有效性不要走极端复合索引所有列所有列都建单列索引C-可里云SQL语句编写规范规范语法不兼容语法Selectid,count(*)fromsbtest.sb别名所有返回列要给有意义的命名，与列名原则一致，强制as关键词Selectid,count(*)id_countfromsbtest.sbtest1groOnly_full_groupbyC-可里云SQL语句编写规范执行顺序5.WINDOWfunctions语句性能数据流的流向数据返回逻辑数据的筛选机制比如：leftjoinwhere场景C-可里云SQL语句编写规范如何判断语句是否已最优:explainC-可里云SQL语句编写规范禁止项如wherelength(name)=,Admin,或where建议项使用join替代子查询减少过于复杂的查询.&拼串写法C-可里云SQL语句编写规范用数据库的思维考虑SQL-一个语句解决所有问题导致过于复杂的查询，执行计划不稳定-开发应用的逻辑写语句所有的运算、判断应用逻辑都放到SQL实现-存储过程使用过重难以调试、定位问题-少即是美每一层结果集都要最大限度的减小-新feature谨慎应用到生产中C-可里云SQL语句编写规范Sql改写-join请注意join键为PK，也就是左表右表应该是1对1的关系C-可里云SQL语句编写规范Sql改写-join等价于selectcount(a.id)fromsbtest1a;Sql改写一般会出现在复杂查询的Join场景中半连接：exists,in此类查询过慢时，请考虑是否可通过SQL改写优化C-可里云SQL语句编写规范Sql改写-分页统计selectcount(*)from执行计划上无本质区别，但语句冗余C-可里云SQL语句编写规范Sql改写-分页统计selectcount(a.id)fromsbtest1a;2.进一步寻找优化空间C-可里云事务的使用与优化并发控制的单位概念不再具体解释大事务不等于长事务C-可里云事务的使用与优化事务的问题单个事务不拆分存放4.锁问题优化大事务1.大事务拆分为小事务长事务2.事务分解（不必要的请求摘除）3.应用侧保证一致性事务使用基本原则在保证业务逻辑的前提下，尽可能缩短C-可里云事务的使用与优化事务问题定位长事务：Information_schema.innodb_trx例如：SELECTtrx.trx_id,trx.trx_started,锁问题information_schema.innodb_performance_schema.data_lock_wC-可里云开发中常见问题与最佳实践分页问题传统写法扫描大量无效数据后，返回请求数据C-可里云开发中常见问题与最佳实践分页问题select*fromsbtest1whereid>#max_id#orderbyidlimitn;•适用顺序翻页的场景，每次记录上一页#max_id#带入下一次查询中select*fromsbtest1asainnerjoin(selectidfromsbtest1orderbyidlimitm,n)asb•适用只按照id进行分页,无where条件select*fromsbtest1asainnerjoin(selectidfromsbtest1wherecol=xxxxorderbyidlimitm,n)asb•适用于带where条件，同时按照id顺序分页•此时，需要在where条件上创建二级索引C-可里云开发中常见问题与最佳实践大表数据清理①单次delete行数过多，容易导致锁堵塞、主从复制延迟、影响线上业务ForIin“max(id)-min(id)/1000”C-可里云开发中常见问题与最佳实践隐式转换问题隐式转换发生在比较值类型不一值的场景下，除去一些规定情况最终都是转换为浮点数进行比较此类问题在编写时很难发现，上线导致严重性能问题C-可里云开发中常见问题与最佳实践循环外部循环应用侧实现主要问题来自每次请求的rt例如：insert（db交互)建议batch一次写入一般常用在存储过程事务无法保证Commit;endwhile频繁commit或:endwhile数据一致性以及长事务C-可里云开发中常见问题与最佳实践存储过程中的事务处理C-可里云开发中常见问题与最佳实践常见问题-1最左