常见的数据库面试题

LQ521 · · 538 次点击 · · 开始浏览    
这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。

常见的数据库面试题 1.什么是索引,索引是什么样的数据结构?Mysql 常见的两种索引引擎? (1) 索引:索引是一种数据结构,可以帮助我们快速的进行数据的查找. (2) 数据结构:索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引. (3) 索引引擎:MyISAM 不支持事务,但是整个操作是原子性的(事务具备四种特性:原子性、一致性、隔离性、持久性) 不支持外键,支持表锁,每次所住的是整张表 MyISAM的表锁有读锁和写锁(两个锁都是表级别):表共享读锁和表独占写锁。在对MyISAM表进行读操作时,不会阻塞其他用户对同一张表的读请求,但是会阻塞其他用户对表的写请求;对其进行写操作时会阻塞对同一表读操作和写操作。 MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕! 一个MyISAM表有三个文件:索引文件,表结构文件,数据文件 存储表的总行数,执行select count(*) from table时只要简单的读出保存好的行数即可 (myisam存储引擎的表,count(*)速度快的也仅仅是不带where条件的count。这个想想容易理解的,因为你带了where限制条件,原来所以中缓存的表总数能够直接返回用吗?不能用。这个查询引擎也是需要根据where条件去表中扫描数据,进行统计返回的。) 采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致,但是辅索引不用保证唯一性。 支持全文索引和空间索引 对于AUTO_INCREMENT类型的字段,在MyISAM表中,可以和其他字段一起建立联合索引。 MyISAM的主索引图:索引文件的每个数据域存储指向数据文件的指针(每个索引指向了数据地址) MyISAM的辅助索引:索引文件的每个数据域存储指向数据文件的指针(每个索引指向了数据地址),辅助索引不用保证唯一性 (4) 索引引擎:Innodb 支持事务,支持事务的四种隔离级别;是一种具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。 支持行锁和外键约束,因此可以支持写并发。 不存储总行数;也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引 DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除 一个Innodb表存储在一个文件内(共享表空间,表大小不受操作系统的限制),也可能为多个(设置为独立表空间,表大小受操作系统限制,大小为2G),受操作系统文件大小的限制。 主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值;因此从辅索引查找数据,需要先通过辅索引找到主键值,再访问主键索引;最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。 2. Hash索引和B+树所有有什么区别或者说优劣呢? (1) B+ Tree B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。因此,B+树索引被广泛应用于数据库、文件系统等场景。 (2) hash 哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。 (3) 区别 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据; 从示意图中也能看到,如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索; 同理,哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询); 哈希索引也不支持多列联合索引的最左匹配规则; B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。 在HEAP表中,如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希索引。例如这种SQL:SELECT … FROM t WHERE C1 = ?;仅等值查询。 Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。虽然 Hash 索引效率高,但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,代表数据库:redis、memcache等。 既然Hash 索引的效率要比 B-Tree 高很多,为什么大家不都用 Hash 索引而还要使用 B-Tree索引呢?任何事物都是有两面性的,Hash 索引也一样,虽然 Hash 索引效率高,但是 Hash索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。 Hash索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。 Hash 索引无法被用来避免数据的排序操作。由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值,而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算。 Hash索引不能利用部分索引键查询。对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。 Hash索引在任何时候都不能避免表扫描。前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。 Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。 3. 什么是聚簇索引和非聚簇索引? 数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引,聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。 聚集索引 和 辅助索引区别:叶子节点存放的是否是一整行的信息。 聚集索引 聚集索引 ( clustered index ) 按照每张表的主键构造一棵 B+树,同时叶子节点存放的为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。由于实际的数据页只能按照一棵B+树进行排序,所以每张表只能拥有一个聚集索引。查询优化器倾向于采用聚集索引。聚集索引能在B+树索引的叶节点上直接找到数据,是由于定义了数据的逻辑顺序。聚集索引适用于针对范围值的查询。优点:对于主键排序查找和范围查找速度非常快。 辅助索引 ( 非聚集索引 ) 辅助索引 ( secondary index ) ,叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签 ( bookmark ) 。 该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。每张表上可以有多个辅助索引,通过辅助索引查找数据时, InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,再通过主键索引找到完事 的行记录。 4. 聚簇索引和非聚簇索引哪种查询速度更快? 聚簇索引查询更快聚簇索引树的叶子节点直接就是我们要查询的整行数据了。而非聚簇索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再回表进行一次查询(除覆盖索引,覆盖索引只用一次)。 覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。如,表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句:select key2 from covering_index_sample where key1 = 'keytest';的时候,就可以通过覆盖索引查询,无需回表。 5. 什么是回表? 回表就是先通过数据库索引扫描出数据所在的行,再通过行主键id取出索引中未提供的数据,即基于非主键索引的查询需要多扫描一棵索引树。 第4个问题里面有答案。 6. 创建索引会考虑哪些因素? MySQL 表主键设计,INNODB 以主键排序存储;聚集索引只能是主键;存储所有数据;二级索引包含主键键值。如果表没有定义主键,会选择第一个唯一索引(非空)作为聚集索引主键。如果唯一索引也没有,MySQL后台会自动生成RowID。字符类型字段最好不要做主键;常见的主键有两种:自增列和UUID。自增: 顺序存储,索引维护成本低,索引效率高;UUID:非顺序增长,随机IO严重。 索引并不是越多越好,要根据查询,有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是使用了索引还是全表扫描。 应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描。 值分布很稀少的字段不适合建索引,例如“性别”这种只有两三个值的字段。 不用外键,由程序保证约束。 尽量不用UNIQUE,由程序保证约束。 使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引。 排序时,排序字段需要注意index, 尤其是关联查询排序时,尽可能使用小表的字段进行排序。 7 什么是mysql最左前缀原则? 在创建多列索引时(联合索引),我们根据业务需求,where子句中使用最频繁的一列放在最左边,因为MySQL索引查询会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候,如(key1,key2,key3),相当于创建了(key1)、(key1,key2)和(key1,key2,key3)三个索引,这就是最左匹配原则。

有疑问加站长微信联系(非本文作者))

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:692541889

538 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传