MySQL索引

假女吖☌

已于 2023-10-15 20:31:47 修改

阅读量71

点赞数

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

于 2023-10-10 14:28:20 首次发布

本文链接：https://blog.csdn.net/qq_43501821/article/details/133746280

版权

MySQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

MySQL索引

概述

索引可以理解为是一种有序的数据结构，它允许数据库系统快速的找到表中的特定行。类似与字典或者书籍的目录，可以快速的定位到需要的信息。

为什么要使用索引？

目的，当然是为了提供查询性能，避免扫描整个表，减少了磁盘的IO操作。

MySQL有那些索引呢？

单列索引：只有一个索引列，一个数据表可以有多个单列索引。
- 普通索引：允许数据重复，且允许NULL值。
- 主键索引：特殊的一种索引，不允许数据重复，且不允许NULL值。
- 唯一索引：不允许数据重复，但允许NULL值。
- 前缀索引：用于存储字符串前几位的索引，相对于普通索引空间占用更小，但有可能查询会稍慢。
多列索引（复合索引）：多列组合成一个索引，只有遵守**最左前缀原则**的时候才能使该索引生效。
全文索引：适用于对文本进行全文搜索，可以在大量文本数据上提供高效的搜索功能。
空间索引：没用过😂，只知道使用SPATIAL创建。

操作索引

语法1

在CREATE TABLE 中创建索引

CREATE TABLE table_name(
	column1 primary key, -- primary key 添加主键索引
	column2,
    column3,
    column4
    [,[FULLTEXT|UNIQUE|SPATIAL] index  (column_name,...) [index_type],...]
)

index_type:
	USING {BTREE | HASH}

示例

CREATE TABLE Index_Table(
	id bigint primary key,  -- 1.id为表的主键
    code VARCHAR(50),
	name VARCHAR(50),
	sex VARCHAR(50),
	index code_index(code),	-- 2.增加一个普通索引,索引列为 code 名称为 code_index
	unique index name_sex_unique(name,sex) USING BTREE  -- 3.增加一个多列唯一索引,索引列为（name,sex）索引名为 name_sex_uniqu。使用B+树的索引类型
)

语法2

使用ALTER TABLE创建索引，删除索引

-- 添加索引
ALTER TABLE table_name ADD [FULLTEXT|UNIQUE|SPATIAL] INDEX index_name (column1, column2, ...) [index_type];

-- 删除索引
ALTER TABLE table_name drop INDEX index_name;

index_type:
	USING {BTREE | HASH}

示例

-- 为Index_Table添加一个全文索引 索引名FULLTEXT_SEX_INDEX，索引列为sex
alter table Index_Table ADD FULLTEXT INDEX FULLTEXT_SEX_INDEX(sex); 

-- 删除Index_Table表的name_sex_unique索引
ALTER TABLE Index_Table DROP INDEX name_sex_unique;

语法3

使用CREATE INDEX创建索引

CREATE  [FULLTEXT|UNIQUE|SPATIAL] INDEX index_name ON table_name (column1[,...]) [index_type] ;

index_type:
	USING {BTREE | HASH}

示例

--- 在Index_Table上创建一个唯一索引 索引名为 name_unique 索引列为 name 
CREATE UNIQUE INDEX  name_unique on Index_Table USING BTREE;

索引的数据结构

Hash：
- 类似hashmap中的hash表
- 根据索引的key进行hash运算，然后定位到对应数据存储的位置。
- 插入效率很低，并且不支持范围查询，因为key对应的位置是hash运算后的位置，这个跟顺序无关，所以不支持范围查询。
BTree（默认）：
- B+树
- 相邻的两个叶子节点，以及头尾节点是可以互相访问的就类型与双端队列一样。
- 因为索引是从左到右递增的，很好的适配的范围查询。

InnoDB下索引的分类

如图：InnoDB默认索引的数据结构是一颗B+树只有叶子节点中才会存储对应的数据。非叶子节点则是索引的值。聚集索引

在InnoDB中必须要有聚集索引，且只能有一个。

索引与数据是存放在一起的：在聚集索引中，叶子节点存储的数据是当前行的地址。(可以通过这个地址快速的读取到其他列的数据)。

查询数据的过程（当一个查询需要查到除索引值之外的数据）：通过聚集索引定位到数据后，可以通过当前行的地址（索引存储的数据）快速取到其他数据。

为什么MySQL推荐InnoDB表都需要有一个主键?

一个表中的聚集索引的选择是需要遵守一个规则的.

表中定义了主键，那么主键就会成为聚集索引。
为什么主键成为聚集索引，因为主键可以很好的保证唯一性与非空性。

没用定义主键，则会选择一个唯一索引的列当作聚集索引列。
也没用唯一索引列的话，则InnoDB会生成一个隐藏的聚集索引。它的唯一性由 InnoDB 自动生成的内部标识符来保证。

通常来说我们经常需要通过主键去查询，但是如果这个主键是隐藏的，那么就不太友好了。所有MySQL推荐InnoDB表中必须有一个主键，这个主键直接成为聚集索引。

非聚集索引（二级索引）

可以没有，也可以存在多个。

索引与数据是分开的：该索引中存储的数据是当前行主键索引的值。

查询数据的过程（当一个查询需要查到除索引值之外的数据）：通过非聚集索引定位到数据后，拿到当前索引存储的值(聚集索引的值)，再通过这个聚集索引去表中定位到是那一行，再去取其他列的数据。（这个操作也称之为回表）。

回表

回表 是指在使用索引进行查询时，数据库发现它不能直接从索引中获取所有需要的信息，于是它需要返回到表中去检索其他的数据。

回表会产生额外的 IO 操作，因此在设计索引时，可以考虑创建覆盖索引来减少回表的发生，特别是在需要查询的列上创建索引。

覆盖索引

当一次查询只用到了索引列，或者联合索引中用到的字段时，则无需再次回表（即访问实际的数据行）。

正确的使用覆盖索引可以提交查询性能，因为它可以减少回表操作，从而减少IO操作和内存消耗。

最左前缀原则

最左前缀原则指：在使用复合索引的时候，必须使用该复合索引最左侧的索引列。否则会导致索引失效。

例：有一个包含这(col1,col2,col3)的复合索引

查询时同时这三个条件的时候索引生效。
查询时使用col1，col2 或者 col1，col3索引生效。
其他不包含col1这个最左侧列的查询时，均会导致索引失效。

只在复合索引中有这个原则。