java面试题 --- MySQL④

1. 一张表的数据量是百万级的，要做分页查询你怎么优化？

• 查询字段少的话可以考虑查询的字段都加索引，使用索引覆盖来提升性能；
• 子查询优化，比如 select * from students where id in ( select id from students where age > 20 limit 100000, 10)。

2. 连接查询要注意什么问题吗？

• 左连接索引加在右表，右连接索引加在左表；
• 永远要用小表驱动大表，比如 A 表数据量小，B 表数据量大，应该用 A join B。

3. count(1)，count(*)，count(字段) 有什么区别？

• count(*) 会统计所有的行，包括为 null 的行，会对所有字段进行扫描；
• count(1) 也是会统计所有的行，包括为 null 的行，但是它只会对表中的一个字段进行扫描，可以理解为表中有个字段的值全部 1；
• count(字段名) 只会统计指定字段列，不包括为 null 的行。

表中只有一个字段时 count(*) 效率最高，count(列名) 当列名是主键时，它的效率高于 count(1)，其他情况 count(1) 效率更高。

4. 线上系统越跑越慢，你怎么排查？

• 开启慢查日志，用日志分析工具分析慢 SQL，查看执行计划；
• 用 show profile 分析 SQL 执行情况。

5. 什么是慢查日志？

• MySQL 会记录执行时间超过阈值的 SQL，默认阈值是 15s，可以在 MySQL 配置文件配置是否开启、时间阈值以及日志文件位置；
• MySQL 自带的工具 mysqldumpslow 可以用来分析慢查日志，比如要得到访问次数最多的 10 个 SQL，执行 mysqldumpslow -s c -t 10 日志文件位置 即可。

6. show profile 又是什么？

• MySQL 提供的用来分析 SQL 执行时资源消耗情况的工具，会保存最近 15 次 SQL 运行情况，通过show variables like 'profiling'查看是否开启，通过set profiling = 'on'开启。执行show profiles可以列出 SQL、执行时间以及它的 id，执行show profile cpu, block io for query sqlId就可以查看该 SQL 执行时的资源消耗情况。

7. 说一说主从复制和读写分离？

• 主从复制可以保证高可用，降低单个服务器的压力，也可以用从库做升级测试。主库负责写数据，从库负责读数据，主库的 bin log 线程会把更新数据的 SQL 记录到自己的 bin log中，IO 线程在从库启动后，会负责从主库拉取 bin log，放到自己的 relay log 中，从库的 SQL 执行线程再执行 relay log 中的 SQL。
• 读写分离依赖于主从复制，主库写，从库读，可以用代理实现，比如 sharding-jdbc、mycat等，也可以用 MySQL 提供的 jdbc 驱动包，在配置 datasourceUrl 的时候就配置好从库。

8. 你用过存储过程吗？

• 用过，一些 SQL 很多地方都要用到，就可以考虑写成一个存储过程。

9. 存储过程和函数有什么异同？

• 存储过程可以有一个或者多个返回值，必须单独调用；函数只能有一个返回值，可以在查询语句中直接调用。

10. 存储过程和函数有什么优缺点呢？

• 执行效率高，因为它们是预编译过的；
• 存储过程代码存放在数据库，可以直接调用，减少网络通讯；
• 安全性高，它们都需要一定的权限才能调用；
• 可以重复使用，减少开发人员的工作量；
• 可移植性差，维护也比较麻烦。

11. 什么是视图？

• 视图是一个虚拟表，但是可以像操作真实表一样操作它。比如你需要查询的字段分布在两张表，除了连接查询，还可以建立视图。视图可以保护数据，只提供需要的列的权限，也可以简化 SQL，提高复用性。视图的列可以来自同一张表，也可以来自不同的表，视图的建立和删除不影响基本表，对视图内容的修改直接影响基本表，视图来自多个基本表时，不允许添加和删除数据。对视图的操作包括增删改查，视图的建立方法是create view <视图名> as <select语句>，查看用describle <视图名>，修改视图用alter <视图名>，删除视图用drop <视图名>。

12. 什么是触发器？

• 特殊的存储过程，触发了特定条件会自动执行，总共有六种触发器，before insert，after insert，before update，after update，before delete，after delete。