关系数据库MySQL索引学习

MySQL-索引

1、索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

如下面的示意图所示（通过二叉树举例）：

左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在盘上也并不是一定物理相邻的)。

为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

2、索引的优势与劣势

优势：

1. 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗。

劣势：

1. 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2. 虽然索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行INSERT、DELETE、UPDATE。因为更新表时MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3、索引的数据结构

BTREE结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特征如下：

• 树中每个节点最多包含m个孩子。
• 除根节点与叶子节点外，每个节点至少有【ceil(m/2)】个孩子。（ceil为向上取整）
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成，其中【ceil(m/2)】<= n <= m+1。

 

以5叉BTree为例，key的数量：公式推导【ceil(m/2)】<= n <= m+1。所以2<= n <= 4。当n > 4时，中间节点分裂到父节点，两边节点分裂。

示例：

插入C N G A H E K Q M F W L T Z D P R X Y S数据为例。

1. 插入前4个字母 C N G A

2. 

3. 插入H，n>4，中间元素G字母向上分裂到新的节点

4. 

5. 插入E K Q不需要分裂

6.  

   

7. 插入M，中间元素M字母向上分裂到父节点G

8. 

9. 插入F W L T 不需要分裂

10. 

11. 插入Z，中间元素向上分裂到父节点中

12.  

    

13. 插入D，中间元素D向上分裂到父节点中。然后插入P R X Y不需要分裂

14.  

    

15. 最后插入S ，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

16. 

B+TREE结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
• B+Tree的叶子节点保存所有key的信息，依key大小顺序排列。
• 所有的非叶子节点都可以看作是key的索引部分。

MySQL中的B+TREE

MySQL索引数据结构对经典的B+Tree进行了优化。

在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的B+Tree索引结构示意图：

4、索引的分类

1. 主键索引（PRIMARY KEY）：索引列的值必须是唯一的，且不能未NULL。
2. 唯一索引（UNIQUE）：索引列的值必须唯一（除了NULL以外，NULL可以出现多次）。
3. 普通索引（INDEX）：索引值可以出现多次。
4. 全文索引（FULLTEXT）
5. 复合索引：一个索引包含多个列。
6.  

5、索引的语法

#创建索引

CREATE INDEX index_name ON table_name(column_list);

#查看索引

SHOW INDEX FROM table_name;

#删除索引

DROP INDEX index_name ON table_name;

 

#ALTER命令

ALTER TABLE table_name ADD PRIMARY KEY(column_list);

#该语句添加一个主键索引，这意味着索引值必须是唯一的，且不能未NULL

ALTER TABLE table_name ADD UNIQUE index_name(column_list);

#该语句添加一个唯一索引，这意味着索引值必须是唯一的（除了NULL以外，NULL可以出现多次）

ALTER TABLE table_name ADD INDEX index_name(column_list);

#添加普通索引，索引值可以出现多次

ALTER TABLE table_name ADD FULLTEXT index_name(column_list);

#该语句指定了索引为FULLTEXT，用于全文索引

 

6、索引的设计与使用原则

索引的设计原则

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。

• 对查询频次较高,且数据量比较大的表建立索引。
• 索引字段的选择,最佳候选列应当从wher子句的条件中提取,如果 where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组

合。

• ·使用唯一索引,区分度越高,使用索引的效率越高。
• ·索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML

操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗。另外索引过多的话, MySQL

也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价。

• ·使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比

较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升 MysQ访问索引的/效率。

• ·利用最左前缀,N个列组合而成的组合索引,那么相当于是创建了N个索引,如果查询时whee子句中使用了组成该索引的前几个字段,那么这

条查询SQL可以利用组合索引来提升查询效率。

 

索引的使用原则

创建索引优化数据库查询最有效的方式之一，创建索引可以提高查询效率，但是创建索引不一定是提高查询效率，创建索引并且合理的利用索引才可以提高查询效率。

如何避免索引失效：

• 全值匹配

对索引中所有列都指定具体值该情况下，索引生效，执行效率高

• 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列

 

举例：如果对表中A、B、C三个列创建了组合索引

索引生效：WHERE A

WHERE A AND B

WHERE A AND B AND C

 

索引失效：WHERE A AND C(只有A走索引)

WHERE B

WHERE B AND C

WHERE C

 

意思是对A、B、C三个字段创建的组合索引，其实是创建了三个索引，分别是(A)、(A、B)、(A、B、C)

• 范围查询右边的列，不会使用索引
•  

举例：如果对表中A、B、C三个列创建了组合索引

索引失效：WHERE A = ' ' AND B > 1 AND C = ' '(只有A、B走索引)

意思是对A、B、C三个字段创建的组合索引，当WHERE条件有范围查找时，范围查找条件后的查找条件将不再使用索引

• 不要在索引列上进行运算操作，否则索引将失效

举例：如果对表中A、B、C三个列创建了组合索引

索引失效：WHERE substring(A,3,2) = ' '(对索引列进行了截取)

• 字符串不加单引号(同理，数值型加单引号)，造成索引失效。

举例：如果对表中A、B、C三个列创建了组合索引

索引生效：WHERE A = 'abc'

索引失效：WHERE A = abc(字符串未加单引号)

• 尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列))，避免SELECT *。

举例：如果对表中A、B、C三个列创建了组合索引

 

explain SELECT * FROM table_name WHERE A = ' ';

使用explain查询sql获取的信息中，Extra字段值为 Using index condition

 

explain SELECT A,B,C FROM table_name WHERE A = '';

使用explain查询sql获取的信息中，Extra字段值为 Using where;Using index

 

explain SELECT A,B,C,D FROM table_name WHERE A = '';

使用explain查询sql获取的信息中，Extra字段值为 Using index condition

 

explain DELETE FROM table_name WHERE A = '';

使用explain查询sql获取的信息中，Extra字段值为 Using where

 

Extra值的类型：

Using index ：使用覆盖索引的时候就会出现

Using where ：在查询使用索引的情况下，需要回表去查询所需数据

Using index condition ：查询使用了索引，但是需要回表查询数据

Using where;Using index ：查询使用了索引，并且需要的数据在索引中能找到，所以不需要回表查询

 

• 用OR分割开的条件，如果OR前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

举例：如果对表中A、B、C三个列创建了组合索引

索引失效：WHERE A = ' ' OR D = ' '(OR前列有索引,后列没有索引,将会对全表进行扫描)

索引生效：WHERE A = ' ' AND D = ' '(换成AND,A将会走索引)

 

• 以%开头的LIKE模糊查询，索引将失效(如果仅仅是尾部模糊查询，索引不会失效。如果是头部模糊查询，索引失效。)

举例：如果对表中A、B、C三个列创建了组合索引

索引生效：SELECT * FROM table_name WHERE A LIKE 'abc%'

索引失效：SELECT * FROM table_name WHERE A LIKE '%abc'

SELECT * FROM table_name WHERE A LIKE '%abc%'

解决方案：通过覆盖索引来解决

SELECT A,B,C FROM table_name WHERE A LIKE '%abc%'(通过覆盖索引，LIKE字段索引生效)

 

• 如果MySQL评估使用索引比全表扫描更慢，则不使用索引（可以忽略此条）

举例：如果对表中A列创建了索引，表中100条数据中，99条A的值为'abc',1条A的值为'def'

索引生效：SELECT * FROM table_name WHERE A = 'def'(查找索引后，索引回表查询一条数据)

索引失效：SELECT * FROM table_name WHERE A = 'abc'(查找索引后，索引回表查询99条数据,几乎是查找索引后又全表扫描，此时全表扫描更快，所以不走索引)

 

• is NULL，is NOT NULL 有时索引失效(同上原理，MySQL评估使用索引比全表扫描更慢，则不使用索引（可以忽略此条）)

举例：如果对表中A列创建了索引，表中100条数据中，99条A的值为NULL,1条A的值为'abc'

索引生效：SELECT * FROM table_name WHERE A is not NULL(查找索引后，索引回表查询一条数据)

索引失效：SELECT * FROM table_name WHERE A is NULL(查找索引后，索引回表查询99条数据,几乎是查找索引后又全表扫描，此时全表扫描更快，所以不走索引)

 

• IN，NOT IN 有时索引失效(同上原理，MySQL评估使用索引比全表扫描更慢，则不使用索引（可以忽略此条）)

举例：查询索引字段值有100个

索引生效：IN('a','b','c')

索引失效：NOT IN('a','b','c')

这只是举例，一般情况下的表数据，基本也是此类情况，所以尽量不要用NOT IN

 

• 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引

举例：创建复合索引A、B、C

就相当于创建了索引:

A

A、B

A、B、C

创建单列索引A、B、C

就相当于创建了索引:

A

B

C

数据库会选择一个最优的索引来使用，如果是三个单列索引，则并不会使用全部索引。

附带一张联合索引图：

文档：MySQL-索引学习笔记.note
链接：http://note.youdao.com/noteshare?id=542dfc7464a91cd2d9f1bd5ef18cf3b7&sub=D510368512C545DF99CF95EF1114E667