索引 Index
索引顺序
在所有的资料库内,建立索引是提昇资料库资料存取效率的很重要的方式,但是错误的索引顺序
或是 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 = "kejyun@gmail.com"
AND password = "xxx"
我们加入了 emailcrc
的栏位去储存对 email 字串的校验码,再查找 email 字串栏位的资料前,先透过crc整数校验码快速过滤掉不可能的资料,之后再从少数的资料中做 email 字串栏位字串比对,如果资料量很大,这样的效率会提升很多。
SELECT id,name,email
FROM user
WHERE emailcrc = CRC32("kejyun@gmail.com")
AND email="kejyun@gmail.com"
AND password="xxx"
这边要注意的是没办法只使用 crc 校验码去当作唯一的条件,不同的字串可能会出现相同的校验码
,所以最后还是要对你要比对的字串做比对,避免查询发生错误。
emailcrc | |
---|---|
kejyun1@gmail.com | 1234567890 |
kejyun2@gmail.com | 1234567890 |
参考资料
- KeJyun学习日誌: 提高存取MySQL效率小技巧
- KeJyun学习日誌: MySQL效率调校
- MySQL Indexing: Best Practices Slide PDF
- Tools and Techniques for Index Design PDF Slide
- EXPLAIN Demystified PDF slide
- Optimizing MySQL Configuration PDF Slide
- PHP手册 - crc32