一文搞懂MySQL（innodb）中的索引

最新推荐文章于 2025-04-19 12:20:01 发布

摸鱼又混分

最新推荐文章于 2025-04-19 12:20:01 发布

阅读量892

点赞数 29

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_44821447/article/details/142814211

版权

例3：创建主键索引（只能通过主键约束的方式）

一、什么是索引？

索引的本质是数据结构，可以理解为排好序的快速查找数据结构。在使用索引进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗。在innodb中，每新建一个索引就会生成一棵B+树。

二、索引的分类

1. 按照物理实现方式，索引可以分为聚簇索引和非聚簇索引。

2. 按功能逻辑分（常见的）

3.按作用字段分

聚簇索引：并不是单独的一种索引类型，而是一种数据存储方式（所有的记录都在叶子节点），也就是所谓的索引即数据，数据即索引。在innodb中只有主键索引（primary key）是聚簇索引，存放的是完整的数据，其他的都可以理解为二级/辅助索引。

二级/辅助索引：根据非主键的列新生成一颗B+树，该B+树的叶子节点并不存放完整的数据，只存放该二级索引的列值和主键值，通过二级索引找到主键值，然后再回到聚簇（主键）索引生成的B+树中根据主键值获取其叶子节点中完整的数据。这个过程也叫回表。

术语："聚簇" 表示数据和相邻的键值聚簇的存储在一起，即B+树中的叶子节点。

联合索引：也属于二级索引，即由多个字段作为排序规则，比如让B+树按C2和C3列的大小排序。

①先把各个记录和页按C2列进行排序。

②在记录的C2列相同的情况下采用C3列排序。

问：为什么二级/辅助索引的B+树中不存放完整的数据？

答：二级索引只是辅助我们查找到主键的，而且一张表中可会有很多二级索引，如果每个二级索引的B+树的叶子节点都存放完整数据，相当于把数据备份了多次，会占用大量的磁盘空间，开销太大。

三、B+树和B树的区别

B+树的结构如图所示：

B树的结构如下图所示：

总结对比：

特性	B+树	B树
数据存储位置	数据存储在叶子节点，内部节点仅存储索引键	内部节点和叶子节点都有数据
叶子节点链表	叶子节点通过链表有序连接	无
范围查询效率	范围查询高效，叶子节点可顺序遍历	范围查询较慢，需回溯树结构
数据冗余	内部节点和叶子节点中可能有冗余键值	无数据冗余
查找效率	不稳定，查找可能较快，数据可在内部节点结束查找	稳定，查找必须到达叶子节点，路径较长
树的高度	通常较低，因为内部节点存储更多的索引键	可能相对较高，因为内部节点存储数据
磁盘I/O	磁盘I/O 较少，内部节点存储更多索引键值	磁盘I/O 较多，内部节点占空间较大

四、索引的使用

1.查看索引

#方式1：SHOW CREATE TABLE 表名;

#方式2: SHOW INDEX  FROM  表名;

2.创建索引

2.1 创建表的时候创建

#隐式的创建 -- 对字段声明主键约束、唯一约束、外键约束会自动创建索引。

#显示的创建：基本语法如下
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

/**
-- NIQUE 、FULLTEXT 和SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
-- INDEX 与KEY 为同义词，两者的作用相同，用来指定创建索引；
-- index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
-- col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
-- length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
-- ASC 或DESC 指定升序或者降序的索引值存储。(默认为升序，8.0支持降序索引，5.7不支持。)
*/

例1：创建普通索引

CREATE TABLE book(
 book_id INT ,
 book_name VARCHAR(100),
 authors VARCHAR(100),
 info VARCHAR(100) ,
 comment VARCHAR(100),
 year_publication YEAR,
 INDEX(year_publication)
 );

例2：创建唯一索引

CREATE TABLE test1(
 id INT NOT NULL,
 name varchar(30) NOT NULL,
 UNIQUE INDEX uk_idx_id(id)
 );

例3：创建主键索引（只能通过主键约束的方式）

#随表一起创建
CREATE TABLE student (
 id INT(10) UNSIGNED  AUTO_INCREMENT ,
 student_no VARCHAR(200),
 student_name VARCHAR(200),
 PRIMARY KEY(id) 
);

#删除主键索引：
ALTER TABLE student
DROP PRIMARY KEY ;  

#修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

例4：创建单列索引

CREATE TABLE test2(
id INT NULL,
name CHAR(50) NULL,
INDEX single_idx_name(name(20))
);

例5：创建联合索引

#创建表test3，在表中的id、name和age字段上建立组合索引
CREATE TABLE test3(
id INT(11) NOT NULL,
name CHAR(30) NOT NULL,
age INT(11) NOT NULL,
info VARCHAR(255),
INDEX  multi_idx(id,name,age)
);

例6：创建全文索引

创建了一个给title和body字段添加全文索引的表。
CREATE TABLE articles (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR (200),
 body TEXT,
 FULLTEXT index (title, body)
 ) ENGINE = INNODB ;


#全文索引用match+against方式查询：
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

/**
注意点：
1. 5.7及以后得版本不需要指定ENGINE=MyISAM ，因为之后的版本innodb引擎也支持全文索引（中文需要安装插件）
2.全文索引比 like + % 快 N 倍，但是可能存在精度问题。
3.如果需要全文索引的是大量数据，建议先添加数据，再创建索引
*/

例7：创建空间索引

空间索引创建中，要求空间类型的字段必须为非空。

#创建表test5，在空间类型为GEOMETRY的字段上创建空间索引，
CREATE TABLE test5(
 geo GEOMETRY NOT NULL,
 SPATIAL INDEX spa_idx_geo(geo)
 ) ENGINE=MyISAM;

注：从5.7开始，innodb也支持空间索引了。

2.2 在已经存在的表上创建

① 使用ALTER TABLE创建

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
 [index_name] (col_name[length],...) [ASC | DESC]

②用CREATE INDEX创建

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
 ON table_name (col_name[length],...) [ASC | DESC]

3.删除索引

3.1 使用ALTER TABLE删除

基本语法：
ALTER TABLE table_name DROP INDEX index_name;

3.2 使用CREATE INDEX删除

基本语法：
DROP INDEX index_name ON table_name;

提示：

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

4.MySQL8.0的索引新特性

4.1 支持索引降序

例：创建a,b字段的联合索引，其中b采用降序

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));


#当查询语句中的排序条件为 ORDER BY a,b DESC时，b字段的降序索引就起作用了。

 SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

#当排序条件 改为 order by a desc, b desc ，由于是联合索引，我们需要先找到a,但a索引默认是升序。所以这种情况就不起作用了。

4.2 隐藏索引

作用：

①当需要删除某个索引时，我们可以先把这个索引隐藏，然后测试删除后是否会出现错误，防止直接删除索引引起的重大事故。

②可以利用隐藏索引的方式测试某个索引是否有效，删除无效索引。

注意当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

创建方式：

1.建表时直接创建  通过INVISIBLE 关键字实现
CREATE TABLE tablename(
 propname1 type1[CONSTRAINT1],
 propname2 type2[CONSTRAINT2],
 ……
 propnamen typen,
 INDEX [indexname](propname1 [(length)]) INVISIBLE
 );

2.在已存在的表上创建

方式1：
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

方式2：
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

3.修改已存在索引为隐藏
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引