(1) 批量插入 而非逐个插入
(2) 手动提交事务 而非自动提交事务
(3) 主键顺序插入 而非乱序插入

#客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
select @@local-infile; #查询导入数据的开关是否开启
set global local-infile = 1; #开启从本地加载文件导入数据的开关

#执行load指令将准备好的数据加载到表结构中
load data local infile '/export/sql_files/sql_insert.log' 
into table `tb_user` 
fields terminated by ',' 
lines terminated by '\n';

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

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;
explain select profession， count(*) from tb_user group by profession, age;

(1) Using fileSort: 通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫做FileSort排序
(2) Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

create table user (
	id int AUTO_INCREMENT primary key,
    name varchar(32),
    age int,
    phone varchar(11)
);

insert into user (name, age, phone) values ('zhangsan', 21, '15611112222'),('lisi', 19, '13311112222'),('wangwu', 23, '17811112222'),('zhaoliu', 20, '15611116666');

#无索引 -> Using filesort
explain select id,age,phone from user order by age,phone;

#创建索引
create index idx_age_phone_aa on user(age, phone);

#有索引 再次查询
explain select id,age,phone from user order by age,phone;

explain select id,age,phone from user order by age DESC, phone DESC;

explain select id,age,phone from user order by age DESC, phone ASC;

create index idx_age_phone_da on user(age DESC, phone ASC);

explain select * 
from tb_sku t, (select id from tb_sku order by limit 2000000,10) sub_ids 
where t.id = sub_ids.id;