SQL语句优化
-
插入数据优化
-
批量插入
注:通过测试,若插入一千万条数据,不使用批量插入,每条一个insert需要耗费时间10分钟左右,但若是通过批量插入耗费时间为十几秒左右 ,由此可见使用批量插入可以极大的提高性能,同时减少多次访问数据库对数据库造成的压力。
注:若在springboot或springcloud中使用批量插入,需要在写连接到数据库的配置时将批量插入的开关打开,否则即使你在使用 insert in() 时也不会使用批量插入。
如果一次性需要插入大量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load命令进行插入。
前提:本地需要插入数据的文件中文件的内容需要符合规范。每个字段使用逗号分隔。(这里的逗号可以是别的符号,但必须保持一致)
在数据库管理工具中使用批量插入的语句:
#客户端连接服务端时,加上参数 --local-infile mysql --local-infile -u root -p #设置全局参数local——infile为1,开启从本地加载文件导入数据的开关 set global local_infile = 1; #执行load指令将准备好的数据,加载到表结构中 load data local infile '/rooot/sql1.log' (文件名) into table 'tb_user'(表名) fileds terminated by ','(设置字段间分割符) lines terminated by '\n';(设置换行符号)
-
手动提交事务
若不手动控制事务,每条sql语句都会是一个单独的事务,降低性能,而开启手动后可以多条sql语句一起提交,优化性能。
- 主键顺序插入(与b+tree结构有关,涉及到页分裂)
-
-
主键优化
-
数据组织方式
在InnoDB引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索组织表。(想想B+Tree底层叶子结点结构,
之前的文章里有,也可去百度B+Tree结构模型)
所以建议主键存储时使用自增,在符合要求的情况下,尽量降低主键的长度,且使用自增,不要使用UUID随机生成,尽量避免产生页分裂。
-
-
order by 优化
-
Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫做FileSort排序。该方法进行排序性能相对较低。
-
Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。
所以我们在进行优化时尽量使进行排序时使用Using index
例:
#没有创建索引时 select id,age,phone from tb_user order by age,phone (Using filesort) #创建索引 create index idx_user_age_phone_aa on tb_user(age,phone) #创建索引后,根据age,phone进行升序排序 select id,age,phone from tb_user order by age,phone (Using index) #创建索引后,根据age,phone进行降序排序 select id,age,phone from tb_user order by age desc,phone desc (Using index) #根据age,phone进行排序,一个使用升序,一个使用降序 select id,age,phone from tb_user order by age asc,phone desc; (Using filesort) #创建索引 create index idx_user_age_phone_ad on tb_user(age asc,phone desc); (创建索引时age根据升序创建,phone根据降序创建) #根据age,phone进行排序,一个降序,一个升序 select id,age,phone from tb_user order by age asc,phone desc; (Using index)
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。(前面的文章有介绍最左前缀法则)
- 尽量使用覆盖索引。
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
- 如果不可避免的出现filesort,大量数据排序时,可以适当增加排序缓冲区大小sort_buffer_size(默认256k)。
-
-
group by 优化
#执行分组 操作,根据profession字段进行排序 select profession,count(*) from tb_user group by profession (Using temporary 使用临时表,性能低) #创建索引 create index idx_user_pro_age_sta on tb_user(profession,age,status); #执行分组操作,根据profession字段分组 select profession,count(*) from tb_user group by profession (Using index) #执行分组操作,根据age字段分组 select age,count(*) from tb_user group by age (Using temporary 不满足最左前缀法则) #执行分组操作,根据profession进行筛选,age字段排序 select age,count(*) from tb_user where profession = ‘软件工程’ group by age;(Using index 满足最左前缀法则)
- 在分组操作时,可以通过索引来提高效率。
- 分组操作时,索引的使用也是满足最左前缀法则的。
-
limit 优化
-
一个常见又非常头疼的问题就是 limit 2000000,10,此时需要MySQL排序前2000010记录,仅仅返回2000000-2000010的记录,其他记录丢弃,查询排序的代价非常大。
-
通过覆盖索引加子查询的方式优化:
例:
select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id #此条sql语句如果看不懂可以先了解下多表查询中的子查询和自连接查询
-
-
count 优化
-
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
-
InnoDB引擎就很麻烦了,他执行count(*)的时候,需需要把数据一行一行的从引擎里面读出来,然后累积计数。
-
优化思路:自己计数
-
count的几种用法:
-
count是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加一,否则不加,最后返回累计值
-
用法: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(主键)<count(1)≈count(*)
-
-
-
update 优化
update student set no='20000100' where id = 1 #id为主键,必然有索引,所以加的是行锁
update student set no='199999987' where name = '韦一笑' #(若是name没有设置索引,那么在修改时会加表锁,降低性能)
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁