MySQL进阶第三天——SQL优化

insert优化

• 批量插入

insert into tb_test values(1,"Tam"),(2,'Cat'),(3,"Jlerrt");

• 手动提交事务

start transaction;
insert into tb_test values(1,"Tam"),(2,'Cat'),(3,"Jlerrt");
insert into tb_test values(1,"Tam"),(2,'Cat'),(3,"Jlerrt");
insert into tb_test values(1,"Tam"),(2,'Cat'),(3,"Jlerrt");
commit;

• 主键顺序插入

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

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

#查看是否开启local_infile
select @@local_infile

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

#执行load指令将准备好的数据，加载到表结构中
load data local infite <文件地址> into table <表名> fields teminated by ';' lines terminated by '\n' ;

主键优化

• 数据组织方式
  • 在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table lOT)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ee6pm9dg-1663913718434)(%E9%80%BB%E8%BE%91%E5%AD%98%E5%82%A8%E7%BB%93%E6%9E%84.png)]

• 页分裂（主键乱序插入）

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

• 页合并

  • 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

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

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

order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

   1. order by 遵循最佳左前缀原则
      1. where后面的内容是个整体，mySql会整体进行优化的，所以可以不考虑顺序
      2. 虽然会整体优化,但不会对order by 之后的内容做局部优化
      3. 范围查询后索引依旧会失效
      4. 索引失去带头大哥会出现using filesort
   2. 字段必须保证相同的顺序（同升或同降),否则会产生using filesort,如果真的需要使用ASC（desc）的话，可以翻转字符串

2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

   1. 双路排序
      1. 过程：两次I/O，第一次I/O取出指针和order by 字段进行排序，第二次I/O将记录的其余字段和排序字段进行拼接
      2. 缺点：两次读写I/O
   2. 单路排序
      1. 过程：一次I/O，一次将全部记录读进内存进行排序
      2. 缺点：由于记录过大，可能一次无法将全部记录读进内存
   3. 怎样进行优化
      1. 增大max_length_for_sort_data：如果需要排序的列的总大小+order by列的大小超过了 max_length_for_sort_data定义的字节，mysql就会使用双路排序，但是该值如果过高会导致数据的总容量>缓冲区的容量，这个时候会造成高I/O和低cpu处理
      2. 提高sort_buffer_size:两种算法都会超出sort_buffer的容量，超出容量后会创建临时表进行合并，导致多次I/O
      3. 尽量不要使用select * ,基于max_length_for_sort_data进行考虑的

order by能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a, b,c
- ORDER BY a DESC,b DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by能使用索引
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b = const ORDER BY c
- WHEREa = const ORDER BY b, c
- WHERE a = const AND b > const ORDER BY b, c

不能使用索引进行排序
- ORDER BY a ASC, b DESC, c DESC  /*排序不一致*/
- WHERE g = const ORDER BY b,c  /*丢失a索引*/
- WHERE a = const ORDER BY c  /*丢失b索引*/
- WHERE a = const ORDER BY a, d  /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c  /*大对于排序来说，多个相等条件也是范围查询*/

limit 优化

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

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

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

count 优化

explain select count(*) from tb_user ;

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高;

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

优化思路:自己计数。

sql优化总结

1. 插入数据

insert:批量插入、手动控制事务、主键顺序插入
大批量插入:load data lacal infile

2. 主键优化

主键长度尽量短、顺序插入
AUTO_INCREMENT UUID

3. order by优化

using index:直接通过索引返回数据，性能高
using filesort:需要将返回的结果在排序缓冲区排序

4. group by优化

索引，多字段分组满足最左前缀法则

5. limit优化

覆盖索引＋子查询

6. count优化

性能: count(字段)< count(主键id)< count(l)  count(*)

7. update优化

尽量根据主键/索引字段进行数据更新

``
5. limit优化

覆盖索引＋子查询

6. count优化

性能: count(字段)< count(主键id)< count(l)  count(*)

7. update优化

尽量根据主键/索引字段进行数据更新