数据库面试简答、30道高频面试题（一）-阿里云开发者社区

一、MySQL问答#

1、数据库sql语句查询，跨表查询有哪几种方式#

内连接(inner可以不写)

select e.name e.age p.product_name p.saled
from employee e，product p 
where e.id = p.id
select e.name e.age p.product_name p.saled
from employee inner 
join e，product p on e.id = p.id

这就是内连接，它要求数据必须On条件必须百分百匹配才会符合条件并返回。当不满足时，他会返回空。

外连接是用左\右侧的数据去关联另一侧的数据，即使关联不上任何数据也得把左\右侧的数据返回回来。

外连接分（左外连接）和（右外连接）

左外连接（ left join）

select g2.Name，Price，ProductionDate，Amount，g1.name
FROM Goods G1
left join GoodsType G2 on G1.Typeld=G2.IO

右外连接（right join--空值的会显示出来）

select g2.Name，Price，ProductionDate，Amount，g1.name
FROM Goods G1 right join GoodsType G2 on G1.Typeld=G2.IO

全外连接（full outer(可以不写) join--空值的会显示出来）

select g1.name，g2.Name，price，productiondate，g2.Amount
FROM GoodsType g1 full outer join Goods g2 on g1.IO=g2.Typeld

交叉连接（笛卡尔积)查询所有的值

select g1.name，g2.Name，price，productiondate，g2.Amount
FROM GoodsType g1 cross join Goods g2 where g1.IO=g2.Typeld

2、数据库的索引用到的是什么数据结构？#

答：B+树

问：那么B+树的特点是什么？为什么要用这个数据结构？

B+树是B树的变种，他们可以是 23树，234树，2345树等等，当单个节点允许伸出1200节点时，三层就可以有17亿，因此它体型扁平。。。有利益磁盘IO

B+树非叶子结点不存储数据，B树存储数据，所以相同大小数据块，能存更多B+索引

B+树叶子结点上有双向链表串联，有利于进行范围搜索

B+树为什么有利于磁盘IO？

首先了解一下计算机的空间局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

使用红黑树（平衡二叉树）结构的话，每次磁盘预读中的很多数据是用不上的数据。因此，它没能利用好磁盘预读的提供的数据。然后又由于深度大（较B树而言），所以进行的磁盘IO操作更多。

B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。

B树的查询，主要发生在内存中，而平衡二叉树的查询，则是发生在磁盘读取中。因此，虽然B树查询查询的次数不比平衡二叉树的次数少，但是相比起磁盘IO速度，内存中比较的耗时就可以忽略不计了。因此，B树更适合作为索引。

比B树更适合作为索引的结构是B+树。MySQL中也是使用B+树作为索引。它是B树的变种，因此是基于B树来改进的。为什么B+树会比B树更加优秀呢？

B树：有序数组+平衡多叉树。

B+树：有序数组链表+平衡多叉树。

B+树的关键字全部存放在叶子节点中，这样非叶子结点就能在相同的空间存储更多的信息，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。

3、mylsam、innodb的区别#

1.InnoDB和MyISAM都是B+数的结构。

2.InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPETABLE READ （可重复读），并且通过间隙锁策略防止幻读的出现。

3.InnoDB表是基于聚簇索引建立的。

4.InnoDB支持事务。

5.InnoDB具有自动崩溃恢复功能。

6.InnoDB支持外键。

MyISAM

1.MyISAM 不支持事务和行级锁。

2.崩溃后无法安全恢复。

3.对于只读的数据，或者表比较小，可以忍受修复操作的可以使用。

4.MyISAM会将表存储在两个文件中，数据文件和索引文件，分别以.MYD和.MYI为扩展名。

5.MyISAM 支持全文索引。

	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

4、MySQL的Gtid复制原理是什么？#

mysql主从复制原理就是主库创建一个专门用于给从库拉取binlog的账号，并且给这个账号授权，让他可以拉取哪个DB的那个表的binlog，具体的授权SQL是：

grant repliacation slave on xx.xx to username@ip identify by 'password'

这样从库就能登陆主库拉取binlog，那拉取binlog就得知道从哪个binlog的哪个位点拉取，现有的有两个方案：fileName + position 还有就是通过gtid自动找点。

什么是GTID？原理？

https://www.cnblogs.com/ZhuChangwu/p/13040214.html

5、同步、半同步、异步复制原理是什么？#

同步、半同步、异步复制说的是从库在主库上拉取binlog日志的模式。

同步：

主库写redolog 事物处于prepare状态、主库写binlog，然后从库拉取binlog去回放，从库回放成功后返回给主库ack确认，所有的从库都完成回放后主库提交事物。这样是可以保证主从数据一致的但是缺点就是速度太慢了。

半同步：

主库写redolog 事物处于prepare状态、主库写binlog，然后从库拉取binlog后返回给主库ack，在众多从库中只要收到一个ack主库就提交事物

异步复制：

主库根本不管从库有没有拉取回放binlog，直接写redo、binlog、然后提交事物

首先不允许出现主从数据不一致的情况：如果主从不一致对业务来说是有损的，一旦发生主从数据不一致的情况，从库就会出现断开连接的可能。

6、说说你了解的MySQL慢查询？#

MySQL有监控项：slow query ， MySQL会将所有执行时间超过阈值的SQL记录到慢查日志中

我们的监控系统可以监控：当检测到有慢查时触发报警

通常出现慢查到情况如下：

1、表中的数据量很大，而且SQL的执行没有走索引

2、数据量太大了，即使走了索引依然超过了阈值

3、大量的慢查占据MySQL连接，导致正常的SQL得不到连接执行从而变成慢查SQL

4、优化器选错了索引

查看慢查时间阈值

mysql> show global variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row inset (0.00 sec)

查看执行时间最长的10条SQL

mysqldumpslow -s a1 -n 10 mysql.slow_log

7、说说MySQL的执行计划#

什么是执行计划

每次提交一个SQL到MySQL，MySQL内核的查询优化器会针对这个SQL的语意生成一个执行计划，这个执行计划就代表了他会查哪些表？用哪些索引，如何做排序和分组

执行不同的sql有哪几种情况

单表查询举例：

示例1:

select * fromtablewhere id = x ;
select * fromtablewhere name = x ;

id是主键、name是唯一索引像这种可以直接根据聚簇索引或者二级索引+回表就能查询到我们想要的数据的方式在执行计划中称为 const

要求二级索引必须是唯一索引，才属于const

示例2:

select * fromtablewhere name = x ;

name是普通索引查询的过程是：从name这个B+树中查询出一条记录后，得到记录的主键id，然后去聚簇索引中回表，这种查询方式速度也很快，在执行计划中叫：ref

示例3:

select * fromtablewhere name = x and age = y and xx=yy ;

name、age、xx为普通索引这种sql要求where条件之后的SQL全得是等值比较，在执行计划中才算做是ref

示例4:

select * fromtablewhere name = x and name is NULL ;

name为普通索引这种sql就是在二级索引中同时搜索name = x和name = null的值，然后去主库中回表。这种在执行计划中被称为ref_or_null

示例5:

select * fromtablewhere age >=x and age <=y

age是普通索引像这样使用普通索引做范围查询，在执行计划中称为 range

示例6：index方式

index方式并不是说执行计划使用了索引，从聚簇索引中一路二分往下走。

假设有联合索引：key(x1，x2，x3)

查询语句如下：

select x1，x2，x3 fromtablewhere x2=xxx;

想使用联合索引得遵循左前缀原则，但是上面直接使用x2，很显然不符合左前缀原则，所以就用不上联合索引，但是他查询的x1、x2、x3其实对应联合索引中的x1、x2、x3所以他会去扫描联合索引：key(x1，x2，x3)形成的B+树，而不是全表扫描，在执行计划中这就做 index

所以说，index其实是去遍历二级索引，故他的效率肯定比ref，const、ref_or_null慢，但是比全表扫描快一些

示例7：all

比如你去查找数据但是不加where条件，就会进行全表扫描

示例8:

select * fromtablewhere x1 = xxx or x2 >= yy ;

然后你的联合索引是 key1(x1，x3) key2(x2，x4)这时查询优化器只能在key1和key2中二选一使用，具体选哪一个就看使用哪个索引扫描行数少

比如使用x1扫描行数少，就先拿着x1去过滤一部分数据出来（ref的方式）然后去聚簇索引中回表查询所有的数据在内存中根据第二个条件x2 > yy 再过滤一次

示例9:

select * fromtablewhere x1 = xxx and c1=xxx and c2 >= yy and c3 is null;

只有c1有索引查询优化器会根据x1，通过ref的方式查找到一批数据，然后去聚簇索引中回表，将所有符合条件的数据加载进内存，然后在内存中根据剩下的条件继续过滤。

示例10:

select * fromtablewhere x1 = xxx and x2=xxx;

x1和x2都有普通索引情况1: 查询优化器使用x1索引在二级索引中查询中一批数据，然后将这些数据放到聚簇索引中回表，将数据所有字段查询出来，然后在内存中根据x2=xxx再过滤。

情况2:查询优化器使用x1索引在二级索引中查询中一批数据A，再使用x2索引在二级索引中查询中一批数据B，两者做交集，再去聚簇索引中回表，这样的效率会更高。

多表:

示例1:

select * from t1，t2 where t1.x1 = xxx and t1.x2 = t2.x2and t2.x3 = yyy;

第一步：查询优化器会根据t1.x1 = xxx这个条件查询出一部分数据，具体通过ref、index、conf、all根据你索引的情况而定。

假设第一步拿出来了两条记录，然后拿着这两条记录的x2值和x3值去t2表中去匹配有没有一样的，有的话就关联起来返回，其中t1叫做驱动表，t2叫做被驱动表。

示例2:

嵌套循环查询：简单来说就是从驱动表中查询一批数据，然后遍历这批数据挨个去被驱动表中查询。

这时如果被驱动表中的使用的该字段没有加索引，每次查询都是all，就会导致连表查询速度很慢，因此最好两者都建立索引。

explain时你会关注哪几个字段？

答：6个，如下

id：每一个selct语句都有有一个id，复杂的SQL有多个select，就会对应有多个id

select_type: 当前sql的查询类型

type：ref、index、all、const

possible_keys 可以使用的索引都会放在这里

rows：扫描的行数

table：查询的哪张表

8、说说MySQL支持的数据类型#

INT(6)，6即是其宽度指示器，该宽度指示器并不会影响int列存储字段的大小，也就是说，超过6位它不会自动截取，依然会存储，只有超过它本身的存储范围才会截取;此处宽度指示器的作用在于该字段是否有zerofill，如果有就未满足6位的部分就会用0来填充)，

CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围指示器。

CHAR 和 VARCHGAR 不同之处在于 MYSQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。

https://www.cnblogs.com/liangxiaofeng/p/5806874.html

9. 了解数据库如何备份吗#

参考: https://www.cnblogs.com/yourblog/p/10381962.html

备份整个数据库

$> mysqldump -u root -h host -p dbname > backdb.sql

备份数据库中的某个表

$> mysqldump -u root -h host -p dbname tbname1， tbname2 > backdb.sql

备份多个数据库

$> mysqldump -u root -h host -p --databases dbname1， dbname2 > backdb.sql

备份系统中所有数据库

$> mysqldump -u root -h host -p --all-databases > backdb.sql

10. Oracle和Mysql的区别#

宏观上:

Mysql是小型数据库，开源，免费， Oracle收费
Oracle支持大并发，大访问量
MySql中安装后占用的内存小， Oracle不仅占用内存大，而且越用越大

微观上:

Mysql对事务默认不支持，但是它的存储引擎 InnoDB支持事务， Oracle对事务完全支持
并发性: MySQL早期的数据引擎MyISAM是支持表级锁，后来的InnoDB才支持行级锁， Oracle支持行级锁
Oracle会将提交的sql写入连接日志中，然后写入磁盘，保证不会丢失数据， MySql在执行更新的操作时可能会丢失数据
隔离级别不同:
a. Oracle默认使用 read commited 读已经提交
b. MySQL默认使用的是 repeatable read 可重复读
提交方式
a. Oracle 默认不会自动提交事务
b. MySQL默认自动提交事务
逻辑备份
a. Mysql 的数据备份会锁定数据，影响正常的DML
b. Oracle在数据备份时，不会锁定任何数据
数据插入
a. Mysql会更加灵活一点，比如limit分页， insert插入多行数据
b. Oracle的分页使用伪列+子查询实现，插入数据也只能一行行插入
权限控制:
a. Oracle的权限控制是中规中矩的，和系统用户无关
b. MySQL的权限控制和主机相关，感觉没啥意义
性能诊断
a. Oracle 有大量的性能诊断工具，可以实现自动分析
b. Mysql性能诊断方法很少，主要就是通过通过慢查询日志去排查
分区表和分区索引
a. Oracle的分区表和分区索引相对来说比较成熟
b. Mysql 分区表和分区索引就不成熟
数据复制
a. 在搭建的主从复制的模式中，主库出现了问题，可能会导致从库有一定数据的丢失，需要手动的切换的到主库
b. Oracle 则更强大，既有传统的推/拉式的数据复制，同时也有 dataguard双机或者多机的容灾机制，而且主库出现问题，自动切换到备库，但是配置相对复杂

11. 事务的四种特性#

ACID：

Atomic 原子性: 事务不能被分割，要么都做，要么都不做。
Consistency 一致性: 可以用转账的例子解释一致性。
Isolation 隔离性 : 不同的事务，彼此隔离，互不干扰。
Durability 持久性: 也叫做用就行，事务一旦被提交，对数据库做出的修改将被持久化。

12. 四种隔离级别以及什么是脏读，幻读，不可重复读#

read uncommitted 读未提交: 在事务A中读取到了事务B中未提交的数据，也叫做脏读。
read commited 读已提交: Oracle默认使用的隔离级别，读已提交，说白了，事务A先开启，然后事务B再开启，然后事务Bcommit一个事务操作，修改数据，那么这个修改是能被事务A读取到的，这就叫做读已提交，也是所谓的不可重复读，(因为重复读之后，数据可能会发生变化)。
repeatable read : 可重复读，这也是Mysql默认的事务隔离级别，事务A开启后，无论读取多少次，得到的结果都和第一次得到的结果是一样的，但是如果事务B在事务A第一次读取的范围内插入了一条数据的话，会发生幻读，两次读取结果又不一致了， Mysql的InnoDB引擎通过多版本并发控制MVCC解决了这个问题。
serializable : 可串行化，最高的事务隔离级别，到是也是效率最低的事务隔离级别。

13. MySQL中主键索引、普通索引、唯一索引的区别#

主键索引 primary key:

一个表只能有一个主键索引。
主键索引不能为空。
主键索引可以做外键。

唯一索引unique key:

一张表可以存在多个唯一索引。
唯一索引可以是一列或者多列。
唯一索引不可重复的。
因为这个原因，限制唯一索引做多有一个null。

普通索引 normal key :

普通一般是为了加快数据的访问速度而建立的。
针对那些经常被查询，或者经常被排序的字段建立。
被索引的数据允许出现重复的值。

14. 数据库三大范式#

第一大范式:

关系模式R中的所有属性都不能再分解，称关系模式R 满足第一范式，比如 address 字段就可以继续拆分成省市区，我们就可以认为address不满足第一范式。

第二大范式:

在满足第一范式的基础上更进一步，它要求所有的非主属性都必须完全依赖于第一范式中确定下来的主属性，换句话说，比如联合主键就不符合第二范式，因为很有可能这个表中的一部分非主属性和联合主键中的一部分列是有依赖关系的，而和另外一部分并没有依赖关系。

第三大范式:

在第一范式R的基础上，更进一步，要求所有的字段都可主键直接相关而不能间接相关，比如用户表里面不要出现订单表中的订单信息。

15. sql语句各种条件的执行顺序，如select， where， order by， group by#

from where group by having order by limit select

16. 求表的size，或做数据统计可用什么存储引擎#

查询数据表所占的容量

select sum(DATA_LENGTH) + sum(INDEX_LENGTH) from information_schema.tables where table_schema = '数据库名

查询所有数据的大小，用兆的方式输出结果

select concat(round(sum(DATA_LENGTH/1024/1024)，2)，'MB') asdata
from information_schema.tables
where table_schema='blog'andtable_name='catalog'

17. 读多写少可用什么引擎#

MyISAM 它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。

所以，如果系统中的写操作真的很少，并且不使用mysql的事务等高级操作的话，建议使用MYISAM。

18. 假如要统计多个表应该用什么引擎#

考虑报表引擎

19.MySQL Explain各字段意思#

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

20.索引设计的原则？#

适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
基数较小的类，索引效果较差，没有必要在此列建立索引。
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

21. MySQL有关权限的表有哪几个？#

表名	含义
user权限表	记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表	记录各个帐号在各个数据库上的操作权限。
table_priv权限表	记录数据表级的操作权限。
columns_priv权限表	记录数据列级的操作权限。
host权限表	配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。