【MySQL】索引和事务

秋招 秋招中最经典，最高频的面试题

索引 index

在数据库中建立一个特殊的“目录“（一系列特定的数据结构），为了加快查询速度

select 查询都是遍历查询，比较复杂 O(N) 级别复杂度

嵌入式设备 咱们平时用的电脑、手机这些都是“计算机”，还有一些其他设备，也是包含“计算机”的，例如：路由器、冰箱、洗衣机、空调、投影仪等等，这些设备的“计算机”不是用来计算的，是作为辅助设施。

嵌入式系统 这些计算机往往非常小，硬件配置也比较低，这样的计算机系统就叫“嵌入式系统”。

操作索引的 SQL

查看索引

• show index from 表名;
  • primary key 、forigen key 和 unique 都能自动生成索引
  • 索引都是针对列生成的 搜索创建了索引的列才会加快查询速度

  

  image.png

创建索引

• create index 索引名 on 表名(列名);
  • 创建索引需要在最初创建表的时候规划好
  • 危险操作

  

  image.png

删除索引

• drop index 索引名 on 表名;
  • 只能删除自己创建的，不能删自动生成的
  • 危险操作

事务

• 把多个要执行的 SQL 打包成一个“整体”，其能做到要么都完成执行，要么一个都不执行，防止出现“半吊子”
• 同时，也把事务支持的上述“特性”称为“原子性”
• 批量执行 SQL问题

回滚（rollback）

• 不是没执行，而是在出错的时候，数据库会自动执行“还原操作”，将已经执行过的 SQL “撤销“了，效果就和没执行一样。
• 数据库存在一系列“日志体系”，记录在文件中，记录了每一步 SQL 的操作，可以看到对数据进行了哪些修改，回滚时就参考这些信息进行完成。
• 既可应对“程序崩溃”，也可应对“主机掉电”

操作

1. 开启事务
   • start transaction;
   • 一般开发中不会这么搞，都是通过“代码”的方式开启事务，批量执行
2. 提交事务
   • commit;

四个核心特性

1. 原子性（最重要）
   • 事务内的所有操作作为一个整体被执行，不会出现部分完成的情况。
2. 一致性
   • 事务执行前后，数据库中的数据都是“合法状态”，不会出现非法的临时结果的状态
3. 持久性
   • 事务执行之后，就会修改硬盘上的数据，持久生效
4. 隔离性（最常考）
   1. 事务并发执行的时候，相互之间产生的影响是
      • MySQL 是一个“客户端—服务器”结构程序，所以一个服务器通常会给多个客户端同时提供服务，因此这多个客户端就同时给这个服务器提交事务来执行。与之相对，服务器就需要同时执行多个事务，此时就是“并发执行”
      • 若这些同时执行的事务，恰好是针对同一个表进行增删改查，此时会出现一些问题
        1. 脏读（数据过时了）
           • 在一个事务中读取了另一个未提交事务的数据。这意味着读取的数据可能是不正确的、不一致的或者“脏”的，因为这些数据可能随时会被回滚，导致之前读取到的数据无效。
           • 例如，事务A读取了事务B未提交的数据，但事务B后来可能由于某种原因回滚了，那么事务A读取到的数据就是“脏”的，因为它基于了永远不会在数据库中实际存在的数据。
           • 解决办法：给写操作加锁 在进行修改操作的时候，不能进行读操作
        2. 不可重复读
           • 当一个事务在读取某个数据项后，在该事务还未结束时，另一个并发事务修改或删除了这个数据项，并提交了修改。这时，如果第一个事务再次读取这个数据项，就可能会读取到已经被修改或不存在的数据，导致两次读取的结果不一致。
           • 例如：假设有一个银行账户，初始余额为1000元。事务A开始并读取账户余额，看到是1000元。此时，事务B开始并执行了一个存款操作，向该账户存入了500元，然后提交了事务。如果事务A再次读取该账户的余额，它会发现余额已经变成了1500元，与之前的1000元不一致。这就是“不可重复读”的一个例子，因为事务A在两次读取之间看到了不一致的数据。
           • 解决办法：给读操作加锁 在进行读取操作时，其他事务不能修改它正在读的数据
        3. 幻读
           • 相当于“不可重复读”的特殊情况
           • 它发生在一个事务读取了几行数据后，另一个并发事务插入或删除了一些行，然后第一个事务再次读取同样的数据范围时，发现出现了“幻影”行或某些行消失了。
           • 例如，假设有一个事务正在读取一个范围内的订单数据，它先读取了10行数据。此时，另一个事务插入了一个新的订单到这个范围内。当第一个事务再次执行相同的查询时，它会发现现在有11行数据，多出来的这一行就像是“幻影”一样突然出现了。
           • 解决办法：串行化 每个事务都必须等待前一个事务完成后才能开始执行，这样可以确保事务之间不会互相干扰，从而保持数据的一致性和完整性。
   2. 隔离级别
      • 可通过配置文件来设置当前服务器的隔离级别的级别
      • 不同的隔离级别会使事物之间并发执行的影响产生不同的差别，从而影响到上述三个问题的情况
      • 四个隔离级别
        1. read uncommitted：读未提交
           • 一个事物可以读取另一个事务未提交的数据，此时就可能会产生“脏读”、“不可重复读”和“幻读”三种问题。
           • 但此时，多个事务并发执行程度最高的。（速度最快）
        2. read committed：读已提交
           • 一个事物只能读取另一个事务提交后的数据（给写操作加锁了），此时可能会产生“不可重复读“和“幻读“问题。（“脏读“问题解决了）
           • 此时，并发程度降低，执行速度变慢，同时也称为：事物之间的隔离型提高了
        3. repeatable read：可重复读
           • 事务在开始时创建一个快照，事务内的查询都是基于这个快照进行的，从而确保了在同一个事务内多次读取相同数据时会看到一致的结果。相当于是给“写操作”和“读操作”都加锁了。此时可能产生“幻读”问题（“脏读”和“不可重复读”问题解决了）
           • 并发程度进一步降低，执行速度进一步变慢，事物之间的隔离性进一步提高
        4. serializable：串行化
           • 它要求事务串行执行，即事务之间不会并发执行，所有的事务都是在服务器上一个接一个的执行，从而避免了“脏读”、“不可重复读”和“幻读”的所有问题。
           • 并发程度最低，执行速度最慢，隔离型最高，数据最准确