【数据库】索引和事务

whisper-h

于 2023-04-25 13:25:37 发布

阅读量653

点赞数 1

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

本文链接：https://blog.csdn.net/m0_57248752/article/details/130359209

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.索引

1.1关于索引

索引是什么？

概念：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

为什么要有索引？

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

如果没有索引，那么查找时可能需要全整的遍历整个数据集，间复杂度，最坏就是O(N)有了索引之后，就可以通过⻚码，快速定位⼀个范围，然后再这个⼩范围内去找，这时的时间复杂度就

⼤⼤降低了。

索引的作用？

在数据库系统中建立索引主要有以下作用：

（1）快速取数据；

（2）保证数据记录的唯一性；

（3）实现表与表之间的参照完整性；

（4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

索引的优点和缺点？

优点：

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

补充：索引是一个典型的以空间换时间的操作

1.2索引类型及创建

根据数据库的功能，可以在数据库设计器中创建四种索引：单列索引、唯一索引、主键索引和聚集索引。

索引的分类

a. 按使⽤场景分：普通索引，主键索引，唯⼀索引，全⽂索引

b. 按数据组织⽅式分：聚簇索引，⾮聚索引

c. 还有⼀种数据查找过程中出现的现象叫做索引覆盖

创建索引

自动创建索引：

⾃动的创建主键约束（PRIMARY KEY）、唯⼀约束（UNIQUE）、外键约束（FOREIGN KEY）时，会⾃动创建对应列的索引。

示例：

create table test(id int primary key,name varchar(10));
show index from test;

此表中的主键自动创建了索引。

手动创建索引：

普通索引：

语法：

CREATE INDEX <索引的名字> ON tablename (列的列表);

示例：

create table test(id int,name varchar(10));
create index index_test on test(id,name);

唯一索引：

语法：

CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；

补充：

⼀张表⾥⾄少会有⼀个索引

a. 如果⼀张表⾥没有主键，MySql会为每⼀⾏⽣成⼀个唯⼀的字段，并⽤这个字段当前做索引

b. 如果⼀张表⾥有主键，那么主键必然是索引，主键索引也叫聚簇索引，⽽且⼀直存在这也是我们绝⼤部分情况下的使⽤场景

c. 如果⾃⼰⼿动创建索引，那会会为这个列或是列的组合（多个列）创建单独的索引，⾮聚簇索引

索引的命名规范：

普通索引名：idx_<table>_<column>_<column>
唯一索引名：uniq_<table>_<column>_<column>
其中<table>是建立索引的表名，<column>是建立索引的字段名
索引名限制在30个字符内。当索引名超过30字符时，可用缩写来减少索引名的长度，如description –> desc；information –> info；address –> addr等。

1.3索引的数据结构

索引是为了快速查询，那么用什么数据结构可以使数据查询比较快呢?在HASH，二叉搜索树，B树，B+树中，MySQL选择了B+树这种数据结构。

1.HASH

哈希表：散列表（Hash table，也叫哈希表），是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。

哈希作为一个非常常用的查找数据结构，它能够在O(1) 的时间复杂度下进行数据查找。

但是哈希并不支持范围查找，大于小于，between and这种操作，哈希使不支持的。

2.二叉搜索树

二叉搜索树：定义规则为“左边节点值比根节点小，右边节点值比根节点大，并且左右子节点都是排序树”。

红黑树：红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率。

二叉搜索树无法控制高度，就无法决定磁盘的访问次数。

3.B树

B树：B树是一种多路搜索树，每个子节点可以拥有多于2个子节点，M路的B树最多可拥有M个子节点。设计成多路，其目的是为了降低树的高度，降低查询次数，提高查询效率。

但是MySQL没有选择这种数据结构，而是在B树的基础上又做了优化。

4.B+树

B+树：B+树是对于B树进行优化的多路搜索树，主要设计是将数据全部存放于叶子节点，并将叶子节点用指针进行链表链接。

1.叶子节点是相互连接的，MySQL中使用的B+树使用的是一个双向循环链表

2.叶⼦节点包含了树中所有的真实数据，非叶子节点包含的是主键和叶子节点的引用

3.在一定数量范围内，不论查找的数据是什么，查找的时间效率基本相同

4.叶子节点的数据是有序的，所以支持范围查找

5.有效降低了树的高度，减少了磁盘的IO次数

1.4索引覆盖

索引的最左原则：在查询的时候在where中按照索引的顺序写过滤条件
索引覆盖：如果索引中包含要查询的所有列，那么直接从索引中返回结果，这个叫做索引覆盖

回表查询：非主键索引查找数据时需要先找到主键，再根据主键查找具体行数据，这种现象叫回表查询

索引失效：

1. 最左原则：类似于字典的⽬录，这就是⼀个典型的复合索引

2. 判断不等：每个都要判断

3. 类型转换：与原类型不符

4. like '%xxx'：第⼀个字符都不能确定，怎么去索引中⽐较呢？

5. 索引列运算 age + 1：改了原来的值

6. is null 或 is not null ：全表扫描了