【MySQL学习笔记】SQL优化（插入数据、主键优化、order by优化、group by优化、limit优化、count优化、update优化）

SQL优化包含7部分内容，分别为：insert优化、主键优化、order by优化、group by优化、limit优化、count优化、update优化。下面一一介绍。

1、insert优化

insert 优化就是值优化插入数据的 SQL 语句。在需要插入大量数据时，如果每插入一条数据都编写一条 insert 语句，那么每次插入数据都要进行网络请求，并且MySQL都需要开启和提交事务，这写操作会降低插入数据的效率，因此需要考虑对这部分 SQL 进行优化。

-- 每一条 SQL 语句只插入一条数据，SQL 语句执行时需要网络请求，同时 MySQL 服务器需创建和提交事务。当数据量大时，需要大量的网络请求，以及管理大量的事务，会造成大量的资源消耗，降低插入数据的效率。
insert into user values(1, 'user_1');
insert into user values(2, 'user_2');
insert into user values(3, 'user_3');
...
insert into user values(n, 'user_n');

insert 优化的方案有三种：

1. 批量插入数据：
   一条 SQL 语句插入多条数据，这样一次网络请求就可以完成多条数据的插入。但是也不能一次插入过多的数据，数据在500-1000条比较合理。

-- 一条 SQL 批量插入多条数据
insert into user values(1, 'user_1'),(2, 'user_2'),(3, 'user_3'),(n, 'user_n');

2. 手动提交事务
   MySQL的事务默认是自动提交的，因此在每次执行完 SQL 语句之后，都会自动将事务提交。如果大量的插入数据的 Insert 语句执行时，每执行一条都要提交一次事务，会影响性能。因此可以进行手动提交事务。在执行完多条 Insert 语句后，手动提交事务。

start transaction; -- 开启事务
insert into user values(1, 'user_1'),(2, 'user_2'),(3, 'user_3'); -- 插入批量数据
insert into user values(4, 'user_4'),(5, 'user_5'),(6, 'user_6');
insert into user values(7, 'user_7'),(8, 'user_8'),(9, 'user_9');
commit; -- 手动提交事务

3. 主键顺序插入
   按照主键值的顺序进行插入。

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

大批量插入数据（load指令）
如果一次性需要插入大批量数据，使用 Insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入。
load 可以将本地磁盘文件中的数据直接加载到数据库表结构中。本地磁盘文件中的数据需要符合一定的规则，能够与数据库表的字段一一对应。

若要使用 load 指令，需要进行如下操作：

# 客户端连接服务端时，加上参数 --local-infile，加载本地文件
mysql --local-infile -u root -p

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

-- 执行 load 指令将准备好的数据，加载到表结构中
-- '/root/sql1.log'：替换成需要加载的文件路径
-- table_name：加载到哪张表
-- ','：文件中每个字段数据通过什么符号分隔
-- '\n'：文件中每行数据通过什么符号分隔
load data local infile '/root/sql1.log' into table table_name fields terminated by ',' lines terminated by '\n';

2、主键优化

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table， IOT）。
使用 InnoDB 存储引擎，B+Tree索引的每个节点是存储在一个页中，每个页的大小固定为16k，每个页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。
在往数据库插入数据时可能会出现两种现象：页分裂和页合并。
主键乱序插入时可能会出现页分裂现象：
页分裂
首先根据 ID 顺序插入了部分数据。

此时需要插入 ID 为50的数据。这个数据应该插在47之后，55之前。但是这时第一个页和第二个页都已经满了。这个时候会新申请一个页。

将第一个页后50%的数据放入新的页，然后将 ID 为50的数据插入第三个页，让第一个页指向第三个页，第三个页指向第二个页。这就完成了页分裂。

页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE_THRESHOLD （默认为页的50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
这里在第二页中标记了超过50%的记录被删除，而第三页的数据少于页的50%，因此会进行页合并。

此时会将第三页的记录合并到第二页。第二页被标记的记录会被删除。
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则：

1. 满足业务需求的情况下，尽量降低主键长度。这是因为在建立索引时，索然聚集索引只有一个，但是二级索引可能会有很多。而二级索引的子节点中存储了主键 ID。如果主键很长，那么需要大量的磁盘空间来存储，并且会影响查询效率，浪费IO资源。
2. 插入数据时，尽量选择顺序插入，如选择使用 AUTO_INCREMENT 自增主键。这样能够避免页分裂现象，并且可以充分利用数据页的空间。
3. 尽量不要使用 UUID 做主键或者其他自然主键，如身份证号。这是为了避免主键乱序插入，导致页分裂现象。
4. 业务操作时，尽量避免对主键的修改。

3、order by 优化

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

通过下面的例子分析 order by 优化
首先在未创建索引的情况下根据 age 字段排序：

-- 查询 id，age，phone，并通过 age 字段升序排序。由于没有创建索引，因此执行计划中 Extra 信息为 Using filesort。
explain select id, age, phone from user order by age;

然后给 age，phone 字段创建联合索引：

-- 给 user 表的 age，phone 字段创建一个联合索引 idx_user_age_phone。
create index idx_user_age_phone on user(age, phone);

分析下面的 SQL语句

-- 根据 age 字段升序排序。此时由于给 age 字段创建了联合索引，并且满足最左前缀法则，索引生效，
-- 执行计划中 Extra 信息为 Using index。
explain select id, age, phone from user order by age;

-- 根据 age，phone 字段升序排序。由于给 age，phone 字段创建了联合索引，并且满足最左前缀法则，索引生效，
-- 执行计划中 Extra 信息为 Using index。
explain select id, age, phone from user order by age, phone;

-- 根据 age，phone 字段降序排序。由于给 age，phone 字段创建了联合索引，并且满足最左前缀法则，索引生效，
-- 创建索引默认是升序排序，但由于返回结果需要降序排序，需要反向扫描索引，
-- 因此执行计划中 Extra 信息为 Backward index scan; Using index。
explain select id, age, phone from user order by age desc, phone desc;

-- 首先根据 phone 字段升序排序，再根据 age 字段升序排序。创建索引时，第一个字段为 age，而排序条件的第一个字段为 phone， 违背了最左前缀法则。
-- 执行计划中 Extra 信息为 Using index; Using filesort
explain select id, age, phone from user order by phone, age;

-- 首先根据 age 升序排序，在根据 phone 降序排序。创建索引时，索引默认是根据 age 升序排序，再根据 phone 升序排序的。
-- 在查询时，若根据 age 升序排序，再根据 phone 降序排序，则需要在排序缓冲区 sort buffer 中完成额外的排序操作
-- 执行计划中 Extra 信息为 Using index; Using filesort
explain select id, age, phone from user order by age asc, phone desc;
-- 为了解决这一问题，可以在创建索引时指定索引排序方式。
-- 创建 age，phone 的联合索引 idx_user_age_phone_ad，并指定 age 是按升序排序，phone 是按降序排序。
create index idx_user_age_phone_ad on user(age asc, phone desc);

经过上面的分析，可以总结如下：

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
2. 尽量使用覆盖索引。
3. 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
4. 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）。
   通过 show variables like ‘sort_buffer_size’; 指令查看。

4、group by 优化

首先通过案例分析 group by 优化。
在没有创建索引的情况下进行 group by 分组：

-- 根据 job 字段分组，返回 job，count(*)
-- 由于没有建立索引，执行计划中 Extra 信息为 Using Temporary。使用了临时表，效率较低
explain select job, count(*) from user group by job;

给 user 表创建字段 job，age 的联合索引 idx_user_job_age

-- 创建 job，age 的联合索引
create index idx_user_job_age on user(job, age);

分析下面的 SQL 语句：

-- 根据 job 字段分组，查询返回 job，count(*)。由于给 job 和 age 字段创建了联合索引，并且使用索引列的最左列，满足最左前缀法则，索引生效。
-- 执行计划中 Extra 信息为 Using index，效率较高
explain select job, count(*) from user group by job;

-- 根据 job，age 字段分组，查询返回 job，age，count(*)。满足最左前缀法则。
-- 执行计划中 Extra 信息为 Using index，效率较高
explain select job, age, count(*) from user group by job, age;

-- 根据 age 字段分组。不满足最左前缀法则
-- 执行计划中 Extra 信息为 Using index; Using Temporary。出现临时表，查询效率较低
explain select age, count(*) from user group by age;

-- 首先根据 job 字段过滤，然后根据 age 字段分组。满足最左前缀法则，因为在 where 条件中使用到了索引列的最左列
-- 执行计划中 Extra 信息为 Using index。查询效率较高
explain select age, count(*) from user where job = '程序员' group by age;

通过上述分析，总结如下：

1. 在分组操作时，可以通过索引来提高效率。
2. 分组操作时，索引的使用也需要满足最左前缀法则。

5、limit 优化

在使用 limit 排序时，一个常见有非常头疼的问题就是 limit 2000000, 10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

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

例如下面的例子：

-- 查询从第9000000开始之后的10条记录，MySQL 会排序前9000010条记录，仅仅返回9000000-9000010的记录，其他记录丢弃，效率极低。
select * from user limit 9000000, 10;

-- 可以首先查询第9000000-9000010记录的 id，由于 id 字段有聚集索引，可以覆盖索引，效率得到提升
select id from user order by id limit 9000000, 10;

-- 上面的 select 语句返回结果可以作为子表，然后根据查询到的 id 进行连表查询，得到9000000-9000010的记录。
select s.* from user s, (select id from user order by id limit 9000000, 10) a where s.id = a.id;

6、count 优化

不同的存储引擎执行 count 略有不同。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高。但是当 Select 语句中有 where 或其他条件时，还是需要一条一条记录计数。

InnoDB引擎执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

目前没有很好的优化方法。
优化思路：自己计数。也就是自己维护计数，如使用 Redis 这种 key-value 形式的数据库，当插入数据时，计数加一，当删除数据时，计数减一。

count() 的几种用法：

1. count(主键)：
   InnoDB 引擎会遍历整张表，把每一行的主键 ID 值取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为 null ）。
2. count(字段)：
   没有 not null 约束：InnoDB 引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，计数累加。
   有 not null 约束：InnoDB 引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
3. count(常量)：
   InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个常量进去，直接按行进行累加。
4. count(*)：
   InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序：count(字段) < count(主键id) < count(常量) ≈ count(*)

所以尽量使用 count(*)

7、update 优化

update 语句注意事项：进行更新操作时，应根据索引字段进行更新，否则行锁会升级为表锁，影响并发性能。InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

分析下面两种情况：

1. 情况1：假设表 user 除了主键 id 聚集索引外没有构建其他索引，更新数据时以 id 为过滤条件

-- session 1
-- 在第一个会话中手动开启一个事务
begin;
-- 更新 id 为 1 的用户姓名为'张三'，暂时不提交事务，session 1 获取 id 为 1 的数据的行级锁
update user set name = '张三' where id = 1;

-- session 2
-- 在第二个会话中手动开启一个事务
begin;
-- 更新 id 为 2 的用户姓名为'王五'，能成功执行 update 语句，暂时不提交事务，session 2 获取 id 为 2 的数据的行级锁
update user set name = '王五' where id = 2；

-- session 1
-- 提交会话一中的事务，
commit;

-- session 2
-- 提交会话二中的事务，
commit;

再查询表中数据发现 id 为 1 和 2 的数据都成功更新。
2. 情况2：假设表 user 除了主键 id 聚集索引外没有构建其他索引，更新数据时以 name 字段为过滤条件

-- session 1
-- 在第一个会话中手动开启一个事务
begin;
-- 更新 name 为 Tom 的用户姓名为'张三'，暂时不提交事务。但由于 name 字段没有建立索引，因此 session 1 获得该表的表锁
update user set name = '张三' where name = 'Tom';

-- session 2
-- 在第二个会话中手动开启一个事务
begin;
-- 更新 name 为 Jerry 的用户姓名为'王五'，发现 update 语句没有立刻执行，而是在等待
-- 这是因为 name 字段没有建立索引，此时以 name 字段作为过滤条件，第一个会话获取的是表锁，在锁释放之前，其他会话不能对该表进行操作
update user set name = '王五' where name = 'Jerry';

-- session 1
-- 提交会话一中的事务，此时发现会话二的 update 语句执行完成
-- 这是因为会话一提交事务后，释放了表锁，会话二成功获取表锁，拥有对表的操作权
commit;

-- session 2
-- 提交会话二中的事务
commit;

再次查询表中数据，发现姓名为 ‘Tom’ 的用户成功更新为’张三’，姓名为 ‘Jerry’ 的用户成功更新为’王五’。