MySQL索引

一、索引简介

二、索引优缺点

三、索引类型

四、索引数据结构

五、索引操作

六、索引失效

一、索引简介

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

MySQL 索引作为一种种数据结构，用于加快数据库查询的速度和性能。

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

二、索引的优缺点

添加描述

添加描述

三、索引类型

1.Primary Key主键索引：

存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键,则会使用第一非空的唯一索引作为聚集索引l，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引

2.单列索引：

单列索引即一个索引只包含单个列

3.组合索引：

组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

4.Unique唯一索引：

列的值必须唯一，但允许有空值。若是组合索引l，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值

5.Key普通索引：

SQL中的基本索引类型，允许在定义索引I的列中插入重复值和空值

6.FULLTEXT全文索引：

引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

7.SPATIAL空间索引：

索引是对空间数据类型的字段建立的索引I，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引I。创建空间索引的列必须声明为NOT NULL

四、索引的数据结构

1.二叉树

二叉树（Binary Tree）是一种特殊的树状数据结构，其中每个节点最多有两个子节点，分别称为左子节点和右子节点

二叉树的定义：一个二叉树可以为空（即没有节点），或者由一个根节点和两颗分别称为左子树和右子树的二叉树组成

二叉树的特点：

1. 每个节点最多有两个子节点，分别为左子节点和右子节点
2. 左子树和右子树也是二叉树，可以为空
3. 二叉树的子节点没有特定顺序，根据具体应用决定左右子节点的位置

添加描述

二叉树缺点：当值依次递增插入时，二叉树会退化成链表，对加快查询没有任何作用；在数据量较大时反而会降低查询效率

2.红黑树

红黑树（Red-Black Tree）是一种自平衡的二叉查找树，在插入和删除操作后通过重新安排节点的颜色来保持平衡。红黑树的名称来源于每个节点上的颜色标记，每个节点可以是红色或黑色

红黑树特点：

1. 每个节点要么是红色，要么是黑色
2. 根节点是黑色的
3. 所有叶子节点（NIL节点）都是黑色的
4. 如果一个节点是红色的，则其两个子节点都是黑色的
5. 对于任意节点，从该节点到其所有后代叶子节点的简单路径上，均包含相同数目的黑色节点

添加描述

红黑树缺点：当值依次递增插入时，树的高度会变得特别高，在数据量较大时会降低查询效率

3. B-Tree（多路平衡查找树）

B-Tree（B树）是一种用于存储和组织大量数据的自平衡搜索树结构。它被广泛应用于数据库和文件系统等领域，以提供高效的数据访问和查询性能

B-Tree特点：

1. 索引值和data数据分布在整棵树结构中
2. 每个节点可以存放多个索引值及对应的data数据
3. 树节点中的多个索引值从左到右升序排列
4. B-Tree的节点大小通常与硬盘页的大小相匹配，这样可以最大程度地减少磁盘I/O操作，提高读写性能
5. B-Tree能够动态调整自身的结构以适应数据的动态插入和删除操作，保持平衡性和性能稳定

B-Tree的基本操作包括插入、删除和查找。在插入和删除操作时，B-Tree会通过重新分配关键字和调整节点来保持平衡。通过使用B-Tree索引，可以显著提高数据的检索效率，尤其是对于大规模的数据集。

B-Tree并不仅限于二叉树的结构，每个节点可以包含多个子节点，使其适用于处理大规模数据集的情况

添加描述

4.B+Tree

B+树（B+Tree）是一种类似于B-Tree的自平衡搜索树结构，被广泛应用于数据库和文件系统等领域。它是B-Tree的一种变体，相较于B-Tree，在存储和查询性能上有一些优化。B+树与B-Tree相似，也具有多路平衡性、有序性和磁盘友好性的特点

B+ Tree特点：

1. 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
2. 叶子节点包含了所有的索引值和data数据
3. 叶子节点用指针连接，提高区间的访问性能

添加描述

相比于B-Tree区别：

• B+树所有的数据都会出现在叶子节点
• B+树叶子节点形成一个单向链表
• B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可
• B树需要遍历范围内所有的节点和数据，显然B+Tree效率高

5.Hash哈希索引

哈希索引（Hash Index）是一种在数据库中用于快速查找数据的索引结构。它通过将关键字（Key）通过散列函数（Hash Function）转换成一个固定长度的散列值（Hash Value），然后将这个散列值与存储位置建立映射关系，从而实现高效的数据查找

哈希索引的主要特点包括：

1. Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
2. 无法利用索引完成排序操作
3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

添加描述

哈希索引在某些场景下可能效果不如B树索引，因为它无法支持范围查询和排序操作，并且对于存在大量冲突的情况下性能可能会下降

五、索引操作

1.创建索引：

（1）创建主键索引

#建表时，主键默认为索引
create table users(
    id varchar(11) primary key,
    name varchar(20),
    age int
)
#查看user表中的索引
show index from users;

复制

添加描述

（2）创建单列索引

#创建单列索引，只能包含一个字段
create index name_index on users(name);

复制

添加描述

（3）创建组合索引

#组合索引
create index name_age_index on users(name,age);

复制

添加描述

满足复合索引的查询的两大原则：

假如创建的复合索引为三个字段，按顺序分别是(name,age,sex)

在查询时能利用复合索引的查询条件如下：

原则1：最左前缀原则(如下四种都满足条件)

select * from user where name = ? 
select * from user where name = ? and age = ?
select * from user where name = ? and sex = ?
select * from user where name = ? and age = ? and sex = ?

复制

不满足最左前缀的条件

select * from user where name = ? and sex = ? and age = ?
select * from user where age = ? and sex = ? and name = ?
select * from user where sex = ? and age = ? and name = ?
select * from user where age = ? and sex = ?
…………等等

复制

原则2：当条件中的字段全部达到复合索引中的字段时，可以动态调整字段顺序，使其满足最左前缀

#可以使用复合索引：索引中包含的字段数都有，只是顺序不正确，在执行的时候可以动态调整为最前左缀
select * from user where sex = ? and age = ? and name = ?
select * from user where age = ? and sex = ? and name = ?

#不可以使用复合索引：因为缺少字段，并且顺序不正确
select * from user where sex = ? and age = ? 
select * from user where age = ? and name = ?
select * from user where age = ?
select * from user where sex = ? 

复制

2.删除索引：

使用DROP INDEX语句：

DROP INDEX index_name ON table_name;

复制

• DROP INDEX: 用于删除索引的关键字
• index_name: 指定要删除的索引的名称
• ON table_name: 指定要在哪个表上删除索引

使用ALTER TABLE语句：

ALTER TABLE table_name DROP INDEX index_name;

复制

• ALTER TABLE: 用于修改表结构的关键字
• table_name: 指定要修改的表的名称
• DROP INDEX: 用于删除索引的子句
• index_name: 指定要删除的索引的名称

3.显示索引信息：

SHOW INDEX FROM table_name

复制

• SHOW INDEX: 用于显示索引信息的关键字。
• FROM table_name: 指定要查看索引信息的表的名称。

六、索引失效

1.索引列运算

在索引列上进行运算操作，索引将失效：在查询条件或者索引创建时对索引列进行运算（如计算、函数操作等）。在某些情况下，索引列的运算可能导致索引失效

explain select * from tb_user where substring(phone, 10, 2) = '15'
-- 对 phone 列使用了 substring 函数进行运算，数据库无法直接利用 phone 列上的索引来快速定位符合条件的记录

复制

原因：

1. 运算结果不可预测：当对索引列进行运算时，可能会改变列的原始值，导致无法准确匹配索引中的键值
2. 运算结果类型不匹配：索引是按照特定的数据类型进行排序和存储的，如果进行的运算导致结果的数据类型与索引列的数据类型不匹配，索引将无法被正确地使用
3. 运算造成索引列无法比较顺序：索引的主要目的是提供有序性以便快速定位和筛选数据。若进行的运算导致索引列的顺序无法保持一致，索引将失去有序性，并且无法为查询提供优化

2.字符串不加引号

在查询条件或创建索引时字符串没有加上引号，可能会导致索引失效

原因：

1. 数据类型不匹配：数据库中的字符串需要用引号括起来表示，而非引号括起来的值通常被视为其他数据类型（例如列名、函数名等）
2. 字符串比较问题：数据库在进行字符串比较时，通常会依赖字符串的排序规则。如果字符串未加引|号，数据库可能会将其解析为其他类型的数据，而非按照字符串的排序规则进行比较

3.模糊查询

模糊查询中，如果是尾部模糊匹配，索引不会失效；如果头部模糊匹配，索引失效

原因：

1. 尾部模糊匹配：如果模糊查询的通配符（如%）仅出现在搜索字符串的尾部，索引仍然可以有效利用。比如使用LIKE’abc%’进行尾部匹配查询，这样数据库可以通过使用索引进行查找，并返回以"abc"开头的匹配结果
2. 头部模糊匹配：相反，如果模糊查询的通配符（如%）出现在搜索字符串的开头，索引将会失效。例如，使用LIKE ’%abc’进行头部匹配查询，在这种情况下，由于无法确定匹配值的起始位置，数据库无法有效地利用索引进行查找

4.or连接的条件

当使用OR操作符将多个条件组合在一起时，如果其中一个条件的列没有索引，那么涉及的索引不会被用到

原因：

1. 索引选择性：数据库优化器通常会根据索引的选择性来决定是否使用该索引。选择性是指索引中不同值的唯一性程度。当一个条件的列没有索引时，其选择性会较低，它包含的不同值很少
2. 查询计划的成本估算：数据库优化器在确定查询计划时，会根据每个可能的执行路径进行成本估算。如果其中一个条件的列没有索引，那么涉及的索引可能无法提供有效的过滤，从而使得使用索引的执行路径的成本估算较高

5.数据分布影响

当MySQL评估使用索引I比全表扫描更慢时，会选择不使用索引

对于一个学生表，如果包含列info，并且大部分记录的info字段为空，并且该列设置了索引，当执行以下查询时:

SELECT * FROM student WHERE info IS NULL;

在这种情况下，MySQL的优化器可能会选择不使用该列的索引l。