您的位置:首页 > 游戏 > 手游 > MySQL--索引(2)

MySQL--索引(2)

2024/10/14 11:12:21 来源:https://blog.csdn.net/m0_57921272/article/details/140620529  浏览:    关键词: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表有四个字段:idnameageaddress。其中,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` )

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com