MySQL索引机制概述-KeepStudy

MySQL索引机制概述

## 数据库索引的本质
索引就是用来帮助表快速检索目标数据的。

索引本质上在数据库中是什么呢？数据库是基于磁盘工作的，所有的数据都会放到磁盘上存储，而索引也是数据的一种，因此与表数据相同，最终创建出的索引也会在磁盘生成本地文件。

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

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

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

## MySQL索引的创建方式
- ①使用CREATE语句创建
```sql
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
```
    这种创建方式可以给一张已存在的表结构添加索引，其中需要指定几个值：
        - indexName：当前创建的索引，创建成功后叫啥名字。
        - tableName：要在哪张表上创建一个索引，这里指定表名。
        - columnName：要为表中的哪个字段创建索引，这里指定字段名。
        - length：如果字段存储的值过长，选用值的前多少个字符创建索引。
        - ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。
    当然，上述语句中的INDEX也可更改为KEY，作用都是创建一个普通索引，而对于其他的索引类型，这点在后续的索引分类中再聊。

- ②使用ALTER语句创建
```sql
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
```

- ③建表时DDL语句中创建
```sql
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  INDEX [indexName] (columnName(length))  
);
```
这种方式就比较适合在库表设计时，已经确定了索引项的情况下建立。

## 查询、删除、指定索引
但不管通过哪种方式建立索引，本质上创建的索引都是相同的，当索引创建完成后，可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引，如下：
![](/uploads/20230215/2023021511390863720.png)
简单的概述一下查询后，每个字段的含义：

- ①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中并未提供修改索引的命令，也就说当你建错了索引，只能先删再重新建立一次，删除索引的语句如下：
```sql
DROP INDEX indexName ON tableName;
```
当然，当建立了一条索引后，也可以强制性的为SELECT语句指定索引，如下：
```sql
SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;
```
FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询，但要牢记的是：如果当前的查询SQL压根不会走指定的索引字段，哪这种方式是行不通的，这个关键字的用法是：一条查询语句在有多个索引可以检索数据时，显式指定一个索引，减少优化器选择索引的耗时。

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

顶部

MySQL索引

目录

相关推荐