mysql索引有:
索引类型 | 特点 |
1 主键索引 | 非空唯一索引,一个表只有一个主键索引。primary key('name') |
2 唯一索引 | 可以有一个null值,但不可以有相同 |
3 普通索引 | 允许出现相同 |
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 | 读写都加锁 |
在read commited中会出现不可重复读和幻读两种异常情况。
在repeatable read中只会出现幻读异常。
造成这两个异常的原因是:因为读未加锁,造成异常读的问题。
不可重复读和幻读的共同点:
两次读产生的数据不一致。
不可重复读和幻读不同点:
异常情况 | 现象 | 原因 |
不可重复读 | 在某一行中两次都不一致 | 永远读最新的信息。如果另一个事务修改提交了,两次读会结果不一样。 |
幻读 | 两次数据结果集不同 | 快照读和当前读所造成。
例如: select * from t where id=3; 发现没有数据后插入id=3的一行数据: insert (3....) select 语句是使用的快照读,insert语句是新增数据,使用的是当前读。(扩展下:为什么insert是写操作,用的是当前读呢?因为mysql用的是就地更新,要进行增删改的前提需要先找到行的数据,在找的过程就是读的行为,这个时候就用的是当前读。) |