内容简要：
一、前言
二、SQL语句编写规范
三、事务的使用与优化
四、开发中常见问题与最佳实践
一、前言
语句规范要建立在结构规范的基础上。
（一）字符集
1.统一字符集，建议UTF8mb4
常用的字符集包括：Latin1、gbk、utf8、utf8mb4。
2.统一排序规则
目前互联网上以UTF8mb4字符集为主，是官方8.0默认字符集。在之前的5.5、5.6、5.7版本是建表使用的Utf8，排序规则是默认“utf8_general_ci”。UTF8mb4独有的默认排序方式是“utf8mb4_0900_ai_ci”,所以有时会出现字符集不兼容的问题，需要注意。
（二）字段
字段规范包括4个方面：统一字段名/类型、字段长度varchar(255)、定义 id int primary key、禁止Null值。
1．统一字段名/类型
统一字段名是为了解决业务歧义问题。比如MySQL内部系统有一张Information下边的tables表，按照不太规范的命名如“tablename”字段可能就会命名成“table”。如果这时有另一个表也命名了包含的“name”字段，在查询的时候，可能会导致意义上的混乱。统一类型是为了解决隐式转换问题。包括表的连接、查询都会存在隐式转换问题。
2.字段长度 varchar(255)
比较常见的是varchar(255)，指不知道业务将来存多少字段的情况下，先设成255，比较的方便，但存在性能隐患。比如索引评估，在一个255列长的字段上建索引，实际索引评估会考虑列长，如果默认255长度，肯定按照最长列考虑，索引评估就起不到作用。
再比如字段，如果字段有2个255或3个255要做复合作业时，虽然真实的值可能每个字段只存了10或20个，会发现索引建不出来就爆了，对线上维护与后续业务开发都有影响。
3.定义 id int primary key
UNSIGNED 容量大一倍，PK 强制。一个是必须定义组件，为了保证一致性，避免复制时性能问题。另一个是无符号定义组件，一般来说在一个表里ID肯定是自增的，如果定义一个int，int是永符号，会导致int可用的值少一半。因为int最大的值在永符号情况下是21亿，如果定义成无符号实际上是42亿。说明数据快速增长时，有一半的数据没有用上，导致ID或某个自增长满的问题。
4．禁止Null值
Null & Null =?。比如在排序场景下，两个行做比较，如果不设有意义的值，默认为空值的情况下，Null & Null会导致一个随机的顺序，实际上就是业务上的乱序。 又比如无组件表情况下，会导致复制数据不一致的问题，所以要禁止空值。
（三）索引
80%的语句性能问题都可以靠索引解决，但索引有几个问题：
第一，是单列索引要充分评估，比如有20个列，每个上都有1个单列的情况，要充分评估。
第二，定期review索引有效性，指定期领域的索引是不是用的到，在MySQL里相对不好定位，当前业务发展环境下会很常见，这时要看覆盖索引是不是已经覆盖之前的旧索引，旧索引实际上是没有用的。维护他还要多一个IO的成本，尽量删掉保留有效的即可。
第三，不要走极端，包括两点复合索引所有列与所有列都建单列索引。比如一张表有七八个列，只对一列有索引，因为索引有回表不回表的区别，所以直接建立一个复合索引，这个方法不可取，等于又另外维护了一张表。所有列都建单列索引，实际上跟是复合索引所有列是一样的。
索引有关内容，请关注【MySQL表和索引优化实战】课程。
二、SQL语句编写规范
（一）规范语法
不兼容语法
Select * from sbtest.sbtest1 group by id;
Select id,count(*) from sbtest.sbtest1 group by id desc;
SQL有一些基础的规范，是相对成熟的产品，但是它支持的一些语法，不是一个标准的语法，比如“Select * from sbtest.sbtest1 group by id;”在Oracle或其他关系型数据库里是非法的语法，根本不支持。“Select id,count(*) from sbtest.sbtest1 group by id desc;” 8.0已经淘汰。在实际环境下不建议使用。
（二）别名
所有返回列要给有意义的命名，与列名原则一致，强制AS关键词。
Select id,count(*) id_count from sbtest.sbtest1 group by id;
标准写法是要关建词一个“as”别名，必免复杂语句混乱。建议从一开始就写标准。
（三）执行顺序
执行顺序如下：
1 .FROM, including JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. WINDOW functions
6. SELECT
7. DISTINCT
8. UNION
9. ORDER BY
10 .LIMIT and OFFSET
从数据流上来看，首先做数据筛选FROM、WHERE、GROUP BY、HAVING、WINDOW，从SELECT以下开始执行运算，包括DISTINCT、UNION、ORDER BY、LIMIT。
Ø 语句性能
数据流的流向
比如: order by limit 场景。
Ø 数据返回逻辑
数据的筛选机制
比如：left join where场景。
从这里可以看出是先做Join再做Where，有一种情况是在做Join作业时，把Where的条件带进去，条件相同时用一个标准的筛选。做Left join和Right join时，是先做一个数据集，通过Where筛选数据，注意中间返回的结果集数据流向，以最优的方式写出来。
（四）如何判断语句是否已最优:explain
TYPE:
ALL：Full Table Scan， 全表扫描。
index: Full Index Scan，索引扫描。
range:范围扫描。
ref: 表示连接匹配条件。
eq_ref: 类似ref，区别就在使用的索引是唯一索引。
const: 常量查询，比如pk等值。
system是const类型的特例；当查询的表只有一行的情况下，使用system
Extra：
Using filesort 排序。
Using index 使用索引可以返回请求列。
Using index condition通过索引初步过滤；回表再过滤其它条件。
Using temporary 临时表。
Using where 单独出现时；一般代表表上出现全表扫描过滤。
Using index & Using where 使用索引返回数据；同时通过索引过滤。
如何判断语句是否已最优，先要看执行计划，执行计划判断语句的逻辑顺序、数据访问。然后是如何避免全面扫描，接下来是性能角度判断，TYPE区的命令从下往上看，从system开始向上性能越来越差。真实执行情况看Extra区的命令。
结合上图来看一下执行计划：
从上往下看，第一个C是table驱动表,后面用了idx_pk,同时用了index condition、where、MRA , MRA是优化器加速。Using index condition;是索引初步过虑，回表再过滤其它条件。Using where同时通过索引过滤，使用索引返回数据，Using index使用索引返回请求列。这样整个执行计划就完成了。
禁止项
✓ select *，返回无用数据，过多IO消耗，以及schema变更问题。
✓ insert语句指定具体字段名称，不要写成insert into t1 values(…)，道理同上。
✓ 禁止不带WHERE，导致全表扫描以及误操作。
✓ where条件里等号左右字段类型必须一致，否则可能会产生隐式转换，无法利用索引。
✓ 索引列不要使用函数或表达式，否则无法利用索引。
如where length(name)=‘Admin’或where
user_id+2=5。
✓ Replace into，会导致主备不一致。
✓ 业务语句中带有DDL操作，特别是truncate。（严格禁止）
建议项
✓ 减小三表以上Join。（因为8.0、8.0.2以后才支持Join，所以表越多证明中间集越多，数据量指数级往上涨，建议减少多表作业。）
✓ 用union all 替代 union。（指的是去重，去重代表数据要排序。）
✓ 使用join 替代子查询。
✓ 不要使用 like ‘%abc%‘，可以使用 like ‘abc%’。（指的是索引的问题。）
✓ Order by /distinct /group by 都可以利用索引有序性。
✓ 减少使用event/存储过程，通过业务逻辑实现。
✓ 减小where in() 条件数据量。
✓ 减少过于复杂的查询. & 拼串写法。（拼串写法尽量少用，会影响调优。）
（五）用数据库的思维考虑SQL
数据库的思维考虑SQL，实际上要处理的是数据集，不再是简单的数据，与开发逻辑不用，不能做以下3个思维方式处理：
第一个不正确的方式：一个语句解决所有问题；
导致过于复杂的查询，执行计划不稳定。
第二个不正确的方式：开发应用的逻辑写语句；
所有的运算、判断应用逻辑都放到SQL实现。
第三个不正确的方式：存储过程使用过重；
难以调试、定位问题。
正确的思维考虑SQL的原则包括:
第一：少即是美，
每一层结果集都要最大限度的减小。
第二：数据集处理，减小单条处理。
第三：减小数据访问（扫描）。
第四：新feature谨慎应用到生产中。因为feature在不断发展，没有经过长时间测试，可能存在一些性能问题，就充分测试后再上上岗生产。
（六）Sql改写
1.Sql-join
select count(a.id) from sbtest1 a left join sbtest2 b on a.id=b.id
如上图所示，请注意Join 键为PK，也就是左表右表应该是1对1的关系，在left作用的下，可以理解成返回的数据全部是左边的数据，也就是“a”表的数据，这里的执行时间大概20秒。
select count(a.id) from sbtest1 a left join sbtest2 b on a.id=b.id;
等价于select count(a.id) from sbtest1 a ;
如下图所示，改写join后，执行时间大概是3秒左右，速度提升了几倍。
Sql改写一般会出现在复杂查询的Join场景中，除去显式join，还包括:
半连接：exists,in
反连接：not exists,not in
此类查询过慢时，请考虑是否可通过SQL改写优化。
2.分页统计
select a.id from sbtest1 a left join sbtest2 b on a.id=b.id limit 200,20;
取总数据量：
select count(*) from
(select a.id from sbtest1 a left join sbtest2 b on a.id=b.id) as a;
分布统计指一种常见的业务逻辑，比如有1万条数据需要分页，把所有的语名逻辑框起来，在外面加“count”，但语句冗余，且执行时间长。改写的方法有：
改写1：
select count(a.id) from sbtest1 a left join sbtest2 b on a.id=b.id;
改写2:
select count(a.id) from sbtest1 a;
如上图所示，有两种改写方式 ，执行计划上无本质区别，第一种改写后执行时间为15秒，第二种改写后执行时间为0.25秒，且语句更加简单。
Ø 此类改写目的：
1)精简语句，简化语句逻辑;
2)进一步寻找优化空间。
三、事务的使用与优化
（一）事务是什么？
并发控制的单位
Ø 4个属性：
Atomicity；
Consistency；
Isolation；
Durability。
Ø 隔离级别：
Read Uncommitted
Read Committed （一般采用）
Repeatable Read （官方默认）
Serializable
当前生产环境下，用的Read Committed隔离级别比较多。
官方默认是Repeatable Read，因为Repeatable隔离级别下边最严重的问题是可能会导致严重阻塞，所以现在一般生产环境采用Read Committed。
只强调一点：
大事务不等于长事务
例如：
1.Insert table batch
2.Begin
insert single data
sleep(3600)
Commit
上面列的这两个场景来看，第一个“Insert table batch” Insert是一个大事物，但不是长事务。第二个insert是简单的一条数据，但sleep 不包括Commit就成了一条很长的数据，如果Begin是原子形的，可能又大又长，再加列或建索引的话可能越来越长。
（二）事务的问题
1.Undo 异常增长
Ibdata导致空间问题，会增加存储成本； Hitory list过长，导致严重的性能问题。
2.binlog 异常增长
由于单个事务不拆分存放，会导致某一个或者某一些binlog非常的大，再去做复制或组成时产生问题。
3.Slave延迟
DDL类，写入等。（DDL是语句级回放，Slave要等到执行结束后再继续。）
4.锁问题
死锁、阻塞。
Ø 优化
1）大事务：
大事务拆分为小事务；
DDL拆分（无锁变更）。
2）长事务
合并为大事务（特别合适应用于写入场景，对写提升很大，而且数据不会特别长）；
事务分解 （不必要的请求摘除）；
应用侧保证一致性
Ø 事务使用基本原则：
在保证业务逻辑的前提下，尽可能缩短。
（三）事务问题定位
1.长事务
Information_schema.innodb_trx。
例如：
SELECT trx.trx_id, trx.trx_started,
trx.trx_mysql_thread_id FROM
INFORMATION_SCHEMA.INNODB_TRX trx WHERE
trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1
SECOND。
2.锁问题
8.0以前：
information_schema.innodb_lock_waits、innodb_locks。
8.0
performance_schema. data_lock_waits、data_locks。
四、开发中常见问题与最佳实践
（一）分页问题
传统写法
select * from sbtest1 order by id limit M,N。
问题点：
扫描大量无效数据后，返回请求数据。
解决分页问题的方式包括：（在实际操作中根据业务需求做取舍）
➢ select * from sbtest1 where id > #max_id# order by id limit n;
• 适用顺序翻页的场景，每次记录上一页#max_id#带入下一次查询中。（有一个问题是统计不到总的记录条数）。
➢ select * from sbtest1 as a inner join (select id from sbtest1 order by id limit m, n) as b
on a.id = b.id order by a.id;
• 适用只按照id进行分页,无where条件。（可以统计总记录的条数）。
➢ select * from sbtest1 as a
inner join (select id from sbtest1where col=xxxx order by id limit m, n) as b
on a.id = b.id order by a.id;
• 适用于带where条件，同时按照id顺序分页。
• 此时，需要在where条件上创建二级索引。（是第二条的变种，实际上还是传统的写法，只是因为通过索引缩小扫描的量）。
（二）大表数据清理
数据清理场景：
➢ 历史数据清理
• 通常按照条件（比如：时间范围）delete历史数据。
• 问题。
① 单次delete行数过多，容易导致锁堵塞、主从复制延迟、影响线上业务。
② 易失败，死锁、超时等。
• 建议方案
① 伪代码
Select min(id),max(id) from t where gmt_create<$date
For I in “max(id)-min(id)/1000”
Delete from t where id>=min(id) and id<min(id)+1000 and gmt_create<$date
…...
② 定期optimize table回收碎片。
➢ 全表数据清理
• Truncate整张表的数据。
• 问题：
• 大表（如：>100G），truncate期间会造成io持续抖动。
• 建议方案：
• 硬连接方式后truncate，异步trim文件。
（三）隐式转换问题
Create table testtb(id varchar(10) primary key);
Select * from testtb where id=1;
隐式转换发生在比较值类型不一值的场景下，除去一些规定情况最终都是转换为浮点数进行比较。
此类问题在编写时很难发现，上线导致严重性能问题。
（四）循环
1.外部循环
应用侧实现
主要问题来自每次请求的rt。
例如：
for i=0;i++;i<500
insert（db 交互)
next
rt=single rt* total count
建议batch一次写入（一次写入顺序，不在外面循环）。
2.内部循环
一般常用在存储过程，事务无法保证。
While do
insert；
Commit;
end while
频繁commit。（存在频繁commit问题，）
或: Begin tran
While do
insert；
end while
Commit
数据一致性以及长事务。（存在数据一致性问题及长事务，遇到错误以后，其他数据还是会提交）。
（五）存储过程中的事务处理
create procedure insertTest(IN num int)
BEGIN
DECLARE errno int;
declare i int;
declare continue HANDLER for sqlexception set errno=1;
start transaction;
set i=0;
while i<num do
INSERT testfor VALUES(i);
set i=i+1;
end while;
if errno=1 then
rollback;
else
commit;
end if;
end;
存储过程中的事务处理，如上面这个例子，BEGIN以后，下面最终是commit。如果最终是重复键值rollback，前面已经插了10条数据，但是这10条数据是不回滚的，所以这个事故要直接在这里声明，捕捉错误以后，然后回滚整个事物，才能把这个循环回滚掉。
（六）常见问题
1. Where 后面的列顺序是不是要符合最左原则？
Where a=1 and b=2 等价于 Where b=2 and a=1
最左原则指的是索引顺序，不是谓词顺序，以上两个条件都匹配( a,b) 复合索引。
2. Join 的顺序是不是指定了左边为驱动表
inner join场景下，在执行计划中按预估自动选中驱动表，left join ,right join 时左右写的顺序才有显式意义。
3.业务上有随机返回的需求，可不可以用order by rand()
一般不建议，如果结果集非常小，勉强可用，但结果集大时由于随机数排序，会产生 sort操作，甚至溢出到磁盘，有很大性能损耗。
此类需求可以考虑伪随机算法，具体不再此提供。
4.Delete数据之后，为什么磁盘空间占用反而大了？
Delete数据并不能清理数据文件空间，反而会导致undo,binlog文件的增长，使用optimize收缩。
5.Binlog是否一定要row格式？
在主从场景下，Binlog使用row格式是为了保证主从数据一致性。
单机场景下，Binlog做为增长数据备份使用，同时也包括一些语句级数据恢复的功能。
6.死锁、阻塞的区别：
通常说的阻塞，主要是由于锁获取不到，产生的请求被阻塞了，一般需要手动解锁(kill或等待)。
死锁不等于阻塞，虽然死锁中阻塞是必现的，但是会自动回滚事物解锁，不用手动处理，但需要业务判断语句逻辑。
以上两种情况都是由于业务侧逻辑出现，并非内核原因。
7.做DDL时是否会锁表：
实际上所有的DDL都需要锁表，只是操作顺序和操作获取时间的问题。如下图所示，允许并发DDL是no，就证明对业务有一些阻塞。
详情参考官方文档：
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
