MySQL--索引(2)

InnoDB

1.索引类型

主键索引(Primary Key)

        数据表的主键列使用的就是主键索引。

        一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

注：InnoDB主键索引中： B+树的叶子节点key为主键的值,data为数据的内容

二级索引(辅助索引)

        二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

        唯一索引，普通索引，前缀索引等索引属于二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

注：InnoDB二级索引中： B+树的叶子节点key为索引的值,data为主键的值

二级索引：(图片来源于网络)

2. 聚集索引与非聚集索引

2.1 聚集索引

        聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

        在 Mysql 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

聚集索引的优点

        聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点

1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

2.2 非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

        二级索引属于非聚集索引。

MYISAM 引擎的表的.MYI 文件包含了表的索引， 该表的索引(B+树)的每个叶子非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚集索引的优点

        更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点

1. 跟聚集索引一样，非聚集索引也依赖于有序的数据

2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

这是 Mysql 的表的文件截图:

2.3 聚集索引和非聚集索引示意图:

3. 非聚集索引的回表

3.1 为什么非聚集索引要回表操作

        在InnoDB中，表数据是按照聚集索引（通常是主键索引）的顺序物理存储的。非聚集索引的叶子节点仅包含主键值和索引值，不包含实际的行数据。因此，当通过非聚集索引查询数据时，数据库首先通过非聚集索引找到主键值，然后再通过主键值去聚集索引中查找完整的行数据。这个过程就是回表操作。(非聚集索引的叶子节点的key是索引值,value是主键值)

 3.2 非聚集索引一定回表查询吗(覆盖索引)?

非聚集索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

SELECT name FROM table WHERE name='guang19';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

        即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表， 因为它的主键索引的叶子节点存放的是指针。但是如果 SQL 查的就是主键呢?

SELECT id FROM table WHERE id=1;

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

4.覆盖索引

        如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

覆盖索引:

5.联合索引和全文索引

5.1 全文索引

        全文索引的使用主要涉及创建全文索引、插入数据、以及通过全文搜索查询数据。以下是一个具体的例子，以MySQL数据库为例进行说明：

1. 创建包含全文索引的表

首先，需要创建一个表，并在表中的某个或某些文本字段上创建全文索引。以下是一个创建表的例子，其中在address字段上创建了全文索引：

	CREATE TABLE `worker` (
	`id` int NOT NULL,
	`name` varchar(50) NOT NULL,
	`age` int NOT NULL,
	`address` varchar(100) NOT NULL,
	PRIMARY KEY (`id`),
	FULLTEXT INDEX `idx_fulltext` (`address`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

        在这个例子中，worker表有四个字段：id、name、age和address。其中，id是主键，而address字段上创建了名为idx_fulltext的全文索引。注意，从MySQL 5.7开始，InnoDB存储引擎也支持全文索引，但在早期版本中，只有MyISAM存储引擎支持全文索引。

2. 插入数据

接下来，向表中插入一些包含文本数据的记录。以下是一些示例插入语句：

	INSERT INTO worker (id, name, age, address) VALUES (1, 'aa', 18, '河北省保定市莲池区');
	INSERT INTO worker (id, name, age, address) VALUES (2, 'bb', 19, '河北省保定市竞秀区');
	INSERT INTO worker (id, name, age, address) VALUES (3, 'cc', 20, '河北省邢台市五四东路');
	-- 插入更多记录...

3. 使用全文搜索查询数据

最后，通过全文搜索查询包含特定关键词的记录。在MySQL中，可以使用MATCH()和AGAINST()函数来进行全文搜索。以下是一个查询示例，它搜索address字段中包含“河北省保定市”的记录：

SELECT * FROM worker WHERE MATCH(address) AGAINST('河北省保定市');

这个查询将返回所有address字段中包含“广东省深圳市”的记录。MySQL的全文搜索引擎会计算每个文档对象与查询的相关度，并基于匹配的关键词个数和在文档中出现的次数来排序结果。

注意事项

• 在使用全文索引时，需要注意全文索引对文本长度的要求。MySQL InnoDB存储引擎的全文索引默认最短索引字符串长度为4个字符（可以通过ft_min_word_len系统变量调整）。
• 全文索引对于非常短的文本字段可能效果不佳，因为建立和维护索引的成本可能超过其带来的性能提升。
• 可以通过IN BOOLEAN MODE进行布尔模式的搜索，这允许更复杂的查询条件，如包含或排除特定关键词。

4.全文索引和like的区别

• 全文索引：
  • 适用于需要处理大量文本数据、要求高效率和相关性排序的场景。
  • 如搜索引擎、博客平台、新闻网站等需要对文本内容进行复杂搜索的应用。
• LIKE：
  • 适用于简单的字符串模式匹配场景。
  • 如在用户注册信息中查找包含特定关键字的用户名等

5.2 联合索引

        了解联合索引首先先介绍两个概念：       

单列索引

   单列索引即由一列属性组成的索引。

联合索引(多列索引)

   联合索引即由多列属性组成索引。

其次了解最左前缀原则

   假设创建的联合索引由三个字段组成:      

 ALTER TABLE table ADD INDEX index_name (num,name,age)

 那么当查询的条件有为:num / (num AND name) / (num AND name AND age)时，索引才生效。所以在创建联合索引时，尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。

再次强调：

        最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引

 这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

      例如创建了a,b,c联合索引

        where a=xx and b>443 and c=32;(√)

        where c=32 and b =-123 and a =123;(√)

       where b=32 and c =-123;(×)

注：现在我给num和name和age字段建立联合索引，那么 B+Tree 在排序的时候，会首先按照 num排序，当 排序相同的时候，再按照 name进行排序。依次类推

6.如何创建索引

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )