秋招Day14 - MySQL - 场景题

如何查找一个student表中每个班年龄排名在前两名的所有人？

年龄排名前两名，比如一个班的人中的年龄有19 18 17，19和18是最大的两个年龄，但是会有很多人同岁。

SELECT a.class, a.name, a.ageFROM students AS aWHERE (SELECT COUNT(DISTINCT age)FROM student AS bWHERE b.class = a.class AND b.age > a.age                ) < 2ORDER BY a.class, a.age;

子查询同一个班中的所有大于a.age的年龄数字，如果这个数字小于2，就说明a是可以的

两个表，一个表1000W数据，另一个表只有几千数据，要做一个关联查询，如何优化？

为关联字段建立索引

小表驱动大表：根据小表的字段值，利用大表的索引快速定位数据

对于一个新建表，先插数据还是先建索引效率高？

如果要插入的数据量很大，先插数据再建索引效率高，因为每次插入数据也要同时更新索引，可能会造成频繁的页分裂和索引结构调整，引入性能开销

小批量插入可以先建索引

什么是深分页？select * from tbn limit 10000000, 10这个语句有什么问题?

深分页是指随着页号越来越大，比较靠后的页面的offset也随之变大，导致需要从头扫描并跳过的数据行越来越多，有严重的性能问题。

延迟关联：可以先在子查询中借助主键索引查出页面的开头，然后开始查找页面中的元素

或者可以用书签，记住上一次分页的最大主键id

一个学生成绩表，求每个班的前十名

用窗口函数ROW_NUMBER()为每一行打编号

PARTITION BY class类似于GROUP BY但是行数不减少

ORDER BY score按照成绩排序

SELECT student_name, class, score, rn
FROM (SELECT student_name, class, score,ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rnFROM student_scores
) AS tmp
WHERE rn <= 10;