面试-数据知识点准备

最近在为找实习学习数据库相关知识，再此记录一下，后期面试如果遇到新问题，会追加。
问题的回答来自 http://www.cyc2018.xyz/和https://snailclimb.gitee.io/javaguide/#/

索引

一、介绍一下索引
答：索引是一种数据结构，数据库索引是数据库管理系统的有序索引结构。MySQL索引利用BTree和B+Tree实现。
索引是在存储引擎层实现的，因而不同的存储引擎有不同的索引类型和实现。
索引包括B+ Tree索引、哈希索引、全文索引、空间数据索引。
B+ Tree索引：
是MySQL大部分存储引擎的默认索引
1、不需要全表扫描，对树进行搜索，可以提高查找效率；
2、因为B+ Tree的有序性，所以有利于排序和分组；
3、可以指定多个列作为索引列，多个索引值共同组成键
3、适用于全键值、键值范围和键前缀查找，键前缀查找只使用左前缀查找；
InnoDB的索引分为主索引和辅助索引，主索引的叶子节点的data域存储的是完整的数据记录，而辅助索引的节点data域存储的是主键的值。
B+ Tree原理
B Tree是平衡树，所有叶子节点在同一行；
B+ Tree是基于平衡树和叶子节点顺序访问指针实现的，具备平衡性和顺序访问指针顺序读取的特性
从根节点进行二分查找，找到一个key所在的指针，递归地在指针指向的节点上进行二分查找，直到找到叶子节点，然后再叶子节点上进行二分查找，找到key所对应的data
哈希索引：
1、以O(1)时间进行查找，查找速度快，适用于单条记录查询的需求，但丢失有序性
2、不支持排序和分组，只适用于精确匹配，不适用部分和范围匹配
3、InnoDB存储引擎中有一个特殊的自动创建的自适应哈希索引，当某一个索引值被频繁使用时，会在B+Tree索引的基础上加哈希索引，使得B+Tree索引具备哈希索引的优点，例如快速哈希查询；
全文索引：
MyISAM存储引擎支持全文索引。全文索引用来查找关键词，查找条件为match against，全文索引利用倒排索引实现，存储关键词在其所在文档的映射。（InnoDB在MySQL 5.6.4开始支持全文索引）
空间数据索引：
MyISAM支持空间数据索引，存储地理数据，从所有维度检索索引，因此适合任意维度的组合查询。
索引的优点：
（1）创建唯一性索引，保证数据库表数据每一行的唯一性；
（2）有利于提高数据库数据查询效率；
（3）帮助服务器避免排序和临时表（索引的有序性）
（4）随机IO变为顺序IO
（5）加快表与表之间的连接（在经常连接的表上创建索引，主要是外键，可以加快表与表之间的连接）
索引的缺点：
（1）数据增删改时要维护索引，降低数据维护速度
（2）索引占一定的物理空间
（3）创建和维护索引耗时
索引的相关注意事项：
（1）在经常搜索的列上加索引，以提高查询速度；
（2）在经常使用在where子句中的列加索引，以提高条件查询速度；
（3）在经常排序的列上加索引，有利于排序和分组；
（4）在经常连接的列上创建索引，主要是外键，有利于提高表和表连接速度；
（5）在中到大型的数据库中应用索引，而特大型数据库因维护索引困难，因此不适用索引；
（6）如果某一列设置为default null，可以使用索引，索引列为null不影响索引的性能，但不建议这样做，因为null占物理存储且不参与运算；
（7）删除长期未使用的索引，长期未使用但存在的索引消耗系统性能；
（8）在利用limit和offset进行慢查询时，可以用索引
（9）在InnoDB中使用与业务无关的自增主键作为主键
（10）避免在where子句中使用函数，会造成无法命中索引
二、说一下B+ Tree，以及与红黑树的比较
B Tree即balance tree平衡树，其叶子节点在同一行
B+ Tree基于B Tree和叶子节点顺序访问指针实现。既有平衡性，又可以利用顺序访问指针顺序访问数据
B+ Tree的叶子节点按非递减排序
比较：
（1）B+ Tree有更低的树高
平衡树的树高O(h)=O(log d_N)，d为节点的出度。红黑树的节点出度d为2，B+ Tree的节点出度一般比较大，因而其树高更低；
（2）B+ Tree更有利于磁盘访问
操作系统将内存和磁盘分割成固定大小的块，这个块被称为“页”，内存与磁盘之间以页为单位交换数据。而索引的节点大小设置为页的大小，一次IO就可以读入一个节点。若数据不在同一个磁盘上，需要利用制动手臂进行寻道操作，因为制动手臂的物理结构，其移动效率低，降低磁盘访问速度。红黑树比B+ Tree有更高的树高，而寻道次数与树高成正比。而同一磁盘的数据不需要寻道，只需要很短的磁盘旋转时间，因此B+ Tree更适合磁盘数据访问。
（3）磁盘预读特性
为了减少IO次数，磁盘往往会利用磁盘预读特性。在预读过程中，往往采用顺序读取的方式。顺序读取不需要寻道，只需要很短的磁盘旋转时间。并且利用磁盘预读特性，相邻节点可以一次性载入。
三、说一下MySQL不同存储引擎的B+ Tree实现
MySQL主要有两种存储引擎：InnoDB和MyISAM
InnoDB存储引擎中，其数据文件就是索引文件。数据库的表数据文件按照B+ Tree组织成一个索引结构，索引的key为数据的主键，因此其数据文件就是主索引，这就是聚簇索引。其他索引都是辅助索引。主索引的叶子节点的data域存储的是完整的数据记录，根据主索引搜索，找到key值对应的节点，即可获得对应的值；辅助索引的节点data域存储的是数据记录主键的值，按辅助索引搜索，需要先取出主键的值，然后利用主索引查找。
MyISAM存储引擎数据文件与索引文件分离，其叶子节点的data域存储的是数据记录的地址。对索引检索，首先按照B+ Tree搜索算法搜索索引，若指定的key存在，则取出data域的值，以data域的值为地址读取相应的数据记录。
四、为什么索引能提高表查询速度
在查询时，不加索引，则进行两步操作定位到相关的记录：一是定位到数据记录的页，遍历双向链表，查找对应的页；二是在页中查找相应的数据记录，遍历单向链表，若不采用主索引，则须依次遍历单向链表。
加索引之后，利用B+ Tree进行二分查找，大大提高查询速度。
五、索引的优化
1、独立列：索引列不作为函数参数或表达式的一部分；
2、多列索引:可以使用多个列作为条件进行查询，多个索引列组成一个键，使用多列索引比多个单列索引性能更好
3、索引的顺序：索引选择性最强的放在最前面，选择性是不重复索引的数量域记录总数的比值
4、前缀索引：BLOB、TEXT、VARCHAR必须采用前缀索引，前缀的数量由选择性决定
5、覆盖索引：一个索引包含所有需要查询的字段的值。如果辅助索引可以覆盖查询则不需要使用主索引–索引远小于数据记录行数，只检索索引大大减少数据访问量；一些存储引擎的内存只存储索引，而数据由操作系统存储，只检索索引可以不适用系统调度，系统调度很耗时；对遇InnoDB，辅助索引若能覆盖查询，则无需访问主索引。

MySQL

一、查询性能优化
1、explain查询
用explain分析查询语句，其结果中最主要的部分：
select_type：查询类型（简单查询、联合查询和子查询）
key：用到的索引
rows：扫描的行数
2、优化数据访问
（1）减少请求的数据量：只返回需要的行或者列，缓存查询
（2）减少服务器扫描行数：应用索引
3、重构查询方式
（1）切分大查询
（2）分解大连接查询
将大连接查询分解为多个单表查询。在连接查询中，如果其中一张表发生变化，则原有的查询缓存则无法使用，分解连接查询，如果其中一张表发生变化，其他表的查询缓存依然可以使用；分解连接查询，每个表的查询缓存更容易被其他表使用；减少锁竞争，在应用程进行连接，更有助于数据库拆分，更容易实现高性能和可伸缩性；查询本身效率可能会提升。
二、存储引擎
1、InnoDB
InnoDB是MySQL默认的事务型存储引擎。支持四种标准的隔离级别，默认为可重复读级别。在可重复读级别下，加上多版本并发控制和Next-Key Locking可以防止幻读。InnoDB实现很多内部优化：加快读操作且自动创建的自适应哈希索引，加快插入操作的插入缓冲区。InnoDB支持真正的热备份，其他存储引擎不支持，在获得统一性视图时必须停止写入，在读写混合的场景，停止写入就要停止读取。
2、MyISAM
设计简单，数据以紧密格式存储，对于只读，表比较小且可以容忍修复的可以可以用MyISAM。其他特性，压缩表和空间数据索引。不支持事务，不支持行级锁，只能对整张表加锁，在对多张表加共享锁时，可以新插入一条数据，即为并发插入。可以手动或自动进行检查或修复操作。不同于事务恢复和崩溃恢复，可能存在数据丢失。如果指定了DELAY_KEY_WRITE，在每次修改执行完之后，修改的索引并不写入磁盘，而是写入内存的键缓冲区中，当清除键缓冲区或关闭表时，修改的索引才会写入磁盘，若发生数据库崩溃或主机崩溃，则数据容易丢失，需要进行修复操作。
3、比较
（1）事务：InnoDB支持事务，MyISAM不支持事务
（2）并发：MyISAM只支持表级锁，InnoDB支持行级锁
（3）外键：InnoDB支持外键
（4）备份：InnoDB支持在线热备份
（5）其他特性：MyISAM支持压缩表和空间数据索引
（6）崩溃恢复：MyISAM崩溃发生损坏的可能性比InnoDB高，且数据修复慢
四、切分
1、水平切分（sharding）
将数据库表的数据记录切分，写入结构相同的数据表中
当一个表中的数据逐渐增多时，sharding是必然的选择，可以将数据分布到集群的不同节点上，，环节单个数据库的压力
2、垂直切分
将数据库表按列切分成多个表，通常按列的紧密关系进行切分–分库分表
将数据库中表的密集程度部署到不同的库，例如将原来的电商数据库切分为商品库和用户库
五、复制
1、主从复制
涉及三个线程：
（1）binlog线程：负责将数据更改写入主服务器的二进制日志中
（2）IO线程：负责读取主服务器中的二进制日志，并写入从服务器的中继日志
（3）SQL线程：负责读取中继日志，解析主服务器已经执行的数据更改并在从服务器上重放
2、读写分离
主服务器负责写操作和实时性要求高的操作，从服务器负责读操作
优点：（1）主从服务器各自负责各自的操作，从而减小锁竞争；
（2）增加冗余，提高可用性
（3）从服务器可以利用MyISAM，大大提高查询效率，节约系统开销

数据库

一、事务特性
ACID
A 原子性：事务要么一起执行成功，要么都执行失败
C 一致性：数据库事务执行前后保持一致性状态，例如，银行表有两个账户，一个100，一个400，总计500，不管如何进行转账操作，最终两个账户总额依然为500
I 隔离性：不同事务中，一个事务的未提交数据对另一事务不可见
D 持久性：事务一旦提交，将保存到数据库中，即使数据库发生故障，数据也不会发生改变，利用redo日志恢复
二、隔离级别
1、未提交读
事务中的修改即使没有提交，对其他事务也可见
2、提交读
事务只能读取已提交的事务的修改
3、可重复读
一个事务对同一个数据结构进行多次访问，多次访问数据保持一致
4、可串行化
强制事务串行执行，多个事务互不干扰
三、并发一致性问题（破坏了隔离性）
1、丢失修改
一个事务的更新操作被另一事务的更新操作覆盖
2、脏读
不同事务下，一个事务可以读取到另一事务未提交的数据
3、不可重复读
一个事务A对同一数据进行多次访问，若中间有另一事务B对数据进行了修改，则事务A两次读取的数据不一致
4、幻读
本质上就是不可重复读，在一个事务读取某个范围的数据，另一事务在这个范围内插入新的数据，读取该范围的数据就发生了变化
四、封锁
1、封锁粒度
（1）行级锁
（2）表级锁
2、封锁类型
（1）读写锁：共享锁和互斥锁
（2）意向锁
3、封锁协议
（1）三级封锁协议
（2）两段锁协议