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

提高MySQL的查询优化(一)

作者:UncleToo  来源:翻译  日期:2013-12-20 8:04:39
收藏  评论:( 0 )  阅读:822

本文我们重点讨论MySQL查询中 group by 和 order by子句的优化。我们首先介绍相关基本知识(如MySQL中的索引、执行计划等),然后在深入讲解一些高级优化技巧。我们将介绍“loose索引”和“tight索引”的优化并显示测试优化结果。

索引

这部分我们重点介绍B树索引(InnoDB和MyISAM只支持B-树索引),下图显示了一个B树索引实现。

提高MySQL的查询优化

B树同时支持“等于”(where id = 1)和范围(where date > '2013-01-01' and date < '2013-12-01')查询。

对于“等于”查询,我们一般采用主键或唯一键字段搜索,如:

select select * from table where id = 12

这种情况下,MySQL通过扫描树直接定位到目标子叶,然后停止,如下图:

提高MySQL的查询优化

这也是最快的一种索引扫描操作。


范围查询,如:select * from table where id in (6, 12, 18)

这种情况下,MySQL将扫描整棵树,并且需要访问很多子叶,如下图:

提高MySQL的查询优化

因此,相对“等于”查询,范围查询的效率要低的多。


测试表

这里我们使用两张测试表,这里创建第一张表City,它也是MySQL测试数据库“world”的一部分。

Create TABLE City (
    ID int(11) NOT NULL AUTO_INCREMENT,
    Name char(35) NOT NULL DEFAULT '',
    CountryCode char(3) NOT NULL DEFAULT '',
    District char(20) NOT NULL DEFAULT '',
    Population int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (ID),
    KEY CountryCode (CountryCode)
) Engine=InnoDB;


执行计划

MySQL配置查询计划的主要方法是采用 “explain”,下面是一个输出示例:

mysql> EXPLAIN select * from City where Name = 'London'\G

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

          id: 1

 select_type: SIMPLE

       table: City

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 4079

       Extra: Using where

从计划中我们可以看出,MySQL不使用任何索引(key: NULL),因此将扫描整个表。在这种情况下,我们可以添加一个索引来限制行数,如:

mysql> alter table City add key (Name);

Query OK, 4079 rows affected (0.02 sec)

Records: 4079  Duplicates: 0  Warnings: 0

添加完成后,我们再次执行上面的计划:

mysql> explain select * from City where Name = 'London'\G

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

          id: 1

 select_type: SIMPLE

       table: City

        type: ref

possible_keys: Name

         key: Name

     key_len: 35

         ref: const

        rows: 1

       Extra: Using where

结果表明,MySQL使用了索引。


索引的用法

当有多表关联查询时,MySQL只能使用一个索引,不过在某些情况下,MySQL也会存在交叉索引,这里我们就不介绍了。MySQL通过统计索引来决定最合适的索引。


组合索引

组合索引对MySQL的查询优化非常重要。MySQL可以使用任何索引的左边一部分。例如,如果我们有这样一个索引:

Comb(CountryCode, District, Population)

MySQL可以使用如下几种组合:

CountryCode

CountryCode + District

CountryCode + District + Population

下面我们看一个例子,通过这个例子,我们能看出那一部分被使用了:

mysql> explain select * from City

     where CountryCode = 'USA'\G

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

       table: City

        type: ref

possible_keys: comb

         key: comb

     key_len: 3

         ref: const

        rows: 273

注意 key_len: 3 ,这是我们使用的字节数索引。随着 CountryCode 字段声明为char(3),这意味着MySQL将在我们的组合索引使用第一个字段。

同样,MySQL可以使用左边的2个字段或3个字段的索引。在下面例子中,使用了左边的2个字段:

mysql> explain select * from City

where CountryCode = 'USA' and District = 'California'\G

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

       table: City

        type: ref

possible_keys: comb

         key: comb

     key_len: 23

         ref: const,const

        rows: 68

所以,MySQL在comb索引中选择了前面两个字段:

·CountryCode = 3 chars

·District = 20 chars

·Total = 23

下面我们再看一个使用全部索引的例子:

mysql> explain select * from City

where CountryCode = 'USA' and District = 'California’

and population > 10000\G

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

       table: City

        type: range

possible_keys: comb

         key: comb

     key_len: 27

         ref: NULL

        rows: 68

  • CountryCode = 3 chars/bytes

  • District = 20 chars/bytes

  • Population = 4 bytes (INT)

  • Total = 27


然而,如果在查询中没有最左边的第一个索引,MySQL将不使用组合索引,如:

mysql> explain select * from City where  

District = 'California' and population > 10000\G

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

table: City

       type: ALL

possible_keys: NULL

        key: NULL

    key_len: NULL

        ref: NULL

       rows: 3868

这个例子中,我们在where子句中没有使用CountryCode,因此MySQL没有使用我们定义的comb索引。


覆盖索引

覆盖索引是一个涵盖所有查询字段的索引,例如下面的查询:

select name from City  where CountryCode = 'USA' and District = 'Alaska'
and population > 10000

那么下面的索引就是一个覆盖索引:

cov1(CountryCode, District, population, name)

索引中的字段顺序在查询中是有一定顺序的:

1、where条件子句

2、Group By/Order by 子句

3、select

看下面这个例子:

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

输出Extra: Using where; Using index 说明MySQL使用了索引,这也说明了MySQL只会通过索引来实现查询:MySQL需要的信息都在索引中。这样MySQL的查询速度就快得多,尤其是当我们查询大文本字段或blob字段。


索引中字段的顺序

在索引中字段的顺序是非常重要的。B树的工作方式,更有利于一个字段用于“平等”查询和“范围”查询。例如下面的查询:

select * from City where district = 'California' and population > 30000

对于这个查询,组号的索引顺序应该是(district, population)这样一个顺序。

下面是(district, population)顺序的示意图:

提高MySQL的查询优化

在这种情况下MySQL能够去“直接”(通过索引扫描)到正确的district(“CA”)和扫描范围内的population。“district”字段的所有其他节点不会被扫描。

下面我们将顺序颠倒(population,district),看下面示意图:


在这个例子中,MySQL将不得不做一个“范围”扫描每个索引的population记录,并检查正确的district,所以速度会慢一些。

提高MySQL的查询优化

复杂的缓慢的查询

这里我们主要介绍两种比较典型的查询:

“group by”查询

“order by”查询

这些查询一般都比较慢,如何优化这些查询,减少查询响应时间,我们将在下一节为大家介绍。

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


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



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