SQL优化最全总结 - MySQL（2022最新版）

怪咖@

已于 2022-09-04 23:10:27 修改

阅读量2.2k

点赞数 7

分类专栏： # Mysql 文章标签： mysql sql 数据库 sql优化

于 2022-08-04 15:22:32 首次发布

本文链接：https://blog.csdn.net/weixin_43888891/article/details/126111295

版权

Mysql 专栏收录该内容

21 篇文章 5 订阅

订阅专栏

有朋友疑问到，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，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

怪咖@

关注

7
点赞
踩
39

收藏

觉得还不错? 一键收藏
打赏
2
评论
SQL优化最全总结 - MySQL（2022最新版）

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

扫一扫