MySQL之Index（索引）

以你之姓@ 2023-07-04 06:23 15阅读 0赞

## 基本原理 ##

**索引（INDEX）**：帮助MySQL高效获取数据的数据结构  
PS：索引本身很大，不可能全部存储在内存中，往往以索引文件的形式存储在磁盘上  
优点：提高检索效率，降低数据库的IO成本；降低了排序成本，减少CPU消耗  
缺点：占用空间，在写入数据时加大了开销

**索引结构**：  
B树 （每个结点都包含指向数据指针） 效率高，但对内存开销大，每次查询缺页时都会加载较多数据  
B+树 （只有叶子结点包含指针） 效率较低，对内存开销小，缺页时加载结点数据少  
所以MySQL一般使用B+ 树的索引结构

**聚簇索引**：是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序，规定数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引。但该索引可以包含多个列（组合索引）  
**非聚簇索引**：索引顺序与数据物理排列顺序无关叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。

**分类**：单值索引、唯一索引、主键索引、复合索引  
复合索引：建立多个索引， 复合索引是分层的（多个平衡树相关联），在命中字段的时候是依次命中（左到右）

**TIPS**：有关MYISAM引擎和INNODB引擎索引的区别  
MyISAM：使用B+TREE作为索引结构，叶节点data域存放数据记录的地址，主索引和其它索引结构上并无区别，数据文件和索引文件是分开的  
INNODB：使用B+TREE作为索引结构，叶节点data域存放了完整的数据记录，其它索引data域存储主索引对应的值，数据文件本身就是索引文件，要求表必须有主键，若未指定，会自动选择一个列或生成一个隐含字段

## 语法 ##

**①创建**  
CREATE \[UNIQUE\] INDEX 索引名(一般为idx\_）ON 表名（列名）  
![在这里插入图片描述][20200209101239135.png]  
TIPS：可以使用ALTER、ADD关键字来添加索引  
ALTER TABLE 表名 ADD PRIMARY KEY/UNIQUE/INDEX/FULLTEXT(全文索引) 索引名（列名）  
PS：此处也可使用添加表级约束时的方法：ADD \[CONSTRAINT pk/uq/ck（约束名）\] 约束类型(列名)，会自动建立（主键，外键，唯一）索引

**②显示**  
SHOW INDEX FROM 表名\\G  
![在这里插入图片描述][20200209101250389.png]  
**③删除**  
DROP INDEX 索引名 ON 表名  
需要建立索引的情况：  
主键自动创建唯一索引、频繁查询建索引、与外表关联建索引、组合索引性价比更高  
排序的字段、统计或分组的字段  
不需建立索引：表记录小、经常增删改、WHERE用不到、过滤性不好

Ps：索引存储在information\_schema数据库的STATISTICS表中，主要字段（TABLE\_SCHEMA 数据库名，TABLE\_NAME 表名，INDEX\_NAME 索引名）  
![在这里插入图片描述][20200209100653667.png]

[20200209101239135.png]: https://img-blog.csdnimg.cn/20200209101239135.png
[20200209101250389.png]: https://img-blog.csdnimg.cn/20200209101250389.png
[20200209100653667.png]: https://img-blog.csdnimg.cn/20200209100653667.png