MySQL 索引的使用

索引（index）是帮助MySQL高效的获取数据的数据结构

简介

索引 是将关键字数据以某种数据结构的方式存储到外存，用于提升数据的检索性能；

索引既有逻辑上的概念，更是一种物理存储方式，且事实存在、需要耗费一定的储存空间

索引的原理大致概括为以空间换时间

索引数据结构分类

• B-TREE
• HASH

引擎支持的索引类型

支持/不支持（Y/N）

引擎	事务	行级别锁定	B-tree索引	Full-text索引（全文检索）	Hash索引
InnoDB	Y	Y	Y	Y	N
MyISAM	N	Y	Y	Y	N
Memory	N	Y	Y	N	Y
NDB	Y	Y	N	N	Y
Archive	N	Y	N	N	N

索引的优缺点

优点：

• 加快数据的检索速度
• 通过建立唯一索引，保证数据的唯一性
• 帮助服务器避免排序和临时表
• 随机I/O变成顺序I/O
• 可以加速表和表之间的连接，特别是实现数据完整性方面特别有意义

缺点：

• 创建索引和维护索引需要很多的时间，这种时间随着数据的增加而增加
• 如果一个数据创建了一个索引，那么如果增删改这和数据，对应的索引也要进行动态修改，降低了sql执行的效率
• 需要占用物理存储空间。

索引的选取原则：

适合最为索引的：

• 离散度高的
• 占用空间少的
• 经常使用where进行查询的、分组字段、排序字段、两个表的连接字段

不适合：

• 更新频繁的
• 不会出现在where子句中的

HASH索引

哈希表简介

哈希表（Hash table，也叫 散列表 ）

​ 是根据关键码值(Key, value)而直接进行访问的数据结构。它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做 散列函数 ，存放记录的数组叫做 散列表 。

哈希表存储

​ 哈希表将Key通过一个固定的算法函数（哈希函数）转换成一个整型数字，然后就将该数字对数组长度进行取余，取余结果就当作数组的下标，将value存储在以该数字为下标的数组空间里。

index = HashCode (Key) % Array.length

哈希表访问

计算哈希值取余在相应位置进行寻找

哈希碰撞

通俗的说，可能有很多的值进行取余运算后结果相同，他们计算的hash值是相同的。所以它们的映射在同一个位置。

解决办法：

在相同的位置用链表进行存储，

MySQL中的hash索引

Hash 索引比较的是进行 Hash 运算之后的 Hash 值，只能用于等值的过滤，不能用于基于范围的过滤。经过相应的 Hash 算法处理之后的Hash 值的大小关系，并不能保证和Hash运算前完全一样，数据库自然也无法利用索引的数据来避免任何排序运算

优点：

Hash效率高，查找快

缺点：

• Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
• Hash 索引无法被用来避免数据的排序操作。
• Hash 索引不能利用部分索引键（组合索引）查询。
• Hash 索引在任何时候都不能避免表扫描。
• Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

B-Tree索引

BTree 索引是 MySQL 数据库中使用最为频繁的索引类型。大多数索引（如 PRIMARYKEY,UNIQUE,INDEX和FULLTEXT）都是 以BTREE形式存储。

B-Tree索引可以被用在像=,>,>=,<,<=和BETWEEN这些比较操作符上,而且还可以用于LIKE操作符,只要它的查询条件是一个不以通配符开头的常量。

BTree平衡多路查找树

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

数据存储页的概念

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，

在MySQL中可通过如下命令查看页的大小

show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

B-Tree结构特点

一棵m阶的B-Tree有如下特性：

阶数 表示 此树的节点 最多 有多少个孩子结点（子树），一般用字母 M 表示阶数

• 每个节点最多有m个孩子。
• 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
• 若根节点不是叶子节点，则至少有2个孩子
• 所有叶子节点都在同一层，且不包含其它关键字信息
• 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
• 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
• ki(i=1,…n)为关键字，且关键字升序排序。
• Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。

BTree用作数据库索引优点：

1. 优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；
2. 尽可能少的磁盘 IO，加快了检索速度；
3. 可以支持范围查找

如下就是阶数为3的B树

BTree分析

为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。

如上图查找29只需要三次磁盘IO操作

从上图可以看出：

BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

所以我们引出了B+TREE

B+TREE

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+Tree优化思想

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于BTree有几点不同：

1. 非叶子节点只存储键值信息
2. 所有叶子节点之间都有一个链指针
3. 数据记录都存放在叶子节点中

B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

B+Tree的优点

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。 也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

数据库中，B+Tree的高度一般都在2 ~ 4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作

数据库中的B+Tree索引分类：

• 聚集索引（clustered index）：叶子节点存放的是整张表的行记录数据
• 辅助索引（secondary index）：叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键，当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据

索引最终选择B+树的原因

• hash很快，但每次IO只能取一个数
• AVL和红黑树，在大量数据的情况下，IO操作还是太多
• B树每个节点内存储的是数据，因此每个节点存储的分支太少
• B+节点存储的是索引+指针(引用指向下一个节点)，可以存储大量索引，同时最终数据存储在叶子
• 节点，并且有引用横向链接，可以在2-3次的IO操作内完成千万级别的表操作。
• 建议索引是是自增长数字，这样适合范围查找

索引分类

MySql中索引可以分为以下几类：

​ 普通索引（单列索引）

​ 复合索引（组合索引）

​ 唯一索引

​ 主键索引

​ 全文索引

查看索引

Show index|keys from 表名；

创建索引

普通索引（单列索引）：

单列索引是最基本的索引，它没有任何限制。

直接创建索引

CREATE INDEX index_name ON table_name(col_name);

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

创建表的时候同时创建索引

CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` varchar(255) NOT NULL , 
PRIMARY KEY (`id`), 
INDEX index_name (title(255))
)

删除索引

DROP INDEX index_name ON table_name; 
或者
alter table `表名` drop index 索引名;

复合索引：

复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引

的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

创建一个复合索引

create index index_name on table_name(col_name1,col_name2,...);

修改表结构的方式添加索引

alter table table_name add index index_name(col_name,col_name2,..);

基于(列Ａ，列Ｂ)两列创建索引：

• 可应用索引的情况：A;AB：BA两列结合;
• 不应用索引的情况：Ｂ

唯一索引：

唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值。

创建单个索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

创建多个索引

CREATE UNIQUE INDEX index_name on table_name(col_name,...);

修改表结构

单个

ALTER TABLE table_name ADD UNIQUE index index_name(col_name);

多个

ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);

如果在已经有数据的表上添加唯一性索引的话：

如果添加索引的列的值存在两个或者两个以上的空值，则不能创建唯一性索引会失败。（一般在创建表的时候，要对自动设置唯一性索引，需要在字段上加上 not null）如果添加索引的列的值存在两个或者两个以上的null值，还是可以创建唯一性索引，只是后面创建的数据不能再插入null值 ，并且严格意义上此列并不是唯一的，因为存在多个null值。

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

创建表时添加

CREATE TABLE `news` ( 
`id` int(11) NOT NULL AUTO_INCREMENT , 
`title` varchar(255) NOT NULL , 
`content` varchar(255) NULL , 
`time` varchar(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`) 
)

创建表后添加

alter table tbl_name add primary key(col_name);

全文检索

在一般情况下，模糊查询都是通过 like 的方式进行查询。但是，对于海量数据，这并不是一个好办法，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情,所以 like 进行模糊匹配性能很差。

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

CREATE FULLTEXT INDEX index_fulltext ON table_name(col_name)

目前只有char、varchar，text 列上可以创建全文索引

fulltext索引配合match against操作使用，而不是一般的where语句加like。

SELECT * FROM tablename 
WHERE MATCH(column1) AGAINST(‘aaa’,‘ddd′)

这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来

mysql 默认全文索引的最小单词是四个字符

可以通过ft_min_word_len或者innodb_ft_min_token_size设置，配置在文件/etc/my.cnf中

删除索引

DROP INDEX 索引名 ON 表名；

通过主键约束自动添加的主键索引不能用DROP INDEX语句删除，但通过唯一约束自动创建的唯一索引可以这么删除，但是删除后唯一约束也被删除了。

索引失效

• 在where后使用or，导致索引失效（尽量少用or）
• 使用like ，like查询是以%开头
• 复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 使用in导致索引失效
• DATE_FORMAT()格式化时间，格式化后的时间再去比较，可能会导致索引失效。
• 对于order by、group by 、 union、 distinc 中的字段出现在where条件中时，才会利用索引！

有效：

SELECT * FROM tbl_name 
WHERE key_col LIKE 'Patrick%'; 

SELECT * FROM tbl_name 
WHERE key_col LIKE 'Pat%_ck%';

无效：

SELECT * FROM tbl_name 
WHERE key_col LIKE '%Patrick%'; 


SELECT * FROM tbl_name 
WHERE key_col LIKE other_col;