MySQL数据库索引？索引的数据结构？什么是事务？

Author:老九
计算机专业
可控之事 沉重冷静 不可控之事 乐观面对
85180586@qq.com
😄 😆 😵 😭 😰 😅 😢 😤 😍 ☺️ 😎 😩

👍 👎 💯 👏 🔔 🎁 ❓ 💣 ❤️ ☕️ 🌀 🙇 💋 🙏 💦 💩 ❗️ 💢
————————————————
版权声明：本文为CSDN博主「浦上青天」的原创文章

知识点

索引

概念

• 索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或者多列创建索引，并指定索引的类型。

作用

1.索引的主要作用就是进行查找，提高查找效率。查找效率提高了，但是同时也会付出代价。
2.数据库的索引，也是需要消耗一些额外存储空间的，数据量越大，消耗的额外空间就越多。
3.索引确定之后，后续每次对内容进行增删改的时候，往往也需要同步的调整索引的结构。

索引带来的好处：提高了查找速度。
索引带来的坏处：占用过多的空间，拖慢了增删改的速度。

使用场景

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

1. 数据量较大，且经常对这些列进行条件查询
2. 该数据库表的插入操作，及对这些列的修改操作频率较低
3. 索引会占用额外的磁盘空间（用空间换取时间效率）

如何使用

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

查看索引

查看索引（查看一个表上有哪些索引）
show index from [表名]

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

创建索引

创建索引
这里就可以看到我们新创建的索引，不过要注意的是创建索引是一个非常低效的事情，尤其是表里面已经有很多数据的时候，针对服务器上的数据库，如果里面的表没有索引，就不要轻易去创建索引了。不然可能会导致数据库崩溃。
create index 索引名 on 表名(列名);

mysql> create index name_index on student(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引

删除索引
不过要注意的是，删除索引的时候，如果是数据量很大的数据库，也可能导致数据库崩溃，所以在创建、删除索引的时候，应该在数据库刚开始就调整。
drop index 索引名 on 表名

mysql> drop index name_index on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引的数据结构是什么？

MySQL索引详解

可以是二叉搜索树或AVL树或红黑树嘛？

不可以

• 二叉搜索树的平均查找效率是O（logN），有时还需要对二叉搜索树进行中序遍历O（N）
• 如果数据很多，二叉搜索树最多两个分支，所以树的深度会很大，高度高了对应着比较次数，对于数据库来说意味着访问磁盘IO，效率不高
• AVL树(平衡二叉树)：要求任意节点左右子树高度不超过1
• 红黑树：要求更宽松的平衡二叉树

可以是哈希表吗

不可以

1. 如果是处理”相等“的情况，哈希表很高效
2. 但是哈希表不可以处理“大于小于”，以及范围查找进行判定，因为哈希的查找是把key带入哈希函数得到下标，再根据下标取对应的链表，再去遍历链表比较key是否相等，无法进行范围查找

可以是B树(多叉搜索树)吗

本质可以，但是为了更高的效率不选它
B树与二叉树的差异

• 结点不是二叉而是N叉
• 结点不是存储一个数据，而是可能存储多个数据，减少了磁盘io的访问次数
• 在B树上查找就是一个N分查找，效率比二叉树高
• 由于每个结点存储多个数据，每个结点又有多个度，所以和二叉树相比，高度会低很多，查找效率会高，范围查找容易一些
• 因为叶子非叶子都是存储数据都要放到磁盘上，所以遇到较复杂的范围查找，需要读取磁盘次数较高
• 

B+树

真实的索引结构
与B树的差异
数据存到叶子节点上，叶子结点直接使用链表来连接
父节点的值，都会在子节点中体现
非叶子节点中的每个值，最终都会在叶子节点中体现

• 数据（表的一行记录，也称为载荷）只存在叶子节点上，非叶子节点只保存一些辅助查找的边界信息（key值），非叶子结点占用空间较少，甚至可以放在内存中
• 所有查询最终都会落在叶子节点上，每次查询的IO次数都是差不多的，查询速度稳定
• 不需要额外的进行中序遍历，遍历叶子节点就是中序结果，范围查找很高效
• 叶子放在磁盘上，非叶子放在内存中，减少了访问磁盘的次数，查找也就更高效了
  

事务

概念

• 事务的作用就是把若干个独立的操作给打包成一个整体，使其不能分开工作。这种就叫 原子性 。就是，要么不执行，要么全执行。转账尤其使用多。

如何使用

1. 开启事务：start transaction;
2. 执行多条SQL语句
3. 回滚或提交：rollback/commit 说明：rollback即全部失败，commit即全部成功

事务的特点

1. 原子性：事务中的若干个操作，要么全部执行成功，要么全部不执行（本质上不是全部不执行，是把已执行的步骤回滚回去，借助逆向操作，把原来操作造成的影响进行还原）
2. 一致性：执行事务前后，数据库始终处于一种合法的状态，就像转账之后，账户余额不能为负数。
3. 持久性：事务一旦执行完毕，此时对于数据库的修改是持久生效的（写到磁盘中了）
4. 隔离性：多个事务并发执行时，事务之间不能 相互干扰（线程安全问题）

隔离性

隔离性和并发是相悖的
隔离是为了保证数据的准确
并发是为了提高事务执行的效率
如果多个事务之间隔离性越强，并发程度就越低，效率就越低
如果多个事务之间隔离性越弱，并发程度就越高，效率就越高

介绍脏读，不可重复读，幻读

脏读(写的过程中不能读)

• 假如一个学校正在考试，学生甲在答题，学生已在偷瞄，此时甲写了一个答案还没有写完，然后乙就偷看了甲的答案然后写在了自己的试卷上，这时乙的读操作就造成了脏读
• 用事务上来说就是，事务A还没有完全提交，事务B就读取了数据，造成了脏读
• 如何解决脏读：
• 给写操作加锁，就是学生甲在答完这道题的时候，学生乙不可以看，直到学生甲写完知道题 学生乙才 可以看
• 事务上来说就是事务A完全提交之前，事务B取读数据就会阻塞，一直阻塞到A提交数据之后B 才能读取数据
• 引入写加锁 事务的并发程度就降低了，效率也降低了，隔离性提高了

不可重复读

• 还是刚刚哪个考试的例子 ，学生甲在写一道数学大题，学生乙偷瞄，学生乙偷瞄一眼后，赶快将答案写在了自己的卷子上，可是在乙写的时候，甲发现自己有更好的解法，就把答案全擦了，重新写了，等到乙再去看第二眼的时候，发现怎么完全不一样了，乙就陷入了迷惑之中，乙就造就了不可重复读的问题
• 解决方法
• 再给读加锁，就好比学生乙在偷瞄答案的时候甲是不可以更改的
• 在事务上说就是事务B在读数据的时候，事务A就不能写了，此时也就解决了不可重复读的问题
• 引入读加锁，事务的并发程度就更低了，效率也低了，隔离性就更加高了

幻读

• 我们给写和读加完锁之后解决了脏读和不可重复读的问题，但是还有幻读问题
• 幻读就好比 学生甲在写第二道大题，因为加了锁，所以学生乙不能读第二道大题，但是它不想浪费时间，所以读了第一道大题，不小心写在了第二道大题的位置，从此导致了幻读
• 事务上来说，同一个事务中，两次读到的结果集不一致，虽然读加锁了，读的时候不能改，但是可以增加或者删除别的类，从而导致的结果集仍然跟之前是不一样的
• 解决方案：
• 必须严格的进行串行化执行，就是我在读操作的时候，别人就不能写了。隔离性最高，并发性最低，数据最可靠，速度最慢。

如何理解隔离性

• 隔离性就是让多个事务并发执行的时候，事务之间不能相互干扰，本质上就是为了线程安全
• 所以为什么要引入隔离性呢？
  虽然隔离和并发是相悖的，但是在不同场景下，对于数据的准确性的要求不一样，就可以引入不同的隔离等级，尽可能的既提高并发性又可以保证准确性

MySQL的隔离级别

对隔离性的要求具体多高就使用哪个隔离级别（隔离高了，并发程度就低，数据可靠性就高，效率就低，反之）

read uncommitted

• 允许读取未提交的数据
• 隔离程度最低，并发最高，但会有脏读+不可重复读+幻读问题

read committed

• 只允许读取已经提交的数据，相当于给写加锁
• 隔离性提高了一些，并发性降低了一些，解决了脏读问题，但是会有不可重复读+幻读问题

repeatable read

• MySQL的默认隔离级别
• 相当于给 读和写 都加锁
• 隔离性更高了一些，并发性更低了，解决了脏读+不可重复读的问题，但是还有幻读问题

serializable

• 严格执行串行化执行
• 隔离性最高，并发性最低，解决了幻读问题

先赞后看，养成习惯！！！^ _ ^♥♥♥
每天都更新知识点哦！！！
码字不易，大家的支持就是我坚持下去的动力。点赞后不要忘记关注我哦！