SQL优化相关

---

SQL优化

1. 数据插入

当我们需要插入多条数据时候，建议使用批量插入，因为每次插入数据都会执行一条SQL，都要进行网络传输涉及到TCP建立连接这么一个过程，所以建议使用批量插入

不推荐写法

insert into user (id,name,age) value (1,'张三'，18)；
insert into user (id,name,age) value (2,'李四'，18)；
insert into user (id,name,age) value (3,'王五'，18)；

建议使用批量插入

insert into user (id,name,age) value (1,'张三'，18),(2,'李四'，18),(3,'王五'，18)；

手动控制事务，因为事务的开启和提交页需要一定的开销，那么我们就可以手动控制事务

start transaction;
insert into user (id,name,age) value (1,'张三'，18),(2,'李四'，18),(3,'王五'，18)；
insert into user (id,name,age) value (4,'姜子牙'，18),(5,'嬴政'，18),(6,'孙悟空'，18)；
commit;

受到MySQL索引结构的影响，对于主键的插入建议要顺序进行插入

插入大批量数据，插入大批量数据不建议使用insert语句，建议使用load，指定从本地文件中读取，指定一条数据列之间的分隔符以及每一条数据的分割付，如下

-- 客户端连接时，加上参数 --local-infile
mysql --local-infile -uroot -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 使用load命令进行导入
load data local infile '/root/load_user_100w_sort.sql' into table test fields terminated by ',' lines terminated by '\n';

部分文件数据：

表结构

mysql> desc test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(32) | YES  |     | NULL    |       |
| school | varchar(32) | YES  |     | NULL    |       |
| info   | varchar(32) | YES  |     | NULL    |       |
| time   | datetime    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

2. 主键优化

在InnoDB存储引擎中，表的数据都是根据主键顺序存放的，这种方式称为索引组织表。每一行的数据都是存在叶子节点

在InnoDB引擎中，每一个行数据都是存在逻辑结构page页中，每一页的大小是16K，也就是说一页可以存放多行数据，每一个页通过指针连接形成一个双向链表。

页分裂

当主键顺序插入时，会先把第一个页写满后，再写第二个页，以此类推，页与页之间使用指针进行连接。如图：

当主键乱序插入时，如图：

此时又要插入一个主键为11的数据，该怎么办？开辟一个新页，直接写到后面的新页中吗？显然不合理，因为这并不有序。

InnoDB真正的做法是，开辟一个新的页Page3，将Page1的后半部分数据移动到Page3中，并将数据插入到Page3中再修改页的指针指向。这就被称为页分裂

页合并

其实在一个页里删除一个数据的时候，数据并没有在物理磁盘上真正删除，而是做了一个标记，标记这个一块空间为可使用状态。

如下图每删除一个数据都会标记为可用状态，当删除后一个页的数据只被填充了50%的时候，InnoDB会寻找相邻的页看看是否能进行合并，从而优化空间。如下图，将12和11删除之后就将后面的页进行了合并

注意：**MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。 **

索引设计原则

• 在满足需求的情况下，尽量降低主键的长度，因为二级索引的叶子节点都是存放的主键，如果主键太索引比较多的时候就会占用磁盘空间，并且在搜索的时候增加磁盘I/O
• 插入数据时，尽量使用顺序插入，选择使用AUTO_INCREMENT自增主键
• 尽量不使用UUID或者身份证这一类没有顺序的数据，在插入数据的时候就会造成页分裂，并且UUID这种这么长的字符串作为索引也会增加磁盘I/O从而影响性能
• 尽量不要对主键进行修改，如果对主键进行修改就需要动索引结构，这个代价是非常大的，因为很多二级索引的叶子节点就是主键，并且在聚集索引中主键也是按顺序排序的

3. order by 优化

在MySQL中有两中排序方式：

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

上诉两种排序方式，显然Using index的性能高，所以我们在对查询数据排序时候尽量优化到Using index

比如说查询user表中的id、age、phone，id为主键，通过age,phone进行升序排序

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

此时age和phone没有建立联合索引所以效率比较低，也就是Using filesort

-- 建立联合索引
create index age_phone_index on tb_user(age,phone);

建立联合索引后此时效率就提高了，变成了Using index,但如果按age升序排phone降序排序，最后这就不行了

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

此时就可以在建立索引时指定排序方式

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

来看一下此时的索引结构：

order by优化原则：

• 根据排序字段建立索引，多字段排序的时候，也是遵循最左匹配原则的
• 排序时尽量使用覆盖索引
• 多字段排序，一个升序一个降序，此时在创建联合索引时指定排序规则ASC/DESC
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k) ，因为如果大量数据排序超过排序缓冲区大小就在磁盘文件中进行排序，那么此时效率就会比较低

4. group by 优化

在对查询语句进行分组操作时，也可以建立索引提升效率。

比如说我要查每个专业有多少人，返回专业名称和人数

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

执行结果

在没有建立索引的情况下，使用到了临时表，效率比较低，就可以建立索引。

create index pro_index on tb_user(profession);

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

1. 在分组操作时，可以通过索引来提高效率。
2. 分组操作时，索引的使用也是满足最左前缀法则的（针对联合索引）

5. limit优化

在数据量比较大的时候，如果进行limit分页查询，在查询的时候，越往后，分页查询效率越低。

因为在分页查询时，如果执行了limit 10 offset 2000000，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大

select * from user  limit 10 offset 2000000;

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

select * from user as a,(select id from user  limit 10 offset 2000000) as b where a.id=b.id;

6. count优化

当数据量比较大的时候，在执行count函数的时候也是比较耗时的

select count(*) from user;

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候回直接返回这个数，效率很高，但是如果带条件的count，MyISAM也是很慢的
• InnoDB引擎就不一样，它在执行count(*)的时候，需要把数据一行一行的从引擎里面读取出来，然后累计计数。

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法：

• count(*)：InnoDB 并不会把全部字段取出来，而是专门做了优化，不取出值，在服务层直接按行来进行累加
• count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id都取出来，返回给服务层，服务层拿到主键之后，直接按行进行累加(因为主键不能为null的)
• count(字段)
  • 字段没有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
  • 字段有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
• count(数字)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

**按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。 **

7. update 优化

如下图，两个事务对同一张表的不同行的数据进行修改，发生了阻塞，从而影响性能。

这一条sql 的where条件是一个普通字段，而InnoDB针对的是索引加锁，并不是对记录加锁，而且索引页不能失效，否则就会从行锁升级为表锁。

update tb_user set age=30 where name='姜子牙';

所以使用update的时候where条件建议使用索引字段，比如使用主键，避免锁升级从而影响并发下的性能

update tb_user set age=30 where id=24;

---