您好,UncleToo欢迎您!  为了更好的浏览本站,请使用高版本浏览器
RSS  Tag     设为首页 | 加入收藏
 您所在的位置:首页 > 数据库技术 > MySQL

提高MySQL的查询优化(二)

作者:UncleToo  来源:翻译  日期:2013-12-21 8:23:47
收藏  评论:( 0 )  阅读:669

在上一篇文章里(提高MySQL的查询优化(一)),我们主要讨论了MySQL索引的相关知识及优化方式。本节我们重点谈论 order by 子句和group by 子句的优化。

Group by的例子

我们先看看下面的例子:查询一个国家有多少成个城市。

mysql> explain select name from City  where CountryCode = 'USA' and District = 'Alaska' and population > 10000\G

*************************** 1. row ***********

       table: City

        type: range

possible_keys: cov1

         key: cov1

     key_len: 27

         ref: NULL

        rows: 1

       Extra: Using where; Using index

从上面的输出中,我们可以看出MySQL没有使用任何索引(没有适当的索引可用),但它也显示了“Using temporary; Using filesort”。MySQL需要创建一个临时表来满足“Group by子句当没有适当的索引时(在这里你可以找到更多关于临时表的信息)。然而,MySQL也可以使用组合索引来满足“group by子句,避免创建临时表。


Group by和覆盖索引

为了说明“group by”查询我将使用如下表:

Create TABLE ontime_2012 (

   YearD int(11) DEFAULT NULL,

   MonthD tinyint(4) DEFAULT NULL,

   DayofMonth tinyint(4) DEFAULT NULL,

   DayOfWeek tinyint(4) DEFAULT NULL,

   Carrier char(2) DEFAULT NULL,

   Origin char(5) DEFAULT NULL,

   DepDelayMinutes int(11) DEFAULT NULL,

   ...

) ENGINE=InnoDB DEFAULT CHARSET=latin1

该表包含并可自由查询航空公司统计数据。表中有600万条记录,约2G大小。

从这些数据中,我们要找到在周日最大延迟的航班,并且按航空公司分组。

我们的查询方式是:

select max(DepDelayMinutes),

carrier, dayofweek

from ontime_2012

where dayofweek = 7

group by Carrier

执行计划是:

...

       type: ALL

possible_keys: NULL

        key: NULL

    key_len: NULL

        ref: NULL

       rows: 4833086

      Extra: Using where; Using temporary; Using filesort

正如我们所料想的,MySQL没有使用任何索引并且需要扫描大约4百万行。 因此,它必须创建一个大的临时表。

如果我们在“dayofweek”字段上创建一个索引会过滤掉一些行,但是MySQL仍然需要创建一个临时表:

mysql> explain select name from City  where CountryCode = 'USA' and District = 'Alaska' and population > 10000\G

*************************** 1. row ***********

       table: City

        type: range

possible_keys: cov1

         key: cov1

     key_len: 27

         ref: NULL

        rows: 1

       Extra: Using where; Using index

这时,我们可以按照(dayofweek, Carrier, DepDelayMinutes)这个特定的顺序创建一个覆盖索引。这时,MySQL就会去使用这个索引而避免了创建临时表:

mysql> alter table ontime_2012

add key covered(dayofweek, Carrier, DepDelayMinutes);


explain select max(DepDelayMinutes), Carrier, dayofweek from ontime_2012 where dayofweek =7 group by Carrier\G

...            

possible_keys: DayOfWeek,covered

        key: covered

    key_len: 2

        ref: const

       rows: 905138

      Extra: Using where; Using index

结果和我们预想的一样,MySQL没有创建临时表,这也是最快的一种方案。

注意:MySQL也可以使用非覆盖索引 (dayofweek, Carrier)来避免创建临时表,但是,覆盖索引会更有效率,因为MySQL只要通过覆盖索引就能够查询整个表。


Group by 与范围查询

如果我们有一个“常量”(dayofweek = N),那么使用覆盖指标效果很好,但是,如果在where子句中存在范围的条件,那么MySQL将无法使用索引和filesort,例如下面的例子:

mysql> explain select max(DepDelayMinutes), Carrier, dayofweek from ontime_2012

where dayofweek > 5 group by Carrier, dayofweek\G

...


       type: range

possible_keys: covered

        key: covered

    key_len: 2

        ref: NULL

       rows: 2441781

      Extra: Using where; Using index; Using temporary; Using filesort

这个例子中,MySQL仍然会创建临时表。为了解决这个问题,我们可以使用一个简单的技巧,用UNION将查询变成两部分。

(select max(DepDelayMinutes), Carrier, dayofweek

from ontime_2012

where dayofweek = 6

group by Carrier, dayofweek)

union

(select max(DepDelayMinutes), Carrier, dayofweek

from ontime_2012

where dayofweek = 7

group by Carrier, dayofweek)

对于每个查询,MySQL都会使用索引而不用创建临时表。执行计划如下:

*************************** 1. row ***************************

      table: ontime_2012

        key: covered

...

      Extra: Using where; Using index

*************************** 2. row ***************************

      table: ontime_2012

        key: covered

...

      Extra: Using where; Using index

*************************** 3. row ***************************

         id: NULL

select_type: UNION RESULT

      table: <union1,2>

       type: ALL

possible_keys: NULL

        key: NULL

    key_len: NULL

        ref: NULL

       rows: NULL

      Extra: Using temporary

结果说明,MySQL在两个查询中都使用了覆盖索引,但是它还是会创建一张临时表用来合并两个结果集,只是这时的临时表要小的对,因为它只需要存储两个查询的结果。


Group by和松散索引

松散索引查询是MySQL中GROUP BY查询的另外一种优化算法,它只需要考虑一小部分索引建,所以速度也是很快的,它的使用有下面的一些限制:

·单表查询;

·GROUP BY 的字段只允许是索引最左边的一个字段,没有其他字段;

·只允许使用MIN()和MAX()两个聚合函数,列相同。

(在MySQL手册中我们可以找到更多关于松散索引的信息。)

使用松散索引时,我们需要从 Group By 子句中列开始创建额外的索引,然后是where子句中所有的列(字段索引的顺序问题!)。例如,对于下面的查询:

select max(DepDelayMinutes) as ddm, Carrier, dayofweek from ontime_2012  where dayofweek = 5  group by Carrier, dayofweek

我们需要创建如下的索引:

KEY loose_index_scan (Carrier,DayOfWeek,DepDelayMinutes)

注意:loose_index_scan仅仅是一个索引名称,也可以是其他任何名字。

执行计划如下:

mysql> explain select max(DepDelayMinutes) as ddm, Carrier, dayofweek from ontime_2012  where dayofweek = 5  group by Carrier, dayofweek


      table: ontime_2012

       type: range

possible_keys: covered

        key: loose_index_scan

    key_len: 5

        ref: NULL

       rows: 201

      Extra: Using where; Using index for group-by

在where子句中“Using index for group-by”意味着MySQL将使用索引扫描,松散索引查询速度很很快,因为它只查询一小部分兼职键值。同事它还可以执行范围查询,如:

mysql> explain select max(DepDelayMinutes) as ddm, Carrier, dayofweek from ontime_2012

where dayofweek > 5 group by Carrier, dayofweek;


table: ontime_2012

       type: range

possible_keys: covered

        key: loose_index_scan

    key_len: 5

        ref: NULL

       rows: 213

      Extra: Using where; Using index for group-by;


Benchmark(用基准问题测试)

我们已经将紧凑索引、松散索引及临时表的查询速度进行了基准(Benchmark)比较,同样是600万条数据的表,大小为2GB,结果如下图:

从图中我们能看到,松散索引的性能是最好的,遗憾的是在group by中它只能使用max()和min()两个聚合函数,group by + avg() 是不使用索引的。

再看下面一个例子:

mysql> explain select avg(DepDelayMinutes) as ddm, Carrier, dayofweek from ontime_2012 where dayofweek >5  group by Carrier, dayofweek \G


      table: ontime_2012

       type: range

        key: covered

    key_len: 2

        ref: NULL

       rows: 2961617

      Extra: Using where; Using index; Using temporary; Using fileso

在例子中,MySQL使用了覆盖索引(而不是松散索引),因为在where子句中存在范围条件(dayofweek > 5),因此它将不得不创建一个临时表。


ORDER BY 和 filesort

当我们使用ORDER BY子句查询时,MySQL可能会执行“filesort”命令,在MySQL手册中可以找到更多关于filesort的信息。

以下filesort操作通常是相当缓慢的(即使它不涉及创建磁盘上的文件),特别是如果MySQL有大量的记录需要排序:

table: City

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 4079

       Extra: Using where; Using filesort

我们可以采用组合索引的方式来优化这个查询,如:

mysql> alter table City

add key my_sort2 (CountryCode, population);


mysql> explain select district, name, population from City where CountryCode = 'USA' order by population desc limit 10\G


      table: City

       type: ref

        key: my_sort2

    key_len: 3

        ref: const

       rows: 207

      Extra: Using where

MySQL的是能够使用我们的组合索引,以避免排序:作为需要排序的索引,MySQL可以读取索引子页完整正确的排序。


排序和 LIMIT

如果在查询中有一个 LIMIT 子句(与表中的数据相比,LIMIT是相对较少的,如 LIMIT 10 或 LIMIT 100),MySQL可以避免使用filesort并可以利用索引查询。

看下面的例子:

mysql> alter table ontime_2012 add key (DepDelayMinutes);

我们可以只在DepDelayMinutes字段上创建索引,并且执行下面的计划(注意 LIMIT 10 的查询):

mysql> explain select * from ontime_2012

where dayofweek in (6,7) order by DepDelayMinutes desc

limit 10\G


       type: index

possible_keys: DayOfWeek,covered

        key: DepDelayMinutes

    key_len: 5

        ref: NULL

       rows: 24

      Extra: Using where

关于MySQL中 Group by 和 order by 的优化我们就介绍完了,希望对对大家有所帮助。


相关文章:提高MySQL的查询优化(一)


本文由UncleToo翻译整理,转载请注明出处!



除非特别声明,本站所有PHP教程及其他教程/文章均为原创、翻译或网友投稿,版权均归UncleToo中文网所有, 转载请注明作者及出处。
原文网址:http://www.uncletoo.com/html/mysql/749.html
读完这篇文章后,你是否有所收获? 分享是一种生活的信念!
  • 0
  • 0
我来说两句
更多>>网友评论