索引 Index

Database 最佳化: 索引 Index
Categories:
Tags:

索引顺序

在所有的资料库内，建立索引是提昇资料库资料存取效率的很重要的方式，但是错误的索引顺序或是 SQL 语法 都可能让查询语法变成了 slow query。

假设我们有一个部落格文章的资料表，裡头存放所有使用者的部落格文章，资料表资料如下：

posts

post_id post_status author content
1 正常 kejyun 文章 1
2 删除 kejyun 文章 2
3 正常 kejyun 文章 3
4 正常 kj 文章 4
5 删除 kj 文章 5

如果我们要捞取 作者为 kejyun 且 状态正常 的文章，我们可能会用下列语法去捞取

SQL 语法 1

SELECT *
FROM posts
WHERE post_status = '正常'
AND author = 'kejyun'

或者用这个语法捞取

SQL 语法 2

SELECT *
FROM posts
WHERE author = 'kejyun'
AND post_status = '正常'

这两句 SQL 语法都可以捞出我们想要的结果，但是对于不同的索引执行的效率却是差异很多

如果我们的索引是 post_status、author 的顺序，SQL 语法 1 则会正确的使用索引来做查询，执行的效率会很快。

但是对于SQL 语法 2，会因为找不到适合该语法的索引去做查询，所以会变成不使用索引，而对整个资料表作完整的检索，去查找出资料来。

主要原因是

在 SQL 语法 WHERE 的顺序会影响查询索引的顺序

同样的索引资料在不同的顺序，表示为不同的索引，所以 post_status、author 与 author、post_status 这两个索引虽然是使用相同的资料栏位，但因为顺序不同所带来的查询效果也不同

同一句 SQL 语法只能使用单一个索引，所以不同的索引没办法共用

资料库没有这麽人工智慧，会去判断两个 SQL 语法可以使用相同的索引去查找出相同的资料，他会依照程式设计师给的语法，依序去查找是否有可用的索引。

分析 SQL 语法 1

在 SQL 语法 1 中，我们 WHERE 的第 1 个条件是 post_status = '正常'，所以资料库会去查找有没有索引开头是使用 post_status 的索引。

若有该索引，则再判断 WHERE 的第 2 个条件 author = 'kejyun'，所以资料库会去查找有没有索引开头是使用 post_status 的索引，且该索引的第 2 个索引是使用 author 的索引。

资料库会一直判断比较 SQL 语法 WHERE 条件与索引之间的顺序关係，直到没办法匹配后，后面的没办法匹配索引则使用完整比对的方式去进行查询

分析 SQL 语法 2

在 SQL 语法 2 中，我们 WHERE 的第 1 个条件是 author = 'kejyun' 但我们资料库没有建立索引开头是使用 author 的索引，所以只能对资料库使用完整比对。

原理

索引就像是书籍的目录一样，若以童话故事书为例，我们书中会收录世界各国的故事，且每个故事有他自己的类型，像是奇幻、惊悚、传说、神话。

如果我们以国家当做大标题（e.g. 第 1 索引），以故事类型当作小标题（e.g. 第 2 索引），那麽书籍目录会像：

  • 台湾
    • 奇幻
    • 惊悚
    • 传说
    • 神话
  • 日本
    • 奇幻
    • 惊悚
    • 传说
    • 神话
  • 欧美
    • 奇幻
    • 惊悚
    • 传说
    • 神话
  • 印度
    • 奇幻
    • 惊悚
    • 传说
    • 神话
  • etc…

如果我们要找台湾且类型为传说的童话故事，我们的目光会先移动到台湾的区段，然后在这个区块下找到类型为传说的故事

  • 台湾
    • 奇幻
    • 惊悚
    • 传说
    • 神话

但是如果我们要找所有类型为传说的故事中，发生在台湾的故事，依照步骤我们会希望先把所有传说的故事先列出来，再从这个目录下去找属于台湾的故事，所以我们希望会看到像这样的目录（索引）：

  • 传说
    • 台湾
    • 日本
    • 欧美
    • 印度

但是在这本故事书的目录（索引）中，我们没有看到这样的目录结构，所以我们没办法透过目录快速的找到我们要看的所有类型为传说的章节资料，只好从头到尾的去翻阅整本书，直到找到全部我们传说章节的故事，然后再从这些找出来的传说故事中，再去区别出哪些为台湾的故事。

所以目录（索引）的规则就是希望看故事书的人要怎麽快速找到他想要的东西，当没有我们可以参考的目录的话，就像资料库没有可参考的索引一样，就会找得比较慢（但还是找得出来）。

结论

因为索引顺序的不同，以及 SQL WHERE 条件顺序的不同，会使得资料库在使用索引进行查询有不同的效率，所以要谨慎的使用索引及 SQL 语法，才能达到高效率的查询结果。

控制索引更新

关闭索引更新：

ALTER TABLE table_name DISABLE KEYS;

开启索引更新：

ALTER TABLE table_name ENABLE KEYS;

MySQL在新增（INSERT）、删除（DELETE）、更新（UPDATE）的时候会去更新现有的索引表，而更新索引表也需要花费一些时间，当异动一笔资料的时候，索引表也做一次的异动，但当在做大量资料异动的时候，例如异动1000笔资料，索引表也需要异动1000次，而其实我们只需要最后一次（最新）的异动就好了，前面的999次都是不需要做的索引表异动更新，所以在异动大量资料前，可以使用指令 ALTER TABLE table_name DISABLE KEYS; 关闭索引更新，等异动完成后，再使用指令 ALTER TABLE table_name ENABLE KEYS; 开启索引更新。

ALTER TABLE users DISABLE KEYS;
异动（INSERT、DELETE、UPDATE）大量资料SQL语法
ALTER TABLE users ENABLE KEYS;

自定义Hash Index做字串完整比对

我们知道在对字串（CHAR或VARCHAR）去做查找的时候效率会远比对整数（INT）查找还慢，因CRC32对字串做校验后会回传整数的校验码，我们在资料表增加一个整数型态栏位，储存要比对字串的校验码。

crc32

建立str 的 32 位迴圈冗余校验码多项式。这通常用于检查传输的资料是否完整。

由于 PHP 的整数是带符号的，许多 crc32 校验码将返回负整数，因此你需要使用 sprintf() 或 printf() 的「%u」格式符来取得表示无符号 crc32 校验码的字串。 在原本实作email登入时会对email栏位做索引，所以会先去查找email字串栏位的资料，之后再去比对密码是否正确，但若资料过多字串比对的效率会降低很多

SELECT id,name,email
FROM user
WHERE email = "[email protected]"
AND password = "xxx"

我们加入了 emailcrc 的栏位去储存对 email 字串的校验码，再查找 email 字串栏位的资料前，先透过crc整数校验码快速过滤掉不可能的资料，之后再从少数的资料中做 email 字串栏位字串比对，如果资料量很大，这样的效率会提升很多。

SELECT id,name,email
FROM user
WHERE emailcrc = CRC32("[email protected]")
AND email="[email protected]"
AND password="xxx"

这边要注意的是没办法只使用 crc 校验码去当作唯一的条件，不同的字串可能会出现相同的校验码，所以最后还是要对你要比对的字串做比对，避免查询发生错误。

email emailcrc
[email protected] 1234567890
[email protected] 1234567890

参考资料