mysql索引

MySQL索引是一种用于提高数据库查询性能的数据结构。它是按照特定的算法和规则创建的，可以加速数据库的查询操作。索引以一种类似于字典的方式存储数据，并提供快速的数据检索能力。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

简化了index_demo表的行格式示意图

record_type ：记录头信息的一项属性，表示记录的类型，0表示普通记录、2表示最小记 录、3表示最大记录

next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用 箭头来表明下一条记录是谁。

各个列的值：这里只记录在 index_demo 表中的三个列，分别是 c1 、c2和c3。

其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息

常见索引概念

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

1. 聚簇索引

聚簇索引是一种数据存储方式：在 InnoDB 中，聚簇索引是通过将表的数据存储在按照索引键值排序的 B+ 树结构中来实现的。 B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起， 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

特点： 1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义： 页内的记录是按照主键的大小顺序排成一个 各个存放 单向链表。 用户记录的页也是根据页中用户记录的主键大小顺序排成一个 双向链表。 存放 目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表。

2. B+树的 叶子节点存储的是完整的用户记录。 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

InnoDB 的表要求必须要有聚簇索引：

在 InnoBD中 通过主键来聚簇数据，也就是说聚簇索引的的 B+Tree 上的叶子节点所存储的 key 总是主键值
如果没有定义主键，InnoDB 会选择一个唯一的非空索引来代替
如果也没有这样的索引，InnoDB 会隐式地定义一个主键来聚簇数据，这个隐式的主键被称为 row-id

优点： 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非 聚簇索引更快 聚簇索引对于主键的 排序查找和 范围查找速度非常快 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多 个数据块中提取数据，所以 节省了大量的io操作。

缺点： 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影 响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新

2. 二级索引（辅助索引、非聚簇索引）

二级索引（Secondary Index），也被称为辅助索引（Non-Clustered Index），是MySQL中的一种非聚簇索引类型。与聚簇索引不同，二级索引并不决定数据在磁盘上的物理存储顺序，而是通过索引列的值来进行排序和定位数据行。

非聚簇索引的工作原理是在索引的叶子节点上存储指向实际数据行的指针。当使用非聚簇索引进行查询时，首先查找索引并获取对应的指针，然后使用指针找到实际数据行。

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

与聚簇索引相比，非聚簇索引有以下几个特点：

一个表可以有多个非聚簇索引，但只能有一个聚簇索引。

非聚簇索引不会改变表中数据的物理排序，而聚簇索引会根据索引顺序来存储数据行。

非聚簇索引通常比聚簇索引更小，因为它不需要存储实际数据行。

使用非聚簇索引进行查询时，MySQL需要进行两次查找：先查找索引，然后查找实际数据行。

3. 联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按 照c2 和c3列 的大小进行排序，这个包含两层含义： 先把各个记录和页按照c2列进行排序。 在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引，本质上也是一个二级索引。它的意 思与分别为c2和c3列分别建立索引的表述是不同的，

不同点如下： 建立联 合索引只会建立如上图一样的1棵B+树。

为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

几种常见的索引类型

1. 唯一索引（Unique Index）：唯一索引要求索引列的值在整个表中是唯一的。它可以强制保证数据的唯一性，避免插入重复的值。唯一索引通常用于主键列或具有唯一性约束的列。在MySQL中，主键索引是一种特殊的唯一索引。

2. 主键索引（Primary Key Index）：主键索引是一种特殊的唯一索引，用于标识表中的唯一记录。主键索引要求每个索引列的值都是唯一的且不能为空。主键索引可以加速针对主键的等值查询和连接操作。

3. 复合索引（Composite Index）：复合索引是由多个列组成的索引。它可以覆盖多个列，并按照指定的顺序进行排序。复合索引适用于多列的查询条件，可以提高多列查询的性能。需要注意的是，复合索引的顺序非常重要，应根据查询频率和查询顺序来选择列的顺序。

4. 全文索引（Full-Text Index）：全文索引是一种针对文本内容的索引类型，用于支持全文搜索。它可以在文本字段中进行关键词搜索，并返回与搜索条件匹配的结果。全文索引适用于对大段文本进行搜索的场景，如文章、评论等。

5. 空间索引（Spatial Index）：空间索引是一种用于处理地理信息和几何数据的索引类型。它可以加速空间查询，如点、线、多边形之间的距离计算、范围查询等。空间索引可以在存储和查询空间数据时提供高效的索引支持。

MyISAM 与 InnoDB对比

① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表操作，意味着MyISAM中建立的索引相当于全部都是

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 据记录的地址。

Innodb 创建表后生成的文件有：

1. frm:创建表的语句
2. idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有：

1. frm:创建表的语句
2. MYD:表里面的数据文件（myisam data）
3. MYI:表里面的索引文件（myisam index）

③ InnoDB的非聚簇索引data域存储相应记录 二级索引。 分离的，索引文件仅保存数 主键的值，而MyISAM索引记录的是 地址。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分 快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通 过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表 必须有主键（ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐 含字段作为主键，这个字段长度为6个字节，类型为长整型。