数据库面试题总结

面试题总结：

1、海量数据下，如何根据执行计划，调优sql

EXPLAIN SELECT * FROM dept

2、mysql索引体系如何应对海量数据存储

• 磁盘预读：磁盘和内存进行交互的时候有一个最基本的逻辑单位，叫做页，也称为dataPage,一般情况下是4k或者8k

• hash:
  mysql中是有hash索引的，只不过适用于memory存储引擎，innodb存储引擎支持自适应hash

  • 优点:
    将数据散列，查找速度快
  • 缺点：
    hash冲突，hash碰撞
    无法进行范围查找

• tree
  二叉树、BST、AVL、红黑树
  共同点：做多只有两个分枝
  后面三个：有序
  后面两个：平衡
  这些树随着数据量的增加，会导致树的高度变高，那么会降低io的效率
  一般三到四层就能支持千万级别的存储，key值占用的空间越小越好

• id在满足业务场景的境况下，要不要自增：
  要 重新索引排序，分裂

3、海量数据下，如何设计性能优良的mysql

4、mysql的聚葱索引非聚葱索引：

innopdb存储引擎在进行数据插入的时候，数据值必须要跟某一个索引字段绑定在一起，如果表有主键，那么使用之间，如果没有主键，那么选择唯一键，系统回自动生成一个6字节的rowid来供数据使用。
一个表可以有多少个索引：
理论上不限制个数
一个索引对应一棵B+树，还是所有索引共用B+树
一个索引一颗B+树

5、回表：

6、索引覆盖：

7、索引下推：

满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。
在mysql5.6之前，只能从id开始一个个回表，到主键索引上找出数据行，再对比字段值。
在mysql5.6之后的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

谓词下推：先查询数据，再进行筛选

8、最左匹配：

• 最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
• mysql会一值向右匹配直到遇到范围查询（>、<、between、like）就停止匹配
• =和in可以乱序，比如a=1 and b=2 and c=3建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

9、mysql架构：

10、mysql的log:

• binlog 进行主从同步 server层
• redolog 预写日志 innodb
• undolog 事务回滚和mvcc innodb
• errorlog 错误日志 server
• relaylog 中继日志 server
• slowlog 慢日志 server

11、innodb和myissam的区别：

• 事务：innodb有，myissam没有；
• 行锁：innodb有，myissam没有；
• 表锁：innodb有，myissam有；
• 外键：innodb有，myissam没有；
• 计数器：innodb没有，myissam有；
• 聚簇索引：innodb有，myissam没有；

12、索引的分类有哪些：

根据叶子节点的内容，索引类型分为主键索引和非主键索引。 主键索引的叶子存的是正行数据，在Innodb里，主键索引也被称为聚簇索引 非主键索引的叶子节点内容是主键的值，在innodb里非主键索引也被称为二级索引。

13、innodb为什么设计B+树，而不是B-树，Hash，二叉树，红黑树？

• 哈希索引能够以O(1)的速度处理单个数据行的增删改查，但是面对范围查询或者排序时就会导致全表扫描的结果。
• B树可以在非叶节点存储数据，由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来大量的随机IO,造成性能下降。
• B+树所有的数据行都存储在叶子节点，而这些叶子节点可以通过指针一次按照顺序链接，当我们在遍历B+树数据可以直接在多个字节点之间进行跳转，这样能够节省大量的磁盘io时间。
• 二叉树 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且io代价高。
• 红黑树 树的高度随着数据量的高度增加而增加，IO代码高。

14、讲一讲聚簇索引与非聚簇索引？

在InnoDB里，索引B+树叶子节点存储了正行数据的是主键索引，也称为聚簇索引，即将数据存储与索引放到一块，找到索引也就找到了数据。而索引B+树的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

15、innodb为什么要用自增id作为主键：

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会开辟一个新的页。如果使用非自增主键，由于每次插入主键的值近似随机，因此每次新纪录都要被插到现有索引的中间位置，频繁的移动、分页操作造成大量的碎片，得到了不够紧凑的索引结构，后续不得不通过优化表来重建表并优化填充页面。

16、有哪些场景下会导致索引失效：

• 背景：B+树提供的这个快速定位能力，来源于同一层兄弟节点的有序性，所以说破坏了这个有序性，大概率就失效了，具体有如下几种情况。
• 对索引使用左或者左右模糊匹配
• 对索引使用函数/对索引进行表达式计算：因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
• 对索引隐式类型转换；相当于用了新函数。
• where子句中的or：的含义就是两个只要满足一个即可，因此只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就是进行全表扫描。

17、binlog三种格式的对比：**

• row格式的binlog记录的操作行的主键id以及每个字段的真实值，所以不会出现主备操作数据不一致的情况。
• statment记录的源sql语句
• mixed：前两种混合，为什么还需要有mixed格式的文件，因为有些statment格式的binlog可能会导致主备不一致，所以要使用row格式。但是row格式的缺点是很占空间。mysql就取了个折中方案，mysql自己会判断这条sql语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statment格式。

18、mysql大表查询为什么不会把爆内存：

• mysql是"边读边发的"，这就意味着，如果客户端接收的慢，会导致mysql服务端游于结果发不出去，这个事物的执行时间变长。
• 服务端并不需要保存一个完整的结果集，取数据和发数据的流程都是通过一个next_buffer来操作的。
• 内存的数据页是在bufferPool中管理的。
• Innodb管理的buffer Pool使用改进的LRU算法，是用链表分成了young区域和old区域，确保大批量加载冷数据时不会冲掉热数据。

19、mysql事务的隔离级别：

• 读未提交 最低级别，任何情况都无法保证
• 读已提交 可避免脏读的发生
• 可重复读 可避免脏读、不可重复读、幻读的发生。
• 串行化 可避免脏读、不可重复读、幻读的发生
• mysql默认的事务隔离级别：可重复读

20、MVCC实现原理：

• MVCC就是同一份数据保留多版本的一种方式，进而实现并发控制。查询的时候，通过read view和版本链接找到对应版本的数据。
• 作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小
• MVCC的实现依赖于版本链，版本链是通过表的三个隐藏字段实现
  -1）DB_TRX_ID：当前事务id,通过事务id的大小判断事务的时间顺序。
  -2）DB_ROLL_PRT:回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本链接在一起构成undo log版本链
  -3）DB_ROLL_ID：主键，如果数据表没有主键，InnoDB会自动生成主键

21、mysql主从同步延时产生原因？怎么优化？

• 主节点如果执行一个很大的事务，那么就会对主从延迟产生较大的影响。
• 网络延迟，日志较大，slave数量过多。
• 主上多线程写入，从节点周游单线程同步
• 机器性能问题，从节点是否使用了“烂机器”
• 锁冲突问题也可能导致从几的SQL线程执行慢。

22、什么是脏读、不可重复读、幻读：

• 「脏读」：脏读指的是读到了其他事务未提交的数据，未提交意味着这次数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。
• 「不可重复读」：不可重复读指的是在一个事物内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。
• 「幻读」：幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的select操作得到的结果的数据状态无法支撑后续的业务操作。更为具体一下：select某记录是否存在，不存在，准备插入次记录，单执行insert时发现此记录已存在，无法插入，此时就发生了幻读。

mysql优化总结：

1、使用show profile 查询剖析工具

• all显示所有性能信息 ：show profile all for query n
• block Io 显示io操作的次数：show profile block io for query n
• content switchs 显示上下文切换次数，被动和主动：show profile context switches for query n
• cpu 现实用户cpu时间，系统cpu时间：show profile cpu for query n
• IPC 显示接收和发送的消息数量：show profile ipc for query n
• swap 显示swap的次数

2、使用performance schema来更加容易的监控mysql：

1、MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。

• 1、提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema
  数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息
• 2、performance_schema通过监视server的事件来实现监视server内部运行情况，“事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing或sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。
• 3、performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
• 4、performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
• 5、 当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。
• 6、PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同
• 7、收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）
• 8、performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）
• 9、MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。

2、使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征**

3、schema与数据类型优化：

• 更小的通常好用
• 简单就好
• 尽量避免null
• 实际细则
  整数类型:可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。
  尽量使用满足需求的最小数据类型
  字符和字符串类型:VARCHAR(根据实际长度保存数据)和char(固定长度保存数据)
  BLOB

4、datatime和timestamp的区别：

• datatime：
  - 占用8个字节
  - 与时区无关，数据库底层时区配置，对datatime无效。
  - 可保存到毫秒
  - 可保存时间范围大
  - 不能使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷型。
• timestamp:
  - 占用四个字节
  - 时间范围：1970-01-01——2038-01-19
  - 精确到秒
  - 采用整型存储
  - 依赖数据库设置的时区
  - 自动更新timestamp列的值
• data:
  - 占用的字节数比字符串、int、datatime要少，data只需要3个字节。
  - 使用data类型还可以利用日期时间函数进行日期之间的计算。
  - data类型用于保存1000-01-01到9999-12-31之间的日期。

5、char和varchar的区别:

• char:
  - 固定长度
  - 最大长度为255
  - 会自动删除末尾的空格
  - 检索效率，写效率要比varchar高，以空间换效率
• varchar:
  - 根据内容的实际长度保存数据
  - varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
  - varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。
  - varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。

数据库索引：

1、索引的优化：

1. 索引的优点：
   • 大大减少了服务器扫描的数据量
   • 帮助服务器避免排序和临时表
   • 将随机IO变成顺序IO
2. 索引的分类：
   • 主键索引
   • 唯一索引
   • 普通索引
   • 全文索引
   • 组合索引
3. 索引采用的数据结构：
   哈希表
   B+树
4. 哈希索引：
   • 基于哈希表的实现，只有精确匹配索引所有列的查询才有效果。
   • 只有memory的存储引擎显示支持哈希索引
   • 哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快。
   • 哈希索引的限制：
     - 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行。
     - 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
     - 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
     - 哈希索引支持等值比较查询，也不支持任何范围查询
     - 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
     - 哈希冲突比较多的话，维护的代价也会很高
5. 扩展（b树的特点：）：
   • 所有的键值分布在整颗树中。
   • 搜索又可能在非叶子结点结束，在关键子全集内做一次查找性能逼近二分查找。
   • 每个节点最多拥有m个子树
   • 根节点至少有两个子树
   • 分支节点至少有m/2颗子树（除根节点和叶子节点外都是分支节点）
   • 所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列。

2、查询计划：

• id: select查询的序列号，包含一组数字，表示查询执行select子句 或者操作表的顺序。
• select_type:
• table:
• type:显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：system>const>eq_ref>ref& fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
  • all:全表扫描
  • index:全索引扫描
  • range:表示利用索引查询时限制了范围，在指定的范围内进行查询，这样避免了index的全索引的过程。
  • index_subquery:利用索引来关联子查询，不用扫描全表
  • unique_subquery:利用唯一索引来关联子查询，不用扫描全表
  • index_merge:查询过程中需要多个索引组合使用
  • ref :使用了非唯一性索引进行数据的查找。
  • const:这个表至多有一个匹配行
• possible_keys:显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将按列出，但不一定被查询实际使用。
• key:实际使用的索引，如果为空，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
• key_len:表示字段中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下，长度越短越好。
• ref:显示索引的那一列被使用了，如果可能的话，这是一个常数。
• rows:根据表的统计信息及索引使用概况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql着了多少数据，在完成目的的情况下越少越好。

3、索引监控：

• show status like ‘%handler_read%’;查看
• Handler_read_first ：读取索引第一个条目的次数
• Handler_read_key ：通过索引获取数据的次数
• Handler_read_last ：读取索引最后一个条目的次数
• Handler_read_next ：读取索引下一条数据的次数
• Handler_read_prev ：通过索引读取上一条索引的次数
• Handler_read_rnd ：从固定位置读取数据的次数
• Handler_read_rnd_next ：从数据点读取下一条数据的次数