SQL优化相关

---

SQL优化

1. 数据插入

当我们必要插入多条数据时间，发起使用批量插入，由于每次插入数据都会实验一条SQL，都要举行网络传输涉及到TCP建立连接这么一个过程，所以发起使用批量插入
不推荐写法

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

复制代码

发起使用批量插入

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

复制代码

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

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

复制代码

受到MySQL索引布局的影响，对于主键的插入发起要次序举行插入
插入大批量数据，插入大批量数据不发起使用insert语句，发起使用load，指定从当地文件中读取，指定一条数据列之间的分隔符以及每一条数据的分割付，如下

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

复制代码

部分文件数据：

表布局

1. mysql> desc test;
2. +--------+-------------+------+-----+---------+-------+
3. | Field  | Type        | Null | Key | Default | Extra |
4. +--------+-------------+------+-----+---------+-------+
5. | id     | int(11)     | YES  |     | NULL    |       |
6. | name   | varchar(32) | YES  |     | NULL    |       |
7. | school | varchar(32) | YES  |     | NULL    |       |
8. | info   | varchar(32) | YES  |     | NULL    |       |
9. | time   | datetime    | YES  |     | NULL    |       |
10. +--------+-------------+------+-----+---------+-------+

复制代码

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举行升序排序

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

复制代码

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

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

复制代码

建立联合索引后此时效率就进步了，酿成了Using index,但如果按age升序排phone降序排序，末了这就不行了

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

复制代码

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

1. 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 优化

在对查询语句举行分组操作时，也可以建立索引提拔效率。
比如说我要查每个专业有多少人，返回专业名称和人数

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

复制代码

实验结果

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

1. create index pro_index on tb_user(profession);

复制代码

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

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

5. limit优化

在数据量比较大的时间，如果举行limit分页查询，在查询的时间，越往后，分页查询效率越低。
由于在分页查询时，如果实验了limit 10 offset 2000000，此时必要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大

1. select * from user  limit 10 offset 2000000;

复制代码

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

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

复制代码

6. count优化

当数据量比较大的时间，在实验count函数的时间也是比较耗时的

1. 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针对的是索引加锁，并不是对记录加锁，而且索引页不能失效，否则就会从行锁升级为表锁。

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

复制代码

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

1. update tb_user set age=30 where id=24;

复制代码

---

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。