SQL语句的优化

插入数据

大量插入数据ｌｏａｄ，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

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

主键顺序插入要高于乱序插入

主键优化

在InnoDB存储引擎当中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）

主键顺序插入的时候b+树的根叶子结点会维护一个双向的链表，使主键顺序的插入。

页分裂

因为页可以为空或者也可以插不满，所以每个页包含了2-n行数据（如果一行数据多大，会行溢出）。当主键乱序插入的时候就有可能会出现页分裂的现象

页合并

当删除一行记录的时候，实际上并没有物理删除，只是被标记为（flaged）未删除并且他的空间变得被允许其他空间记录并使用。

当页中的记录达到MERGE-THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页，看看是否可以将两个页进行合并。

主键的设计原则：

1. 满足业务需求的情况下，尽量降低主键的长度。

2. 插入数据时，尽量顺序插入。

3. 尽量不要使用uuid做主见，或者其他自然主键。

4. 尽量避免对主键的修改。

order by优化：

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

2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index。

#没有创建索引时，根据age,phone进行排序
explain select id,age,phone from tb user order by age , phone
#创建索引
create index idx_user age_phone_aa on tb_user(age,phone)
#创建索引后，根据age,phone进行升序排序
explain select id,age,phone from tb user order by age , phone,
#创建索引后，根据age,phone进行降序排序
explain select id,age,phone from tb user order by age desc , phone desc ;

注意：order by进行优化的时候尽量用using index，默认创建的索引都是升序，当我们查询的时候如果不全是升序，那么还是会出现using filesort 所以我们可以穿件倒序的索引

#根据age,phone进行降序一个升序，一个降序
explain 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),
#根据age,phone进行降序一个升序，一个降序
explain select id,age,phone from tb user order by age asc , phone desc,

如图为创建索引的时候出现索引结构的情况。

注意：

1. 我们需要多字段建立合适索引，多字段排序时也会遵循最左前缀法则（有优先级）。

2. 尽量使用覆盖索引。

3. 多字段排序是，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）

4. 如果不可避免的出现filesort，大量数据排序时，可以适当增加排序缓冲区的大小。

group by优化：

1. 在分组操作时，可以通过索引来提高效率。

2. 分组操作时，索引使用也是满足最左前缀法则的。

limit优化：

• limit深度查询是一个较为头疼的问题，当查询的深度较深时，查询的代价会非常的大。

• 优化思路：

  • 一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化。

  • explain select * from tb sku t , (select id from tb sku order by id limit 2000000,10) a wheret.id = a.id,

count优化：

count没有比较好的优化：在myASIM的存储引擎当中，会将其存在内存当中，没有查询条件的count（*）语句。

count的几种用法：

1. count(主键) InnoDB 引擎会遍历整张表，把每一行的主键id 值都取出来，返]给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。

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

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

4. count(*） InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取服务层直接按行进行累加。直接按行进行累加，

Update优化：

1. 在InnODB里面，行锁是针对索引加的锁，并不是针对记录加的锁，所以在使用Update语句的时候，要避免出现表锁的情况，同样的索引不能够失效，否则也会从行锁升级为表锁。

总结：

1. 插入数据   1. insert：批量插入，手动控制事务，主键顺序插入。  2. 大批量插入：load data local infile
2. 主键优化：   - 主键长度尽量的短，并且主键要按照顺序插入，同时选择主键的时候要选择自增且有序的，uuid不可取，因为过长且无序，容易造成页分裂。
3. order by优化：    1. using index：直接通过索引返回数据。    2. using filesort：需要将返回结果放在排序缓冲区排序。    - 在实际应用中优化尽量使其using index。
4. group by优化：    1. 索引和多字段满足最左前缀法则。
5. limit优化：    1. 覆盖索引+子查询
6. count优化：    1. count（字段）<count(主键)<count(1)~count(*)
7. update优化：    1. 尽量根据主键，索引字段进行更新。