javaMySQL面试题-SQL 查询

1 、SQL语句的执行过程

①通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限

②Mysql8.0之前开看是否开启缓存，开启了 Query Cache 且命中完全相同的 SQL 语句，则将查询结果直接返回给客户端;

③由解析器进行语法语义解析，并生成解析树。如查询是select、表名tb_student、条件是id='1'

④查询优化器生成执行计划。根据索引看看是否可以优化

⑤查询执行引擎执行 SQL 语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。

2 、回表查询和覆盖索引

普通索引(唯一索引+联合索引+全文索引)需要扫描两遍索引树

(1)先通过普通索引定位到主键值id=5;

(2)在通过聚集索引定位到行记录;

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

覆盖索引：主键索引==聚簇索引==覆盖索引

如果where条件的列和返回的数据在一个索引中，那么不需要回查表，那么就叫覆盖索引。

实现覆盖索引：常见的方法是，将被查询的字段，建立到联合索引里去。

3 Explain 及优化

参考：https://www.jianshu.com/p/8fab76bbf448

索引优化：

①最左前缀索引：like只用于'string%'，语句中的=和in会动态调整顺序

②唯一索引：唯一键区分度在0.1以上

③无法使用索引：!= 、is null 、 or、>< 、(5.7以后根据数量自动判定)in 、not in

④联合索引：避免select * ，查询列使用覆盖索引

语句优化：

①char固定长度查询效率高，varchar第一个字节记录数据长度

②应该针对Explain中Rows增加索引

③group/order by字段均会涉及索引

④Limit中分页查询会随着start值增大而变缓慢，通过子查询+表连接解决

⑤count会进行全表扫描，如果估算可以使用explain

⑥delete删除表时会增加大量undo和redo日志， 确定删除可使用trancate

表结构优化：

①单库不超过200张表

②单表不超过500w数据

③单表不超过40列

④单表索引不超过5个

数据库范式 ：

①第一范式(1NF)列不可分割

②第二范式(2NF)属性完全依赖于主键 [ 消除部分子函数依赖 ]

③第三范式(3NF)属性不依赖于其它非主属性 [ 消除传递依赖 ]

配置优化：

配置连接数、禁用Swap、增加内存、升级SSD硬盘

4 、JOIN 查询

left join(左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录

inner join(等值连接) 只返回两个表中关联字段相等的行