数据库--sql优化

插入数据

insert优化：一次性插入多条数据，介意批量插入
1、批量插入
500-1000条：

insert into tb_test values(1,'TOM'),(2,'CAT'),(3,'JERRY');

2、手动提交事务

start transaction;
insert into tb_test values(1,'TOM'),(2,'CAT'),(3,'JERRY');
insert into tb_test values(1,'TOM'),(2,'CAT'),(3,'JERRY');
insert into tb_test values(1,'TOM'),(2,'CAT'),(3,'JERRY');
commit;

3、主键顺序插入

100万数据

大批量插入数据–加载本地文件数据

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

#客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load date local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键优化

数据组织方式
在innoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
主键设计原则

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

order by排序优化

将相关的字段添加了索引，就会出现using index,性能较高

backward index scan:反向扫描索引

创建索引时，可以设置索引的升序还是降序规则形成索引

#默认都是升序
create index idx_user_age_phone_ad on tb_user;
#创建一个升序一个降序
create index idx_user_age_phone_ad on tb_user(age asc,phone desc);

group by优化

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

limit优化

如果limit后面的第一个跨度大，会大大降低执行效率，越往后，越耗时间。
eg:
limit 100万 10

优化：覆盖索引加子查询

select s.* from tb_sku s,(select id from tb_sku order by id limit 9000000,10) a where s.id = a.id

count优化

count(参数)是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值。

count(*)是mysql专门做了优化的

update的优化

行级锁：注意只有在更新判断字段上，该字段具有索引时，才会使用行级锁，否则使用表锁！
所以更新字段时，一定要根据有索引的字段进行更新；eg:

update course set name = 'SPRING BOOT' where name = 'PHP';

当name没有索引时，mysql就会使用表锁，如果在没有提交的情况下，有其他事务也开始对该表进行操作，那就会等待前一个表锁释放，才能操作该表其他条的数据。但是如果name具有索引，那就会只锁住这一条数据，而其他事务可以对这个表的其他条数据进行更新操作