MySQL性能优化之SQL语句优化

前言

这个话题其实难度蛮大的，因为不但需要理解一些Mysql的专业知识，同时还需要长时间的观察统计，以及大量的使用经验

之前在某项目中，服务端发生过两起关于Mysql的频繁大量抛错的事件，一个是抛出Mysql异常" too many connections", 另一个是抛出Mysql异常 " open too many files",这促使我开始重视起Mysql的性能优化。

优化目标

1.减少 IO 次数

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

2.降低 CPU 计算

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

优化方向

1.SQL语句及索引的优化

2.数据库表结构的优化

3.系统配置的优化

4.硬件的优化

SQL语句及索引的优化

1.如何发现有问题的SQL语句？

（1）使用mysql慢查询日志监控有效率问题的SQL语句

检查满查询日志是否开启

mysql> show variables like '%slow%';

（2）开启慢查询日志

开启

mysql> set global slow_query_log='ON';

设置记录慢查询的时间阀值

mysql> set global long_query_time=0.1;

即超过100ms的查询就认为是慢查询

（注：slow_launch_time 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加）

记录没有使用索引的查询

mysql> set global log_queries_not_using_indexes=on;

开启慢查询日志的操作也可以在MySQL的配置文件my.cnf中添加以下两行：

log-slow-queries=/var/run/mysqld/mysqld-slow.log

long_query_time=0.1

（3）使用慢查询日志分析工具：mysqldumpslow或mysqlsla

mysqldumpslow是官方提供的，在安装Mysql时就已经自带了，使用方法请自行输入命令 mysqldumpslow --help查看。

mysqlsal是hackmysql.com推出的，功能非常强大，请自行google其安装步骤和使用方法。

2.使用EXPLAIN分析SQL查询语句

例如：mysql> explain select * from file;

mysql> explain select * from file where fid > 0;

比较以上两条语句的执行结果。

EXPLAIN语句返回结果中列的解释：

table：这行数据的所属表的表名字

type：显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

const：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref：MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref：只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range：使用索引返回一个范围中的行，比如使用>或

index：对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL：对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢，会创建临时表或文件来存储结果，造成大量的IO操作，影响效率

3.count() 和 max()的优化

（1）max()优化

max(A) 给max函数里的字段A添加索引

mysql> create index idx_A on table B(A)

此时若用 EXPLAIN语句再次分析，会在Extra列中看到如下返回：

表示不需要查询，只需要通过索引就可以找出查询结果，这条索引叫覆盖索引，就是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

（2）count()优化

在不加where限制条件的情况下， count(*)与 count(COL)基本可以认为是等价的；

但是在有where限制条件的情况下， count(*)会比 count(COL)快非常多；

count(*)通常是对主键进行索引扫描，而 count(COL)就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的列的纪录数。另外，count(*)会包含NULL值

4.子查询优化

通常情况下需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，注意重复数据：

select tid from t1 where tid in (select id from t);

改成

selectdistinctt1.tid from t1 join t2 on t1.tid = t.id;

5.limit优化

limit常用于分页处理，会伴随order by 语句，因为会使用FileSorts 这样会造成大量的IO问题

优化1：使用有索引的列或主键进行order by操作

不使用索引列的语句：

使用索引列后的语句：

对比以上两条语句可以看到type从ALL变成了index。

但随着翻页越来越往后，扫描的行数会越来越多， 实际上响应速度会越来越慢

查询第一页：

查询第N页

对比以上两条，可以看到扫描的行数明显增加了。因此需要进一步优化。

优化步骤2：

记录上次返回的主键，在下次查询的时候使用主键过滤 （避免数据量大时扫描过多的记录）

可以看到扫描行数明显减少。

6.选择合适的列建立合适的索引

（1）在where从句，group by从句， order by从句， on从句中出现的列

（2）索引字段越小越好（数据库存储是以页为单位的，一页里能存越多数据，一次IO操作获取的数据量越大，效率更高）

（3）离散度大的列放到联合索引前面

如何判断哪个列离散度高？

select count(A), count(B) from X;

如果count(A)的值大于count(B)的值，那么说明A字段的离散度大，选择范围会更大，需要以A为主要排序字段，索引应该为index(A,B) 而不是inde（B，A）

7.索引的维护及优化

建立索引会有利于查询但是会影响insert和update、delete等更新操作

并且过多的索引也会影响查询，不利于数据库分析和选择索引

重复索引：设为主键的字段不需要再加索引了

冗余索引：在联合索引中包含了主键的索引，对于innodb来说，每一个索引后面都会附加主键信息，这个时候就是冗余的。