索引和事务究竟是何方神圣?那可是面试中的常客!

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: 索引和事务究竟是何方神圣?那可是面试中的常客!

在本文中,介绍的是MySQL中的索引和事务。但是,在关系型数据库中,这些知识也都通用



索引

1.介绍索引

1.1.什么是索引

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

(2)索引就是一种类似于目录的东西。表相当于一本图,而索引就相当于书的目录,表中的内容就相当于书中的内容

1.2.索引的优点

可以提高查询速度

1.3.索引的缺点

(1)索引会占据额外的存储空间,一般都是在硬盘上面

(2)索引可能会拖慢增删改的速度

1.4.索引的使用场景

推荐:

(1)数据量比较大,而且常使用条件查询

(2)数据库的插入和修改操作使用频率比较低

(3)索引会占用额外的磁盘空间

不推荐:

(1)非条件查询

(2)经常做插入、修改操作

(3)磁盘空间不足

2.索引的基本操作

2.1.查看索引

(1)语法:show index from 表名;

(2)示例:

(3)哪些操作会自带索引

1)主键列

2)带unique的

3)foreign key(外键)

2.2.创造索引

(1)语法:create index 索引名字 on 表名(列名);

(2)索引是创建在列上面的。在查询的时候,也需要查询有索引的一列,才能达到提速的效果

(3)示例

查看刚才创造的索引:

(4)创造索引的注意事项

在数据量很大的时候,是不建议再加上索引的,否则会引发大量的CPU/硬盘IO的消耗;所以,一般在建表初期就考虑是否要加上索引    

(5)做法

1)另外搞个机器,也搭建好一样的数据库服务器

2)创建表,建立索引(此时是空表)

3)把旧的数据库的数据,导入到新的数据库中(非常耗时,但也要控制好速度,否则容易挂)

4)数据导好了,把应用程序的请求切换到新的服务器上

2.3.删除索引

(1)语法:drop index 索引名 on 表名;

(2)示例:

3.索引的底层原理

在这里面会将关于索引背后的数据结构是什么?有什么优势?在查询的时候,是如何工作的?

在innodb存储引擎下,数据库索引底层的数据结构一般是B+树,表不存在任何主键、外键和unique等,索引的背后就不再是B+树。

3.1.简单介绍B+树

(1)是一棵N叉搜索树,每个结点可以有多个值

(2)非叶子结点的值最终都会出现在其叶子结点上

(3)最终的叶子结点(最底层)就是数据的全集

(4)B+树的大致样子:

(5)特点

每个结点中的值都会在下一层中出现,最终就会形成最后一层叶子结点就是数据的全集

3.2.B+树相比B树的优势

(1)非常方便进行遍历和范围查询

(2)任何一次查询操作,最终都要落到叶子结点完成的,所有的查询操作经历的硬盘IO次数都一样(体现稳定性)

(3)叶子结点是数据的全集,所以在非叶子结点中,都是重复出现的数据,就可以把表每一行的数据,最终都关联到叶子结点这一层,非叶子结点就只需要保存一个key值(如id)即可

非叶子结点所占的空间比较小,就可以在缓存在内存中了,进而查询速度会比较快

3.3.索引背后是B+树的条件

基本条件:在innodb存储引擎下,……去创造一张表的时候。

(1)创造的表(不存在主键、外键、unique),此时表会有默认主键,会根据该主键创建表,此时表索引的背后不是B+树

(2)创造表时(存在主键、外键、unique等),就会根据主键列生成一个索引,从而根据索引创造B+树;此时,表索引背后的数据结构就是B+树,而整个表的数据就会存储在这棵B+树中

(3)在创建表之后,对某列设置索引,索引背后会再生成一棵独立的B+树

3.4.数据如何存储

这里介绍有索引且索引背后的数据结构是B+树的情况下,也就是B+树是如何存储插入表中的数据的

(1)主键索引B+树

前提:主键索引就是创造表的时候根据主键生成的一个索引,该索引背后的数据结构就是B+树

这样的一棵B+树,非叶子结点只存储主键列的值(例如id,也就称为key值),只有在叶子结点才会存储一整行的数据

当插入数据的时候,那一行数据会拿着key值去遍历b+树,找到合适的位置(也就是叶子结点层),才将整行数据存入叶子结点中。(此时非叶子结点也会存储着key值)

(2)非主键索引B+树

前提:非主键索引是在表已经存在的时候,给表中的某一列创造索引,该索引的背后也是B+树

此时,非叶子结点上面存储的也只有一个key(就是在哪一个列上设置的索引,key值就是那个列的值),在叶子结点上面,存储的则是之前主键的key值(拿到这个key值,再去遍历之前的B+树)

3.5.查询操作

(1)非索引非主键查询:在select时,where后面限制的列不存在索引,就会根据从B+树最左边的叶子结点开始遍历,俗称(链表查询),查询速度不会提升(非索引查询)

(2)主键索引查询:在select时,查询的是列是主键(主键列肯定有索引),就会从B+树起始位置开始查询(二叉搜索树查询)

(3)非主键索引查询:在select时,查询的是索引列但是非主键列,也是从B+树起始查询,拿到主键id后再回到主键列的B+树进行查询(查询两次)

事务

事务的四大特性(功能):原子性、一致性、持久性、隔离性

1.介绍事务

(1)背景引入

在执行多个SQL语句时,可能会发生这些情况:程序崩溃、系统崩溃、网络断开、主机掉电了……可能会导致前面SQL语句执行成功,而后面的SQL语句执行失败

(2)背景发生

在A向B转账的过程中,由于一些失误,就会导致A的钱转出去了,但是B却没有收到钱

(3)事务的概念

为了防止上述等情况的发生,引入事务的概念

事务,就是把多个操作打包成一个整体(操作)

(4) 事务的保证

将多个操作打包成一个操作后,就能保证整体要么执行成功,要么都执行失败。可以避免只有一部分操作执行成功,避免产生一些问题

(5)突出”原子性“

将多个操作打包成一个操作,则体现事务的原子性

2.事务的四大特性

2.1.原子性

(1)背景

前面说到,事务可以保证整个操作要么执行,要么失败,结果是这样子展示的,但是他的过程却不是这样子的。

(2)事务的回滚机制

事务中的多个sql语句也是一条一条执行,但是事务可以保证,在事务中的sql语句出现问题时,可以将他恢复回去,就行如没有执行过sql语句一样,一般术语称为翻新,在mysql中,称为回滚(rollback)

1.为了实现事务的回滚机制,事务在执行的时候都会记录日志。

2.日志就是可以记录一些操作,比如sql语句是怎么执行的,执行了删除还是插入操作

如何回滚(在执行事务的过程中出现了问题,只要数据库处于正常工作状态,都能恢复):

1)之前执行了新增操作,就把插入的数据删除掉

2)之前执行了删除操作,就会把数据新增上来

3)之前进行了修改操作,就会把数据该回去

4)之前是查询操作,则没有任何影响,不进行任何的恢复操作

在执行完事务操作后,这些日志通常会被删除

(3)事务、原子性、日志关系

日志保证了事务回滚机制的进行,回滚机制又保证了事务的原子性(也成为功能)

2.2.事务的一致性

(1)性质体现

事务执行之前,和事务执行完毕后,可以保证执行前的状态是一致的,执行后的状态也是一致的,也就是保证数据的正确性。

2.3.持久性

(1)在执行完事务之后,可以保证数据在硬盘上持久存储,重启后仍然存在

(2)保证事务的每一次操作对数据的改变都是“永久”的

2.4.隔离性

隔离性主要是为了确保事务执行的成功,防止被其他的事务干扰(其他的事务不可见)。

在执行并发事务时,没有实现隔离时会产生的问题:

(1)脏读问题

问题实质:在数据库中,如果有事务A和事务B;事务A针对某个表做出了一些修改,在事务A提交之前(未修改完),事务B就对这里的数据进行了读取,最终就可能出现A后续的操作又把上述的数据进行了修改,导致事务B读到的数据和A提交的数据是不相同的。

做法:对写操作加上锁,称为写加锁操作

结果:加了锁之后,在事务A执行写操作的时候,事务B就不能执行了,需要等待A执行完。

导致:降低了“并发能力”,变相降低了数据库服务器的处理效率;但是提高了“隔离性”,也提高了数据的准确性。

(2)不可重复读问题

存在三个事务A,B,C

问题实质:事务A针对数据进行修改提交,接下来事务B进行读取数据(里面有多个sql语句都要进行读操作),在执行B的过程中,有一个事务C也参与了进行,并且对数据进行了修改。导致B里面不同的读操作,读到的结果是不一样的

做法:引入读加锁

结果:在读取数据的时候,也不能进行任何的写操作

导致:又会使“并发程度”进一步降低,效率也会降低,而“隔离性”又进一步提高,数据的准确性也得到提高(ABC都不能进行并发操作)

(3)幻读问题

问题实质:事务A修改并提交数据,事务B进行读数据,此时C给对应的表进行了新增数据/删除数据等操作,使得事务B读到的数据集是不同的(内容一样,数量不一样)

做法:实现串行化的方式,使所有的事务按照一个接一个的执行

后果:所有事务只能一个接一个执行

导致:此时,完全没有并发性,执行效率最低,隔离性最高,数据也是最准确的

(4)总结

问题:脏读问题和不可重复读问题,一般都是由于一个事务在读操作时,其他事务对内容数据进行了修改,导致读取的内容前后不一样;而幻读问题,是由于一个事务在读操作时,其他事务进行了插入/删除操作,导致数据的数量发生变化。

做法总结:

1)脏读:事务B读到了事务A中未提交的临时数据(脏数据)==>写加锁

2)不可重复读问题:事务B读的过程中,又有一个事务C对刚才事务A提交的数据进行了修改,使事务B内部不同的读操作读到的结果是不同的==>读加锁

3)幻读:类似不可重复读,事务B读的过程中,事务C没有修改数据内容,而是修改了“结果集”,导致B内部不同的读操作读到的数量不一样==>串行化

所以:

事务的隔离性,就是提供了几个隔离等级供选择,可以针对不同的情况进行不同程度的隔离。

3.隔离等级

(1)read uncommitted:允许读取其他事务未提交的数据==>脏读+不可重复读问题+幻读 并发程度最高,隔离性最低

(2)read committed:只能读取其他事务提交后的数据==>解决了脏读,存在不可重复读+幻读,并发程度降低,隔离性提高

(3)repeatable read:针对读操作和写操作都加锁==>解决了脏读+不可重复读,存在幻读,并发程度又降低,隔离性又提高

(4)串行化(serializable):所有的事务都是串行执行的==>解决了脏读+不可重复读+幻读,并发基本没有,隔离性最高。

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
|
2月前
|
存储 SQL 数据库
面试官:索引失效场景有哪些?
以下是内容的摘要: 本文列举了可能导致数据库索引失效的16种情况:全表扫描、索引列使用计算或函数、LIKE查询条件不匹配、未遵循联合索引最左前缀原则、索引列参与排序无筛选、隐式类型转换、OR条件连接索引、IN子句大量值、NOT操作、数据分布不均的JOIN、数据过于分散的查询、大结果集、临时表或派生表操作、索引维护不及时以及不等于比较和IS NOT NULL条件。这些情况都可能使查询优化器放弃使用索引,影响查询性能。
94 1
|
2月前
|
SQL 存储 关系型数据库
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
索引下推是MySQL 5.6引入的优化,允许部分WHERE条件在索引中处理,减少回表次数。例如,对于索引(zipcode, lastname, firstname),查询`WHERE zipcode='95054' AND lastname LIKE '%etrunia%'`时,索引下推先过滤zipcode,然后在索引中应用lastname条件,降低回表需求。索引下推可在EXPLAIN的`Using index condition`中看到。
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
|
1月前
|
索引 NoSQL 关系型数据库
【后端面经】【NoSQL】ElasticSearch - 1 -2 Translog + Elasticsearch索引与分片 + 面试准备
【6月更文挑战第15天】Elasticsearch利用Translog确保数据安全,类比MySQL的redo log,它在内存缓冲后记录Translog,每隔5秒持久化磁盘,提供高效且顺序的写入。尽管如此,仍可能最多丢失5秒数据。索引由分片组成,每个分片有主从结构,分布于不同节点以降低故障影响。当主分片失败,主节点会选择新主分片。面试中可讨论公司如何使用Elasticsearch、其性能、索引设计、可用性策略及解决过的挑战。常见问题涉及Elasticsearch的应用场景、问题解决及写入流程。
14 1
【后端面经】【NoSQL】ElasticSearch - 1 -2 Translog + Elasticsearch索引与分片 + 面试准备
|
27天前
|
存储 关系型数据库 MySQL
架构面试题汇总:mysql索引汇总(2024版)
架构面试题汇总:mysql索引汇总(2024版)
|
11天前
|
XML Java 关系型数据库
面试一口气说出Spring的声明式事务@Transactional注解的6种失效场景
面试一口气说出Spring的声明式事务@Transactional注解的6种失效场景
|
1月前
|
存储 Java 关系型数据库
Spring事务失效的 8 大原因,这次可以吊打面试官了!
Spring事务失效的 8 大原因,这次可以吊打面试官了!
|
2月前
|
NoSQL Dubbo Java
StringBoot编程式事务与声明式事务java工程师面试突击第一季
StringBoot编程式事务与声明式事务java工程师面试突击第一季
|
2月前
|
关系型数据库 MySQL 测试技术
面试-MySQL的四种事务隔离级别
面试-MySQL的四种事务隔离级别
37 0
|
2天前
|
SQL Java Unix
Android经典面试题之Java中获取时间戳的方式有哪些?有什么区别?
在Java中获取时间戳有多种方式,包括`System.currentTimeMillis()`(毫秒级,适用于日志和计时)、`System.nanoTime()`(纳秒级,高精度计时)、`Instant.now().toEpochMilli()`(毫秒级,ISO-8601标准)和`Instant.now().getEpochSecond()`(秒级)。`Timestamp.valueOf(LocalDateTime.now()).getTime()`适用于数据库操作。选择方法取决于精度、用途和时间起点的需求。
15 3
|
18天前
|
存储 算法 Java
Java面试之SpringCloud篇
Java面试之SpringCloud篇
33 1