Sql优化

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

前言

SQL优化

插入数据

insert优化

• 建议使用批量插入
• 手动提交事务
• 主键顺序插入

当出现大批量插入数据
此时使用insert语句性能较低，推荐使用load语句

如何使用load？

1. 客户端链接服务器加上参数 --load-infilemysql --local-infile -u -root -p
2. 设置全局参数local_infile为1.开启从本地加载文件导入数据的开关
   我们可以使用：select @@localhost_infile;查看开关是否开启
   
   表示没有开启

set global local_infile = 1;

此时就打开了

3. 执行load指令，将准备好的数据加载到表结构当中。

load data local infile '文件路径' into table 表名 fields terminated by ',' lines terminated by '/n';

表示每个字段用“，”分割，每行用“/n“分割。

主键优化

• 数据组织方式：
  
  ”黄色的块“相当于是一个个的页；
  
  一个区包含64个页，一个页可以为空，

页分裂

主键乱序插入：

此时插入50这个数据会如何操作呢？

因为50这个数据插入不进去1页，所以他会重新区开辟一个页面
将50以及其他元素写入
而此时1数据页下一个页面不再是2数据页
而是3数据页，因此，原本的指针链表会重置。
这种现象就叫做–页分裂

页合并

当页中删除记录达到MERGE_THRESHOLD（合并页阈值，可以自己设置）(默认50%）的时候，会开始寻找靠近的页，观察是否能够合并。

主键设计原则

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

order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高
   
   可以看到，因为没有满足最左前缀法则而产生的Using filesort
   
   因为创建索引时，没有指定顺序，默认时升序，而此时我们age升序，phone降序，就会产生额外的排序。
   可以通过show index from 表名查看
   
   Collation中的‘A’就表示为升序排列
   
   可以通过创建索引指令来实现，一个升序排列一个降序排列
   
   叶子节点排列顺序如上。

总结：

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

group by优化

同样的，group by使用索引时也要满足最左前缀法则


因为不满足最左前缀法则，所以使用了临时表，性能降低。

但是，在分组前方，加一个‘过滤’where profession='软件工程'
我们可以看到，索引正常使用马，没有使用到临时表。

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

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

limit优化

常见的问题如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

– 此语句耗时很长

select * from tb_sku limit 9000000, 10;

– 通过覆盖索引加快速度，直接通过主键索引进行排序及查询

select id from tb_sku order by id limit 9000000, 10;

– 下面的语句是错误的，因为 MySQL 不支持 in 里面使用 limit

 select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);

– 通过连表查询即可实现第一句的效果，并且能达到第二句的速度

select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

count优化

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不适用where）；
InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。
优化方案：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis

count的几种用法：

• 如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值
• 用法：count(*)、count(主键)、count(字段)、count(1)
• count(主键)跟count(*)一样，因为主键不能为空；count(字段)只计算字段值不为NULL的行；count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count(*)一样；count(null)返回0

各种用法的性能：

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

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

update优化

对于update优化，主要为防止行级锁升级为表级锁

我们在第一个客户端开启事务，更新id为1 的这行字段，此时，在我们未提交事务的时候,就把id为1这一行锁住了。
其他修改无法修改成功这一行，但是对其他行没有修改限制

因为我们name字段没有索引，所以把整张表都锁住了，其他修改语句无法成功。
因此我们在更新的时候，要根据索引字段进行更新。

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

总结：