MySQL进阶之（六）索引的使用与设计原则

最新推荐文章于 2024-04-27 08:43:32 发布

EijoaHoaiQ:D

最新推荐文章于 2024-04-27 08:43:32 发布

阅读量652

点赞数 9

分类专栏： MySQL进阶文章标签： mysql 数据库索引的设计原则索引的使用

本文链接：https://blog.csdn.net/qq_50994235/article/details/136693626

版权

MySQL进阶专栏收录该内容

7 篇文章 1 订阅

订阅专栏

六、索引的使用与设计原则

6.1 索引的声明与使用
- 6.1.1 索引的分类
6.2 创建索引
6.3 删除索引
- 6.3.1 使用 ALTER TABLE 语句删除索引
- 6.3.2 使用 DROP INDEX 语句删除索引
6.4 MySQL 8.0索引新特性
6.5 索引的设计原则
6.6 小结

6.1 索引的声明与使用

前文中介绍了索引的数据结构，使用索引可以帮助我们从海量的数据中快速定位想要查找的数据。
但是我们在使用索引的时候，需要平衡索引的利（提升查询效率）和弊（维护索引所需的代价）。下文中将介绍如何使用索引？哪些情况下适合创建索引？哪些情况下不适合创建索引？

6.1.1 索引的分类

MySQL 的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等等。从不同的角度可以划分不同的分类：

从功能逻辑上划分，索引主要有 4 种：普通索引、唯一索引、主键索引、全文索引。
从物理实现方式上划分，索引主要有 2 种：聚簇索引、非聚簇索引。
从作用字段个数上划分，索引主要有 2 种：单列索引、联合索引。

01、普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。

这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引后，可以通过索引进行查询。

例如：在表 student 的字段 name 上创建一个普通索引，查询记录时就可以根据该索引（name 字段）进行查询。

02、唯一性索引

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

在一张表数据里可以有多个唯一索引。

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

03、主键索引

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

注：因为主键是用来唯一标识表中的每一行数据的，如果一张表里有多个主键，那么就无法确定每一行数据的唯一性，这就会导致许多数据库操作的问题。所以，为了保证数据的完整性和一致性，MySQL 表只能有一个主键。

04、单列索引

单列索引是指在表中的单个字段上创建索引。

单列索引只根据该字段进行索引，可以是普通索引，也可以是唯一性索引，还可以是全文索引。

一个表中可以有多个单列索引，只要保证该索引只对应一个字段即可。

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

多列索引是在表的多个字段组合上创建一个索引。

该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，查询时遵循 “最左前缀原则”。

例如：在表中的字段 id、name 和 gender 上建立一个多列索引 idx_id_name_gender，只有在查询条件中使用了字段 id 时该索引才会被使用（可以是 id；id、name；id、name、gender）。

06、全文索引

全文索引（全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的结果。

全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

使用参数 FULL TEXT 可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在 char、varchar、text 类型及其系列类型的字段上，查询数据量较大的字符串类型时，使用全文索引可以提高查询速度。

例如：表 student 的字段 information 是 text 类型，该字段包含了很多文字信息。在字段 information 上创建全文索引后，可以提高查询字段 information 的速度。

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

MySQL 数据库从 3.23.23 版开始支持全文索引，但是 MySQL 5.6.4 以前只有 MyISAM 支持，5.6.4 版本以后 InnoDB 才支持，但是官方版本不支持中文分词，需要第三方分词插件。在 5.7.6 版本，MySQL 内置了 ngram 全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时，要先查看一下 MySQL 的版本、存储引擎、数据类型是否支持全文索引。

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

07、空间索引

使用参数 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 等索引。

6.2 创建索引

在一个表中创建索引有两种方式：创建表时创建索引、在已经存在的表上创建索引。

6.2.1 创建表时创建索引

在创建表时创建索引有两种方式：隐式创建、显式创建。

隐式创建，顾名思义，不用自己特意指定索引。在声明有主键约束、唯一性约束、外键约束的字段上，会自动地添加相关的索引。

显示创建，需要手动设置相关的参数，其语法格式如下：

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指定升序或者降序的索引值存储。

除了特殊的隐式创建之外，一般使用显示创建的方式创建索引。

01、创建普通索引

# ① 创建普通索引
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)
);

# 查看创建的索引情况
SHOW INDEX FROM book;

在这里插入图片描述

02、创建唯一索引

# ② 创建唯一索引
# 声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加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;

在这里插入图片描述

03、创建主键索引

设置为主键后数据库会自动建立索引，InnoDB 为聚簇索引。

# ③ 创建主键索引
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 book2
DROP PRIMARY KEY;

在这里插入图片描述

04、创建单列索引

# ④ 创建单列索引
CREATE TABLE book3(
	book_id INT,
	book_name VARCHAR(100),
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR,
	# 声明索引
	INDEX idx_bname(book_name)
);

# 查看创建的索引情况
SHOW INDEX FROM book3;

在这里插入图片描述

05、创建组合索引

# ⑤ 创建组合索引（联合索引）
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;

在这里插入图片描述

06、创建全文索引

FULLTEXT 全文索引可以用于全文搜索，并且只为 char、varchar、text 列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。

# ⑥ 创建全文索引
CREATE TABLE book5(
	book_id INT,
	book_name VARCHAR(100),
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR,
	# 声明索引
	FULLTEXT INDEX futxt_idx_info(info(50))
)ENGINE = MyISAM;

# 查看创建的索引情况
SHOW INDEX FROM book5;

在这里插入图片描述

# 全文索引：给 title 和 content字段添加全文索引
CREATE TABLE `papers` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`title` varchar(200) DEFAULT NULL,
	`content` text,
	PRIMARY KEY (`id`),
	FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

全文索引不同于 like 方式的查询：

SELECT * FROM papers WHERE content LIKE '%查询字符串%';

全文索引使用 match + against 方式查询：

SELECT * FROM papers WHERE MATCH(title,content) AGAINST ('查询字符串');

注意点：

使用全文索引时，需要注意版本支持情况（在 MySQL 5.5 版本中只有 MyISAM 支持全文索引，InnoDB 不支持。MySQL 5.7 之后版本中都支持全文索引了）。
全文索引比 like 快 N 倍，但是可能存在精度问题。
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

07、创建空间索引

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

# ⑦ 创建空间索引
CREATE TABLE book6(
	book_id INT,
	book_name VARCHAR(100) NOT NULL,
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR NOT NULL,
	geo GEOMETRY NOT NULL,
	# 声明索引
	SPATIAL INDEX spa_geo(geo)
)ENGINE = MyISAM;

# 查看创建的索引情况
SHOW INDEX FROM book6;

在这里插入图片描述

6.2.2 在已经存在的表上创建索引

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

01、ALTER TABLE语句

使用 ALTER TABLE 语句创建索引的基本语法：

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

在这里插入图片描述

02、CREATE INDEX语句

使用 CREATE INDEX 语句创建索引的基本语法：

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

在这里插入图片描述

6.3 删除索引

删除索引可以使用 ALTER TABLE 语句或者DROP INDEX 语句实现，DROP INDEX 语句在内部会被映射到一个 ALTER TABLE 语句中。

6.3.1 使用 ALTER TABLE 语句删除索引

使用 ALTER TABLE 语句删除索引的基本语法：

ALTER TABLE table_name DROP INDEX index_name;

在这里插入图片描述
但是这里需要注意的是，添加 AUTO_INCREMENT 约束字段的唯一索引不能被删除。

自增的条件：主键索引或主键唯一索引。也就是说，如果某字段被定义成自增的，则它的索引就不能被删除。

6.3.2 使用 DROP INDEX 语句删除索引

使用 DROP INDEX 语句删除索引的基本语法：

DROP INDEX index_name ON table_name;

在这里插入图片描述

注：删除索引所在列时，索引会重新排序。

6.4 MySQL 8.0索引新特性

6.4.1 特性一：支持降序索引

降序索引以降序存储键值。

在语法上，从 MySQL 4 版本开始就已经支持降序索引的语法了，但实际上 DESC 语法是被忽略的，直到 MySQL 8.x 版本才开始真正支持降序索引（仅限于 InnoDB 存储引擎）。

MySQL 在 8.0 版本之前创建的仍然是升序索引，使用时进行反向扫描，这就大大降低了数据库的效率。在某些长江下，降序索引是比较重要的。

比如：现有一查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

# ① 支持降序索引
CREATE TABLE myTest(
	a INT,
	b INT,
	INDEX idx_a_b(a ASC, b DESC)
);

SHOW INDEX FROM myTest;

在这里插入图片描述
注意：降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。

6.4.2 特性二、隐藏索引

在 MySQL 5.7 版本及之前，只能通过显式地方式删除索引。如果发现删除索引后出现错误，又只能通过显式创建索引地方式将删除的索引创建回来。如果数据库表中的数据量非常大、或者数据库表本身比较大时，操作就会消耗系统过多的资源，操作成本非常高。

从 MySQL 8.x 开始支持隐藏索引。只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使强制使用索引：force index，优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

但是，主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会称为隐式主键，也不能设置为隐藏索引。

索引默认式可见的，在使用 CREATE TABLE、CREATE INDEX、ALTER TABLE 等语句时可以通过 VISIBLE、INVISIBLE 关键词设置索引的可见性。

01、创建表时直接创建

## 创建表时创建隐藏索引
CREATE TABLE book7(
	book_id INT,
	book_name VARCHAR(100) NOT NULL,
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR NOT NULL,
	# 创建不可见索引
	INDEX idx_cmt(comment) invisible
);

SHOW INDEX FROM book7;

在这里插入图片描述

02、修改时创建

## 修改时创建隐藏索引(切换索引的可见状态)
ALTER TABLE book7 ALTER INDEX idx_year_pub invisible;

SHOW INDEX FROM book7;

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

6.5 索引的设计原则

在多数查询中，添加索引会提高查询的效率，但是创建过多的索引会对新增、更新、删除操作带来性能上的损耗。所以，什么情况下适合创建索引？什么情况下不适合创建索引？这两个问题是很重要的。

6.5.1 哪些情况下适合创建索引？

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

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

例如：学生表中学号字段是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息。但如果使用姓名的话，可能会存在同名的现象，从而降低查询速度。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

说明：不是创建了唯一索引就一定影响了 insert 的速度，这个速度损耗是可以忽略的，但是很明显是可以提高查询速度的。

02、频繁作为 where 查询条件的字段

某个字段在 select 语句的 where 条件中经常被使用到时，就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅度提升数据查询的效率。

例如：student_info 表里有 10w 条数据，如果想要查询 student_id = 173368 的用户信息，添加索引和不添加索引的区别还是很大的。查询效果如下图所示：

在这里插入图片描述

由此可见，效率的提升还是很明显的。

03、经常 group by 和 order by 的列

索引就是让数据按照某种顺序进行存储或检索，因此当使用 group by 对数据进行分组查询，或者使用 order by 对数据进行排序的时候，就需要对分组或者排序的字段进行索引了。如果待排序的列有多个，就可以在这些列上建立组合索引。

比如：按照 student_id 对学生选修的课程进行分组，显示不同的 student_id 和课程数量。

SELECT student_id, COUNT(student_id) AS num FROM student_info GROUP BY student_id;

在这里插入图片描述

由此可见，效率提升明显，并且得到的结果中 student_id 字段的数值也是按照顺序展示的。

order by 语句的效果与 group by 是一致的。因为添加索引时 B+Tree 本身就排完序了，使用 group by 或 order by 时可以直接取数据了（索引相当于提前把数据查出来放入缓存里，去查询时直接从缓存中取数据）。

04、update、delete 语句的 where 条件列

当我们对某条数据进行 update 或 delete 操作的时候，是否也需要对 where 的条件列创建索引呢？

例如：现在想把 name 为 NKmdJX 对应的 student_id 修改为 10002，加了索引和不加索引的执行效果：
在这里插入图片描述

由此可以看出，添加索引后执行效率有很大地提升。

下面再来试一下 delete 语句中，添加了索引和不添加索引的区别：
在这里插入图片描述

由此可见，添加索引后，delete 语句的执行效率也有很大地提升。

总结：对数据按照某个条件进行查询后再进行 update 或 delete 操作时，如果对 where 字段创建了索引，就能大幅度提升效率。原理是因为我们需要先根据 where 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

05、distinct 字段需要创建索引

如果需要对某个字段进行去重，使用 distinct 时，如果对该字段创建索引，也会提升查询效率。

例如：现在想要查询课程表中不同的 student_id 都有哪些，对 student_id 创建索引和不创建索引的结果：

在这里插入图片描述

可以看到添加索引的效率比不加索引的效率明显是提升的，并且查出的字段还是按照递增的顺序进行展示的，这是因为索引会对数据按照某种顺序进行排序，所以在去重时也会快很多。

06、多表 join 连接操作时，创建索引注意事项

多表 join 连接操作时，创建索引注意事项：

首先，连接表的数量尽量不要超过 3 条，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长也会非常快，严重影响查询的效率。
其次，对 where 条件创建索引，因为 where 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 where 条件过滤还是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。也就是说假如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

数据类型不一致时，会进行隐式转换，从而导致索引失效。

例如：只对 student_id 创建索引和对 student_id、name 都创建索引，执行的效率：
在这里插入图片描述

07、使用列的类型小的创建索引

这里所说的类型大小指的就是该类型表示的数据范围的大小。

在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TININT、MEDIUMINT、INT、BIGINT 等等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型（MEDIUMINT -> INT -> BIGINT），有两个原因：

数据类型越小，查询时的效率越高；
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放得下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键值使用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O。

08、使用字符串前缀创建索引

假如字符串很长，存储一个字符串就需要占用很大的存储空间。当需要为这个字符串建立索引时，那就意味着在对应的 B+Tree 中有两个问题：

B+Tree 索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间就越大。
如果 B+Tree 索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

所以，可以通过截取字段的前面一部分内容建立索引，就叫做前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，再根据前缀相同的记录的主键值回表查询完整的字符串值。既节约了空间，又减少了字符串的比较时间，还大体能解决排序的问题。

09、区分度高（散列性高）的列适合作为索引

列的基数指的是某一列中不重复的个数，比如说某个列包含值 2，5，8，2，5，8，2，5，8，虽然有 9 条记录，但是该列的基数是 3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。列的基数直接影响到我们能否有效的利用索引，最好为列的基数大的列创建索引，为基数太小的列建立索引效果不太好。

注意：联合索引把区分度高的列（散列性高）放在前面。

10、使用最频繁的列放到联合索引的左侧（最左前缀原则）

根据最左前缀原则，可以增加联合索引的使用率。

11、在多个字段都要创建索引的情况下，联合索引优于单值索引

当有多个单值索引，使用时会优先执行一个，所以在多个值都需要索引的时候，建议创建联合索引。

创建联合索引时，使用最频繁的字段必须放在最左边，如果不频繁，建议把区分度高的字段放在最前面。

explain 计划中有两个字段：possible_key、key。其中，possible_key 代表可能使用到的索引，经过优化器判断后会选择一个最优索引执行。如果可能用到的索引过多，优化器在判别最优索引时耗时较大，影响性能。

6.5.2 哪些情况下不适合创建索引？

01、在 where 中使用不到的字段，不要设置索引

where 条件（包括 group by、order by）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是无需创建索引的。

SELECT course_id, student_id, create_time FROM student_info WHERE student_id = 41251;

例如：这个是按照 student_id 来进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在 select 字段中。

02、数据量小的表最好不要使用索引

如果表里记录的数据太少，比如少于 1000 个，那么是不需要创建索引的。表记录太少的话，是否创建索引对查询效率的影响是并不大的。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

03、有大量重复数据的列上不要建立索引

在条件表达式中，建议给经常用到的不同值较多的列上建立索引，但是字段中如果有大量重复数据，也不用建立索引。

比如：在学生表的 “性别” 字段上只有 <男><女> 两个不同的值，所以无需建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据的更新速度。

如果要在 100w 行数据中查找其中的 50w 行（性别为男的数据），一旦创建了索引，就需要先访问 50w 次索引，然后再访问 50w 次数据表，这样加起来的开销比不适用索引可能还要大。

04、避免对经常更新的表创建过多的索引

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

05、不建议用无序的值作为索引

例如身份证、UUID（在索引比较时需要转为 ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。

06、删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引将它们删除，从而减少索引对更新操作的影响。

07、不要定义冗余或重复的索引

① 冗余索引

CREATE TABLE person_info(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	birthday DATE NOT NULL,
	phone_number CHAR(11) NOT NULL,
	country varchar(100) NOT NULL,
	PRIMARY KEY (id),
	KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
	KEY idx_name (name(10))
);

例如：通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

② 重复索引

CREATE TABLE repeat_index_demo (
	col1 INT PRIMARY KEY,
	col2 INT,
	UNIQUE uk_idx_c1 (col1),
	INDEX idx_c1 (col1)
);

例如：col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就
会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

6.6 小结

索引是一把双刃剑，既可以提高查询效率，但是也会降低插入和更新的速度、占用磁盘空间。

选择索引的最终目的是为了使查询的速度变快。上文中给出的建议只是基本的准则，在使用中要根据实际情况进行分析和判断，选择最合适的索引方式。

EijoaHoaiQ:D

关注

9
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
MySQL进阶之（六）索引的使用与设计原则

① 冗余索引例如：通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。② 重复索引col2 INT,例如：col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。
复制链接

扫一扫