MySQL 索引

索引

概述

索引(Index) 是帮助 MySQL 高效获取数据的数据结构.

索引的本质：索引是数据结构，可以理解为"排好序的快速查找结构"，满足特定查找算法。

优点

  1. 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
  2. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。
  4. 在分组和排序操作时，可以显著减少时间。

缺点

  1. 创建索引和维护索引需要消耗时间，随着数据量的增加，所消耗的时间也会增加。
  2. 索引需要占用 磁盘空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间。
  3. 虽然大大的提高了查询速度，同时却会降低更新表的速度。因为在 RUD 操作时，需要动态维护索引。

常见索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

术语"聚簇"表示数据行和相邻的键值聚簇的存储在一起。

特点：
  1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
    • 页内的记录是按照主键的大小顺序排成一个单向链表。
    • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顶序排成一个双向链表。
  2. B+树的叶子节点存储的是完整的用户记录。
    所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的 B+ 树称为聚簇索引，所有完整的用户记录都存放在这个聚族索引的叶子节点处。这种聚簇索引并不需要我们在 MySQL 语句中显式的使用 INDEX 语句去创建，InnoDB 存储引擎会自动的为我们创建聚簇索引。

优点：
  • 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索更快；
  • 聚簇索对于主键的排序查找和范围查找速度非常快；
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 IO 操作。
缺点：
  • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB:表，我们一股都会定义一个自增的 ID 列为主键；
  • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新；
  • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据
限制：
  • 对于MyQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引.
  • 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚族索引。一般情况下就是该表的主键。
  • 如果没有定义主键，Innodb会选择非空的唯一索引代替。如果没有这样的索引，Innodb会隐式的定义一个主键来作为聚簇索引。
  • 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序 ID ,而不建议用无序的ID ,比如 UUID、MD5、HASH、字符串列作为主键无法保正数据的顺序增长。

二级索引（辅助索引，非聚簇索引）

因为这种按照非主键列建立的 B+ 树需要一次回表操作才可以定位到完整的用户记录，所以这种 B+ 树也被称为二级索引（英文名 secondary index ),或者辅助索引。由于我们使用的是 c2 列的大小作为 B+ 树的排序规则，所以我们也称这个 B+ 树是为 c2 列建立的索引。

image

与 B+ 树与聚簇索引有几处不同：

  • 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：
    • 页内的记录是按照c2列的大小顶序排成一个单向链表。
    • 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表，
  • B+树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键 这两个列的值。
  • 目录项记录中不再是 主键+页号 的搭配，而变成了 c2列+页号 的搭配，
回表操作：

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树！

小节：

  • 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
  • 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索
    目录提供数据检索。
  • 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和 c3 列的大小进行排序，这个包含两层含义：

  • 先把各个记录和页按照c2列进行排序。
  • 在记录的c2列相同的情况下，采用c3列进行排序
示意图：

images

注意点：
  • 每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。
  • B+树叶子节点处的用户记录由 c2、c3 和主键 c1 列组成。
  • 建立联合索引只会建立如上图一样的1棵B+树。
  • 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

InnoDB 的 B+ 树索引的注意事项

根页面位置万年不动

  • 每当为某个表创建一个B+树索引（聚簇索不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
  • 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
  • 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索列的值)的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

内节点中目录项纪录的唯一性

一个页面最少存储 2 条纪录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢？那就是目录层级非常非常非常多，而且最后的那个存放真实数据
的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录？所以IoDB的一个数据页至少可以存放 两条记录。

MyISAM 中的索引方案

B 树索引适用存储引擎如表所示：

索引/存储引擎 MyISAM InnoDB Memory
B-Tree 索引 支持 支持 支持

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。InnoDB 和 MyISAM 默认的索引是 B-tree 索引；而 Memory 默认的索引是 Hash 索引。

MyISAM 引擎使用 B+Tree 作为索引结构，叶子节点的 data 域存放的是数据记录的地址。

MyISAM 索引的原理

我们知道 InnoDB 中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

  • 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小挂序，所以我们并不能在这些数据上使用二分法进行查找
  • 使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的幼叶子节点中存储的不是完整的用户记录，而是主键值+数据记录地址的组合。

image

同样也是一棵 B+Tree ,data 域保存数据记录的地址。因此，MylSAM 中索引检索的算法为：首先按照 B+Tree搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

MyISAM 与 InnoDB 关于索引的对比

MylSAM 的索引方式都是“聚簇”的，与引nnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

  1. 在IoDB存储引擎中，我们只需要根据主键值对聚族索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味若MyISAM中建立的索引相当于全部都是二级索引。
  2. InnoDB的数据文件本身就是索引文件，而MylSAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
  3. InnoDB的非聚簇索引datai域存储相应记录主键的值，而MylSAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
  4. MyISAME的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
  5. InnoDB 要求表必须有主键(MyISAM可以没有)。如果没有显式指定，则 MySQL 系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为loDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

索引的代价

  • 空间上的代价：

    每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的 B+ 树由许多数据页组成，那就是很大的一片存储空间。

  • 时间上的代价：

    每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而目 B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

索引的声明与使用

索引分类

MySQL 的索引包括 普通索引、全文索引 、单例索引、多列索引 和 空间索引等。

  • 从 功能逻辑 上说，索引分为 4 种：普通索引、唯一索引、主键索引、全文索引；
  • 从 物理实现 上说，索引分为 2 种：聚簇索引、非聚簇索引；
  • 从 作用字段个数 上说，索引分为 2 种：单例索引、联合索引。
普通索引

创建普通索引的时候，不附加任何限制条件，只适用于提高查询效率。可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束决定。

唯一索引

使用 UNIQUE 参数 可以设置索引为唯一性索引，在创建唯一性索引的时候，限制该索引的值必须是唯一的，但允许有空值。在一个表里面可以有多个唯一索引。

主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE,一张表里最多只有一个主键索引。

Why?这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

单例索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

多例（组合、联合）索引

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和 gender.上建立一个多列索引idx_id_nane_gender,只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引

全文索引（也称全文检索）是目前 搜索引擎 使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能的筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

使用参数 FULLTEXT 可以设置索引为全文索引。全文索引只能创建在 CHAR、VARCHAR 或 TEXT 类型上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

小结：不同的存储引擎支持的索引类型也不一样。
  • InnoDB：支持 B-tree、Ful-text 等索引，不支持 Hash 索引；
  • MylSAM：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  • Memory：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
  • NDB：支持 Hash 索引，不支持 B-tree 、Full-text 等索引；
  • Archive：不支持 B-tree、Hash、Ful-text 等索引；

创建索引

MySQL支持多种方法在单个或多个列上创建索：在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。

建表时创建（隐式创建）

隐式的方式创建索引，在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关索引。

CREATE TABLE dept(
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(20)
)

CREATE TABLE emp(
emp id INT PRIMARY KEY AUTO_INCREMENT,
emp name VARCHAR (20) UNIQUE,
dept id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id)REFERENCES dept(dept_id)
);
显示的创建
CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
  • UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引，全文索引和空间索引；
  • INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
  • index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认 col_name 为索引名；
  • col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
  • length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
  • ASC 或 DESC 指定升序或者降序的索引值存储。

在已经存在的表上创建索引

使用 ALTER TABLE 语句创建索引：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
例子：
-- 创建一个普通索引
ALTER TABLE book ADD INDEX idx_bkname( book_name(30));
-- 使用 SHOW INDEX 语句查看表中的索引
SHOW INDEX FROM boot \G
使用 CREATE INDEX 创建索引：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name on table_name (col_name [length]) [ASC | DESC]
列子：
CREATE INDEX idx_bk_name ON book(book_name);

删除索引

添加 AUTO_INCREMENT 约束字段的唯一索引不能被删除。

提示：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

使用 ALTER TABLE 删除索引：
ALTER TABLE table_name DROP INDEX index_name;
使用 DROP INDEX 语句删除索引：
DROP INDEX index_name on table_name;
posted @ 2022-09-13 22:48  李小龙他哥  阅读(63)  评论(0)    收藏  举报