记一次SQL调优

insert
优化

如果你在某一时刻有大量的 insert
操作，一条一条插入是非常耗时的。 insert
语句本身支持一次插入很多条记录，插入记录数上限受sql语句长度限制，一般一次插个几千条是没问题的。在我的 《如何手动实现Try Insert和Insert Or Update》
一文中对于各种情况都有具体的例子，这里就不赘述了。

explain
语句结果分析

SQL本身是一种对机器来说抽象级别很高的语言，我们通过SQL告诉DBMS我们需要什么，而没有告诉它具体要怎么做。DBMS会猜测性地以最优的方法去完成我们给的任务，但是它往往做得不太好，毕竟不同业务最优做法各不相同，目前我们还没有办法让机器完全理解我们的业务。所以我们需要辅助机器，帮助它找到最好的查询逻辑。通常的做法是 添加合适的索引，让所有的查询都走索引
。在MySQL中，在任何一个 select
语句前加上 explain
，就可以知道MySQL对这条查询的理解和实际执行逻辑。

下面来分析 explain
语句返回的结果。 explain
会展示查询涉及到的每张表分析结果，里面有很多参数，我们一般只需要关注以下几个参数：

1. type

   type描述表是怎么 join
   的，按从 最好到最坏
   一共有以下几个值：

   值	解释
   system	表只有一行，是一种特殊的 const type
   const	表里只有一行匹配的记录， join 时可以认为是常量
   eq_ref	使用的索引为 primary key 或 unique not null index
   ref	join 只使用最左前缀匹配原则的普通索引
   fulltext	使用全文检索索引
   ref_or_null	与 ref 差不多，主要是多了NULL值的查询
   index_merge	使用了MySQL的索引合并优化
   unique_subquery	类似 eq_ref ，主要用于包含IN子查询的查询
   range	走索引的范围查询
   index	索引树被整个扫了，速度比扫表好一点
   ALL	整个表被扫，非常糟糕的情况，一般要避免

   一般做SQL优化，通常出现 index
   和 ALL
   都是需要优化的。

2. Extra

   MySQL查询的附件信息，有时候代表着查询的额外代价，出现 Using filesort
   、 Using temperary
   都表示查询速度不行。

   • Using filesort
     表示 order by
     子句不走索引，使用文件排序，需要对 order by
     进行优化。
   • Using temperary
     表示查询过程中创建了临时表，通常发生在包含 group by
     和 order by
     的查询中。
3. rows和filtered

   rows
   表示MySQL预估的查询需要的行数， filtered
   表示根据条件过滤之后的行所占的百分比。值为100表示没有行被过滤掉。所以 rows
   * filtered
   查询需要的总的行数。这个值自然是越小越好。

查询优化实践

查询优化的策略就是加索引，primary key 和 unique key在根据具体业务定，我们做优化，一般都是添加普通索引。普通索引分为两种，单个字段的索引和多个字段的联合索引。联合索引的应用场景相对窄一点，如果你要查的数据可以被联合索引全部囊括，直接从索引拿数据，可以考虑使用联合索引。 读多写少重复值少散列分布的字段最适合建索引
。你可以把你的程序使用到的所有SQL都列出来，一条一条 explain
，没有走索引的，就酌情给某个或某几个字段（ join
里的字段、 where
里的字段都是重点考虑对象）加上索引，直到所有的查询走索引为止。这么做以后，你的查询type正常都可以到达比较好的情况，但是对于包含 order by
子句的查询，可能你的Extra信息就不太理想了。 Using filesort
和 Using temperary
有时候阴魂不散，很难搞。这时候最佳的策略就是变着花样选择排序的字段。比如你的表有一个自增主键，你可以考虑用它作为插入时间来做排序。MySQL本身在这方面的优化非常糟糕，需要耐心地多尝试。

Reference

MySQL explain

MySQL ORDER BY优化