mysql 索引

szc1767

已于 2024-01-09 17:49:59 修改

阅读量403

点赞数 8

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

于 2024-01-09 14:55:59 首次发布

本文链接：https://blog.csdn.net/szchen2/article/details/135474491

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中的索引概念，包括主键索引、普通索引、唯一索引、全文索引和空间索引。讲解了索引在提高查询效率中的作用，以及InnoDB和MyISAM存储引擎中索引的不同实现。还涵盖了如何查看、创建和删除索引，以及B+Tree等数据结构在索引中的应用。

摘要由CSDN通过智能技术生成

一、什么是索引

mysql 索引是帮助mysql 高效获取数据的数据结构（相当于书的目录）

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

二、索引的分类

主键索引（`primary key`）

设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

普通索引

普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引。

CREATE INDEX index_name ON table_name (column(length));

column 是指定要创建索引的列名
length 是可选项

// 修改表的方式创建索引
ALTER TABLE table_name ADD INDEX index_name (column(length));

注：索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。

唯一索引（UNIQUE）

索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）

CREATE UNIQUE INDEX index_name ON table_name(column);

CREATE UNIQUE INDEX index_name ON table_name(column(length));

全文索引（FULLTEXT）

Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。

在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引（SPATIAL）

。。。。

三、索引相关命令

查看索引

show index from tablename;

show keys from tablename;

删除索引

DROP INDEX 索引名 ON 表名; 

ALTER TABLE 表名 DROP INDEX 索引名;

四、索引的数据结构

MySQL索引使用的数据结构主要有BTree索引和hash索引。

1、流程引擎不同、实现方式不同

InnoDB中data阈存储的是行数据，而MyISAM中存储的是磁盘地址。

MyISAM：

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则根据data域中磁盘地址到磁盘中寻址定位到对应的磁盘块，然后读取相应的数据记录，这被称为“非聚簇索引”。

InnoDB：

==其数据文件本身就是索引文件==。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按照B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的Key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“ 聚簇索引（聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。

2、B+ 树结构

特点：

只有叶子节点才会存储数据，非叶子节点只存储键值key；

叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

注：

在根据主索引搜索时，直接找到Key所在的节点即可取出数据；
在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。

B+ 树存储数据量：

设计前提：MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘的IO操作。访问二叉树的每个节点都会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。

在MySQL的InnoDB引擎的一次IO操作会读取一页的数据量（默认一页大小为16K），如果key为INT(4个字节) / BIGINT(8个字节)，指针类型也一般为4或8个字节，也就是说一个页中大概存储16KB/(8B+8B)=1K=1000个键值，也就是说一个深度为2的B+Tree索引可以维护100万条记录。

注：mysql的InnoDB存储引擎在设计时是将根节点常驻内存的