面试题 Mysql 数据库

多表关联查询

关联查询sql编写的思路：1.先确定所连接的表；2.确定所要查询的字段；3.确定连接条件以及连接方式。

题

假设有t_employee(员工表)和t_dept(部门表)为例：
t_employee表中的dept代表该员工所在的部门

1.查询出员工姓名以及其对应的部门名称（没有部门，部门没有员工不显示）
2.查询所有员工姓名以及他所在的部门名称（没有部门显示为null）
3.查询所有部门名称和它所对应的员工信息（没有员工显示null）
4.查询所有员工和所有部门信息（没有部门或者没有员工都显示null）
5.查询员工以及他的上司的姓名

1.内连接查询

SELECT e.emp_name, d.dept_name FROM t_employee e INNER JOIN t_dept d ON e.dept=d.id
SELECT e.emp_name, d.dept_name FROM t_employee e, t_dept d WHERE e.dept=d.id

2.左外连接查询

SELECT e.emp_name, d.dept_name FROM t_employee e LEFT JOIN t_dept d ON e.dept=d.id

3.右外连接查询

SELECT d.dept_name, e.* FROM t_employee e RIGHT JOIN t_dept d ON e.dept=d.id

4.全外连接查询

SELECT e.*, d.* FROM t_employee e LEFT JOIN t_dept d ON e.dept=d.id
UNION
SELECT e.*, d.* FROM t_employee e RIGHT JOIN t_dept d ON e.dept=d.id
ps:在oracle中，直接就使用full outer join关键字连接两表就行了

5.自连接查询

自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名.
SELECT e.emp_name, d.emp_name FROM t_employee e LEFT JOIN t_employee d ON e.boss_id = d.id

索引是什么？

1. 大大减少服务器需要的扫描量。
2. 帮助服务器避免排序和临时表
3. 随机IO变成顺序IO，减少磁道寻址时间

索引类型？主键、唯一、普通、全文、复合索引

B+Tree

B+Tree是B-Tree的延申
底层数据结构

一个表中有几个索引就有几个B+树。

一个表中可以有多少个索引？

看执行计划。id key，keylength Extral
key代表当前查询是否会用到索引。
type：访问类型。const，system，ref，range，all

一个表中实际数据存储几份？

表中数据插入时，必须跟某个索引绑定存储。
索引的选择顺序是有主键用主键，没主键用唯一键。没有唯一键则用6字节的rowid。
而其他索引的B+树结构中叶子节点放的是数据绑定索引那个key值。

聚簇索引、非聚簇索引

聚簇索引：跟数据绑定存储的索引，如id。
非聚簇索引：跟数据分开存储的索引，如name。
InnoDB是聚簇还是非聚簇
InnoDB至少有一个聚簇索引，可以有多个非聚簇索引。
如果都没有索引，系统生成一个row_id。
.frm表结构，.ibd中数据+索引文件。

2.MyISAM都是非聚簇索引。.frm .myd .myi

为什么使用int类型id自增好

因为InnoDB引擎默认主键是聚簇索引，聚簇索引的数据的物理存放顺序与索引顺序一致，只要索引相邻，那么数据也在相邻磁盘上。（int类型id自增，这与数据库索引的数据结构有关系。）

索引优化

1 回表

根据普通列查到索引值，再回到主键查找。

6字节的role_id

2 索引覆盖（select *）

select id, name FROM Student WHERE id=1（不需要回表）
select * FROM Student WHERE id=1（需要回表）

3 最左匹配

优化器自动匹配
强制执行

4 索引下推

存储引擎中处理

OR会走索引吗？

答：主键会。

优化细节

1. 使用索引列时，尽量不使用表达式。
2. 尽量使用主键，自增（叶子分裂）不会回表。
3. 把某个字符串前x位创建索引。
4. 备注、或中文名称字段尽量不要做索引。如备注信息区分度不高，不加索引。
5. 使用索引扫描来排序。（耗费性能）
6. 题：用256M内存把1T的文件排序。key_Length计算方式。
7. where条件查询时，中间的参数尽量不用 > 或 <
8. 强制类型转换，全表扫描。（整型向字符串转换，隐式转换）
9. 总行数大于80%，需要建索引。小于就不用建索引。
10. 创建索引的列，不允许为空。Mybatis的坑把0当为null。
11. 表连接时，不要超过3张表。（尽可能减少数据冗余）
12. limiit
13. 单表索引数量5个以内。
14. 多列单索引字段数不超过5个。
15. 误区：①索引越多越好；② 过早优化

Explain执行计划

题：用256M内存把1T的文件排序？

参考地址

一次 MySQL 索引面试
https://mp.weixin.qq.com/s/65V-htzeAU1ACBvga8MlaQ
什么是悲观锁和乐观锁？
https://mp.weixin.qq.com/s/I5hHbSeJZlpGcRhV1vOBnw

为什么索引能提高查询速度？
https://mp.weixin.qq.com/s/BTCZikV60bZPWCtZ1to6Qg