MySql数据库

1.什么是索引？

索引是对数据表中一列或者多列的值进行排序的数据结构，用来加快数据库检索数据的速度。

2.MySql索引的类别有哪些？

从数据结构的角度：

B+树索引：最常见的索引类型，基于B+树数据结构(InnoDB和MyISAM引擎、memory引擎)
Hash索引：基于hash表，所以只支持精确查找(时间复杂度O(1))，不支持范围查找(Memory引擎)
全文索引：主要用来查找文本中的关键字(MyISAM,InnoDB)
空间索引：基于R树实现，用于地理数据存储(MyISAM)

从物理存储角度：

聚簇索引：表中记录的物理顺序与键值的索引顺序相同，数据库主键就是聚簇索引
非聚簇索引：表中记录的物理顺序与键值的索引顺序不相同 Mysql中InnoDB引擎的主键索引为聚簇索引，MyISAM存储引擎采用非聚簇索引

从逻辑角度：

普通索引：最基本的索引，没有任何限制
唯一索引：唯一索引是在表上一个字段或者多个字段组合建立的索引，这个字段或者这些字段的组合在表中不允许重复
主键索引：是唯一索引的一种特殊情况，在表的主键上建立索引
组合索引：基于多个字段创建的索引成为组合索引，一个查询可以只使用索引中的一部分，但必须是最左侧部分，比如索引 index(a,b,c),那么只可以支持a|a,b|a,b,c进行查找

3.MySql数据库索引为什么用B+树而不是B树？

回答这个问题，首先要知道什么是B树，什么是B+树

一颗m阶(阶数代表一棵树的节点最多可以拥有的子节点数，3阶B数的节点最多拥有3个子节点)的B树具有以下性质：
- 根节点至少有两个子女
- 每个中间节点都包含k-1个元素和k个孩子，ceil(m/2)<=k<=m
- 每个叶子节点都包含k-1个元素,ceil(m/2)<=k<=m
- 每个叶子节点都在同一层
- 每个节点中元素按照从小到大的顺序排列，且k-1个元素正好是k个孩子元素的值域分划
一颗m阶的B+树具有以下性质：
- 有k个子树的中间节点包含有k个元素(B树是k-1个元素)，每个元素不保存数据，只用来索引，所有的数据都保存在叶子节点
- 叶子节点包含了全部元素的信息，及指向这些元素记录的指针，且叶子节点本身依照关键字的大小从小到大顺序连接
- 所有的中间节点元素都同时存在于子节点，是子节点中最大(或最小)元素

影响索引查找速度的最主要的就是磁盘IO次数，而最坏情况下磁盘IO次数等于索引树的高度，所以我们需要索引树越“矮胖”越好之所以使用B+树做为数据库索引的数据结构，是因为B+树相较于B树有以下几个优点： 主要原因是，B树在提高磁盘IO性能的同时并没有能解决元素遍历效率低下的问题

B+树更少的IO次数：B+树的中间节点不存储卫星数据，意味着同样大小的磁盘页可以容纳更多的节点(每次磁盘IO可以读取到更多的节点)，在查询时IO次数会更少
B+树具有更稳定的查询性能：B+树的查询最终必须查找叶子节点，查询性能更加稳定
B+树的范围查询性能更好：B+树叶子节点形成有序链表，范围查询性能更好(B树则需要不停的中序遍历，B+树直接查找叶子节点的有序链表即可)

4.数据库为什么用B+树而不用红黑树？

红黑树IO次数更多：磁盘IO的最大次数由树的高度决定，红黑树是二叉树，同样数据量的情况下，红黑树的高度会比B+树高出许多
没有很好的利用磁盘预读特性：红黑树没有很好得利用磁盘预读特性(磁盘不是严格的按需读取，而是每次都会预读一部分数据进内存，依据是局部性原理，即当一个数据被用到时，其附近的数据通常会马上被使用) 红黑树逻辑上很近的节点，物理上可能很远，无法很好的利用局部性原理进行预读；B+树的非叶子节点不存储卫星数据，只做为索引，相对来说同样的磁盘页可以存储更多的节点，节点大小设置为磁盘页的大小，充分利用磁盘预读

5.一般什么情况下使用索引？

主键的列上
经常连接的列上，加快连接的速度(join)
经常使用排序的列上(order by)
经常使用在where子句中的列上(where)

6.什么情况下索引会失效？

查询条件中使用or时，如果两个字段有一个没有索引，则索引失效
组合索引，不满足最左前缀匹配
like的模糊查询以'%'开头，例如 like "%w"会导致索引失效
列为字符串，做条件查询时字符串常量未加引号
mysql预计使用全表扫描比使用索引要快

7.InnoDB和MyISAM的区别？

InnoDB支持事务，MyISAM不支持事务
InnoDB支持外键，MyISAM不支持外键
InnoDB是聚簇索引，MyISAM是非聚簇索引
InnoDB不保存表的具体行数，需要的时候要扫描全表；MyISAM专门有一个变量来保存表的行数，执行时只需要读出该变量即可，速度很快
InnoDB最小锁粒度是行锁，MyISAM最小锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新被阻塞

8.怎么修改数据库引擎？

alert table xx type=xxx

9.数据库中有哪些锁？

乐观锁：乐观锁在操作数据时非常乐观，认为别人不会同时修改数据，因此乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据，如果修改了则放弃操作，否则执行操作
悲观锁：悲观锁在操作数据时比较悲观，认为别人会同时修改数据，因此操作数据时会对数据上锁，直到操作完成才会释放锁，上锁期间其他人不能操作该数据
共享锁(Share Lock,S锁)：又被称为读锁，是悲观锁的一种实现方式，多个事务对于同一数据共享一把锁，但是只能读不能修改，也可以再对该数据加共享锁，但不能加排他锁
排他锁(Exclusive Lock,X锁)：又被称为写锁，是悲观锁的一种实现方式，若某事务对数据A上排他锁，则其他事务不能并发读取该数据，并且不能再对该数据加任何类型的锁，获得排他锁的事务即可读取，又可修改

10.乐观锁的实现方式有哪些？

版本号机制和CAS算法

版本号机制：在数据表中加一个版本号字段version，表示数据被修改的次数，每被修改一次版本号就会加一，读取数据时会一并读取这个version字段，当提交更新时，只有当提交数据的版本号大于数据库当前的版本号，才会执行更新；否则认为是过期数据
volatile+CAS算法：CAS算法涉及到三个操作数
- 需要读写的内存值V(存储在主内存中，线程需要从主内存中读取，备份到本地)
- 进行比较的值A(线程从主内存中读取的V的值的本地备份A)
- 想要写入的新值B(用来更新主内存V的值，更新完以后，V=B) 流程：每个线程读取主内存的值(V)后会备份一个副本到本地(A),当执行更新时需要判断V和A的值是否相等，如果相等则用新值B来更新V，使得V=B；否则不予更新，并且不断地重试(自选操作)

CAS算法存在的问题:

ABA问题：一个线程将内存值从A改为B，另外一个线程又将内存值从B改成A(解决方法:在加上版本号)
循环时间长开销大：自旋CAS(不成功一直循环到成功)如果长时间不成功，会给CPU带来非常大的执行开销(限制自旋次数)
CAS的原子操作只能针对一个共享变量(把多个共享变量合并成一个共享变量操作)

11.什么时候会对行或者表加锁？

InnoDB引擎即支持行锁也支持表锁，只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表级锁

12.什么是事务？事物的隔离级别有哪些？

事务是逻辑上的一组操作，要么都执行，要么都不执行事物的特性(ACID):原子性，一致性，隔离性，持久性

原子性：事务是最小的执行单位，不可分割
一致性：是指事务使系统从一个一致的状态转换到另一个一致的状态，一致性是对数据可见性的约束，保证在一个事务中的多次操作的数据中间状态对其他事务不可见的
隔离性：事务之间互不干扰，彼此独立
持久性：事务提交后，对系统的影响是永久的

并发事务带来的问题：

脏读(Dirty Read)：当一个事务正在访问数据并对数据进行了修改，但是这个修改还没有提交到数据库；此时其他事务访问了这个数据，此时访问到的数据为“脏数据”，依据“脏数据”所做的操作可能是不正确的
丢失修改(Lost to modify):指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务修改了数据之后，另一个事务也对该数据进行了修改，导致前一个事物的修改丢失
不可重复读(Unrepeatable read):指在同一个事务内多次访问同一数据，在两次访问该数据之间，有其他的事务访问该数据，并对该数据进行了修改，导致同一事务内对同一数据的访问结果不一致
幻读(Phantom read)：一个事务读取了几行数据，随后另外一个并发事务插入了一些数据，导致前一个事务再次访问该数据时，多出了一部分的数据

并发事务带来的问题需要数据库提供事务间的隔离机制来实现，实现隔离机制的方法主要有：
- 加读写锁
- 一致性快照，即MVCC

事务隔离级别

READ-UNCOMMITED(读取未提交)：最低的隔离级别，事务可以读取其他并发事务尚未提交的数据变更，可能会导致脏读、幻读、不可重复读
READ-COMMITED(读取已提交)：一个事务只能读取并发事务已提交的数据，可以阻止脏读，但是幻读和不可重复度仍有可能发生
REPEATABLE-READ(可重复读)：在一个事务内对同一数据的多次读取结果是一致的，但是无法避免产生幻读
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID，该级别可以防止脏读、幻读、不可重复读

13.mysql的主从复制如何实现的？

mysql的主从复制主要涉及到三个线程：

主节点binlog线程：负责将主服务器上的数据更改写入到二进制日志中(Binary log)
从节点I/O线程：负责从主服务器读取二进制日志，并写入到从服务器的中继日志(Relay log)
从节点SQL线程：负责读取中继日志，解析出服务器已经执行的数据更改并在从服务器中重放

14.什么是MVCC?MVCC是怎么实现的？

MVCC：多版本并发控制，InnoDB存储引擎默认隔离级别是Repeatable Read(可重复读)，MVCC是行级锁的一种实现，MVCC在Read Commited和Repeatable隔离级别下生效

**实现原理：**MVCC是通过在每行的后面保存两个隐藏的列来实现的，一个保存行的创建时间，一个保存行的过期时间，保存的并不是实际上的时间，而是系统版本号，每开始一个事务，系统版本号就会加一，同时这个系统版本号会作为事务的版本号，用来和每行记录的版本号进行比较

Select：InnoDB查询返回满足的条件是：创建时间<=当前事务版本<过期时间或者过期时间未定义
- 记录的创建时间小于等于当前事务的版本号(确保当前事务查询的结果是要么在事务开始前就存在的，要么是事务自己插入或者修改过的)
- 记录的过期时间要么未定义要么大于当前事务的版本号(确保事务读取到的行在事务开始前未过期)
insert：InnoDB每插入一个新的行，保存当前事务版本号作为开始时间
update：InnoDB执行update实际上是插入一条新纪录，并将原纪录的过期时间设置为当前事务版本号
delete：InnoDB执行delete时，删除的行的过期时间设置为当前事务版本号

15.为什么(InnoDB)推荐使用自增列做主键？

因为InnoDB使用聚簇索引，数据记录本身就存储在B+树的叶子节点上，且按照主键由小到大顺序排列，这就要求每个叶子节点(大小为一个内存页或者磁盘页大小)中的关键字都是顺序排列的，如果使用自增主键的话，那么每次插入新的数据只需要根据其主键将他插入到适当的节点和位置，如果页面达到装载因子(InnoDB是 15/16)，那么就会开辟一个新的页来存储，这样是效率很高的，而且得到的也会是一个紧凑的索引结构，也不会增加很多开销在索引上；

如果不适用自增列做主键，比如使用身份证号或者学号之类的做主键的话，那么每次插入新的记录对应的主键基本上可以看作是随机的，那么在找他的插入位置的时候就会带来很多不必要的开销，比如有些页面已经回写到磁盘上了，但是要做插入可能还要再次读取这个页面，就带来了很多额外的开销，并且频繁的移动、分页可能会导致索引的结构不够紧凑，有大量的空间碎片得不到利用

所以一般都推荐使用自增列做主键

16.数据库分库分表

17.数据库三范式？有哪些反范式的设计？

第一范式：强调列的原子性，即列不能再分为其他几列
第二范式：首先要满足第一范式，其次需要满足表必须有一个主键，非主键列要完全依赖于主键，而不能只依赖于主键的一部分
第三范式：首先满足第二范式，在第二范式的基础上消除传递依赖，即非主键列要直接依赖于主键，不可以传递依赖

MySql数据库知识点总结