MYSQL的SQL语句优化分析
1、大批量的SQL插入优化
当用load命令导入数据的时候，适当的设置可以提高导入的速度
1.1、MyISAM引擎中的设置
对于MyISAM引擎的表，可以通过以下方式快速地导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;
DISABLE KEYS和ENABLE KEYS用来打开或者关闭MyISAM表非唯一索引的更新。在导入大量的数据到一个非空的MyISAM表时，可以通过设置这两个命令，可以提高导入的效率。对于导入大量的数据到一个空的MyISAM表时，可以不用设置。
1.2、InnoDB引擎中的设置
对于InnoDB类型的引擎，可以使用以下几种方式提高导入的效率
1）因为InnoDB类型的表时按照主键的顺序保的，所以将导入的数据按照主键的书序排列，可以有效地提高导入数据的效率。
2）在导入前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
3）如果应该使用自动提交方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后在执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
4） 调整插入shuju缓存大小
如：
set session bulk_insert_buffer_size = 256217728
5）禁止更新索引
如：
set global key_buffer_size = 256217728 alter table <tablename> disable keys
2、优化INSERT 语句
1、如果同时从一个mysql客户端插入很多行，应尽量使用多个值表的INSERT语句，这种方式可以将大大缩减客户端与数据库之间的连接，关闭等消耗。
如：
INSERT INTO test VALUES(1,2),(1,3),(1,4)....
2、
3、将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）
4、如果批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对MyISAM表使用。
5、当从一个文本文件装载一个表时，使用LOAD_DATA_INFILE，这通常比使用很多INSERT语句块20倍。
6、 从一个表导入另外一个表（部分数据，或者全部数据），这个速度也是很快的。
insert into <tablename1>(a1, b2, c3) select a11, b22, c33 from <tablename2> where <...>
3、ORDER BY语句优化
尽量减少额外的排序，通过索引直接返回有序的数据
两种排序方式：
order by的排序有两种方式：第一种依靠索引的排序性实现，是最佳的实现方式；第二种是在语句获取到目标数据后，对此时无序的数据进行排序。
索引的排序性
mysql中有两种排序方式：第一种是通过有序索引顺序扫描直接返回有序数据，这种方式在使用explain分析查询的时候显示为Using Index，不需要额外的排序，操作效率较高，例如：
以上排序利用的是id作为自增主键的特性，以及索引自带的排序性质，所以可以直接快速的得到排序结果。
需要说明一下，上面的表中id上有两个索引，一个是主键索引，一个id的非主键索引，由于非主键索引针对查询速度快读，所以优化器选择了非主键索引（覆盖索引）
除了上面的简单索引举例以外，在联合索引中也存在这个特性，因此在排序的时候，最好利用索引的特性。
对返回数据进行排序
对返回的数据进行排序，也就是常说的FileSort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。FileSort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或者临时表等，取决于MySQL服务器对排序参数的设置和需要排序的数据的大小。
示例一：
注意：以下实例使用了*，实际使用时不推荐使用，因为*代表全部字段，全部字段使用的内存多，如果不是实际需要，就太浪费内存了。
如下：按照uuid排序返回的数据，出现了全表扫描
cmd_log一共有9976表记录，rows是9719，也就是9719是个估计值。possible_keys为空，key为空表示为无索引，需要在数据获取后进行排序。
针对数据id，做一个过滤的优化
通过where先过滤了一次数据，后面排序的数据就少很多
示例二：联合索引
FileSort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，他就会将磁盘上的数据进行分块，在对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以同一个时刻，mysql中存在多个sort buffer排序区。
FileSort优化
通过合适的索引能够减少FileSort出现，但是在某些情况下，条件限制不能让FileSort消失，那就只能想办法加快Filesort实现。对于Filesort有两种方式，可以加快排序序
1、两次扫描算法
2、一次扫描算法。一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法高。
4、GROUP BY 优化
默认情况下，MySQL对所有的GROUP BY col1, col2, col3 ..... 的字段进行排序。这与在查询中指定ORDER BY col1, col2, col3 ... 类似。因此如果显示的包括一个包含相同列的ORDER BY子句，对MYSQL的实际执行性能是没有影响的。
如果查询包含GROUP BY，但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序，如下面的例子：
默认GROUP BY
禁止对GROUP BY 排序
通过EXPLAIN解析后得到的执行流程可知，以上两个句子执行的大部分流程一样，但是默认情况多了一个using filesort，而下面的没有Using filesort，而filesort往往比较耗时，因此查询速度更快。
5、子查询优化
6、MYSQL优化OR条件
对于含有OR的查询子句，如果要用到索引，则OR之间的每个条件列都必须有单独的索引，如果没有索引，则应该考虑到增加索引。
6.1、OR的优化实现
用or分割的条件，如果or的前后列，有一个没有索引，则会执行全盘扫描
OR的最佳用法：OR中的条件全部都有索引
例如有如下表和索引：
使用OR查询分析
分析以上查询过程可知，在正确的情况下，MySQL在处理含有OR子句的查询时，实际是对OR的各个字段分别查询后的结果进行了UNION操作。
以上的type=index_merge对应了Extra中的Using union(PRIMARY , idx_create_time);
OR的未优化使用
在没有为or的每个字段添加索引时，执行OR会转变成全盘扫描
如下所示，or并不是每个字段都有索引：
执行OR查询转变成了全盘扫描：
6.2、OR与AND的执行优先级
在where中可以包含任意数目的and和or操作符，在没有任何其他符号的时候，例如括号，SQL会首先执行and条件，然后才执行or语句，如：
select * from table from id=1 or id=2 and price>=10;
这条语句默认执行的是id=2并且price大于等于10的，或者是id=1。
如果加上括号：
select * from table from (id=1 or id=2) and price>=10;
则这条语句执行的是id=1或id=2,并且price大于等于10。
浙公网安备 33010602011771号