软件信息网 前沿探讨 主键索引和唯一索引的区别?

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

mysql索引有:

索引类型 特点
1 主键索引 非空唯一索引,一个表只有一个主键索引。primary key('name')
2 唯一索引 可以有一个null值,但不可以有相同
3 普通索引 允许出现相同
4 组合索引 多个key组成的索引
5 全文索引 倒排索引。前面四个都是正排索引。

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

  1. 如果使用了primary key,就是主键索引。
  2. 如果没有primary key,则从非空唯一索引中选择,从上往下选择第一个。
  3. 如果前两种都没创建,就自动生成一个主键索引,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)。

答案:

  1. 主键索引 非空唯一,唯一索引允许为空唯一
  2. 主键索引可能从唯一索引中进行选取
  3. 主键索引对应的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 读写都加锁

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

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

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

 

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

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

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

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

例如:

select * from t where id=3;

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

insert (3....)

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

本文来自网络,不代表软件信息网立场,转载请注明出处。软件定制开发交流:15528175269(微信同号)https://www.saasyo.com/xz/15761.html

作者: 王鹏程序员

上一篇
下一篇
联系我们

联系我们

15889726201

在线咨询: QQ交谈

邮箱: 187395037@qq.com

工作时间:周一至周五,9:00-17:30,节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部