14道MySql面试题及答案分享

1.事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素。acid,原子性(atomicity)、一致性(correspondence)、隔离性(isolation)、持久性(durability)。

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚(rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性:隔离状态执行事务，使它们好像是在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。

持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

2.视图的作用，视图可以更改么?

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据;视图创建后，可以使用与表相同的方式利用它们。

视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。

创建视图：create view xxx as xxxxxxxxxxxxxx;

对于某些视图比如未使用联结子查询分组聚集函数distinct unio

3.数据库范式

1 第一范式(1nf)

在任何一个关系数据库中，第一范式(1nf)是对关系模式的基本要求，不满足第一范式(1nf)的数据库就不是关系数据库。

所谓第一范式(1nf)是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式(1nf)中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

2 第二范式(2nf)

第二范式(2nf)是在第一范式(1nf)的基础上建立起来的，即满足第二范式(2nf)必须先满足第一范式(1nf)。第二范式(2nf)要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

第二范式(2nf)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

3 第三范式(3nf)

满足第三范式(3nf)必须先满足第二范式(2nf)。简而言之，第三范式(3nf)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式(3nf)也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。(我的理解是消除冗余)

4.数据库优化的思路

这个我借鉴了慕课上关于数据库优化的课程。

1.sql语句优化

1)应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2)应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3)很多时候用 exists 代替 in 是一个好的选择

4)用where子句替换having 子句 因为having 只会在检索出所有记录之后才对结果集进行过滤

2.索引优化

看上文索引

3.数据库结构优化

1)范式优化： 比如消除冗余(节省空间。。) 2)反范式优化：比如适当加冗余等(减少join) 3)拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘i/o，一个精心设置的分区可以将数据传输对磁盘i/o竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

4)拆分其实又分垂直拆分和水平拆分： 案例： 简单购物系统暂设涉及如下表： 1.产品表(数据量10w，稳定) 2.订单表(数据量200w，且有增长趋势) 3.用户表 (数据量100w，且有增长趋势) 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺

方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

5.存储过程与触发器的区别

触发器与存储过程非常相似，触发器也是sql语句集，两者唯一的区别是触发器不能用execute语句调用，而是在用户执行transact-sql语句时自动触发(激活)执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如update、insert、delete这些操作时，sqlserver就会自动执行触发器所定义的sql语句，从而确保对数据的处理必须符合这些sql语句所定义的规则。

6.mysql中myisam与innodb的区别，至少5点

(1)、问5点不同;

1>.innodb支持事物，而myisam不支持事物

2>.innodb支持行级锁，而myisam支持表级锁

3>.innodb支持mvcc, 而myisam不支持

4>.innodb支持外键，而myisam不支持

5>.innodb不支持全文索引，而myisam支持。

(2)、innodb引擎的4大特性

(2)、innodb引擎的4大特性

插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

(3)、2者selectcount(*)哪个更快，为什么

(3)、2者selectcount(*)哪个更快，为什么

myisam更快，因为myisam内部维护了一个计数器，可以直接调取。

7.mysql中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别

char是一种固定长度的类型，varchar则是一种可变长度的类型

(2)、varchar(50)中50的涵义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)

(3)、int(20)中20的涵义

是指显示字符的长度

但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0

20表示最大显示宽度为20，但仍占4字节存储，存储范围不变;

(4)、mysql为什么这么设计

对大多数应用没有意义，只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;

8、问了innodb的事务与日志的实现方式

(1)、有多少种日志;

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

慢查询日志：设置一个阈值，将运行时间超过该值的所有sql语句都记录到慢查询的日志文件中。

二进制日志：记录对数据库执行更改的所有操作。

中继日志：

事务日志：

(2)、事物的4种隔离级别

隔离级别

读未提交(ru)

读已提交(rc)

可重复读(rr)

串行

(3)、事务是如何通过日志来实现的，说得越深入越好。

事务日志是通过redo和innodb的存储引擎日志缓冲(innodb log buffer)来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往innodb存储引擎的日志

的日志缓存里面插入事务日志;当事务提交时，必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制)，也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

9、sql优化

(1)、explain出来的各种item的意义;

select_type

表示查询中每个select子句的类型

type

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

possible_keys

指出mysql能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

显示mysql在查询中实际使用的索引，若没有使用索引，显示为null

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

extra

包含不适合在其他列中显示但十分重要的额外信息

(2)、profile的意义以及使用场景;

查询到 sql 会执行多少时间, 并看出 cpu/memory 使用量, 执行过程中 systemlock, table lock 花多少时间等等

10.一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

1、如果a表tid是自增长,并且是连续的,b表的id为索引

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果a表的tid不是连续的,那么就需要使用覆盖索引.tid要么是主键,要么是辅助索引,b表id也需要有索引。

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

11. 主键 超键 候选键 外键

1)超键(super key) ：在关系中能唯一标识元组的属性集称为关系模式的超键

2)候选键(candidate key)：不含有多余属性的超键称为候选键

3)主键(primary key)：用户选作元组标识的一个候选键程序主键

4)外键(foreign key)：如果关系模式r1中的某属性集不是r1的主键，而是另一个关系r2的主键则该属性集是关系模式r1的外键

12、 对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

a)、索引的目的是什么?

快速访问数据表中的特定信息，提高检索速度

创建唯一性索引，保证数据库表中每一行数据的唯一性。

加速表和表之间的连接

使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

b)、索引对数据库系统的负面影响是什么?

负面影响：

创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加;索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间;当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

c)、为数据表建立索引的原则有哪些?

在最频繁使用的、用以缩小查询范围的字段上建立索引。

在频繁使用的、需要排序的字段上建立索引

d)、 什么情况下不宜建立索引?

对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

对于一些特殊的数据类型，不宜建立索引，比如文本字段(text)等

13.什么是存储过程

存储过程是一些编译好的sql语句

因为系统在调用sql的时候比较浪费时间，所以之前先将一些基本的额sql语句代码进行编译(对单表或多表的增删改查)，然后再给代码取一个名字，在需要这个功能时去调用它就可以了。

优缺点

存储工程是编译后的代码 效率高

存储过程代替sql语句，降低网络通信

在一定的程度确保数据安全

14.数据库中的乐观锁和悲观锁

根据不同类型可以对数据设置不同的锁权限

** 乐观 悲观 锁 主要是作用在并发访问控制**

悲观锁 假定会发生并发冲突，屏蔽任何违反数据完整的操作

乐观锁 假定不会发生冲突，只有在提交操作时检查是否违反数据的完整性