1.插入数据
1.insert优化
1.批量插入
/*批量插入数据*/
insert into 表名 values ( , , ,),( , , ,);
2.批量插入手动提交事务
start transaction;
insert into 表名 values ( , , ,),( , , ,);
insert into 表名 values ( , , ,),( , , ,);
insert into 表名 values ( , , ,),( , , ,);
commit;
主键顺序插入的性能高于乱序插入
2.load大批量的插入数据
如果一次性需要大批量的插入数据,使用insert的语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。
load指令使用步骤
数据
2.主键优化
1.数据的组织方式
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式称为索引组织表。
2.页分裂
也可以为空,也可以填充一半,也可以填充100%.每个页包含了2-N行数据(如果一行数据多了,会行溢出),根据主键排列。
1.分裂
2.分裂
2.页合并
当删除一行记录时,实际记录并没有被物理删除,只是记录被标记为删除并且他的空间允许被其他数据使用。当页中数据删除达到默认页50%,InnoDB开始寻找靠近页(前或后)看看是否可以将两个数据页合并以优化空间使用。
2.主键的设计原则
3.order by 优化
-
Using filesort:这种操作是指数据库在处理查询时,如果无法直接通过索引返回排序结果,那么它会先读取满足条件的数据行,然后在排序缓冲区(sort buffer)中进行排序。这种情况下的排序被称为FileSort排序。
-
Using index:这种操作是指数据库能够通过有序索引顺序扫描直接返回有序数据,即使用索引来避免额外的排序操作,这样的操作效率较高,因为不需要进行排序。没有创建索引时的SQL查询语句:没有创建索引时的SQL查询语句:
/*没有创建索引时的SQL查询语句:*/
explain select id,age,phone from tb_user order by age , phone;
/*创建索引的SQL语句:*/
create index idx_user_age_phone_aa on tb_user(age,phone);
/*创建索引后,根据age,phone进行升序排序的SQL查询语句:*/
explain select id,age,phone from tb_user order by age , phone;
/*创建索引后,根据age,phone进行降序排序的SQL查询语句:*/
explain select id,age,phone from tb_user order by age desc , phone desc ;
尽量避免使用fillsort(需要额外在缓冲区排序),要使用index(不需要额外排序),order by遵循最左前缀法则,且为覆盖查询
根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
4.group by 优化
/*删除掉目前的联合索引 idx_user_pro_age_sta*/
drop index idx_user_pro_age_sta on tb_user;
/* 执行分组操作,根据profession字段分组*/
explain select profession, count(*) from tb_user group by profession ;
/*创建索引*/
Create index idx_user_pro_age_sta on tb_user(profession, age , status);
/*执行分组操作,根据profession字段分组*/
explain select profession, count(*) from tb_user group by profession ;
/*执行分组操作,根据profession字段分组*/
explain select profession, count(*) from tb_user group by profession, age;
懵逼
5.limit优化
limit分页查询
一个常见又非常头疼的问题就是 limit 2000000,10 ,此时需要MySQL排序前2000010记录,仅仅返回2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大。
优化思路:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku_t,(select id from tb_sku order by id limit 2000000 10) a where tid = aid;
6.count优化
explain select count(*) from tb_user;
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
InnoDB引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
优化思路:自己计数。
1.count的几种用法
-
count(主键):InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。
-
count(字段):没有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。
-
count(1):InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加。
-
count(*):InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
按照效率排序:count(字段)<count(主键)<count(1)~~count(*)
7.update优化
update student set no='2000100100' where id =1;
第二个SQL语句:
update student set no='2000100105' where name='韦一笑';
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
where后面的条件为有索引的字段(行锁),如果为五索引字段否则升级为表锁影响并发事务功能
8.总结
-
插入数据:
- insert:批量插入、手动控制事务、主键顺序插入
- 大批量插入:load data local infile
-
主键优化:
- 主键长度尽量短、顺序插入
- AUTO_INCREMENT
- UUID
-
order by优化:
- using index:直接通过索引返回数据,性能高
- using filesort:需要将返回的结果在排序缓冲区排序
-
group by优化:
- 索引,多字段分组满足最左前缀法则
-
limit优化:
- 覆盖索引+子查询
-
count优化:
- 性能:count(字段)<count(主键id)<count(1)≈count(*)
-
update优化:
- 尽量根据主键/索引字段进行数据更新