目录
insert优化
主键优化
页分裂
order by优化
测试：
group by优化
limit优化
count优化
update优化
insert优化
如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。
优化方案一
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');批量插入会，会频繁调用事务，因此我们有了第二种优化方案。
优化方案二
手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;优化方案三
主键顺序插入，性能要高于乱序插入-----后文会讲解
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
优化方案四-大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使 用MySQL数据库提供的load指令进行插入。操作如下：
左图为待插入的数据，右图为sql中搜索的数据，大概有100w数据插入需要19秒左右。
-- 第一步：客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 第二步设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 第三步执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;主键优化
接下来我们来说一下主键优化策略。为什么说顺序插入会优于乱序插入。
我们都知道在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT)。
行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：
在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。 那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储 到下一个页中，页与页之间会通过指针连接。
页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行 溢出)，根据主键排列。
主键顺序插入效果。
插入第一页。
可以看出顺序插入时，数据会依次插入到每页当中。
主键乱序插入效果
假如，我们已经存入以下数据
此时再插入id为50的记录，我们来看看会发生什么现象
因为索引结果的叶子结点是有序的，按照顺序由应该在47之后
但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。
乱序插入有页分裂现象，因此顺序插入性能会优于乱序，因此在插入数据时，一定使得数据顺序插入。
【主键设计原则】
- 满足业务需求的情况下，尽量降低主键的长度。---保证能够存储较多数据
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号和手机号。---要求主键短，而且自增。
- 业务操作时，避免对主键的修改
order by优化
MySQL的排序，有两种方式：
- Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作。
- 如果缓冲区大小不够的话会在磁盘中进行排序，效率是非常低的，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
- Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要 额外排序，操作效率高。
- 对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。
测试：
在之前测试时，把tb_user表所建立的部分索引直接删除
drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;执行排序SQL
explain select id,age,phone from tb_user order by age ;
explain select id,age,phone from tb_user order by age, phone ;
-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);explain select id,age,phone from tb_user order by age
同时mysql也支持降序联合索引，跟正序是一样的，我们看一下他们的结构图。
由上述的测试,我们得出order by优化原则:
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大sort_buffer_size(默认256k)。
group by优化
我们主要来看看索引对于分组操作的影响。首先我们先将 tb_user 表的索引全部删除掉 。
接下来，在没有索引的情况下，执行如下SQL，查询执行计划：
explain select profession , count(*) from tb_user group by profession;
然后，我们在针对于 profession ， age， status 创建一个联合索引。
create index idx_user_pro_age_sta on tb_user(profession , age , status);紧接着，再执行前面相同的SQL查看执行计划。
- 我们发现，如果仅仅根据age分组，就会出现 Using temporary ；
- 而如果是 根据 profession,age两个字段同时分组，则不会出现 Using temporary。
- 原因是因为对于分组操作， 在联合索引中，也是符合最左前缀法则的。
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 t.id = a.idcount优化
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽 量使用 count(*)。
update优化
针对update，我们需要注意行锁升级为表锁的情况。
update course set name = 'javaEE' where id = 1 ;当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。
update course set name = 'SpringBoot' where name = 'PHP' ;当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能 大大降低。
原因：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁 升级为表锁 。
