Mysql的sql优化方案--黑马笔记

1. 简介

? SQL语句的优化是将性能低下的SQL语句转换成目的相同但是性能优异的SQL语句。

2. sql优化策略

2.1 主键优化

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

2.2 order by 优化

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

• 尽量使用覆盖索引。

• 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

  create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
  

• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

2.3 group by 优化

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

2.4 limit 优化

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

2.5 插入优化

当你要执行多条插入语句时，如

insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,'c');
......

2.1.1 批量插入数据

Insert into test values(1,'a'),(2,'b'),(3,'c').....;

2.1.2 手动控制事务

start transaction;
insert into test values(1,'a'),(2,'b'),(3,'c');
insert into test values(4,'d'),(5,'e'),(6,'f');
commit;

2.1.3 顺序插入

主键顺序插入，性能要高于乱序插入。
主键乱序插入 : 3 532 33 23 54 56 21 14 34
主键顺序插入 : 1 2 3 4 5 7 8 9 10 24 67 87

2.1.3 load指令进行插入

步骤

1. 创建表结构

2. 设置参数

   -- 客户端连接服务端时，加上参数 -–local-infile
   mysql –-local-infile -u root -p
   -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
   set global local_infile = 1;
   

3. load加载数据

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

2.6 count 优化

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。

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

  优化思路：

  • 自己计数(可以借助于redis这样的数 据库进行,但是如果是带条件的count又比较麻烦了)。

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

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

2.7 更新优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能 大大降低。

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