【MySQL】索引和事务

'Dream是普通小孩耶

已于 2023-01-10 16:32:25 修改

阅读量753

点赞数 1

分类专栏： Note-数据库文章标签： 1024程序员节 mysql CURD 索引事务

于 2022-10-24 00:28:28 首次发布

本文链接：https://blog.csdn.net/weixin_54150521/article/details/127482876

版权

Note-数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

前言

你所做的事情，也许暂时看不到成果，但不要灰心或焦虑，你不是没有成长，而是在扎根

【索引和事务】主要和面试题有关！

一、索引

一）概念

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

二）作用

索引的存在主要是为了提高查询的效率
但是索引也是需要付出代价的：

①消耗更多的空间
②虽然提高了查询效率，但是降低了增加、删除、修改的效率——增删改，不仅要修改数据，还要调整索引！

（虽然索引有代价，但是是值得的，因为：大多数情况下，查询频率大于增删改频率）

索引所起的作用类似书籍目录，可用于快速定位、检索数据。

三）使用场景

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

数据量较大，且经常对这些列进行条件查询。
该数据库表的插入操作，及对这些列的修改操作频率较低。
索引会占用额外的磁盘空间。
满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

四）使用

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

1. 查看索引

show index from 表名;

2. 创建索引

语法：

create index 索引名 on 表名(列名);

创建索引是一个“低效操作”，如果表中数据少则影响不大；如果表中数据较多时创建索引就可能非常耗时+带来大量的硬盘IO——也是可能会卡死数据库的
创建索引会创建出一些相关的数据结构。（索引的实现通常使用B树及其变种B+树。）
对于非主键、非唯一约束、非外键的字段，可以创建普通索引。

3. 删除索引

语法：

 drop index 索引名 on 表名;

删除操作和创建操作类似，都是比较低效的操作
（对于生产环境上比较大的表，一般都是建表之初就把索引规划好的）
SQL确实能够支持一些定义变量、定义函数、条件、循环、库函数等操作，但是很少会使用；一般不会使用SQL写一些太复杂的逻辑，逻辑层面的东西一般都是通过Java这样的语言来完成的；SQL一般是完成增删查改操作的。
可以使用explain来进行查看SQL的执行：

explain select * from test_user where id_number=123;

4. 补充

面试经典问题：
1）索引背后的数据结构是啥样的
2）谈谈ArrayList和LinkedList的区别：
① ArrayList底层是顺序表，LinkedList底层是链表；
② ArrayList查找较快，LinkedList增加删除较快（这是错误的！）——ArrayList查找并不快，只是具备随机访问能力（随机访问是通过下标获取元素；查找是根据值来获取元素位置indexOf 。而ArrayList查找的时候是要遍历的，时间复杂度O(N)并没有优势）
LinkedList增加删除也不快，首尾增删是O(1)，但是中间位置进行增删还是O(N)，增加add(index,value)要遍历找index位置，所以还是O(N)）】
（这里的中间位置插入删除之所以是O(N)不是链表不行，而是LinkedList不太行）
哈希表和二叉搜索树都不太适合左数据库的索引。理由如下：
①哈希表增删查改都快，但是只能查询值相等的情况，对于> < between…and… 这类比较大小的范围查询是不太适合的。
②二叉搜索树最坏查询速度是O(N)，像AVL树/红黑树这些比较平衡的二叉搜索树是O(logN)；如果数据库数据特别多，则二叉树高度O(logN)就会比较高，这又和查询次数相关。
故：如果比较是在内存内进行的，那么多比几次少比几次影响不大，但是如果每次都要读取硬盘，这个就可能会产生影响：IO次数越少越好）
数据库索引的量身定做：B+树
（认识B+树之前，先认识一下B树，有些书上的B-树其实就是B树）
1）B树是一个N叉搜索树，每个结点上可能会包含N-1个值，当然也可以更少；N-1个值就把区间划分成N份；这样，在同样的数据集合下，N叉树就会比二叉树的高度小很多，IO次数也就降低了
2）B树代码的实现是非常复杂的，复杂主要是在分裂和合并上
【B树与B+树的区别：
① B树每个结点N-1个值，就分出了N个区间；B+树N个值就分出了N个区间；
② B树中的值不会重复出现，B+树是可能重复出现的（况且在B+树中，父元素的值会在子元素中以最大值/最小值的姿态出现）；
③ 在叶子结点这里，B+树会把所有的叶子结点以链表的形式首尾相连（so：在叶子结点这里已经是完整的数据集合了，前面的非叶子结点都会在这里体现出来，这也就是元素重复的意义），此时就非常便于范围查找
④ 正因为叶子结点是全集数据，只需要把每一行（每一条记录的完整的所有列关联到叶子结点上即可），非叶子结点只需保存索引列（其实就是一个id）
——so：非叶子结点占用的空间相比于完整的数据集合非常小，就可以在内存中缓存；因此此时的查询又进一步减少了硬盘IO】

二、事务

一）为什么使用事务

以银行、购物等为例，钱只有一边减少。

二）事务的概念

事务：操作要么全部执行，要么一个都不执行（其实就是如果中途执行失败就自动回滚rollback），也就是保证了原子性。
在事务的执行过程中，MySQL会记录每一步都干了啥，一旦出现问题就根据记录的日志进行修改回滚
注：为什么数据库不设置撤回功能？——因为撤回功能实际上一般是人为操作失误进行撤回的，但是操作数据库大部分都是使用代码，而且要实现撤回功能要记录所有操作；因此，撤回功能不常使用且代价太大。

三）使用

start transaction; // 事务开启
commit; // 事务结束，提交事务
rollbacck; // 回滚
注：事务的开启、提交、回滚一般都是通过代码来控制的
（代码都不复杂，复杂的是 事务的相关原理（面试常考！）)
事务的特性
① 原子性A：事务的根本/事务存在的意义，能够把多个SQL打包成一个整体，要么都执行，要么都不执行（如果执行过程中出错就进行回滚）
② 一致性C：事务执行前后，数据处在“一致”的状态
③ 持久性D：事务进行的改动都是写到硬盘里，不会随着程序启动/主机重启而丢失
④ 隔离性I：（最复杂）多个事务并发执行的时候，事务之间能够保持“隔离”独立，不相互干扰

（如果这些并发执行的事务操作的是不同的数据库/不同的表，那么此时是没啥影响的；但是如果操作的是相同的数据库、相同的表就可能会进行抢占而出现问题）

so：隔离性存在的意义其实就是：让并发执行事务的过程中尽量不出现问题/问题在可控范围内

脏读问题：即读脏数据。脏数据就是一个临时的数据，不代表最终结果，中间可能数据就被修改了。
1）所以：脏读其实就是一个事务A在提交之前修改了数据，而事务B读到的是事务A修改之前的数据，即事务B读到了“无效的数据”，这就是脏读。
2）【如何解决脏读问题：在提交之后才能读（即：写完读），相当于对 写操作加锁】
3）在写加锁之前，读和写操作是完全并发的，隔离性是最低的；但是当写操作加锁之后，并发性降低了（效率降低），隔离性提高了（准确性提高）。
不可重复读：在事务提交之后，对于该事务读取数据时发现数据被人修改，多次读取到的数据不一样。
1）【解决方法：写加锁 + 读加锁】
2）读加锁的引入，使得并发程度进一步降低（效率降低），但是隔离性/准确率提高）
3）【在一个程序读文件A的时候，另一个程序去操作其他的文件，此时并不影响该程序对文件数据的读取，但是会发现最后的结果集可能改变了——这是 “幻读”（可以视为是“不可重复读”的特殊情况）
——解决“幻读”问题：“串行化”（一次只有一个程序在操作）——此时并发程度最低/效率最低，但是隔离性最高/数据的准确性最高】
并发执行事务中可能带来的影响：【脏读、不可重复度、幻读。】但是这些影响并不一定是bug（是不是bug看实际需求，是否符合需求、是否允许存在一定误差）
如果需求对于数据精度要求不高，就可以让并发性高一些、隔离性差一些，以此提高效率；但是如果需求对于数据精度要求很高，则并发性低一些，隔离性高一些，以此提高精度
MySQL的隔离级别：
① read uncommitted 允许读未提交的数据，并发程度最高，隔离性最低；可能存在脏读、不可重复读、幻读问题。
② read committed 只能读提交之后的数据，相当于写加锁，并发程度降低，隔离性提高；解决了脏读问题，但是可能存在不可重复读、幻读问题。
③ repeatable read 相当于读和写都加锁了，并发性再降低，隔离性再提高；解决了脏读和不可重复度问题，但是可能存在幻读问题。【MySQL默认！】
④ serializable 严格执行串行化，并发程度最低，隔离性最高；解决了脏读、不可重复读和幻读问题。效率最低。
（可以在mysql的配置文件my.ini中进行设置：根据不同的需求场景设置不同的级别，以此来平衡效率和准确性）
【面试题：】
1）谈谈如何理解索引——①索引是干啥的（解决了什么问题） ②索引付出的代价 ③索引背后的数据结构
2）谈谈如何理解事务——①事务是干啥的（从原子性切入） ②事务的其他特性 ③重点隔离性，在并发执行事务下会有哪些问题、以及如何解决 ④MySQL的隔离级别有哪些，和上面的问题如何对应