mysql语句优化的几种方法

概述

分享一下之前笔记记录的一些不同类型sql语句优化方法，针对mysql。

主要分成优化ｉｎｓｅｒｔ语句、优化ORDER BY语句、优化GROUP BY 语句、优化嵌套查询、优化OR语句这几个方面，可能写的不是很全，大家有啥好想法可以在下方留言哦。

优化ｉｎｓｅｒｔ语句

当进行数据ｉｎｓｅｒｔ时，mysql客户端大致要经过的步骤如下：

1、客户端连接mysql服务器

2、客户端发送ｉｎｓｅｒｔ语句到服务器

3、服务器解析ｉｎｓｅｒｔ语句

4、服务器增加数据

5、服务器给增加的记录添加索引

6、服务器关闭连接

优化ｉｎｓｅｒｔ的常用方法：

1、如果从同一客户端插入很多行数据到mysql服务器，一次性插入多个值将大大缩短客户端与数据库服务器之间的连接和关闭等操作，如：

ｉｎｓｅｒｔ into books values (1,‘book1‘),(2,‘book2‘),(3,‘book3‘);

当从一个文本文件装载一个表时，使用LOAD DATA INFILE加载数据往往比使用很多ｉｎｓｅｒｔ语句效率至少提高20倍。

2、对于myisam类型的表，如果从不同客户端插入很多行，可使用ｉｎｓｅｒｔ DELAYED语句提升执行速度。

ｉｎｓｅｒｔ DELAYED INTO 是客户端提交数据给mysql服务器，mysql服务器返回ok状态给客户端，而这并不是将数据立即执行插入到表，而是存储在内存里面等待排队，直至mysql服务器有空闲时再插入。

3、锁定表以加速插入数据。

lock tables test write;

ｉｎｓｅｒｔ into test values (3,‘t3‘),(4,‘t4‘);

unlock tables

如果不加锁定表，每一次执行ｉｎｓｅｒｔ语句完成后，索引缓冲区都会被写到磁盘上，而加入锁定后索引缓冲区仅被写到磁盘上一次。

优化ORDER BY语句

1、对ORDER BY + LIMIT 组合的索引优化，sql形式 如下：

ｓｅｌｅｃｔ [column1] ... FROM [TABLE] ORDER BY [sort ] LIMIT [offset ],[LIMIT];

优化：在[sort]上建立索引。

2、对WHERE + ORDER BY +LIMIT组合的索引优化，sql形式 如下：

ｓｅｌｅｃｔ [column1] ... FROM [TABLE] WHERE [columnX]=[value] ORDER BY [sort ] LIMIT [offset ],[LIMIT];

此时如果对[sort]添加索引，效率不是很高，可采用更高效的方法建立一个联合索引（columnX,sort）

3、不要对where和order by的选项使用表达式或者函数，sql形式如下：

ｓｅｌｅｃｔ * FROM [TABLE] ORDER BY YEAR(date) LIMIT 0,30;

下面几种情况不应该使用索引：

1、order by的字段混合使用asc和desc

ｓｅｌｅｃｔ * from ordertable order by col1 desc ,col2 asc;

2、where子句使用的字段和order by的字段不一致

ｓｅｌｅｃｔ * from ordertable where col1=1 order by col2;

3、对不同的关键字使用order by排序

ｓｅｌｅｃｔ * from ordertable order by col1,col2;优化GROUP BY 语句

使用group by语句时，mysql会对符合的结果自动排序，通过扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累计行数。在某些情况下，mysql可以通过索引访问而不用临时表。

通过制定order by null可以禁止排序，从而节省耗损。

#没有优化

explain ｓｅｌｅｃｔ id,count(data) from test group by id;

#使用order by null优化

explain ｓｅｌｅｃｔ id,count(data) from test group by id order by null ;优化嵌套查询

mysql从4.1版本开始支持子查询，使用子查询可进行ｓｅｌｅｃｔ语句的嵌套查询，即一个ｓｅｌｅｃｔ查询的结果作为另一个ｓｅｌｅｃｔ语句的条件。执行子查询时，mysql需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后，在撤销这些临时表，因此子查询速度会受到一定影响。

在mysql中可以使用连接（JOIN）查询来替代子查询，连接查询不需要建立临时表，其速度比子查询快，如果查询中使用索引的话，性能会更好。连接之所以更有效率，是因为mysql不需要在内存中创建临时表来完成查询工作。

子查询：

explain ｓｅｌｅｃｔ * from emp where dept_id not in (ｓｅｌｅｃｔ id from dept);

连接:

explain ｓｅｌｅｃｔ * from emp left join dept on emp.dept_id=dept.id where emp.dept_id is null;优化OR语句

对于使用OR条件的子查询，如果要使用索引，则OR之间的每个条件列都必须使用索引。

explain ｓｅｌｅｃｔ * from ortest where data1=2 or data2=3;create index idx_data1 on ortest(data1);create index idx_data2 on ortest(data2);

#创建索引后，mysql在处理包含or子查询中，是对or的各个字段在查询结果之后在进行UNION操作。

explain ｓｅｌｅｃｔ * from ortest where data1=2 or data2=3;

平时都是想到哪写到哪，所以可能会比较杂，大家有什么好的想法可以提一下哦，一起交流

后面会分享更多DBA方面内容，感兴趣的朋友可以关注下！