【MySQL】MySQL的索引事务 | 索引的优缺点 | 使用 | 原理 | B+树 | 事务的特性 | 隔离级别

码农世界 2024-05-23 后端 61 次浏览 0个评论

文章目录

MySQL索引事务
- 一、索引
- - - - 1.概念：
      - 2.代价
      - 3.索引的使用
      - 1.查看索引
        2.创建索引
        3.删除索引
        4.索引的原理
        B树
        B+树
        B+树的优点：
        二、事务
        1.概念
        2.使用
        3.回滚的原理：
        4.事务的特性
        1.原子性：
        2.一致性：
        3.持久性：
        4.隔离性：
        1.脏读问题
        解决方法：给写操作加锁
        2.不可重复读
        解决方法：给读操作加锁
        3.幻读
        解决方式：
        事务的隔离级别
        1.read uncommitted 读未提交
        2.read committed 读已提交
        3.repeatable read 可重复读
        4.serializable 串行化
        MySQL索引事务
        
        一、索引
        
        1.概念：
        
        索引 index
        
        可以把索引看成这个博客的目录，可以通过查看目录来定位到要阅读的内容。
        
        数据库在使用select查询的时候的流程：
        
        1.先遍历表
        
        2.把当前的行带入到条件中，看条件是否成立
        
        3.条件成立，这行保留。不成立就跳过。
        
        如果表非常大，这样的遍历成本就会变高。至少是o(n)。数据库把数据存储在硬盘上，每次读取一个数据，都需要读取硬盘，读取硬盘的操作本身开销就很大。
        索引就是针对查询操作进行了优化。通过索引来加快查询的速度，避免针对表进行遍历。
        
        举个例子，我可以直接通过目录来查看这篇文章中介绍“事务”的内容。而不用一行一行来分辨出“事务”的内容。
        
        2.代价
        
        引入索引的目的是为了提高查询的速度，但是也会存在响应的代价。
        
        朋友们，万物都是相对的。有舍就有得。就如同网络的TCP传输一样，为了保证可靠传输，就不可避免的要牺牲效率。而索引在提高效率的同时，也要付出一定的代价。
        
        1.占用更多的空间。
        
        生成索引，需要一系列的数据结构，以及一系列的额外数据，来存储到硬盘空间中。
        
        2.可能会降低插入、修改、删除的速度。
        
        不仅要修改原本的记录，还要修改索引的内容。在实际开发中，查询的操作（读操作）的频率要远高于插入、删除、修改（写操作）的频率。
        
        所以，当需要频繁进行写操作、或者磁盘空间不足时，最好不要创建索引。
        
        3.索引的使用
        
        主键约束、unique、外键约束都是会自动生成索引。
        
        主键和unique都是唯一的，并且往往需要大量的查询
        
        外键约束，往往要频繁查询子表和父表的信息，来约束插入内容。
        
        mysql> create table student(id int primary key,name varchar(20));
        
        一个索引是针对一个列来使用的。
        只有针对这一列查询时，查询速度才会被索引优化。
        一张表可以有多个索引
        
        1.查看索引
        
        show index from 表名; mysql> show index from student;
        
        2.创建索引
        
        create index 索引名 on 表名（列名）; create index idx_student_name on student(name);
        
        创建索引也是一个危险操作。在创建索引的时候，要针对现有的数据，进行大规模的重新整理。所以创建索引，一般在创建表的时候就应该规划好。如果已经存在大量数据，就要谨慎选择创建索引。这种情况，可以在另一台部署mysql的服务器上重新建表并且创建好索引，然后导入数据时进行相应的速度控制。
        3.删除索引
        
        手动创建的索引可以进行删除，但是像主键、unique这样自动生成的索引不能删除。
        drop index 索引名 on 表名; mysql> drop index idx_student_name on student;
        
        4.索引的原理
        
        索引也是通过一定的数据结构来实现的。
        
        采用B+树（N叉搜索树）
        
        B树
        
        B树、 B-树（- 不是减号，而是“连接符”）
        
        每个节点的度都是不确定的。一个节点上保存N个key，就划分出N+1个区间。每个区间都可以衍生出子树。
        由于每个节点都是存储在一个硬盘区域中的，一可以从硬盘中读取整个节点（多个key）。然后再进行比较，去找哪个符合的区间节点。
        大大降低了树的高度
        同时，一个节点虽然可以保存N个key。当增加元素，达到一定规模时，就会触发节点的分裂。
        
        同理，当删除元素达到一定的数目，也会触发结点的合并。
        
        B树的节点可以存储多个key,大幅度降低了树的高度，进而降低了时间复杂度。同时，一次可以读取多个key，降低了硬盘读取文件的IO次数，进而降低了资源消耗。（硬盘读一次相当于内存读一万次）
        
        B+树
        
        B+树，是B树的改进。是针对数据库量身定做的。
        
        B+树同样也是一个N叉搜索树，一个节点上存在N个key，划分成N个区间（B树是N+1个区间）
        每个节点上N个key中，最后一个key,就相当于当前子树的最大值
        父节点上的每个key都会以最大值的身份在子节点的对应区间中存在。（key可能会重复出现）
        
        最终的叶子节点这一层，包含了整个树的数据全集。
        
        B+树会用链表这样的数据结构，把叶子节点串起来
        
        此时就便于完成数据集合的遍历
        
        并且也便于从数据集合中按照范围取出一个“子集”
        
        比如：当查询 id >=7 and id<=18 的数据时。从根节点出发，从上往下分别找到叶子节点5的位置和11的位置。此时可以直接取出5到11的子链表。这个子链表就是结果集合。
        
        B+树的优点：
        
        相对于B树、哈希表和红黑树来说
        
        1.B+树是一个N叉搜索树，树的高度是有限。能够降低IO次数
        
        2.非常擅长范围查询
        
        3.所有查询最终都在叶子节点上，查询和查询之间的时间开销是稳定的。
        
        4.由于叶子节点是全集，会把行数据只存储在叶子节点上。非叶子节点只是存储一个用来排序的key(比如只存id)
        
        数据库是按照行来组织数据的，创建索引的时候，是针对这一列进行创建。行中的数据的比较多的，相比之下id的数据比较少。叶子节点需要存储行数据，占用空间大。非叶子节点只需要存储id,占用空间小。
        
        所以，可以将不占多少空间的非叶子结点，缓存到内存中。这些非叶子结点还是存储在硬盘中。但是当进行查询时，就可以把非叶子加载到内存中。这样整体的比较过程就可以在内存中进行，进一步减少了IO的访问次数。
        
        以上的经典的B+树，具体的数据库，会有不同程度的优化。
        
        mysql的索引实现，不止有B+树这一种情况。
        
        “存储引擎”，是mysql当中的一个重要模块。存储引擎提供了很多版本的实现。当前最常用的存储引擎叫INNODB，这个引擎使用的是B+树。后续其他的存储引擎可能会采用别的数据结构进行存储。
        
        二、事务
        
        1.概念
        
        开发中，经常会涉及到一些场景，需要“一气呵成”的完成一些操作。
        
        比如：转账操作。
        
        account(name balance){ 张三 1000 李四 1000 } -- 如果张三给李四转500的过程中，程序、数据库崩溃了、机器断电了,导致没有给李四加上500.导致张三消失了500 account(name balance){ 张三 500 李四 1000 }
        
        引入索引，就是为了避免出现上述问题。事务会把多个sql语句打包成一个整体，可以保证这些sql语句要么全部执行正确，要么“一个都不执行”
        
        不是真的一个都没执行，只有执行了才能知道是否失败。只是看起来像一个都不执行。
        
        执行如果出错，通过回滚（rollback）来将出错之前的sql语句，再逆向回退。看起来就像“一个都没有执行”
        
        account(name balance){ 张三 500 李四 1000 } -- 李四的1000没有加上500，通过回滚，再给张三加回去 account(name balance){ 张三 500+500 李四 1000 }
        
        事务将对个sql语句打包在一起，作为一个整体来执行。这个特点叫做“原子性”。
        
        2.使用
        
        1.开启事务 start transaction;
        
        单独执行的每个sql,都是自成一个体系，是没有原子性的。开启事务后，在事务当中执行的sql，才具有原子性。
        
        start transaction; -- 开启事务
        
        2.执行多条语句
        
        3.回滚或提交 rollback / commit
        
        rollback是全部失败，也可以主动来触发回滚。一般用来搭配一些条件判断逻辑来使用
        
        commit是全部成功，事务结束
        
        3.回滚的原理：
        
        通过日志的方式，来记录事务中的关键操作。这些记录就是回滚的依据。日志是打印出来的内容，存储在文件中。即使是主机掉电，也不影响。回滚用的日志已经被存储在文件中了。一旦重新启动主机，mysql也会重新启动，此时就会发现回滚日志中有一些需要进行回滚的操作。于是就可以完成回滚。
        
        4.事务的特性
        
        1.原子性：
        
        通过回滚的方式，保证这一系列操作都能正确执行，或者恢复成原样以便后续重新执行
        
        2.一致性：
        
        事务执行之前和之后，数据不能太离谱。要通过数据库的约束和一系列的检查机制来完成。
        
        3.持久性：
        
        事务做出的修改，都是在硬盘上持久保存的。即使重启服务器，数据仍然存在。事务执行的修改仍然有效。
        
        4.隔离性：
        
        数据库在执行并发事务的时候，会出现一些问题：
        
        1.脏读问题
        
        事务A正在写数据的过程中，另一个事务B读取了同一个数据。接下来事务A又修改了数据，导致B之前读到的数据变成了无效的数据。这个无效的数据就被称为脏数据。
        
        解决方法：给写操作加锁
        
        针对写操作进行加锁。在写数据的时候，先不要进行读取
        
        加锁之后并发性降低了，提高了隔离性，降低了效率，提高了数据的准确性。
        
        2.不可重复读
        
        在并发执行事务的过程中，事务A在内部多次读取同一个数据，出现不同的情况，就称为不可重复读。事务A在两次读的过程之间，事务B修改了数据并提交了事务。
        
        解决方法：给读操作加锁
        
        在进行读操作的时候，不能进行写操作。进一步降低了并发程度，降低了效率，提高了隔离性
        
        隔离性：如果两个事务之间的影响越大，隔离性就越低。影响越小，隔离性就越高。
        
        3.幻读
        
        约定了读加锁和写加锁。写的时候，不能进行读操作。读的时候不能进行写操作。从而解决了脏读和不可重复度的问题。
        
        虽然约定读的时候不能写，在读A文件的时候，不能写A文件。但是可以去写B文件。这样B文件的数据就改变了。当事务A在执行的过程中，两次的读取操作。数据的内容虽然没变，但是结果集改变了。这样的情况称为“幻读”。
        
        解决方式：
        
        引入串行化的方式，来解决幻读。保存绝对的串行执行事务，此时完全没有并发了。并发程度最低（完全没有），隔离性最高、效率最低、数据最准确。
        
        事务的隔离级别
        
        效率和数据的准确性无法兼顾。不同的场景有不同的需求。MySQL服务器也提供了“隔离级别”，针对不同的需求的隔离程度进行设置。有的场景需要追求效率，有的场景需要追求准确性。可以直接在MySQL的配置文件中，修改数据库的隔离级别。
        
        1.read uncommitted 读未提交
        
        并发程度最高、速度最快、隔离性最低、准确性最低
        
        2.read committed 读已提交
        
        引入了写加锁，只能读写完之后提交的版本。解决了脏读问题。
        
        并发程度降低了、速度降低了、隔离性提高了、准确性提高了
        
        3.repeatable read 可重复读
        
        引入了写加锁和读加锁。写的时候不能读，读的时候也不能写
        
        并发程度进一步降低、速度降低、隔离性提高、准确性提高。
        
        4.serializable 串行化
        
        严格的按照串行的方式，一个一个执行事务
        
        没有并发、速度最低、隔离性最高、准确性最高。
        
        默认的隔离级别：可重复读repeatable read
        
        点击移步博客主页，欢迎光临~