SQL优化

1. 插入数据SQL优化

• insert优化

1. 批量插入（一次性插入多条数据）

insert into tb_user values(1,'TOM'),(2,'Mike')....
1

2. 手动事务提交

start transaction
insert into tb_user values(1,'TOM'),(2,'Mike')....
insert into tb_user values(1,'TOM'),(2,'Mike')....
commit;
1
2
3
4

3. 主键顺序插入

主键顺序插入会比乱序插入的效率高

• 插入大批量数据

如果一次性大批量插入数据，使用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
7
8

操作案例

1. 进入数据库，查看local_infile参数设置（0是没有打开)

select @@local_infile;
1

2. 开启local_infile 参数

set global local_infile=1;
1

3. 执行命令加载就行

2. 主键优化

在讲insert优化的时候，说到主键顺序插入的时候，会比乱序插入效率更高，这里就可以解释底层原理

• 数据组织方式

在innoDB存储引擎中，表数据都是根据主键顺序组织存放（聚集索引底层原理）的，这种存储方式的表称为索引组织表(index organized table IOT)，下图是InnoDB底层存储逻辑结构。

• page分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包括2-N行数据（如果一行数据多大，会行溢出），根据主键排列。

又上面可以看出，主键顺序插入的时候，当一个页的容量不够时，会开辟新的页然后将继续插入（页与页之间会有双向联系），下面演示主键乱序插入

上图中，page1和page2已经插满了数据，现在继续插入id为50的数据，由于主键顺序排列的关系，50行是不能直接插入到page3的，而只能插入到47之后，但47之后已经没有位置了，此时数据库会找到page1的50%位置，然后把后半部分数据，放到page3中，接着50放入47后面

最后重新设计页表指针，这就是页分裂现象 ，这也解释了为什么主键顺序插入效率会高

• 页合并

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

• 主键设计原则

1. 满足业务需求的情况下，尽量降低主键的长度
2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMNET自增主键
3. 尽量不要使用UUID或者是其它自然主键，如身份证号作为主键（因为它们是无序的或长度较长）
4. 尽量不要修改主键

3. order by 排序优化

对于Mysql中的排序有两种方式：

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

这里测试使用到了这篇文章的数据库（可以创建一下）

按照年龄排序查询用户信息

select 用户ID,姓名,年龄 from 用户表 order by 年龄;
1

-- 查看一下性能分析
explain select 年龄 from 用户表 order by 年龄;
1
2

可以发现使用的是filesort排序方式，因为age字段没有索引，效率是比较低的，下面给age创建索引，这时候再排序就是用index排序了

create index  idx_age on 用户表(年龄)；
1

• order by优化规则

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2. 尽量使用覆盖索引
3. 多字段排序，一个升序一个降，此时需要注意联合索引创建时的规则(ASC/DESC)
4. 如果不可避免的出现filesort，大数据量排序时，可以适当增加排序缓冲区大小sort_buffer_size（排序缓存区，默认256K，超过这个大小会在磁盘文件中进行排序，效率很低)

show variables like 'sort_buffer_size'
1

4. Group by优化

操作之前删除所有索引

执行一个分组操作

 select 城市,count(*) from 用户表 group by 城市;
1

 explain select 城市,count(*) from 用户表 group by 城市;
1

using temporary发现查询使用到了临时表（这样的效率是很低的），下面创建联合索引

create index idx_city_name_age_sex on 用户表(城市,姓名,年龄,性别);
1

再次执行分组操作,发现使用了索引

 explain select 城市,count(*) from 用户表 group by 城市;
1

• group by 优化原则

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

5. Limit优化

首先执行分页操作

select * from 用户表 limit 0,10;
1

对于limit来说，一个常见非常头疼的问题是limit 2000000,10，此时需要mysql排序前200010记录，仅仅返回2000000-2000010的记录，其它记录丢弃，查询排序的代价非常大，这里可以使用覆盖索引+子查询的方式进行优化

6. count优化

explain select count(*) from 用户表;
1

在MyISAM存储引擎中，一张表的总行数存储在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，InnoDB就麻烦了，它执行count(*)时，需要把数据一行一行地从引擎里面读出来，然后累积计数，所以这里的优化思路只能是：自己计数(例如每插入一条数据我们可以维护一个变量来加1)

• count的几种用法

1. count()是一个聚合函数，对于返回的结果集，一行行判断，如果count函数的参数不是null，累积值就加1，最后返回累积值
2. 用法：count(*)、count(主键)、count(字段)、count(1)
3. count(主键)：innoDB引擎会遍历整张表，把每一行的主键id都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）
4. cout(字段值)：如果没有not null 约束，innoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。如果有not null 约束，innoDB存储引擎会遍历每一行的字段值都取出来，返回给服务层，直接按行进行累加
5. count(1)：innoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行都会放一个数字1进去，直接按行进行累加
6. count(*)：innoDB引擎不回把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

7. Update优化

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