SQL优化详解

目录

·插入数据

insert的优化（少量数据）

批量插入

手动事务提交

主键顺序插入

插入大量数据

·主键优化

数据组织方式：

页分裂：

主键顺序插入的方式：

主键乱序插入：

页合并：

主键设计原则：

·order by优化

·group by 优化

·limit优化

优化方式：

·count优化

count的用法：

几种用法的优劣：

count（主键)

count(字段)

count ( 1 )

count (* )

·update优化

---

·插入数据

插入多条数据的优化方案：（插入一条没啥优化的哈）

insert的优化（少量数据）

批量插入

像这种模式，一般建议数据量最多在500-1000条左右。

Insert into tb_test values(1,'To'),(2,'WO'),(3,'jee');

手动事务提交

因为每句insert的执行都会涉及事务的提交，执行的越多越占用时间，为此可以进行手动事务提交，

start transaction;

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');

insert into tb_test values(7,'Tom'),(8,'Cat'),(9.'Jerry');

commit;

主键顺序插入

一般而言主键顺序插入还是乱序插入都可以，建议主键顺序插入

乱序：2 5 22 86 34 8987 213

顺序：1 2 3 4 5 6 7 8 9

插入大量数据

一次性插入大量数据，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';

插入1000000的数据需要16s，如果是使用insert大概需要十几分钟。

·主键优化

数据组织方式：

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

为什么都是根据主键顺序存放的呢：

数据库的数据是根据表空间，段，区，页，行的形式存放的，行就是每一条数据，表是InnoDB管理的最小单位。数据的索引分为聚集索引和二级索引，聚集索引使用的是B+树，它的每个主键都会在树的最下面存放，而前面的向上分裂的部分作为查询数据的索引。所以表的数据都是根据主键顺序组织存放的。

页分裂：

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。

主键顺序插入的方式：

就从前往后插数据，第一个页写满了，就写入第二个页，然后用一个双向指针维护两个页的关系。

主键乱序插入：

如果是这种情况，第一个页写满了，但是要插入一个id为50的数据，这个时候怎么办呢？

他会新建一个数据页，然后把第一个页分一半出来，把50插入到新的数据页中

然后把链表指针改变一下，把1号的下一个改为3号，3号的next改为2号。

所以：顺序插入的效率最高。

页合并：

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

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%)，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

主键设计原则：

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

因为二级索引的最下面存放都是主键，主键太长，就会占用大量的IO和磁盘。

2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

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

两个原因：无序且过长

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

·order by优化

在mysql中有两种排序方式

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

2、Using index :通过有序索引顺序扫描直接返回有序数据，这种情况即为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,phgne);
#创建索引后，根据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 ;

创建了联合

·group by 优化

分组操作的研究中，我们主要研究索引对分组操作的影响。

这是没有索引的查询效率：

下面

是创建索引后的性能：

建立适当的索引，满足最左前缀法则即可提高分组效率。

如果查询时是这样，pro在where，而age在group by也是满足最左前缀法则的。

·limit优化

什么时候要考虑limit进行优化呢：

select * from sb_tu limit 0,10;

第0条数据开始，查10个数据

select * from sb_tu limit 100000,10;

从第100000个数据开始查10个数据。

越往后查询的代价越大，他会把0到100000个数据全部排序，然后丢弃，只返回100000到100010这10个数据。

优化方式：

覆盖索引加子查询

1：先查出想要的id

select id from tb_sku order by id limit 100000,10;

2：表联查查数据

select s.* from tb_sku s , (select id from tb_sku order by id limit 100000,10) a where s.id = a.id;

原方法耗时19s，优化后耗时11.47s。

·count优化

select count(*) from tb_sku;

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。如果有where条件就需要一行一行的读取了。

InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

目前由于存储引擎的缘故，没有太好的优化count的方法，常用的就是使用像redis之类的数据库进行自我计数。

count的用法：

count(*)，count(主键)，count(字段)，count(1)；

count(主键)统计有多少个主键。一般为数据库表的总数。

count(字段)是查询该字段不为空的总数，如果为空则不计入。count(*)同理。

count(1)是给每个字段添加一个1的标识，然后统计1的数量。

几种用法的优劣：

count（主键)

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

count(字段)

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

count ( 1 )

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

count (* )

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

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

·update优化

我们在更新数据时一定要根据id进行更新，因为在事务中使用id索引进行update用的是行级锁，该行在事务未commit之前不会再被其他人修改。

如果使用字段进行字段进行查询则会被开启表级锁，整张表在该事务未提交之前不会再被修改，会影响其他人操作。

如果字段有建立索引表则不会被添加表级锁，而是行级锁。