MYSQL（索引、事务）

一、索引

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系

1. 概述

• 概念：相当于是一本书的目录，是以‘列’为维度进行建立的
• 使用场景：如果我们要查询一个表中的某个数据，可以通过遍历来实现，但那样，速度太慢，可以通过给这个表建立索引，来提高查找的速度
• 使用讲解：
  • 按照id这列，建立索引，效果是在数据库上额外搞了一个空间，用来维护一些和id相关的信息，即id为1在哪个位置，id为2在哪个位置，参考目录。后续按照id查找的时候，就不必直接遍历了，而是从索引中进行查询，根据索引就能初步锁定数据所在的位置
  • 注意，这个索引中只能按照id来查询，按照name来查询，不能提高效率，需要给name也建立一个索引才行
• 作用：
  • 优点：提高查询效率
  • 缺点：
    （1）消耗额外的磁盘空间
    （2）可能会拖慢增删改的速度，因为新增不光要往表里写数据，还要增加索引。如果是删和改，如果删或改的条件正好是和索引匹配的，速度能快点，但是如果涉及到了索引列的删除/修改，就需要同时维护索引，就会很慢

2. 使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。因为主键不允许重复，进行插入或修改就需要先查询看是否重复

• 查看索引：show index from 表名;

• 创建索引：create index 索引名 on 表名(字段名);

  • 对于非主键、非唯一约束、非外键的字段，可以创建普通索引
  • 注意这个操作，可能会很危险
    • 如果表是空的，或者表里包含的数据本身就不多，那可以创建。但是如果表非空，并且里面包含了非常多的数据，创建索引，就会引起非常大规模的硬盘IO操作，进一步就会导致数据库被卡死。这就相当于要整理全部的数据。
    • 所以需要提前考虑好，在设计表的阶段，哪些列要索引

• 删除索引：drop index 索引名 on 表名;

  • 删除索引，只能针对手动创建的索引，自动生成的索引，是不能被删除的
  • 这个操作也十分危险，原因参考创建

• 如果就是要给一个有着非常多数据的表删除/创建索引

  • 准备一个新的mysql服务器，把表和索引都创建好，然后把数据都导入过来，再把要替换的mysql服务器关闭，最后把新的mysql服务器替换上去就行了
  • （上述操作的原因）数据库服务器往往也不是单台服务器，为了整个系统的可靠性，通常会搞多个mysql服务器节点，这些节点的数据都是一样的，能够提供相同的服务，彼此之间相互独立，其中一个挂了，也不影响整体

• 关于if、while等语法

  • sql中不建议使用一些其他支持的语法，如条件、循环等，因为该语法较老，且sql写得复杂，不方便调试和优化，实际开发中，去表示这样的更复杂的逻辑往往是使用其他语言+增删改查的方式

3. 底层的数据结构

其实在物理层面上，不需要‘表格’这样的数据结构，直接使用B+树来存储这个表的数据，“表格”只是让用户看起来这像个表格而已

1.概念

mysql的索引的数据结构是什么样的，取决于mysql使用哪个存储引擎

• 位置

  • 数据库的数据结构是在硬盘上的
  • 内存上的数据结构，对于访问操作来说，是不敏感的（找数据的过程花费时间多，真正访问的操作时间则不多）
  • 硬盘上的数据结构，对于访问操作来说，是比较敏感的，读写一次硬盘，开销远远大于内存
  • 读写一次硬盘，相当于读写1w次内存（大约的约束，内存和内存，硬盘和硬盘之间彼此差异很大）

• 存储引擎

  • mysql这个程序中，包含很多模块，有负责解析sql的，有负责网络通信的，有负责存储数据的……
  • 负责存储数据的就是存储引擎，本质上就是代码中的一个模块（包含了若干个代码文件、一群具体代码……）

• mysql支持多种存储方案，Innodb是当下最主流的一种方式

2.分析

索引主要是为了快速查找，这方面红黑树和哈希表效率高。

• 哈希表：不适合数据库的查询场景（哈希表是通过hash函数来得到一个确定的下标），而且只能做精确查询，没法做模糊查询和范围查询
• 红黑树：不适合数据库的查询场景，元素有序时，可以处理范围查询，但是在元素个数比较多的时候，红黑树的高度会高，对应的比较次数会多，14个数据就有4层了，而每次比较都在内存上，都要进行硬盘IO操作

B+树

为了解决上述问题，出现了为数据库量身定做的数据结构 ------ B+树

（1）B树
在了解B+树之前，需要先了解下B树（有时写作B-，'-'是连接符，不是减号）

B树本质上，是一个N叉搜索树，一个节点上，可以保存多个key，N个key就能延伸出 N+1 个分叉来，N个key就划分出 N+1 个区间

1. 此时每个节点上，都可以保存多个元素了，对于数据库来说，每个节点都需要把数据从硬盘上读取出来，才能进行比较，一个节点有多个key，和一个节点上有一个key，硬盘IO的开销是差不多的，就像下楼倒一个垃圾和倒四个垃圾时间上没太多区别，所以B树的高度是远远小于二叉搜索树的，所以进行查询的时候，硬盘IO的次数会减少
2. B树查询元素的流程：
   （1）从根节点出发判定要查找的元素是否在根节点上存在
   （2）如果不存在，看这个元素在哪个区间，然后根据这个区间的路线，往下一个节点找，最终找到叶子结点，如果到叶子结点还不存在，那就是真的不存在
3. 对于B树来说，在进行插入元素和删除元素的时候，会涉及到拆分和合并的操作。
   （1）一个节点，可以存多个key，但是不能无限地存，当存储的key数量达到一定程度的时候，就需要把这个节点给拆分，把这个节点中的一部分以树的子节点的方式重新组织
   （2）这样可以保持当前这个节点中key的数量始终不会太多，能延伸出新的叶子结点
   
   （2）B+树

【1】B+树也是N叉搜索树，但是N个key分出了N个区间，其中节点上最后一个key就是最大值（也可以取最小值）

【2】父节点的key会在子节点中重复值出现（以最大值的身份），虽然有很多重复元素，看起来浪费了空间，实际上能够达成 **叶子结点这一层，包含了整个数据的全集 **的效果

【3】把叶子结点按照链表这样首尾相连，就可以通过叶子结点之间的连接，快速找到“上一个”，“下一个”元素，这也方便进行范围查询

优势

• 擅长范围查询
• 所有的查询操作，最终都会落在叶子节点上，比较次数是均衡的，查询时间是稳定的（有时 ‘稳’ 比 ‘快’ 更重要）
• 由于叶子节点上是完整的数据全集，因此表的每一行数据的其他列，都可以保存到叶子节点上，而非叶子节点，只存储构建索引的key即可。此时，非叶子节点的存储空间，消耗是非常小的，我们甚至可以在内存中缓存一份。这样，在进行数据查询的时候，就可以通过内存来直接进行比较，从而更快速地找到叶子节点上的记录，也进一步减少硬盘IO的次数

二、事务

• 使用场景:为了完成某个工作，需要多组sql操作。如A转账给B，那么需要先给A的账户余额减钱，再给B账户加钱
• 概念：事务的本质，就是为了把多个操作，打包成一个操作来完成（让这多个操作，要么全都能执行成功，要么就一个都不执行，不然就像给A扣钱了，但是没给B加钱）--------- 事务的原子性（这些操作是一个整体，不能再分割了）
  • 这个“一个都不执行”不是真的没执行，执行成不成功得执行了才知道，真正执行时是不知道哪一步操作会失败的，如果执行到中间出错了，就需要自动地把前面已经成功执行的操作还原回最初没有执行的模样
  • 所以本质上，这个“一个都不执行”不是真的没执行，而是看起来就像没执行一样
  • 还原回最初是【回滚】，回滚要把事务中执行的每个操作，都记录下来，如果需要回滚，就直接按照之前操作的“逆操作”来执行就行了
    • 操作的记录是通过特定的日志，来记录的。日志的数据是始终在硬盘上存在的，即使是数据库服务器重启，也能在重启后根据之前没有回滚完的情况继续进行处理
• 使用：
  • 开启事务：start transaction;
  • 执行多条SQL语句
  • 手动触发回滚或提交事务：rollback/commit
    • rollback即是全部失败，commit即是全部成功
    • 一个事务务必要以这两个操作结尾，如果没有这两个操作，接下来的各种sql操作都会被认为是事务的一部分
• 事务的基本特性：
  • 原子性：保证多个操作被打包成一个整体，要么能够全部执行正确，要么就一个都不执行
  • 一致性：事务执行之前，和事物执行之后，数据能对上。这依靠约束和回滚机制来实现
  • 持久性：事务这里执行的各种操作，一旦事务执行成功了，这里的所有操作产生的修改都会写到硬盘中，即持久生效的
  • 隔离性：并发执行事务的时候，隔离性会在执行效率和数据可靠之间做出权衡，“隔离”描述的是同时执行的事务之间相互的影响，隔离性越高，并发性就越低，数据越可靠，性能就越低
    • 并发意思是“同时执行”，数据库是一个客户端服务器结构的程序，既然是服务器，服务器就可以同一时刻给多个客户端提供服务（多个客户端都能给服务器提交事务），如果提交的这两个事务，是修改不同的数据库/表，相互独立那没什么影响。但是如果修改的是一个库/表，顺序的先后会影响最终的执行结果，就会出现问题（脏读、不可重复读、幻读）

1. 脏读

问题描述：读到的数据是一个临时的数据，不是最终的数据，“脏”表示临时的，并非是最终准确结果

解决方法：给写操作加锁，一个事务A写的时候，其他事务B不能读了，直到A事务写完数据，提交事务，其他的事务B才能来读取数据。

效果：引入了写加锁，降低了两个事务之间的并发性，提高了隔离性，降低了效率，使数据更加准确了

2. 不可重复读
问题描述：一个事务中，两次读到的数据不一致（在读的过程中，另一个事务修改了数据）

解决方法：给读操作加锁，别人读的时候，我不能写（除非是写完提交后，别人才能读），此时，别人读的时候，可以再开启一个事务来写，第二个事务提交之前，其他读事务读到的都是旧版本数据，第二个事务提交之后，别人再读读到的就是新版数据了

效果：并发程度又进一步降低了，隔离性提高，执行效率降低，数据更加可靠

3. 幻读
问题描述：一个事务在多次读的过程中，虽然读到的数据的值是一样的，但是结果集是不同的（多出或少出一些记录），可以视为是不可重复读的特殊情况

解决方法：串行化，彻底放弃并发执行事务，所有的事务都是一个挨着一个地串行执行（执行完一个事务，再执行下一个事务）

效果：并发性最低，隔离性最高，效率最低，数据最可靠

4. mysql的四种事务的隔离级别