Mysql优化策略

目录

 

一、实践中如何优化SQL

1.1 插入数据时以主键顺序插入

1.2  优化insert语句

a.多条分开的insert语句合并成一条sql语句

b.如果多个客户端在同时插许多条数据，使用 insert delay 代替 insert (只用在Myisam)

1.3 order by 语句优化

a.  select 时不要用 * ，改用具体字段

b. 排序时按照索引的顺序，就不会出现Using filesort

c. 升降序不一致,无法利用索引排序

Filesort的两种排序方法

1.4 group by语句优化

1.5 优化子查询（用多表联查代替子查询）

1.6 优化分页查询 

优化思路1：在索引上完成排序操作

优化思路2：仅仅使用于主键自增的表

---

一、实践中如何优化SQL

1.1 插入数据时以主键顺序插入

因为InnoDB底层的索引用的是B+树，而B+树的节点索引值如果不按顺序插入的话，就会导致频繁分裂，这会很耗费性能的。

所以在平时的开发过程中，如果没有特别的业务需要，应该尽可能的使用自增列作为主键。

 

1.2  优化insert语句

a.多条分开的insert语句合并成一条sql语句

如果需要对一张表同时插入多行数据，应该尽量使用一条sql语句进行插入，避免用多条sql语句插入多行数据，这样可以大大地缩减客户端与数据库之间的连接，关闭等消耗。使得效率比分开执行的单挑insert语句高。

例如：

优化后：

mysql客户端与服务端交互的详情：https://www.cnblogs.com/zzl-156783663/p/8506488.html

b.如果多个客户端在同时插许多条数据，使用 insert delay 代替 insert (只用在Myisam)

利：客户端马上返回，数据排成一队；数据整齐的先写到一个内存块里，而不是分散。当表不被其他线程使用时，再把数据真正插入到表中。

弊：待插的数据放在内存里，一旦数据库被意外终止（如kill -9），那么数据会丢失。

 

1.3 order by 语句优化

在Mysql中，有两种排序方式：

1. filesort：通过文件系统进行排序，filesort是不走索引的，所以排序效果很差。

2. using index：通过索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高。

详情：https://www.cnblogs.com/developer_chan/p/9225638.html 

简要归纳一下：

先假设一张表：

并且创建如下索引：

即c1，c2，c3，c4作为一个联合索引。

a.  select 时不要用 * ，改用具体字段

可以看到 select*是走 Uisng filesort的，解决方法把 * 改成具体字段就行：

b. 排序时按照索引的顺序，就不会出现Using filesort

由于表的索引的顺序是 c1、c2、c3、c4。所以order by后面要跟按这个顺序

反例：

order by 后面使用了c2，没了c1，所以排序时出现 Using filesort。

c. 升降序不一致,无法利用索引排序

ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。

Filesort的两种排序方法

通过建立合适的索引，能减少Filesort的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快Filesort的操作，对于Filesort，Mysql有两种算法：

1. 两次扫描法(双路排序)：

首先根据条件取出 排序字段 和 行指针信息 ，之后在排序区sort buffer中排序。这种排序算法需要访问两次数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，第二次读取操作可能会导致大量随机I/O操作。优点是排序的时候内存开销较小。这种方法需要两次扫描磁盘，最终得到数据。即从磁盘读取排序字段(order by的字段)，在buffer进行排序，再从磁盘取其他字段。

如果使用两次扫描法，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

（sort buffer是在内存里的。）

2.一次扫描法(单路排序)：

一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。

单路排序会出现的问题：

如果读取的数据大于sort buffer，就会出现多次读取数据，这反而会增加IO操作的次数。

解决方法：

1.增大 sort_buffer_size参数的设置。

2. 调整max_length_for_sort_data参数的设置。

MySQL根据max_length_for_sort_data变量来确定使用哪种算法，默认值是1024字节，如果需要返回的列的总长度大于max_length_for_sort_data，使用双路排序，否则使用单路排序。

1.4 group by语句优化

group by不同于 order by的地方是，group by不仅排序，还会分组。

group by 在没有使用索引的时候，需要创建临时表和排序。在执行计划中通常可以看到“Using temporary; Using filesort”。建立临时表是十分消耗资源的。所以优化策略就是要取消临时表。

解决方法：通过建立索引。例如 索引是 x字段，那么 可以group by x 。

为什么加索引可以取消临时表：https://www.cnblogs.com/jimmyhe/p/11184230.html

 

1.5 优化子查询（用多表联查代替子查询）

看两条SQL语句：

第一条是子查询，第二条是多表联查。

第一条的type是index，第二条的type是ref，ref的效率比index高

 

1.6 优化分页查询 

分页查询语句例子：假设我们现在有一张表 table：

select * from table limit 10    ----------表示查询table表的第一页，每页10条记录(即最终返回10条记录)；

select * from table limit 10，10  - - - - - 表示查询table表的第二页，每页10条记录；

select * from table limit  2000000，10 - - - - -表示查询table表的第200w零一页，每页10条记录；

limit 后面的数目越大，查询速度越慢，因为例如 limit 在分页的时候，还要对主键进行排序操作，所以limit 后的数字很大的时候，例如 limit 2000000，10 是对排序了前2000010记录，才取2000000-2000019的记录，其他记录丢弃，查询排序的代价很大。

优化思路1：在索引上完成排序操作

步骤：

1.先用 主键id 进行排序： 

select id from table order by id limit 2000000，10

2.得到排序后的id后，再取数据：

select * from table t,(select id from table order by id limit 2000000，10) a where t.id=a.id

优化思路2：仅仅使用于主键自增的表

（这种情况只使用于 主键已经是自增 的表）

那么直接：

select * from table where id>2000000 limit 10

二、什么情况下索引会失效