MYSQL数据库进阶篇——SQL优化

1.插入数据优化

①批量插入数据

②手动提交事务

③主键顺序插入

大批量插入数据：

2.主键优化

数据组织方式

页分裂：页可以为空，也可以填充一半，也可以填满，每个页包含2-N行数据，如果一行数据过大，就会行溢出。

例如：

页合并

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

MERGE_THRESHOLD:合并页的阈值，在创建表或者创建索引时指定。

主键设计原则：

①尽量降低主键长度

②插入数据尽量选择顺序插入，使用auto_increment自增主键

③尽量不要使用UUID做主键，如身份证号。

④避免修改主键

3.order by优化

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

②Using index：通过有序索引顺序扫描直接返回有序数据，这种情况就是Using index，不需要额外排序，操作效率高

①根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

②尽量使用覆盖索引

③多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则

④如果不可避免出现FileSort，大量数据排序时，可适量增大缓冲区大小sort_buffer_size(默认256k）

4.group by优化

①在分组时，可通过索引来提升效率

②分组时，索引的使用符号最左前缀法则

5.limit优化

limit(m,n),如果m特别大，而n比较小，那么有大量数据被丢弃，查询排序的代价特别大。

一般分页查询时，通过创建覆盖索引，可通过覆盖索引加子查询的方式进行优化。

6.count优化

执行select count(*)时：

MYISAM引擎把整个表的总行数存在了磁盘，因此执行时会直接返回这个数，效率高

InnoDB引擎执行时，需要把数据一行一行从引擎读出来，然后计数。

count(主键）：

InnoDB会遍历整个表，把每一行的主键id取出来，返回给服务层，服务层拿到主键后，直接按行累加

count(字段）：

没有not null约束：InnoDB会遍历整张表把每一行字段值都取出来，返回给服务层，服务层判断是否为null，不为bull计数。

有null约束：同主键一样。

count(1):

InnoDB遍历整张表，但不取值，服务层对于返回的每一行，放一个"1"进去，进行累加。

count(*):

InnoDB不会取全部字段，而是做了优化，不取值，服务层直接按行进行累加

按效率排行：

count(字段)<count(主键id)<count(1)≈count(*)

7.update优化

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

最后可以参考一下博客：sql优化的15个小技巧（必知五颗星），面试说出七八个就有了_sql优化常用的15种方法-CSDN博客