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

ACMUG征集原创技术文章。详情请添加 A_CMUG或者扫描文末二维码关注我们的微信公众号。有奖征稿,请发送稿件至:acmug@acmug.com。

3306现金有奖征稿说明:知识无价,劳动有偿,ACMUG特约撰稿人有奖回报计划(修订版)


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

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

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


如何为SELECT语句构建最佳索引

问题描述

如果你有一个SELECT语句,你想给它构建一个最佳索引,下面的内容就是一份如何完成该任务的指南:

  • 一个适用于很多简单SELECT语句的算法,该算法对复杂查询也有帮助。
  • 关于该算法的一些例子,外加一些关于算法例外与变体的附加内容。
  • 最后是一个很长的”其他案例”列表。

这部分内容的目的是帮助一个新手快速成长,从此他/她创建的索引不会被认为是一个新手所为。

同时涉及很多边缘场景的解释,因此即使是专家也会得到一些有用的信息。

算法

这是一种为给定SELECT语句创建INDEX的方法。按照下面的步骤,收集列按顺序放到INDEX中。当步骤结束时,你通常会拥有“完美”索引。

  1. 给定一个由一串AND连接的表达式组成的WHERE:包括与常量做比较的,以任何顺序排列的列(如果有),这些列不藏在函数中。
  2. 你还有更多机会往INDEX中添加列;先把这些中的适用的一个加上:
    • 2a. 一个在”范围”中使用的列 — BETWEEN, ‘>’,不用通配符开头的LIKE等。
    • 2b. GROUP BY中按顺序的列。
    • 2c. ORDER BY中按顺序的列,如果没有混用ASC和DESC的话。

题外话

这部分内容假设你已经知道索引背后的原理,这里做一个简单的要点回顾。

实际上MySQL的索引都是以BTree来构建的,BTree在处理下面这些操作时效率会非常高:

  • 指定一个key,找到相符的行;
  • “Range scans” — 从一个值开始然后持续的寻找下一个值;

主键是一个唯一索引; a UNIQUE KEY is an INDEX. (“KEY” == “INDEX”.)

InnoDB的主键是一个聚簇索引,所以, 所有的数据都会存储在局聚簇索引的叶子节点上。非主键索引会首先通过该索引得到主键值,然后再通过主键索引查询到其他的数据。

每个InnoDB表都含有主键,如果你没有显示的指定主键,InnoDB会生成一个默认的主键。最好显式地指定主键.

从完整的实现来说,MyISAM的原理和InnoDB不同,所有的索引包括主键索引都包含一个数据指针,指向真正的数据文件。

后面所有的讨论都是在InnoDB的前提下,虽然大部分语句也适用于其他的存储引擎。

首先,一些例子

设想有一张姓名表,按照用户的last_name、first_name进行排序,你可以很容易的想象出这张表的样子,一般情况下还包括了一些其他信息,比如地址和电话号码。假设你想要从中找到我。如果你记得我的全名是Rick James,可以很容易的查找到我的记录。如果你只记得我的last_name是James,并且记得我的first_name的首字母是R,你可以很快的缩小范围到last_name是James,然后再其中寻找R开头的first_name,最后你可能会很快就想起来了Rick而排除Ronald。但是,假设你知道我的first_name是Rick,我的last_name首字母是J,这样你找起来会比较麻烦,你会去找所有的last_name以J开头的姓名,比如Jones, Rick; Johnson, Rick; Jamison, Rick等,这样会效率很低。

上述的过程就等同于

    INDEX(last_name, first_name) -- the order of the list.
    WHERE last_name = 'James' AND first_name = 'Rick'  -- best case
    WHERE last_name = 'James' AND first_name LIKE 'R%' -- pretty good
    WHERE last_name LIKE 'J%' AND first_name = 'Rick'  -- pretty bad

算法,步骤1 (WHERE “column = const”)

  • WHERE aaa = 123 AND ... : aaa开头的索引是个好索引。
  • WHERE aaa = 123 AND bbb = 456 AND ... : aaa和bbb开头的索引是个好索引。这种情况下,索引中aaa和bbb无论谁在前面都没有关系。
  • xxx IS NULL : 跟讨论中的”=const”一样。
  • WHERE t1.aa = 123 AND t2.bb = 456 — 你只能考虑当前表中的列。

注意表达式必须是column_name=const这种形式。这不适用于该算法中的该步:DATE(dt) = ‘…’, LOWER(s) = ‘…’, CAST(s …) = ‘…’, x=’…’ COLLATE…

(如果WHERE子句中没有”=”部分,不要在你的INDEX中加任何列,转到的步骤2。)

算法,步骤2

在2a/2b/2c算法当中找到第一个与你的情况对应的算法, 使用这个算法,然后退出。如果没有对应的,你就通过收集列来获取索引。

在一些场景下,最优的的方法是做完步骤1(或者与1等价的操作)再做步骤2c(ORDER BY)。

算法,步骤2a(one range)

range查询表现为:

  • aaa >= 123 — any of <, <=, >=, >; but not <>, !=
  • aaa BETWEEN 22 AND 44
  • sss LIKE ‘blah%’ — but not sss LIKE ‘%blah’
  • xxx IS NOT NULL Add the column in the range to your putative INDEX.

如果有多个片段在where条件中,你就必须马上停止。

完整的例子(假设没有其他东西在片段后面):

  • WHERE aaa >= 123 AND bbb = 1 ⇒ INDEX(bbb, aaa) (WHERE的顺序不重要,但是索引的顺序比较重要)
  • WHERE aaa >= 123 ⇒ INDEX(aaa)
  • WHERE aaa >= 123 AND ccc > ‘xyz’ ⇒ INDEX(aaa) or INDEX(ccc) (只有一个range)
  • WHERE aaa >= 123 ORDER BY aaa ⇒ INDEX(aaa) —额外好处,ORDER BY也会使用到索引。
  • WHERE aaa >= 123 ORDER BY aaa ⇒ INDEX(aaa) DESC —一样的额外好处。

算法,步骤2b(GROUP BY)

如果Query 中存在GROUP BY,所有GROUP BY中的列都需要加入到你正在创建的索引中,并且按照GROUP BY的顺序。(我不知道如果其中的一个列已经在索引中会发生什么。)

如果你正在GROUP BY的是一个表达式(包括了函数调用),你不能使用GROUP BY算法,停下来。

完整的例子(假设没有其他东西在片段后面):

  • WHERE aaa = 123 AND bbb = 1 GROUP BY ccc ⇒ INDEX(bbb, aaa, ccc) 或者 INDEX(aaa, bbb, ccc) 先处理=,任何顺序,然后再按照GROUP BY。
  • WHERE aaa >= 123 GROUP BY xxx ⇒ INDEX(aaa) 你应该停止在步骤2a。
  • GROUP BY x,y ⇒ INDEX(x,y) 没有WHERE
  • WHERE aaa = 123 GROUP BY xxx, (a+b) ⇒ INDEX(aaa) — GROUP BY中含有表达式,索引中不能包含xxx。

算法步,骤2c(ORDER BY)

如果Query 中存在ORDER BY,所有ORDER BY中的列都需要加入到索引中,并且按照ORDER BY的顺序。(我不知道如果其中的一个列已经在索引中会发生什么。)

如果ORDER BY中包含多列,并且混合使用ASC和DESC,不能添加ORDER BY列;他们不会有帮助,停下来。

如果你正在ORDER BY的是一个表达式(包括了函数调用),你不能使用ORDER BY算法,停下来。

完整的例子(假设没有其他东西在片段后面):

  • WHERE aaa = 123 GROUP BY ccc ORDER BY ddd ⇒ INDEX(aaa, ccc) —应该在2b就已经停止
  • WHERE aaa = 123 GROUP BY ccc ORDER BY ccc ⇒ INDEX(aaa, ccc) —ccc字段会同时被GROUPY  BY和ODER BY使用。
  • WHERE aaa = 123 ORDER BY xxx ASC, yyy DESC ⇒ INDEX(aaa) —混合使用ASC和DESC。

下面的情况特别好。通常只有等到所有行被收集到并根据ORDER BY进行排序之后才能进行LIMIT。但是如果是通过ORDER BY的索引去获取它们则只需要遍历(OFFSET+LIMIT)行。所以 在这些情况下, 你新建的索引会给你带来很大好处。

  • WHERE aaa = 123 GROUP BY ccc ORDER BY ccc LIMIT 10 ⇒ INDEX(aaa, ccc)
  • WHERE aaa = 123 ORDER BY ccc LIMIT 10 ⇒ INDEX(aaa, ccc)
  • ORDER BY ccc LIMIT 10 ⇒ INDEX(ccc)
  • WHERE ccc > 432 ORDER BY ccc LIMIT 10 ⇒ INDEX(ccc) — “range” 和ORDER BY是兼容的

(没有ORDER BY的LIMIT没有什么意义,所以我不讨论这种情况。)

算法例外

你已经收集了几个列,将它们放在索引中,并且添加到表中。这通常会为你的SELECT语句产生一个”好”的索引。以下是一些其他相关建议。

算法”错误”示例:

SELECT ... FROM t WHERE flag = true;

这将(根据算法)调用索引(flag). 然而,在一个列只有两个值(或者很少值)上建立索引几乎是无用的。这个叫’low cardinality’(低基数)。 优化器更喜欢执行全表扫,而不是在Btree索引和数据之间来回查找。

另一个方面,算法’正确’的示例

SELECT ... FROM t WHERE flag = true AND data >= '2015-01-01';

这将建立一个以flag开头的联合索引: INDEX(flag,data). 该索引可能非常有益,它可能比INDEX(date)索引更有利。

如果你索引中的列包含了可能被UPDATE语句更新的列。注意UPDATE语句将会有额外的工作,从INDEX的Btree中的一个地方删除一”行”,并插入一”行”到Btree中。例如:

INDEX(X)
UPDATE t SET x = ... WHERE ...;

有很多种说法关于更好的保持索引还是放弃它。
在这种情况下,缩短索引也许是有利的。

INDEX(z,x)
UPDATE t SET x = ... WHERE ...;

更改索引为INDEX(z)将会减少UPDATE工作,但是可能会对SELECT有损伤。这取决于每个语句执行的频率和其他更多的因素。

局限

(一般有如下例外。)

  • 你不能创建大于3KB的索引。
  • 你的索引中不能包含超过767字节的列(767 bytes — VARCHAR(255) CHARACTER SET utf8)。
  • 你可以使用”前缀”索引处理大字段,但请见下文。
  • 你在索引中不应该超过5个列。(这仅是一个最优实践规则, 没有任何硬性限制)
  • 你不应该有冗余索引。(请见下文)

状态值与低基数(Flags and low cardinality)

如果’flag’列只有非常少的值,那么INDEX(flag)几乎无用。更具体的说,例如 WHERE flag =1 ,flag列超过20%的概率都是1,那么该索引会被忽略。优化器更喜欢执行全表扫描而不是在INDEX与data之间来回跳转获取超过20%的行。

(20% 这个数实际上是一个在10%到30% 之间的数, 这个取决于一些其他因素)

“覆盖”索引

“覆盖”索引是包含SELECT语句中所有列的索引。特指SELECT语句仅查看INDEX Btree来完成。(由于Innodb的PRIMARY KEY 是数据的聚集,所以在PRIMARY KEY上的”覆盖”索引并没有什么好处)。

迷你指南:1. 根据上面的”算法”收集列的列表。2.在列表的末尾以任何的顺序添加SELECT语句中看到的其余列。

例如:

  • SELECT x FROM t WHERE y = 5; => INDEX(y,x) — 根据算法来说应该INDEX(y)
  • SELECT x,z FROM t WHERE y = 5 AND q = 7 => INDEX(y,q,x,z) — y 和 q 以任何顺序(根据算法),然后x 和在 按任一顺序(覆盖)。
  • SELECT x FROM t WHERE y > 5 AND q > 7; => INDEX(y,q,x) — y 或者 q 在前(根据算法),然后其他列放在后。

覆盖索引带来的加速可能很小,或者很大;这个很难预测。

注意事项:

  • 构建包含大量列的索引是不明智的,建议不要超过5列。
  • 前缀索引不能用于”覆盖”,因此不要在覆盖索引中的任何位置使用前缀索引。
  • 索引列’宽度’的一些限制(3KB),所以”覆盖”索引可能是不可能的完全适用的。

冗余/过度索引

index(a,b)可以查找到所有index(a)可以查找到的信息。所以你不需两者都要。摒弃掉较短的那个。

如果你有很多SELECT语句,并且要为这些语句生成很多索引,这将会引起另一个问题。每个索引必然(迟早)会被每个INSERT更新。越多的索引意味着越慢的INSERT。限制每个表上的索引数在6个左右(经验法则)。

注意指南中几处地方中说的“以任何顺序”。例如,你有这样两个语句(在不同的SELECT中):

  • WHERE a=1 AND b=2 既可以请求INDEX(a,b),也可以请求INDEX(b,a)
  • WHERE a>1 AND b=2 只能请求仅含有INDEX(b,a)的索引(b,a),原因是INDEX(b,a)可以只用一个索引处理两种情况。

假设你有很多个索引,包括(a,b,c,dd)和(a,b,c,ee)。这些都变得很长。考虑要么取其中任何一个,要么直接用(a,b,c)。有时候(a, b, c)的选择性非常好,以致于加上‘’dd‘’或“ee‘也不会产生足够的差异。

优化器选择ORDER BY

优化指南跳过了一个有时会用到的重要优化。优化器有的时候会忽略WHERE,而使用与ORDER BY匹配的INDEX。这当然需要是一个完美的匹配—ORDER BY中的所有列都以相同的顺序排序。且都以ASC或都以DESC的方式。

如果有LIMIT,这会变得特别有好处。

但有一个问题,可能有两种情况,优化器有时不够聪明,无法领会哪种情况适用:

  • 如果WHERE进行非常少的过滤,则以ORDER BY顺序提取行避免了排序,并且几乎没有性能浪费(因为“少过滤”)。在这种情况下使用INDEX匹配ORDER BY更好。
  • 如果WHERE做了很多过滤,ORDER BY浪费了很多时间来获取行来过滤它们。使用INDEX匹配WHERE子句更好。

你该怎么办?如果你认为有可能“少过滤”,那么使用ORDER BY列按顺序创建一个索引,并希望优化器在它该上场的时候使用它。

OR

一些例子:

WHERE a=1 OR a=2 -- This is turned into WHERE a IN (1,2) and optimized that way.
WHERE a=1 OR b=2 usually cannot be optimized.
WHERE x.a=1 OR y.b=2 This is even worse because of using two different tables.

其中一个变通的方法是使用UNION,UNION的每个部分都可以被独立优化,对第二个例子而言:

( SELECT ... WHERE a=1 )   -- and have INDEX(a)
UNION DISTINCT -- "DISTINCT" is assuming you need to get rid of dups
( SELECT ... WHERE b=2 )   -- and have INDEX(b)
GROUP BY ... ORDER BY ...  -- whatever you had at the end of the original query

现在query可以很好地利用两个不同索引。 注意:“索引合并”可能会在原始query中起作用,但它不一定比UNION更快。复合索引的姊妹篇包括索引合并。

第三种情况(OR跨越两个表)与第二种类似。

如果你原本有一个LIMIT,UNION就变得复杂。 如果你开始使用ORDER BY z LIMIT 190, 10,那么UNION需要这样:

( SELECT ... LIMIT 200 )   -- Note: OFFSET 0, LIMIT 190+10
UNION DISTINCT -- (or ALL)
( SELECT ... LIMIT 200 )
LIMIT 190, 10              -- Same as originally

TEXT / BLOB

您不能直接索引TEXT或BLOB或大VARCHAR或大BINARY列。 但是,您可以使用“前缀”索引:INDEX(foo(20))。 这表明索引了foo的前20个字符。 但…这几乎没什么作用。

前缀索引示例:

INDEX(last_name(2),first_name)

该索引将包含“Ja”,“Rick”。 这无法区分“Jamison”,“Jackson”,“James”等,因此这个索引近乎无用以至于优化器常常会忽略它。

有可能的话,永远不要做UNIQUE(foo(20))这样的索引,因为这将唯一性约束应用于列的前20个字符,而不是整个列!

关于prefix indexing 的延伸阅读

Dates

DATE,DATETIME等等比较起来是很复杂的。

一些诱人,但效率低下的技术:

date_col LIKE’2016-01%’,必须将date_col转换为字符串,类似的行为如函数LEFT(date_col,4)=’2016-01’(译者:原文如此,应该是‘2016’)— 在函数DATE(date_col)= 2016中隐藏列 —把列隐藏在函数中。

必须进行全部扫描所有记录。 (另一方面,它可以方便地使用GROUP BY LEFT(date_col,7)进行每月分组,但这不是INDEX问题。)

这些是高效的,并且可以使用索引:

        date_col> ='2016-01-01'
     AND date_col <'2016-01-01'+ INTERVAL 3 MONTH

这种情况下起作用是因为右侧的值都转换为常量,它就是一个“range”。 我喜欢INTERVAL的设计模式,因为它避免计算一个月的最后一天,并且避免出现’23:59:59‘,当你有微秒的时间时这是错误的。(还有其他的情况。)

EXPLAIN Key_len

执行EXPLAIN SELECT …(和EXPLAIN FORMAT = JSON SELECT …如果你是5.6.5)。 观察所选择的Key以及Key_len,由此你可以推导出索引的哪些列被用于过滤。 (JSON格会让你更容易的获得这个答案。)从那里,你可以判断它正在使用如你所设想的那些索引。 警告:Key_len只覆盖到WHERE部分; 非JSON的输出不会很清楚的说明GROUP BY或ORDER BY是否由索引处理。

IN

IN(1,99,3)有时被优化为“=”,但不总是。 旧版本的MySQL没有像新版本一样优化(5.6可能是主要转折点。)

IN(SELECT…)

从版本4.1到5.5,IN(SELECT …)的优化非常差。 SELECT每次都被实际地重新执行。 通常它可以转换为JOIN,其工作速度更快。 下面是一个例子:

SELECT  ...    FROM  a    WHERE  test_a      AND  x IN (        SELECT  x            FROM  b            WHERE  test_b
                );SELECT  ...    FROM  a    JOIN  b USING(x)    WHERE  test_a      AND  test_b;

SELECT表达式将需要在列名前加表名“a”为前缀。

在有些情况下,这种模式很难去应用。

5.6做了一些优化,但可能不如JOIN好。

如果在子查询中存在JOIN或GROUP BY或ORDER BY LIMIT,在这种模式下转化成JOIN会很复杂,下面这种模式可以尝试:

SELECT  ...    FROM  a    WHERE  test_a      AND  x IN ( SELECT  x  FROM ... );SELECT  ...    FROM  a    JOIN        ( SELECT  x  FROM ... ) b        USING(x)    WHERE  test_a;

注意:如果你最终有两个子查询JOIN在一起,注意没有任何索引,性能可能是非常糟糕。 (5.6通过动态创建子查询的索引来改进)。

在MariaDB和Oracle 5.7中,有关“NOT IN”,“NOT EXISTS”和“LEFT JOIN..IS NULL”的工作正在进行; 因此这里的讨论可能在将来是不成立的。

Explode/Implode(剧增/剧减)

当你有JOIN和GROUP BY时,你可能碰到这样的情况,相比原来的query,JOIN激增了更多的行数(由于多:多关联),但是你所需要的仅是原表中的一行,所以,你添加GROUP BY来缩减行数到期望的集合。

这中激增+缩减本身的代价是昂贵的。如果可能的话,最好避免它。

有时候,下面的情况会起作用。

使用DISTINCT和GROUP BY来抵消激增

SELECT  DISTINCT
        a.*,
        b.y
    FROM a
    JOIN b
⇒
SELECT  a.*,
        ( SELECT GROUP_CONCAT(b.y) FROM b WHERE b.x = a.x ) AS ys
    FROM a

当使用第二个表只是为了检查存在性时:

SELECT  a.*
    FROM a
    JOIN b  ON b.x = a.x
    GROUP BY a.id
⇒
SELECT  a.*,
    FROM a
    WHERE EXISTS ( SELECT *  FROM b  WHERE b.x = a.x )

(译者注:原文SQL应该有误)

另一种转换

多对多映射表

以这种方式来做:

    CREATE TABLE XtoY (
        # No surrogate id for this table
        x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
        y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
        # Include other fields specific to the 'relation'
        PRIMARY KEY(x_id, y_id),            -- When starting with X
        INDEX      (y_id, x_id)             -- When starting with Y
    ) ENGINE=InnoDB;

笔记:

  • 此表缺少AUTO_INCREMENT ID – 给定的PK是’普通’PK;没有好的理由不用它。
  • “MEDIUMINT” – 这是一个提醒,所有INT应该尽可能小以保证安全(更小⇒更快)。当然这里的声明必须要与在表中的定义所指向的内容相匹配。
  • “UNSIGNED” – 几乎所有INT也可以声明为非负数。
  • “NOT NULL” – 嗯,这是对的,不是吗?
  • “InnoDB” – 比MyISAM更高效,是因为InnoDB中的主键数据聚集方式。
  • “INDEX(y_id,x_id)” – PRIMARY KEY使得向某一个方向遍历非常高效;这个索引使得通过其它方向遍历很高效。不需要设置UNIQUE;在INSERTs时将需要付出额外的努力。
  • 在二级索引中,我们说只有索引INDEX(y_id)会正常工作,因为它隐式包含x_id。但我宁愿让它更明显因为我希望这是一个“覆盖”索引。

为了有条件地INSERT新记录,使用IODKU
需要注意的是如果这个表中有AUTO_INCREMENT,IODKU会将ids”烧”得非常快。

Subquery和UNIONs

SELECT中的每一个子查询和UNION中的每一个SELECT可以被认为在找到合适索引方面是独立的。
例外情况:在一个“相关”(“依赖”)子查询中,依赖外层表的WHERE条件部分不是很容易为他生成一个INDEX。

JOINs

第一步是决定优化器采用何种顺序使用表。如果你不能弄清楚这点,那么你可能需要悲观一点,并为每个表创建两个索引,一个假设这个表会先被用到,另外一个假设它将在后面被使用到。

优化器通常以一个表开始,并从中提取所需的数据。如果它找到一个匹配的(即匹配WHERE子句)行,下一步它会在“下一个”表中寻找,这被称为NLJ(“Nested Loop Join”)。过滤和寻找会持续在剩下的表中进行。

优化器通常基于下面这些提示选择“第一个”表:

  • STRAIGHT_JOIN强制表顺序。
  • WHERE子句限制所需的行(无论是否索引)。
  • 在LEFT JOIN中的“左”表通常在“右”表之前。 (通过查看表定义,优化器可以决定“LEFT”是不相关的。)
  • 当前的INDEXs将决定顺序。

运行EXPLAIN将告诉您Optimizer当前很有可能使用的表顺序。在添加新的INDEX之后,优化器可以选择不同的表顺序。你应该预测顺序的变化,猜测什么顺序最有意义,并相应地建立INDEX。然后重新运行EXPLAIN,看看优化器是否和你想的一样。

您应该根据与其相关的WHERE,GROUP BY和ORDER BY子句的任何部分构建“第一个”表的INDEX。如果GROUP/ORDER BY使用不同的表,则应忽略该子句。

基于ON子句,将选择第二个表(尽量不使用逗号来做join,请写JOINs与JOIN关键字和ON子句!)。此外,可能有一部分WHERE子句是相关的。 针对后续的表,GROUP/ORDER BY一般不在索引的考虑范围。

分区表

分区很少替代良好的INDEX。

PARTITION BY RANGE是一种在索引不够好时有用的技术。在二维空间场景下,例如求地理上附近的地方,一个维度可以部分地通过分区修剪来处理;那么其他维度可以由常规索引(最好是PRIMARY KEY)来处理。

全文索引

全文索引现在在InnoDB中的实现与MYISAM中一样。它提供了一种在TEXT列中查找“words”的方法。这比像’%word%’的列更快(当它合适的时候)。

WHERE x = 1

AND MATCH (…) AGAINST (…)

总是(?)首先使用全文索引。那就是说,当ANDs中的其中一个MATCH时整个算法就失效了。

一个新手的特征

– 没有“复合”(又名“组合索引”)索引

– 没有主键

– 冗余索引(尤其明显的是PRIMARY KEY(id), KEY(id))

– 大多数或所有的列单独建立索引(“但是我给所有列都建了索引”)

– “Commajoin” — 那是`FROM a, b WHERE a.x=b.x` 而不是`FROM a JOIN b ON a.x=b.x`

发布日志

首次发布2015年3月;更新2016年2月;添加日期2016年6月。

该文档中的建议适用于MySQL, MariaDB 和 Percona。

附加资源

– 手册中的一些信息:ORDER BY优化

– A short, but complicated, example

– 关于访问复合索引范畴的手册页

– 一些JOIN的讨论

– 这个文章是我在2013年给出的一个Percona教程,加上很多年来在几百个系统中处理几千个慢查询的经验的合并

– 我很抱歉这个文章不能告诉你如何为所有的SELECT语句创建索引,一些太复杂了

– Indexing 101: 在一个单独的表上优化MySQL查询(Stephane Combaudon – Percona)

– 一个复杂查询,较好的被explain。

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

[Rick James’ site]还有其他有用的建议,方法,优化,和调试技巧。

源地址: [http://mysql.rjweb.org/doc.php/random]


注:ACMUG收录技术文章版权属于原作者本人所有。如有疑问,请联系作者。
看完转发,手留余香。关注我们,一起进步。
关注ACMUG公众号,参与社区活动,交流开源技术,分享学习心得,一起共同进步。

发表评论