在MySQL优化中，最常做的事情就是SQL语句优化，因为这个才是影响性能的最主要因素。

explain语句

对SQL语句进行优化少不了使用explain分析SQL语句。下面先来说说怎么使用explain语句。

explain语法

explain语法比较简单，只需要在你要分析的SQL语句前面加上explain即可，如：

explain select id,name from tb_user；

explain语句输出的每一行为对一个语句的分析，来看看每行有哪些输出：

mysql> explain select * from test \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra:
1 row in set (0.00 sec)

这里为了方便查看结果使用了\G代替分号。从上面可以看出explain的每行分析结果有10列，下面逐一分析每列的作用。

id

代表select语句的编号, 如果是连接查询，表之间是平等关系，select编号相同。如果某select中有子查询，则编号基于主查询递增。例如：

mysql> explain select * from (select c1 from test) as tmp \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: test
         type: index
possible_keys: NULL
          key: c1234
      key_len: 4
          ref: NULL
         rows: 6
        Extra: Using index
2 rows in set (0.00 sec)

select_type

select_type表示select语句的类型，取值有如下几种：

• simple：简单语句，不含子查询
• primary：含有子查询的语句
• subquery：非from型子查询语句
• derived：from型子查询语句
• union：union的语句
• union result：union的结果

table

table即该语句所查询的表，可能的取值有：

• 实际的表名
• 表的别名：如explain select * from t2 as tmp，则table为tmp
• derived：from型子查询时
• NULL：直接计算得结果，不用走表

type

type指查询的方式，非常重要，是分析数据查询过程的重要依据。可能的值如下：

• ALL：意味着对全表逐行扫描，运气不好扫描到最后一行，性能最低。
• index：比all性能稍好一点，通俗的说: ALL扫描所有的数据行（扫描整本书），index扫描所有的索引节点（扫描书的全部目录），其实可以理解为index_all。
• range：查询时，能根据索引做范围的扫描（扫描书的部分目录），可理解为index_range。
• ref：通过索引列，可以直接引用到某些数据行（定位到某些行的数据范围）。
• eq_ref：通过索引列，直接引用某1行数据（定位到某行的数据位置），常见于连接查询中。
• const,system,null：这3个分别指查询为常量级别, 甚至不需要查找时间。一般按照主键来查询时，易出现const,system，或者直接查询某个表达式，不经过表时, 出现NULL。

以上取值代码的性能越来越高，所以我们的优化目标应该是将SQL语句优化到常量级别，最好则是不使用SQL查询。

possible_keys

possible_keys指开始查询前估计可能使用的索引。

key

key指实际查询时所使用的索引。

key_len

key_len指实际查询时所使用的索引的长度。因为对于多列索引可能只会用到其中的部分列，用这个看出来用了哪些列。

ref

在表的连接匹配时，哪些列或常量被用于查找索引列上的值。

rows

估计查询结果的行数，MySQL根据表统计信息及索引选用情况，估算找到所需的记录所需要读取的行数。

Extra

查询的额外信息，比较重要。取值为下面的一项或多项：

• useing index：使用了索引覆盖，效率非常高。
• using where：光靠索引定位不了，还使用了where辅助判断。
• using temporary：使用了临时表，当group by与order by不同列时会出现。
• using filesort：使用文件排序(文件可能在磁盘,也可能在内存)，当数据量大时性能较低，要避免这种情况。

注：如果取出的列，含有text,blob或者更大的如mediumtext等，filesort将会发生在磁盘上。

in型子查询的陷阱

MySQL针对in型子查询做了优化，将in改成了exists子查询的执行效果。

执行过程不是我们直观想象的：先执行in子查询取出所有的数据，然后执行主查询判断每个数据是否在in取出的数据中。

而实际上的执行过程是：先执行主查询取出数据，然后遍历每个数据，将每个数据使用exists查询，这会每次拿着数据去in子查询表中查询该数据是否存在。

当in子查询表数据越多时, 查询速度越慢，我们可以使用连接查询代替in型子查询。如果in子查询表数据很少，使用in问题不大，甚至性能比连接查询要好。

limit及翻页优化

我们可能会经常使用limit做翻页：limit offset, N。其实上limit有一个问题：当offset非常大时, 效率极低。

原因是MySQL并不是跳过offset行，然后只取出后面的N行，而是会取出offset+N行，之后再丢掉前offset行。如果offset过大，那么取出的数据会非常大，很消耗资源。

如何优化？

1. 从业务上去解决，办法: 不允许翻过100页，以百度为例,一般翻页到70页左右。这种方法是限制offset不至于过大。
2. 不用limit，用条件查询：

   -- 原limit语句
   select id,title,time from tb_article limit offset, N
   -- 使用下面代替
   select id,title,time from tb_article where row_index>offset limit N
   

   这种方法需要增加一个额外的字段记录行数(可以直接使用主键)，并且数据不进行物理删除(可以逻辑删除，什么是逻辑删除请自行百度)。
3. 非要物理删除，还要用offset精确查询，还不限制用户分页，怎么办？优化思路是不查,少查,查索引,少取列。如果必须要查，则只查索引，不查数据，得到id，再用id去查具体条目信息，这样会使取出的数据不至于过大。SQL语句参考：

   select id,title,time
   from tb_article
   inner join (select id from tb_article limit offset, N) as tmp using id
   

其它子查询优化

from型子查询

from子查询查到的临时表, 是没有索引的。所以from的返回内容要尽量少，如果需要排序，在子查询内就先排好序。

group by优化

注意：分组用于统计，而不用于筛选重复数据。不重复的行，分组统计数据用, 而不要让查询产生N多重复数据，用group去重，效率会很低。

比如：1->N 连接时，栏目—-左连接—->商品表，将会产生重复行。

比如: 分组用于统计平均分、最高分较适合，但用于筛选重复数据，则不适合。

以及可以用索引来避免临时表和文件排序（using filesort）。

group by 的列要有索引，可以避免临时表及文件排序。
order by 的列要和group by 的一致，否则也会引起临时表。(原因是因为group by 和order by 都需要排序，所以如果2者的列不一致，那必须经过至少1次排序)。

order by优化

通过索引排序是性能最好的，通常如果SQL语句不合理，就无法使用索引排序，以下几种情况是无法使用索引排序的。

1. 查询使用了两种不同的排序方向，但是索引列都是正序排序的;
2. 查询的where和order by中的列无法组合成索引的最左前缀;
3. 查询在索引列的第一列上是范围条件;
4. 查询条件上有多个等于条件。对排序来说，这也是一种范围查询。

union优化

union总是会产生临时表，对union的优化比较棘手。

1. 注意union的子句条件要尽量具体，即—查询更少的行。
2. 子句的结果在内存里并成结果集，需要去重复，去重复就得先排序。而加all之后,不需要去重, union尽量加all，可以在代码中去重（压根就不应该取出重复的行）。

count() 优化

误区: myisam的count()非常快
答: 是比较快，但仅限于查询表的”所有行”比较快, 因为Myisam对行数进行了存储。一旦带有where条件, 速度就不再快了，尤其是where条件的列上没有索引更慢了。

假如，id<100 的商家都是我们内部测试的,我们想查查真实的商家有多少?

select count(*) from lx_com where id>=100;  -- (1000多万行用了6.X秒)
-- 小技巧:
select count(*) from lx_com; -- 极快
select count(*) from lx_com where id<100; -- 快（数据少）
select (select count(*) from lx_com) - (select count(*) from lx_com where id<100); -- 快

参考文章

• mysql 语句优化 - 小秀的博客 - CSDN博客
• MySQL Explain详解 - 杰克思勒(Jacksile) - 博客园
• MySQL常用SQL语句优化 - yayun - 博客园