MySQL深入学习（5）——索引

最新推荐文章于 2024-07-20 20:04:43 发布

常识与偏见

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量186

点赞数

分类专栏： mysql数据库学习文章标签：数据库 MySQL 索引 B+树

本文链接：https://blog.csdn.net/dg19971024/article/details/103833782

版权

mysql数据库学习专栏收录该内容

6 篇文章 0 订阅

订阅专栏

在学习索引之前，必须要先去了解B+树的数据结构以及其相关算法知识，本文中不做详细讲解。可以参考我的另一篇文章y有关于B与B+树的介绍

1. InnoDB存储引擎索引类型

刚开始接触MySQL中的索引时，真的很迷，因为其索引分类真的搞不清，简单列一下索引分类名词：

B+树索引
全文索引
哈希索引
主键索引
唯一索引
普通索引
组合索引
聚集索引（聚簇索引）
非聚集索引（非聚簇索引）
主键索引（后来才知道其实主键索引就是聚簇索引）
辅助索引（辅助索引就是非聚集索引）

反正当时看到这些索引分类名词是真的很晕，一个个看完才知道到底是怎么分类的，以及各类索引的用途区别。

1.1 按照索引组织结构进行的分类（B+树索引、全文索引、哈希索引）

1.1.1 B+树索引

最常见的索引，目前的关系型数据库基本都以B+树的数据结构来管理组织索引和数据，为什么采用B+树？首先要知道一点，数据库读取页中的数据时并不是一行一行的读取，而是直接扫描整个数据页的数据放到内存中。而对于B+树来说，其非叶子结点是不存储数据的，只存储索引，那么这样就能保证在一个数据页的容量之内可以尽可能多的保存索引数据，这样就可以快速的通过在一个索引页中定位到数据的具体范围（在哪个页中），进而减少IO次数。

B+树结构的特点如下：

B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

这些特点就使得B+树非常适合数据库作为数据库索引结构，其具有磁盘读写代价低、查询速度稳定的优点。

为什么B+ 树的查询性能优秀，磁盘读写代价低？

因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree性能高，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

个人理解的话有一个非常形象的比喻来帮助理解为什么B+树的结构能够快速定位到数据页：

（1）其实索引页就是书中的目录页，书中目录那几页其实就是数据库中的索引页，而索引页后面的内容就是数据页。

（2）我们每次看书中的一页内容，其实就相当于数据库每次读取一整个数据页的数据到内存中。读目录页就是读取索引页的索引数据到内存中。

（3）如果要查找某个内容，我们肯定会先去目录页中查找这个内容所在的页码范围。比如说在《MySQL技术内幕》这本书中，我想知道关于B+树索引的知识内容，那么我肯定先去目录翻找。如果第一页目录找不到，那就翻到下一页目录去找。一页纸的大小肯定是有限的，所以如果我们在一页中能够存储较多的目录索引，我们就不需要进行多次翻页操作，以最少的翻页（IO)次数获取最多的索引范围，进而就能快速定位到目标查询内容的位置范围。

（4）为什么B+树的非叶子结点页不存储数据？很容易理解，如果目录中每个标题的内容都紧跟其后，这叫目录吗？在数据库里以这样的方式存储数据一个索引页能放几个索引？放在现实中的一本书里，如果这样干，其实就相当于没有目录页了，翻开书就是正文，如果你想看找某个部分的内容，只有一页一页的翻找了，效率可想而知。

其实，这一块用一个专业名词来说，叫做扇入和扇出。

1.1.2 全文索引

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引（B+树索引），那么匹配文本前几个字符还是可行的（LIKE word%），但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

#创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
#创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX (column_name);

全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

注意：

*对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

*5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引

*在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

*在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

实际上，在真正的项目开发中，对于这种模糊匹配查询，并不会使用MySQL这种关系型数据库，所以全文索引用的极少，一般都会使用solr、elasticsearch等中间件实现，查询性能极高。

1.1.3 哈希索引

在InnoDB存储引擎中是不支持手动创建或者说干预哈希索引的创建的，因为哈希索引是由InnoDB存储引擎内部进行自动管理的，所以说官方文档给出InnoDB的不支持哈希索引也没问题，以为InnoDB存储引擎内部自动维护管理的，无法人为干预是否在表中生成哈希索引。

对于哈希索引的理解其实很简单，其实与Java中的HashMap原理是一样的，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序。

1.2 按照索引字段进行的分类（主键索引、唯一索引、组合索引、普通索引）

注意一点，这几个索引都是B+树索引，但只有主键索引是聚集索引，其余都是非聚集索引。

1.2.1 主键索引

即依据所指定的主键自动建立的索引，可以通过直接在建表的时候指定主键，也可以在建表之后再指定，但注意，主键索引只能存在一个，不能有多个主键索引（并不是说只能指定一个列作为主键索引，主键索引可以作是组合索引）；主键索引的添加删除语句命令如下：（其实语法形式比较多，这里不做全部说明，简单列举一种即可）

#创建表的时候指定主键
CREATE TABLE test  (
  id int(0) NOT NULL,
  name varchar(255),
  age int(0),
  PRIMARY KEY (`id`)
);

#添加主键索引
ALTER TABLE test
ADD PRIMARY KEY (`id`);

#删除主键索引
ALTER TABLE test
DROP PRIMARY KEY;

1.2.2 唯一索引

即要求索引字段在表中值唯一，相当于添加了一个唯一约束，但是值可以为NULL。

方式1：ALTER TABLE `table_name` ADD UNIQUE  [indexName] (`column`)

比如：ALTER TABLE users ADD UNIQUE ( id )

方式2：CREATE UNIQUE INDEX index_name ON table_name (column_name)

比如：CREATE UNIQUE INDEX index_users ON users(id)

创建表的时候直接指定：CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns));

1.2.3 组合索引

组合索引，即指的是由多列字段组成的索引，组合索引实际上包含主键索引、唯一索引、普通索引，因为多列字段可以指定为主键索引、唯一索引、普通索引。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');
(普通索引)
或

create table 'table_name'(col1,col2,col3,col4,primary index col1_col2(col1,col2));

组合索引的结构以下表为实例进行讲解

CREATE TABLE People (
   last_name varchar(50)    not null,
   first_name varchar(50)    not null,
   dob        date           not null,
   gender     enum('m', 'f') not null,
   key(last_name, first_name, dob)
);

那么对应的，其索引存储结构应该如下图所示

图片描述
那么在使用该联合索引进行查找时，首先会依据第一个字段，也就是last_name的值进行查找，然后才是first_name、dob，所以使用联合索引时，必须遵从最左匹配原则才能通过联合索引进行查找。其实另一种理解，联合索引的建立就相当于建立了（last_name)/(last_name,first_name)/(last_name,first_name,dob)三个索引。

在使用联合索引时，where后面跟的过滤条件必须严格按照最左匹配原则，也就是说在where后面的过滤条件必须严格按照（last_name)/(last_name,first_name)/(last_name,first_name,dob)这三种顺序，否则就无法使用联合索引查询。比如


会使用联合索引查询：
select * from People where last_name = '***';
select * from People where last_name = '***' and first_name = '**';
select * from People where last_name = '***' and first_name = '**' and dob = '**';

不会使用联合索引的查询:
select * from People where first_name = '**';
select * from People where first_name = '**' and dob = '**';

其实使用索引时还有另一种现象，叫做覆盖索引，很多时候我们并不是要查询获取每行记录中每个字段的数据，而是抽取部分字段的数据，而如果我们所抽取的部分字段恰好是索引的字段，那么就会发生一个索引覆盖现象。索引覆盖会带来一个好处，因为B+树中真正的全部数据都是放在叶子节点，而非叶子结点只会存放索引数据，当发生索引覆盖时，就不会再向叶子节点进行查询，直接将索引页中的数据返回即可，这样就会减少至少一次IO，对于性能的提升还是很明显的，尤其是在联合索引中，其实应用覆盖索引原理的比较多。这也是为什么SQL语句都建议select具体字段，而不是无脑select *。

1.2.4 普通索引

普通索引就很简单了，就是指索引字段没有任何的约束，值不唯一而且可以为NULL。

1.3 按照数据存储逻辑进行的分类（聚集索引与非聚集索引）

首先，聚集索引又被称为聚簇索引和主索引，而非聚集索引又被称为非聚簇索引和辅助索引，这个要先明确。而且也都是B+树索引。

对于聚集索引和非聚集索引，我们首先明确一点：非聚集索引不存储全部数据，聚集索引才是真正存储全部数据的地方。

聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

*使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的主键索引后，还要到聚集索引中进行查找数据。

*因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。

*聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

而非聚簇索引的主索引和辅助索引的叶子节点的data都是目标数据的主键索引值，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序

非聚簇索引的解释是:索引顺序与数据物理排列顺序无关

常识与偏见

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL深入学习（5）——索引

在学习索引之前，必须要先去了解B+树的数据结构以及其相关算法知识，本文中不做详细讲解。可以参考我的另一篇文章y有关于B与B+树的介绍1. InnoDB存储引擎索引类型刚开始接触MySQL中的索引时，真的很迷，因为其索引分类真的搞不清，简单列一下索引分类名词：B+树索引全文索引哈希索引主键索引唯一索引普通索引组合索引聚集索引（聚簇索引）非聚集索引（非...
复制链接

扫一扫