03MySQL面试宝典

目录

1.数据库架构

1.1关系型数据库主要考点

1.2如何设计一个关系型数据库

2.索引模块

2.2运用二叉查找树。

2.3.运用B树。

2.4.运用B+Tree

2.5.运用Hash和BitMap

2.6.密集索引和稀疏索引的区别

额外知识

2.7.如何调优SQL(重点)

2.8.最左索引匹配原则

2.9.索引是建立的越多越好吗？

3.锁模块之MyISAM与InnoDB锁方面的区别

3.1.MyISAM与InnoDB锁方面的区别2

乐观锁的实现

3.2.数据库事物的四大特性

3.3.事务并发产生问题及事务隔离机制

3.4.当前读和快照读

3.5.RR如何避免幻读

3.6锁小结：

---

提前声明：本篇文章是我个人看视频截图的杂乱笔记，各位大佬不要较真。也许只有自己看的懂的笔记。

1.数据库架构

1.1关系型数据库主要考点

架构，索引，锁，语法，理论范式

 

 

 

1.2如何设计一个关系型数据库

记一个东西最好的方法就是站在设计者的角度去设计他。

储存管理，缓存机制，SQL解析，日志管理，权限划分，容灾机制，索引管理，锁管理。

锁管理和索引管理是重点，圈起来，必考。

 

 

2.索引模块

常见问题：

（1）为什么要使用索引？

避免全表扫描，快速查询数据。

（2）什么样的信息能成为索引？

主键，唯一键以及普通健等。

（3）索引的数据结构？

生成索引，建立二叉查找树进行二叉查找。

生成索引，建立B Tree结构进行查找。

生成索引，建立B+ Tree结构进行查找。

生成索引，建立Hash结构进行查找。

其中MySQL不支持BitMap。 

 

（4）密集索引和稀疏索引的区别？

密集索引文件中的每个搜索码值都对应一个索引值。

稀疏索引文件只为索引码的某些值建立索引值。

 

2.2运用二叉查找树。

二叉查找树

左子树和右子树

 

2.3.运用B树。

定义：

根节点至少包含两个孩子。

树中每个节点最多含有m个孩子（m>=2）。

除根节点和叶节点外，其它每个节点至少有ceil（m/2）个孩子。

所有的叶子节点都位于同一层。

概述

假设每个非终端结点中包含有n个关键字信息，其中

2.4.运用B+Tree

B+树概述

结论

B+Tree更适合用来做存储索引：

B+树的磁盘读写代价更低，

B+树的查询效率更加稳定，

B+树更有利于对数据库的扫描。

 

2.5.运用Hash和BitMap

Hash

缺点

例如我们要查询Sandra，经过hash计算会直接找到索引值，那么我们为什么不直接用Hash索引呢？

BitMap

目前只有Oracle支持BitMap索引。

 

2.6.密集索引和稀疏索引的区别

密集索引和稀疏索引

 

额外知识

InnoDB

• 若一个主键别定义，该主键则作为密集索引。
• 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引。
• 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）。
• 非主键索引存储相关键位和其对应的主键值，包含两次查找。

InnoDB使用的是密集索引，将主键组织到一棵B+树中，而行数据存储在叶子节点上，因为InnoDB的主键索引和对应的数据是保存在同一个文件的，所以检索的时候在加载叶子节点的主键进入内存的同时也加载了对应的数据。如果对稀疏索引进行条件筛选，则需要经过两个步骤，第一步在稀疏索引的B+树中检索该键，第二步使用主键在B+树中在执行一步检索操作。

MyISAM的节点结构跟InnoDB基本一致，知识存储的内容不一样，主键索引B+树存储了主键，辅助索引B+树存储了辅助键，表数据存储在独立的地方。

两个B+树索引都使用了一个地址指向真正的表数据。

 

linux文件目录下查看，

ls -lrth /data/database_demo/

上图中，person_info_large是InnoDB，shop_info_small是MyISAM，

frm是表结构，ibd是InnoDB的数据和索引。

MyISAM数据（.MYD）和索引（.MYI）是分开存储的。

 

2.7.如何调优SQL(重点)

如何定位并优化慢查询SQL？

开启慢查询

 

long_query_time 查询超过多少秒记录在慢日志中。

slow_query_log 是否显示慢查询日志。

slow_query_log_file 慢查询日志路径。

-- 查询所有慢查询变量
show VARIABLES like '%query%';

-- 查询慢查询数量
show status like '%slow_quer%'

-- 设置全局变量
set GLOBAL slow_query_log= on;
set GLOBAL long_query_time=1;

设置完之后要重新连接数据库，才能看到生效，这样mysql重启会充值，最好去my.cnf去配置，可以永久生效。

 

我们测试查询一张100w数据的表，让它记录到慢查询日志中。进入我们/var/lib/mysql/iZwz9j02r724hodmqj7awqZ-slow.log日志中，可以看到，第9行正是我刚才测试的慢查询语句。用时6s。

 

explain慢查询

概要描述：
id:选择标识符.
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

 

type性能由高到低，出现all和index，大多数情况下我们需要优化了。

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL<index<range< ref<eq_ref<const<system<NULL（从左到右，性能从差到好）

 

Extra该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

 

如上第4句，我们可以给我们的name添加索引。提高我们查询速度。

 

如何定位并优化慢查询SQL总结：

 

2.8.最左索引匹配原则

 

 

 

2.9.索引是建立的越多越好吗？

 

3.锁模块之MyISAM与InnoDB锁方面的区别

 

MyISAM的执行操作

InnoDB的执行操作

MyISAM默认是表级锁不支持行级锁

InnoDB默认是行级锁也支持表级锁

MyISAM默认会给select语句上共享锁(读锁)

InnoDB默认不会给select语句上共享锁(读锁)

上了共享锁的可以再上共享锁但是不能上排他锁

上了排他锁的不可以再上共享锁和排他锁

 

3.1.MyISAM与InnoDB锁方面的区别2

 

乐观锁的实现

先执行1窗口的命令更新，

再执行窗口2，会发现money的值没有变。 

 

3.2.数据库事物的四大特性

• Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

 

3.3.事务并发产生问题及事务隔离机制

并发访问的三个问题

并发访问的问题	含义
脏读	一个事务读取到了另一个事务没有提交的数据
不可重复读	一个事务读取2次的数据不同，数据不能重复读取。在update的时候会引发这个问题。
幻读/虚读	一个事务读取2次数据的个数不同，在insert和delete会引发这个问题

 

 

四种隔离级别：

1是最低，4是最高

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

设置数据库隔离级别： set global transaction isolation level 隔离级别；

解决脏读的方案： 把数据库的隔离级别提高至read committed;

不可重复读的解决 方案： 把数据库的隔离级别再次提升至： repeatable read;

虚读的解决方案： 把数据库的隔离级别提高serializable ，但是几乎没有人去使用这种隔离级别。serializable 串行化，只允许单事务执行。

 

举例：脏读，

1.鲁夫开启事务start transaction。

2.鲁夫账户有1000，账户取出100块。

3.娜美开启事务start transaction。

4.娜美查询鲁夫的账户余额，发现有900。（这个问题显示出来了，鲁夫这边还没有commit事务，娜美就能查询到余额）

5.娜美从账户里面取出100块。（900-100）

6.娜美提交事务。

7.鲁夫这边rollback回滚事务，也就是取消操作。

8.鲁夫再查询余额，发现账户上只有800块钱。（问题来了，我1000块，取消操作了，怎么就剩800块了）。

 

3.4.当前读和快照读

实现原理

窗口1

#快照读,（account为0）
start transaction;
select * from tb_account where id=2;

窗口2执行
start transaction;
update tb_account set account=600 where id=2 ;
commit;

再回到窗口1执行，会发现结果与第一次一致。

select * from tb_account where id=2;

窗口1用

#当前读（结果更新为600了）
select * from tb_account where id=2 LOCK in SHARE MODE;

也就是说我们的select * from tb_account where id=2; 查询的是我们快照中的数据。

 

 

 

3.5.RR如何避免幻读

 

3.6锁小结：

 

 

4.关键语法讲解

GROUP BY

上图案例中，如果我们加多一个查询字段如student_name，那么sql语句会报错，因为student_name不满足列函数也满足分组列。

错误eg: select student_id,count(course_id),sum(score),student_name from score group by student_id;

如果是from后加多一个student表，那么sql语句能生效.

eg:select s.student_id,count(s.course_id),sum(s.score),stu.student_name from score s,student stu where stu.id=s.student_id group by student_id;

HAVING

练习案例