MySQL优化(三)——SQL语句优化

MySQL 阅读: 440

在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的结果

select_type

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); -- 快

参考文章

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

版权声明:本文为博主原创文章,转载需注明来自: 洛洛の空间