MySQL SQL优化

3.3 SQL优化

3.3.1 插入数据

1. insert优化

   -- 1.批量插入，不过万
   insert into tb_name values(1,'a')(2,'b')(3,'c')... ;
   
   -- 2.手动事务提交
   start transaction;
   insert into tb_name values(1,'a')(2,'b')(3,'c')...;
   insert into tb_name values(4,'a')(5,'b')(6,'c')...;
   insert into tb_name values(7,'a')(8,'b')(9,'c')...;
   ...
   commit;
   
   -- 3.主键顺序插入效率会高于乱序插入
   

2. 大批量数据插入

   不建议使用insert，此时可以使用MySQL提供的load指令

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

3.3.2 主键优化

1. 数据组织方式

   在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表索引组织表

2. 页分裂

   • 页可以为空，也可以填充一半，也可以填充100%，InnoDB规定每页至少存储2行数据，所以每个页包含了2-N行数据，根据主键排序

   • 主键索引（聚集索引）要保证主键的顺序性，主键乱序插入可能会出现页分裂

3. 页合并

   • 当删除一行记录时，实际上记录并没有被物理删除，只是标记（flaged）为删除，并且它的空间变得允许被其它记录声明使用

   • 当页中删除的记录达到merge——threshold（默认为页的50%），InnoDB会开始寻找最靠近的页看看是否可以将两个页合并

4. 主键的设计原则

   • 满足业务场景的情况下，尽量降低主键的长度
   • 插入数据时，尽量选择顺序插入，选择使用auto_increment自增逐渐
   • 尽量不使用uuid做主键或者其他自然主键，如身份证号
   • 业务操作时，尽量避免对主键的修改

3.3.3 order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序叫做filesort排序
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为Using index，不需要额外排序，效率高
3. 注意最左前缀法则和覆盖索引
4. 多个字段排序时，一个升序，一个降序，需要注意联合索引在创建时的规则（asc，desc）
5. 若避免不了filesort，大量数据排序时，可以适当增大排序缓冲区sort_buffer_size（默认256K）；

3.3.4 group by优化

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

3.3.5 limit优化

当页数很大时，可以通过覆盖索引加子查询的方式进行优化

select a.* from t1 a,(select id from t1 order by id limit 2000000,10) b where a.id=b.id;

3.3.6 count优化

1. 在MyIsAM引擎中，把一个表中的总数存在了磁盘上，count(*)查询全表时，就会直接返回这个数，效率很高

2. 在InnoDB中，他执行count(*)时，需要把数据一行一行的从引擎中读出来，然后累加

3. count的几种用法

   count()是一个聚合函数，对于返回的结果集，一行行的判断，如果count函数的参数不是null，累计值加一，最后返回。

   用法：count(*)、count(主键)、count(字段)、count(1)

   count(主键)：InnoDB引擎会比遍历整张表，把每一行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加

   count(字段)：InnoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，服务层判断是否为null，不为null在累加

   count(1)：InnoDB引擎遍历整张表，但不取值，服务层对返回的每一行，放一个数字 “1” 进去，直接进行累加

   count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

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

3.3.7 update 优化

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