一、基础部分
1、为什么使用数据库
数据保存在内存
优点：存取速度快
缺点：数据不能永久保存
数据保存在文件
优点：数据永久保存
缺点：1）速度比内存操作慢，频繁的IO操作。
2）查询数据不方
数据保存在数据库
1）数据永久保存
2）使用SQL语句，查询方便效率高。
3）管理数据方
2、mysql的权限表都有那几个
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
二、引擎
InnoDB：InnoDB引擎提供了ACID事务的支持，并且还提供了外键的约束（外键是建立和加强两张表之间的连接关系），他的设计目的就是处理大容量的数据库系统
MyIASM ：不提供事务支持，也不提供行级锁和事务支持
1、InnoDB和MylASM的区别对比
| MyISAM | Innodb | |
|---|---|---|
| 存储结构 | 每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 | 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB |
| 存储空间 | MyISAM可被压缩，存储空间较小 | InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
| 可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份binlog，或者用mysqldump，在数据量达到几十G的时候就相对痛苦了 |
| 文件格式 | 数据和索引是分别存储的，数据.MYD，索引.MYI | 数据和索引是集中存储的，.ibd |
| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的） | 表级锁定 | 行级锁定、表级锁定，锁定力度小并发能力高 |
| SELECT | MyISAM更优 | |
| INSERT、UPDATE、DELETE | InnoDB更优 | |
| selectcount(*) | myisam更快，因为myisam内部维护了一个计数器，可以直接调取。 | |
| 索引的实现方式 | B+树索引，myisam是堆表 | B+树索引，Innodb是索引组织表 |
| 哈希索引 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
2、MyISAM索引与InnoDB索引的区别总结
- InnoDB索引是聚簇索引（索引和数据在一块），MylSAM是非聚簇索引
- InnoDB主键索引的叶子节点存储这行数据，因此主键索引效率非常高
- MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
- InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
三、索引
1、什么是索引?
- 索引是一种排好序的数据结构，是一种特使的文件包含了数据库表里所有记录的引用指针
- 使用了B+Tree
2、索引有哪几种类型？
- 主键索引：数据列不允许重复，不允许为null
- 唯一索引：数据列不允许重复，允许为null,一个表可以有多个唯一索引
- 普通索引：基本的索引类型，没有唯一的限制性，允许为null
3、索引的数据结构
索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有
Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能快；其余大部分场景，建议选择BTree索引。
4、索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。索引的原理很简单，就是把无序的数据变成有序的查询
- 把创建了索引的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
5、索引的设计原则
- 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
- 基数较小的类，索引效果较差，没有必要在此列建立索引
- 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
- 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
6、创建索引的原则
- 左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a=1 and b=2 and c>3 and d=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- 较频繁作为查询条件的字段才去创建索引
- 更新频繁字段不适合创建索引
- 定义有外键的数据列一定要建立索引
7、索引失效的情况
- 组合索引不遵循最左匹配原则
- 组合索引的前面索引列使用范围查询(<,>,like),会导致后续的索引失效
- 不要在索引上做任何操作（计算，函数，类型转换）
- is null和is not null无法使用索引
- 尽量少使用or操作符，否则连接时索引会失效
- 字符串不添加引号会导致索引失效
- 两表关联使用的条件字段中字段的长度、编码不一致会导致索引失效
- like语句中，以%开头的模糊查询
- 如果mysql中使用全表扫描比使用索引快，也会导致索引失效
8、聚簇索引和非聚簇索引的区别
innodb存储引擎在进行数据插入的时候必须要绑定到一个索引列上，默认是主键，如果没有主键，会选择唯一键，如果没有唯一键，那么会选择生成6字节的rowid，跟数据绑定在一起的索引我们称之为聚簇索引，没有跟数据绑定在一起的索引我们称之为非聚簇索引。
innodb存储引擎中既有聚簇索引也有非聚簇索引，而myisam存储引擎中只有非聚簇索引
9、前缀索引
语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。
前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相
同。
实操的难度：在于前缀截取的长度。
我们可以利用selectcount(*)/count(distinctleft(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录
10、B-Tree和B+Tree的区别
1、在B树中，你可以将键和值存放在内部节点和叶子节点；
2、但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
3、B+树的叶子节点有一条链相连，而B树的叶子节点各自独立
使用B树的好处
B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。
使用B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间
四、事务
1、事务的四大特性（ACID）
- 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
-
一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
-
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
-
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
2、什么是脏读？幻读？不可重复读
- 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
- 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这
- 可能是两次查询过程中间插入了一个事务更新的原有的数据。
- 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
3、什么是事务的隔离级别？MySQL的默认隔离级别是什么？
| 隔离级别 | 脏读 | 不可重复 读 | 幻影读 |
|---|---|---|---|
| READ- UNCOM MITTED | √ | √ | √ |
| READ- COMMIT TED | × | √ | √ |
| REPEATA BLE- READ | × | × | √ |
| SERIALIZ ABLE | × | × | × |
SQL 标准定义了四个隔离级别：
-
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
-
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
-
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
-
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle默认采用的 READ_COMMITTED隔离级别
事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别
五、锁
1、描述一下mysql的乐观锁和悲观锁，锁的种类？
乐观锁并不是数据库自带的，如果需要使用乐观锁，那么需要自己去实现，一般情况下，我们会在表中新增一个version字段，每次更新数据version+1,在进行提交之前会判断version是否一致。
行锁:
- 共享锁：当读取一行记录的时候，为了防止别人修改，则需要添加S锁
- 排它锁：当修改一行记录的时候，为了防止别人同时进行修改，则需要添加X锁
- 记录锁：添加在行索引上的锁
- 间隙锁：锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别
- 临键锁：记录锁+间隙锁
表锁：
- 意向锁：在获取某行的锁之前，必须要获取表的锁，分为意向共享锁，意向排它锁
- 自增锁：对自增字段所采用的特殊表级锁
- 锁模式的含义：
- IX：意向排它锁
- X：锁定记录本身和记录之前的间隙
- S：锁定记录本身和记录之前的间隙
- X,REC_NOT_GAP：只锁定记录本身
- S，REC_NOT_GAP：只锁定记录本身
- X，GAP：间隙锁，不锁定记录本身
- S，GAP：间隙锁，不锁定记录本身
- X，GAP,INSERT_INTENTION：插入意向锁
2、什么是死锁？怎么解决？
- 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
- 常见的解决死锁的方法
- 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
- 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
- 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
- 如果业务处理不好可以用分布式事务锁或者使用乐观锁
3、MySQL中InnoDB引擎的行锁是怎么实现的？
InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id不是索引键那么InnoDB将完成表锁，并发将无从谈
六、MVCC
1、什么是MVCC
MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
2、当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
3、快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
4、当前读、快照读、MVCC的关系
MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现的。
5、MVCC解决的问题
数据库并发场景有三种，分别为：
1、读读：不存在任何问题，也不需要并发控制
2、读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
3、写写：有线程安全问题，可能存在更新丢失问题
MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：
1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题
6、MVCC实现原理
mvcc的实现原理主要依赖于记录中的三个隐藏字段，undolog，read view来实现的
隐藏字段
- 每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段
- DB_TRX_ID
- 6字节，最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id
- DB_ROLL_PTR
- 7字节，回滚指针，指向这条记录的上一个版本,用于配合undolog，指向上一个旧版本
- DB_ROW_JD
- 6字节，隐藏的主键，如果数据表没有主键，那么innodb会自动生成一个6字节的row_id
在上图中，DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID，DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本
undo log
undolog被称之为回滚日志，表示在进行insert，delete，update操作的时候产生的方便回滚的日志当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃，当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除（当数据发生更新和删除操作的时候都只是设置一下老记录的deleted_bit，并不是真正的将过时的记录删除，因为为了节省磁盘空间，innodb有专门的purge线程来清除deleted_bit为true的记录，如果某个记录的deleted_id为true，并且DB_TRX_ID相对于purge线程的read view 可见，那么这条记录一定时可以被清除的）
下面我们来看一下undolog生成的记录链
1、假设有一个事务编号为1的事务向表中插入一条记录，那么此时行数据的状态为：
2、假设有第二个事务编号为2对该记录的name做出修改，改为lisi
在事务2修改该行记录数据时，数据库会对该行加排他锁然后把该行数据拷贝到undolog中，作为 旧记录，即在undolog中有当前行的拷贝副本拷贝完毕后，修改该行name为lisi，并且修改隐藏字段的事务id为当前事务2的id，回滚指针指向拷贝到undolog的副本记录中事务提交后，释放锁
3、假设有第三个事务编号为3对该记录的age做了修改，改为32
在事务3修改该行数据的时，数据库会对该行加排他锁然后把该行数据拷贝到undolog中，作为旧纪录，发现该行记录已经有undolog了，那么最新的旧数据作为链表的表头，插在该行记录的undolo最前面修改该行age为32岁，并且修改隐藏字段的事务id为当前事务3的id，回滚指针指向刚刚拷贝的undolog的副本记录事务提交，释放锁
从上述的一系列图中，大家可以发现，不同事务或者相同事务的对同一记录的修改，会导致该记录的undolog生成一条记录版本线性表，即链表，undolog的链首就是最新的旧记录，链尾就是最早的旧记录。
Read View
Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。
其实Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据
7、RC、RR级别下的InnoDB快照读有什么不同
因为Read View生成时机的不同，从而造成RC、RR级别下快照读的结果的不同
1、在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照即Read View,将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View,所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View,所以对之后的修改不可见
2、在RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动和事务的快照，这些事务的修改对于当前事务都是不可见的，而早于Read View创建的事务所做的修改均是可见
3、在RC级别下，事务中，每次快照读都会新生成一个快照和Read View,这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。
总结：在RC隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View
299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
