快速掌握索引的创建与设计原则

最新推荐文章于 2024-07-27 13:31:57 发布

允谦呀

最新推荐文章于 2024-07-27 13:31:57 发布

阅读量249

点赞数 3

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

本文链接：https://blog.csdn.net/qiangqiang103/article/details/123122397

版权

Mysql 专栏收录该内容

21 篇文章 1 订阅

订阅专栏

索引的创建与设计原则

⭐️写在前面

这里是允谦的学习之路
👍如果对你有帮助，给博主一个免费的点赞以示鼓励把QAQ
👋博客主页🎉 允谦的学习小屋
⭐️更多文章👨‍🎓请关注允谦主页📝
🍅文章发布日期：2022.02.24
👋java学习之路！
欢迎各位🔎点赞👍评论收藏⭐️
🎄冲冲冲、持续更新🎄

文章目录

索引的创建与设计原则

1、索引的声明与使用

1.1 索引的分类

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

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

1、普通索引

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

2、唯一性索引

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

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

3、主键索引

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

4、单列索引

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

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

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

6、全文索引

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

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

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

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

7、补充：空间索引

使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGN等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

InnoDB:支持B-Tree,Full-text等索引，不支持Hash索引。

1.2 创建索引

MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。

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,
	enp_name varchar(20) UNIQUE,
	dept_id INT,
	CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

显式的创建表时创建索引：

CREATE TABLE table_name[col_name data_type][UNIQUE | FULLTEXT | SPATIAL][INDEX | KEY][index_name] (col_name[length])[ASC|DESC]

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

1、创建普通索引

--  显式的创建索引
CREATE TABLE book(
book_id INT,
book_name VARCHAR(100),
`AUTHORS` VARCHAR(100),
info VARCHAR(100),
`COMMENT` VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX idx_bname(book_name)
);
-- 通过命令查看索引
-- 方式1：
SHOW CREATE TABLE book;
-- 方式2：
SHOW INDEX FROM book;

# 性能分析工具：EXPLAIN
EXPLAIN SELECT * FROM book WHERE book_name = 'mysql高级';

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 uk_idx_cmt(COMMENT)
);
show INDEX FROM book1;


-- 举例
INSERT INTO book1(book_id,book_name,COMMENT) VALUES(1,'MySQL高级','适合有工作经验的人学习');
SELECT * FROM book1;
INSERT INTO book1(book_id,book_name,COMMENT) VALUES(1,'MySQL高级','适合有工作经验的人学习'); # 此时报错：> 1062 - Duplicate entry '适合有工作经验的人学习' for key 'book1.uk_idx_cmt'

3、主键索引

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

SHOW INDEX FROM book2;
-- 通过删除主键约束的方式删除主键索引
ALTER TABLE book2 DROP PRIMARY KEY;

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_bname(book_name)
);

5、联合索引

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

SHOW INDEX FROM book4;

在这里插入图片描述

6、全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR、TEXT列创建索引，索引总是对整个列进行，不支持局部索引。

CREATE TABLE test4(
	id INT NOT NULL,
	name char(30) NOT NULL,
	age INT NOT NULL,
	info VARCHAR(255),
	FULLTEXT INDEX ful_idx(info)
);

全文索引引用match+against方式查询：明显的提高查询效率

SELECT * FROM papers where match(title,content) against('查询字符串');

注意点：
	1、使用全文索引前，搞清楚版本情况
	2、全文索引比like+%快N倍，但是可能存在精度问题
	3、如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

7、创建空间索引

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

2、在已经存在的表上创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句

1、使用ALTER TABLE语句创建索引

alter table table_name add[unique | fulltext | spatial][index | key] [index_name](col_name[length],...)[ASC|DESC]

-- 举例：
ALTER TABLE book5 ADD INDEX idx_cmt(`COMMENT`);
ALTER TABLE book5 ADD INDEX uk_bname(book_name);

2、使用CREATE INDEX

-- 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
);
SHOW INDEX FROM book6;
CREATE INDEX idx_cmt on book6(COMMENT);

1.3 删除索引

MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。

1、使用ALTER TABLE删除索引

ALTER TABLE table_name DROP IALTER TABLE book5 DROP INDEX idx_cmt;NDEX index_name;
-- 举例
ALTER TABLE book5 DROP INDEX idx_cmt;

2、使用DROP INDEX删除索引

DROP INDEX index_name ON table_name;
-- 举例
DROP INDEX uk_bname ON book5;

3、索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要，设计索引时，应该考虑相应的准则。

3.1哪些情况适合创建索引

1、字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的,就可以直接创建唯一性索引,或者主键索引>。这样我们就可以更快的通过该索引来确定某条记录。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了Insert速度，这个速度损耗可以忽略，但是提高查找速度是明显的。

2、频繁作为WHERE查询条件的字段

#student_id字段上没有索引的  0.243s
SELECT course_id,class_id,`name`,create_time,student_id FROM student_info WHERE student_id = 123110;
#给student_id添加索引
ALTER TABLE student_info ADD INDEX idx_sid(student_id);
#student_id字段上有索引的  0.033s
SELECT course_id,class_id,`name`,create_time,student_id FROM student_info WHERE student_id = 123110;