[大数据] SQL优化最全总结 - MySQL（2022最新版）

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> SQL优化最全总结 - MySQL（2022最新版） -> 正文阅读

[大数据]SQL优化最全总结 - MySQL（2022最新版）

有朋友疑问到，SQL优化真的有这么重要么？SQL优化在提升系统性能中是：（成本最低 && 优化效果最明显）的途径。如果你的团队在SQL优化这方面搞得很优秀，对你们整个大型系统可用性方面无疑是一个质的跨越，真的能让你们老板省下不止几沓子钱。

SELECT语句 - 执行顺序

在这里插入图片描述

执行 from 知道先从<left_table>这个表开始的
执行 on 过滤根据 <join_condition> 这里的条件过滤掉不符合内容的数据
执行 join 添加外部行
- inner join 找两张表共同的部分
- left join 以左表为准，找出左表所有的信息，包括右表没有的
- right join 以右表为准，找出左表所有的信息，包括左表没有的
- 注意：mysql不支持全外连接 full JOIN 可以用union
执行 where 条件 where后加需要满足的条件，然后就会得到满足条件的数据
执行 group by 分组当我们得到满足where条件后的数据时候，group by 可以对其进行分组操作
执行 having 过滤 having 和 group by 通常配合使用，可以对满足where条件内容进行过滤
执行 select 打印操作当以上内容都满足之后，才会执行得到select列表
执行 distinct 去重得到select列表之后，如果指定有 distinct ，执行select后会执行 distinct去重操作
执行 order by 排序以上得到select列表也经过去重基本上就已经得到想要的所有内容了然后就会执行 order by 排序asc desc
执行 limit 限制打印行数，我们可以用limit 来打印出我们想要显示多少行。

SQL优化策略

所有的sql优化基本上都是围绕以下3点来进行的：

最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询；

这一篇记录了索引的所有相关知识，以及怎么查看索引是否生效等等：https://blog.csdn.net/weixin_43888891/article/details/126073266

一定要学会读sql的执行计划，要不然优化工作根本无法进行，所有的sql优化工作基本上全是围绕索引来进行优化的！！！

一、插入数据优化

1. insert插入多条数据优化

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

insert into tb_test values(1,'tom'); 
insert into tb_test values(2,'cat'); 
insert into tb_test values(3,'jerry');

（1）优化方案一：批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

这样一来只需要解析一次就能进行数据的插入操作，减少SQL语句解析的操作，MySQL没有类似Oracle的share pool；
SQL语句较短，可以减少网络传输的IO。

（2）优化方案二：手动控制事务，频繁的开启关闭事务也是有一定的耗时的

start transaction; 
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); 
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); 
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

（3）优化方案三：主键顺序插入，性能要高于乱序插入（尽量使用雪花算法id，或者自增id，尽可能的避免使用uuid）。

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

2. 大批量导入数据优化

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

mysql不止可以通过正规的insert语句可以添加到数据库，load他就是通过数据的规律性，然后就可以将数据导入到数据库当中，如下所示：

在这里插入图片描述

mysql千万数据脚本（感兴趣的可以自己下载，然后进行load导入）：

链接：https://pan.baidu.com/s/19r9l7LvtBPNsgpLi9knhpA
提取码：sm4y

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile 
mysql –-local-infile -u root -p 

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
set global local_infile = 1; 

-- 执行load指令将准备好的数据，加载到表结构中 
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

主键顺序插入性能高于乱序插入

示例演示：

（1）创建表结构

CREATE TABLE `tb_user` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`username` VARCHAR ( 50 ) NOT NULL,
	`password` VARCHAR ( 50 ) NOT NULL,
	`name` VARCHAR ( 20 ) NOT NULL,
	`birthday` DATE DEFAULT NULL,
	`sex` CHAR ( 1 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ),
UNIQUE KEY `unique_user_username` ( `username` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

（2）设置参数

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
set global local_infile = 1;

（3）load加载数据

 load data local infile 'C:\\Users\\gxs\\Desktop\\load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

在这里插入图片描述

我们看到，插入100w的记录，17s就完成了，性能很好。

二、主键优化

在上面我们提到，主键顺序插入的性能是要高于乱序插入的。这一小节，就来介绍一下具体的原因，然后再分析一下主键又该如何设计。

（1）数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表
(index organized table IOT)。
在这里插入图片描述

行数据，都是存储在聚集索引（聚簇索引）的叶子节点上的。InnoDB的逻辑结构图如下：

在这里插入图片描述

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

存储引擎讲解：https://blog.csdn.net/weixin_43888891/article/details/125958409

（2）页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行
溢出)，根据主键排列。

A. 主键顺序插入效果

1.从磁盘中申请页，主键顺序插入
在这里插入图片描述

2.第一个页没有满，继续往第一页插入
在这里插入图片描述

3.当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
在这里插入图片描述
4.当第二页写满了，再往第三页写入

B. 主键乱序插入效果

1.假如1#,2#页都已经写满了，存放了如图所示的数据
在这里插入图片描述

2.此时再插入id为50的记录，我们来看看会发生什么现象，想一下会再次开启一个页，写入新的页中吗？
在这里插入图片描述
不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。

但是47所在的1#页，已经写满了，存储不了50对应的数据了。那么此时会开辟一个新的页 3#。

但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。
在这里插入图片描述
移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。所以，此时，需要重新设置链表指针。

上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

页分裂往往就是不按照顺序插入导致的，同时修改索引id也会导致这种情况的出现！

（3）页合并

目前表中已有数据的索引结构(叶子节点)如下：
在这里插入图片描述
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间
变得允许被其他记录声明使用。

当我们继续删除2#的数据记录

在这里插入图片描述
当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

在这里插入图片描述
删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页

在这里插入图片描述
这个里面所发生的合并页的这个现象，就称之为 “页合并”。

知识小贴士：MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

（4）主键设计原则

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。

（5）UUID、自增id、雪花id选哪个作为主键比较好？

插入数据的性能比较：UUID < 雪花有序算法id < 自增id （自增id性能最高）

使用自增id的缺点：

别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况
对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争
自增id涉及到数据迁移的话是相当麻烦的！
而且一旦涉及到分库分表自增id也是相当麻烦的！

uuid的缺点：

因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上
由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片
而且uuid本身就都是字母，而且还特别长，性能自然不是特别好了。

雪花算法id： snowflake是Twitter开源的分布式ID生成算法，结果是64bit的Long类型的ID，有着全局唯一和有序递增的特点。

如果小的系统，使用自增id完全可以，如果系统较大，或者说以后可能会成为大的系统，那么就有可能会涉及到数据迁移、分表等等操作，我强烈推荐使用雪花算法id。

三、order by优化

MySQL的排序，有两种方式：

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

接下来，我们来做一个测试：

CREATE TABLE tb_user (
	id INT PRIMARY KEY auto_increment COMMENT '主键',
	NAME VARCHAR ( 50 ) NOT NULL COMMENT '用户名',
	phone VARCHAR ( 11 ) NOT NULL COMMENT '手机号',
	email VARCHAR ( 100 ) COMMENT '邮箱',
	profession VARCHAR ( 11 ) COMMENT '专业',
	age TINYINT UNSIGNED COMMENT '年龄',
	gender CHAR ( 1 ) COMMENT '性别 , 1: 男, 2: 女',
	STATUS CHAR ( 1 ) COMMENT '状态',
	createtime datetime COMMENT '创建时间' 
) COMMENT '系统用户表';

INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动 化', 27, '1', '2', '2001-08-16 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工 程', 27, '1', '0', '2001-06-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00' );
INSERT INTO tb_user ( NAME, phone, email, profession, age, gender, STATUS, createtime )
VALUES ( '姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00' );

执行排序SQL：explain select id,age,phone from tb_user order by age;

由于 age, phone 都没有索引，所以此时再排序时，出现Using filesort，排序性能较低。

创建了一个组合索引，然后再进行测试：
CREATE INDEX idx_user_age_phone ON tb_user(age,phone);
explain select id,age,phone from tb_user order by age, phone ;

索引创建的是age、phone然后排序的时候按照phone、age，这时候就出现了Using filesort

索引失效：添加了age和phone的索引，但是查询的列，不止有这两个，导致直接索引失效！

如果就是要查三个列怎么办？要么选择把profession也添加上索引，要么可以采用如下查询方式。其实这样查询也可以保持索引生效，只不过会出现回表查询。

创建索引后，根据age, phone进行降序排序
explain select id,age,phone from tb_user order by age desc , phone desc;

也出现 Using index，但是此时Extra中出现了 Backward index scan（mysql版本低的话是没有这个的，仍然显示的 Using index），这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，我们也可以创建降序索引。

根据age, phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc ;
因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。

为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。

创建联合索引(age 升序排序，phone 倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

然后再次执行如下SQL： explain select id,age,phone from tb_user order by age asc , phone desc ;

升序/降序联合索引结构图示:

age asc,phone desc：代表的是先按年龄升序，假如年龄相等的到时候，安装phone倒序进行二级排序
在这里插入图片描述

由上述的测试,我们得出order by优化原则:

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

四、group by优化

分组操作，我们主要来看看索引对于分组操作的影响。

首先我们先将 tb_user 表的索引全部删除掉，只留了主键索引！

drop index 索引名称 on 表名;

在这里插入图片描述

接下来，在没有索引的情况下，执行如下SQL，查询执行计划：
explain select profession , count(*) from tb_user group by profession ;

很明显出现了Using temporary（临时表），效率要远远低于Using index。

然后，我们在针对于 profession ， age， status 创建一个联合索引。
create index idx_user_pro_age_sta on tb_user(profession , age , status);
紧接着，再执行前面相同的SQL查看执行计划。
explain select profession , count(*) from tb_user group by profession ;

再执行如下的分组查询SQL，查看执行计划：

会发现一个问题：group by也是遵循了索引最左前缀法则，直接使用age进行分组虽然走了索引，但是出现了Using temporary，然后建立索引的时候索引列的顺序是(profession , age , status)，而分组的时候使用的是 age，profession，这样也会出现Using temporary。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。
满足最左前缀的基础上，对顺序也会有要求，假如分组的列顺序，和创建索引列的顺序不一致也会出现Using temporary。

五、limit 优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：

在这里插入图片描述

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。

因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;`

上面示例当中直接分页，和子查询的方式其实都会走主键索引，两种相比较差距并不是特别大，在1000万数据，然后访问limit9200000,19 ，子查询这种方式速度大概能快个2s左右。

六、count 优化

（1）概述

select count(*) from tb_user ;

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。
InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行，但是如果是带条件的count又比较麻烦了)。

（2）count用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

在这里插入图片描述
按照效率排序：count(字段) < count(主键 id) < count(1) ≈ count(*)
所以尽量使用 count(*)，在阿里巴巴规范当中也有这一点！

七、update 优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。导致该update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁。

八、总结

批量insert的时候，尽量不要一条一条insert执行，而是一下子执行完。
导入数据量较大的时候，可以考虑使用load导入数据。
主键id尽量选择有序的，可以避免页分裂。
排序和group by数据量大的情况下一定要用索引，一定要遵循索引的最左前缀法则，
尽最大的可能去避免回表查询，使用覆盖索引。
count的时候尽量使用count(*),效率高，注意:count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。

阿里巴巴Mysql规范

一、Mysql 建表规约

以下部分都来源于阿里巴巴规范当中：

表名、字段名必须使用小写字母或数字。
主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。
说明：pk_即 primary key；uk_即 unique key；idx_即 index 的简称
小数类型为 decimal，禁止使用 float 和 double
说明：float 和 double 都存在精度损失的问题。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引率。
如果存储的字符串长度几乎相等，使用 char 定长字符串类型。（char是定长字符，比vachar效率要高，所谓定长就是声明char（10）初始阶段就是占用10个字节，根本不管他到底存储的数据够不够这些，他都占用这些空间）
表必备三字段：id，create_time，update_time。
说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time，update_time 的类型均为
datetime 类型，如果要记录时区信息，那么类型设置为 timestamp。
在数据库中不能使用物理删除操作，要使用逻辑删除。
库名与应用名称尽量一致。表的命名最好是遵循“业务名称_表的作用”。
修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

二、Mysql 索引规约

以下部分都来源于阿里巴巴规范当中：

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层
做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：这个就是说的索引当中的前缀索引（在我的索引文档当中有提到）。
页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 filesort 的情况，影响查询性能。
利用覆盖索引来进行查询操作，避免回表。
说明：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain
的结果，extra 列会出现：using index。
利用延迟关联或者子查询优化超多分页场景。
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大
的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT t1.* FROM 表 1 as t1 , (select id from 表 1 where 条件 LIMIT 100000 , 20) as t2 where t1.id = t2.id
SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 const 最好（指的是执行计划当中的type）。
- consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
- ref 指的是使用普通的索引（normal index）。
- range 对索引进行范围检索。
- type = index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。
建组合索引的时候，区分度最高的在最左边。
正例：如果 where a = ? and b = ?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
防止因字段类型不同造成的隐式转换，导致索引失效。
最典型的案例就是字段是字符串类型，但是查询的时候不带引号，导致隐式转换。
创建索引时避免有如下极端误解：
- 索引宁滥勿缺。认为一个查询就需要建一个索引。（如果要是这样，有可能会导致索引列重复等问题）
- 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。（该创建索引的地方，不要节省）
- 抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。（尽量用唯一索引）

三、SQL 语句需要注意的

不要使用 count(列名) 或 count(常量) 来替代 count(*)
count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1 , col2) 如
果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
当某一列的值全是 NULL 时，count(col) 的返回结果为 0；但 sum(col) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。
正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column) , 0) FROM table;
使用 ISNULL() 来判断是否为 NULL 值。
说明：NULL 与任何值的直接比较都为 NULL。
代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的
student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、
高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
数据订正（特别是删除或修改记录操作）时，要先 select，避免出现误删除的情况，确认无误才能执行更新语句。
对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
SQL 语句中表的别名前加 as，并且以 t1、t2、t3、...的顺序依次命名。
in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在1000 个之内。
因国际化需要，所有的字符存储与表示，均采用 utf8mb4 字符集，字符计数方法需要注意。
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。

四、ORM 映射

在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
不要用 resultClass 当返回参数，即使所有类属性名与数据库字段一一对应，也需要定义<resultMap>；反过来，每一个表也必然有一个<resultMap>与之对应。
说明：配置映射关系，使字段与 DO 类解耦，方便维护。
sql.xml 配置参数使用：#{}，#param# 不要使用 ${} 此种方式容易出现 SQL 注入。
iBATIS 自带的 queryForList(String statementName，int start，int size) 不推荐使用。
说明：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取 start，size
的子集合，线上因为这个原因曾经出现过 OOM。
不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
反例：某同学为避免写一个xxx，直接使用 Hashtable 来接收数据库返回结果，结果出现
日常是把 bigint 转成 Long 值，而线上由于数据库版本不一样，解析成 BigInteger，导致线上问题。
更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间。
不要写一个大而全的数据更新接口。传入为 POJO 类，不管是不是自己的目标更新字段，都进行
update table set c1 = value1 , c2 = value2 , c3 = value3；这是不对的。执行 SQL 时，不要更新无改
动的字段，一是易出错；二是效率低；三是增加 binlog 存储。
@Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。