MySQL第12章——高级技术——索引——索引概念、创建索引、查看索引、删除索引

本文链接：https://blog.csdn.net/m0_68657832/article/details/140884601

🌈hello，你好鸭，我是Ethan，西安电子科技大学大三在读，很高兴你能来阅读。

✔️目前博客主要更新Java系列、项目案例、计算机必学四件套等。
🏃人生之义，在于追求，不在成败，勤通大道。加油呀！

🔥个人主页：Ethan Yankang
🔥推荐：史上最强八股文||一分钟看完我的几百篇博客

🔥温馨提示：划到文末发现专栏彩蛋点击这里直接传送

🔥本篇概览：详细讲解了MySQL中索引概念、创建索引、查看索引、删除索引的方方面面🌈⭕🔥

【计算机领域一切迷惑的源头都是基本概念的模糊，算法除外】

🌈序言：

从0到1完完整整学习一遍MySQL语言，记住了，你学JAVA就要把他相关的所有知识都系统的学一学，后而习之，这样才算说有认真去对待这门语言。

🌈引出：

1.索引简介

        默认情况下，对于任何查询操作，数据库都会根据查询条件进行全表扫描，也就是从第一行数据一直扫描到最后一行数据，遇到符合条件的数据就会将其加入查询结果集中。表越大，查询所花费的时间就越多。对于大的表来说，如果想要快速查询出想要的数据，那么应该怎么处理更好呢?这个时候可以使用索引来实现了
        索引是建立在表中列上的一个数据库对象，在一个表中可以给一列或者多列设置索引。如果在查询数据时把设置的索引列作为查询列，那么就会大大提高查询速度。可能小伙伴们会问:“为什么？给某一个字段设置索引，查询的速度就会变快呢?
        这是因为如果给某一个字段设置了索引，那么查询的时候会先去索引列表中查询，而不是对整个表进行查询。索引列表是B类树的数据结果，查询时间复杂度为O(logn2)定位到特定值的行会非常快，所以其查询速度就会非常快。这里涉及一定的算法基础，不过小伙伴们只需要知道使用索引时查询速度非常快就可以了。
        使用索引的目的就是提高查询特定数据的速度。SQL索引在数据库优化中占有非常大的比例。
        一个好的索引设计，可以让查询效率提高几十倍甚至几百倍。例如一个表中有 10万行数据，现在要执行这样一个查询语句:select*from table whereid=10000;。如果不使用索引，那么就必须从头开始遍历整个表，直到找到id 等于 10 000 的这一行数据。但如果为id 字段设置了一个索引，则不需要遍历整个表，而是直接在索引里找到 1000

create index 索引名
on 表名(列名);

0然后得到这一行的数据。你没有看错，索引可以直接定位到第 10 000 行，而不需要从第一行数据开始逐行扫描。
当然，如果只是对像本书例子中代码在 10 行以内的表进行操作，那么有没有索引都没有区别,因为数据量实在太小了。索引主要用于加快大数据的查询速度，小数据使用索引的实际意义不大。

如果想要详细了解聚簇索引、非聚簇索引、覆盖索引以及他们的区别，请移步：

MySQL第十二章——高级技术——聚簇索引、非聚簇索引、覆盖索引的区别-CSDN博客

2.创建索引

在 MySQL 中，可以使用 create index 语句来创建索引。需要注意的是，只能对表创建索引而不能对视图创建索引。

语法:

create index 索引名
on 表名(列名);

举例:

CREATE INDEX name_index ON fruit(name);

实例

这是提供的实验表单：

建立索引：

CREATE INDEX name_index ON fruit(name);

索引创建运行结果如下：

当运行结果中有“OK”时，说明成功创建了一个名为“name index”的索引，该索引是针对name 这一列来创建的。

3.使用索引

成功查询

我们对name字段创建了索引，当我们对name 这一列进行查询时(比如执行下面的 SQL代码 )，会先去索引列表中查询，而不是对整个表进行查询。索引列表是B类树的数据结果,查询速度会比原来没有设置索引时快得多。

SELECT `name` , `price`
FROM fruit
WHERE `name` in ('葡萄','柿子','橘子');

EXPLAIN解析

使用Explain解析查询字段：

EXPLAIN
SELECT `name` , `price`
FROM fruit
WHERE `name` in ('葡萄','柿子','橘子');

下面详细解释EXPLIAN的每一行执行

`select_type`

select_type 字段表示查询的类型，它可以帮助我们理解查询的层次结构和复杂程度。以下是常见的 select_type 值及其含义：

SIMPLE    这是最简单的查询类型。表示查询中不包含子查询或者 UNION 操作。

PRIMARY    当查询中包含子查询时，最外层的查询会被标记为 PRIMARY。

SUBQUERY    在 SELECT 或 WHERE 子句中包含的子查询会被标记为 SUBQUERY。

UNION    在 UNION 操作中，除了最外层的查询，每个参与 UNION 的查询的 select_type 都是 UNION。

`type`

type 字段表示访问表的方式，它描述了 MySQL 如何查找表中的行。不同的 type 值反映了查询的效率，从最优到最差通常有以下几种常见类型：

system
- 这是 const 连接类型的特殊情况，表中只有一行数据（例如系统表），是最好的访问类型。但这种情况非常罕见。
const
- 当通过索引一次就能找到数据时（通常是通过主键或唯一索引（索引就能够直接定位！！）），type 就是 const。例如，根据主键查询某一行数据。
- 例如：SELECT * FROM table WHERE primary_key = 1; 如果 primary_key 是主键，且只有一行满足条件，那么就是 const 类型。
eq_ref
- 通常用于多表连接中，通过主键或唯一索引进行关联，对于每个来自前面的表的行组合，从该表中只能读取一行。
- 例如，两个表通过主键关联进行查询：SELECT * FROM table1 JOIN table2 ON table1.key = table2.key; 如果 key 是 table2 的主键或唯一索引，对于 table1 的每一行，在 table2 中只有一行与之匹配，就是 eq_ref 类型。
ref
- 使用普通索引或唯一索引的部分前缀来查找数据。与 eq_ref 不同的是，它可能返回多行数据。
- 例如：SELECT * FROM table WHERE index_column = 'some_value'; 如果 index_column 是普通索引，可能有多行满足条件，就是 ref 类型。
range
- 使用索引进行范围查找，例如使用 BETWEEN、<、> 等操作符。
- 例如：SELECT * FROM table WHERE id BETWEEN 10 AND 20; 如果 id 是索引字段，就是 range 类型。
index
- 全索引扫描，MySQL 遍历整个索引来查找数据，而不是表数据本身。通常比 ALL 全表扫描快，因为索引文件通常比数据文件小。
- 例如：SELECT index_column FROM table; 如果只查询索引列，并且 MySQL 可以通过索引获取所有需要的数据，就是 index 类型。
ALL
- 全表扫描，意味着 MySQL 会扫描整张表来查找数据，这通常是效率较低的方式，应该尽量避免。
- 例如：SELECT * FROM table WHERE column_without_index = 'some_value'; 如果 column_without_index 没有索引，就可能进行全表扫描，即 ALL 类型。

possible_keys

由此栏目中的值——name_index，可以看出，这是可能使用到的索引。

虽然 possible_keys 列出了可能的索引，但并不意味着这些索引一定会被使用。查询优化器会根据实际的数据分布、索引的选择性、表的大小等因素来综合判断。

`key`

含义：表示实际使用的索引。如果没有使用索引，这里会显示 NULL。
示例：对于一个有 id（主键）、name（普通索引）字段的表，执行 SELECT * FROM table WHERE name = 'John'，如果使用了 name 索引，这里就会显示该索引的名称。

`key_len`

含义：索引字段可能占用的字节数。它可以帮助我们了解索引的使用情况，以及索引的长度是否合适。
示例：对于一个 CHAR(10) 类型的字段作为索引，它可能的 key_len 会根据字符集等因素来确定。比如使用 utf8mb4 字符集，一个字符可能占用 4 个字节，那么 10 个字符的索引长度大概就是 40 字节（但实际还会受其他因素影响，如是否可为空等）。

`ref`

含义：显示哪些列或常量与索引一起被使用来查找行。常见的值有：
- 常量值，如具体的数字或字符串。
- 某个表的某个字段，表示通过该字段与索引进行关联。
示例：如果查询语句是 SELECT * FROM table1 JOIN table2 ON table1.id = table2.ref_id，对于 table2 的 EXPLAIN 结果，ref 可能显示 table1.id，表示通过 table1 的 id 字段与 table2 进行关联。