MySQL 之索引（简单版）

最新推荐文章于 2024-07-25 16:06:56 发布
最新推荐文章于 2024-07-25 16:06:56 发布
阅读量800 收藏 27
点赞数 10
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_52112756/article/details/140000229
版权

在这里插入图片描述

一、介绍

索引是一种数据结构，例如 B-Tree，它提高了从表中检索数据行的速度，但需要额外的写入和存储来维护它。

查询优化器可以使用索引来快速定位数据，而不必针对给定查询扫描表的每一行。

当您使用 主键 或 唯一键创建表 时，MySQL 会自动创建一个名为 PRIMARY 的索引。该索引称为 聚集索引。

PRIMARY 索引是特殊的，因为索引本身与数据一起存储在同一个表中。聚集索引强制执行表中行的顺序。

PRIMARY 索引以外的其他索引称为二级索引或非聚集索引。

二、MySQL 索引类型

默认情况下，如果您不指定索引类型，MySQL 将创建 B-Tree 索引。下面显示了基于表的存储引擎允许的索引类型：

存储引擎允许的索引类型
InnoDBBTREE
MyISAMBTREE
MEMORY/HEAPHASH，BTREE

在 InnoDB 中的索引种类？

物理存储上：聚簇索引、非聚簇索引（二级索引）

字段个数上：单列索引、联合索引

功能上：主键索引、唯一索引、普通索引、全文索引

三、创建索引

索引可以提高对表的数据检索速度。在 MySQL 数据库中，您可以使用 CREATE INDEX 语句向表提交索引。

对于一个具有大量数据行的表，如果你根据某个查询条件检索数据时很慢，可能是因为你没有在检索条件相关的列上创建索引。

索引类似于词典中的目录。如果您想要词典中查询一个词，正确的做法是先查看目录，再根据目录中的指示到指定的页面找到相关的词。正确的索引可以显著提高从数据库表中检索数据行的速度。

MySQL 允许您使用 CREATE INDEX 语句在指定的表上为指定的列创建索引。

1、CREATE INDEX 语法

您应该按照如下的语法使用 CREATE INDEX 为一个表添加一个索引：

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

说明：

  • UNIQUE 关键字表明此索引为 唯一索引。它是可选的。

  • index_name 是索引的名字。一个表中不应该出现两个相同名字的索引。

  • table_name 是表的名字。

  • column_list 是表中的列名。多个列名使用逗号分隔。

  • USING 子句指定索引的类型。可选值：BTREE，HASH。它是可选的。

  • algorithm_option 指定删除索引的算法。它使用以下的语法：

    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

    ALGORITHM 子句是可选的。默认为 INSTANT。如果不支持 INSTANT，则使用 INPLACE。

    使用 DEFAULT 和省略 ALGORITHM 子句效果相同。

    以下是对各个算法的说明：

    • COPY：对原表的副本进行操作，将原表中的表数据逐行复制到新表中。不允许并发 DML。
    • INPLACE：操作避免复制表数据，但可能会就地重建表。在操作的准备和执行阶段，可能会短暂地对表进行独占元数据锁定。通常，支持并发 DML。
    • INSTANT：操作只修改数据字典中的元数据。在操作的执行阶段，可能会短暂地对表进行行独占元数据锁定。表数据不受影响，使操作瞬间完成。允许并发 DML。（在MySQL 8.0.12 中引入）

  • lock_option 指定删除索引的并发控制策略。它使用以下语法：

    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

    LOCK 子句是可选的。以下是对各个并发策略的说明：

    • DEFAULT

      给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的最大并发级别：如果支持，则允许并发读取和写入。如果不是，则允许并发读取（如果支持）。如果不是，则强制执行独立访问。

    • NONE

      如果支持，允许并发读取和写入。否则，会发生错误。

    • SHARED

      如果支持，允许并发读取但阻止写入。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发写入，写入也会阻止。如果不支持并发读取，则会发生错误。

    • EXCLUSIVE

      强制执行独占访问。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发读/写，也会这样做。

在 MySQL 内部，CREATE INDEX 语句被映射为 ALTER TABLE … ADD INDEX … 语句。

2、CREATE INDEX 实例

在以下实例中，我们使用 Sakila 示例数据库 作为演示数据库 actor 表 进行演示。

以下语句查找名字为 NICK 的演员：

SELECT * FROM sakila.actor WHERE first_name = 'NICK';

这是输出：

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|       44 | NICK       | STALLONE  | 2006-02-15 04:34:33 |
|      166 | NICK       | DEGENERES | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

您可以是使用 EXPLAIN 来查看以上 SELECT 语句的执行计划，以了解 MySQL 在内部如何执行此查询，如下所示：

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  201 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如您所见，MySQL 必须扫描由 201 行组成的整个表才能找到具有符合条件的行。

现在，让我们使用以下 CREATE INDEX 语句为该列 first_name 创建索引：

CREATE INDEX first_name ON actor(first_name);

要查看索引是否创建成功，请使用以下 SHOW INDEXES 语句显示表 actor 的索引，例如：

SHOW INDEXES FROM actor;

这是输出：

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor |          0 | PRIMARY             |            1 | actor_id    | A         |         201 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | idx_actor_last_name |            1 | last_name   | A         |         122 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | first_name          |            1 | first_name  | A         |         129 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

然后，再次执行上面的 EXPLAIN 语句：

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

输出是：

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | first_name    | first_name | 182     | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

如您所见，MySQL 只需从 first_name 指示的索引中定位其中的 3 行，而无需扫描整个表。

3、结论

在 MySQL 中，索引能提高从表中查询数据的效率。您可以使用 CREATE INDEX 为表创建索引。

四、删除索引

在MySQL 数据库中，您可以使用 DROP INDEX 从表中删除已有的索引。

有时候，您可能因为以下一些原因想要从一个表中删除一个或多个索引：

  • 创建了错误的索引
  • 为了更快，在插入或更新大量的数据之前先删除索引

MySQL 允许您使用 DROP INDEX 从表中删除已有的索引。

1、DROP INDEX 语法

您应该按照如下的语法使用 DROP INDEX 从一个表中删除一个索引：

DROP INDEX index_name
ON table_name
[algorithm_option | lock_option];

说明：

  • index_name 是索引的名字。

  • table_name 是表的名字。

  • algorithm_option 指定删除的索引算法。它使用以下的语法：

    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

    ALGORITHM 子句是可选的。默认为 INPLACE。如果不支持 INPLACE，则使用 COPY。

    使用 DEFAULT 和省略 ALGORITHM 子句效果相同。

    以下是对各个算法的说明：

    • COPY：对原表的副本进行操作，将原表中的表数据逐行复制到新表中。不允许并发 DML。
    • INPLACE：操作避免复制表数据，但可能会就地重建表。在操作的准备和执行阶段，可能会短暂地对表进行独占元数据锁定。通常，支持并发 DML。

  • lock_option 指定删除索引的并发控制策略。它使用以下语法：

    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

    LOCK 子句是可选的。以下是对各个并发策略的说明。

    • DEFAULT

      给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的最大并发级别：如果支持，则允许并发读取和写入。如果不是，则允许并发读取（如果支持）。如果不是，则强制执行独立访问。

    • NONE

      如果支持，允许并发读取和写入。否则，会发生错误。

    • SHARED

      如果支持，允许并发读取但阻止写入。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发写入，写入也会阻止。如果不支持并发读取，则会发生错误。

    • EXCLUSIVE

      强制执行独占访问。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发读/写，也会这样做。

在 MySQL 内部，DROP INDEX 语句被映射为 ALTER TABLE … DROP INDEX … 语句。

2、DROP INDEX 实例

在 MySQL 创建索引教程中，我们在我们在 Sakila 示例数据库中的 actor 表创建了一个索引 first_name。

现在，我们将使用下面的语句将它删除：

DROP INDEX first_name ON actor;

要查看索引是否删除成功，请使用以下 SHOW INDEXES 语句显示表 actor 的索引，例如：

SHOW INDEXES FROM actor;

这是输出：

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor |          0 | PRIMARY             |            1 | actor_id    | A         |         201 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | idx_actor_last_name |            1 | last_name   | A         |         122 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

3、删除主键索引

在 MySQL 中，主键的索引名字固定为 PRIMARY。要删除表 t 上的主键索引，请使用下面的语句：

DROP INDEX `PRIMARY` ON t;

4、结论

在 MySQL 中，您可以使用 DROP INDEX 为从表中删除指定的索引。

五、显示索引

在 MySQL 中，您可以使用 SHOW INDEXES 命令从表中查询索引信息。

1、SHOW INDEXES 语法

查询表索引信息，使用 SHOW INDEXES 如下语句：

SHOW INDEXES FROM db_name.table_name;

或

SHOW INDEXES FROM table_name IN db_name;

说明：

  • db_name 指示数据库的名字。如果您可以选择了数据库，则它可以省略。
  • table_name 指示表的名字。
  • 关键字 INDEXES 可以被替换为 INDEX 或者 KEYS。
  • 关键字 IN 可以替换为 FROM。
  • 关键字 FROM 可以替换为 IN。

2、WHERE 子句

您可以在 SHOW INDEXES 语句中使用 WHERE 语句过滤结果。如下：

SHOW INDEXES FROM db_name.table_name WHERE condition;

3、SHOW INDEXES 返回的列

在这里插入图片描述

  • Table：表名
  • Non_unique：是否唯一索引。如果不是，则为 1，否则为 0。
  • Key_name：索引的名称。主键 索引的名称固定为 PRIMARY。
  • Seq_in_index：索引中的序列号。第一列号从 1 开始。
  • Column_name：列名
  • Collation：排序规则表示列在索引中的排序方式。A 表示升序、B 表示降序或 NULL 表现未排序。
  • Cardinality：索引基数，它是索引中为唯一值的估计数量。注意，这个数字是不精确的，只是一个估计值。请注意，基础越高，查询优化器使用索引进行查找的机会就越大。
  • Sub_part：索引前缀。如果整个列都被索引，则为 NULL。否则，它会在列被部分索引的情况下显示索引字符数。
  • Packed：指示键是如何打包的；如果不是，则为 NULL。
  • Null：如果该列可能包含 NULL 值为 YES，如果不包含则为空白。
  • Index_type：索引类型。可能的值：BTREE、HASH、RTREE 或 FULLTEXT。
  • Comment：在其自己的列中描述的有关索引的信息。
  • Index_comment：显示创建索引时使用 COMMENT 属性指定的索引的注释。
  • Visible：索引对查询优化器是否可见或不可见；如果可见为 YES，否则为 NO。
  • Expression：如果索引使用表达式而不是列或列前缀值时有值。此时 column_name 列为 NULL。

4、SHOW INDEXES 实例

在以下实例中，我们使用 Sakila 示例数据库中的 film 表进行演示。

①显示全部索引

要显示 film 表中的所有的索引，请使用以下语句：

SHOW INDEXES FROM sakila.film;

+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                    | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| film  |          0 | PRIMARY                     |            1 | film_id              | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_title                   |            1 | title                | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_fk_language_id          |            1 | language_id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_fk_original_language_id |            1 | original_language_id | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

②过滤索引

您可以使用 WHERE 子句对 SHOW INDEXES 的结果进行过滤。比如，如果您想要从 film 表中获取所有的唯一索引，请使用以下语句：

SHOW INDEXES FROM sakila.film WHERE Non_unique = 0;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| film  |          0 | PRIMARY  |            1 | film_id     | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

5、结论

在 MySQL 中，索引能提高从表中查询数据的效率。您可以使用 SHOW INDEXES 语句获取一个表的索引以了解该表中的索引情况。您还可以使用 WHERE 子句对 SHOW INDEXES 结果进行过滤。

亿先生@
关注
  • 10
    点赞
  • 踩
  • 27
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
Mysql之索引分享
12-14
综上所述，理解并合理利用MySQL索引，如选择合适的数据结构、创建有效的索引策略，以及利用优化特性如ICP，对于提升数据库性能至关重要。在设计数据库和编写SQL查询时，应充分考虑这些因素，以确保系统的高效运行。
mysql索引的新手入门详解
HealerJean梦想博客
07-27 4万+
前言 如果是小白的话，我觉得看到这篇文章一定会对博主赞赏有加，因为实在是太好了，没错，博主就是要让你喜欢上。 1、索引 索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。 2、索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即...
MySQL索引优化
simple_122的博客
08-11 524
1、最好能做到全值匹配，即查询条件中包含的字段和字段顺序与建立的复合索引完全一致——全值匹配我最爱； 2、最左前缀匹配法则，一个复合索引要想生效，查询条件中必须要包含复合索引最左边的列，并且一旦中间断掉，比如复合索引（name，age，address），查询时使用name和address，因为age断掉了，那么address的查询将用不到索引——带头大哥不能少，中间兄弟莫断掉； 3、不在索引列上做...
MySql之索引
猎户星座
11-05 3万+
Mysql 索引精讲 开门见山，直接上图，下面的思维导图即是现在要讲的内容，可以先有个印象～ 常见索引类型(实现层面) 索引种类(应用层面) 聚簇索引与非聚簇索引 覆盖索引 最佳索引使用策略 1.常见索引类型(实现层面) 首先不谈Mysql怎么实现索引的,先马后炮一下,如果让我们来设计数据库的索引，该怎么设计？ 我们首先思考一下索引到底想达到什么效果？其实就是想能够实现快速查...
MYSQL之索引语法与使用
指针先生
01-22 1443
如果索引了多列（联合索引），要遵守最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。尽量使用覆盖索引（查询使用了索引，并且要返回的列能在该索引中全部找到），减少select*（要回表查询，使性能下降）using index：查找使用了索引，但需要的数据在索引列能找到，不需要回表查询。用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引豆瓣不会被用到。在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建立联合索引，而非单列索引。
MySQL 之全文索引
潜心做事的博客
04-14 10万+
最近在复习数据库索引部分，看到了 fulltext，也即全文索引，虽然全文索引在平时的业务中用到的不多，但是感觉它有点儿意思，所以花了点时间研究一下，特此记录。 引入 概念 通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。 你可能会说，用 like...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL之索引失效情况
上善若泪
02-08 7604
文章目录1 MySQL索引1.1 简介1.1.1 索引创建1.1.2 索引类型1.1.3 索引优缺点1.2 SQL优化1.2.1 查看执行计划1.2.2 show profile分析1.2.3 trace2 索引失效2.1 问题引入2.2 准备工作2.2.1 创建user表2.2.2 插入数据2.2.3 查看数据库版本2.3 联合索引2.3.1 联合索引中索引有效2.3.2 联合索引中索引失效2.4 select *2.5 索引列上有计算2.6 索引列用了函数2.7 字段类型不同2.8 like左边包含%2
mysql之索引
m0_72167535的博客
01-23 1284
mysql之索引
mysql 索引
liulanba的博客
10-25 1万+
在 MySQL 中创建索引可以大大提高查询效率，加快数据检索速度，他的索引包含 普通索引和唯一索引，以及主键索引，联合索引。
MySQL联合索引用法示例
09-10
MySQL联合索引是一种优化查询性能的机制，它允许在多个列上创建单个索引，以便在查询中同时使用这些列时能更快地找到数据。本示例将深入讲解联合索引的概念，并通过实例来展示如何在MySQL中创建和使用联合索引。 ...
MySQL索引之聚集索引介绍
12-15
在MySQL里，聚集索引和非聚集索引分别是什么意思，有什么区别? 在MySQL中，InnoDB引擎表是(聚集)索引组织表(clustered index organize table)，而MyISAM引擎表则是堆组织表(heap organize table)。 也有人把聚集索引...
MySQL全文索引实现简单版搜索引擎实例代码
09-09
MySQL全文索引是一种高效的数据检索机制，尤其在处理大量文本数据时，能够快速找到包含特定词汇的记录。本文将深入探讨MySQL全文索引的实现、使用方法以及相关的配置参数。 首先，全文索引仅适用于InnoDB和MyISAM这...
MySQL创建全文索引分享
12-16
使用全文索引进行查询时，不能像使用`LIKE`关键字那样简单操作，而应该使用`MATCH`和`AGAINST`关键字。例如： ```sql SELECT * FROM article WHERE MATCH(title,content) AGAINST ('查询字符串'); ``` 这条SQL语句...
mysql高阶语句
qq_59169480的博客
07-24 912
高级语法的查询语句。
《MySQL DBA 修炼之道》第四章 开发进阶
最新发布
Trishia的博客
07-25 853
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
mysql 主从复制 读写分离 MHA
qq_64410777的博客
07-24 1032
mysql 的主从复制和读写分离：读写分离和MHA高可用的前提 主从复制。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

亿先生@

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值