【官方】MariaDB Optimization and Indexes 中文版(四)

ACMUG征集原创技术文章。详情请添加 A_CMUG或者扫描文末二维码关注我们的微信公众号。有奖征稿,请发送稿件至:acmug@acmug.com。
3306现金有奖征稿说明:知识无价,劳动有偿,ACMUG特约撰稿人有奖回报计划(修订版)


作者: MariaDB官方授权资词翻译组翻译

资词翻译组,由数名数据库技术爱好者组成,旨在传播技术,帮助他人,提升自我。目前的成员有:田丽芳,强昌金,王竹峰,侯军伟,吕智超,刘启荣,周彦伟,古雷。
译事三难:信、达、雅。信者,真也,真者,不伪也;达者,至也,至者,无过无不及也;雅者,文学性也,文学性者,当雅则雅当俗则俗也。我们深知能达成此三事,绝非一日之功,亦非常人所能。然,苟利国家生死以,岂因祸福避趋之。我们还是希望竭尽所能,不遗余力,做一点微小的工作,提高姿势水平。由于能力有限,水平一般,有错误不妥之处,还请批评指正,希望能得到大家的资词。

资词翻译组获得MySQL Server团队和MariaDB官方授权,翻译相关技术文章。


联合索引

一个关于联合索引的微型课程

该文档开头的内容琐碎甚至可能有些无聊。但之后会向您呈现有趣的多的信息,这些知识也许是一些你并没有意识到的关乎于MariaDB和MySQL的索引如何工作的内容。

这里也解释了EXPLAIN (在一定程度上)。
(这里的大部分内容也适用于非MySQL类型的数据库)

将要讨论的Query

问题是“美国的Andrew Johnson总统任期是什么时候?”
可用的表Presidents如下:

+-----+------------+----------------+-----------+
| seq | last_name  | first_name     | term      |
+-----+------------+----------------+-----------+
|   1 | Washington | George         | 1789-1797 |
|   2 | Adams      | John           | 1797-1801 |
...
|   7 | Jackson    | Andrew         | 1829-1837 |
...
|  17 | Johnson    | Andrew         | 1865-1869 |
...
|  36 | Johnson    | Lyndon B.      | 1963-1969 |
...

(‘Andrew Johnson’在本课中被挑中是因为它的重复度)
在这个问题中什么索引是最好的?更具体的说,什么索引对以下语句最适用:

SELECT  term
        FROM  Presidents
        WHERE  last_name = 'Johnson'
          AND  first_name = 'Andrew';

可尝试的索引如下:

  • 没有索引
  • 索引(first_name),索引(last_name)(两个独立的索引)
  • “合并索引取交集”
  • 索引(last_name, first_name)(一个联合索引)
  • 索引(last_name, first_name, term)(一个覆盖索引)
  • 其他

没有索引

在这里我做了一些假设。在seq列上有一个主键,但它对我们正在研究的查询并没有好处。

mysql>  SHOW CREATE TABLE Presidents \G
CREATE TABLE `presidents` (
  `seq` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(30) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `term` varchar(9) NOT NULL,
  PRIMARY KEY (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8

mysql>  EXPLAIN  SELECT  term
            FROM  Presidents
            WHERE  last_name = 'Johnson'
              AND  first_name = 'Andrew';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Presidents | ALL  | NULL          | NULL | NULL    | NULL |   44 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

# Or, using the other form of display:  EXPLAIN ... \G
           id: 1
  select_type: SIMPLE
        table: Presidents
         type: ALL        <-- Implies table scan
possible_keys: NULL
          key: NULL       <-- Implies that no index is useful, hence table scan
      key_len: NULL
          ref: NULL
         rows: 44         <-- That's about how many rows in the table, so table scan
        Extra: Using where

实现细则

首先,让我们描述一下InnoDB是如何存储和使用索引的

  • 数据和主键在BTree上是聚合在一起的
  • BTree查找非常快速有效。对于一个百万行的表来说,它可能有一个三层的BTree,上面两层可能会被缓存
  • 每个二级索引都是一个单独的叶子节点中含有主键的BTree
  • 从一个BTree中读取连续数据(按照索引)非常高效,因为它们是顺序存储的
  • 为了简单起见,我们可以忽略对于连续数据的扫描,将每个BTree查找计数为一个工作单元。这近似于一个繁忙系统中对于一个大表的磁盘命中数
    对于MyISAM,主键和数据不在一起存储,所以可以将主键当作一个二级索引(有点过度简化了)。

索引(first_name),索引(last_name)

初学者学到索引时,常常会在给很多列加索引时,每次只加一列。但是…
MySQL很少会在一次查询中使用多于一个的索引。所以,它会分析可能用到的索引。

  • first_name — 有2行可能符合需求(一次BTree查找,然后连续扫描)
  • last_name — 我们假设它采用last_name时有2行可能符合需求。这里有一些做SELECT时的步骤:1.采用INDEX(last_name),用last_name = ‘Johnson’找到两个索引项;2.获得主键(在InnoDB隐含添加在每个二级索引中),得到(17, 36);3.利用seq = (17, 36)去数据中获得Andrew Johnson 和 Lyndon B. Johnson这两行;4.用WHERE子句中的其余部分筛选结果找出想得到的行;5.发送结果(1865-1869)。
mysql>  EXPLAIN  SELECT  term
            FROM  Presidents
            WHERE  last_name = 'Johnson'
              AND  first_name = 'Andrew'  \G
  select_type: SIMPLE
        table: Presidents
         type: ref
possible_keys: last_name, first_name
          key: last_name
      key_len: 92                 <-- VARCHAR(30) utf8 may need 2+3*30 bytes
          ref: const
         rows: 2                  <-- Two 'Johnson's
        Extra: Using where

合并索引取交集

你很聪明而且认为MySQL应该也很聪明以至于能使用两个名字索引去获取答案。这叫做”Intersect”。1.使用INDEX(last_name),利用last_name = ‘Johnson’找到两个索引项,得到(7, 17);2.使用INDEX(first_name),利用first_name = ‘Andrew’找到两个索引项,得到(17, 36);3.将这两个列表结合在一起(7,17) & (17,36) = (17);4.利用seq = (17)深入数据中得到Andrew Johnson那一行;5.发送结果(1865-1869)。

          id: 1
  select_type: SIMPLE
        table: Presidents
         type: index_merge
possible_keys: first_name,last_name
          key: first_name,last_name
      key_len: 92,92
          ref: NULL
         rows: 1
        Extra: Using intersect(first_name,last_name); Using where

EXPLAIN给不出每一个索引收集了多少行数据等内容的具体细节。

索引(last_name, first_name)

这叫做复合索引或联合索引,因为它含有不只一个列。1.深入到这个索引的BTree中精确获取到Johnson+Andrew对应的行,得到seq = (17);2.利用seq = (17)深入数据中得到Andrew Johnson那一行;3.发送结果(1865-1869)。这样更好,事实上这通常是最好的。

    ALTER TABLE Presidents
        (drop old indexes and...)
        ADD INDEX compound(last_name, first_name);

           id: 1
  select_type: SIMPLE
        table: Presidents
         type: ref
possible_keys: compound
          key: compound
      key_len: 184             <-- The length of both fields
          ref: const,const     <-- The WHERE clause gave constants for both
         rows: 1               <-- Goodie!  It homed in on the one row.
        Extra: Using where

“覆盖”:INDEX(last_name, first_name, term)

令人惊讶的是,事实上我们可以做的更好。覆盖索引就是一个在其中能找到所有SELECT的字段的索引。它带来的额外好处是不必回表来完成工作。1.深入到这个索引的BTree中精确获取到Johnson+Andrew对应的行,得到seq = (17);2.发送结果(1865-1869)。数据的B树没被涉及到,这是一个优于联合索引的改善。

 ... ADD INDEX covering(last_name, first_name, term);

           id: 1
  select_type: SIMPLE
        table: Presidents
         type: ref
possible_keys: covering
          key: covering
      key_len: 184
          ref: const,const
         rows: 1
        Extra: Using where; Using index   <-- Note

除了”Using index”的加入,其他都与使用联合索引时一样。

其他

  • 如果你打乱了where子句中的字段顺序会发生什么?答:被AND的字段顺序并没有影响。
  • 如果你打乱了索引中的字段顺序会发生什么? 答:这可能会引起很大的不同。稍后再做进一步的讨论。
  • 如果在末尾有多余的字段会怎么样? 答:很小的危害;还有可能会有很大的好处(如,覆盖索引)
  • 冗余?就是说如果你有两个索引:INDEX(a), INDEX(a,b)会怎么样?答:冗余会在插入时耗费一些资源,它对SELECT几乎没有用。
  • 前缀?就是INDEX(last_name(5),first_name(5))。答:不必困扰,它几乎没有帮助,而且经常会引起问题。(细节作为另一个主题)

更多例子

INDEX(last, first)
    ... WHERE last = '...' -- good (even though `first` is unused)
    ... WHERE first = '...' -- index is useless

    INDEX(first, last), INDEX(last, first)
    ... WHERE first = '...' -- 1st index is used
    ... WHERE last = '...' -- 2nd index is used
    ... WHERE first = '...' AND last = '...' -- either could be used equally well

    INDEX(last, first)
    Both of these are handled by that one INDEX:
    ... WHERE last = '...'
    ... WHERE last = '...' AND first = '...'

    INDEX(last), INDEX(last, first)
    In light of the above example, don't bother including INDEX(last).

发布日期

更新 — 2012.10; 更多链接 — 2016.11

请参阅

Rick James仁慈的允许我们在知识库中使用这篇文章。

Rick James的网站还有其他有用的技巧,使用方法,优化和调试技巧。
原文地址:http://mysql.rjweb.org/doc.php/index1


注:ACMUG收录技术文章版权属于原作者本人所有。如有疑问,请联系作者。

看完转发,手留余香。关注我们,一起进步。
关注ACMUG公众号,参与社区活动,交流开源技术,分享学习心得,一起共同进步。

发表评论