👉🏻索引
MySQL的索引是数据库管理系统中一个非常重要的部分,它用于快速查找和访问数据库中的数据。索引就像一本书的目录,可以帮助我们更快地定位到书中的特定信息。
以下是关于MySQL索引的一些关键点:
-
定义:
- 索引是一个
数据结构
,它允许数据库系统快速访问数据表中的特定数据。 - 它由数据库管理系统自动创建和维护,用于加速数据的检索速度。
- 索引是一个
-
类型:
- B-Tree索引:最常见的索引类型,适用于全值匹配、范围查询等。
- 哈希索引:基于哈希表的索引,适用于等值查询。
- 空间索引 (R-Tree):用于地理空间数据类型。
- 全文索引:用于全文搜索。
- 聚簇索引(InnoDB特有的):数据行的物理顺序与键值的逻辑(索引)顺序相同。
- 非聚簇索引:数据行的物理顺序与键值的逻辑(索引)顺序不同。
-
优点:
- 提高数据检索速度。
- 加速表与表之间的连接。
- 使用索引可以在查询的过程中,使用优化隐藏器,提高系统的性能。
-
缺点:
- 创建索引和维护索引需要时间,这会降低插入、更新和删除的速度,因为每当表中的数据更改时,索引也必须进行更新。
- 索引需要占用磁盘空间。
-
何时使用索引:
- 在经常需要搜索的列上创建索引。
- 在经常作为连接条件的列上创建索引。
- 在经常需要根据范围进行搜索的列上创建索引。
- 在经常需要排序的列上创建索引。
- 在经常需要作为WHERE子句条件的列上创建索引。
-
注意事项:
- 不要过度索引,只创建真正需要的索引。
- 删除不再需要的索引。
- 使用
EXPLAIN
命令来查看查询是否使用了索引以及如何使用索引。 - 定期分析表和索引的使用情况,以便进行优化。
- 考虑使用复合索引(多个列的组合索引)来优化多列的查询条件。
-
创建和删除索引:
- 使用
CREATE INDEX
语句创建索引。 - 使用
DROP INDEX
语句删除索引。 - 在创建表时,也可以直接在
CREATE TABLE
语句中定义索引。
- 使用
👉🏻主键索引
主键索引的创建
主键索引是MySQL中用于唯一标识表中每一行记录的索引。它确保表中的每一行数据都具有唯一的标识,并且不允许有空值。主键索引的创建可以通过以下两种方式:
- 在创建表时直接指定
CREATE TABLE students (
id INT NOT NULL,
name VARCHAR(50),
age INT,
PRIMARY KEY (id)
);
在这个例子中,id
列被设置为主键,MySQL会自动为这个列创建主键索引。
- 在表创建后添加
如果表已经被创建,但是还没有主键索引,可以使用ALTER TABLE
语句来添加:
ALTER TABLE students ADD PRIMARY KEY (id);
主键索引的查询
主键索引的查询通常非常快速,因为MySQL可以直接通过主键索引定位到数据。查询时,可以直接使用主键值作为WHERE子句的条件:
SELECT * FROM students WHERE id = 1;
在这个例子中,MySQL会利用主键索引快速定位到id
为1的记录,并返回查询结果。
主键索引的删除
在某些情况下,可能需要删除主键索引。删除主键索引之前,需要确保没有其他依赖于主键的约束或关联关系存在。删除主键索引的步骤如下:
- 使用
SHOW CREATE TABLE
命令查看表的结构
这个命令会显示表的创建语句,包括主键索引的定义。
SHOW CREATE TABLE students;
- 确定要删除的主键索引的名称
虽然主键索引通常没有显式的名称(除非在创建时指定了),但在某些情况下,可能需要知道它的名称以便删除。通常,主键索引的名称就是“PRIMARY”。
- 使用
ALTER TABLE
语句删除主键索引
ALTER TABLE students DROP PRIMARY KEY;
执行这个命令后,主键索引将被删除,并且id
列将不再具有唯一性约束。需要注意的是,删除主键索引后,表中的数据可能不再具有唯一性标识,这可能会影响到数据的完整性和查询性能。因此,在删除主键索引之前,需要仔细考虑并确保没有其他依赖于主键的约束或关联关系存在。
👉🏻唯一索引
唯一索引的创建
唯一索引确保索引列中的每个值都是唯一的,与主键索引类似,但允许有空值。以下是创建唯一索引的几种方式:
- 创建索引时指定唯一性
使用CREATE UNIQUE INDEX
语句来创建唯一索引:
CREATE UNIQUE INDEX index_name ON table_name(column_name);
这里的index_name
是你为索引指定的名称,table_name
是表名,column_name
是你希望创建索引的列名。
- 在修改表结构时添加唯一索引
使用ALTER TABLE
语句和ADD UNIQUE
子句来添加唯一索引:
ALTER TABLE table_name ADD UNIQUE (column_name);
或者,如果你希望给唯一索引指定一个名称:
ALTER TABLE table_name ADD UNIQUE index_name (column_name);
- 在创建表时直接指定唯一索引
在创建表的时候,可以直接在列定义后使用UNIQUE
关键字来指定唯一索引:
CREATE TABLE table_name (
column_name datatype UNIQUE,
...
);
或者,如果你希望为多个列组合创建唯一索引:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...,
UNIQUE (column1, column2)
);
唯一索引的查询
要查询MySQL表中是否存在唯一索引,你需要查看该表的索引信息。以下是如何查询表中所有索引(包括唯一索引)的步骤:
- 连接到MySQL数据库并选择目标数据库。
- 使用
SHOW INDEX FROM
语句查询表的索引信息:
SHOW INDEX FROM table_name;
在返回的结果中,Non_unique
列表示索引是否唯一(0表示唯一索引,1表示非唯一索引)。
唯一索引的删除
要删除MySQL表中的唯一索引,可以使用ALTER TABLE
语句或DROP INDEX
语句。以下是两种删除方法:
- 使用ALTER TABLE语句删除
ALTER TABLE table_name DROP INDEX index_name;
这里的index_name
是你要删除的唯一索引的名称。
- 使用DROP INDEX语句删除
如果你知道索引的名称,并且想要明确地删除它,可以使用DROP INDEX
语句:
DROP INDEX index_name ON table_name;
再次强调,index_name
是你要删除的唯一索引的名称,table_name
是表名。
👉🏻普通索引
普通索引的创建
普通索引(也称为单列索引或二级索引)是MySQL中最基本的索引类型,它没有任何限制,只是简单地加快数据的检索速度。以下是创建普通索引的几种方式:
- 创建索引时指定
使用CREATE INDEX
语句来创建普通索引:
CREATE INDEX index_name ON table_name(column_name);
这里的index_name
是你为索引指定的名称,table_name
是表名,column_name
是你希望创建索引的列名。
- 在修改表结构时添加索引
使用ALTER TABLE
语句和ADD INDEX
子句来添加普通索引:
ALTER TABLE table_name ADD INDEX index_name (column_name);
- 在创建表时直接指定索引
在创建表的时候,可以直接在列定义后使用INDEX
或KEY
关键字来指定普通索引:
CREATE TABLE table_name (
column_name datatype,
...,
INDEX index_name (column_name)
);
或者:
CREATE TABLE table_name (
column_name datatype,
...,
KEY index_name (column_name)
);
普通索引的查询
要查询MySQL表中是否存在普通索引,你需要查看该表的索引信息。以下是如何查询表中所有索引(包括普通索引)的步骤:
- 连接到MySQL数据库并选择目标数据库。
- 使用
SHOW INDEX FROM
语句查询表的索引信息:
SHOW INDEX FROM table_name;
在返回的结果中,Key_name
列表示索引的名称,Column_name
列表示索引关联的列名,Non_unique
列表示索引是否唯一(1表示非唯一索引,0表示唯一索引)。
普通索引的删除
要删除MySQL表中的普通索引,可以使用ALTER TABLE
语句或DROP INDEX
语句。以下是两种删除方法:
- 使用ALTER TABLE语句删除
ALTER TABLE table_name DROP INDEX index_name;
这里的index_name
是你要删除的普通索引的名称。
- 使用DROP INDEX语句删除
如果你知道索引的名称,并且想要明确地删除它,可以使用DROP INDEX
语句:
DROP INDEX index_name ON table_name;
再次强调,index_name
是你要删除的普通索引的名称,table_name
是表名。
请注意,在删除索引之前,你应该确保这个索引不再被查询或其他操作所使用,因为删除索引可能会影响查询性能。
👉🏻全文索引
全文索引的创建
MySQL的全文索引主要用于在InnoDB或MyISAM表中搜索信息。以下是创建全文索引的几种方式:
-
在创建表时添加全文索引:
CREATE TABLE table_name ( column1 datatype, column2 datatype, ..., FULLTEXT (column_name) );
这里,
FULLTEXT (column_name)
指定了需要在哪个列上创建全文索引。 -
在已经存在的表上添加全文索引:
ALTER TABLE table_name ADD FULLTEXT (column_name);
或者,如果你希望给全文索引指定一个名称:
ALTER TABLE table_name ADD FULLTEXT index_name (column_name);
全文索引的查询
全文索引主要使用MATCH()
和AGAINST()
函数来进行搜索操作。基本语法格式为:
SELECT column_name(s) FROM table_name WHERE MATCH(col1, col2, ...) AGAINST (expr [search_modifier]);
其中,MATCH(col1, col2, ...)
定义了要在哪些列上进行全文搜索,而AGAINST(expr [search_modifier])
则指定了要搜索的字符串和搜索模式。
搜索模式可以是以下几种类型:
- 自然语言模式搜索(默认):不使用任何修饰符。
- 布尔模式搜索:使用
IN BOOLEAN MODE
修饰符,支持操作符,如+
表示包含,-
表示不包含。 - 查询扩展模式搜索:使用
WITH QUERY EXPANSION
修饰符,相当于自然语言模式下的一个扩展,执行两次检索。
全文索引的删除
要删除MySQL表中的全文索引,可以使用ALTER TABLE
语句结合DROP INDEX
子句。语法如下:
ALTER TABLE table_name DROP INDEX index_name;
这里的index_name
是你要删除的全文索引的名称。如果全文索引是在创建表时直接定义的,并且没有指定名称(即使用了默认的索引名),则可以通过查看表的索引信息来确定索引的名称。
注意:
- 全文索引只能用于MyISAM和InnoDB存储引擎的表。
- 全文索引只能为CHAR、VARCHAR或TEXT列创建。
- 在MySQL 5.7.6及更高版本中,全文索引支持中文、日文和韩文(CJK)的内置全文ngram解析器。
- 对于大型数据集,将数据加载到没有FULLTEXT索引的表中然后创建索引,通常比将数据加载到具有现有FULLTEXT索引的表中要快得多。
- 可以通过修改MySQL配置文件(如
my.cnf
)中的innodb_ft_min_token_size
和innodb_ft_max_token_size
参数来调整全文索引中单词的最小和最大长度。 - 如果需要支持3个字符的全文索引,可以将
ft_min_word_len
的值修改为3,并重启MySQL服务。
如上便是本期的所有内容了,如果喜欢并觉得有帮助的话,希望可以博个点赞+收藏+关注🌹🌹🌹❤️ 🧡 💛,学海无涯苦作舟,愿与君一起共勉成长