MySQL高级-索引的使用和设计原则

最新推荐文章于 2024-06-03 17:23:42 发布

Java-阿旭

最新推荐文章于 2024-06-03 17:23:42 发布

阅读量473

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_45108959/article/details/128073486

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

索引

一、索引的使用
二、索引的设计原理

MySQL的从分类角度来看，包含：普通索引、唯一索引、主键索引、全文索引等。

一、索引的使用

1. 创建索引

1.1 创建表时创建索引

1.1.0 创建索引

在创建数据库表的时候，除了可以定义列名和数据类型以外，我们还可以定义主键约束、唯一约束、外键约束，不管定义那种约束，都相当于给该列创建了索引。

#隐式
CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(20)
);

#显式
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,	#主键索引
t_name VARCHAR(20) UNIQUE,	#唯一索引
stu_id INT,
CONSTRAINT student_teacher_id_fk FOREIGN KEY(stu_id) REFERENCES stu(stu_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]：指定索引名称，如果不指定的话，就是用列名来表示。
(col_name [length])：指定要给那一列或多列创建索引，必须指定列名；length为指定索引的长度，只有列类型为字符串的列才能指定长度。可选。
[ASC | DESC]：指定索引是按值的升序还是降序进行存储的。可选。

1.1.1 案例

1. 创建普通索引
给student表中的stu_address创建普通索引。

CREATE TABLE student(
stu_id INT ,
stu_no VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_address VARCHAR(60),
INDEX(stu_address)	#普通索引
);

2. 创建主键索引
给student表中的stu_id 创建主键索引，设置为主键后，数据库会自动为stu_id创建主键索引。

CREATE TABLE student(
stu_id INT UNSIGNED AUTO_INCREMENT,
stu_no VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_address VARCHAR(60),
PRIMARY KEY(stu_id)
);

3. 创建唯一索引
给student表中的stu_no 创建唯一索引。

CREATE TABLE student(
stu_id ,
stu_no VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_address VARCHAR(60),
UNIQUE INDEX uk_idx_stu_no(stu_no)
);

4. 创建组合索引
给student表中的stu_id、stu_no、stu_name创建组合索引。

CREATE TABLE student(
stu_id ,
stu_no VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_address VARCHAR(60),
INDEX multi_idx_id_no_name(stu_id,stu_no,stu_name)
);

5. 创建全文索引
给student表中的stu_address创建组合索引。

CREATE TABLE student(
stu_id ,
stu_no VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_address VARCHAR(60),
FULLTEXT INDEX fultext_idx_add(stu_address)
);

1.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]

以student(stu_id,stu_no,stu_name,stu_address)为例：

a.给stu_name创建普通索引

ALTER TABLE student ADD INDEX idx_name(stu_name);

b.给stu_no创建唯一索引

ALTER TABLE student ADD UNIQUE INDEX uk_idx_no(stu_no);

b.给stu_id,stu_no,stu_name创建组合索引

ALTER TABLE student ADD INDEX multi_idx_id_no_name(stu_id,stu_no,stu_name);

1. 使用CREATE INDEX创建索引
基本语法：

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

以student(stu_id,stu_no,stu_name,stu_address)为例：

a.给stu_name创建普通索引

CREATE INDEX idx_name ON student(stu_name);

b.给stu_no创建唯一索引

CREATE UNIQUE INDEX unk_idx_no ON student(stu_no);

b.给stu_id,stu_no,stu_name创建唯一索引

CREATE INDEX multi_idx_id_no_name ON student(stu_id,stu_no,stu_name);

2. 删除索引

可以使用ALTER TABLE或者DROP INDEX两种方式删除索引。
a.使用ALTER TABLE删除

ALTER TABLE table_name DROP INDEX index_name;

a.使用DROP INDEX删除

DROP INDEX index_name ON table_name;

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

二、索引的设计原理

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

1. 那种字段适合创建索引

经常被用来做where条件的字段。当某个字段在select查询语句中，经常用来做查询条件，那么这个字段需要创建索引，在数据量庞大的时候，是非常提升查询速度的。
具有唯一性的字段。在数据库表中，当某个字段具有唯一性的，那么这个字段适合创建唯一索引或者主键索引。索引也是其他约束作用的。在业务上，组合字段具有唯一特性的，也可以给这个组合字段创建组合索引的。创建索引后，可以大大增加我们的查询效率，即使索引会影响增删改的操作，但是这个影响是可以忽略的，它给我们带来的查询速度是明显的。
经常用来做为GROUP BY或ORDER BY的列。索引就是让数据按照某种顺序进行存储和检索的，当我们在GROUP BY对数据进行分组的时候，或者对数据进行ORDER BY排序的时候，需要对分组或排序的字段进行索引。需要排序的字段是多个时，可以创建组合索引。
UPDATE/DELETE的WHERE过滤条件。当我们对某个数据进行更新或者删除时，是先将该数据从表中查询出来，如果给过滤条件加了索引，那么查询速度会大大的增加，因为更新/删除语句是更加where过滤条件将数据查询出来，再进行操作的。在做更新操作的时候，如果更新的是索引字段，需要维护索引，更新比较慢，但是更新的是非索引字段，更新速度是非常快的。
需要DISTINCT的字段。当某个字段需要进行去重的时候，给这个字段创建索引，也会提升查询的数据。
多表联查的时候，需要创建索引。当我们在进行多表联查的时候，不仅要给查询语句的WHERE过滤条件的字段创建索引，也要给表连接的字段创建索引。
下面查询语句，我们需要给student和teacher的stu_id字段创建索引，还要给student的stu_name创建索引。
```
SELECT s.stu_name,s.stu_age,t.tea_name FROM student s
JOIN teacher t ON s.stu_id = t.stu_id
WHERE s.stu_name = '张三'
```
散列性高的列字段适合创建索引。散列性高指的是同一个列，出现相同内容的少。比如：age字段中，【18，20，18，16，16，20，18，20】，在这8个记录中，实际基数为3，说明散列性很低。在创建索引的适合，我们应该为基数大的列创建索引效果才是最好的，为基数小的列创建索引，效果是非常小的。
使用最频繁的联合索引列，放在最左边。因为最左原则，使用频繁的列，可以放在最左侧，这样可以减少创建一些索引。也增加了索引的使用率。
在多个列需要创建索引的情况下，组合索引优先于单列索引

2. 索引的限制数量

以为创建索引也是需要占用存储空间的，索引越多，占用的磁盘空间就越多。在进行增删改的时候，表数据进行变更的同事，索引也是需要调整和更新，会照成负担的。因此，每张表建议索引的数量不要超过6个。

3. 那些字段不适合创建索引

查询语句中WHERE、GROUP BY、ORDER BY过滤条件没有用到的字段。
数据量小的表不建议创建索引。比如数据量才几十、几百条、一千条数据，没有必要创建索引，不要查询很大的数据量，，此时创建索引反而会占用磁盘空间，而且查询走索引的影响并没有很大帮助。
有大量重复数据的列，不建议创建索引。在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的"性别"字段上只有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
经常跟新的字段，不要创建索引。因为更新字段的的时候，也需要跟新索引，如果索引过多，跟新索引的时候会照成过大的影响，从而影响效率。
字段值是无序，建议不创建索引。如：UUID、MD5等。

避免冗余的索引。

CREATE TABLE student(
stu_id INT UNSIGNED AUTO_INCREMENT,
stu_no VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_address VARCHAR(60),
PRIMARY KEY(stu_id),
INDEX idx_no_name(stu_no ,stu_name ),	#联合索引
INDEX idx_no(stu_no )	#给stu_no创建单列索引
);

上面的建表语句中，idx_no_name索引已经有stu_no 字段了，再单独给stu_no 建个idx_no完全是没有必要了，在用idx_no查询的时候，优先使用联合索引（idx_no_name），而且所有还要占用磁盘空间。

避免给字段创建重复索引。

		CREATE TABLE student(
		stu_id INT UNSIGNED AUTO_INCREMENT,
		stu_no VARCHAR(20),
		stu_name VARCHAR(20),
		stu_age INT,
		stu_address VARCHAR(60),
		PRIMARY KEY(stu_id),
		UNIQUE INDEX idx_id(stu_id )
		);