原文请关注 这里
这是 文章 的翻译,在翻译过程中,会对其中涉及到的语句加上一些个人理解以及 SQL 语句的执行,并进行特别的标注。
1. 你做了一个很棒的选择,因为:
对于普通开发者和 DBA,理解索引都是非常重要的;
对于大量的生产环境上的问题,糟糕的索引要负有责任;
索引没有非常的高深。
2. MySQL 索引事项
理解索引;
为自己的应用选择最好的索引;
解决常见的 MySQL 限制。
3. 废话少数,索引有什么用?
可以更快的访问数据库;
可以增加强制限制(UNIQUE, FOREIGN KEY);
没有索引的查询可以运行,但是可能会花费很长时间。
4. 你可能听过的一些索引类型
B-Tree 索引:MySQL 中最主要的索引;
RTREE 索引:仅仅是 MyISAM,GIS;
哈希索引:MyISAM,5.6 开始的 Innodb。
5. BTREE 索引家族
多种不同的实现:
为了加速,都有相同的操作;
内存和磁盘是需要考虑的两方面;
B+Tree 是典型的磁盘存储:数据存储在页节点上;
TokuDB Fractal Trees 在逻辑上是相似的:但是在物理存储上不同
6. B+ 树例子
7. MyISAM vs Innodb
在 MyISAM 中,数据指针指向数据文件的物理节点,所有的索引都是等价的;
在 Innodb 中,主键在页节点上存储数据,耳机索引存储主键作为数据指针。
8. BTREE 索引能做什么?
直接查看 KEY=5 的所有列;
找到 KEY > 5 的列,范围查找;
查找 5
不能找到 KEY 的最后一个数字是 0 的列(这个不是范围查找)。
9. 字符串索引
字符串索引实际上也没什么不同,按照字典顺序排列,例如 ”AAAA" < "AAAB";
like 前缀是一个特殊排序,例如 LIKE “ABC%” 意味着 “ABC[LOWEST]”
10. 多列索引
按照定义的顺序从左往右进行比较,例如在 KEY(col1,col2,col3) 中,(1,2,3) < (1,3,1);
多列索引仍然是一个 BTREE 索引,但不是每列都是一个单独的 BTREE。
11. 索引注意事项:
索引会有消耗,不要加比需要更多的索引,在多数情况下拓展现有的索引比加新索引更好;
更新索引会造成数据库大量的写操作;
索引会在磁盘和内存上浪费空间,但是会优化查询性能。
12. Innodb 索引
数据通过主键聚集存储:
通过主键查询是最好的方式;
对于文章的评论,(POST_ID,COMMENT_ID) 可能是一个比较好的主键,因为这会把同一篇文章的评论存储在一起;
bigint 型的字段也是主键的可选项。
主键被隐式的连接到其他索引上:
KEY(A) 实际上是 KEY(A, ID);
Useful for sorting, Covering Index.
13. MySQL 怎么使用索引?
数据查询;
排序;
Avoiding reading “data”;
特定优化。
14. 在数据查询中使用索引
用了索引 LAST_NAME
SELECT * FROM EMPLOYEES WHERE LAST_NAME=“Smith”;
用了索引 (DEPT,LAST_NAME)
SELECT * FROM EMPLOYEES WHERE
LAST_NAME=“Smith” AND
DEPT=“Accounting”
这里虽然索引字段顺序和查询的顺序颠倒,依然会走索引,不是因为最左匹配不走索引。
多列索引会变的困难,对于索引 (A,B,C):
下面的条件会走索引:
A>5
A=5 AND B>6
A=5 AND B=6 AND C=7
A=5 AND B IN (2,3) AND C>5
下面的条件不走索引,因为不符合最左匹配,缺少第一列
B > 5
B = 6 AND C = 7
在 MySQL5.7 中使用 explain 执行了一下,发现还是会走索引的,估计 MySQL 底层做了什么优化?
下面条件会走部分索引
A>5 AND B=2
A=5 AND B>6 AND C=2
SQL 优化的第一原则:
MySQL 在多列索引中,一遇到 (,between)就会停止使用 key,然而能继续使用 key 直到 in 范围的右边。
15. 在排序中使用索引
排序
SELECT * FROM PLAYERS ORDER BY SCORE DESC LIMIT 10
该 SQL 会使用建立在 SCORE 列上的 索引;
如果排序的时候没有使用索引,将会导致非常耗时的文件排序;
在排序中经常会考虑组合索引, 例如下面的 SQL 可以考虑(COUNTRY,SCORE) 索引:
SELECT * FROM PLAYERS WHERE COUNTRY=“US” ORDER BY SCORE DESC LIMIT 10
使用多列索引进行高效的排序,在排序中使用索引有很多的限制,对于 KEY(A,B):
下面排序会使用索引:
ORDER BY A:主列索引;
A=5 ORDER BY B:通过第一列过滤数据,第二列进行排序;
ORDER BY A DESC, B DESC:用相同的排序进行排序;
A>5 ORDER BY A:主列上进行查询和排序
下面的语句不会使用索引:
ORDER BY B :非主列索引排序;
A>5 ORDER BY B:第一列上使用范围,第二列进行排序;
A IN(1,2) ORDER BY B:第一列上用 IN;
ORDER BY A ASC, B DESC:两列的排列顺序不同。
使用索引进行排序的规则
两列的排列顺序不能不一致;
非排序的列中索引部分只能用 =,in 也不能用。
16. 避免读数据
覆盖索引:对特定的查询使用索引,而不是对索引类型使用索引;
仅仅读取索引,而不是读数据:索引比数据小;
SELECT STATUS FROM ORDERS WHERE CUSTOMER_ID=123 使用索引 KEY(CUSTOMER_ID,STATUS);
通过索引读取数据是有顺序的,而通过数据指针读取数据经常是随机的。
17. 特定优化
Min/Max 优化
索引对 Min/Max 聚集函数有帮助,当然也只对这俩有作用;
SELECT MAX(ID) FROM TBL;
SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_ID:
Will benefit from (DEPT_ID,SALARY) index;
“Using index for group-by”
索引和 JOIN
在 MySQL 中使用 join 会导致嵌套循环,例如下面语句会遍历 POSTS 表找到 Peter 的所有文章,然后根据找到的文章从 COMMENTS 表中找到该文章的索引评论;
SELECT * FROM POSTS,COMMENTS WHERE
AUTHOR=“Peter” AND COMMENTS.POST_ID=POSTS.ID
在简单查询的时候才会使用索引,例如上面的语句不会使用 POST.ID 这个索引;
重新设计 join 中没法进行索引的语句也是非常重要的。
18. 表中存在多个索引
MySQL 中可以存在多个索引:会有索引合并;
SELECT * FROM TBL WHERE A=5 AND B=6:该语句能分别使用在 A 和 B 上的索引,但是在 (A,B) 上建立索引是更好的;
SELECT * FROM TBL WHERE A=5 OR B=6:该语句使用两个独立的索引,但不会使用在(A,B) 上建立的索引
19. 前缀索引
可以在索引最左边一列上建立前缀索引:
ALTER TABLE TITLE ADD KEY(TITLE(20));
需要在 BLOB/TEXT 上建立索引;
能显著的提升效率;
不能被用作覆盖索引;
选择合适的前缀长度是一个问题。
20. MySQL 5.6 新特性
很多优化器上的改进:
这些改进会使你的查询更加自动;
join_buffer_size 有了新的意义,意味着该值大于 32 M,会有比较好的效果;
本文专注于索引设计上的实践,其中最重要的一个是 ICP(索引条件下推)。
21. 理解 ICP
下推一些过滤条件到引擎层:NAME LIKE “%ill%” 不能使用 range 过滤;
更加灵活的覆盖索引:一些过滤在引擎层就完成了;
5.5 之前:在范围内就会读取全部的数据。
22. ICP 举例
SELECT A … WHERE B=2 AND C LIKE “%ill%’
MySQL 5.5 and below:
KEY (B) – Traditional. Using index for range only
KEY (B,C,A) - 覆盖索引,用到的列都在索引中;
MySQL 5.6
KEY(B,C):通过 B 找到数据范围; 会根据 C 读取所有的列查找满足条件的数据;
SELECT * …WHERE A=5 and C=6 ;
KEY (A,B,C):Will scan all index entries with A=5 not all rows。
23. 怎么选择使用哪一个索引?
对于每一个查询动态的选择索引:查询文本中的常量非常重要;
通过下潜到表中,估算每个索引需要问访问的列数;
使用基数统计,这个会分析表的更新。
24. 用 EXPLAIN
EXPLAIN 是一个非常好的查看数据库怎么执行查询的工具:
需要记住真是的执行可能和 EXPLAIN 不同
5.6 中可以使用 json 格式化 EXPLAIN 的结果:
EXPLAIN FORMAT=JSON SELECT * FROM t1 JOIN t2 ON t1.i = t2.i WHERE t1.j > 1 AND t2.j < 3;
25. 索引策略
对于关键的查询建立索引,需要整体来看索引,不要单独的看;
最好的对于 WHERE 和 JOIN 的所有查询条件使用索引,至少也是大部分条件;
多数情况下拓展现有索引,不要新建索引;
作出改变的时候,验证一下性能的影响,把所有的查询都执行一遍。
26. 索引示例
索引要有利于多个查询:
SELECT * FROM TBL WHERE A=5 AND B=6
SELECT * FROM TBL WHERE A>5 AND B=6
KEY (B,A) Is better for such query mix
把最有选择性的条件放在索引第一位;
对于非关键查询,可以不增加索引,索引太多会影响性能。
27. 技巧
假设索引的顺序是 KEY(A,B)
SELECT * FROM TBL WHERE A BETWEEN 2 AND 4 AND B=5:只会用到索引的第一部分;
SELECT * FROM TBL WHERE A IN (2,3,4) AND B=5:会用到整个索引。
增加 FAKE 过滤,例如 KEY(GENDER,CITY),想要只使用一个索引:
SELECT * FROM PEOPLE WHERE CITY=“NEW YORK”:不会用到索引;
SELECT * FROM PEOPLE WHERE GENDER IN (“M”,”F”) AND CITY=“NEW YORK”:会使用索引;
这个技巧对于低选择的列非常有用,例如性别、状态、布尔类型等。
个人理解:对于 key,如果想使用后面的列,要想走索引的话,可以把前面的列全部列出来,当然,前面的列的可能数最好比较小。
文件排序,例如只有 KEY(A,B):
SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5:不会使用索引;
(SELECT * FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT * FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5:会用到索引,排序只会排列 10 行。
联系人
Email: pz@percona.com