Java面试-数据库篇-优化

如何定位慢查询

面试话术

HR：MySQL中，如何定位慢查?
我：
        我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统skywalking，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题
        如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MSQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。(调试环境下)

SQL执行太慢如何进行分析？

possible key 当前sql可能会使用到的索引

Extra 额外的优化建议

---

key 当前sql实际命中的索引
key len 索引占用的大小
通过它们两个查看是否可能会命中索引

type：这条sql的连接的类型，性能由好到差为NULL、system、const、eq rf、ref、range、index、all
system:查询系统中的表
const:根据主键查询
eq_ref:主键索引查询或唯一索引查询
ref:索引查询
range:范围查询--
index:索引树扫描
all:全盘扫描

面试话术

面试官:那这个SQL语句执行很慢,如何分析呢?
候选人:如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

索引

B+树

面试话术

面试官:了解过索引吗?(什么是索引)
候选人:嗯，索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的10成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

面试官:索引的底层数据结构了解过嘛?
候选人:MySQL的默认的存储引擎ImnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是:第一阶数更多，路径更短，第二个磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据，第三是B+树便于扫库和区间查询，叶子节点是一个双向链表。

面试官:B树和B+树的区别是什么呢?
候选人:第一:在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定
           第二:在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表。

聚簇索引和二级索引

回表查询

面试话术

面试官:什么是聚簇索引什么是非聚簇索引?
候选人:聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个,一般情况下主键作为聚簇索引。
           非聚簇索引指的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引。

面试官:知道什么是回表查询嘛?
候选人:嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。
【备注:如果面试官直接问回表，则需要先介绍聚索引和非聚簇索引】

覆盖索引

Mysql超大分页处理

↓↓↓↓↓↓使用覆盖索引优化后↓↓↓↓↓↓

面试话术

面试官:知道什么叫覆盖索引嘛?
候选人:覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
        如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*，尽量在返回的列中都包含添加索引的字段。

面试官:MYSQL超大分页怎么处理?
候选人:嗯，超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。
        先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了，因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

索引创建的原则

面试话术

面试官:索引创建原则有哪些?
候选人:嗯，这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。
        还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

索引失效

面试话术

面试官:什么情况下索引会失效?
候选人:嗯，这个情况比较多，我说一些自己的经验，以前遇到过的。比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。
        我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析

SQL优化

面试话术

面试官:sql的优化的经验
候选人:嗯，这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表.

面试官:创建表的时候，你们是如何优化的呢?
候选人:这个我们主要参考的阿里出的那个开发手册《嵩山版》，就比如，在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型.

面试官:那在使用索引的时候，是如何优化呢?
候选人:【参考索引创建原则 进行描述】

面试官:你平时对sql语句做了哪些优化呢?
候选人:嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用select*，还有就是要注意SQL语句避免造成索引失效的写法;如果是聚合查询，尽量用union all代替union，union会多一次过滤，效率比较低;如果是表关联的话，尽量使用inner join，不要使用用left join 和right join，如必须使用 一定要以小表为驱动。