insert优化
普通批量插入
insert into tb_name values(...),(...)...;
手动提交事务
start transaction;
insert into tb_name values(...),(...)...;
insert into tb_name values(...),(...)...;
insert into tb_name values(...),(...)...;
commit;
主键按照顺序插入
顺序插入性能高于乱序插入
大批量插入数据
不推荐使用insert，推荐使用load进行插入，把本地文件中的数据插入
数据的结构
#连接客户端的时候，加上参数 --local-infile
mysql --local-infile -uroot -p password
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load命令
load data local infile 'file_pwd' into table `tb_name` fields terminated by ',' lines terminated by '\n';
主键优化
- 尽量降低主键长度
- 尽量选择顺序插入
order by优化
- Using filesort
先把数据取出来然后在排序缓冲区sort buffer中完成排序操作
- Using index
通过索引扫描直接返回有序的数据，不需要额外排序，性能高
因为创建索引的时候默认是数据按照升序排列的，如果创建一个联合索引，查询的时候创建联合索引的这两个字段一个升序，一个降序，那么就会先Using index，在Using filesort
因为第一个字段按照升序取出所有字段，然后再根据第二个字段进行降序排列，两个都会用到。
这时我们只能再创建一个索引，在这个联合索引中第一个字段升序，第二个字段降序
# 两个默认升序
create index idx_user_age_phone on tb_user(age, phone);
# 只有Using index
select id, age, phone from tb_user order by age asc, phone asc;
# 有Using index也有Using filesort
select id, age, phone from tb_user order by age asc, phone desc;
# 创建一个升序一个降序
create index idx_user_age_phone_ad on tb_user(age asc, phone desc);
# 此时再使用第二条sql就是Using index
group by优化
也是建立索引去优化速度
limit优化
limit越往后查询代价也就越大
做法：覆盖索引+子查询
# 原先的做法
select * from tb_user limit 100000, 10;(19秒)
# 现在的做法
# 子查询
select id from tb_user limit 100000, 10;
select s.* from tb_user s, (select id from tb_user limit 100000, 10) a where s.id = a.id;(11秒)
# 或者
select * from tb_user where id in (select id from tb_user limit 100000, 10)(11秒)
count优化
优化思路：自己计数，比如使用redis去存储总数
count(*)是最快的，count(id)其次
update优化(防止行锁升级为表锁)
当我们开启一个事务，根据字段去update的时候，如果该字段有索引且索引没有失效，那么这时候这一行数据会有一个行锁，如果该字段没有索引或者索引失效，那么这时的行锁就会升级为表锁，大大降低了我们的并发性能，所以我们要避免这种情况的发生
