MySQL之索引作用与实现，所以的创建、删除、唯一索引、普通索引、及命名规则；何时创建索引、创建注意事项、如何优化索引、索引失效；聚簇索引与非聚簇索引；使用自增还是UUID

置顶风华浪浪

已于 2023-09-04 10:22:26 修改

阅读量1.6k

点赞数 1

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

于 2023-02-13 17:57:42 首次发布

本文链接：https://blog.csdn.net/a6864657/article/details/129013347

版权

数据库专栏收录该内容

23 篇文章 0 订阅

订阅专栏

一、MySQL 索引作用与实现

1.1 索引的作用与实现

索引：索引是一种排序的数据结构，它存储了一些列值以及它们在相应数据表中的位置信息
作用：协助快速查询、更新数据表的数据，从而提高查询效率。
实现：通常基于B-tree或hash表实现。

1.2 B-tree树

图片来源 BTree和B+Tree详解 https://blog.csdn.net/yin767833376/article/details/81511377
在这里插入图片描述
B-tree的结构与二叉搜索树不同，它的每个节点可以包含多个键和对应的指针，且所有叶子节点均处于同一层级，因此它的查找和遍历效率更高。

B-tree索引是一种自平衡树结构，它可以快速定位一个值或一段值的范围，因为B-tree索引将数据分成块并在每个块上建立一个搜索树
B+树的非叶子节点不存储数据，只存储索引信息，而叶子节点存储所有数据信息，这样可以使得每个节点存储更多的关键字信息，从而减少节点数目，降低树的高度，提高查询效率
B+树的所有叶子节点之间通过一个指针串联起来，形成一个有序链表，便于范围查询等操作
B+树的索引结构更加紧凑，因为每个节点存储的是索引信息而非数据信息，从而减少了节点所需的存储空间，降低了磁盘I/O操作次数
B+树的叶子节点中包含了所有的数据信息，因此在进行全表扫描时可以更加高效，而且在范围查询等操作时，只需要遍历叶子节点，而非遍历整棵树
B+树的叶子节点只包含关键字和对应的数据记录的指针，而不是数据记录本身，可以提高B+树的查询效率，并减少内存的占用

综上所述，B+树是一种高效的索引结构，广泛应用于数据库系统中。它的应用可以提高查询效率，减少磁盘I/O操作，从而提高数据库系统的性能。
在这里插入图片描述

1.4 B-tree 与B+tree 区别

在这里插入图片描述

B-tree
	1. 一个节点有多个元素
    2. 节点左侧都是比其小，节点右侧比其大(有序性)
    3. 叶子节点之间没有指针

B+tree： B+tree 存的数据叶子节点均存在，叶子节点之间用指针链接起来，且有序性，且数据冗余
    1. 一个节点有多个元素
    2. 节点左侧都是比其小，节点右侧比其大(有序性)
    3. 叶子节点之间存在双向指针
    4. 非叶子节点的数据均在叶子节点上面冗余一份

Mysql索引使用的是B+树，因为索引是用来加快查询，而B+树通过对数据进行排序可以提高查询速度，然后通过一个节点中可以存储交个元素，从而可以使得B+树的高度不会太高。
在Mysql中—个nnodb页就是一个B+树节点，一个Innodb页默认位16kb
所以一般情况下一棵两层的B+树可以存2000万行左右的数据，然后通过利用B+树叶子节点存储了所有数据并且进行了排序，
并且叶子节点之间有指针，可以很好的支持全表扫描，范围查询等SQL语句

二、索引的分类、创建、删除、何处创建索引

2.1 根据其特性和用途进行分类

普通索引：是最基本的索引类型，用于加速对列的查询。它可以包含重复值和空值
唯一索引：唯一索引要求索引列的值必须唯一，不允许重复值，但允许空值。它用于确保索引列的唯一性
主键索引：主键索引用于唯一标识表中的每一行。它要求索引列的值唯一且非空，每个表只能有一个主键索引
复合索引：复合索引是指一个索引包含多个列。它用于加速涉及到复合索引中的所有列的查询

2.2 根据其特性和用途进行分类

查看某张表索引
```
show index from account;
```

删除某个索引

drop index idx_name on talbe_name;
alter table table_name drop index idx_name;

创建普通索引命名规则：idx_xxxx index一定要简写简写间歇说三遍
索引值可出现多次

create index idx_name on table_name (column_1, column_2, ...);
create index idx_email on account (email);

索引列的长度较长，选择前面的一部分作为索引是一个通用的策略
CREATE INDEX idx_name ON table_name (column_name(10));


alter table table_name add index index_name (column_1, column_2, ...)
alter table account add index idx_email (email);

创建唯一索引命名规则：uniq_xxxx unique一定要简写简写简写说三遍 ：
索引值是唯一的(NULL除外，NUL可出现多次)

create unique index uniq_name on table_name (column_1, column_2, ...);
create unique index uniq_email on account (email);

alter table table_name add unique uniq_name (column_1, column_2, ...);
alter table account add unique uniq_email (email);

创建主见索引命名规则：uniq_xxxx unique一定要简写简写简写说三遍
索引值必须是唯一的，且不能为NULL

create  [unique] index inx_name ON table_name (columname(length))   
alter table table_name add PRIMARY KEY (column_list):

创建全文索引：索引为FULLTEXT，用于全文索引
FULLTEXT是一种特殊类型的索引，用于对文本数据进行全文搜索。
它可以对文本数据进行分词，并为每个分词建立索引，以支持快速检索数据
```
alter table table_name add fulltext index idx_name (column_1, column_2, ...);
```

2.3 什么情况下创建索引

索引可以大大加快查询速度，同时维护索引需要额外的存储空间和时间

1. 频繁作为查询条件的字段应该创建索引
2. 频繁更新的字段不适合创建索引(因为不仅更新字段，还要跟新索引)
3. where条件用不到的字段不用创建索引
4. 高并发情况下倾向组合索引
5. 查询中的排序字段，将大大提高排序速度
6. 查询中统计或分组字段
7. 表记录太小
8. 经常增删改的表（提高查询速度, 降了更新速度；因为更新不仅保存数据，还要跟新索引）
9. 数据重复的内容 （效果不大）

2.4 创建索引注意事项

索引列应该是经常被查询和排序的列。
尽量选择具有唯一值的列作为索引列，因为这样可以提高索引的效率。
索引列的数据类型应该尽量简单，例如整数或字符串，而不是复杂的数据类型，如日期和时间。
如果索引列的长度较长，应该选择前面的一部分作为索引。
不要对大量重复的列创建索引，因为这样可能会导致索引变得很大，并降低性能。
避免创建太多的索引：如果表中有太多的索引，那么插入、更新和删除操作可能会变慢。
尽量避免对于 NULL 值的列创建索引：如果列中有大量的 NULL 值，那么索引可能会变得非常大，从而降低查询性能。
避免在多个列上创建组合索引，除非它们经常被查询和排序。
需要注意的是，创建索引可以提高查询性能，但是也会增加表的大小，因此应根据需要选择是否创建索引。

2.5 索引失效无法命中索引的情况

组合索引不符合左前缀原则(最左侧的索引列的字段，而非查询的最左边)
如我们有4个列a、b、c、d，我们创建个组合索引 IDEx(b,c.d）创建普通索引 a
那么能命中索引的查询为 b, bc, bcd，除此之外都无法命中索引
```
explain select * from t1 where b=1;  命中
explain select * from t1 where b=1 and c=1 and d=1;  命中
explain select * from t1 where c=1 and d=1 and b=1;  命中
explain select * from t1 where c=1 and d=1;  没命中，缺少最左a
```
不正确的like查询，如like '%a' 或者 like '%a%'
```
explain select * from t1 where b like '1%';  	命中
explain select * from t1 where b like '%1';  	没命中
explain select * from t1 where b like '%1%';  	没命中
```
1. %号在右：由于B+树的索引顺序，是按照首字母的大小进行排序，
  所以%号在右符合B+树上进行有序的查找
2. %号在左：匹配字符串尾部数据。B+树首字母排序规则，尾部字母没有顺序的
  所以不能按照索引序列顺序查询，就用不到索引
3. 两个%％号：查询任意位置的字母满足条性即可，只有首字母是退行家引非序约，
  其他位置的字安都是相对无序的，所以查找任意位置的学母是用不上素引的

对索引列进行了计算或者使用函数

explain select * from t1 where a=1;      		 命中
explain select * from t1 where a+1=1;    		 没命中
explain select * from t1 where ifnull(a,0)=1;    没命中(a是否等于空)

索引列进行类型转换
e字段类型vachar，查询sql把e字段为int类型，索引失效（因值是字符）
a字段类型int，a = 1 、a = ‘1’ 可以命中索引（因值是字符）
```
explain select * from t1 where e='1';      		 命中
explain select * from t1 where e=1;      		 没命中
```

<>不等于索引失效

explain select * from t1 where b=1;      		 命中
explain select * from t1 where b<>1;      		 没命中

order by 导致索引失效

explain select * from t1 order by b,c,d;        没命中

or 导致索引失效

explain select * from t1 where b=1 or c=1;        没命中

or 导致索引失效

explain select * from t1 where b=1 or c=1;        没命中

范围查询数据量过多，可能导致索引失效(条件查询尽可能精确)
如果mysql 估计使用全表扫描要比使用索引快，则不使用索引

使用or关键字会导致无法命中索引
左前导查询会导致无法命中索引，如like "%a 或者 like "%a%’
单列索引的索引列为 null 时全值匹配会使素引
失效，组合索引全为 null 时索引失效
组合索引不符合左前缀原则的列无法命中索引，如我们有4个列a、b、c、d，我们创建个组合索引 IDEx(a,b,c.d），那么能命中索引的查询为 a, ab, abc, abcd，除此之外都无法命中索引
强制类型转换会导致索引失效
负向查询条件会导致无法使用索引，比如 NOT IN NOT LIKEI 等
如果mysql 估计使用全表扫描要比使用索引快，则不使用索引

三、索引优化

查看SQL语句的执行计划，查看该SQL语句有没有使用索引，有没有做全表扫描，获得可能被优化器考虑到的访问策略的细节，
ID
- id相同，执行顺序由下而上(t2–>t3–>t1)
id不同，如是子查询，id号会递增；id值越大则优先级越高越先执行(t3–>t1–>t2)
- select_type
- SIMPLE 简单的select查询，查询中不包含子查询或者UNION
- PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
- SUBQUERY 在SELECT或WHERE列表中包含了子查询
- DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
- UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
- UNION RESULT 从UNION表获取结果的SELECT
type

最好到最差依次是：system > const > eq_ref > ref > range > index > all
all 全表扫描 非常暴力和原始的查找方法，非常的耗时而且低效,有很大的优化空间。
- S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止
index 按照索引顺序全表扫描
range 有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index
ref_eq 结果集只有一个，使用主键或者唯一性索引进行查找的情况精确查询，无需过多的扫描
- 比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询
const 将一个主键放置到where后面作为条件查询，mysql优化器就把查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器
possible_keys 可用能使用的索引，不一定被查询时使用
key 列显示MySQL实际使用的索引；没有选择索引键是NULL
key_len 索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度）
不损失精确性的情况下，长度越短越好
rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

四、索引使用自增还是UUID，数据库主键的类型如何选择？

Auto-increment

简单易用：自增主键是数字类型，更易于管理和理解
性能优势：数字类型的自增主键通常比UUID更节省存储空间，查询也更快
有序：因为主键是自增的，所以插入数据时可以保证数据在物理存储上的有序性

可预测性：因为是自增的，攻击者可能会利用这一点进行一些安全攻击。
扩展问题：在分布式系统中，保证全局唯一的自增ID可能会比较复杂(分库分表自增场景不再适用)

UUID

全局唯一：UUID可以保证在分布式系统中的全局唯一性
安全性：UUID是难以预测的，增加了系统的安全性
UUID可以在应用层生成，提高吞吐能力

性能问题：因为UUID是较长的字符串，所以会占用更多的存储空间，并且查询速度可能会慢一些。
无序：UUID是无序的，如果需要有序存储，这可能会是一个问题。

怎么选择？
如果您的应用是单体应用或小型应用，并且没有分布式扩展的计划，使用自增主键通常是更简单、更高效的选择。
如果您的应用是一个大规模、分布式的系统，或者您需要合并多个数据库，使用UUID可能是更好的选择。
总体而言，选择哪种类型的主键取决于您的具体需求和应用场景。

五、聚簇索引与非聚簇索引

聚簇索引与非聚簇索引的区别：叶子节点是否存放一整行记录
聚簇索引：将数据存储与索引放到一块，索引结构的叶子结点保存了行的数据
非聚簇索引：将数据与索引分开存放，索引结构的叶子结点指向了数据对应的位置
InnoDB主健使用的是聚簇索引；MyISAM 非聚簇索引
聚簇索引
聚簇索引是一种数据存储方式，InnoDB的索引是按照主健顺序构建B+Tree结构。
B+Tree 的叶子节点就是行记录。行记录和主键值紧凑地存储在一起；叶子节点直接包含了完整的数据行内容
也就是说，数据是按照主键的顺序直接存储在叶子节点中的。因此，聚簇索引本身就是表数据
非聚簇索引
非聚簇索引和聚簇索引的B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同
非聚簇索引的B+树叶子节点并不包含完整的数据行。
叶子节点包含索引键值和一个指向聚簇索引中相应数据行的“指针”

因此，当您通过非聚簇索引进行查询时，通常会涉及两次查找：
首先，在非聚簇索引的B+树中按索引键值查找，找到叶子节点获取对应的聚簇索引键值。
然后，在聚簇索引的B+树中按键值查找，直接找到并读取数据行
这种两步查找过程也被称为“回表”操作
综上所述，
聚簇索引的B+树中，叶子节点直接存储了完整的数据行内容，而非仅仅是主键和一个指向数据行的地址。
非聚簇索引，叶子节点包含索引键值和一个指向聚簇索引中相应数据行的“指针”

六、mysql 存储引擎 MyISAM 与 InnoDB

事务和外键
```
InnoDB 支持事务和外键，具有安全性和完整行，适合大量的insert和update操作
MyIsAM 不支持事务和外键，提供高速的存储和检索，适合大量的select查询操作
```
InnoDB实现了ACID属性（原子性、一致性、隔离性和持久性），这使得数据处理更为可靠和健壮
外键约束允许建立表与表之间的关系，确保数据的参照完整性
InnoDB提供了数据完整性检查、支持提交和回滚操作,并在系统崩溃后可以恢复数据
锁粒度
```
InnoDB支持行锁，锁定指定行记录。基于索引来加锁实现
MyISAM 支持表锁，锁定整张表
```
InnoDB使用行级锁定，同一时间可以有多个事务并发地操作表中的不同行
MyISAM表锁一个更新语句会锁住整张表，导致其它查询和更新都会阻塞，因此并发受限
这也是MySQl将默认存储引擎由MyISAM变成InnoDB的主要原因之一

索引结构

InnoDB使用聚簇索引，索引和记录在一起存储，既缓存索引，也缓存记录
MyISAM 非聚簇索引。索引和记录分开存储
MyISAM支持FULLTEXT类型的全文索引
InnoDB不支持FULLTEXT类型的全文索引，可使用spinx插件支持此类型

存储的文件
MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型

.frm 文件存储表的定义
数据文件的扩展名为 .MYD (MYData)
素引文件的扩展名是.MYI (MYindex)

Innodb 存储引擎存储数据库数据，一共有两个文件(没有专门保存数据的文件)：

frm文什：表的定义文件
Ibd文件：数据和索弓!存储文件。数据以主键进行聚集存储，把真正的数据保存在叶子节点中。

风华浪浪

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL之索引作用与实现，所以的创建、删除、唯一索引、普通索引、及命名规则；何时创建索引、创建注意事项、如何优化索引、索引失效；聚簇索引与非聚簇索引；使用自增还是UUID

B-tree的结构与二叉搜索树不同，它的每个节点可以包含多个键和对应的指针，且所有叶子节点均处于同一层级，因此它的查找和遍历效率更高。它的应用可以提高查询效率，减少磁盘I/O操作，从而提高数据库系统的性能。key_len 索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度）：唯一索引要求索引列的值必须唯一，不允许重复值，但允许空值。：复合索引是指一个索引包含多个列。，相对于index的全索引扫描，它有范围限制，因此要优于index。：是最基本的索引类型，用于加速对列的查询。
复制链接

扫一扫