MySQL性能优化之骨灰级高阶神技( 四 ) _小知识

③设置慢查询日志的时间，这里的单位是秒，意思是只要是执行时间超过 X 秒的查询语句被记录到这个日志中。这里的 X 就是你要设置的。(下面的例子设置的是 3 秒)
SETGLOBAL long_query_time=3;

文章插图

④查看多少 SQL 语句是超过查询阀值的(3 秒)

文章插图

Explain 分析 SQL 查询
通过上面的方法可以知道哪些 SQL 花费了较多的时间，那么如何对这些 SQL 语句进行分析呢。毕竟，我们的目的是通过分析以后，优化 SQL 从而提高其性能。
将 Explain 关键字放在要执行的 SQL 语句前面，可以模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理你的 SQL 语句的。

文章插图

Explain 执行 SQL 示意图
上面每个字段的含义，在这里不展开描述。
SQL 优化建议
如果发现慢查询的 SQL，我们就需要针对其问题进行优化。这里针对几个常见的 SQL 给出一些优化建议。
类似 SQL 优化的文章和例子在网上种类繁多，千奇百怪。建议在优化之前，先查看慢查询日志和 Explain 的语句，再进行优化，做到有的放矢。
①Count 优化
从 user 表中搜索 id 大于 7 的所有用户。如果是 InnoDB 存储引擎会进行逐行扫描，如果表中记录比较多，性能就是问题了。
Select count(*) from user where id>7 如果先将所有的行数 Count 出来，再减去 id<=7 的记录，这样速度就会快一些。
Select (select count(*) - (select count(*) from user where id <=7) from user) 如果有一个货物表 items，其中有一个 color 字段来表示货物的颜色，如果需要知道颜色是蓝色或者红色的货物的数量，可以这么写：
Select count(color=‘blue’ or color=‘red’) from items Select count(*) from items where color=‘blue’ and color=‘red’ 不过颜色本身是除斥的字段，所以可以优化成下面的 SQL 。
Select count(color=‘blue’ or null) as blue, count(color=‘red’ or null) as red from items ②GROUPBY 优化
MySQL 通过索引来优化 GROUPBY 查询。在无法使用索引的时候，会使用两种策略优化：临时表和文件排序分组。
可以通过两个参数 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 提升其性能。
这两个参数只对 Select 语句有效。它们告诉优化器对 GROUPBY 查询使用临时表及排序。
SQL_SMALL_RESULT 告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。
如果是 SQL_BIG_RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。
例如：
SelectSQL_BUFFER_RESULTfield1, count(*) from table1 groupby field1 假设两个表做关联查询，选择查询表中的标识列(主键)分组效率会高。
例如 actor 表和 film 表通过 actorId 做关联，查询如下：
Select actor.FirstName, actor.LastName,count(*) from film inner join actor using(actorId) Group by actor.FirstName,actor.LastName 就可以修改为：
Select actor.FirstName, actor.LastName, count(*) from film inner join actor using(actorId) Group by film.actorId ③Limit
Limit 对我们再熟悉也不过了，特别是在做分页操作的时候，经常会用到它。但在偏移量非常的时候问题就来了。
例如，Limit 1000，20 就需要偏移 1000 条数据以后，再返回后面的 20 条记录，前面的 1000 条数据是被抛弃掉的。
按照上例 SQL 代码如下：
Select name from user order by id limit1000,20 这里通过 id 索引到第 1001 条记录，然后取 20 条记录。这里利用 id 的索引的优势直接跳过了前面 1000 条记录。
Select name from user where id>=1001order by id limit 20 总结
从开发者的角度了解 MySQL 的应用优化。从数据类型的选择开始，针对数值类型，字符类型，时间类型进行了举例说明。
接下来谈到，作为数据表的查询，修改的优化，我们应该注意哪些细节。然后，聊了索引独立，前缀索引，多列索引，覆盖索引的优化方法。
最后，针对使用最多的查询优化进行了探讨。从影响查询的因素到查询基础，再到如何发现慢查询，用几个 SQL 优化的建议结束了我们的 MySQL 应用优化之旅。
写完全文感觉 MySQL 博大精深，需要学习的东西很多，一文不能面面俱到，还需不断学习。