mysql索引与md5_mysql的索引与优化

转载请注明作者，来源地址。

本文基于mysql5.6与mariadb文档.

1 索引

1.1 概述索引无非就是把一列或一些列的数据的使用一种数据结构独立存储起来,用于快速查询各行.通常这个数据结构是树,主要用到B树.

使用WHERE语句配合=,>,BETWEEN,IN等算子可以查找一个特定的数据,一组特定的数据,一组特定的数据范围.可以用EXPLAIN来确认某个查询是否使用到索引.仅MEMORYRY引擎的数据表支持Hash索引.

1.1 主键索引 / Primary Index / 重要

主键自带一个主键索引.唯一但不能为空.主键索引提供了唯一约束。

在XtraDB / InnoDB表中，所有索引都包含主键作为后缀。因此，在使用该存储引擎时，保持主键尽可能小尤为重要。

1.2 唯一索引 / Unique Index / 重要

背景知识

唯一键自带一个唯一索引,唯一但能为空。唯一索引提供了唯一约束.支持索引前缀

支持复合索引,复合索引的唯一性由整个组合定义.

唯一索引中中的NULL不会被视为重复出现.在SQL中，任何NULL都不等于任何东西，甚至不等于另一个NULL。

补充案例

CREATE TABLE t1 (a INT NOT NULL, b INT, UNIQUE (a,b));

INSERT INTO t1 values (1,1), (2,2);

SELECT * FROM t1;

+---+------+| a | b |

+---+------+| 1 | 1 |

| 2 | 2 |

+---+------+

复合索引

INSERT INTO t1 values (2,1);

SELECT * FROM t1;

+---+------+| a | b |

+---+------+| 1 | 1 |

| 2 | 1 |

| 2 | 2 |

+---+------+

NULL重复

INSERT INTO t1 values (3,NULL), (3, NULL);

SELECT * FROM t1;

+---+------+| a | b |

+---+------+| 1 | 1 |

| 2 | 1 |

| 2 | 2 |

| 3 | NULL |

| 3 | NULL |

+---+------+

1.3 普通索引 / Plain Index / 重要

1.3b 索引前缀 / Index Prefixes / 重要使用col_name(N)语法来对特定的列的前n个单位"字符"或"字节"进行索引.

以字节作为基本单位.在String类型(CHAR,VARCHAR,TEXT)是代表字符的数目(至于多少个字节代表一个字符就看编码).在Binary类型中(BINARY,VARBINARY,BLOB)是代表字节的数目.

如果搜索词超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配.

利用索引前缀可以降低整个索引文件大小(毕竟没有索引所有东西).

对于BLOB或TEXT,必须指定索引长度(即使用索引前缀).最大可以达到1000字节(InnoDB默认最大767字节).

1.3c 复合索引 / Multiple-Column Indexes / 重要

背景知识复合索引就是用于多列的索引。一个索引最多可以包括16列。对于特定的数据类型(TEXT,CHAR,BINARY),可以包含索引前缀(#2.2)。

复合索引对列的顺序敏感。正确的顺序可以加快查询速度。

优化

复合索引的优化部分基本围绕着最左前缀原则,实际的原理和b树有关.构建Index(A,B,C)索引,那么实际上等同于得到了Index(A,B,C),Index(A,B),Index(A).所以我们能单独进行(A),(A,B),(A,B,C)形式的WHERE语句查询.

类似前述全文索引,使用通配符后缀的形式才能使用到索引.形如'f%'.

假如有Index(A,B,C),而我们又想查询(A,C)的内容,为了使用到这个索引,我们不得不把这个查询转换为(A,B,C)的形式.这时候可以手动把B的所有种类的数据列举出来,再使用IN算子.

sql SELECT * FROM tbl_name WHERE A="China" AND B IN ("male","female") AND C="USA"引擎能为我们自动优化WHERE语句下的顺序符合最左前缀的形式(譬如把(A,C,B)转换为(A,B,C)).

补充案例

在一些情况下,可以建一个单列索引作为复合索引的替代品,因为前者的建立成本更低,查询速度也更快.

譬如我们有一个Index(A,B),我们不妨构建一列C=Hashed(A,B).假如能保证C很短且唯一,那么不妨为构建Index(C)作为Index(A,B)的替代品.

这时候查询的代码为:

SELECT * FROM tbl_name

WHERE hash_col=MD5(CONCAT(val1,val2))

AND col1=val1 AND col2=val2;

1.4 全文索引 / FULLTEXT Indexes / 重要

背景知识全文索引用于全文本数据的搜索.仅在InnoDB和MyISAM引擎下支持.仅支持CHAR,VARCHAR,TEXT列.使用这种索引不支持索引前缀的形式(全文都要被记录).

全文索引可以在CREATE TABLE的时候进行,也可以在使用ALTER TABLE或CREATE INDEX添加.

使用MATCH (col1,col2,...) AGAINST (expr [search_modifier])语法.Match一列或数列,AGAINST查找特定String.

原理是分词,默认是按照非英文,非数字来切分(所以中文句子会被切成一个个字即3-4字节).3种查询模式:

Natural Language:

返回包含某些用''包围起来的字符串的行的结果.停用词表(stopwords)的单词并没有作为索引,因此不能返回.

https://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

Boolean:

可以使用特定规则的查询语言来完成更复杂的查询,这些查询语句会包含一些特定的算子.常见例子是:'+MySQL -YourSQL'(出现MySQL但不出现YourSQL).

可以使用通配符:*,但只能接在后面.查询方式形如'mysq*'.

https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

(Blind) Query Expansion:

拓展模式,类似于Natural Language,在其功能上进行了拓展.用于返回用户提供的String及这个String的关联String所在的行(譬如mysql,database,db2).原理是根据用户提供的String进行搜索,之后二次搜索这个String的关联String. 优化:由于盲查询扩展往往会通过返回不相关的文档而显着增加噪声，因此仅在搜索String较短时才使用它。

https://dev.mysql.com/doc/refman/5.6/en/fulltext-query-expansion.html

优化对于大型数据集,添加没有全文索引的TABLE之后再新建索引比添加带全文索引的TABLE更快.

全文索引在只返回文档id或文档匹配排位效率最高。

全文索引返回匹配得分顺序(降序/DESC)(限制数量/LIMIT)时假如没有额外的WHERE语句效率最高。

1.5 空间索引 / Spatial Indexes / 不重要MyISAM使用R树。

InnoDB使用B+树。

1.6 哈希索引 / Hash Indexes / 重要

仅MEMORY引擎的数据表支持Hash索引.

关于MEMORY引擎:

背景知识仅用于相等比较(=,<=>算子).不能用于范围比较.故适用于单值查询的场景,譬如是Key-Value.

也不能用于聚合查询(ORDER BY语句).

也不能确认出现在两个值之间的行的数量(类似于第一点,Hash Index不能使用范围优化器).

仅能查询整个key(在B树索引中支持最左前缀原则,哈希索引则不能).

1.7 自适应哈希索引 / Adaptive Hash Index / 一般

背景知识

自适应哈希索引可以让InnoDB表现得更像内存数据库,而不会牺牲事务功能或可靠性

自适应哈希索引(以下简称自适应)实际上是InnoDB的B树索引的索引.自适应能够在根据曾出现的搜索关键字,利用B树索引的key的前缀来建立索引(索引可能是任何长度).

优化有时候,构建和维护自适应所花费的时间远比自适应提升查询效率所节省的时间更长.

在多个并发请求下,自适应也有可能会成为被抢占的资源.

使用LIKE运算符和％通配符的查询也不会受益(类似之前的Hash索引).

在以上场合下,可以考虑关闭自适应.由于很难预先预测自适应是否适用于特定的系统和工作负载，因此在启用/禁用该索引前应该使用基准测试。

2. 原理

2.1 二叉查找树 / 无关

平均状态下查插删时间复杂度为O(log2 n).假如这棵树是最不平衡的状态(数列有序，树退化成线性表),查插删等操作的时间复杂度退化到O(n).

2.2 AVL树 / 无关

一种自平衡二叉查找树.在AVL树中，任一节点对应的两棵子树的最大高度差为1，因此它也被称为高度平衡树。

反正就是上面那种的进阶版,加入了一些机制保证这棵二叉查找树能够维持完全平衡的状态.

2.3 红黑树 / 无关

红黑树相对于AVL树来说，牺牲了部分平衡性以换取插入/删除操作时少量的旋转操作，整体来说性能要优于AVL树。

但由于AVL树总能维持完全平衡的状态,所以读取操作性能会比红黑树更强(层数相比红黑树低一层).所以可以认为红黑树是一种更符合现实需要的替代AVL树的方案.

2.4 B树 / 重要

是一种广义的二叉查找树(他有可能不是二叉的,2-3树``,2-3-4树都有可能,这个取决于树的阶数).

B树适用于读写相对大的数据块的存储系统，例如磁盘。B树减少定位记录时所经历的中间过程，从而加快存取速度。B树这种数据结构可以用来描述外部存储。这种数据结构常被应用在数据库和文件系统的实现上。正是因为这个特性,他适用于数据库索引中.

背景知识在B树中，内部(非叶子)节点可以拥有可变数量的子节点(数量范围预先定义好)。当数据被插入或从一个节点中移除，它的子节点数量发生变化。

B树中每一个内部节点会包含一定数量的键，键将节点的子树分开。例如，如果一个内部节点有3个子节点(子树)，那么它就必须有两个键： a1 和 a2 。左边子树的所有值都必须小于 a1 ，中间子树的所有值都必须在 a1 和a2 之间，右边子树的所有值都必须大于 a2 。

通常，键的数量被选定在 d 和 2d 之间。其中 d 是键的最小数量，d+1 是树最小的度或分支因子(分支) 。

一个节点的分支(或子节点)的数量会比存储在节点内部键值的数量大1。在 2-3 B树中，内部节点将会存储1个键值(带有2个子节点)或2个键值(带有3个子节点)。一个B树有时会被描述为(d+1)-(2d+1) 或简单地使用最高分支 (2d+1) 。

一个Ｂ树通过约束所有叶子节点在相同深度来保持平衡。深度在元素添加至树的过程中缓慢增长，而整体深度极少地增长，并导致所有叶子节点与根节点距离加1。

2.5 B+树 / 重要

B+树是B树的一种变体.B+树元素自底向上插入，这与二叉树恰好相反.与B树不同,B+树的仅在叶子节点里存储着数据指针,所有的非叶子节点仅存储键.

实际存储的数据被组织为升序链表的形式，其中一个键指向的数据有指针指向下一个键指向的数据.

知识背景

| S.NO | B TREE | B+ TREE | | :--: | :----------------------------------------------------------: | :----------------------------------------------------------: | | 1. | All internal and leaf nodes have data pointers | Only leaf nodes have data pointers | | 2. | Since all keys are not available at leaf, search often takes more time. | All keys are at leaf nodes, hence search is faster and accurate.. | | 3. | No duplicate of keys is maintained in the tree. | Duplicate of keys are maintained and all nodes are present at leaf. | | 4. | Insertion takes more time and it is not predictable sometimes. | Insertion is easier and the results are always the same. | | 5. | Deletion of internal node is very complex and tree has to undergo lot of transformations. | Deletion of any node is easy because all node are found at leaf. | | 6. | Leaf nodes are not stored as structural linked list. | Leaf nodes are stored as structural linked list. | | 7. | No redundant search keys are present.. | Redundant search keys may be present.. |

3 场景

3.1 主键优化主键自带一个主键索引(b+树),可以提升查询效率,此外还有提供唯一约束(主键都是唯一的).

在InnoDB引擎下,数据表的物理存储是根据主键(主键索引)的顺序来存储的.所以来按顺序查询数据会更具效率.

假如一个数据表没有主键,那么你可以人为为它设置一个自增列作为主键.

3.2 外键优化

假如你一个数据表有好几个列,其中有一些列是极少被查询到,那么你可以把这个列以及主键新建一个独立的表(主键作为对应的外键).这样子当你在原表需要查询数据但是不查询到这个列的情况下能够加速你的查询.

因为数据库物理存储是会把同一行(相近列)存储在附近的,而数据库物理存储又以块的形式存储.当我们查询的时候,尽可能少读取块能提高效率.所以一个块装少一些极少被查询的列而装多一些行会更好.

4 总结

| | B树索引 | Hash索引 | | ------------------ | ------------------------ | ---------------------------- | | 适用硬件 | 硬盘(可以分块的存储器) | 内存(支持随机读取的存储器) | | 支持引擎 | InnoDB，MyISAM，MEMORY | MEMORY(默认) | | 查询平均时间复杂度 | O(logN) | O(1) | | 适用于范围查询 | 支持 | / | | 适用于等值查询 | 支持 | 支持(效率更高) | | 适用最左前缀原则 | 支持 | / |

| | 主键索引 | 唯一索引 | 普通索引 | 全文索引 | | ------------ | -------- | -------- | -------- | -------------- | | 原理 | B树 | B树 | B树 | 分词与倒排索引 | | 唯一约束 | 支持 | 支持 | / | / | | 空值约束 | 支持 | / | / | / | | 支持复合索引 | / | 支持 | 支持 | / | | 支持索引前缀 | / | 支持 | 支持 | / |