InnoDB
1.索引类型
主键索引(Primary Key)
数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键,并且主键不能为 null,不能重复。
在 mysql 的 InnoDB 的表中,当没有显示的指定表的主键时,InnoDB 会自动先检查表中是否有唯一索引的字段,如果有,则选择该字段为默认的主键,否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
注:InnoDB主键索引中: B+树的叶子节点key为主键的值,data为数据的内容
二级索引(辅助索引)
二级索引又称为辅助索引,是因为二级索引的叶子节点存储的数据是主键。也就是说,通过二级索引,可以定位主键的位置。
唯一索引,普通索引,前缀索引等索引属于二级索引。
唯一索引(Unique Key) :唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。
普通索引(Index) :普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。
前缀索引(Prefix) :前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小, 因为只取前几个字符。
全文索引(Full Text) :全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引,5.6 之后 InnoDB 也支持了全文索引。
注:InnoDB二级索引中: B+树的叶子节点key为索引的值,data为主键的值
二级索引:(图片来源于网络)
2. 聚集索引与非聚集索引
2.1 聚集索引
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
在 Mysql 中,InnoDB 引擎的表的
.ibd
文件就包含了该表的索引和数据,对于 InnoDB 引擎表来说,该表的索引(B+树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据。聚集索引的优点
聚集索引的查询速度非常的快,因为整个 B+树本身就是一颗多叉平衡树,叶子节点也都是有序的,定位到索引的节点,就相当于定位到了数据。
聚集索引的缺点
依赖于有序的数据 :因为 B+树是多路平衡树,如果索引的数据不是有序的,那么就需要在插入时排序,如果数据是整型还好,否则类似于字符串或 UUID 这种又长又难比较的数据,插入或查找的速度肯定比较慢。
更新代价大 : 如果对索引列的数据被修改时,那么对应的索引也将会被修改, 而且况聚集索引的叶子节点还存放着数据,修改代价肯定是较大的, 所以对于主键索引来说,主键一般都是不可被修改的。
2.2 非聚集索引
非聚集索引即索引结构和数据分开存放的索引。
二级索引属于非聚集索引。
MYISAM 引擎的表的.MYI 文件包含了表的索引, 该表的索引(B+树)的每个叶子非叶子节点存储索引, 叶子节点存储索引和索引对应数据的指针,指向.MYD 文件的数据。
非聚集索引的叶子节点并不一定存放数据的指针, 因为二级索引的叶子节点就存放的是主键,根据主键再回表查数据。
非聚集索引的优点
更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了,非聚集索引的叶子节点是不存放数据的
非聚集索引的缺点
跟聚集索引一样,非聚集索引也依赖于有序的数据
可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。
这是 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` )