MYSQL之——索引

最新推荐文章于 2024-07-16 08:19:13 发布

Coke_3.2.2

最新推荐文章于 2024-07-16 08:19:13 发布

阅读量32

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_66660784/article/details/134892726

版权

索引是什么

索引 index 存储引擎用于快速找到数据的一种数据结构，即提高数据查询效率的数据结构，它可以比作是一本字典的目录，可以帮助快速找到对应的记录。

索引一般存储在磁盘的文件中，它是占用物理空间的。

同时要注意的是，适当的索引能提高查询效率，但过多的索引会影响数据库表的插入和更新功能。

MySQL默认使用innoDB存储引擎，该存储引擎是最重要、使用最广泛的，除非有特别的原因需要使用其他存储引擎，否则优先考虑innoDB存储引擎。

索引的优点：

减少服务器需要扫描的数据量

索引是根据表中的某个或多个列创建的数据结构，它们包含了对应列值的引用和位置信息。当执行查询时，数据库可以使用索引来快速定位满足查询条件的数据行，而不必扫描整个表。通过定位到索引上的特定值，可以有效减少需要扫描的数据量，提高查询效率。

帮助服务器避免排序和临时表

具有适当索引的查询可以避免许多排序和创建临时表的操作。例如，如果查询需要按某个列进行排序，而该列已经被索引，数据库可以直接利用索引顺序提供有序结果，而不必进行额外的排序操作。同样，如果查询需要使用临时表来存储中间结果，适当的索引可以帮助避免创建临时表，直接从索引中获取所需的数据。

索引可以将随机I/O变为顺序I/O，提高查询性能

在没有索引的情况下，数据库可能需要随机读取磁盘上的数据块来获取所需的数据行，这会导致大量的随机I/O操作，并降低查询性能。然而，有了合适的索引，数据库可以按照索引顺序读取数据块，并将随机I/O转化为顺序I/O。顺序I/O比随机I/O具有更高的吞吐量和更低的访问延迟，从而提高查询性能。

下面我们来举个例子来更好的说明索引：

现在我们有一个表格，表中的数据有：ID 、作者名字(author)、文章内容(text)

通常来说一篇文章的文本部分text，是一个不小的数据量，所以磁盘需要开辟一块较大的内存空间来存储文本+id+author，这样在我们查找一篇文章的作者的时候，会很费时间。

那么有没有一种好的办法来解决这个费时间的问题呢？这里我们就要引入索引了，首先明确查找的目标：作者author，那么我们是不是可以新建一个表格，里面的内容是作者author和原本这篇文章存储的地址呢，也就是查找目标+地址，这样这个数据的地址就会相比原地址小了不少：

常见索引类型

哈希索引：基于哈希表实现，适合等值查询，检索效率高，一次到位，但是不适合范围查找和排序操作，也不支持部分索引列的查找，如果哈希冲突很多的话，索引维护的代价会很高：

不支持范围查找和排序操作：由于哈希函数的特性，哈希索引无法按照索引键的顺序进行范围查找或排序操作。因为哈希函数将索引键映射到哈希值时，相邻的索引键可能会被映射到远离彼此的哈希值。
不支持部分索引列的查找：哈希索引通常只针对一个或多个列进行哈希计算，而不是整个索引列集合。因此，如果查询条件涉及到的列不包含在哈希索引中，就无法利用哈希索引进行查询。
哈希冲突问题：由于哈希函数的局限性和输入数据的多样性，哈希索引可能会出现冲突，即不同的索引键可能被哈希到相同的哈希值和存储地址上。当冲突很多时，会增加索引维护的代价，并且可能会导致性能下降。

全文索引：用于全文搜索的索引类型，可以执行关键字的搜索，全文索引有很多限制，例如当数据量很大，内存无法装载全部索引的话，搜索速度会很慢，并且全文索引维护成本也很大。 MyISAM支持全文索引，innoDB从1.2版本(MySQL5.6)开始支持全文索引。
B+树索引：B(balance)+树索引是传统意义上的索引，是目前关系型数据库中查找最为常用和最为有效的索引。B+树能加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，B+树所有数据存储在叶子节点，复杂度为O(logn)。

B树和B+树

首先我们来回顾一下

二分查找法

从5、10、19,、21、31、37、42、48、50、55这一个有序数列，如果用顺序查找的话需要查找8次，但是如果用二分查找的话则只需要3次：

二叉搜索树

二叉搜索树的定义是：左节点小于父节点的值、右节点大于父节点的值，例如这个二叉搜索树：找到数字8，需要查找3次，而顺序查找需要6次：

同样的下图也是一个二叉搜索树，但是下图这种的查找效率会很低(斜树)：

所以我们就由二叉搜索树引出了：

平衡二叉树

平衡二叉树的要求是：任何子树高度最大差值为1.平衡性确保查找的速度可以很快，也就避免了二叉搜索树的极端情况

遍历方式的演变

以上遍历方式由顺序遍历--存储地址--二分查找--二叉搜索树--平衡二叉树，逐步演变，查找效率提高的同时也付出了空间变大，需要维护的代价。

B树

平衡二叉树随着节点的增加，树的高度会越来越高，则磁盘的I/O次数也会增加，影响查询效率，从而引出了B树：B树不限制一个节点只能有两个子节点，这样就降低了树的高度。

B树可以将节点的大小优化为磁盘块的大小，每次读取可以有效加载多个节点，B树常用语数据库等于要访问的磁盘的场景。

由上图可以看出：B树的每个节点的key值是和其数据是挂在一起的。

B+树

B+树是对B树的升级，B+树只有叶子节点存数据，非叶子节点只存索引。叶子结点包含所有索引，叶子节点构成一个有序链表，范围查找更快。由于非叶子节点只存索引，B+树比B树的非叶子节点可以存更多索引，高度更低，磁盘I/O次数更少。

B+树相比于B树的优点：

B+树是一个平衡的多路查找树
每个节点的关键字key都是按照从小到大的顺序排列的
叶子节点是双链表串联的，方便范围查找
非叶子节点只存储索引值，不存储数据，而B树非叶子节点也会存储数据，B+树在B树的基础上降低了树的高度。
由于高度相比B树高度低从而降低了磁盘I/O的次数

使用索引

介绍如何使用索引之前，我们来举个索引使用的例子：假设有一个表，表中有一个‘name’字段，在当我们要经常查询这个‘name’字段中的相关数据时，倘若没有任何索引，那么在查找时系统就会逐个字段遍历直至找到名为‘name’的字段，所以我们就可以为‘name’字段添加一个索引，建立索引的本质上也就是为这个字段建立了一个B+树。

创建索引

create index 索引名 on 表名(列名)；
create index 索引名 on 表名(列明(前缀长度))；

上述第二行代码的前缀长度为：当我们需要建立索引的字段很长，例如文章内容text，如果全都存下会很占内存，那么此时就可以取前缀来建立索引。

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

目前和删除有关的语句有两个：

delete 删除数据
drop 删除结构

查看某一条语句是否使用了索引

explain 查询语句;
desc 查询语句;

下面根据上图来介绍一下

explain各属性的含义

id：查询的序列号(暂时不做探讨)
select_type:查询的类型，主要区别普通查询和联合查询，子查询之类的复杂查询
1. SIMPLE:查询中不包含子查询或者UNION(联合查询)
2. PRIMARY:查询中包含任何复杂的子部分
3. SUBQUERY:作为SELECT或WHERE列表中的子查询
table：输出的行所引用的表
type：访问类型
1. ALL：扫描全表，也就是该字段没索引
2. index：扫描全部索引树，该字段有索引，但要扫描全部索引树来找到范围比ALL小
3. range：扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between，<,>等的查询。
4. ref：使用唯一索引或非唯一索引前缀进行的查找
5. eq_ref：唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配，常见于主键或者唯一性扫描
6. const,system：单表中最多有一个匹配行，查询起来非常迅速，例如主键或唯一索引查询，system是const类型的特例，当查询的表只有一行的情况下，使用system。
7. NULL ：不用访问或者索引，直接能得到结果，select 1 from test where 1
key:显示MySQL实际决定使用的索引，如果没有索引被选择，是NULL
key_len：使用索引字段的长度，key_len显示的值为索引字段最大的可能长度，并非实际长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref：显示哪个字段或常数与key一起被使用
rows：这个数表示MySQL要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innoDB上可能不是准确的
Extra：执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
1. Using index：表示使用索引，如果只有Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，即覆盖索引
2. Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where

前缀索引

介绍前缀索引的时候，我们先拿之前建立的表格举一个例子：

如果我们要添加索引的是文章内容‘text’，那么每一个‘text’的大小相比id和author来说是非常大的，则该索引的地址会非常大，相当于建立了两份占很大空间的表，就没意义了。

并且数据库要求索引大小不能超过767字节，所以对于文章内容text这类较大的内容来说，我们应该使用前缀索引

前缀索引的优缺点：

优点：节省空间

缺点：索引的选择性

选择性定义：不重复索引值/总的行数。

基于前缀索引，我们举个例子：

create table if not exists alphabet(
alpha varchar(4)
);
insert into alphabet(alpha) 
values ('abcd'),
('abcc'),
('abas'),
('accs')
;

不重复索引值M = 3（abcd，abcc，abas）

总的行数N = 4

前缀为‘ab’的前缀索引的选择性是3 /4 = 0.75，选择性越靠近1越好。

如何找到最适合的前缀长度

我们知道，选择性越靠近1越好，那么我们如哈找到最适合的前缀长度呢？

#有一表 pref 中存储一系列名字
#第一步
#统计 name字段最常见值的数量
select count(*) as cnt,name from pref
group by name
order by cnot desc limit 5;
#第二步
#统计 name前缀最常见值数量
select count(*) ans cnt,left(name,1) as pre_name from pref
group by pre_name
order by cnt desc limit 5;
#与第一步对比看是否相近
select count(*) ans cnt,left(name,2) as pre_name from pref
group by pre_name
order by cnt desc limit 5;
#与第一步对比看是否相近
...

最后进行多次对比之后找到最适合的前缀索引的前缀长度。

聚簇索引

聚簇索引是一种数据存储方式，是将数据放在索引的叶子页，索引和数据在同一个B+树上。

聚簇索引决定了表在磁盘中的物理排序，因为这个排序只能有一个，所以聚簇索引是唯一的。

在InnoDB中，这个索引就是主键，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，也没有的话InnoDB会隐式定义一个主键作为索引。

在非聚簇索引中可以看到，如果要在非聚簇索引B+树中查找表内数据，因为该B+树中只有索引以及主键值，所以要根据主键值再回到聚簇索引的B+树中找到想要的数据，这个从非聚簇索引回到聚簇索引的行为叫做——回表。

聚簇索引优点

当问到聚簇索引的优点时，我们要从两方面分析

MySQL中设置聚簇索引相对于MySQL不设置聚簇索引的优点

可以把相关数据保存在一起

聚簇索引和非聚簇索引相比具有的优点

访问数据更快索引和数据在同一个结构中

聚簇索引缺点

插入速度严重依赖插入顺序(对应一般主键都设置为自增，这样避免了插入的顺序不一致)
更新聚簇索引列的代价很高(对应设置主键的列不轻易改变)
插入数据或更新主键时可能面临“页分裂”问题。当主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来进行，也会导致表占用更多的磁盘空间(同样是对应插入顺序，如果在两个连续的数据中间插入一个新的数据可能会面临“页分裂”)
非聚簇索引需要两次查找(回表)

覆盖索引

我们来将非聚簇索引的B+树单独拿出来看一下：

如果我们查找的数据就在非聚簇索引的索引值和主键值中，那么就不需要回表查询了，就省去回表这一操作的时间了，这一现象就叫做——覆盖索引，（非聚簇索引中的数据包括了要查找的所有数据），索引覆盖的现象叫做覆盖索引。

create index ename_job on emp(ename,job);
explain select ename,job from emp;
#索引覆盖，不采取回表，直接在非聚簇索引中能查到所有想要数据

查询所需要的数据都可以从索引中获取，而不用再去查询数据表中的实际数据，覆盖索引可以减少树的搜索次数，避免了回表，显著提高了查询性能。

最左前缀原则

MySQL在建立联合索引时，会从左到右对字段进行依次匹配，满足最左前缀原则，例如我们建立一张表 card，表的结构为：

然后我们创建一个(name,age,sex)三列的联合索引

create index name_age_sex on id_car(name,age,sex);

就相当于创建了：name,(name,age),(name,age,sex)这三个索引，创建完联合索引之后，在检索数据时会从联合索引的最左边开始匹配。

例如上表，在我们进行检索查询的时候，会从最左端的‘name’字段向右边依次查询。

例如以下查询代码：

select * from card where name = '张三';                             #命中索引
select * from card where name = '张三' and age = 18;                #命中索引
select * from card where name = '张三' and age = 18 and sex = 'man';#命中索引
select * from card where age = 18 and sex = 'man';                 #未命中索引
select * from card where sex = 'man';                              #未命中索引

联合索引在explain查询中的Extra中会显示Using Index condition。

索引策略

独立的列：索引使用不当会导致索引失效，(查询中实际没有使用索引)。如果查询中的列不是独立的，MySQL不会使用索引。独立的列是指查询时索引列不能是表达式中的一部分，也不能是函数的参数(聚合函数等),这两种情况都会失效，下面举几个简单的例子：

表id_card

id	name（varchar(20)类型）	age(int类型)
1	‘LiHua’	18
2	'Chensen'	20

select * from id_card where age + 1 = 20;    #索引失效
select * from id_card where age != 20；      #索引失效
select * from id_card where age > 10;        #索引失效
select * from id_card where age = 'LiHua';   #索引成功
#成功原因:age是int 类型 int 类型 = varchar类型 varchar类型需要强制转换成int类型，但是等式左边不发生计算，所以索引成功
select * from id_card where name = 18;   #索引失败
#失败原因：name需要转换成int 类型采取计算 所以索引失败