MySQL相关 索引、sql语句

来源：
1. mysql面试题
2. MySQL索引背后的数据结构及算法原理
3. 索引的本质解析

慢查询如何优化方法之一：加索引

1.索引

索引是帮助MySQL高效获取数据的排好序的数据结构。

二叉树：右斜树没法避免
红黑树：hashmap底层。大数据量的数据，树的高度很大，I/O次数多。特殊的二叉查找树。
Hash表
B-Tree

2.B+Tree

（1）MySQL定义一个节点设置16kB（16384B）
（2）B+树（数据+索引）
特点：叶子节点连起来（提高区间访问的性能，解决范围查找），非叶子节点不存数据，数据和节点一样多
（3）MySQL一个节点可以存
16k/(8B+6B)=1170个索引元素,
3层可以放千万级数据（1170117016）

3.MySQL存储引擎索引实现

MyISAM 和InnoDB是形容数据表的。
（1）MyISAM索引文件和数据文件是分离的，属于非聚集索引
xx.frm：表结构
xx.MYD：数据
xx.MYI：索引 B+Tree结构

（2）InnoDB索引属于聚集索引.
表数据文件本身就是按B+Tree组织的一个索引结构文件。
聚集索引：叶节点包含了完整的数据记录。
xx.frm：表结构
xx.ibd：数据和索引

面试题：
1.为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
InnoDB用B+树来组织。若没有不重复的字段，MySQL自动生成主键。
B+树是有顺序的，int类型比对数字快，占用存储空间更小。
为什么自增？如果是非自增主键，每次新纪录都要被插到现有索引页得中间某个位置，引起数据移动，可能增加磁盘I/O。
2.为什么非主键索引结构叶子节点存储的是主键值
3.为什么不使用hash表？
范围查找效果不好
4.为什么B+树叶子节点用指针连起来？
提高范围查找的功能
5.如何通俗地理解三个范式？
第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
范式化设计优缺点:
优点:可以尽量得减少数据冗余，使得更新快，体积小
缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化
反范式化:
优点:可以减少表得关联，可以更好得进行索引优化
缺点:数据冗余以及数据异常，数据得修改需要更多的成本

4. 欢聚时代 sql题

student表

course表

course_score表

1.张小明同学的科目名称，成绩
select course.course_name,sc.score1 from course left join
(select course_score.course_id,course_score.score score1 from student left join course_score on student.student_id=course_score.student_id where student.student_name=“张小明”) sc
on course.course_id=sc.course_id
2.总分最高的同学的 姓名，分数
select student.student_name,sum(score) sum1 from student left join course_score on student.student_id=course_score.student_id
group by(student.student_id) order by sum1 desc limit 1