第三章：SQL优化【mysql数据库-进阶】

1.插入数据优化

1.1 批量插入

Insert into tb_ _test values(1,'Tom'),(2,'Cat'),(3,Jerry');
1

1.2 手动提交事务

因为每次插入一条数据就会自动提交事务，频繁的提交事务，会浪费很多时间。

start transaction;
insert into tb_ _test values(1,'Tom'),(2,'Cat)(,erry');
insert into tb_ test values(4,'Tom),(5,'Cat)(6,lerry');
insert into tb_ _test values(7,'Tom'),(8,'Cat'),(9,Jerry'); 
commit;
1
2
3
4
5

1.3 主键顺序插入

顺序插入的效率比乱序插入的效率更高。

主键乱序插入:819 21 88 24 15 89 5 7 3
主键顺序插入:1 2 3 4 5 7 8 9 15 21 88 89
1
2

1.4 大批量插入数据

如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。操作如下:

主要有以下三个步骤：

# 客户端连接服务端时，加，上参数--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' ;
1
2
3
4
5
6

导入数据脚本

查看一下这个数据的脚本信息：

往数据库中插入数据：

注意：使用此方法插入数据，也需要按照主键顺序插入，这样效率更高

2.主键优化

2.1 数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table I0T)。

在这个B+树的结构中，所有数据都存在叶子节点，父节点只是作为逻辑存储单元，这些存储单元全部存放在页中。

2.2 页分裂

页可以为空。也可以填充一半, 也可以填充100%。每个页包含了2-N行数据(如果一行数据多大, 会行溢出),根据主键排列。
主键顺序插入的情况：

主键乱序插入：
加入两个页已经插满了，但是又要插入一个新的数据。

因为叶子节点是有序的，因此50 那就应该加入47之后，此时就会将第一个页从中间分割开，然后将分开的左边与50放到一个新的页中。

然后将页进行移动变的有序。

这个过程称之为页分裂现象。

2.3 页合并

当删除一行记录时，实际上记录并没有被物理删除,只是记录被标记(flaged) 为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_ THRESHOLD ( 默认为页的50%)，InnoDB会开始寻找最靠近的页( 前或后)看看是否可以将两个页合并以优
化空间使用。

然后新的数据就会往3号页插入数据。

【小贴士】
MERGE_ THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引的时侯指定。

2.4 主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度。
  
  因为数据库中很多二级索引，如果主键的长度太长了，则会浪费很多存储空间。

• 插入数据时，尽量选择顺序插入，选择使用AUTO_ INCREMENT自增主键。

• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

• 业务操作时，避免对主键的修改。

3.order by 优化

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

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

两种排序查询返回的排序结果是Using filesort,因为phone和age没有建立索引。
于是给二者建立一个联合索引，查看一下查询的结果：

create  index  idx_user_age_phone on tb_user(age,phone);
explain select id,age,phone from tb_user order by age,phone;
1
2

这个时间返回的排序结果为Using index。

那么如果进行下面的操作，结果是怎么样的：

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

因为在创建索引的时候先按照age升序排列，然后再按照phone升序排列，所以倒序排列的时候就需要进行反向扫描索引。

如果将phone和age的顺序交换的时候会怎么样呢？

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

会出现Using filesort，违背了最左前缀法则。
如果按照age 升序，phone降序排列会出现什么情况呢？

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

还是出现了Using filesort，因为在创建索引的时候，默认都是按照升序进行排列的。查看索引的时候，collation中是A就是表示升序排列。

如果想要解决这个问题，可以重新创建一个索引，将age升序排列，phone降序排列。

-- 创建索引
create index idx_user_age_phone_ad on tb_user(age asc,phone desc);
-- 再次查询
explain select id,age,phone from tb_user order by phone ASC,age DESC;
1
2
3
4

此时就使用了索引，两种索引排序方式的数据结构如下所示。

注意：所有的排序都满足一个条件，就是覆盖索引。
【注意】

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• 尽量使用覆盖索引。
• 多字段排序, -一个升序-一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC) 。
• 如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort_ _buffer. _size(默认256k)。

查看排序缓冲区大小的方法：

show variables  like 'sort_buffer_size';
1

4.group by 优化

首先删除所有的索引，查看没有索引下的group by 有什么影响。

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

查询结果显示只用了临时表，没有使用任何的索引，使用的是全表查询。
于是创建一个联合索引：

create index idx_user_pro_age_sta on tb_user(profession,age,status);
explain select profession,count(*) from tb_user group by  profession;
1
2

此时就使用了索引进行了分组查询。
如果只使用age查询呢？

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

此时使用了临时表，因为违背了最左前缀法则，效率并不高。
如果同时使用了professor和age呢？

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

此时满足了最左前缀法则，则使用了索引。
如果以professor作为条件，age作为分组查询的条件，会使用索引吗？

explain select profession,age,count(*) from tb_user where profession='软件工程' group by  age;
1

此时满足最左前缀法则，则使用了索引了。

• 在分组操作时，可以通过索引来提高效率
• 分组操作时,索引的使用也是满足最左前缀法则的。

5.limit优化

假设要进行一个分页查询：

-- 查询第一页
select * from tb_sku limit 0,10;
-- 查询第二页
select * from tb_sku limit 10,10;
1
2
3
4

此时的查询时间只用了77ms
但是如果从1000页，10000页开始查询的时候：

select * from tb_sku limit 10000,10;-- 986ms
select * from tb_sku limit 100000,10;-- 8s
1
2

此时的查询的时间就很长了，10000页要8秒。
因此查询的效率很低，就需要 `覆盖索引查询 和 子查询 来优化sql。
一个 常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录,仅仅返回2000000 - 2000010的记录，其他记录丢弃,查询排序的代价非常大。
比如只对id进行查询：

-- 首先通id进行查询
select id from tb_sku limit 100000,10;-- 88ms
-- 然后id作为子查询的条件进行查询
select s.* from tb_sku s,(select id from tb_sku limit 100000,10) a where s.id=a.id; -- 69ms
1
2
3
4

此时的查询时间只要69ms，因为使用了id的索引，所以查询效率被大大的优化了。

6. count优化

6.1 不同引擎count计数方法

• MylSAM引擎把一个表的总行数存在 了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高;
• InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一-行地从引擎里面读出来，然后累积计数。
  优化范式：自己计数

6.2 count的几种方法

• ount()是一个聚合函数,对于返回的结果集，一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加，最后返回累计值。
• 用法: count (*)、count (主键)、count (字段)、count (1)

count（1）：数据库表所查询的记录返回值都会放一个1进去，如果返回值为1不为null累积+1

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

7. update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

8.总结