SQL优化——插入数据、主键优化、order by 优化、group by 优化、limit 优化、count优化、update优化、

1、SQL优化1——插入数据（Insert）

1.1、普通插入：

1.1.1、采用批量插入（一次插入的数据不建议超过1000条）

1.1.2、手动提交事务

1.1.3、主键顺序插入

1.2、大批量插入

1.2.1、在客户端连接服务器的时候，不用mysql -u root -p，加上参数--local-infile(这一行在bash/cmd界面输入)

1.2.2、设置全局函数local_infile=1，也就是开启从本地加载文件导入数据的开关

1.2.3、查看开关是否开启

1.2.4、执行load指令将准备好的数据，加载到表结构中

2、SQL优化2——主键优化

2.1、页分裂

2.1.1、主键顺序插入：会根据主键的ID顺序进行插入，第一页写完之后，开始第二页，之间会维护一个双向指针

2.1.2、主键乱序插入：主键ID不是排序好的，当我们要插入ID=50的时候，因为叶子节点是有序的，所以50必须存放在47之后，也就是应该将50放在第一页。

2.2、页合并

2.3、主键索引设计原则

2.3.1、满足业务条件的情况下，尽量减小主键的长度，因为耳机索引的时候叶子节点挂的是主键

2.3.2、插入数据的时候，尽量选择顺序插入，可以选择自增主键

2.3.3、尽量不要使用无序，长度太长的作为主键，例如身份证

2.3.4、在实际操作的时候，尽量避免对主键的操作

3、SQL优化3——order by 优化

3.1、using filesort

3.2、using index

3.3、举个例子

3.3.1、创建表格

3.3.2、执行以下两个查询语句。

3.3.3、但是当name升序，phone降序，又会出现什么情况呢

4、SQL优化4——group by 优化

4.1、从表中查找gender，并对gender进行分组

4.2、此时可以给这个字段添加索引，提高效率

4.3、执行语句

4.4、总结：

4.4.1、在分组操作时，可以通过索引来提高效率

4.4.2、分组操作的时候，还是必须要满足最左前缀法则的

5、SQL优化5——limit 优化

5.1、优化方案

5.1.1、通过创建覆盖索引能够比较好的提高性能

5.1.2、之后，通过覆盖索引加子查询形式进行优化

5.2、实施例子

5.2.1、查询9000000-9000010

5.2.2、使用覆盖索引加快速度，直接通过主键索引进行排序和查询，先执行order by再执行limit

5.2.3、通过id找数据

5.3、总结

6、SQL优化6——count优化

6.1、count的几种用法

6.1.1、如果count函数的参数（count里面写的字段）不是null（字段值不是null），累计就加一，最后返回累计值

6.1.2、用法：count(*)、count(主键)、count(字段)、count(1)

6.1.3、count（*）和count（主键）一样，因为主键不可以为空；count（字段）只计算字段值不是null的行；count（1）引擎会为每一行添加一个1，然后count这个1，返回结果也跟count（*）一样；count(null)返回0

6.2、count用法的性能

6.2.1、count(主键)：InnoDB引擎会遍历整张表，把每行的主键id都取出来，返回给服务层，服务层拿到主键之后，直接按照行进行累加（主键不可能为空）

6.2.2、count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层会判断是否为null，不为null，计数累加。如果有not null约束的话，InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加

6.2.3、count(1)：InnoDB引擎会遍历整张表，但不取值，服务层对于返回的每一层，放一个数字1进去，直接按行进行累加

6.2.4、count(*)：InnoDB引擎并不会把全部字段都取出来，而是专门做了优化，不取值，服务层直接按行进行累加；

6.3、总结效率

7、SQL优化7——update优化

7.1、这句sql语句id有主键索引，索引只会锁这一行

7.2、这句话name没有索引，所以会将整张表都锁住进行数据更新（当我们开了两个事务的时候，必须等这句话的事务完成之后，才能提交另一个事务）

1、SQL优化1——插入数据（Insert）

在SQL优化中，如何高效插入数据也是比较重要的一点，那么现有的插入数据类型主要分为普通插入和大批量插入。

1.1、普通插入：

1.1.1、采用批量插入（一次插入的数据不建议超过1000条）

语法：

Insert into tb_test values(1,'A'),(2,'B'),(3,'C')...;

1.1.2、手动提交事务

这比起1.1的优点就是1.1在没插入一次数据就要提交一次，而手动提交事务是所有的数据插入完成之后，一起进行提交，相比较于1.1更高效，更省时间

语法：

Start transaction;
Insert into tb_test values(1,'A'),(2,'B'),(3,'C')...;
Insert into tb_test values(4,'D'),(5,'E'),(6,'F')...;
Insert into tb_test values(7,'G'),(8,'H'),(9,'I')...;
......
commit;

1.1.3、主键顺序插入

举个例子：

乱序插入：4，7，2，5，78，9，1；

顺序插入：1，2，5，4，7，9，78；

顺序插入的性能会比乱序插入的性能更高

1.2、大批量插入

如果一次性需要插入大量的数据（上百万的数据100w-500w），使用insert插入的话就很低效，性能较低，此时可以使用MySQL数据库提供的load指令插入

语法：

1.2.1、在客户端连接服务器的时候，不用mysql -u root -p，加上参数--local-infile(这一行在bash/cmd界面输入)

mysql --local-infile -u root -p

1.2.2、设置全局函数local_infile=1，也就是开启从本地加载文件导入数据的开关

set global local_infile=1;

1.2.3、查看开关是否开启

select @@local_infile;

1.2.4、执行load指令将准备好的数据，加载到表结构中

load data local infile '/你的sql文件地址' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的（聚集索引，主键下面挂的是一行数据），这种存储方式的表称为索引组织表（index organized table, IOT ）。

2、SQL优化2——主键优化

2.1、页分裂

主键索引的页可以为空，也可以为一半，也可以为100%，每个页都会包含2N行数据（如果一行的数据过大，超出了一定的阈值之后，会出现行溢出）

2.1.1、主键顺序插入：会根据主键的ID顺序进行插入，第一页写完之后，开始第二页，之间会维护一个双向指针

2.1.2、主键乱序插入：主键ID不是排序好的，当我们要插入ID=50的时候，因为叶子节点是有序的，所以50必须存放在47之后，也就是应该将50放在第一页。

但是第一页现在剩下的空间不够ID=50，那么此时就会开辟新的数据页，找到第一个数据页50%的数据，将23和47这两个数据移动到第三页，再将50放在47后面

此时双向链表第一页就把不能接上第二页了，而是指向第三页。这种现象就叫页分裂

2.2、页合并

当删除一行记录的时候，实际上记录并没有被物理删除，只是标记一下（例如红色圆圈里面的13，14，15，16），并且这四个空间变得运行被其他记录声明使用。当页中删除的记（MERGE_THRESHOLD）页的50%的时候，InnoDB会寻找最靠近的前后页，看看是否可以将两个页合并，从而优化空间。

也就是说，当13，14，15，16被标记删除后，系统会找到第三页的17，18，19.将这三个数据放在第二页，那么第三页就空出来了，这样就可以达到优化空间的效果

MERGE_THRESHOLD：可以自己设计

2.3、主键索引设计原则

2.3.1、满足业务条件的情况下，尽量减小主键的长度，因为耳机索引的时候叶子节点挂的是主键

2.3.2、插入数据的时候，尽量选择顺序插入，可以选择自增主键

2.3.3、尽量不要使用无序，长度太长的作为主键，例如身份证

2.3.4、在实际操作的时候，尽量避免对主键的操作

3、SQL优化3——order by 优化

关于SQL优化，order by 在执行过程中会出现两种类型：using index 和 using filesort

3.1、using filesort

using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作。所有不是通过索引直接返回排序结果的排序都叫做filesort排序

3.2、using index

using index：通过有序序列顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

3.3、举个例子

3.3.1、创建表格

索引为：

3.3.2、执行以下两个查询语句。

explain select id, age, phone from tb_user order by age;
explain select id, age, phone from tb_user order by age,phone;

发现Extra都是using filesort，查询效率不高，解决办法就是

create index index_age_phone on tb_user(age,phone);

发现现在的都是using index，查询效率提高

3.3.3、但是当name升序，phone降序，又会出现什么情况呢

explain select id, age, phone from tb_user order by age asc,phone desc;

发现既有using index又有using filesort。因为age升序之后，phone需要反向排序

解决办法就是，建立索引

create index index_age_phone_ad on tb_user(age asc,phone desc);

4、SQL优化4——group by 优化

在SQL优化的时候，group by分组操作的时候，可以通过索引来提高效率

举个例子

4.1、从表中查找gender，并对gender进行分组

 explain select gender, count(*) from tb_user group by gender;

发现Extra=using temporary，表示效率不高。

4.2、此时可以给这个字段添加索引，提高效率

 create index index_user_gender_age_phone on tb_user(gender,age,phone);

4.3、执行语句

explain select age, count(*) from tb_user group by age;

出现这个原因是因为，他违背了最左前缀法则

解决：

 explain select gender, age, count(*) from tb_user group by gender, age;

或者

explain select  age, count(*) from tb_user where gender='1' group by age;

4.4、总结：

4.4.1、在分组操作时，可以通过索引来提高效率

4.4.2、分组操作的时候，还是必须要满足最左前缀法则的

5、SQL优化5——limit 优化

如果数据量很小的时候，limit优化效果是很不明显的，当数据量很大的时候（上百万），需要分页查询数据的时候，例如limit 2000000，10，此时需要MySQL排序前2000000条记录，但是我们仅仅需要得到的数据量只有2000000-2000010，这10个记录，其他的记录丢弃，这样会造成排序的代价非常大。那么，该如何优化呢？

5.1、优化方案

5.1.1、通过创建覆盖索引能够比较好的提高性能

5.1.2、之后，通过覆盖索引加子查询形式进行优化

5.2、实施例子

5.2.1、查询9000000-9000010

select * from tb_user limit 9000000,10;

这个语句将会耗时很长，如何解决呢？

5.2.2、使用覆盖索引加快速度，直接通过主键索引进行排序和查询，先执行order by再执行limit

select id from tb_user order by id limit 9000000,10;

这个语句就得到了9000000-9000010这10个数据的id

5.2.3、通过id找数据

错误示范：MySQL不支持in里面使用limit

select * from tb_user where id in (select id from tb_user order by id limit 9000000,10);

正确动作：将select * from tb_user where id in (select id from tb_user order by id limit 9000000,10);的结果看成一张表

select t.* from tb_user as t, (select id from tb_user order by id limit 9000000,10) as a where t.id=a.id;

5.3、总结

limit优化包括两个步骤：先覆盖索引，再子查询。

6、SQL优化6——count优化

关于count优化，首先我们先来看一下引擎。

MyISAM引擎：把一个表的总行数存在了磁盘上，因此执行coount(*)的时候会直接返回这个数，效率很高（前提是不适用where）；

InnoDB引擎：在执行count(*)的时候，需要把数据一行一行的从引擎中读出来，然后累计技术。那么InnoDB引擎是现有的最常用的引擎，该如何进行优化呢？可以自己去维护，自己计数，写入一个数据，就加一；