首页>软件资讯>行业资讯

行业资讯

MySQL连环15问,你能坚持到哪个问题?

发布时间:2022-09-21 15:32:50人气:237

  文章目录


  前言


  一、关系型和非关系型的区别,以及使用场景


  二、Mysql索引优缺点


  三、给字段加索引最好怎么加?


  四、什么情况下会导致索引失效?


  五、为什么使用模糊匹配会使索引失效


  六、回表查询和索引覆盖是什么


  七、联合索引的好处是什么


  八、MySQL怎么判断走索引还是全表扫描


  九、Explain语句结果中各个字段分别表示什么


  十、Mysql慢查询该如何优化?


  十一、左匹配,右匹配,innerjoin说一下区别


  十二、你对使用外键怎么看?


  十三、说一下脏读、不可重复读以及幻读是什么


  十四、说说select...这个查询语句,在mysql查询的过程


  十五、说说redolog和undolog日志的过程


  前言


  本篇文章中列出的有关MySQL的问题,都是作者在平时学习中遇到或者面试中被问到的问题,针对每个问题作者都做了自己的梳理和总结,整篇文章内容很丰富,希望能给读者带来实际的帮助。


  一、关系类型与非关系类型的区别,以及使用场景


  关系数据库:利用关系模型组织数据的数据库。关系模型是二维表模型。二维表的名称是关系,二维表的一行是记录,二维表的一列是字段


  优点:易于理解,易于使用,通用,sql语言,易于维护,完整性丰富(物理完整性,参考用户定义的完整性和完整性)


  缺点:磁盘I/O这是并发的瓶颈。海量数据查询效率低,难以水平扩展。仅通过添加硬件和服务节点来扩展性能和负载能力是不可能的。当需要升级和扩展数据库时,需要停止维护和数据迁移、多表相关查询和复杂数据分析类型sql查询,性能差。因为要保证。ACID.


  非关系数据库:分布式,一般不保证遵循ACID数据存储系统的原理。结构不固定,结构不固定。


  优点:结构简单易扩展,数据模型高性能灵活


  缺点:只适合存储一些简单的数据,不适合复杂的查询数据,不适合长期存储大量的数据


  因此,适合存储相对简单的数据。有些数据不能持久,因此需要与关系数据库结合。

  二,Mysql索引优缺点


  索引优点


  1.有助于加快数据检索,减少数据库I/O成本也是创建索引的主要原因。


  2.通过创建独特性索引,可以保证数据库表中各行数据的独特性


  3.加速表与表之间的连接,实现数据参考的完整性尤为有意义。


  4.在使用分组和排序子句进行数据检索时,也能显著减少查询中分组和排序的时间,减少查询中分组和排序的时间CPU的消耗。


  索引缺点


  1.创建和维护索引需要时间,当数据量增加时更明显


  2.索引需要占用物理空间


  3.增加表中的数据,删除和修改索引,动态维护,改进CPU的消耗


  三、给字段加索引最好怎么加?


  建议创建索引


  1.根据查询要求建立索引:查询频率高、实时要求高的字段应创建索引,如主键、外键、经常需要连接查询字段、排序字段、查询指定范围的字段。


  2.大数据表应创建索引


  不建议创建索引。


  1.很少引用数据表查询,大量重复的字段不应创建索引。


  2.数据量非常小的数据表,索引可以提高其数据访问效率,无需创建索引。


  3.基本表不应建立太多索引,当数据表被添加或删除时,索引也会发生变化。索引需要占用文件目录和存储空间,并需要维护,过多会增加系统的负担。


  四、在什么情况下会导致索引失效?


  1.组合索引最左前缀原则:使用组合索引列为条件时,必须出现最左列为条件,否则索引无效


  2.如有条件or,即使有条件带索引也不会使用(这就是为什么要尽量少用(这就是为什么要尽量少用or的原因)


  3.like查询以%开头,比如%张三,%张三


  4.字符串类型的字段被引入int索引在类型参数中失效,而索引则失效,int字符串类型的字段不会失效。


  例如,手机号码是字符串类型,传入13057900876,即int类型的变量会导致索引失效。


  根据mysql官网解释说,字符串1a都可以转换成int类型1,也就是说可能有多个字符串对应一个字符串int类型参数的情况,那么,mysql如何知道该把int类型1转换成哪个字符串,用哪个索引快速查值?所以索引会失效。


  5、为什么使用模糊匹配会使索引失效?


  索引实际上是排序,或者排队更直观。


  like张三%,其实你要找的是张三%XXX只要把张三开头的所有内容都回来,这部分就是连续的,不需要全表扫描。


  like实际上你要找的是‘%张三’,XXX张三,这部分在索引中是不连续的,如果要返回所需的结果,只能全表扫描。


  6、什么是回表查询和索引覆盖?


  InnoDB收集索引的叶节点存储记录,InnoDB必须有,只有一个聚集索引:如果表定义了主键(一般)ID自增),主要是聚集索引;


  InnoDB普通索引的叶节点存储主键值。


  回表查询:根据普通索引叶片节点上的主键值,重新定位主键索引树上的行记录。


  比如你给学生的学号添加了唯一的索引,但是你现在执行这个sql:selectname,snofromstudentwheresno=123;因为name字段在学号索引树上不存在,需要拿着学号索引树上的主键值在主键索引树上找名字,这是一个回表查询。


  索引覆盖是⼀个SQL在执行过程中,可以使用索引快速查找,并且这样,SQL当前索引对所需查询的字段


  所有的字段都包含在应该的字段中,这意味着这一点SQL索引完成后,无需返回表格。所需的字段都在当前索引的叶片中⼦节


  点上存在,可直接作为结果返回。


  七、联合索引有什么好处?


  减少索引建立的费用。建立了一个建议。(a,b,c)复合索引,那么实际上就等于建了(a),(a,b),(a,b,c)三个索引,因为每个索引,都会增加写作操作和磁盘空间的成本。对于大量的数据表,这是一笔不小的开支!


  索引覆盖。同样有复合索引。(a,b,c),如下所示selecta,b,cfromtablewherea=1andb=1,**那么MySQL数据可以直接通过遍历索引引直接获取数据。**在实际应用中,索引覆盖是提高性能的主要优化手段之一


  减少扫描行数。有1万人。W数据表如下select*fromtablewherea=1andb=2andc=3.假设每个条件都能筛选出10%的数据,如果只有单值索引,那么通过这个索引可以筛选出1万个数据W*10%=100w条数据,然后从100回表w在条数据中找到符合b=2andc=3数据;如果是复合索引,可以通过索引筛选出1万个索引w*10%*10%*10%=1w,然后回到表。


  八,MySQL如何判断走索引还是全表扫描


  当我们查询索引字段时,有时我们会发现我们没有去索引,而是去扫描整个表格,因为MySQL发现走全表扫描会比走索引快,所以选择了全表扫描。


  比如你走了联合索引,找到了七个符合条件的记录,然后进行七次回表查询,mysql我觉得与其先走索引再多次查询,不如直接扫描整个表,所以虽然用索引,但还是扫描整个表。


  小结:mysql会计算成本,使用索引的成本是多少,全表扫描的成本是多少,如果使用索引但回表太多,去全表扫描


  九,Explain语句结果中的每个字段表示什么


  几个重要字段:


  1.possible_keys:代表可能使用的索引


  2.key:实际使用的索引


  3.key_len:实际使用索引的长度


  4.type显示查询使用哪种类型,type包含的类型包括下图所示的类型:


  从最好到最差的依次是:


  [system>const>eq_ref>ref>range>index>all


  1.system:表中只有一行记录(等于系统表)const平时不会出现类型的特列,这也可以忽略不计


  2.const:单表中最多只有一个匹配行(主键或唯一索引),在优化阶段可以读取数据


  3.eq_ref:对于每个索引键,表中只有一个记录与之匹配。主键或唯一索引扫描很常见


  4.ref:普通索引


  5.range:范围查询,一般在你的范围内,where语句中出现between,<,>,in等的查询,


  6.index:通历索引树。这通常比较比较。ALL快,但也没不到哪里去。


  7.all:全表扫描,最差的情况]


  5.Extra:非常重要的额外信息


  6.Usingindex:表示相应的select覆盖索引用于操作(CoveringIndex)


  7.Usingwhere:表明使用了where过滤


  十,Mysql如何优化慢慢查询?


  检查是否有索引,如果没有,优化SQL利用索引。


  检查所使用的索引是否为最优索引。


  检查所查字段是否必要,是否查询过多字段,查出过多数据。


  检查表中数据是否过多,是否应进行分库分表。


  检查数据库实例所在机器的性能配置,是否过低,是否能适当增加资源。

  

  十一.左、右、innerjoin说一下区别


  区别:


  1.left以leftjoin左表为主表,左表中的记录会出现在查询结果中。如果右表没有匹配记录,则以右表为主表。null填充。


  2.right以rightjoin右表为主表,记录会出现在查询结果中,如果左表没有匹配记录,则以右表为主表,记录会出现在查询结果中。null填充。


  3.innerjoin找到的数据是左右表共有的


  什么是小表驱动大表?


  小表驱动大表是指用小数据集驱动大数据集。


  1.当使用leftjoin左表为驱动表,右表为驱动表;


  2.当使用rightjoin右表时驱动表,左表为驱动表;


  3.当使用innerjoin时,mysql将选择数据量较小的表作为驱动表,大表作为驱动表;


  例如:现有两个表A和B,表A有200条数据,表B有20万条数据:


  小表驱动大表


  for(200条){


  for(20万条){


  …


  }


  大表驱动小表


  for(20万){


  for(200条){


  …


  }


  总结:


  1.如果小循环在外层,表连接只连接200次;


  2.如果大循环在外层,则需要进行20万次表连接,从而浪费资源,增加消耗;


  综上:


  小表驱动大表的主要目的是减少表连接创建的次数,加快查询速度。


  十二.你觉得使用外键怎么样?


  使用外键的原因


  1.外键保证数据的完整性和一致性,不会孤立行。


  2.能得到好的“级联删除,级联更新”行为,自动清理表


  3.将数据完整性判断委托给数据库,减少程序代码量


  4.外键提供了一个非常重要的提示,说明在数据库中收集哪些统计信息是最重要的。


  不使用外键的原因


  1.数据库需要维护外键的内部管理。


  2.外键等于实现数据的一致性事务,全部交给数据库服务器完成,使数据库在每个数据库中CRUD所有的操作都是额外的,因为它必须检查外键的一致性,这消耗了大量的资源。如果大量更新,那就更痛苦了。


  3.通过强制关系,外键指定了您必须添加/删除内容的顺序。例如,如果学生关联订单,他们必须先删除订单数据,然后删除学生数据。


  4.外键也容易死锁,因为需要要求锁定其他表内部。


  例如,外键约束带来的费用还包括锁定费用,例如向子表中添加数据,外键约束允许外键约束使用innodb检查相应的父表。这里需要锁定父表,以确保该记录在事务完成前不会被删除。这带来了额外的锁费。甚至是死锁。


  十三.说一下脏读.什么是不可重复读和幻读?


  1.脏读:事务A读取事务B更新的数据,然后B回滚操作,那么A读取的数据就是脏数据。


  2.不能重复阅读:事务A多次阅读相同数据,事务B在事务A多次阅读过程中更新并提交数据,导致事务A多次阅读相同数据,结果不一致。


  3.幻读:系统管理员A将数据库中所有学生的分数从具体分数改为ABCDE等级,但系统管理员B此时插入了一个具体的分数记录。当系统管理员A改变时,他发现还有一个记录没有改变,就像幻觉一样,这被称为幻觉阅读。


  总结:不可重复阅读和幻读容易混淆,不可重复阅读侧重于修改,幻读侧重于添加或删除。解决不可重复阅读的问题,只需锁定符合条件的行,解决幻读需要锁表


  注意:在innodb在存储引擎下,引入mvvc之后,幻读问题几乎得到了解决。


  十四.说说select…这个查询语句,在mysql查询的过程


  mysql分为server层与存储引擎层,server层层包含连接器.分析器.优化器.执行器。


  接下来sql查询语句执行过程,介绍各部分功能。客户端执行一条sql:


  1.连接器:连接到数据库、身份验证、权限管理


  2.分析器:执行前,MySQL一定要知道你想做什么,先进行词法分析,识别关键词,然后进行语法分析,看看你SQL语句语法是否有错。


  3.优化器:通过分析器,我们知道了SQL需要做什么,但直接根据需要做什么,SQL获得结果可能会消耗很多性能,因此需要通过优化器进行优化。生成执行计划。.选择索引等操作,选择最佳执行方案


  4.执行器,打开表调用存储引擎接口,逐行判断是否符合查询条件,放入结果集,最后返回客户端;如果使用索引,筛选行也会根据索引进行筛选。


  十五.说说redolog和undolog日志的过程


  redolog功能:确保事务的持久性。防止在故障发生时,仍有脏页未写入磁盘,重启时重启mysql服务时,根据redolog重做,从而达到事务的持久性。


  内容:物理格式日志,记录物理数据页面的修改信息,其内容:物理格式日志,记录redolog是顺序写入redologfile物理文件。什么时候产生:事务开始后产生redolog,redolog落盘不是随着事务的提交而写入的,而是在事务的执行过程中,开始写入redolog文件中。


  undolog功能:如果事务因某些原因失败或回滚,可以使用undo进行回滚。


  内容:可以认为是当delete记录时,undolog中会记录一个对应的insert记录,反之亦然,当update记录时,它记录一条对应相反的update记录。执行时rollback时间,可以从undolog逻辑记录读取相应内容并回滚。


  总结


  作为JAVA开发程序员,与数据库打交道是必不可少的。MySQL作为目前流行的数据库,它已广泛应用于企业的实际发展中,在面试中也占有很大比例,必须熟练掌握。

  

文章转自网络


上一条:Windows Server 2022现在开始支持WSL2 Linux发行版

下一条:中国工业报:开源开放平台加工业控制助力数字化转型