专栏首页业余草MySQL 是如何保证一致性、原子性和持久性的!

MySQL 是如何保证一致性、原子性和持久性的!

编辑:业余草

今天,我们来简单的看一下 MySQL 的一致性、原子性和持久性问题。后面还扩展了 15 个简单的面试题,希望大家喜欢!

1、Mysql怎么保证一致性的?

OK,这个问题分为两个层面来说。

从数据库层面,数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中,C(一致性)是目的,A(原子性)、I(隔离性)、D(持久性)是手段,是为了保证一致性,数据库提供的手段。数据库必须要实现AID三大特性,才有可能实现一致性。例如,原子性无法保证,显然一致性也无法保证。

但是,如果你在事务里故意写出违反约束的代码,一致性还是无法保证的。例如,你在转账的例子中,你的代码里故意不给B账户加钱,那一致性还是无法保证。因此,还必须从应用层角度考虑。

从应用层面,通过代码判断数据库数据是否有效,然后决定回滚还是提交数据!

2、Mysql怎么保证原子性的?

OK,是利用Innodb的undo log。

undo log名为回滚日志,是实现原子性的关键,当事务回滚时能够撤销所有已经成功执行的sql语句,他需要记录你要回滚的相应日志信息。

例如

  • (1)当你delete一条数据的时候,就需要记录这条数据的信息,回滚的时候,insert这条旧数据
  • (2)当你update一条数据的时候,就需要记录之前的旧值,回滚的时候,根据旧值执行update操作
  • (3)当年insert一条数据的时候,就需要这条记录的主键,回滚的时候,根据主键执行delete操

undo log记录了这些回滚需要的信息,当事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。

ps:具体的undo log日志长啥样,这个可以写一篇文章了。而且写出来,看的人也不多,姑且先这么简单的理解吧。

3、Mysql怎么保证持久性的?

OK,是利用Innodb的redo log。

正如之前说的,Mysql是先把磁盘上的数据加载到内存中,在内存中对数据进行修改,再刷回磁盘上。如果此时突然宕机,内存中的数据就会丢失。

怎么解决这个问题?

简单啊,事务提交前直接把数据写入磁盘就行啊。

这么做有什么问题?

只修改一个页面里的一个字节,就要将整个页面刷入磁盘,太浪费资源了。毕竟一个页面16kb大小,你只改其中一点点东西,就要将16kb的内容刷入磁盘,听着也不合理。

毕竟一个事务里的SQL可能牵涉到多个数据页的修改,而这些数据页可能不是相邻的,也就是属于随机IO。显然操作随机IO,速度会比较慢。

于是,决定采用redo log解决上面的问题。当做数据修改的时候,不仅在内存中操作,还会在redo log中记录这次操作。当事务提交的时候,会将redo log日志进行刷盘(redo log一部分在内存中,一部分在磁盘上)。当数据库宕机重启的时候,会将redo log中的内容恢复到数据库中,再根据undo log和binlog内容决定回滚数据还是提交数据。

4. UNION ALL 与 UNION 的区别

  • UNION和UNION ALL关键字都是将两个结果集合并为一个。
  • UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。
  • 而UNION ALL只是简单的将两个结果合并后就返回。
  • 由于UNION需要排序去重,所以 UNION ALL 的效率比 UNION 好很多。

5. TRUNCATE 与 DELETE 区别

  • TRUNCATE 是DDL语句,而 DELETE 是DML语句。
  • TRUNCATE 是先把整张表drop调,然后重建该表。而 DELETE 是一行一行的删除,所以 TRUNCATE 的速度肯定比 DELETE 速度快。
  • TRUNCATE 不可以回滚,DELETE 可以。
  • TRUNCATE 执行结果只是返回0 rows affected,可以解释为没有返回结果。
  • TRUNCATE 会重置水平线(自增长列起始位),DELETE 不会。
  • TRUNCATE 只能清理整张表,DELETE 可以按照条件删除。

一般情景下,TRUNCATE性能比DELETE好一点。

6. TIMESTAMP 与 DATETIME 的区别

相同点

TIMESTAMP 列的显示格式与 DATETIME 列相同。显示列宽固定在19字符,并且格式为YYYY-MM-DD HH:MM:SS。

不同点

TIMESTAMP

  • 4个字节存储,时间范围:1970-01-01 08:00:01~2038-01-19 11:14:07。
  • 值以UTC格式保存,涉及时区转化,存储时对当前的时区进行转换,检索时再转换回当前的时区。

DATETIME

  • 8个字节存储,时间范围:1000-10-01 00:00:00~9999-12-31 23:59:59。
  • 实际格式存储,与时区无关。

7. 什么是联合索引

两个或更多个列上的索引被称作联合索引,联合索引又叫复合索引。

8. 为什么要使用联合索引

  • 减少开销:建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。减少磁盘空间的开销。
  • 覆盖索引:对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。覆盖索引是主要的提升性能的优化手段之一。
  • 效率高:索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W * 10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w * 10% * 10% * 10%=1w,效率得到明显提升。

9. MySQL 联合索引最左匹配原则

  • 在 MySQL 建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。
  • MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
  • = 和 in 可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

10. 什么是聚集和非聚集索引

  • 聚集索引就是以主键创建的索引。
  • 非聚集索引就是以非主键创建的索引。

11. 什么是覆盖索引

覆盖索引(covering index)指一个查询语句的执行只用从索引页中就能够取得(如果不是聚集索引,叶子节点存储的是主键+列值,最终还是要回表,也就是要通过主键再查找一次),避免了查到索引后,再做回表操作,减少I/O提高效率。

可以结合第10个问题更容易理解。

12. 什么是前缀索引

前缀索引就是对文本的前几个字符(具体是几个字符在创建索引时指定)创建索引,这样创建起来的索引更小。但是MySQL不能在ORDER BY或GROUP BY中使用前缀索引,也不能把它们用作覆盖索引。

创建前缀索引的语法:

ALTER TABLE table_name ADD
KEY(column_name(prefix_length))

13. InnoDB 与 MyISAM 索引存储结构的区别

  • MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
  • 而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引,所以必须有主键,如果没有显示定义,自动为生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型。
  • InnoDB的辅助索引(Secondary Index,也就是非主键索引)存储的只是主键列和索引列,如果主键定义的比较大,其他索引也将很大。
  • MyISAM引擎使用B+Tree作为索引结构,索引文件叶节点的data域存放的是数据记录的地址,指向数据文件中对应的值,每个节点只有该索引列的值。
  • MyISAM主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,辅助索引可以重复,(由于MyISAM辅助索引在叶子节点上存储的是数据记录的地址,和主键索引一样,所以不需要再遍历一次主键索引)。

简单的说:

  • 主索引的区别:InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
  • 辅助索引的区别:InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

14. 为什么尽量选择单调递增数值类型的主键

InnoDB中数据记录本身被存于主索引(B+树)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的结点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页。

如果使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引结点的后续位置,当一页写满,就会自动开辟一个新的页,这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

如果使用非自增主键,由于每次插入主键的值近似于随机,因此每次新纪录都要被插入到现有索引页的中间某个位置,此时MySQL不得不为了将新记录查到合适位置而移动元素,甚至目标页可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

简单的说:

索引树只能定位到某一页,每一页内的插入还是需要通过比较、移动插入的。所以有序主键可以提升插入效率。

15. 建表时,int 后面的长度的意义

int占多少个字节,已经是固定的了,长度代表了显示的最大宽度。如果不够会用0在左边填充,但必须搭配zerofill使用。也就是说,int的长度并不影响数据的存储精度,长度只和显示有关。

16. SHOW INDEX 结果字段代表什么意思

Table:

  • 表名。

Non_unique:

  • 0:该索引不含重复值。
  • 1:该索引可含有重复值。

Key_name:

  • 索引名称,如果是注解索引,名称总是为PRIMARY。

Seq_in_index:

  • 该列在索引中的序号,从 1 开始。例如:存在联合索引 idx_a_b_c (a,b,c),则a的Seq_in_index=1,b=2,c=3。

Column_name:

  • 列名。

Collation:

  • 索引的排列顺序:A(ascending),D (descending),NULL (not sorted)。

Cardinality:

  • 一个衡量该索引的唯一程度的值,可以使用ANALYZE TABLE(INNODB) 或者 myisamchk -a(MyISAM)更新该值。
  • 如果表记录太少,该字段的意义不大。一般情况下,该值越大,索引效率越高。

Sub_part:

  • 对于前缀索引,用于索引的字符个数。如果整个字段都加上了索引,则显示为NULL。

Null:

  • YES:该列允许NULL值。
  • '':该列不允许NULL值。

Index_type:

  • 索引类型,包括(BTREE, FULLTEXT, HASH, RTREE)。
  1. 如何解决like'%字符串%'时索引失效?

LIKE问题:like 以通配符开头 ('%abc…'),mysql索引失效会变成全表扫描的操作。

  • 罪魁祸首是%,不是LIKE,LIKE 条件是 type = range 级别
  • %xxx%:全表扫描
  • %xxx:全表扫描
  • xxx%:range

解决办法:

使用覆盖索引,可以由 ALL 变为INDEX,为啥呢?覆盖索引之后就能使用使用索引进行全表扫描。这里要注意一下,使用符合索引的时候,命中一个字段就可以,不用全部命中。

17. MySQL高效分页

存在SQL:SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M。其中 LIMIT N,M 存在的问题最大:取出N+M行,丢弃前N行,返回 N ~ N+M 行的记录,如果N值非常大,效率极差(表记录1500w,N=10000000,M=30 需要9秒)。

解决办法:SQL:SELECT id FROM ttl_product_info WHERE id > N LIMIT M,id 列是索引列,id > N属于 range 级别,效率自然高,然后从位置开始取30条记录,效率极高(表记录1500w,N=10000000,M=30,需要0.9毫秒)。

当然想要实现上述效果的前提是:

  • id是唯一索引,而且单调递增。
  • N 的值是上一次查询的记录的最后一条id,(需要前端保存一下,不能直接用传统的方法获得)
  • 不支持跨页查询,只能按照第1,2,3,4页这样查询逐页查询。

总结

知识都是在于平时的积累,养成良好的习惯,多阅读源码,年薪百万不是梦!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 从根上理解SQL的like查询%在前为什么不走索引?

    其实每天都有人私信我,如果遇到一些好的问题,我会拿来单独写文章的。比如,昨天就有人问我,like 查询 % 在前为什么不走索引?不能人云亦云,我们应该从根上理解...

    业余草
  • www.xttblog.com MySQL InnoDB 索引原理

    此小结与索引其实没有太多的关联,但是为了便于理解索引的内容,添加此小结作为铺垫知识。

    业余草
  • 一波骚操作,我把 SQL 执行效率提高了 10,000,000 倍

    原文链接:http://www.toutiao.com/a6730869910135636494/

    业余草
  • MySQL索引知识学习笔记

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    SmileNicky
  • 深入理解四种数据库索引类型(- 唯一索引/非唯一索引 - 主键索引(主索引) - 聚集索引/非聚集索引 - 组合索引)唯一索引/非唯一索引主键索引(主索引)聚集索引/非聚集索引5.组合索引(联合索引)

    1.唯一索引是在表上一个或者多个字段组合建立的索引,这个或者这些字段的值组合起来在表中不可以重复。

    desperate633
  • 稀疏索引和稠密索引你了解吗?

    最近参加了一个面试,面试官先问了mysql的数据库的索引的底层数据接口,我回答了:平时都用的是innodb引擎,所以其底层的索引数据类型是B+树。面试官问我用没...

    居士
  • Mysql索引

    Mysql官方对索引的定义为:索引(index)是帮助Mysql高效获取数据的数据结构。

    彼岸舞
  • 索引入门:顺序索引

    之前我对索引的了解基本就是主索引和二级索引,此外还经常见到一些其他概念,如聚集索引和非聚集索引,稀疏索引和密集索引等,今天系统整理一下。

    Apache IoTDB
  • 数据库中的联合索引

    用户1637228
  • linux一些基本命令与mysql索引

    在Linux中大部分的操作都是通过终端来实现的,或者直接使用命令行界面,一般使用Linux架构服务器的话,安装的都是命令行界面,不安装图形化界面的原因是因为没有...

    端碗吹水

扫码关注云+社区

领取腾讯云代金券