8.MySQL之SQL优化（insert 优化，主键优化，页分裂，页合并，order by优化，group by优化，limit 优化，count 优化，update 优化，千万级数据生成方法）

8.1. insert 优化

1. 当数据有多条时，建议：

   insert into 表 values(值,值,...),(值,值,...),...;
   

2. 当数据较多时，建议：

   start transaction;
   insert into 表 values(值,值,...),(值,值,...),...;
   insert into 表 values(值,值,...),(值,值,...),...;
   insert into 表 values(值,值,...),(值,值,...),...;
   ...
   commit;
   

   事务默认是自动提交的，批量插入再一次性手动提交效率更高，关于事务，具体可以看 4.MySQL之事务

3. 当数据比较庞大时，建议：

   # 客户端连接服务器端，加上参数 --local-infile
   mysql --local-infile -u root -p
   
   # 设置全局参数 local_infile 为1，开启从本地加载文件导入数据的开关
   set global local_infile = 1;
   
   # 执行load指令将准备好的数据，加载到表结构中
   load data local infile '/文件路径/文件名' into table `表名` fields terminated by ',' lines terminated by '\n';
   

   示例：

   连接mysql 加上 --local-infile 参数
   
   查看local_infile 开关，默认关闭，打开开关
   
   对 tb_sku 表进行大批量数据插入
   
   查看要插入的数据集，可以看到200w行，共5个文件，所以共有1000W条数据，每个文件的数据格式为各个字段数据用逗号隔开
   
   执行load指令将准备好的数据加载到表结构中
   
   报错了：解决办法（若未报错，可直接跳过）

   通过报错的提示可以发现是由于secure-file-priv变量的设置所引起的，查看该变量的设置，默认的路径是/var/lib/mysql-files/
   
   说明：
   secure_file_prive=null 限制mysqld 不允许导入导出
   secure_file_priv=/var/lib/mysql-files/ 限制mysqld的导入导出只能发生在/var/lib/mysql-files/目录下
   secure_file_priv=’ ’ 不对mysqld的导入导出做限制
   所以有两种方法：
   第一种：将文件移入/var/lib/mysql-files/ 目录下
   第二种：修改secure_file_prive的值为 null
   vim /etc/my.cnf 进入配置文件进行配置
   secure_file_priv=''
systemctl restart mysqld.service 重启mysql
   进入查看
   

   再次执行load指令：
   
   额，数据集不对，但是理论是没错的，等我数据集改对，再更新结果

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

8.2. 主键优化

• 数据组织方式：
  在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为 索引组织表
  
  页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据很大，会行溢出），根据主键排列
• 页分裂
  这是一种现象，指新增一行数据要插入时，可能不会正常新开一页插入，如图：
  
  可能会出现主键乱序插入，即 key-50 要插入到 key-47 后面，这时 1# page 会分出50%的内容放在新开的 3# page，这时 key-50 可以插入到 key-47 后面了，如图：
  
  而之面的 key-9 与 key-23 的顺序还是要维持的，所以会重新排列页顺序，如图：
  
• 页合并
  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。
  当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间的使用，如图：
  
  

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

主键设计原则

1. 满足业务需求的情况下，尽量降低主键的长度。
   (聚集索引只有一个，但是二级索引有多个，而二级索引叶子节点存放的值就是主键，若主键很长，则会浪费大量的空间)
2. 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT自增主键。
   （若为顺序插入，则页数据是一页一页的顺序增加，但是若为乱序插入，则可能会出现 页分裂）
3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
   （这种设置的主键为乱序，所以在插入时可能会出现 页分裂，并且主键长度相对较长，在检索时会耗费大量的磁盘空间）
4. 业务操作时，避免对主键的修改。
   （主键作为唯一的标识，若修改主键，还需要修改索引结构，代价大）

8.3. order by优化

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

示例：
对 book 表的字段 Price 和 Category_Id 进行排序

没有创建索引时，根据 Price 和 Category_Id 进行排序

没有通过索引直接返回排序结果

创建索引

创建索引后，再根据 Price 和 Category_Id 进行升序排序

可以看到是 通过有序索引顺序扫描直接返回有序数据

根据 Price 和 Category_Id 再进行降序排序试试

也是直接可以返回的，但一些情况可能会出现 Backward index scan; 表示该表进行了反向扫描

若将 Price 进行升序，Category_Id 进行降序又会怎么样呢

可以看到既直接返回了也重新排序了，这是因为 在创建索引时若没有指定字段排列顺序，那么默认都是升序排列，所以会直接返回 Price,但是若 Price 相同，则要对 Category_Id 进行重新排序再返回

改进方法，可以根据order by的排序顺序来创建索引，创建排序索引：

create index idx_book_price_category_ad on book(price asc,category_id desc);

注意： MySQL5.0 以上才支持这种索引方式，默认都为升序

对于排序，重点是要关注索引的结构

另外，如果返回的结果字段不是覆盖索引的话，依然要重新排序返回

优化细节：

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

8.4. group by优化

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

示例：
先删除联合索引 Price-Category_ID

分组查询统计字段Price，查看查询计划

可以看到用了临时表，没有直接返回结果
建立Price索引

再次执行分组操作

可以看到可以直接返回结果，不需要添加临时表，效率大大提高

8.5. limit 优化

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

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

示例：
对于一张800W数据的表，只有主键索引（聚集索引）

先普通方式查询 500W开始的 10条数据，看看时间

耗时 3.91s
再用优化后的查询方式

耗时仅为 0.97s !!! 时间大大提高了啊

该种优化方式的思想为，先用limit查询出主键id，这样不用取出所有数据，提高了效率，再通过主键id进行索引查询取出全部数据

8.6. count 优化

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回个数，效率很高。（但是不能用 where 条件，否则效率也低）
• InnoDB 引擎就麻烦一些，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

优化方法

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

按照效率排序：
count(字段) < count(主键 id) < count(1) ≈ count(*)
所以尽量使用 count(*)

示例：
再次拿出我那800W数据进行验证：

差距还是蛮明显的，字段 耗时3.76s，主键 耗时2.25s，1 耗时1.62s，* 耗时1.63s

8.7. update 优化

InnoDB 存储引擎有一个特点，支持 行级锁，所以这意味着当多个线程同时更新一张表时，若更新的行不同，则可以同时对该表进行更新修改；
同时介绍一下 表锁，即多个线程要对表进行修改的时候，不能同时修改，因为锁住的是整张表，只能一个一个排着队进行

所以 update 的优化，意味着要规避 行锁升级为表锁：
InnoDB 的行锁是针对 索引 加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

8.8. 千万级数据生成方法

对于大型数据，公司肯定不会随便拿来给你玩的，自己去找也不靠谱，碰到里面有错误，百万数据里去改，人都要麻掉，所以有时候为了测试性能，可以自己动手生成一个千万级大型数据用来自己测试模拟：

# 1. 建个表
CREATE TABLE `user_test` (
id BIGINT(20) UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_name`  VARCHAR(255) DEFAULT NULL  COMMENT '用户名',
`pass_word`  VARCHAR(255) DEFAULT NULL  COMMENT '密码',
PRIMARY KEY (id)
)
ENGINE=INNODB
AUTO_INCREMENT=1
COMMENT= '用户信息表'
;

# 2. 先插入一条数据（一定要先插入）
INSERT INTO user_test (user_name, pass_word) VALUE ('张三','12345');

# 3. 开始执行这个牛逼的代码
INSERT INTO user_test (user_name, pass_word) 
SELECT
    MD5(RAND()),
    RAND(10000) 
FROM
     user_test;

这是一个以2的指数增长的数据指令，点23次有800多W，24次就上千W了