【MySQL】索引

在这里插入图片描述

欢迎来到Cefler的博客😁
🕌博客主页:折纸花满衣
🏠个人专栏:MySQL

在这里插入图片描述


👉🏻索引

MySQL的索引是数据库管理系统中一个非常重要的部分,它用于快速查找和访问数据库中的数据。索引就像一本书的目录,可以帮助我们更快地定位到书中的特定信息。

以下是关于MySQL索引的一些关键点:

  1. 定义

    • 索引是一个数据结构,它允许数据库系统快速访问数据表中的特定数据。
    • 它由数据库管理系统自动创建和维护,用于加速数据的检索速度。
  2. 类型

    • B-Tree索引:最常见的索引类型,适用于全值匹配、范围查询等。
    • 哈希索引:基于哈希表的索引,适用于等值查询。
    • 空间索引 (R-Tree):用于地理空间数据类型。
    • 全文索引:用于全文搜索。
    • 聚簇索引(InnoDB特有的):数据行的物理顺序与键值的逻辑(索引)顺序相同。
    • 非聚簇索引:数据行的物理顺序与键值的逻辑(索引)顺序不同。
  3. 优点

    • 提高数据检索速度。
    • 加速表与表之间的连接。
    • 使用索引可以在查询的过程中,使用优化隐藏器,提高系统的性能。
  4. 缺点

    • 创建索引和维护索引需要时间,这会降低插入、更新和删除的速度,因为每当表中的数据更改时,索引也必须进行更新。
    • 索引需要占用磁盘空间。
  5. 何时使用索引

    • 在经常需要搜索的列上创建索引。
    • 在经常作为连接条件的列上创建索引。
    • 在经常需要根据范围进行搜索的列上创建索引。
    • 在经常需要排序的列上创建索引。
    • 在经常需要作为WHERE子句条件的列上创建索引。
  6. 注意事项

    • 不要过度索引,只创建真正需要的索引。
    • 删除不再需要的索引。
    • 使用EXPLAIN命令来查看查询是否使用了索引以及如何使用索引。
    • 定期分析表和索引的使用情况,以便进行优化。
    • 考虑使用复合索引(多个列的组合索引)来优化多列的查询条件。
  7. 创建和删除索引

    • 使用CREATE INDEX语句创建索引。
    • 使用DROP INDEX语句删除索引。
    • 在创建表时,也可以直接在CREATE TABLE语句中定义索引。

👉🏻主键索引

主键索引的创建

主键索引是MySQL中用于唯一标识表中每一行记录的索引。它确保表中的每一行数据都具有唯一的标识,并且不允许有空值。主键索引的创建可以通过以下两种方式:

  1. 在创建表时直接指定
CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id)
);

在这个例子中,id列被设置为主键,MySQL会自动为这个列创建主键索引。

  1. 在表创建后添加

如果表已经被创建,但是还没有主键索引,可以使用ALTER TABLE语句来添加:

ALTER TABLE students ADD PRIMARY KEY (id);

主键索引的查询

主键索引的查询通常非常快速,因为MySQL可以直接通过主键索引定位到数据。查询时,可以直接使用主键值作为WHERE子句的条件:

SELECT * FROM students WHERE id = 1;

在这个例子中,MySQL会利用主键索引快速定位到id为1的记录,并返回查询结果。

主键索引的删除

在某些情况下,可能需要删除主键索引。删除主键索引之前,需要确保没有其他依赖于主键的约束或关联关系存在。删除主键索引的步骤如下:

  1. 使用SHOW CREATE TABLE命令查看表的结构

这个命令会显示表的创建语句,包括主键索引的定义。

SHOW CREATE TABLE students;
  1. 确定要删除的主键索引的名称

虽然主键索引通常没有显式的名称(除非在创建时指定了),但在某些情况下,可能需要知道它的名称以便删除。通常,主键索引的名称就是“PRIMARY”。

  1. 使用ALTER TABLE语句删除主键索引
ALTER TABLE students DROP PRIMARY KEY;

执行这个命令后,主键索引将被删除,并且id列将不再具有唯一性约束。需要注意的是,删除主键索引后,表中的数据可能不再具有唯一性标识,这可能会影响到数据的完整性和查询性能。因此,在删除主键索引之前,需要仔细考虑并确保没有其他依赖于主键的约束或关联关系存在。

👉🏻唯一索引

唯一索引的创建

唯一索引确保索引列中的每个值都是唯一的,与主键索引类似,但允许有空值。以下是创建唯一索引的几种方式:

  1. 创建索引时指定唯一性

使用CREATE UNIQUE INDEX语句来创建唯一索引:

CREATE UNIQUE INDEX index_name ON table_name(column_name);

这里的index_name是你为索引指定的名称,table_name是表名,column_name是你希望创建索引的列名。

  1. 在修改表结构时添加唯一索引

使用ALTER TABLE语句和ADD UNIQUE子句来添加唯一索引:

ALTER TABLE table_name ADD UNIQUE (column_name);

或者,如果你希望给唯一索引指定一个名称:

ALTER TABLE table_name ADD UNIQUE index_name (column_name);
  1. 在创建表时直接指定唯一索引

在创建表的时候,可以直接在列定义后使用UNIQUE关键字来指定唯一索引:

CREATE TABLE table_name (
    column_name datatype UNIQUE,
    ...
);

或者,如果你希望为多个列组合创建唯一索引:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...,
    UNIQUE (column1, column2)
);

唯一索引的查询

要查询MySQL表中是否存在唯一索引,你需要查看该表的索引信息。以下是如何查询表中所有索引(包括唯一索引)的步骤:

  1. 连接到MySQL数据库并选择目标数据库。
  2. 使用SHOW INDEX FROM语句查询表的索引信息:
SHOW INDEX FROM table_name;

在返回的结果中,Non_unique列表示索引是否唯一(0表示唯一索引,1表示非唯一索引)。

唯一索引的删除

要删除MySQL表中的唯一索引,可以使用ALTER TABLE语句或DROP INDEX语句。以下是两种删除方法:

  1. 使用ALTER TABLE语句删除
ALTER TABLE table_name DROP INDEX index_name;

这里的index_name是你要删除的唯一索引的名称。

  1. 使用DROP INDEX语句删除

如果你知道索引的名称,并且想要明确地删除它,可以使用DROP INDEX语句:

DROP INDEX index_name ON table_name;

再次强调,index_name是你要删除的唯一索引的名称,table_name是表名。

👉🏻普通索引

普通索引的创建

普通索引(也称为单列索引或二级索引)是MySQL中最基本的索引类型,它没有任何限制,只是简单地加快数据的检索速度。以下是创建普通索引的几种方式:

  1. 创建索引时指定

使用CREATE INDEX语句来创建普通索引:

CREATE INDEX index_name ON table_name(column_name);

这里的index_name是你为索引指定的名称,table_name是表名,column_name是你希望创建索引的列名。

  1. 在修改表结构时添加索引

使用ALTER TABLE语句和ADD INDEX子句来添加普通索引:

ALTER TABLE table_name ADD INDEX index_name (column_name);
  1. 在创建表时直接指定索引

在创建表的时候,可以直接在列定义后使用INDEXKEY关键字来指定普通索引:

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表中是否存在普通索引,你需要查看该表的索引信息。以下是如何查询表中所有索引(包括普通索引)的步骤:

  1. 连接到MySQL数据库并选择目标数据库。
  2. 使用SHOW INDEX FROM语句查询表的索引信息:
SHOW INDEX FROM table_name;

在返回的结果中,Key_name列表示索引的名称,Column_name列表示索引关联的列名,Non_unique列表示索引是否唯一(1表示非唯一索引,0表示唯一索引)。

普通索引的删除

要删除MySQL表中的普通索引,可以使用ALTER TABLE语句或DROP INDEX语句。以下是两种删除方法:

  1. 使用ALTER TABLE语句删除
ALTER TABLE table_name DROP INDEX index_name;

这里的index_name是你要删除的普通索引的名称。

  1. 使用DROP INDEX语句删除

如果你知道索引的名称,并且想要明确地删除它,可以使用DROP INDEX语句:

DROP INDEX index_name ON table_name;

再次强调,index_name是你要删除的普通索引的名称,table_name是表名。

请注意,在删除索引之前,你应该确保这个索引不再被查询或其他操作所使用,因为删除索引可能会影响查询性能。

👉🏻全文索引

全文索引的创建

MySQL的全文索引主要用于在InnoDB或MyISAM表中搜索信息。以下是创建全文索引的几种方式:

  1. 在创建表时添加全文索引

    CREATE TABLE table_name (
        column1 datatype,
        column2 datatype,
        ...,
        FULLTEXT (column_name)
    );
    

    这里,FULLTEXT (column_name)指定了需要在哪个列上创建全文索引。

  2. 在已经存在的表上添加全文索引

    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_sizeinnodb_ft_max_token_size参数来调整全文索引中单词的最小和最大长度。
  • 如果需要支持3个字符的全文索引,可以将ft_min_word_len的值修改为3,并重启MySQL服务。

如上便是本期的所有内容了,如果喜欢并觉得有帮助的话,希望可以博个点赞+收藏+关注🌹🌹🌹❤️ 🧡 💛,学海无涯苦作舟,愿与君一起共勉成长
在这里插入图片描述
在这里插入图片描述

  • 27
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值