MySQL常见面试题()

1 主键索引和唯一索引的区别?

mysql索引有:

索引类型 特点
1 主键索引 非空唯一索引,一个表只有一个主键索引。primary key(‘name’)
2 唯一索引 可以有一个null值,但不可以有相同
3 普通索引

允许出现相同

4 组合索引

多个key组成的索引

5 全文索引

倒排索引。前面四个都是正排索引。

允许出现相同

4 组合索引

多个key组成的索引

5 全文索引

innodb主键索引是怎么创建的呢?分为几种情况:

  • 如果使用了primary key,就是主键索引。
  • 如果没有primary key,则从非空唯一索引中选择,从上往下选择第一个。
  • 如果前两种都没创建,就自动生成一个主键索引,rowid作为主键

由于1到4这四种索引都会在内部自动创建B+树,但是这个B+树有不同区别。

  • 主键索引对应的B+树,是一个聚集索引,叶子节点包含行数据。
  • 其他3种索引对应的B+树,都是辅助索引,包含的只有比较key字段,还有bookmark(书签),书签中存储主键id。如果辅助索引中没有需要的信息,则要通过主键id去查聚集索引,得到行数据(select * from T where name =xx)。如果从辅助索引中能得到需要的信息,则不需要再通过聚集索引查找(id 为主键id 。select id, name from T where name =xx)。

答案:

  • 主键索引 非空唯一,唯一索引允许为空唯一
  • 主键索引可能从唯一索引中进行选取
  • 主键索引对应的B+树包含完整行信息。唯一索引对应的B+树只包含key和主键id。

2 什么是覆盖索引?原理是什么?

覆盖索引:

主要是指辅助索引B+树。从辅助索引中就能找到数据,而不需要通过聚集索引查找。

在项目开发过程中,我们会发现很少有查询select * from T where name =xx的情况。通常通过索引

查询主键id  select id, name from T where name =xx。

原理:

        尽量匹配辅助索引的数据,减少回表查询。

        辅助索引包含的信息少,页中能容纳的数据多,树的高度会矮一些,有更少的磁盘io。

3 什么是回表插叙?原理是什么?

当一个查询语句中where条件匹配的是辅助索引B+树,但是select中的字段,有些没有在辅助索引

B+树中,就需要通过主键id再去聚集索引中查询行信息。这个通过主键id再去聚集索引中查询数据

的过程,就叫回表查询。

4 什么是索引下推?原理是什么?

索引下推:是mysql内部优化,mysq 5.6 才有。有时候也叫索引条件下推(index condition

pushdown)

原理:在使用辅助索引进行查询时,将查询条件也推送给聚集索引进行查询,减少回表次数。

讲解案例:

        一张表有 字段:id, name, height,age

        组合索引 (height, age)

        查询:select * from table where height =180 and age >17;

mysql内部在查询时会先通过height =180进行辅助索引查询,假设查到4条数据,因为select * 要查

询全部数据,在未使用索引下推时,还需要查询通过主键id去查询四次主键索引B+树获取息。若使

用索引下推,会把age >17的条件也下推给存储引擎,只做一次主键索引的回表查询。

5 数据库事务的四个特性及含义?

需要数据库事务的前提:通常有多个连接与数据库建立连接,mysql内部会有一个主线程,它用来

      监听连接建立,当每条连接建立时,它会为这条连接建立独立的连接线程。在每个连接线程中

      都独自执行sql语句。mysql内部实际上是多线程并发处理连接命令。

事务的定义:用户定义的操作序列,通俗的说多条sql语句。

事务四个特性:

原子性:事务操作要么都做(提交),要么都不做(回滚),undolog 来实现逆操作回滚。

隔离性:在通常代码编程中会加互斥锁、读写锁,会造成串行执行,在mysql中不允许,因为会造

      成性能降低,mysql是采用制定隔离级别来实现。隔离级别是描述各个事务之间相互的影响程

      度。设计不同的隔离级别,主要用来提升并发性能。隔离级别会用到一些技术,比如MVCC 非

      锁定一致读,即读不加锁,写加锁。

持久性:事务一旦完成,要将数据所做的变更记录下来。redolog 来记录,append的方式追加记录

      操作日志。

一致性:不能违反数据的一致性检测(完整约束检测)。

6 不可重复读与幻读的区别?

需要先了解隔离级别:

隔离级别 特点
read uncommited  读不加锁,也不用MVCC,就写加锁了
read commited 使用mvcc机制,只读最新版本信息,写加锁(排他锁)
repeatable read

读操作也使用mvcc,读事务开始前的版本写也加锁(间隙锁)。

mvcc主要是快照读、当前读。

也可以在事务中做当前读,例如删除、更新、新增。

也可以在sql语句加锁读:

select * from t where … for update;        x锁

select * from t where… lock in share mode;    s锁

serializable 读写都加锁

读操作也使用mvcc,读事务开始前的版本写也加锁(间隙锁)。

mvcc主要是快照读、当前读。

也可以在事务中做当前读,例如删除、更新、新增。

也可以在sql语句加锁读:

select * from t where … for update;        x锁

select * from t where… lock in share mode;    s锁

在read commited中会出现不可重复读和幻读两种异常情况。

在repeatable read中只会出现幻读异常。

造成这两个异常的原因是:因为读未加锁,造成异常读的问题。

不可重复读和幻读的共同点:

        两次读产生的数据不一致。

不可重复读和幻读不同点:

     异常情况 现象 原因
不可重复读 在某一行中两次都不一致 永远读最新的信息。如果另一个事务修改提交了,两次读会结果不一样。
  幻读 两次数据结果集不同

快照读和当前读所造成。

例如:

select * from t where id=3;      

发现没有数据后插入id=3的一行数据:

insert (3….)

select 语句是使用的快照读,insert语句是新增数据,使用的是当前读。(扩展下:为什么insert是写操作,用的是当前读呢?因为mysql用的是就地更新,要进行增删改的前提需要先找到行的数据,在找的过程就是读的行为,这个时候就用的是当前读。)

快照读和当前读所造成。

例如:

select * from t where id=3;      

发现没有数据后插入id=3的一行数据:

insert (3….)

select 语句是使用的快照读,insert语句是新增数据,使用的是当前读。(扩展下:为什么insert是写操作,用的是当前读呢?因为mysql用的是就地更新,要进行增删改的前提需要先找到行的数据,在找的过程就是读的行为,这个时候就用的是当前读。)

————————

1 主键索引和唯一索引的区别?

mysql索引有:

索引类型 特点
1 主键索引 非空唯一索引,一个表只有一个主键索引。primary key(‘name’)
2 唯一索引 可以有一个null值,但不可以有相同
3 普通索引

允许出现相同

4 组合索引

多个key组成的索引

5 全文索引

倒排索引。前面四个都是正排索引。

允许出现相同

4 组合索引

多个key组成的索引

5 全文索引

innodb主键索引是怎么创建的呢?分为几种情况:

  • 如果使用了primary key,就是主键索引。
  • 如果没有primary key,则从非空唯一索引中选择,从上往下选择第一个。
  • 如果前两种都没创建,就自动生成一个主键索引,rowid作为主键

由于1到4这四种索引都会在内部自动创建B+树,但是这个B+树有不同区别。

  • 主键索引对应的B+树,是一个聚集索引,叶子节点包含行数据。
  • 其他3种索引对应的B+树,都是辅助索引,包含的只有比较key字段,还有bookmark(书签),书签中存储主键id。如果辅助索引中没有需要的信息,则要通过主键id去查聚集索引,得到行数据(select * from T where name =xx)。如果从辅助索引中能得到需要的信息,则不需要再通过聚集索引查找(id 为主键id 。select id, name from T where name =xx)。

答案:

  • 主键索引 非空唯一,唯一索引允许为空唯一
  • 主键索引可能从唯一索引中进行选取
  • 主键索引对应的B+树包含完整行信息。唯一索引对应的B+树只包含key和主键id。

2 什么是覆盖索引?原理是什么?

覆盖索引:

主要是指辅助索引B+树。从辅助索引中就能找到数据,而不需要通过聚集索引查找。

在项目开发过程中,我们会发现很少有查询select * from T where name =xx的情况。通常通过索引

查询主键id  select id, name from T where name =xx。

原理:

        尽量匹配辅助索引的数据,减少回表查询。

        辅助索引包含的信息少,页中能容纳的数据多,树的高度会矮一些,有更少的磁盘io。

3 什么是回表插叙?原理是什么?

当一个查询语句中where条件匹配的是辅助索引B+树,但是select中的字段,有些没有在辅助索引

B+树中,就需要通过主键id再去聚集索引中查询行信息。这个通过主键id再去聚集索引中查询数据

的过程,就叫回表查询。

4 什么是索引下推?原理是什么?

索引下推:是mysql内部优化,mysq 5.6 才有。有时候也叫索引条件下推(index condition

pushdown)

原理:在使用辅助索引进行查询时,将查询条件也推送给聚集索引进行查询,减少回表次数。

讲解案例:

        一张表有 字段:id, name, height,age

        组合索引 (height, age)

        查询:select * from table where height =180 and age >17;

mysql内部在查询时会先通过height =180进行辅助索引查询,假设查到4条数据,因为select * 要查

询全部数据,在未使用索引下推时,还需要查询通过主键id去查询四次主键索引B+树获取息。若使

用索引下推,会把age >17的条件也下推给存储引擎,只做一次主键索引的回表查询。

5 数据库事务的四个特性及含义?

需要数据库事务的前提:通常有多个连接与数据库建立连接,mysql内部会有一个主线程,它用来

      监听连接建立,当每条连接建立时,它会为这条连接建立独立的连接线程。在每个连接线程中

      都独自执行sql语句。mysql内部实际上是多线程并发处理连接命令。

事务的定义:用户定义的操作序列,通俗的说多条sql语句。

事务四个特性:

原子性:事务操作要么都做(提交),要么都不做(回滚),undolog 来实现逆操作回滚。

隔离性:在通常代码编程中会加互斥锁、读写锁,会造成串行执行,在mysql中不允许,因为会造

      成性能降低,mysql是采用制定隔离级别来实现。隔离级别是描述各个事务之间相互的影响程

      度。设计不同的隔离级别,主要用来提升并发性能。隔离级别会用到一些技术,比如MVCC 非

      锁定一致读,即读不加锁,写加锁。

持久性:事务一旦完成,要将数据所做的变更记录下来。redolog 来记录,append的方式追加记录

      操作日志。

一致性:不能违反数据的一致性检测(完整约束检测)。

6 不可重复读与幻读的区别?

需要先了解隔离级别:

隔离级别 特点
read uncommited  读不加锁,也不用MVCC,就写加锁了
read commited 使用mvcc机制,只读最新版本信息,写加锁(排他锁)
repeatable read

读操作也使用mvcc,读事务开始前的版本写也加锁(间隙锁)。

mvcc主要是快照读、当前读。

也可以在事务中做当前读,例如删除、更新、新增。

也可以在sql语句加锁读:

select * from t where … for update;        x锁

select * from t where… lock in share mode;    s锁

serializable 读写都加锁

读操作也使用mvcc,读事务开始前的版本写也加锁(间隙锁)。

mvcc主要是快照读、当前读。

也可以在事务中做当前读,例如删除、更新、新增。

也可以在sql语句加锁读:

select * from t where … for update;        x锁

select * from t where… lock in share mode;    s锁

在read commited中会出现不可重复读和幻读两种异常情况。

在repeatable read中只会出现幻读异常。

造成这两个异常的原因是:因为读未加锁,造成异常读的问题。

不可重复读和幻读的共同点:

        两次读产生的数据不一致。

不可重复读和幻读不同点:

     异常情况 现象 原因
不可重复读 在某一行中两次都不一致 永远读最新的信息。如果另一个事务修改提交了,两次读会结果不一样。
  幻读 两次数据结果集不同

快照读和当前读所造成。

例如:

select * from t where id=3;      

发现没有数据后插入id=3的一行数据:

insert (3….)

select 语句是使用的快照读,insert语句是新增数据,使用的是当前读。(扩展下:为什么insert是写操作,用的是当前读呢?因为mysql用的是就地更新,要进行增删改的前提需要先找到行的数据,在找的过程就是读的行为,这个时候就用的是当前读。)

快照读和当前读所造成。

例如:

select * from t where id=3;      

发现没有数据后插入id=3的一行数据:

insert (3….)

select 语句是使用的快照读,insert语句是新增数据,使用的是当前读。(扩展下:为什么insert是写操作,用的是当前读呢?因为mysql用的是就地更新,要进行增删改的前提需要先找到行的数据,在找的过程就是读的行为,这个时候就用的是当前读。)