深入浅出mysql索引原理及使用

木鱼木鱼木鱼

已于 2022-08-16 13:44:30 修改

阅读量350

点赞数 1

分类专栏： mysql mysql索引文章标签： mysql 数据库 java

于 2022-08-16 10:50:21 首次发布

本文链接：https://blog.csdn.net/m0_49535395/article/details/126265345

版权

mysql索引同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

1.索引的声明和使用

1.1索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分，分成单列索引和联合索引。

1.普通索引

在创建索引时，不附件任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的name上建立一个普通索引，查询记录时就可以根据该索引进行查询。

2.唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。

例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引，可以更快速的确定某条记录

3.主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUER,一张表里最多只有一个主键索引

4.单列索引

在表中的某个字段上创建索引。单例索引只根据该字段进行索引。单例索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单例索引。

5.多列（组合、联合）索引

联合索引是在表的多个字段组合上创建的一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender上建立一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

6.全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法只能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则只能的筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小

使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文朝招，允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时间，使用全文索引可以提高查询速度。例如，表student的字段information是TEXT类型，该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。

全文索引典型的有两种类型：自然语言的全文索引和布尔全文索引

MySQL数据库从3.23.23版开始支持全文索引，但是MySQL5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词。

随着大数据时代的到来，关系型数据库应对全文检索的需求已力不从心，逐渐被solr、ElasticSearch等专门的搜索引擎所替代

7.补充：空间索引

使用参数SPATIAL可以设置索引为空间索引。空间索引只能简历在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为控制。

小结：不同的存储引擎支持的索引类型也不一样

InnoDB：支持B-tree、Full-text等索引，不支持Hash索引；

MyISAM：支持B-tree、Full-text等索引，不支持Hash索引；

Memory：支持B-tree、Hash等索引，不支持Full-text索引；

NDB：支持Hash索引，不支持B-tree、Full-text等索引；

Archive：不支持B-tree、Hash、Full-text等索引

1.2创建索引

1.2.1 create table 隐式的方法创建索引

CREATE TABLE dept ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR ( 20 ) ) CREATE TABLE emp (
	emp_id INT PRIMARY KEY AUTO_INCREMENT,#主键索引
	emp_name VARCHAR ( 20 ) UNIQUE,#唯一索引
	dept_id INT,
	CONSTRAINT emp_dept_id_fk FOREIGN KEY ( dept_id ) REFERENCES dept ( dept_id ) #外键索引
)

1.2.2 显式的方法创建

（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 ) 
);
#通过命令查看索引结构
#方式一
show create table book;

#方式二
show index from book;

#性能分析工具：EXPLAIN 可以查看有没有运行索引
EXPLAIN SELECT *FROM book where year year_publication = '测试'

（2）创建唯一索引

# 创建唯一索引
# 声明有唯一索引的字段，在添加数据时，不能重复，要保证唯一性，但是可以添加null值也可以多次添加

CREATE TABLE book1 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR,
	UNIQUE INDEX ( COMMENT ) 
);

show index from book1

（3）主键索引

#创建主键索引
#通过定义主键约束的方式去定义主键索引
CREATE TABLE book2 (
	book_id INT PRIMARY KEY AUTO_INCREMENT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR
);

SHOW INDEX FROM book2

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

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

（4）单例索引

#创建单列索引（非联合索引）
CREATE TABLE book3 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR,
	UNIQUE INDEX idx_name( book_name) 
);

show INDEX from book3

（5）联合索引

#创建联合索引
CREATE TABLE book4 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR,
	INDEX mul_bid_bname_info( book_id,book_name,info) 
);

show index from book4

#遵循最左前缀原则 生效原理参考B+树 
#可以生效 
EXPLAIN select *from book4 where book_id = 1001 and book_name = 'mysql'

#可以生效
EXPLAIN select *from book4 where book_id = 1001

#不可以生效
EXPLAIN select *from book4 where book_name = '1001'

#不可以生效
EXPLAIN select *from book4 where info = '1001'

（6）创建全文索引

#创建全文索引
CREATE TABLE test4 (
	id INT NOT NULL,
	NAME CHAR ( 30 ) NOT NULL,
	age INT NOT NULL,
	info VARCHAR ( 255 ),
	FULLTEXT INDEX futxt_idx_info ( info ) 
) ENGINE = MyISAM;

show index from test4
#不同于like方式的查询：
#全文索引引用match+against方式查询
select *from papers where MATCH(title,content) against('要查询的字符串')

1.2.3 表已经创建成功的情况下添加索引

ALTER TABLE... ADD...

#第二种：表已经创建成功
CREATE TABLE book5 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR 
);

show index from book5

# ALTER TABLE... ADD...
#普通索引
ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT)

#唯一索引
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name)

#联合索引 将常用的字段放在前边，以此类推。务必遵循左前缀的原则
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info)

CREATE INDEX ... ON ...

#CREATE INDEX ... ON ...
CREATE TABLE book6 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR 
);

CREATE INDEX idx_cmt on book6(COMMENT)

CREATE INDEX uk_idx_name on book6(book_name)

CREATE INDEX mul_bid_bname_info on book6(book_id,book_name,info)

show index from book6

1.3索引的删除

# 索引的删除
# 注意：添加AUTO_INCREMENT约束字段的唯一索引不能删除
# 方式一 ALTER TABLE ... DROP INDEX...
ALTER TABLE book6 drop index mul_bid_bname_info
show index from book6

# 方式二 DROP INDEX ... ON ...
DROP INDEX idx_cmt on book6;
show index from book6

删除表列的时候，对应的索引也会被删除