Java面试题冲刺第二十八天--数据库(5)

面试题1：MySQL数据库cpu飙升到500%的话你会怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看是没用上索引还是IO过大造成的。

mysql> show processlist;
+--------+-----------------+--------------------+---------+---------+------+-----------------------------+------------------+
| Id     | User            | Host               | db      | Command | Time | State                       | Info             |
+--------+-----------------+--------------------+---------+---------+------+-----------------------------+------------------+
|      1 | event_scheduler | localhost          | NULL    | Daemon  |  313 | Waiting for next activation | NULL             |
| 239896 | root            | 192.168.1.21:55050 | finance | Sleep   | 1160 |                             | NULL             |
| 239898 | root            | 192.168.1.21:58118 | NULL    | Sleep   |  397 |                             | NULL             |
| 239899 | root            | 192.168.1.21:58127 | csjdemo | Sleep   |  393 |                             | NULL             |
| 239901 | root            | 192.168.1.21:58135 | csjdemo | Sleep   |  387 |                             | NULL             |
| 239901 | root            | 192.168.1.21:58135 | csjdemo | Query   | 1044 |                             | select * from T like `name` like '%陈哈哈%'            |
| 239904 | root            | localhost          | NULL    | Query   |    0 | starting                    | show processlist |
+--------+-----------------+--------------------+---------+---------+------+-----------------------------+------------------+
6 rows in set (0.00 sec)

show full processlist 可以看到所有链接的情况，但是大多链接的 state 其实是 Sleep 的，这种的其实是空闲状态，没有太多查看价值；我们要观察的是有问题的，所以可以进行过滤：

-- 查询非 Sleep 状态的链接，按消耗时间倒序展示，自己加条件过滤
select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc 

mysql> select id, db, user, host, command, time, state, info from information_schema.processlist where command != 'Sleep' order by time desc \g;
+--------+------+-----------------+-----------+---------+------+-----------------------------+---------------------------------------------+
| id     | db   | user            | host      | command | time | state                       |                        info                 |
+--------+------+-----------------+-----------+---------+------+-----------------------------+---------------------------------------------+
|      1 | NULL | event_scheduler | localhost | Daemon  |  515 | Waiting for next activation | NULL                                        |
| 239904 | NULL | root            | localhost | Query   | 1044 | executing                   | select * from T like `name` like '%陈哈哈%' |
+--------+------+-----------------+-----------+---------+------+-----------------------------+---------------------------------------------+
2 rows in set (0.00 sec)

这样就过滤出来哪些是正在干活的，然后按照消耗时间倒叙展示，排在最前面的，极大可能就是有问题的链接了，然后查看 info 一列，就能看到具体执行的什么 SQL 语句了，针对分析。

一般来说，要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

面试题2：什么是存储过程？有哪些优缺点

存储过程（Procedure）是一条或多条预编译的SQL语句，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

优点

在数据库中集中业务逻辑

我们可以使用存储过程来实现可被多条SQL的业务逻辑，存储过程有助于减少在许多应用程序中重复相同逻辑的工作。

使数据库更安全

数据库管理员可以为仅访问特定存储过程的应用程序授予适当的特权，而无需在基础表上授予任何特权。

较快的执行速度

如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

缺点

不可移植性

每种数据库的存储过程不尽相同，如果MySQL使用大量的存储过程，当你们想切换成Oracle时，就会发现是多么的不切实际。

复杂存储过程消耗资源多

如果存储过程中逻辑比较复杂，包含多条SQL，则每个连接的内存使用量可能将大大增加，执行时间也会很长，要有所准备。

故障排除难

调试存储过程很困难。不幸的是，MySQL没有像其他企业数据库产品（如Oracle和SQL Server）那样提供任何调试存储过程的功能。存储过程可能会封装很多业务细节，可能会导致开发人员难以理解业务，试想一下一条前辈留下来的几百行的存储过程，老板突然让你改实现逻辑，你懵逼不？

维护成本高

开发和维护存储过程可能非专业人员搞不定，新手很容易留坑或者浪费很多时间。

普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。存储过程可以快速解决问题，但是移植性、维护性、扩展性不好，它有时会约束软件的架构，约速程序员的思维，在你的系统没有性能问题时不建议用存储过程。如果你要完成的功能只是一次或有限次的工作，如数据订正、数据迁移等等，存储过程也可以拿上用场。

如果你的系统很小，并且有50%的开发人员熟练掌握PL/SQL，人员结构稳定，那存储过程可以减少很多代码量，并且性能不错。当系统变复杂了，开发人员多了，存储过程的弊端就会呈现，这时你需要痛下决心了。

 面试题3：比如有个用户表，身份证号字段唯一，那么基于这个字段建索引的话，从效率上讲，你会有哪些考虑呢？

答案参考林晓斌的MySQL实战45讲

如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。如果从效率上讲，主要关注点还是在SELECT和UPDATE操作上；

对于一条SELECT查询来说：

假设，执行查询的语句是 select id from T where id=5。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，然后取出该叶子节点所在的数据页（先判断changebuffer内存中是否有该页，没有就先从磁盘中读到内存），最后通过二分法在数据页中定位id=5的行数据。

• 对于普通索引：查到第一条id=5后，然后继续往后查找直到碰到第一个id!=5的记录时，结束。
• 对于唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，直接结束。

这两者性能差距会有多少呢？微乎其微。对于普通索引，因为本身就是以数据页为单位读进内存，数据页大小默认16KB（大概1000行），要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

对于一条UPDATE查询来说：

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为merge。除了（SELECT）访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用 change buffer 呢？对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 id=5 这条记录，就要先判断现在表中是否已经存在 id=5 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

那么如果要在这张表(id,name)中插入一个新记录 (5,“陈哈哈”) ，InnoDB 的处理流程是怎样的呢？

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

之前我就碰到过一件事儿，有个 DBA 的同学跟我反馈说，他负责的某个业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，我发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引。

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注猪先飞的更多内容！