MySQL面试知识点追命连环问(二)事务、索引及SQL优化

时间:2020-03-11 来源: 热点专题

目录

1。MySQL事务

2。MySQL索引

3。SQL优化

4。常见问题?上次我们讨论了MySQL的操作过程和原理、字段设计、存储引擎和查询缓存。

MySQL访谈知识点系列问题(1)

这一次我们将继续提问关于事务、索引、SQL优化和其他相关内容的系列问题。你准备好了吗?

Transaction

Index

FAQ

FAQ

1。采访者:你知道这笔交易吗?

1。MySQL事务

一个事务可能包含一个或多个已执行或未执行的sql语句。

事务有四个主要特征。ACID:原子性、一致性、隔离性和持久性。

原子性:一个不可分割的操作单元,其中事务中的所有操作要么成功。要么回滚到事务执行前的状态。

一致性:在事务开始之前和事务结束之后,数据库的完整性约束不会被破坏。

isolation:事务操作相互独立且透明,互不影响。如果一项交易的结果影响到其他交易,其他交易将被撤销。

持久性:一旦事务被提交,结果就是永久的。即使发生系统故障,也可以恢复。

采访者:嗯,这四个特征是对的。您知道在高并发情况下事务中可能出现的问题吗?

me:如果事务同时执行,会有一些问题。例如:魔法阅读、肮脏阅读、不重复阅读。因为孤立的肮脏书写不会发生。

dirty read:一个事务读取由另一个未提交的事务修改的数据。

对于会话A,在会话回滚之前读取脏数据

不能重复读取:多次读取的数据内容不同。

魔力阅读:前后阅读多次,数据总量不同。

采访者:在什么情况下会出现这些问题?

MySQL标准定义了四个隔离级别,并指定在每个隔离级别下是否存在上述问题。

一般来说,隔离级别越低,系统开销越低,支持的并发性越高,但是隔离越差。隔离级别和读取问题之间的关系如下:

read uncommitted:脏读取,不可重复读取,幻影读取可以发生

read committed:不可重复读取,幻影读取可以发生

repeatable read:幻影读取可以发生

serializable:不能发生

在实际应用中,未提交的读取在并发时会导致许多问题,但是与其他隔离级别相比,性能提高是有限的,因此使用较少。

Serializable强制事务是串行的,并发效率低。它仅在数据一致性要求非常高并且不存在并发性是可以接受的情况下使用,因此它的使用也较少。

因此,在大多数数据库系统中,默认的隔离级别是提交读(如Oracle)或可重复读。

MySQL事务的默认隔离级别是可重复读取,MySQL可以解决幻影读取的问题。

采访者:看来你对事物有很好的理解。你知道MySQL的另一个重要特征索引吗?

2。索引是数据库管理系统中的一种数据结构,用来帮助快速查询和更新数据库表中的数据。索引的实现通常使用B树及其变体B树。

除了数据,数据库必须为维护这些快速找到的索引付出代价。一个是增加数据库的存储,另一个是在插入和修改数据时花费更多的时间(因为索引也会改变)。

2。MySQL索引

A:不一样。MyISAM和Innodb都使用B树作为索引结构,但是索引的实现仍然不同。

MyISAM的叶节点的数据字段存储数据记录的地址,而Innodb数据文件本身就是索引文件。

MyISAM中的索引检索算法是先根据B树搜索算法搜索索引。如果指定的键存在,则取出其数据字段的值,然后用数据字段的值作为地址读取相应的数据记录。

MyISAM Index

InnoDB中,表数据文件本身是由B树组织的索引结构,该树的叶节点数据字段保存完整的数据记录。该索引的键是数据表的主键,因此InnoDB表数据文件本身就是主索引。

0

InnoDB中,表数据文件本身是由B树组织的索引结构,该树的叶节点数据字段保存完整的数据记录。该索引的键是数据表的主键,因此InnoDB表数据文件本身就是主索引。

因为InnoDB的数据文件本身需要通过主键进行聚类,所以InnoDB要求表必须有一个主键(MyISAM不能)。

如果没有明确指定,MySQL将自动选择一个唯一标识数据记录的列作为主键。如果不存在这样的列,MySQL将自动为InnoDB表生成一个隐式字段作为主键。

B-tree是B-tree的变体,它平衡有序数组链表的多分支树。基本上类似于B树,只有叶节点存储数据,叶节点通过指针连接。

采访者:那为什么用二叉树做索引?B树有什么优点?

1 、B树的磁盘读写开销较低:B树的内部节点没有指向特定关键字信息的指针,因此其内部节点比B树小。如果同一个内部节点的所有关键字都存储在同一个磁盘块中,则该磁盘块可以容纳的关键字越多,一旦读入内存就需要搜索的关键字越多,并且相对的IO读写时间越短。

2。由于B树的数据存储在叶节点中,而分支节点是索引,所以扫描数据库很方便,只需扫描一次叶节点。但是,由于B树的分支节点也存储数据,我们需要通过一个中间顺序的遍历顺序扫描来找到具体的数据,所以B树更适合于区间查询的情况,所以B树通常用于数据库索引。

采访者:什么是聚集索引?

集群索引是一种数据存储方法。它实际上以相同的结构保存了B树索引和数据行。InnoDB表是根据集群索引组织的。

InnoDB通过主键聚集数据。他使用主键值的大小来对记录和页面进行排序。叶节点存储完整的用户记录。

注意:聚集索引不需要如图所示创建。它是由InnoDB存储引擎自动为我们创建的。如果没有主键,默认情况下它也会创建一个主键。

但是,聚集索引只有在搜索条件是主键的情况下才有效。如果不是其他字段,此时需要正常索引。

辅助索引的叶节点不再是完整的数据记录,而是一个字段和主键值。当需要该记录的其他字段时,仍然需要根据该主键id进行查询。这一步叫做返回表。

聚簇索引表最大限度地提高了I/O密集型应用程序的性能,但它也有以下局限性:

插入速度在很大程度上取决于插入顺序,按照主键的顺序插入是最快的方式,否则会发生页面分裂,严重影响性能。因此,对于InnoDB表,我们通常定义一个自增ID列作为主键。

更新主键代价很高,因为这会导致更新的行移动。因此,对于InnoDB表,我们通常将主键定义为不可更新的。

辅助索引访问需要两次索引搜索,第一次搜索主键值,第二次搜索根据主键值的行数据。

采访者:索引的类型是什么?指数越多越好?

除了上面提到的主键索引和公共索引,还有唯一索引、联合索引和全文索引。

唯一索引:此列是唯一的,同时也是索引。不允许复制。

全文索引:主要用于文本查询。它的出现是为了解决模糊查询在文本上效率低的问题,如像“%字%”这样的名字。

联合索引:多列值索引比索引合并更有效。必须遵循前缀原则。

建立索引很昂贵,所以越多越好,只要你在你需要的字段上建立索引。

首先,创建和维护索引需要时间,索引会随着数据量的增加而增加。

其次,索引需要占用物理空间。除了数据表占用数据空间外,每个索引还需要占用一定量的物理空间。如果要建立聚集索引,所需的空间将会更大。

第三,在添加、删除和修改表中的数据时,索引应该是动态维护的,这样会降低数据的维护速度。使用

index时应注意以下几点:

1。最左边前缀原则。联合指数(a,b,c)。如果有一个带有A和B的查询条件,那么他将采用索引。如果有一个查询条件没有A,那么他就不会取这个索引。

2。使用唯一的索引。具有多个重复值的列的索引效果最差。

3.不要超过指数。每个额外的索引都会占用额外的磁盘空间,并降低写入性能。修改表的内容时,索引必须更新,有时可能需要重新构建。因此,索引越多,所需时间越长。

4。索引列不能参与计算并保持列“干净”。例如,不能使用from _ unix时间(create _ time)=' 2014-05-29 '索引。原因很简单。存储在B树中的所有字段都是数据表中的字段值。然而,当搜索时,所有的元素都需要用函数来比较,这显然花费太多。因此,该语句应该编写为create _ time=UNIX _ timestamp(“2014-05-29”);

5。务必设置一个主键。早些时候,聚集索引说如果没有指定主键,InnoDB会自动为它指定一个主键,我们看不到。无论如何,我们必须生成一个主键。它不如我们的环境好。我们还可以在将来的一些搜索条件中使用主键的聚集索引。

6。建议主键使用自增id,而不是uuid。上面的聚集索引表示对每一页数据都进行了排序,并且页面也进行了排序。如果使用uuid,那么它必须是随机的,可以从中间插入,导致页面分裂,产生大量的表片段。如果它是自增加的,那么它从小到大增加并且有一个序列,然后在插入时被添加到当前索引的后续位置。当页面已满时,新页面将自动打开。

索引禁忌:

不是建立在低差异的列上?索引,如“gender”

尽量避免% leading查询,如“like”

尽量避免否定查询,如not in/like

避免全表扫描和频繁的表返回操作

访问者:看起来您对索引有很好的掌握,您如何优化慢速查询?

3。SQL优化

SQL语句通过网络协议从客户端进入查询缓存。如果缓存中没有命中,则通过解析获得确切的SQL,然后将其提交给优化器。

首先,我们知道每个SQL都有不同的执行方法,要么通过索引,要么通过全表扫描。

SQL速度主要受输入/输出成本和CPU成本的消耗影响。

数据存储在硬盘上,我们需要将其加载到内存中进行一些操作。这个过程的时间称为输入/输出成本。在内存中对结果集进行排序所花费的时间称为CPU开销。

对于sql优化,应该首先进行索引优化,这样我们的sql语句应该使用索引而不是全表扫描。

当遇到慢速查询时,首先分析慢速查询日志,找出慢速查询的sql。然后分析这些sql。常见的慢速查询包括以下内容:

index不起作用。该字段没有索引,或者索引不起作用。使用like关键字或多列索引的查询语句。

数据库结构不合理。合理的数据库结构不仅可以使数据库占用更小的磁盘,而且可以使sql执行更快。首先,具有许多字段的表可以分解成多个表。其次,添加一个中间表。

反汇编相关查询。将大查询分成多个小查询。

优化极限分页。当偏移量非常大时,先前查询的无用数据将被丢弃。如果表非常大,并且筛选的字段没有适当的索引,那么成本非常高。如果我们的下一个查询可以在前一个查询结束后从标记的位置开始,将会节省很多开销。

4。常见问题

问题1:现在,让我们来看看具体的问题。你认为这个声明会使用索引吗?

以下语句是否适用于索引:从用户中选择2007年(添加日期);

A:不,因为只要列涉及操作,MySQL就不会使用索引。

问题2:如果列值为空,查询会使用索引吗?MySQL中带有空值的

列也会被索引。当然,如果您计划索引一个列,请尽量避免将其设置为可空。MySQL很难优化引用可空列的查询,这将使索引、索引统计和值更加复杂。

问题3:索引一定会加速吗?

一般来说,按索引查询数据比扫描整个表要快。但是我们也必须注意它的成本。

索引需要存储空间,需要定期维护。每当在表中添加或删除记录或修改索引列时,索引本身都会被修改。这意味着每条记录的INSERT、DELETE、UPDATE UPDATE将多支付4到5倍的磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会降低查询响应时间。使用索引查询不一定能提高查询性能。

问题4:我如何找到第n高的薪水?

问题5:一个6亿的表A和一个3亿的表B,如何通过外部的tid关联,从到的200条数据记录中找出最符合条件的?

1。如果表A的TID是自增和连续的,而表B的标识是索引

2,如果表A的TID不是连续的,则需要一个覆盖索引。TID要么是主键,要么是辅索引,而表号也需要一个索引。

好了,今天关于猎命师的系列问题到此结束。下次继续。如果您对本文有任何疑问或补充,请留言(●?)●)1 .创意并不容易。如果这对你有帮助,请表扬它!

??????相关推荐阅读????你知道一个完整的HTTP请求过程吗????Redis经常连续提问。你能回答哪个问题?(上)

????大工厂高频面试问题:高并发下接口幂等性的解决方案?

????

新闻排行
  1.   《国学》是中华民族的传统文化,蕴含着民族精神和民族情感,承载着中华民族的思想精华。为传承和弘扬中

      《国学》是中华民族的传统文化,蕴含着民族精神和民族情感,承载着中华民族的思想精华。为传承和弘扬中...

  2. 统计数据显示,本周国外三元猪肉的价格一直在波动。上周一,生猪价格继续下跌,全国许多省份的生猪价格下跌

    统计数据显示,本周国外三元猪肉的价格一直在波动。上周一,生猪价格继续下跌,全国许多省份的生猪价格下跌...

  3. 着力提升专业能力加强干部队伍建设社区事务受理服务中心,以“党员三个先例”工作方法为指导,推进党员示范

    着力提升专业能力加强干部队伍建设社区事务受理服务中心,以“党员三个先例”工作方法为指导,推进党员示范...

  4. 12月9日,亚足联正式公布了2019年各协会技术得分排名。在这次排名中,中国以100分领先,这也意味着中国将继

    12月9日,亚足联正式公布了2019年各协会技术得分排名。在这次排名中,中国以100分领先,这也意味着中国将继...

  5. 对大学教师来说,被排除在职业规划之外也是国家对教育产业重视的表现。你为什么这么说?有必要知道,一所大

    对大学教师来说,被排除在职业规划之外也是国家对教育产业重视的表现。你为什么这么说?有必要知道,一所大...

  6. 根据目前1600万新生儿和近700亿奶粉的市场规模,考虑到各种因素,第二个孩子的完全自由化带来的市场增量至?

    根据目前1600万新生儿和近700亿奶粉的市场规模,考虑到各种因素,第二个孩子的完全自由化带来的市场增量至?...

  7.   “老书记,你连水都没喝一口,却费尽口舌为我们化解宅基地矛盾,我同意你的调解意见。”7月16日,湖南?

      “老书记,你连水都没喝一口,却费尽口舌为我们化解宅基地矛盾,我同意你的调解意见。”7月16日,湖南?...

  8. 双鱼座爱最高的双鱼座,只要他们能拥有彼此的爱,他们愿意交换一切。在情感世界里,盲目地去爱。爱会迷失自

    双鱼座爱最高的双鱼座,只要他们能拥有彼此的爱,他们愿意交换一切。在情感世界里,盲目地去爱。爱会迷失自...

  9. 锘夸腑鍏遍儜宸炲竞濮旗綉缁滀俊鎭姙鍏镊村竞鍐呯綉鍙嬬殑寤鸿鍑戒粖镞ラ儜宸炵綉缁滀俊鎭綉鍙嬩

    锘夸腑鍏遍儜宸炲竞濮旗綉缁滀俊鎭姙鍏镊村竞鍐呯綉鍙嬬殑寤鸿鍑戒粖镞ラ儜宸炵綉缁滀俊鎭綉鍙嬩...

  10. 南湖网讯(通讯员于洪七学角)12月11日下午,华中科技大学郭小萍教授参观我校文法学校,在第二教育系205届以?

    南湖网讯(通讯员于洪七学角)12月11日下午,华中科技大学郭小萍教授参观我校文法学校,在第二教育系205届以?...

友情链接