目录
1.2.1 事务的提交方式(MYSQL中有两种方式进行事务的操作)：
1.3 事务的四大特性 / 特征 --- ACID(重点掌握)
最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：
思考题： 为什么InnoDB存储引擎选择使用B+tree索引结构?
接下来，我们就来看一下在B+Tree结构当中，它又是如何来规避这个问题的？
MySQL为什么要用B+Tree索引而不是用BTree索引？
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
按照底层存储方式角度划分：聚簇索引和非聚簇索引 / 辅助索引 / 二级索引
聚集索引和二级索引 / 辅助索引 / 非聚簇索引 的具体结构如下：
这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
1. 事务
- 在MySQL中只有使用了InnoDB存储引擎的数据库或数据库表才支持事务，MySQL默认就是InnoDB存储引擎！
场景：学工部整个部门解散了，该部门及部门下的员工都需要删除了。
在部门表当中维护的是部门的相关信息，在员工表当中维护了员工的相关信息，在员工表当中有一个字段dept_id关联的就是部门表的主键。
- 操作：
-- ================================事务==================================
-- 模拟一个解散部门的操作 --- 解散学工部
-- 删除学工部
delete from tb_dept where id = 1; -- 删除成功
-- 删除学工部下的员工
delete from tb_emp where dept_id = 1; -- 删除失败(操作过程中出现错误:造成删除没有成功)
-- 在操作前后,数据出现了不一致的问题
- 问题：如果删除部门成功了，而删除该部门的员工时失败了，此时就造成了数据的 不一致。
要解决上述的问题，就需要通过数据库中的事务来解决。
导学：什么是事务，以及事务的控制操作和事务的四大特性
- 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
- 事务的使用场景的典型案例就是转账：转账时不允许出现任何差错的，出现一点差错都会造成巨大的损失。例如：此时用户A给用户B转了200块钱，那么在数据库中执行的操作就是让用户A的余额减200，让用户的B的余额加200。
- 如果不使用事务，则可能会出现以下问题：用户A的余额在减200之后，数据库在执行会给用户B的余额加200时，数据库挂了或者出现异常情况了，此时用户B的余额并没有加200，那么A就平白无故少了200元，此时问题就很大了。
- 但如果使用事务时，用户A的余额减200，用户B的余额加200这两个操作是一个整体，结果就两个：要么成功，要么失败。那么上述出现的问题就可以很容易进行处理了，如果出现上述的问题，那么此时数据库就会进行事务的回滚，可以理解为撤销操作，将数据库恢复到数据执行前。由此可见事务的重要性。
-- =========================转账案例演示事务============================== -- 创建db10数据库 CREATE DATABASE db10; -- 使用db10数据库 USE db10; -- 创建账户表 CREATE TABLE account( id INT PRIMARY KEY AUTO_INCREMENT, -- 账户id NAME VARCHAR(20), -- 账户名称 money DOUBLE -- 账户余额 ); -- 添加数据 INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
-- =======================管理事务演示========================= -- 开启事务 START TRANSACTION; -- 张三给李四转账500元 -- 1.张三账户-500 UPDATE account SET money=money-500 WHERE NAME='张三'; -- 2.李四账户+500 -- 出错了... UPDATE account SET money=money+500 WHERE NAME='李四'; -- 回滚事务(出现问题) ROLLBACK; -- 提交事务(没出现问题) COMMIT;
1.1 介绍
事务是数据库当中非常重要的一个特性。
- 在实际的业务开发中，有些业务操作要多次访问数据库。
- 一个业务要发送多条SQL语句给数据库执行，需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功，要么全部执行失败。
- 事务：一条或多条SQL语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败
- 单元中的每条SQL语句都相互依赖，形成一个整体。
- 如果其中有一条SQL语句执行失败或者出现错误，就会进行事务的回滚，那么整个单元就会撤回到事务最初的状态，所有的SQL语句全部执行失败。
- 如果单元中所有的SQL语句都执行成功，则事务就顺利执行。
- 简而言之：事务是一组操作的集合，它是一个不可分割的工作单位。 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
- 事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。
提问：刚才在执行解散部门的这个业务操作时，涉及到两个操作，一个是删除部门，一个是删除 部门下的员工，由于它是属于同一个业务，也就意味着这两个操作是一个不可分割的工作 单位，属于一个事务，里面所涉及到的操作要么同时成功，要么同时失败，那为什么我们 刚才在测试的时候删除部门成功了，删除部门下的员工信息却失败了，而没有同时失败呢？
回答：
- 原因是在MySQL数据库当中，事务默认是自动提交的，也就是说，当我们在执行一条DML 语句的时候，一旦这条DML语句执行完成，MySQL当中的事务会立刻提交。
- 这也就意味着上面的这条SQL语句是一个事务，下面的这条SQL语句又是一个事务，即每一条DML语句都是一个单独的事务，也就是说，在我们刚才执行解散部门这个业务操作的时候其实它是两个事务，第一条delete语句它是一个事务，第二条delete语句它又是一个事务，所以就出现问题了。
如何解决？
- 需要将这两步操作控制在一个事务的范围内，此时就涉及到数据库当中的事务控制。
1.2 操作
我们可以在业务操作执行开始之前执行一个指令：start transaction；或者是begin； 来开启一个事务 。
当这一组操作当中，所有的操作都执行成功之后，我们再来提交事务。
- 提交事务就是把我们这次所执行的这些操作真正的提交到数据库来修改表结构当中的数据。
最后一步就是回滚事务，当我们这组操作当中有任何一个操作失败，此时就需要回滚事务。
- 回滚事务就是把所有的数据恢复到操作之前的样子。
- 操作事务的三个步骤
- 开启事务： 记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同 时失败
- 执行sql语句： 执行具体的一条或多条sql语句
- 提交： 没出现问题，数据进行更新
- 回滚： 出现问题，数据恢复到开启事务时的状态- 结束事务(提交|回滚)
1.2.1 事务的提交方式(MYSQL中有两种方式进行事务的操作)：
- 自动提交事务：即执行一条sql语句就提交一次事务。（默认MySQL的事务是自动提交）
- 手动提交事务：先开启，再提交。
查看 / 查询事务提交方式：
- SELECT @@AUTOCOMMIT；
- 这两个@@符号相当于是一个全局的系统变量，这个变量就是用来记录事务的提交方式的。
- 查询出来的这个事务的提交方式，会有两个数字来进行记录，0代表的是手动提交，1代表的是自动提交。
- 当然我们也可以去修改事务的提交方式。
修改事务提交方式：
- SET @@AUTOCOMMIT=数字；
/*
查询事务提交方式:SELECT @@AUTOCOMMIT; -- 1代表自动提交 0代表手动提交
修改事务提交方式:SET @@AUTOCOMMIT=数字;
*/
-- 查询事务的提交方式
SELECT @@autocommit;
-- 修改事务的提交方式
SET @@autocommit = 0;
事务操作有关的SQL语句：
| SQL语句 | 描述 |
|---|---|
| start transaction; / begin ; | 开启手动控制事务 |
| commit; | 提交事务 |
| rollback; | 回滚事务 |
手动提交事务使用步骤：
- 第1种情况：开启事务 => 执行SQL语句 => 成功 => 提交事务
- 第2种情况：开启事务 => 执行SQL语句 => 失败 => 回滚事务
-- =======================事务控制========================
-- 在该窗口开启了一个事务,只要这个事务还没有提交,在其它窗口当中是看不到我们所操作的数据的,
-- 因为其它窗口也相当于是一个事务,它们两者之间是相互隔离的。
-- commit是所有操作都成功执行之后才来执行的提交事务,只要有一个操作执行失败,
-- 我们要执行的是rollback回滚事务。
-- 一旦出现异常,我们就可以通过rollback回滚事务将删除掉的数据再恢复回来,
-- 从而保证在操作前后数据是一致的。
-- 这就是事务,要么同时成功,要么同时失败。
-- 开启事务
start transaction;
-- 删除学工部
delete from tb_dept where id = 1;
-- 删除学工部下的员工
delete from tb_emp where dept_id = 1;
-- 上述的这组SQL语句，如果如果执行成功，则提交事务
-- 提交事务(成功时执行)
commit;
-- 上述的这组SQL语句，如果如果执行失败，则回滚事务
-- 回滚事务(出错时执行)
rollback;
select * from tb_dept;
select * from tb_emp;
1.3 事务的四大特性 / 特征 --- ACID(重点掌握)
原子性： 原子指的就是不可再分的，事务是一个不可分割的最小操作单元，这个操作单元当中的 操作要么全部成功，要么全部失败。
一致性： 一致性指的是事务完成时必须使所有的数据都保持一致状态，不论是事务提交还是事务 回滚都算事务完成了，只要事务完成了，所有的数据都保持一致状态。
隔离性： 隔离性指的是数据库当中所提供的隔离机制，保证事务在不受外部并发操作的影响下独 立运行。
-
比如：在该窗口开启了一个事务,只要这个事务还没有提交,在其它窗口当中是看不到我们所操作的数据的,因为其它窗口也相当于是一个事务,它们两者之间是相互隔离的，这就是隔离性的体现。 - 隔离性我们是可以通过隔离级别去设置的，隔离性越高，事务越安全，但是效率也就越低，换隔离性我们一般不会手动去控制。
持久性： 事务一旦提交或回滚，它对数据库当中数据的改变就是永久的。
- 举例：如果说你把一个事务提交了或者是回滚了，接下来你把数据库关了，下一次你再打 开，数据依然是提交以及回滚之后的数据，对数据的改变是永久的。
事务的四大特性简称为：ACID
- 原子性（Atomicity） ： 原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败，不会存在部分完成的情况。如果有任何一个操作失败，事务会被回滚到开始前的状态，所有修改过的数据都会被撤销。因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
- 一致性（Consistency）： 一个事务完成之后数据都必须处于一致性状态。
如果事务成功的完成，那么数据库的所有变化将生效。
如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。
- 隔离性（Isolation）： 多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离，不会产生互相干扰的情况，保证每个事务独立执行。
一个事务的成功或者失败对于其他的事务是没有影响。
- 持久性（Durability）： 一个事务一旦被提交或回滚，它对数据库中的数据的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。
总结： 事务的四大特性保证了数据库操作过程的可靠性、一致性和持久性，是保证数据库完整性和正确性的重要手段。
事务总结：
1.4 并发事务问题
- 事务在操作时的理想状态： 多个客户端操作时，各个客户端的事务之间应该是隔离的，相互独立的，不受影响的。
- 而如果多个事务操作同一批数据时，就会产生不同的问题，因为并发操作，多个用户同时访问同一个数据，可能引发并发访问的问题：
问题 现象 脏读 是指在一个事务处理过程中读取了另一个事务中尚未提交的数据 , 导致两次查询结果不一致 不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。 幻读 select 查询记录不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入；或不存在执行delete删除，却发现删除成功，好像出现了"幻影"。
1). 脏读： 一个事务读到另外一个事务还没有提交的数据。
比如B读取到了A未提交的数据。
2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
事务A两次读取同一条记录，但是读取到的数据却是不一样的。
3). 幻读： 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数 据已经存在，好像出现了 " 幻影 " 。
- 我们需要设置不同的隔离级别来解决这些问题。
1.5 事务的隔离级别
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别 。主要有以下几种：
注意：事务隔离级别越高，数据越安全，但是性能越低。
隔离级别分类
- MySQL 数据库有四种隔离级别
- 上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。
1.6 MySQL 事务隔离级别相关的命令
查询全局事务隔离级别 / 查询数据库隔离级别
- 老版本MySQL：SELECT @@TX_ISOLATION；
- MySQL8：SELECT @@transaction_isolation
修改数据库隔离级别
- SET SESSION - 当前窗口或会话/GLOBAL - 全局或所有的窗口 TRANSACTION ISOLATION LEVEL 级别字符串；
- 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
/*
查询隔离级别:SELECT @@TX_ISOLATION;
修改隔离级别:SET GLOBAL TRANSACTION LEVEL 级别字符串;
*/
-- 查询事务隔离级别
-- 老版本
SELECT @@tx_isolation;
-- MySQL8
SELECT @@transaction_isolation;
-- 修改事务隔离级别(修改后需要重新连接,也就是关闭后重新打开DataGrip即可)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
1.7 事务隔离级别问题演示
1. 脏读的问题演示和解决
打开两个窗口，分别代表两个事务。
- 窗口1
/*
脏读的问题演示和解决
脏读:一个事务中读取到了其它事务未提交的数据
*/
-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;
-- 开启事务
start transaction;
-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;
-- 查询账户表
select * from account;
-- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)
-- 窗口2查看转账结果后，执行回滚
rollback;
- 窗口2
-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;
-- 查询隔离级别
select @@transaction_isolation;
-- 开启事务
start transaction;
-- 查询账户表
select * from account;
此时，在事务二里面查询到了事务一还没有提交的数据，此时就出现了脏读问题。
脏读这种问题会造成什么样的影响呢？
- 比如，李四找到张三说你来借我500块钱，此时张三开始转账了，转完帐以后告诉李四说钱我已经给你转过去了，你来查询以下，这个时候，李四一查询确实这个钱已经到账了，然后张三就说了，亲兄弟还得明算账，你给我打个500块的欠条，那这个时候李四就给他打了一个这样的欠条，这样的话在法律意义上就已经生效了。
- 张三拿到这个欠条以后，他执行了一个回滚的操作，我们知道回滚的操作是把我们的数据还原到事务最开始的状态，回滚完成后，数据又还原回去了。
- 然后李四过了一阵想去取钱，取1500块，但是银行告诉他余额不足，这个时候，李四再去查询，发现他卡里余额变成了原来的1000块钱，那这个时候，李四就比较吃亏了，钱没收到，而且还给张三写了一张500块钱的欠条，这就是脏读所带来的一个问题。
-- 解决脏读:修改事务的隔离级别为read committed
set global transaction isolation level read committed;
/*
脏读的问题演示和解决
脏读:一个事务中读取到了其它事务未提交的数据
*/
-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;
-- 解决脏读:修改事务的隔离级别为read committed
set global transaction isolation level read committed;
-- 查询隔离级别
select @@transaction_isolation;
-- 开启事务
start transaction;
-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;
-- 查询账户表
select * from account;
-- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)
-- 窗口2查看转账结果后，执行回滚
rollback;
-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;
-- 查询隔离级别
select @@transaction_isolation;
-- 开启事务
start transaction;
-- 查询账户表
select * from account;
-- 提交事务
commit;
2. 不可重复读的问题演示和解决
- 窗口1
/*
不可重复读的问题演示和解决
不可重复读:一个事务中读取到了其它事务修改并已提交的数据,导致两次查询结果不一致
*/
-- 设置隔离级别为read committed
set global transaction isolation level read committed;
-- 查询隔离级别
select @@transaction_isolation;
-- 开启事务
start transaction;
-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;
-- 查询账户表
select * from account;
-- 窗口2查看转账结果，并没有发生变化(脏读问题被解决了)
-- 执行提交事务。
commit;
-- 窗口2查看转账结果，数据发生了变化(出现了不可重复读的问题，读取到其他事务已提交的数据)
- 窗口2
-- 查询隔离级别
select @@transaction_isolation;
-- 开启事务
start transaction;
-- 查询账户表
select * from account;
-- 提交事务
commit;
不可重复读会带来什么问题呢？
- 不可重复读的问题：在一个事务当中读取到了另外一个事务已经提交的数据，那么就会造成两次查询的结果是不一样的。
- 大家会有疑问，这种现象不是很正常吗？
- 你窗口1没提交事务之前，我窗口二是不能查询的，窗口1提交事务以后，那么窗口2就可以查询出来数据了，这不是一个很正常的逻辑吗？
- 注意，不可重复读也是带有一定问题的。
- 比如说公司的老板找到小王，说想让小王去查询一下当年度的一个本公司的营收情况，这个时候小王在上午的时候查询出来的数据，比如说是100万，然后等到下午去开会了，那么小王就会把100万的这个数字告诉给老板，但是在下午的时候这一时间段当中，这个数据又变化了，比如说变成了80万，那这个时候小王告诉给老板的是100万，而老板自己查询出来数据是80万，那这个时候老板可能就会对小王产生一些信任的问题，那也就是说在我们多次查询的数据是不一样的。
- 我们该如何解决不可重复读这个问题呢？
- 这个时候同样我们也需要去修改事务的隔离级别。
-- 修改事务的隔离级别解决不可重复读的问题:repeatable read
set global transaction isolation level repeatable read;
- 此时，不可重复读的问题就已经解决掉了。
- 这就相当于，这个小王想去找财务部来查询这个营收数据，这个时候财务部说我们目前还在进行核算，目前没有办法告诉你一个准确的数据，等我们这边核算完成以后，那么会把这个数据告诉你以及老板。这样，就能够保证小王和老板所查看到的数据都是相同的。所以说，目前这个不可重复读的问题就已经被解决掉了。
- 在窗口1把数据修改完成后并且已经提交，这个时候在窗口2如果想查看修改之后的数据，我们需要在窗口2里面也来去提交事务，提交完成后再去查询表数据，此时就可以查看到真正修改之后的数据，这样，不可重复读的问题也被解决掉了。
3. 幻读的问题演示和解决
- 窗口1
/*
幻读的问题演示和解决
查询某记录是否存在,不存在
准备插入此纪录，单执行插入时发现此纪录已存在,无法插入
或某记录不存在执行删除,却发现删除成功
*/
-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;
-- 查询隔离级别
select @@transaction_isolation;
-- 开启事务
start transaction;
-- 添加一条记录
INSERT INTO account VALUES (3,'王五',1500);
-- 查询账户表，本窗口可以查看到id为3的结果
SELECT * FROM account;
-- 提交事务
COMMIT;
- 窗口2
-- 查询隔离级别
select @@transaction_isolation;
-- 开启事务
start transaction;
-- 查询账户表，查询不到新添加的id为3的记录
select * from account;
-- 添加id为3的一条数据，发现添加失败。出现了幻读
INSERT INTO account VALUES (3,'测试',200);
-- 提交事务
COMMIT;
-- 查询账户表，查询到了新添加的id为3的记录
select * from account;
- 窗口2查询没有第三条数据，但是添加失败不成功，好像出现了幻觉。
- 此时窗口2也来提交事务，再次查询表数据又查询到了id为3的记录
-- 解决幻读问题:修改事务的隔离级别为serializable串行化
set global transaction isolation level serializable;
- 此时，窗口1只有提交事务，才可以在窗口2当中也正常查询到表数据。
- 如果窗口1不提交事务，那么窗口2在查询的时候会一直处于卡死的状态，因为serialization相当于是一个锁表的操作，相当于把整个表给锁起来了，也就是数据表被锁住，在当前事务当中如果没有去提交事务，也就是结束事务的话，在其它事务里面什么操作都做不了，不管是增删改查都做不了。
- 只有当窗口1立马提交事务之后，才能在窗口2查询到表数据。
事务隔离级别小结：
| 隔离级别 | 名称 | 出现脏读 | 出现不可重复读 | 出现幻读 | 数据库默认隔离级别 | |
|---|---|---|---|---|---|---|
| 1 | read uncommitted | 读未提交 | 是 | 是 | 是 | |
| 2 | read committed | 读已提交 | 否 | 是 | 是 | Oracle / SQL Server |
| 3 | repeatable read | 可重复读 | 否 | 否 | 是 | MySQL |
| 4 | serializable | 串行化 | 否 | 否 | 否 |
注意： 隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别，不建议修改数据库默认的隔离级别。
事务的总结
-
一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作
-
开启事务：start transaction;
-
回滚事务：rollback;
-
提交事务：commit;
-
事务四大特征
- 原子性
- 持久性
- 隔离性
- 一致性
-
事务的隔离级别
- read uncommitted(读未提交)
- read committed (读已提交)
- repeatable read (可重复读)
- serializable (串行化)
到此已经学习了MySQL数据库的设计，数据库的操作部分，包括单表的设计，单表的增删改查，以及多表的设计，多表的增删改查操作。
接下来学习最后一个部分数据库的优化 --- 提升查询效率最有效的方式 ---- 索引。
2. 索引
-- ================================索引==========================
-- 一旦某一张表,它的数据量比较大,那么它的查询效率就会降低
-- 数据量越大,查询效率越低
导学：什么是索引，索引的数据结构，索引的具体操作语法，以及索引的具体操作
2.1 索引概念版
- 我们之前学习过集合，其中的ArrayList集合的特点之一就是有索引。那么有索引会带来哪些好处呢？
- 没错，查询数据快！ 我们可以通过索引来快速查找到想要的数据。那么对于我们的MySQL数据库中的索引功能也是类似的！
- **MySQL数据库中的索引：是帮助MySQL高效获取数据的一种排好序的数据结构！ 所以，索引的本质就是数据结构，作用是用来高效获取数据结构。
- 在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
- 一张数据表，用于保存数据。 一个索引配置文件，用于保存索引，每个索引都去指向了某一个数据(表格演示)
- 举例，无索引和有索引的查找原理
概念：
索引(index)：是帮助 数据库 高效获取数据的 数据结构。
索引它的作用就是来高效获取数据的，换句话说索引就是来提高查询效率的，而且它的本质是一种数据结构，简单来讲，就是使用索引可以提高查询的效率。
测试没有使用索引的查询：
添加索引后查询：
为该字段来构建索引，虽然这个过程是比较耗时的，但是这是一次性的操作。
-- 添加索引
create index idx_sku_sn on tb_sku (sn); #在添加索引时，也需要消耗时间
-- 查询数据（使用了索引）
select * from tb_sku where sn = '100000003145008';
提问：在数据库当中没有索引的情况下它是如何查询的，有索引的情况下它又是如何查询的？
通过索引是如何来提高查询效率的？
- 在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。
在没有索引的情况下，它就需要从第一条记录开始进行匹配查找，首先判断第一个值是不是我们要查找的，如果不是，再去找第二个，如果还不是，再去找第三个，继续往后找直到找到要查找的值。
提问：如果已经找到要查找的值了，还要不要继续往后找呢？
回答：如果要查找的该字段它不是唯一的，那我们还需要继续往后找，直到找到最后的一条记录，这就是没有索引的情况下，这种情况我们也称之为全表扫描。全表扫描的性能是非常低的，而且数据量越大，性能越低。
说明：上图所演示的二叉搜索树它仅仅是一个示意图，并不是真正的MySQL底层的索引结构。
如果我们为该字段建立了一个索引，它就会维护一个索引对应的数据结构，而这个数据结构，会大大的提高查询的效率。
提问： 如果让你来实现这个索引，你会选择什么样的数据结构来实现索引呢？
注意：索引是用来提高查询效率的。
回答： 用树形结构来实现索引，比如我们最为常见的二叉搜索树 / 二叉查找树
提问： 以二叉搜索树为例，通过这样一个树形结构是如何来提高查询效率的？
在构建这个树形结构的时候，每一个结点都会去关联它的原始的数据记录，在二叉树当中，一个节点下面最多只能有两个子节点，二叉搜索树它的左子树上所有节点的值是要小于根节点的，而右子树上所有节点的值是要大于根节点的。
提问：有了二叉搜索树之后，现在我们要查询数据它又是如何查找的？
回答： 如果构建了二叉查找树这样一个树形结构之后，接下来再进行查询的时候，就不用再去遍历整张表进行查询了，直接拿要查找的值与根节点的值进行比较，如果比根节点的值小，往左子树查找，如果大于根节点的值，往右子树查找。找到该节点后，又由于每一个节点又和原始的数据记录关联着，因此最终也就找到了原始的数据记录，这就是有索引的情况，在有索引的情况下进行数据记录的查询，就不用进行整表扫描了，我们只需要在索引结构上进行几次对比，就可以找到对应的数据，而且这个过程是非常高效的，查询效率会得到很大幅度的提升。
其实这个索引就类似于是一本书当中的目录，如果这本书当中没有目录，我们要查找某一章节的内容，就需要从第一页一直往后翻，直到找到这一章节的内容，类似于是一个全书扫描。
如果说我给这本书籍建立一个目录，那接下来我要查找某一章节的内容，我只需要去看一眼这个目录，我就知道这一章节的内容在第几页，那我就可以精准的定位到这一页的内容。
索引的优缺点：
优点：
- 提高数据查询的效率，降低数据库的磁盘IO成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。
缺点：
- 索引会占用存储 / 磁盘空间。
- 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。
优点：索引可以提升数据库查询的效率以及排序的效率。
缺点：索引会占用磁盘空间。索引是一种数据结构，这种数据结构最终是要存储在磁盘文件当中 的。data目录下存放的就是MySQL的数据文件。
索引虽然可以大大的提高查询的效率，但是同时却降低了增删改的效率。
因为在进行增删改操作时，数据发生变化了，此时就需要去重新维护这个索引的数据结 构，所以，我们在进行增删改的时候，除了需要去操作数据本身以外，还需要来维护这个 索引结构，那就会降低增删改的效率。
注意：在优点面前，索引的两个缺点可以忽略不计，因为对于现在的企业服务器来说，磁盘空间都是拿T来计算的，而且索引本身占用的磁盘空间也并不会太大。对于第二点降低增删改的效率就更不用担心了，因为对于一个正常的业务系统来说，查询会占到90%以上，增删改的频次本来就已经很低了，所以我们在进行业务操作时，主要考虑的是查询性能的优化。
1024T = 1P
2.2 结构
MySQL当中索引的数据结构
MySQL数据库当中支持的索引结构比较多，比如像：
Hash索引 --- 哈希索引 B+Tree索引 --- B+树索引 Full-Text --- 全文索引 等....
我们平时所说的索引，如果没有特别指明，都指的是MySQL当中默认的B+Tree索引结构 / 结构组织的索引。
在没有了解B+Tree结构前，我们先回顾下之前所学习的树结构：
- 二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大
- 在二叉搜索树当中，左子树上所有节点的值都是小于根节点的值，右子树上所有节点的值都是大于根节点的值，而且根节点的左右两个子树又都是一个二叉搜索树。
- 而如果是一个二叉搜索树，又很容易出现一个问题，那就是偏向一边的情况 --- 瘸子现象。
二叉查找树 / 二叉搜索树
- 当我们在向二叉查找树保存数据的时候，如果是根据数据的从大到小或者是从小到大的顺序来保存的，那此时就会将二叉搜索树退化成这样一个单向链表，此时，它的搜索性能就会大打折扣。
- 当我们向二叉查找树保存数据时，是按照从大到小(或从小到大)的顺序保存的，此时就会形成一个单向链表，搜索性能会打折扣。
瘸子现象 --- 单向链表
这个时候，我们就可以选择平衡二叉树或者红黑树来解决这个问题，因为红黑树也是一颗平衡的二叉树。
红黑树
但是，在MySQL数据库当中，并没有采用二叉搜索树或者是平衡二叉树或者红黑树来作为索引的数据结构。
思考 / 提问： 如果采用二叉搜索树或者是红黑树来作为索引的结构，有什么问题呢？
回答：
- 最大的问题就是大数据量的情况下，层级较深，检索速度比较慢。因为不管是二叉搜索树还是红黑树，它一个节点下面只能有两个子节点，此时，在大数据量的情况下就会造成树的高度比较高，树的高度一旦高了，检索速度就会降低。
- 说明：如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO，那么100万用户，那么会造成效率极其低下。所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存一个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如B+Tree就满足。
2.2.3 B-Tree
知识小贴士: 树的度数指的是一个节点的子节点个数。 非叶子节点也被称为冗余节点！
2.2.4 B+Tree
B+Tree是B-Tree的变种
标准的B+Tree的数据结构
我们可以看到，两部分：
- 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
- 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的 B+Tree。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。
MySQL中的B+Tree的索引结构图(不是标准的，看下图)
2.2.5 Hash
MySQL 中除了支持 B+Tree 索引，还支持一种索引类型 ---Hash 索引。
1). 结构
- 哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个 ( 或多个 ) 键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表来解决。
2). 特点
- A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
- B. 无法利用索引完成排序操作
- C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于 B+tree索引
3). 存储引擎支持
- 在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
思考题： 为什么InnoDB存储引擎选择使用B+tree索引结构?
- A. 相对于二叉树，层级更少，搜索效率高；
- B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
- C. 相对Hash索引，B+tree支持范围匹配及排序操作；
接下来，我们就来看一下在B+Tree结构当中，它又是如何来规避这个问题的？
B+Tree也叫多路平衡搜索树，首先，它是一颗平衡的树，而且它还是一颗多路平衡搜索树，那什么是多路呢？
B+Tree的索引结构图：
MySQL中的B+Tree的索引结构图
MySQL的默认存储引擎是InnoDB，它采用的是B+树结构的索引。****
B+Tree结构：
-
每一个节点，可以存储多个key（有n个key，就有n个指针）
-
节点分为：叶子节点、非叶子节点
- 叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上，只有叶子节点才会存储数据，叶子节点之间使用双向指针连接
- 非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针
-
为了提高范围查询效率，最底层的叶子节点形成了一个双向有序链表，便于数据的排序及区间范围查询
- 在B+Tree当中，每一个节点是可以存储多个Key的，不像二叉树，二叉树当中一个节点只能存储一个Key，而且在B+Tree每一个节点当中，存储了N个Key就会有N个指针，如图，P1、P2、P3其实就是三个指针，这三个指针分别用来指向下一个磁盘块，黄色的区域我们就叫一个磁盘块，这个在数据库当中也叫页，页是数据库进行磁盘管理的最小单位，一个页它的大小是16KB。
- 这是B+Tree的第一个特点：在一个节点当中可以存储多个Key，有N个Key就会有N个指针，也就意味着这个节点会有N个子节点，多个子节点也就是我们提到的多路。
- 第二个特点：在B+Tree当中，上面的这些节点我们都称之为非叶子节点，下面的这层我们称之为叶子节点。B+Tree当中的非叶子节点它仅仅起到索引数据、查找数据的作用，它并不保存具体的数据，所有的数据都是在叶子节点这个部分来保存的，而且所有的Key也都会出现在叶子节点。
- 比如，在根节点当中有6这个Key，6这个Key在叶子节点当中也会有，在根节点当中还有38这个Key，38这个Key在叶子节点当中也存在。在根节点当中还有一个Key是67，67在叶子节点当中也有。也就是说，所有的Key都会在叶子节点出现，而且，在叶子节点当中会保存这个Key对应的数据，下面绿色的部分就是对应的数据。这是B+Tree的第二个特点。
- B+Tree的第三个特点：由于B+Tree它是一颗多路平衡搜索树，所以它的叶子节点元素是按照元素的从小到大的顺序来排序的，而且在叶子节点形成了一个双向链表，我们由上一个元素可以找到下一个元素，由下一个元素也可以找到上一个元素，这是B+Tree它的索引结构它的一个特点。
提问：在B+Tree这个索引结构当中，我们进行数据的查询，它是怎么样进行查找的?
回答：首先它会从根节点开始对比，在根节点当中进行对比的时候，它内部会采用二分查找的方 式进行操作。
对于MySQL当中的B+Tree索引，重点关注它的三个特点：
- 在B+Tree当中，每一个节点是可以存储多个Key的，存储了N个Key就会有N个指针，有N个指针也就意味着这个节点下面可以有多个子节点，相对于红黑树或者是平衡二叉树而言，相同数据量的情况下，它的树的高度就要低很多，所以MySQL当中的B+Tree索引最终形成的其实是一个矮胖矮胖的树，树的层级一般可能在三到四层。
- 在B+Tree的索引结构当中，所有的数据都是存储在叶子节点的，非叶子节点仅仅起到索引数据的作用，它并不存储数据，而且所有的Key都会出现在叶子节点。我们不管查找哪一条数据，最终都要找到叶子节点才能拿到对应的数据，这样就能够保证数据库服务器它的查询性能比较稳定。
- 叶子节点当中的这些元素它形成了一个双向链表，而且是有序的，便于数据的排序及区间范围查询。
B+Tree的索引结构图
拓展：键值指的就是键的值！
思考题： InnoDB主键索引的B+Tree高度为多高呢?三层索引的B+Tree，能支撑多大 的数据量？
- B+Tree是InnoDB存储引擎使用的索引结构，我们都知道，随着表中数据量的增加，B+Tree的高度会逐渐增加，如果B+Tree的高度过高，每次查询时需要经过较多的层级，会导致查询性能降低。
- 在一棵索引树里面，能存数据量的大小由很多因素来决定的，数据行大小、索引大小、Page页的大小。
- Page页是存储行数据的实际物理空间，是InnoDB存储引擎中用于存储数据的基本单位，以页为单位进行磁盘读写操作，它是磁盘上的一块儿连续区域，默认大小为16KB， B+Tree的每个节点都对应这一个Page页，包括根节点、非叶子节点和叶子节点，B+Tree通过节点之间的指针连接了不同层级的数据页，从而构建了一个有序的索引结构。
- Page页是内存跟磁盘交互的最小单位，一次磁盘到内存的读取最小是16KB，一次内存到磁盘的持久化也是最小16KB。
- 一行数据大小为1k，一Page数据页大小为16K，因此一页当中可以存储16行这样的数据
- InnoDB的指针占用6个字节的空间，主键即使为bigint，对应Java中的Long类型，占用字节数为8
- 16K = 16384个字节
B+Tree的叶子节点包含实际的数据行，每个数据行存储在一个数据页中。
B+Tree的非叶子节点都是由key主键{键值}+指针(指向其它数据页的指针)组成的，一个key占8字节，一个指针默认占6字节，而一个节点总共容量是16KB，那么可以计算出一个节点可以存储的元素个数：
- 16*1024字节 / (8+6)=1170个元素。
- 查看mysql索引节点大小：show global status like 'innodb_page_size'; -- 节点大小：16384
当根节点中可以存储1170个元素，意味着根节点可以扩展出1170个Page页或子节点，每个子节点或每个Page页也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：1170*1170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据，每个数据又对应着第三层的一个Page页。
对于第二层每个元素有指针，那么会找到第三层，第三层由key+数据组成，假设key+数据总大小是1KB(也就是一行数据，即一条/个数据的存储空间是1KB) ，而每个节点一共能存储16KB，所以一个第三层一个节点大概可以存储16个元素(即16条记录) 。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W*16结果就是21，902，400，即2000W+的元素个数，即三层高度的B+Tree最终可存储数据量为2000W+！
如果数据量超过1000多w，就要考虑分表了。
注意：上面指的是树的高度为3，如果树的高度为2 ，则：
1170 * 16 = 18720，也就是说，如果树的高度为2，则可以存储 18000 多条记录。
总结：一般为2-3层，一个三层的B+Tree索引结果就可以容纳两千万左右的数据。
结合上述分析B+Tree有如下优点：
- 千万条数据，B+Tree可以控制在小于等于3的高度
- 所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找
MySQL为什么要用B+Tree索引而不是用BTree索引？
MySQL使用B+Tree索引而不是BTree索引的原因是B+Tree索引更适合磁盘存储。
- 从磁盘I/O效率方面来说：B+Tree索引将所有数据都存储在叶子节点中，而非叶子节点只存储索引信息而不存储数据，所以树的每一层就能够存储更多的索引数量，也就是说，B+树在层高相同的情况下，比B树存储的数据量更多，这样可以间接减少磁盘I/O操作的次数，提高查询效率。 B树的所有节点既存放键(key)也存放数据(data)
- 从范围查询效率方面来说：B+树将所有存储在叶子节点当中的数据使用了双向有序链表的方式来进行关联，以此提高范围查询和顺序遍历的效率，便于数据的排序操作{order by}。B树的叶子节点都是独立的
- 从全表扫描方面来看：如果我们要对表进行全表扫描，因为B+树的叶子节点存储所有数据，所以只需要遍历/扫描叶子节点就可以了，不需要遍历整颗B+Tree，而B树需要遍历整个树。
- 另外，B+Tree索引还支持范围查询和排序操作，这些操作在BTree索引中效率较低。因此，MySQL选择使用B+Tree索引作为默认的索引类型。
2.3 索引的操作语法
索引的操作语法，主要包括三个部分：创建索引、查看索引和删除索引。
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
通过on关键字来指定我们要为哪一张表当中的哪一个字段来建立索引，我们也可以为多个字段建立一个索引，多个字段之间使用逗号分隔。
如果我们要创建的是一个唯一索引，我们可以在create后面加上一个关键字unique，但这个unique并不是必须的，如果创建的是唯一索引才需要添加。
索引名的命名规范：idx_表名_字段名
- idx：index的简写
查看 / 查询索引
show index from 表名;
查看索引：通过show index from后面指定表名，这就代表我们要查看这张表当中所有的索引信 息。
删除索引
drop index 索引名 on 表名;
删除索引：drop index 索引名 on 表名，代表我们要删除这张表当中的这个索引。
-- ==========================MySQL --- 索引 --- 操作语法=======================
-- 创建:为 tb_emp 表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
-- 查询:查询 tb_emp 表的索引信息
-- 代表我们要查看这张表当中所有的索引信息
-- 在创建表时，如果添加了主键和给某一个字段添加了唯一约束，数据库就会默认创建：主键索引、唯一索引(UNIQUE INDEX)
-- 而且主键索引它的性能是最高的,主键索引是所有索引当中性能最高的 所以我们添加的唯一约束其本质就是添加了一个唯一索引
show index from tb_emp;
-- 删除:删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;
注意事项：
- 主键字段，在建表时，会自动创建主键索引
- 添加唯一约束时，数据库实际上会添加唯一索引
索引小结：
2.4 索引分类
从三个不同维度对索引分类：
例如从基本使用使用的角度来讲：
- 主键索引：针对于表中主键创建的索引，数据列不允许重复，不允许为NULL，默认自动创建，一个表只能有一个主键{索引}。关键字：PRIMARY
- 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。避免同一个表中某数据列中的值重复 关键字：UNIQUE
- 普通 / 常规索引：基本的索引类型，仅加速查询，没有唯一性的限制，允许为NULL值。
- 组合 / 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
- 全文索引：全文索引查找的是文本中的关键字，而不是比较索引中的值。一般不会使用，效率较低。 关键字：FULLTEXT
- 覆盖索引：一个索引包含或者说覆盖所有需要查询的字段的值。
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
- InnoDB 中的主键索引就属于聚簇索引，聚簇索引也是B+Tree结构。
- 聚簇索引在InnoDB存储的表结构当中，必须有，而且只能有一个； 如果有多个，则会存储多份行数据造成重复。
聚集索引选取规则:
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个非空的唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
按照底层存储方式角度划分：聚簇索引和非聚簇索引 / 辅助索引 / 二级索引
- 聚簇索引和非聚簇索引是数据库中常用的两种索引类型。
- 聚簇索引是将数据按照索引的顺序存储在磁盘上，同一索引的数据存储在相邻的磁盘块中，可以提高查询效率。
- 非聚簇索引是将索引和数据分开存储在磁盘上，索引存储在一个地方，数据存储在另一个地方，查询时需要进行两次I/O操作，效率较低。
- 聚簇索引适用于经常需要按照某个字段进行查询的表，非聚簇索引适用于经常需要进行范围查询的表。
聚集索引和二级索引 / 辅助索引 / 非聚簇索引 的具体结构如下：
- 根据主键建的索引叫聚簇索引，一张表当中不能没有聚簇索引，并且有仅只有一个聚簇索引。
- 根据字段建的索引叫二级索引/辅助索引/非聚簇索引
- 聚集索引的叶子节点下挂的是这一行的数据 。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。
具体过程如下：
- 由于是根据name字段进行查询，所以先根据字段值name='Arm'到name字段的二级索引中进行匹配查找，因为二级索引就是根据字段建立的索引，但是在二级索引中只能查找到字段 Arm 对应的主键值 10。
- 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
- 最终拿到这一行的数据，直接返回即可。
回表查询：
-
这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
思考题：
以下两条SQL语句，那个执行效率高? 为什么?
- A. select * from user where id = 10 ;
- B. select * from user where name = 'Arm' ;
备注 : id 为主键， name 字段创建的有索引；
解答：
- A 语句的执行性能要高于B 语句。
- 因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然 后再查询聚集索引，也就是需要进行回表查询。
非聚簇索引一定回表查询吗(覆盖索引)?
- 非聚簇索引不一定回表查询。
- 如果 SQL 查的就是主键呢?
SELECT id FROM table WHERE id=1;
主键索引本身的 key 就是主键，查到返回就行了，无需回表查询。这种情况就称之为覆盖索引了。