1. 首页
  2. IT资讯

面试官让我谈谈对mysql索引的认识,我是这么答的

“u003Cdivu003Eu003Cblockquoteu003Eu003Cpu003E转载:https:u002Fu002Fmp.weixin.qq.comu002Fsu002FfUPESYvyno3SNKC7vxeDBAu003Cu002Fpu003Eu003Cu002Fblockquoteu003Eu003Cpu003Eu003Cstrongu003E引言u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003E大家好,我渣渣烟。我曾经写过一篇《面试官:讲讲mysql表设计要注意啥》,当时写完后,似乎效果还行!u003Cu002Fpu003Eu003Cpu003E于是呢,决定再来一个mysql的数据库专题,这篇我们就来谈谈关于索引方面的mysql面试题。还是老规矩,讲的是在Innodb存储引擎下的情形,毕竟我还真没用过Mysiam之类的存储引擎。u003Cu002Fpu003Eu003Cpu003Eps:其实很早就想写了,一直偷懒!u003Cu002Fpu003Eu003Cpu003E其实这下面每个问题,我都可以讲一篇文章出来!而且这些问题,不是我凭空编的。如下图所示(注意看第三题)u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F2fdcab8dd70248abac421f3b76cba300″ img_width=”750″ img_height=”407″ alt=”面试官让我谈谈对mysql索引的认识,我是这么答的” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cp class=”ql-align-center”u003Eu003Cbru003Eu003Cu002Fpu003Eu003Cpu003E所以我回忆了一下,索引常见考点有哪些,总结成了这篇文章!u003Cu002Fpu003Eu003Cpu003E主要题目有下面这些u003Cu002Fpu003Eu003Culu003Eu003Cliu003E(1)你一般怎么建索引的?u003Cu002Fliu003Eu003Cliu003E(2)讲讲索引的分类?你知道哪些?u003Cu002Fliu003Eu003Cliu003E(3)如何避免回表查询?什么是索引覆盖?u003Cu002Fliu003Eu003Cliu003E(4)现在我有一个列,里头的数据都是唯一的,需要建一个索引,选唯一索引还是普通索引?u003Cu002Fliu003Eu003Cliu003E(5)mysql索引是什么结构的?用红黑树可以么?u003Cu002Fliu003Eu003Cliu003E(6)mysql某表建了多个单索引,查询多个条件时如何走索引的?u003Cu002Fliu003Eu003Cu002Fulu003Eu003Cpu003Eu003Cstrongu003E正文u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003Eu003Cemu003E1、你一般怎么建索引的?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003E烟哥注:曾记得有一个粉丝来找我的时候,出现如下搞笑一幕u003Cu002Fpu003Eu003Cblockquoteu003Eu003Cpu003E渣渣烟:”你这个简历上写了拥有SQL优化经验,你怎么建索引的?”u003Cu002Fpu003Eu003Cpu003E只见该粉丝嘿嘿一笑..说道:”就那样建啊…”u003Cu002Fpu003Eu003Cpu003E渣渣烟:”噢(第二声),就哪样建啊…”u003Cu002Fpu003Eu003Cpu003E粉丝:”…就网上说的那些索引规则啊”u003Cu002Fpu003Eu003Cpu003E渣渣烟:”那你怎么知道那些SQL出问题,需要建索引呢?”u003Cu002Fpu003Eu003Cpu003E粉丝:”我…..”u003Cu002Fpu003Eu003Cu002Fblockquoteu003Eu003Cpu003E嗯,这道题其实很基础。但是有没有做过,这题是可以看出来的。u003Cu002Fpu003Eu003Cpu003E去my.cnf里配置三个配置u003Cu002Fpu003Eu003Cpreu003E打开慢查询日志u003Cbru003Eslow_query_log=1u003Cbru003E慢查询日志存储路径u003Cbru003Eslow_query_log_file=u002Fvaru002Flogu002Fmysqlu002Flog-slow-queries.logu003Cbru003ESQL执行时间大于3秒,则记录日志u003Cbru003Elong_query_time=3u003Cbru003Eu003Cu002Fpreu003Eu003Cpu003E监控到慢SQL后,就马上开始建索引?u003Cu002Fpu003Eu003Cpu003ENO,NO,NO….这种时候,应该先考虑你的SQL能不能进行SQL优化。u003Cu002Fpu003Eu003Cpu003Eu003Cstrongu003E例如,当只要一行数据时使用 limit 1u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003E查询时如果已知会得到一条数据,这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引擎会在找到一条结果停止搜索,而不是继续查询下一条是否符合标准直到所有记录查询完毕。u003Cu002Fpu003Eu003Cpu003E然而大多数情况下,业务SQL十分复杂,没法优化。所以就要建立索引了。这个时候,参照如下规则建立索引u003Cu002Fpu003Eu003Culu003Eu003Cliu003E(1)索引并非越多越好,大量的索引不仅占用磁盘空间,而且还会影响insert,delete,update等语句的性能u003Cu002Fliu003Eu003Cliu003E(2)避免对经常更新的表做更多的索引,并且索引中的列尽可能少;对经常用于查询的字段创建索引,避免添加不必要的索引u003Cu002Fliu003Eu003Cliu003E(3)数据量少的表尽量不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果u003Cu002Fliu003Eu003Cliu003E(4)在条件表达式中经常用到不同值较多的列上创建索引,在不同值很少的列上不要建立索引。比如性别字段只有“男”“女”俩个值,就无需建立索引。如果建立了索引不但不会提升效率,反而严重减低数据的更新速度u003Cu002Fliu003Eu003Cliu003E(5)在频繁进行排序或者分组的列上建立索引,如果排序的列有多个,可以在这些列上建立联合索引。u003Cu002Fliu003Eu003Cu002Fulu003Eu003Cpu003Eu003Cemu003E2、讲讲索引的分类?你知道哪些?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003Eu003Cstrongu003E从物理存储角度:u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003E 聚簇索引和非聚簇索引u003Cu002Fpu003Eu003Cpu003Eu003Cstrongu003E从数据结构角度:u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003E B+树索引、hash索引、FULLTEXT索引、R-Tree索引u003Cu002Fpu003Eu003Cpu003Eu003Cstrongu003E从逻辑角度:u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Culu003Eu003Cliu003E主键索引:主键索引是一种特殊的唯一索引,不允许有空值u003Cu002Fliu003Eu003Cliu003E普通索引或者单列索引u003Cu002Fliu003Eu003Cliu003E多列索引(复合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合u003Cu002Fliu003Eu003Cliu003E唯一索引或者非唯一索引u003Cu002Fliu003Eu003Cliu003E空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。u003Cu002Fliu003Eu003Cu002Fulu003Eu003Cpu003Eu003Cemu003E3、如何避免回表查询?什么是索引覆盖?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003E这个问题,如果要看详细版,请参阅文章《Innodb中索引的原理》u003Cu002Fpu003Eu003Cpu003E这里简单说一下。u003Cu002Fpu003Eu003Cpu003E当能通过读取索引就可以得到想要的数据,那就不需要回表读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做索引覆盖。u003Cu002Fpu003Eu003Cpu003E例如此时有一张表table1,有一个联合索引(a,b)u003Cu002Fpu003Eu003Cpu003E执行如下SQLu003Cu002Fpu003Eu003Cpreu003Eselect a,b from table1u003Cbru003Eu003Cu002Fpreu003Eu003Cpu003E在索引上就能找到结果,就不用回表去查询!u003Cu002Fpu003Eu003Cpu003E而你执行的是u003Cu002Fpu003Eu003Cpreu003Eselect a,b,c from table2u003Cbru003Eu003Cu002Fpreu003Eu003Cpu003Ec列在索引上不存在,就需要回表查询。u003Cu002Fpu003Eu003Cpu003E需要说明的是覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引不存储索引列的值,所以mysql只能用B+ tree索引做覆盖索引。u003Cu002Fpu003Eu003Cpu003Eu003Cemu003E4、现在我有一个列,里头的数据都是唯一的,需要建一个索引,选唯一索引还是普通索引?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003E答唯一索引!u003Cu002Fpu003Eu003Cpu003E首先,在孤尽出的《阿里巴巴JAVA开发规范》中有这么一段话u003Cu002Fpu003Eu003Cblockquoteu003Eu003Cpu003Eu003Cstrongu003E【强制】业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003Eu003Cstrongu003E说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cu002Fblockquoteu003Eu003Cpu003E那好,下一问出现了!u003Cu002Fpu003Eu003Cpu003Eu003Cemu003E为什么唯一索引的插入速度比不上普通索引?为什么唯一索引的查找速度比普通索引快?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003E这个问题就要从Insert Buffer开始讲起了,在进行非聚簇索引的插入时,先判断插入的索引页是否在内存中。如果在,则直接插入;如果不在,则先放入Insert Buffer 中,然后再以一定频率和情况进行Insert Buffer和原数据页合并(merge)操作。u003Cu002Fpu003Eu003Cpu003E这么做的优点:能将多个插入合并到一个操作中,就大大提高了非聚簇索引的插入性能。u003Cu002Fpu003Eu003Cpu003EInnoDB 从 1.0.x 版本开始引入了 Change Buffer,可以算是对 Insert Buffer 的升级。从这个版本开始,InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。u003Cu002Fpu003Eu003Cpu003E唯一速度的u003Cstrongu003E插入u003Cu002Fstrongu003E比普通索引慢的原因就是:u003Cu002Fpu003Eu003Culu003Eu003Cliu003E唯一索引无法利用Change Bufferu003Cu002Fliu003Eu003Cliu003E普通索引可以利用Change Bufferu003Cu002Fliu003Eu003Cu002Fulu003Eu003Cpu003E于是乎下一问又来了!u003Cu002Fpu003Eu003Cpu003Eu003Cemu003E为什么唯一索引的更新不使用 Change Buffer?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003E因为唯一索引为了保证唯一性,需要将数据页加载进内存才能判断是否违反唯一性约束。但是,既然数据页都加载到内存了,还不如直接更新内存中的数据页,没有必要再使用Change Buffer。u003Cu002Fpu003Eu003Cpu003E最后回答一下,唯一索引的u003Cstrongu003E搜索速度u003Cu002Fstrongu003E比普通索引快的原因就是:u003Cu002Fpu003Eu003Culu003Eu003Cliu003E普通索引在找到满足条件的第一条记录后,还需要判断下一条记录,直到第一个不满足条件的记录出现。u003Cu002Fliu003Eu003Cliu003E唯一索引在找到满足条件的第一条记录后,直接返回,不用判断下一条记录了。u003Cu002Fliu003Eu003Cu002Fulu003Eu003Cpu003Eu003Cemu003E5、mysql索引是什么结构的?用红黑树可以么?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003E这个妥妥答最常见的B+ Tree。u003Cu002Fpu003Eu003Cpu003EAVL树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据数据存储的时候,显然不能将全部数据全部加载进内存,因此如果采用红黑树,就会造成频繁IO,效率低下。u003Cu002Fpu003Eu003Cpu003E那为啥不用B Tree,而选择B+ tree呢?u003Cu002Fpu003Eu003Cpu003E这就需要贴一下经典的两张图。B tree是长下面这样的u003Cu002Fpu003Eu003Cp class=”ql-align-center”u003Eu003Cbru003Eu003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F0e7b12b5ab904a2d94239e3fc5638efb” img_width=”1080″ img_height=”371″ alt=”面试官让我谈谈对mysql索引的认识,我是这么答的” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E注意一下B tree的两个明显特点u003Cu002Fpu003Eu003Culu003Eu003Cliu003E树内存储数据u003Cu002Fliu003Eu003Cliu003E叶子节点上无链表u003Cu002Fliu003Eu003Cu002Fulu003Eu003Cpu003E而B+ tree长下面这样的u003Cu002Fpu003Eu003Cp class=”ql-align-center”u003Eu003Cbru003Eu003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp3.pstatp.comu002Flargeu002Fpgc-imageu002F06b37fee2ae84c28aad8ce490480f1f0″ img_width=”1080″ img_height=”413″ alt=”面试官让我谈谈对mysql索引的认识,我是这么答的” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cp class=”ql-align-center”u003Eu003Cbru003Eu003Cu002Fpu003Eu003Cpu003E注意一下B+ tree的两个明显特点u003Cu002Fpu003Eu003Culu003Eu003Cliu003E数据只出现在叶子节点u003Cu002Fliu003Eu003Cliu003E所有叶子节点增加了一个链指针u003Cu002Fliu003Eu003Cu002Fulu003Eu003Cpu003E接下来就可以开始编了~~比如数据库索引采用B+ tree的主要原因是B Tree在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题,B+ tree应运而生。B+ tree只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,如果使用B Tree,则需要做局部的中序遍历,可能要跨层访问,效率太慢。u003Cu002Fpu003Eu003Cpu003E提示,我下一问就是:u003Cu002Fpu003Eu003Cpu003E你刚才说了这么多B tree不行,那你知道为啥Mongodb用B Tree当索引,而不用B+ Tree么?u003Cu002Fpu003Eu003Cpu003E(从关系数据库和非关系数据库的区别角度去答,不拓展了!仔细想想,在Mongodb里表示二者的关系,你会怎么处理!)u003Cu002Fpu003Eu003Cpu003Eu003Cemu003E6、mysql某表建了多个单索引,查询多个条件时如何走索引的?u003Cu002Femu003Eu003Cu002Fpu003Eu003Cpu003E其实,我看到这题的时候,内心一抖。这题让后端开发来答,真的很拼功底!u003Cu002Fpu003Eu003Cpu003E这里希望大家先看看我的另一篇文章《我是一条DQL》。此题在考优化器的知识!此题是在考察优化器如何抉择索引的!优化器会评估出走哪个索引最优,然后执行。u003Cu002Fpu003Eu003Cpu003EMysql在优化器中有一个优化器称为Range 优化器,负责进行范围查询的优化!u003Cu002Fpu003Eu003Cpu003E那么该优化器计算执行成本有两种方式index dive与index statistics。u003Cu002Fpu003Eu003Cpu003E它们是MySQL优化器对开销代价的估算方法,前者统计速度慢但是能得到精准的值,后者统计速度快但是数据未必精准。u003Cu002Fpu003Eu003Cpu003E坦白说写到这里,我内心痛哭流涕,要把index dive和index statistics写明白,真不是一件容易的事,这里只能稍微扯扯。u003Cu002Fpu003Eu003Cpu003E对于index dive:u003Cu002Fpu003Eu003Cpu003E计算成本的方式为u003Cu002Fpu003Eu003Cpreu003ECOST = CPU COST + IO COSTu003Cbru003Eu003Cu002Fpreu003Eu003Cpu003E其中CPU COST指的是处理返回记录所花的开销。而IO COST指的是读取页面的开销。u003Cu002Fpu003Eu003Cpu003Emysql会对每种索引的执行情况,进行上述成本计算,最后以成本小的方式进行执行。u003Cu002Fpu003Eu003Cpu003E但是呢,在某些情况下mysql执行index dive的成本太大。因此优化器会选择以index statistics方式进行估算成本。u003Cu002Fpu003Eu003Cpu003E具体如下:u003Cu002Fpu003Eu003Cpreu003ESHOW INDEX FROM tbl_name [FROM db_name] u003Cbru003Eu003Cu002Fpreu003Eu003Cpu003E此时出来的结果中,有一列名为Cardinality,该值表示索引列中不重复值的个数。u003Cu002Fpu003Eu003Cpu003E简单来说就是,索引列的唯一值的个数,如果是复合索引就是唯一组合的个数。u003Cu002Fpu003Eu003Cpu003E这个数值将会作为mysql优化器对语句执行计划进行判定时依据。如果唯一性太小,那么优化器会认为,这个索引对语句没有太大帮助,而不使用索引。u003Cu002Fpu003Eu003Cpu003ECardinality值越大,就意味着,使用索引能排除越多的数据,执行也更为高效。u003Cu002Fpu003Eu003Cu002Fdivu003E”

原文始发于:面试官让我谈谈对mysql索引的认识,我是这么答的

主题测试文章,只做测试使用。发布者:逗乐男神i,转转请注明出处:http://www.cxybcw.com/26494.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code