开发者学堂课程【MySQL 实战进阶：MySQL 开发规约实战】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/83/detail/1311
MySQL 开发规约实战
内容介绍：
一、前言
二、语句规范要建立在结构规范的基础上
三、SQL 语句编写规范
四、事务的使用与优化
五、开发中常见问题与最佳实践
一、前言
1、本节课讲解内容是 MySQL 开发规约实战 。从前言开始，JAVA 开发手册大家或多或少听说过，是阿里集团对JAVA开发做出一系列的相对标准的规范，其中有一部分涉及到了 MySQL 相关的内容，包括下图列出的建表规约、索引规约、SQL 语句怎样写，还有包括一些针对数据库操作使用 ORM。
2、本课程主要针对规约里的前三项做出更详细的讲解，更偏原理一些。开发手册更大一部分内容是针对 JAVA 开发的，MySQL是其中一部分内容，现在我们用到的数据库有多种多样的开发工具，包括C++、PHP等。实际上，后台访问数据库MySQL为多，像 sql 怎样去写是一个通用性的标准。
这节课叫开发规约实战，会有很多理解成怎么写 SQL？实际上 sql 规不规范是和基础有很强的关连性的，包括结构的不规范，写出来的语句不管怎么优化，最终可能都是有问题的。
二、语句规范要建立在结构规范的基础上
1、字符集
先从字符集说起，字符集实际上是最常识的，虽然常用的写的是 latin1、gbk、utf8 和 utf8mb4，有大概四种比较常用的，但目前互联网是以 utf8 为主。
因为 mb4 在近些年用的多一些，但是有一个问题，包括之前提到的版本5.5，5.6，5.7，这些默认的表建的字符集可能都是 utf8 的，但是到了2.0以后会考虑emoji 新的业务，所以说 mb4 是更通用的字符集。但是有一个问题，这里写的统一排序规则，因为在8.0之前默认的是 utf8 的排序规则，
到了8.0之后是 utf8mb4_0900_ai_ci，这个字符集是8.0独有的，也是默认的。但是经常会在这个问题上出现不兼容的情况，字符集不一样，在写的时候都比较不出来，所以一定要注意。
2、统一字段名、类型
字段有几个比较大的规则，第一是业务上的需求。不光是字段名，还有表名，统一字段名、统一类型，包括其它相对常识类的内容，表名肯定是要跟业务相关的，包括字段名也是跟业务相关的。统一字段名和类型是解决业务上的歧义。比如说 mysql 内部的系统里，有一张 information下边的 tables 表，按照不太规范的命名如“tablename”字段可能就会命名成“table”。
但是在 schema 这张表里，有一个 schema name，如果把这个命名成 name，可能会重复。在写查询的时候，可能会导致意义上的混乱。还有一个类型的统计问题，类型的统计主要是隐式转换的问题，包括表的连接和位次查询等，都可能会导致隐式转换。
3、字段长度 varchar(255)
比较常见的是 varchar(255)，指不知道业务将来存多少字段的情况下，先设成255，这种情况只是在开发阶段方便，方便建表。但实际真实生产业务之后，会有很严重的性能隐患，包括像索引评估。假如说在 255 列长上建一个索引，实际上索引评估会考虑列长度，会按照最长去考虑，评估索引是否能用。字段太多的时候，如果字段有2个255或3个255，需要建一个复合索引，真正的值可能每个字段里只存了10或20，会发现索引是建不出来的，会报错。这个时候对线上维护，还有后续的业务开发实践是有影响的。
4、定义 id int primary key
定义主键 primary key 必须要有。在没有主键的时候，会有很多问题，比如说一致性、复制性能等问题。还有无符号定义主键，id int primary key 直接定义主键。一般一个表的 ID 可能是一个字增，int 是一个有符号的，就会导致 int 可用的值少一半，本身 int 最大的值在有符号的时候是 21亿，如果定义成无符号是42 亿。在数据量增长很快的时候，可能有一半的数据范围没有用上，可能会导致某个 ID 或某个字增长满。
5、禁止 Null 值
默认间段有很多字段，如果不写 default 给一个有意义的值，就默认是空值。但是空值有一些问题，包括排序、建索引的时候，还有做表整理这种查询的时候，空值和空值比较的时候，单拿一个排序的发明来说，两个行在做比较的时候，空值比空值会等于随机一个排序，实际上是变成业务上的乱序，这是空值的问题。像无主键表一样，可能会导致复制、数据不一致的问题，所以一般来说，尽量要避免空值。还有一个场景，虽然业务上经常会允许空值，但实际上去查的时候经常会用 exnight 返回默认值，谓词是有问题的，包括一些索引的利用都可能会出现问题，所以这是一个相对严格的要求。
6、索引
（1）80%的语句性能问题都可以靠索引解决，但是有一个问题，就是用不用所有的主键索引？可能有些同学会觉得比较夸张，但真实生产环境里是有很多这种情况的。比如说有20个列，每个列上都会有单列索引。定期 review 索引的有效性，这在 mysql 里面可能不太好定位，经常检查的是随着生产环境和业务的发展，会建很多新的索引出来，这时候要看覆盖索引是不是已经 cover 之前的旧索引，旧索引已经没有用了，再去维护它还要多一段 io 的成本，所以把它删掉就好。
（2）不要走极端，包括两点复合索引所有列与所有列都建单列索引。比如说一张表有七八个列，只对一列有索引。大家会了解索引有回表还是不回表的区别，干脆直接建立一个复合索引，是这张表的所有列，这样是不可取的，因为相当于有又另外维护了一张表，所有列都要建单列索引，和复合索引所有列是一样的。索引有关内容，请关注【MySQL 表和索引优化实战】课程。
三、SQL 语句编写规范
1、下面进入到课程真正要讨论的内容，先是一些SQL 语句的编写规范，然后是事务的使用与优化，还有是开发中经常见到的问题与最佳实践。
2、规范语法
先说一个最基础的规范，MySQL 本身是一个相对成熟的产品，但是他支持的语法不是标准 SQL 语法。
像 group id 直接 select*，在一些标准的、传统的数据结构中，这是一个非法的语法，因为根本不支持。还有8.0已经淘汰的语法，这两个语法实际上都不是标准语法，不建议在生产环境中使用。不兼容语法在版本升级以后，会带来大量的应用报错，所以尽量要把语法写得规范，符合 SQL 标准就可以了。
3、别名
如果建一个子查询，把查询框起来，如果不给别名，根本不能执行。但还有一种是在列上的别名，因为有一种应用按照顺序获取导入列值。但是有一个问题，如果不给别名，会导致语义不清，当返回一个值不给名，不知道是什么意思。还有一种标准的写法是要 as 一个别名，如果不写的话，实际上是支持的。语句特别复杂的时候，这种情况下可能会造成看这个语句有些乱，建议从一开始就要写标准。
4、SQL 的执行顺序
有的同学可能第一次了解SQL 语句是一个怎样执行的顺序，这是一些经常见到的关键词 from。
从数据流上来看，首先做的是数据筛选 FROM、WHERE、GROUP BY、HAVING、WINDOW，从 SELECT 以下开始执行运算，包括 DISTINCT、UNION、ORDER BY、LIMIT。要注意数据流是怎样流的，比如说 order by limit 场景在最后才运行。limit 300000，20 取20条数据，为什么会这么慢？是因为把所有数据取出来以后，最后才做 OFFSET，也就是返回的数据量特别大，所以逻辑要搞清楚。还有数据的筛选机制，我很长一段时间都认为 where 先做数据筛选，然后才做 join，这种情况是不成立的。因为从这里看是先做 join，然后做 where，但是有一种情况，在做 int join 的时候，会把 where 的条件带进去，在这种情况下 int join 和 where 的条件是一样的，是一个标准的筛选。但是做 Left join 和 Right join 时，是先做一个数据集，通过 Where 筛选数据，注意中间返回的结果集数据流向，以最优的方式写出来。
5、如何判断语句是否已最优:explain
如何判断语句是否已最优，先要看执行计划，执行计划判断语句的逻辑顺序、数据访问是否正常。可能有些同学不太了解 explain 里面的内容都代表什么，像前言里说的开发手册，最起码要求认识范围扫描。要求是如何避免全面扫描，包括 indx 全索引扫描，接下来是性能角度判断，TYPE区的命令从下往上看，从 system 开始向上性能越来越差。
反过来说，如果只看左边的 TYPE，下面那张图是 TYPE。
真实执行情况看 Extra 区的命令，结合上图来看一下执行计划：
从上往下看，第一个 C 是 table 驱动表,后面用了 idx_pk,同时用了index condition、where、MRA , MRA 是优化器加速。Using index condition 是索引初步过虑，有一些数据要回表，用select* 回表再过滤其它条件。Using where 是通过索引过滤数据，从外面取数据向里面做循环。Using where&Using index 使用 atdex 索引扫描列，这样整个执行计划就完成了。
