MySQL-SQL优化

SQL优化

插入数据优化

INSERT插入

• 批量插入

Insert into tb test values(1, 'Tom),(2, ‘Cat’),(3,‘Jerry’);

• 手动事务提交

start transaction;

insert into tb_test values(1, ‘Tom’),(2, ‘Cat’), (3, ‘Jerry’);

insert into tb_test values(4’Tom’),(5,‘Cat’),(6,‘Jerry’);

insert into tb test values(7, Tom’),(8, 'Cat),(9,‘Jerry’);

commit;

• 主键顺序插入

主键乱序插入：8 1 9 21 88 2 4 15 89 57 3

主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89

大批量插入数据：如果一次性需要插入大批量数据，使用 INSERT插入性能较低，此时可以使用MySQL数据库提供的LOAD指令进行插入

#客户端连接服务端时，加上参数–local-infile

mysal-local-infile -u root -p

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

set global local_infile = 1;

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

load data local infile ‘/root/sql1.log’ into table tb_user’ fields terminated by ‘,’ lines terminated by "/n’;

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

主键优化

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

页分裂：页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列

主键乱序插入会导致页分裂

页合并

主键设计原则：

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

ORDER BY 优化

Using filesort：

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

Using index：

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

例：

#根据age,phone进行降序一个升序，一个降序

explain select id,age,phone from tb_user order by age asc, phone desc;

#创建索引

创建索引时，默认为ASC排序，如果此时使用DESC查询会发生回表Using filesort

create index idx_user._age_phone_ad on tb_user(age asc,phone desc);

#根据age,phone进行降序一个升序，一个降序

explain select id, age,phone from tb user order by age asc, phone desc;

ORDER BY 优化原则：

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

GROUP BY优化

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

LIMIT优化

一个常见的问题是limit 20000000 10，此时需要查询MySQL排序前20000010记录，仅返回2000000-20000010的记录，

其他记录丢弃，查询排序的代价很大

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

COUNT优化

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 COUNT(*) 时会直接返回个数效率很高
• InnoDB 引擎比较麻烦，它执行COUNT(*)时，需要把数据一行一行从引擎中读取出来，然后累积计数

优化思路：自己计数

COUNT 的几种用法：

• COUNT(*) 对于返回的结果集，一行行的判断，如果 COUNT 函数的参数不是 NULL，累计值就加1，否则不加，最后返回累计值

• 用法COUNT(*)、COUNT(主键)、COUNT(字段)、COUNT(1)

  COUNT(主键) ：

  ​ InnoDB 引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，

  直接按行进行累加（主键不可能为null）。

  COUNT(字段)：

  ​ 没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

  COUNT(1) ：

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

  COUNT(*)：

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

• 按照效率排序的话，count(字段)<count(主键 id)< count(1)~count(**),所以尽量使用 count(*)

UPDATE优化

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