壹、分享几道关于MySQL索引的重点面试题
简介
索引是一种数据结构,它列出数据库中的一个或多个列值,以帮助数据库高效地检索数据
如果我们打个比方,数据库中的索引就等于书中的目录。
书上说,但即使这样抛出了索引的一个缺点,就是当数据库修改时,修改索引需要花费更多的时间。
但是你真的了解MySQL索引吗?这些问题将帮助您了解有关索引的一些重要知识点
1.什么是最左前缀原则?
以下答案均基于MySQLInnoDB引擎
例如下表
如果我们按照Namefield创建索引,使用树形结构B+索引的大致结构如下。
以“张”开头,即sql语句为
selectIDfromtablewherenamelike'张%'
由于B+树结构索引中,索引项是按照字段顺序排序的索引定义中出现,索引在搜索,此时可以快速找到ID为100的张毅,然后直接遍历所有头部在右边的人,直到满足条件。
也就是说,我们找到第一个符合条件的人后,就可以直接向右走,因为索引是排序的,所有符合条件的人都会聚集在一起。
而这种向左定位,然后向右移动查找,就是我们所说的最左前缀原则。
2为什么使用B+树作为索引而不是哈希表?
1在相应的位置,这样的话,如果我们要进行搜索模糊,显然不支持哈希表结构,我们只能遍历这个表。
B+树可以通过最左前缀原则快速找到相关数据。
2如果我们要进行范围搜索,比如查找ID在100到400之间的人,哈希表也不支持,只能全部遍历。
桌子。
索引字段通过哈希映射到哈希码,如果多个字段关联到相同值的哈希码,则形成的索引结构会过长。
链表,这样的话,搜索时间会大大增加。
3主键索引和非主键索引有什么区别?
例如下面的表(实际上是在上表中添加了一个k字段),ID为主键。
主键索引和非主键索引示意图如下:
其中R代表值整行的。
从图中不难看出,主键索引和非主键索引的区别在于:非主键索引的叶子节点存储的是值主键的索引,而主键索引的叶子节点存储的是主键的整行数据,其中非主键的索引也称为辅助索引,主键的索引为也叫。
聚集索引。
基于这两个结构,我们将运行一个查询,看看它们的查询有什么不同。
1如果查询语句是select*fromtablewhereID=100,即主键查询方式,则只需要查找B+ID树即可。
2如果查询语句为select*fromtablewherek=1,为非主键查询方式,则先在索引k处的树中查找ID=100,然后再查找索引树ID一次这个过程也称为表回滚。
现在,你知道它们之间的区别了吗?
4为什么推荐使用主键自增索引?
对于这个主键索引树
如果我们插入一行ID=650的数据那么我们可以直接插入到最右边
但是如果插入一行ID=350的数据,由于B+树是有序的,所以必须将叶子节点进一步向下移动,以便为插入腾出空间。
对于ID=350的数据,如果R4所在的数据页已经满了,需要进行分页操作,这样会消耗更多的时间,情况会更糟。
但是,如果我们的主键是自增的,每次输入的ID都会比前一次大,那么我们需要每次都插入到后面,而不需要移动位置或者分工等这可以提高性能。
这就是为什么建议使用带有自增主键的索引。
总结
贰、Mysql数据库中sql语句常见的面试题和例子
常见Sql语句示例及解决方案;
根据索引优化SQL语句
MySQL常见面试题
如果tbl_user记录了姓名(name)和学号(ID),SQL中LEFTJOIN的值为__),tbl_score记录了学生的学号(ID)和考试成绩(score),以及学生的考试科目(subject)(有些学生考试后被排除,没有记录)。
想要打印出每个学生的姓名以及对应的每个科目的总成绩,可以使用SQL____语句。
答:自然左外部加入createdatabasephpinterview;usephpinterviewcreatetabletbl_user(IDintnotnull,namevarchar(50)notnull,primarykey(ID));createtabletbl_score(IDintnotnull)。
,scoredec(6,2)notnull,subjectvarchar(20)notnull);insertintotbl_user(ID,name)values(1,'beimu');insertintotbl_user(ID,name)values(2,'aihui');insertintotbl_score(ID,Scores,subjects(1.90,'语文');insertintotbl_score(ID,score,subject)scores(1.80,'数学');insertintotbl_score(ID,score,subject)scores(2.86,'数学')');insertintotbl_score(ID,score,subject)values(2.96,'Chinese');selectA.id,sum(B.score)assumscorefromtbl_userAleftjointbl_scoreBonA.ID=B.IDgroupbyA.id
计算选修课学生总数和统考平均分。
SELECTCOUNT(DISTINCT(Sno))AS学生总数,AVG(Grade)AS平均成绩FROMSC--//使用DISTINCT消除重复行
查询选修2以上学生的平均成绩和课程数培训班。
SELECTStudent.Sname,AVG(Sc.Grade)平均成绩,COUNT(SC.Sno)选择的课程数FROMSC-内联接或内联接,内联接是比较运算符,只返回符合条件的行JOINStudentON(SC.Sno)=Student.Sno)JOINCourseON(SC.Cno=Course.Cno)GROUPBYStudent.SnameHAVINGCOUNT(distinctCourse.Cno)2-组条件
![](http://img1.baidu.com/it/u=3766405413,3350958590&fm=253.jpg)