数据库索引

背景知识：

B树和B+树：

 

 

 

 

B+树可以理解为是在对最底层的有序链表建立索引（将块中的最大值提取出来作为索引），且还可以对索引继续向上建立索引，从而最终形成了一个树状结构。

 

 

 

 

 

 

索引：索引是帮助数据库进行高效查询的数据结构（常见的索引数据结构如B+树）。索引的使用，使得数据库在执行查询语句时，可以在索引数据结构上进行更高效的查找，而避免使用全表扫描这种低效的查找方式。从而减少定位到目标所需的查找次数，减少了IO的次数（数据库记录存在磁盘中，若数据量大，会需要分多次读取数据到内存来查找目标）。但同时每次插入删除更新数据时，也需要同时更新索引数据结构中的内容。

 

mysql中提供的索引算法有：

B-Tree（所有存储引擎都支持）、哈希索引（仅Memory支持）、空间索引R-Tree（仅MyISAM支持）、全文索引（InnoDB,MyISAM支持）

 

B-Tree索引：

创建索引：CREATE INDEX index_name ON table_name (col_name,...)

对表table_name中的(col_name,...)列创建索引，给该索引取名为index_name。

若只指定了单个列，则创建的是单值索引；若指定了多个列，则创建的是复合索引。

（对于复合索引，判断大小的方式则是根据你创建索引时传进去字段的顺序，先比较第一个字段的大小，如果第一个字段大小一样，再比较第二个字段，以此类推）

删除索引：DROP INDEX index_name ON tbl_name

删除表table_name中名字为index_name的索引

 

例：现有表如下，对其col1,col2建立复合索引（4阶b+树）

 

此时执行查询语句select * from table_name where col1='c' and col2=3

根节点的第一个关键字：c>b

根节点的第二个关键字：c<e，向下走

右子树的第一个关键字：c=c，比较第二个字段，3>2

右子树的第二个关键字：c<d，向下走

中子树的第一个关键字：c=c，比较第二个字段，3=3，找到。

 

 

 

索引失效的情况（对于复合索引a,b,c）：

1. 最左前缀法则

从最左索引列开始的查询，如where a=xx and b=xx and c=xx，where a=xx and b=xx，where a=xx将会使用索引进行高效查询；

不是从最左索引列开始的查询，如where b=xx and c=xx，where a LIKE %xx（以xx为结尾的）将使用遍历的低效方式去查询。

从最左索引列开始，但跳过了中间索引列的查询，如where a=xx and c=xx将使用索引去查询到所有a=xx的数据，在这些数据中使用遍历的方式去找到c=xx的数据（a走索引，c不走索引）；

2. 若某个索引列使用了范围查询，则其右边的查询条件将无法走索引

如where a=xx and b>1 and c=xx将会使用索引去查询到所有a=xx and b>1的数据，在这些数据中使用遍历的方式去找到c=xx的数据（ab走索引，c不走索引）；where a LIKE xx% and b=xx将会使用索引去查询到所有a以xx开头的数据，在这些数据中使用遍历的方式去找到b=xx的数据。

3. in使用索引，not in不使用

where a in ('xx', 'xx')相当于where a='xx' or a='xx'，将会使用索引。

4. or语句只有连接相同索引列时才会使用索引，一旦or连接了不同的列将不会使用索引

where a='xx' or a='xx'，使用索引；where a='xx' or b='xx'，不使用索引。

5. 对索引列进行了运算操作，索引失效。如where substring(a, 3, 2)='xx'

6. 索引列经过类型转换后，将会失效。如where a=123。a为varchar类型，输入的数字将会进行类型转换。应当使用where a='123'。

 

另：覆盖索引：当查询的列都为索引列时，称为覆盖索引，如select a,b from xx where a=xx。此时，可以不需要回表查询，直接就能在索引数据结构中获取到。

 

另：若order by语句符合最左前缀法则，如order by a、order by a, b、order by a, b, c，则可以直接按照B+树的叶子节点的顺序进行返回，不需要进行排序；而对于跳过了中间索引列的语句order by a, c，也可以先根据叶子节点获取到有序的数据（按a,b,c进行的排序，而不是a,c），再对那些a字段相等的数据按c字段进行重新排序。

 

哈希索引：

1. 哈希索引是基于哈希表实现的，查询速度非常快（O(1)）。

2. 哈希索引不支持部分索引列匹配。如对于复合索引a,b,c，查询条件where a=xx and b=xx将不会使用索引，必须全部索引列都用上。（因为哈希索引算法是使用了所有的索引列来计算出哈希值，从而找到该条数据在数组中的位置的）

3. 哈希索引只支持基于==进行比较的查询，无法支持任何范围查询。（哈希表特性）

4. 哈希索引不存在覆盖索引，其无论如何都需要回表查询。（数组中每个Node的key只存了当前节点的hash，没存具体的数据，所有数据都在value指针指向的数据行中）

5. 由于哈希表是无序的，哈希索引对于order by语句没有优化效果。