面试套路 - 数据库

辩证唯物主义告诉我们，事物发展轨迹是波浪式前进，螺旋式上升，有的时候似乎重新回到过去，但是却有了本质的区别和进步。

MySQL

为啥索引能加快查询速度

索引其实也是一种空间换时间的思路。

数据库索引使用B+树，B+树是一种N叉排序树，树的每个节点包含N个数据，这些数据按顺序排好，两个数据之间是一个指向子节点的指针，而子节点的数据则在这两个数据大小之间。

如上图所示：B+树的节点存储在磁盘上，每个节点存储1000多个数据，这样树的深度最多只要4层就可以存储数亿的数据。如果将树的根节点缓存在内存中，则最多只需要三次磁盘访问就可检索到需要的索引数据。

B+树只是加快了索引的检索速度，如何通过索引加快数据库记录的查询速度呢？

数据库索引有两种，一种是聚簇索引，聚簇索引的数据库记录和索引存储在一起，上面这张图就是聚簇索引的示意图，在叶子节点，索引1和记录行r1存储在一起，查找到索引就是查找到数据库记录。像MySQL数据库的主键就是聚簇索引，主键ID和所在的记录行存储在一起。MySQL 的数据库文件实际上是以主键作为中间节点，行记录作为叶子节点的一颗B+树。

另一种数据库索引是非聚簇索引，非聚簇索引在叶子节点记录的就不是数据行记录，而是聚簇索引，也就是主键，如下图：

通过B+树在叶子节点找到非聚簇索引a，和索引a在一起存储的是主键1，再根据主键1通过主键（聚簇）索引就可以找到对应的记录r1。这种通过非聚簇索引找到主键索引，再通过主键索引找到行记录的过程也被称作回表。

所以通过索引，可以快速查询到需要的记录，而如果要查询的字段上没有建索引，就只能扫描整张表了，查询速度就会慢很多。

MyISAM引擎索引（无论是否是主键索引）指向磁盘上的物理行；而InnoDB引擎主键索引（聚簇索引：数据和索引在一块）是和数据记录保存在一起的，一般索引（非聚簇索引）存储的是主键。

B+树的页面会分裂，对于聚簇索引这个问题比较严重。对于MyISAM，节点存储的是物理行的地址，内容较小又缓存在内存里，分裂速度快很多，InnoDB节点下存储了行的数据，分裂的时候还需要移动数据，比较耗时。因此InnoDB对于主键的选择非常敏感，如果碰到不规则数据插入时,造成频繁的页分裂。

聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值, (不要用随机字符串或UUID)否则会造成大量的页分裂与页移动.

索引覆盖指的是查询的数据在索引中都能找到，不需要回表（explain的时候出现了using index）。

为什么hash索引使用较少

在memory存储引擎的表里,默认是hash索引, hash的理论查询时间复杂度为O(1)。但是不常用的原因有以下几点：

1. hash函数计算后的结果是随机的,如果是在磁盘上放置数据,例如主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置（磁盘的顺序查找优于随机查找）
2. 无法对范围查询、排序进行优化
3. 无法利用前缀索引. 比如在btree中, field列的值“hellopworld”,并加索引查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引(左前缀索引)。而hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机，无法利用索引
4. 必须回表，也就是说通过索引得到数据的位置后，必须回到表中取数据

如何设计索引

好的索引应该查询频繁、区分度高、长度小（其中区分度和长度是互斥的，需要做一些权衡）。索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。

对于左前缀不易区分的列，例如url列：http://www.baidu.com和http://www.qq.com前11个字符都是相同的，有以下2种建立索引的技巧：

1. 可以考虑存储的时候倒序存储，db中存储：moc.udiab.www//:ptth和moc.qq.www//:ptth，这样左前缀的区分度就更大了
2. 多加一列url_hash（可以采用crc_32）,update t_url set url_hash = crc32(url)，在url_hash上建立索引，索引长度将大幅减少

通用的准则如下：

• 主键应该用递增的值，不要用离散的值，离散值会导致文件在磁盘上的位置有间隙，浪费空间，且不容易顺序读取。
• uuid也是逐步增长的，可以考虑去掉-转为整数。
• 页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
• 避免用NULL:不利于索引，要用特殊字节标注，在磁盘上的空间其实更大
• 不要使用 count(列名) 或 count(常量) 来替代count(*)，count(*)是SQL92定义的标准统计行数的语句，跟数据库无关，跟NULL和非NULL无关。说明：count(*) 会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。
• count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。
• 禁止使用存储过程。存储过程难以调试和扩展，更没有移植性。
• IN操作能避免则避免。若实在避免不了，需要仔细评估IN后面的集合元素数量，控制在 1000 个之内。

索引常见误区

在where条件的所有列上都加索引，例: where cat_id=3 and price>100; //查询第3个栏目,100元以上的商品,如果在cat_id上,和, price上都加上索引，因为是独立索引，同时只能用上1个（虽然5.6版本会进行优化，但是作用还是有限的）。

面试题：联合索引index(a,b,c):

上述最后两条语句b使用了范围查询和模糊查询，导致只使用了(a,b)索引。

什么情况下会产生临时表

1. group by 的列和order by 的列不同时, 2表边查时,取A表的内容,group/order by另外表的列
2. distinct 和 order by 一起使用时

什么情况下临时表会写到磁盘上

1. 取出的列含有text/blob类型时 —内存表储存不了text/blob类型
2. 在group by 或distinct的列中存在>512字节的string列
3. select中含有>512字节的string列,同时又使用了union或union all语句

分页常用的实践方案有哪些，如何优化

1. 从业务上解决，不允许翻过100页，以百度为例，一般70页左右（连百度都无法解决深度分页问题）
2. 不用offset,用条件查询，where id > 5000000 limit 10
3. 解决方案2有个问题：数据被物理删除过，就会造成空洞，分页的结果不一致，解决方案数据不物理删除（使用逻辑删除，被删除的内容不显示即可）
4. 非要物理删除，还要offset精确查询，还不能限制用户分页。优化思路是：不查,少查,查索引,少取，只查索引得到id，再用id去获取具体条目，这种技巧就是延迟索引。

小数据量（几万）只需要使用limit语句进行全表扫描即可，因为即使offset很大，全表扫描的代价也不大。limit中的offset是先查询然后跳过，对于大表来说逐行扫描跳过一定数量是不可接受的。以下以每页10条数据为例子，数据生成脚本如下：

const tableConfig = {
  posts_small:{
    count:10000
  },
  posts_big:{
    count:500000
  }
};

const Mock = require('mockjs');

const Random = Mock.Random;

let sql = '';
for (const tableName of Object.keys(tableConfig)) {
  const {count} = tableConfig[tableName];

  sql += `CREATE TABLE \`${tableName}\` (
  \`id\` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  \`type\` tinyint(3) unsigned DEFAULT NULL COMMENT '类型',
  \`content\` varchar(512) DEFAULT '' COMMENT '内容',
  \`created\` timestamp NULL DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (\`id\`),
  KEY \`idx_created\` (\`created\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n`;

  for (let i = 0;i < count;i++) {
    sql += `INSERT INTO ${tableName} SET type = ${Random.natural(0,1)},content = '${Random.cparagraph()}',created = from_unixtime(${new Date(Random.datetime()).getTime() / 1e3 | 0});\n`;
  }
}

require('fs').writeFileSync('./data.sql',sql);

小表1W条，大表50W条，执行分页查询如下：

在大表中进行分页查询后，非常长的时间才拿到响应（并且随着offset的增加，性能越差）,查看服务器负载：

在top命令中发现其他指标都正常，但是wa比较高意味着大量的磁盘IO，进一步执行iotop验证结论。

使用show profile可以发现耗时都在Sending Data.

原因在于SELECT *会进行回表操作（因为查询的有不包含索引的列），即先按照索引条件筛选出主键，再根据主键查询取出全部的列，这是一个非常耗时的操作。改成只查询出分页的主键（效率高的原因是索引文件比数据文件小得多），然后再使用IN查询（或者用INNER JOIN）性能可得到大幅提高：

这里吐槽下：不知道为啥子查询中不能有LIMIT子句,网上的解决方案是再包一层：

另一种分页优化：SELECT * FROM articles WHERE id >= (SELECT id FROM articles ORDER BY id LIMIT 10000, 1) LIMIT 10，将主查询变成了一个普通的范围查询。

参考资料：MySQL的limit分页的性能分析和优化

MySQL中的索引尽量建立在区分度比较高的列上（也就是说不要有大量的重复数据）。

在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.我们可以通过一个nop操作(不产生对数据实质影响的操作)来修改表.optimize table，修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.这个过程,如果表的行数比较大,也是非常耗费资源的操作.所以,不能频繁的修复.如果表的Update操作很频繁,可以按周/月,来修复.如果不频繁,可以更长的周期来做修复。

为什么预编译的SQL能防止SQL注入

预编译是发生在MySQL服务器端，不仅能防止SQL注入还能提高性能，因为只编译了一遍，不需要再次发送完整的SQL了，只需要绑定参数了，有效减少了IO,nodejs中推荐的库是node-mysql2。

const mysql = require('mysql2/promise');

(async () => {
    const connection = await mysql.createConnection({
        host: '192.168.3.118',
        user: 'root',
        password: 'master',
        database: 'test'
    });
    await connection.query('SELECT * FROM test WHERE uid = ?', [1]); // 使用普通的sql
    await connection.execute('SELECT * FROM test WHERE uid = ?', [1]); // 预编译的sql
    await connection.execute('SELECT * FROM test WHERE uid = ?', [2]); // 预编译的sql
})().then(process.exit).catch(e => console.error(e));

用Wireshark抓包（如果报权限错误可以使用sudo命令sudo /Applications/Wireshark.app/Contents/MacOS/Wireshark）。

MYSQL服务器的查询日志如下：

主主复制架构是怎样的，需要注意什么

主要是解决一主多从架构写库的高可用问题，无法解决写操作的性能和提升数据库的存储能力。需要注意的是任何时候都只能有一台数据库作为主库进行写操作，只有当这台DB宕机的时候才会将写操作切换到另一台主数据库上，这样才能保证数据一致性，不会出现数据冲突。如果确实存在多主写的情况可以抽离主键生成服务，各个服务器先调用主键生成服务生成主键然后插入也可以避免主键冲突。

说说数据库分片

数据库分片用来解决数据库的存储能力。也就是说，将一张表的数据分成若干片，每一片都包含了数据表中一部分的行记录，然后每一片存储在不同的服务器上，这样一张表就存储在多台服务器上了。

最简单的数据库分片存储可以采用硬编码的方式，在程序代码中直接指定一条数据库记录要存放到哪个服务器上。比如将用户表分成两片，存储在两台服务器上，那么就可以在程序代码中根据用户 ID 进行分片计算，ID 为偶数的用户记录存储到服务器1，ID 为奇数的用户记录存储到服务器2。

但是硬编码方式的缺点比较明显:

• 如果要增加服务器，那么就必须修改分片逻辑代码，这样程序代码就会因为非业务需求产生不必要的变更
• 其次，分片逻辑耦合在处理业务逻辑的程序代码中，修改分片逻辑或者修改业务逻辑都可能使另一部分代码因为不小心的改动而出现Bug

可以通过使用分布式关系数据库中间件解决这个问题，将数据的分片逻辑在中间件中完成，对应用程序透明，如MyCat。

实践中，更常见的数据库分片算法是我们所熟悉的余数 Hash 算法，根据主键 ID 和服务器的数目进行取模计算，根据余数连接相对应的服务器。

死锁形成的原因是啥

死锁是一种争夺资源而造成的一种互相等待的现象，如果没有外力作用，它们都将无法推进下去，这时称系统处于死锁或者系统产生了死锁。这些永远在等待的进程被称为死锁进程。表级锁不会产生死锁，所以死锁问题主要针对的是InnoDB。

死锁的关键在于两个（或以上）session的加锁顺序不一致。对应的解决方案是就是让不同的session加锁有次序。

事务是如何实现的

原子性：undo log，保存和执行相反的日志，还能实现MVCC
一致性：
隔离性：锁
持久性：redo log，保证crash safe，即使数据没有持久化成功只要日志持久化了，依然可以进行恢复。这其实是一种WAL（write ahead log）机制

注意：undo log和redo log是InnoDB存储引擎这一层次，而binlog是mysql server这个层次，不要搞混了。

事务补偿机制

针对每一个操作，都要注册一个与其对应的补偿（撤销）操作。在执行失败的时候执行补偿操作撤销之前的动作。

例如下单的时候扣减库存：订单库和商品库是分开的，就存在了分布式事务问题：

下单之前会对库存进行扣减，这个时候可能是UPDATE语句把库存更新了，接下来进行INSERT操作创建订单。但是如果订单创建失败了就直接抛出异常了（由于商品库在别的库，所以无法回滚）。这个时候就需要调用补偿操作把商品库存加回来。

互联网上经常使用银行转账的例子来说明事物，但是这个例子如果涉及到两家银行，这就是一个分布式事务的问题。需要提供补偿机制。

优点：逻辑清晰、流程简单
缺点：数据一致性比XA还要差，可能出错的点比较多。TCC属于应用层的一种补偿方式，需要程序员编写大量代码

分库分表

设计高并发系统的时候，DB层面应该如何设计？其实分库和分表是两回事，可能光分库不分表也可能光分表不分库。其实分库分表一定是跟着公司的业务走的，用户越多，数据量越大单个DB肯定扛不住。单个库最高支撑并发2000就一定要进行扩容了，一个健康的单库并发值最好保持在每秒1000左右，不要太大。

对于一个未分库分表的系统如何动态切换到分库分表

• 最简单的方案：停机迁移。这个方案比较low，谁都能干。
• 还有个高大上的方案：双写。

这种方案可以实现不停机维护：简单来说，就是在线上系统里面，之前所有写库的地方，增删改操作，除了对老库增删改，都加上对新库的增删改，这就是所谓的双写，同时写俩库，老库和新库。然后系统部署之后，新库数据差太远，用数据导出工具，跑起来读老库数据写新库，写的时候要根据gmt_modified这类字段判断这条数据最后修改的时间，除非是读出来的数据在新库里没有，或者是比新库的数据新才会写。简单来说，就是不允许用老数据覆盖新数据。

导完一轮之后，有可能数据还是存在不一致，那么就程序自动做一轮校验，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止。

接着当数据完全一致了，就ok了，基于仅仅使用分库分表的最新代码，重新部署一次，不就仅仅基于分库分表在操作了么，还没有几个小时的停机时间，很稳。所以现在基本玩儿数据迁移之类的，都是这么干的。

NoSQL

NoSQL数据是改善数据存储能力的一个重要手段。主要用来解决大规模分布式数据的存储问题。常用的HBASE，Cassandra，面临的挑战是数据一致性问题。如果数据分布存储在多台服务器组成的集群上，那么当有服务器节点失效的时候，或者服务器之间网络通信故障的时候，不同用户读取的数据就可能会不一致。

如上图所示用户 1 连接服务器节点 A，用户 2 连接服务器节点 B，当两个用户同时修改某个数据的时候，如果正好服务器 A 和服务器 B 之间的网络通信失败，那么这两个节点上的数据也就不一致了，其他用户在访问这个数据的时候，可能会得到不一致的结果。

关于分布式存储系统有一个著名的CAP原理，CAP原理说：一个提供数据服务的分布式系统无法同时满足数据一致性（Consistency）、可用性（Availability）和分区容忍性（Partition Tolerance）这三个条件。

一致性是说，每次读取的数据都应该是最近写入的数据或者返回一个错误，而不是过期数据，也就是说，数据是一致的。

可用性是说，每次请求都应该得到一个响应，而不是返回一个错误或者失去响应，不过这个响应不需要保证数据是最近写入的。也就是说，系统需要一直都是可以正常使用的，不会引起调用者的异常，但是并不保证响应的数据是最新的。

分区容忍性说，即使因为网络原因，网络分区失效的时候，部分服务器节点之间消息丢失或者延迟了，系统依然应该是可以操作的。

CAP原理是说，当网络分区失效发生的时候，我们要么取消操作，保证数据就是一致的，但是系统却不可用；要么继续写入数据，但是数据的一致性就得不到保证了。

对于一个分布式系统而言，网络失效一定会发生，也就是说，分区容忍性是必须要保证的，而对于互联网应用来说，可用性也是需要保证的，分布式存储系统通常需要在一致性上做一些妥协和增强。

Apache Cassandra 解决数据一致性的方案是，在用户写入数据的时候，将一个数据写入集群中的三个服务器节点，等待至少两个节点响应写入成功。用户读取数据的时候，从三个节点尝试读取数据，至少等到两个节点返回数据，并根据返回数据的时间戳，选取最新版本的数据。这样，即使服务器中的数据不一致，但是最终用户还是能得到一个一致的数据，这种方案也被称为最终一致性。

因为各种原因，互联网应用主要采用的是水平伸缩，也就是各种分布式技术。事实上，在数据存储方面，有时候采用垂直伸缩，也就是使用更好的硬件服务器部署数据库，也是一种不错的改善数据存储能力的手段。

参考资料

• 为什么B+树适合做索引
• 行锁升级为表锁