MySQL数据库 --- MySQL索引事务_解数据库原理和mysql数据库,对索引、事务

MySQL索引事务

1. 索引

1.1 索引的概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。 更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1.2 索引的作用

现在我们有一个student表 需要找到学号为8的学生信息

在没有索引的情况瞎,此时数据库查找过程类似于"顺序表"查找.
顺序表是在内存中进行查找的,内存访问的速度快,数据也不多,其速度也是可以的.
数据库顺序查找,数据库的数据是在磁盘上的,磁盘访问的速度慢,数据量很多,其速度就很慢.

索引就是为了避免数据库进行顺序查找,提高查找的效率

1.3索引的数据结构

1.3.1 为什么不用哈希表

我们知道哈希表的查找效率是 O(1) 的,所以使用哈希表的话 查找一个数据就会非常的快,那为什么索引不用哈希表呢?

原因: 如果使用哈希表,只能处理相等的情况,无法处理 > < >= 等 情况.因为哈希表存储是没有顺序的.

1.3.2 为什么不用二叉搜索树

我们知道二叉搜索树的 中序遍历结果是有序的.如果要查找 id<6 且 id>3的数据.可以先找到 id =3 再找到 id = 6 然后中序遍历在找到 3~6之间的数据就可以了.相比于哈希表,二叉树能处理范围查找,那为什么不用二叉搜索树呢?

原因: 如果使用二叉搜索树,二叉树每个节点最多有2个叉,当数据量大时,树的高度就会较高,最终操作的效率也会非常低,而且二叉搜索树获取到中序遍历的效率也不是很高效O(N).这样效率很低,就跟普通查找效率差不多了.

1.3.3 什么是B树

B-TREE的优势：

•  不再是二叉搜索，而是N叉搜索，树的高度会降低，查询快
  1

•  叶子节点，非叶子节点，都可以存储数据，且可以存储多个数据
  1

• 通过中序遍历，可以访问树上所有节点
  1

1.3.4 什么是B+树

真实的索引是用的B+树.

B+TREE的优势：

1. 仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样，而B树不是这样.(表示查询任何一条记录速度是比较平均的,不会出现效率差异大的情况.)
2. 叶子之间，增加了链表(图中红色箭头指向)，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到
3. 范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）
4. 叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储
5. 非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引

1.4 使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

1.5 索引的使用

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

1.5.1 查看索引

show index from 表名;
1

示例:

1.5.2 创建索引

create index 索引名 on 表名(字段名);
1

示例:

1.5.3 删除索引

drop index 索引名 on 表名;
1

示例:

1.6 MySQL中的索引类型

• 普通索引 : 允许被索引的数据列包含重复的值
• 唯一索引 : 可以保证数据记录的唯一性
• 主键索引 : 是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字PRIMARY KEY来创建
• 联合索引 : 索引可以覆盖多个数据列,如像INDEX(columnA,columnB)索引
• 全文索引 : 通过建立倒叙索引,可以极大的提升检索效率,解决判断字段是否包含的问题,是目前搜索引擎使用的一种关键技术,可以通过ALTER TABLE table_name ADD FULLTEXT(column); 来创建全文索引

2. 事务

2.1 为什么使用事务

现有一个数据表,存储了一些人的银行账户,
现在有一个人A需要向B转账3000元.

此时就需要执行两个操作.

1. A的账户余额 - 3000
2. B的账号余额 + 3000

假如在执行操作1的时候,执行之后,网络出现错误,或 数据库挂了,A的钱少了B的钱没有增加,3000就凭空消失了?

解决方案: 使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败

2.2 事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务.

2.3 事务的基本特性(ACID)

事务的基本特性称为 “ACID”

• 原子性（atomicity，或称不可分割性）: 事务中的若干操作,要么全部执行成功,要么就都不执行.(这里的不执行,并不是真的不执行,而是一旦中间的某个步骤执行出错,就把前面已经执行完毕的步骤回滚(rollback)回去.)

• 一致性（consistency）: 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。执行事务前后,数据始终处于一种合法的状态.(例如转账操作,减账户余额的时候,不能把账户减成负数)

• 隔离性（isolation，又称独立性）: 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

• 持久性（durability）: 事务一旦执行完毕,此时对于数据的修改就是持久生效的(数据存到磁盘中就是持久的.存到内存中就是不持久的)

2.4 事务的使用

（ 1） 开启事务： start transaction;
（ 2） 执行多条SQL语句
（ 3） 回滚或提交： rollback/commit;

说明： rollback即是全部失败， commit即是全部成功

测试表

drop table if exists accout;
create table accout(
id int primary key auto_increment,
name varchar(20) comment '账户名称',
money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('阿里巴巴', 5000),
('四十大盗', 1000);
1
2
3
4
5
6
7
8
9

操作截图: