mysql --- 索引初识篇--索引机制概述

标标王者

已于 2023-10-10 17:17:38 修改

阅读量256

点赞数

分类专栏： mysql 文章标签： mysql 数据库

于 2023-10-10 16:53:57 首次发布

本文链接：https://blog.csdn.net/ch568/article/details/133749664

版权

mysql 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

一、MySQL索引机制概述

索引是什么：索引就是用来帮助表快速检索目标数据的

1.1、MySQL索引的创建方式

- ①使用CREATE语句创建

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

这种创建方式可以给一张已存在的表结构添加索引，其中需要指定几个值：

indexName：当前创建的索引，创建成功后叫啥名字。
tableName：要在哪张表上创建一个索引，这里指定表名。
columnName：要为表中的哪个字段创建索引，这里指定字段名。
length：如果字段存储的值过长，选用值的前多少个字符创建索引。
ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。

当然，上述语句中的INDEX也可更改为KEY，作用都是创建一个普通索引，而对于其他的索引类型，这点在后续的索引分类中再聊。

- ②使用`ALTER`语句创建

ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

这里的参数都相同，所以不再重复赘述。

- ③建表时`DDL`语句中创建

CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  INDEX [indexName] (columnName(length))  
);

这种方式就比较适合在库表设计时，已经确定了索引项的情况下建立。

1.2、查询、删除、指定索引

但不管通过哪种方式建立索引，本质上创建的索引都是相同的，当索引创建完成后，可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引，如下：

CREATE TABLE `zz_user`  (
  `user_id` int(8) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NULL DEFAULT "",
  `user_sex` varchar(255) NULL DEFAULT "",
  `user_phone` varchar(255) NULL DEFAULT "",
	PRIMARY KEY (`user_id`) USING BTREE
)
    ENGINE = InnoDB 
    CHARACTER SET = utf8 
    COLLATE = utf8_general_ci 
    ROW_FORMAT = Compact;

在上述的建表SQL中，为user_id创建了一个主键索引，然后来查一下当前表的索引信息：

简单的概述一下查询后，每个字段的含义：

①Table：当前索引属于那张表。
②Non_unique：目前索引是否属于唯一索引，0代表是的，1代表不是。
③Key_name：当前索引的名字。
④Seq_in_index：如果当前是联合索引，目前字段在联合索引中排第几个。
⑤Column_name：当前索引是位于哪个字段上建立的。
⑥Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
⑦Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。
⑧Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
⑨Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
⑩Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
⑪Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。
⑫Comment：创建索引时，是否对索引有备注信息。

这条命令在后续排除问题、性能调优时，会有不小的作用，比如可以通过分析其中的Cardinality字段值，如果该值少于数据的实际行数，那目前索引有可能失效。

在MySQL中并未提供修改索引的命令，也就说当你建错了索引，只能先删再重新建立一次，删除索引的语句如下：

DROP INDEX indexName ON tableName;

当然，当建立了一条索引后，也可以强制性的为SELECT语句指定索引，如下：

SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;

FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询，但要牢记的是：如果当前的查询SQL压根不会走指定的索引字段，哪这种方式是行不通的，这个关键字的用法是：一条查询语句在有多个索引可以检索数据时，显式指定一个索引，减少优化器选择索引的耗时。

但要注意：如果你对于你整个业务系统十分熟悉，那可以这样干。但如果不熟悉的话，还是交给优化器来自行选择，否则会适得其反！

1.3、数据库索引的本质

前面一直在聊创建、查看、删除、指定等一些索引的基本操作，但索引本质上在数据库中是什么呢？大家都知道，数据库是基于磁盘工作的，所有的数据都会放到磁盘上存储，而索引也是数据的一种，因此与表数据相同，最终创建出的索引也会在磁盘生成本地文件。

不过索引文件在磁盘中究竟以何种方式存储，这是由索引的数据结构来决定的。同时，由于索引机制最终是由存储引擎实现，因此不同存储引擎下的索引文件，其保存在本地的格式也并不相同。

在这里有一个点需要注意：建立索引的工作在表数据越少时越好，如果你想要给一张百万、千万条数据级别的表新创建一个索引，那创建的耗时也不短，这是为什么呢？

因为刚刚聊过，索引本质上和表是一样的，都是磁盘中的文件，那也就代表着创建一个索引，并不像单纯的给一张表加个约束那么简单，而是会基于原有的表数据，重新在磁盘中创建新的本地索引文件。假设表中有一千万条数据，那创建索引时，就需要将索引字段上的1000W个值全部拷贝到本地索引文件中，同时做好排序并与表数据产生映射关系。

1.31 以下是数据库索引的一些关键方面和本质：

数据结构：索引本质上是一种数据结构，通常是树状结构（如B树或B+树）或哈希表。这些数据结构存储了索引列的值以及指向实际数据行的指针或引用。
快速查找：索引的主要目的是加速数据检索操作。通过索引，数据库管理系统可以迅速定位到包含所需数据的数据行，而无需扫描整个表。
提高查询性能：索引不仅加速了WHERE子句中包含索引列的条件的查询，还可以加速连接操作（如JOIN）和排序操作。
有序性：索引通常对索引列的值进行排序，这有助于加速范围查询和排序操作。
存储开销：索引需要额外的存储空间来维护索引数据结构。这意味着在选择何时创建索引时需要权衡存储成本和性能提升。
更新开销：插入、更新和删除操作可能会导致索引的更新，这会增加数据库维护的开销。因此，需要谨慎选择要创建索引的列。
数据一致性：索引需要与底层数据表一起维护，以确保索引与表的数据保持一致。

1.4、数据库索引相学习的知识点

索引是什么：
- 索引是数据库中的数据结构，用于提高数据检索性能。它包含一个或多个列的值，以及指向实际数据行的指针或引用。
索引的作用：
- 提高数据查询性能：通过快速定位和检索数据，而无需扫描整个表。
- 加速连接操作：有助于JOIN操作的效率。
- 优化排序操作：对ORDER BY子句的查询能够受益。
索引的类型：
- 单列索引：基于单个列的索引。
- 复合索引：基于多个列的组合索引。
- 唯一索引：确保索引列中的值唯一。
- 主键索引：用于标识表中的唯一行。
- 聚簇索引和非聚簇索引：影响数据的物理存储方式。
创建和管理索引：
- 创建索引可以在表创建时定义，也可以后期添加。
- 创建索引需要权衡存储开销和性能提升。
- 索引需要定期监视和维护，以确保其效率。
查询优化：
- 查询优化器使用索引来确定最有效的查询计划。
- 选择合适的索引可以显著提高查询性能。
索引的权衡：
- 索引不是没有代价的，它们占用存储空间，需要维护，可能会增加写操作的开销。
- 需要谨慎考虑哪些列需要索引，以权衡性能和开销。
不适合创建索引的情况：
- 当表中数据量很小或不常被查询时，创建索引可能不划算。
- 对于频繁进行大批量数据插入、更新或删除的表，过多的索引可能会导致性能问题。
索引的使用案例：
- WHERE子句中包含索引列的条件查询。
- 连接操作（JOIN）中的连接列。
- ORDER BY子句中的排序列。
- 唯一性约束和主键约束。
索引的数据结构：
- B树索引：常见于许多数据库系统中，用于维护有序数据。
- B+树索引：在许多数据库中也很常见，用于支持范围查询。
- 哈希索引：适用于等值查找，但不支持范围查询。
索引的维护和性能调优：
- 定期重建或重新组织索引。
- 监视查询性能并根据需要调整索引策略。