Mysql面试复习笔记-CSDN博客

本文链接：https://blog.csdn.net/cxiaobo520/article/details/142470942

索引覆盖理解:创建的索引，该索引包含查询中用的所有字段(叶子节点包含要查询的数据)，称谓索引覆盖。

只要使用到的列全部在索引中就是索引覆盖(不需要回表查询)。

用explian输出的结果，字段Extra的字段Using index时，就能触发索引覆盖。

索引覆盖使用的场景：需要全表count查询优化，分页查询。

索引可以分为：

1.主键索引、普通索引、唯一索引、全文索引、组合(联合)索引。

2.聚集索引与非聚集索引。

3.Btree索引和Hash索引(可以满足"="、"in"查询,不支持范围查找)。

b-tree与b+tree区别：

1.b+tree非叶子节点不存储data元素，而b-tree则非叶子节点存储data元素

2.b-tree没有冗余索引，而b+tree则很多冗余索引

回表：在流程中从非主键索引树搜索回到主键索引树搜索的过程称为回表。

innodb：既有聚集索引也有非聚集索引。

myisam: 非聚集索引。

mysql索引就是b+Tree(是b-Tree的变种)

非聚集索引:数据跟索引分开存储的叫非聚集索引。叶子节点就是主键(存放可能是主键、唯一键、6字节row id); 如果没全部命中索引则会触发回表。

聚集索引: 数据跟索引绑定存储在一起的叫聚集索引。叶子节点就是数据节点,要没主键则会自动找唯一键，要找不到则会生成row id,叶子节点是双向指针(数据按照主键索引进行排序)。

最左前缀原则：是联合索引字段最左边开始匹配，而不是WHERE条件。

索引下推：(数据库版本大于5.6之后)：减少使用二级索引(非主键索引、普通索引、非聚集索引)过滤where条件时的回表次数和减少Mysql Server和引擎层的交互次数。要使用子查询与存储函数则不能使用索引下推。(using index condition)

索引失效:

1.条件字段数据类型为字符串，值为整型;因为数据库只会把字符串转换整型。字段进行操作也会导致索引失效。

2.模糊匹配前面加%，<>、!=。

3.联合索引按索引最左前缀原则。

前缀索引:

优点：索引更小，效率更快。

缺点：无法做分组与排序，也无法做索引覆盖。

sql创建语句：alter table table_name add index index_name(field(3));

mysql事务隔离级别(默认事务隔离级别是可重复读(rr))：

1.读未提交(READ UNCOMMITTED) （可脏读，可幻读，不可重复读）又称为脏读

2.读已提交(READ COMMITTED)（不可脏读，不可重复读,可幻读）又称为不可重复读

3.可重复读(REPEATABLE READ)（不可脏读，可重复读，可幻读）又称为幻读

4.串行化(SERIALIZABLE)（不可脏读，不可重复读，不可幻读）

脏读:事务A读取了事务b更新数据，事务b回滚了，导致事务a读取的数据为脏数据。(解决锁住满足条件的行数据)

不可重复读:事务a多次读取了同一个数据，事务b进行更新操作，导致事务a读取的多次的相同数据结果不一样。

幻读:a事务读取数据结果集只有3行，则b事务又新增了行数据，导致了a事务再去数据库读取数据显示为4行。（解决需要锁表）

MVCC最大好处读不加锁，读写不冲突。

MVCC是多版本并发控制，提高并发访问数据库时(解决数据库读写并发问题)，对事物内读取的到内存处理，用来避免写操作堵塞读操作的并发问题。使用rc(读已提交;每次查询都会生成一个实时的ReadView)与rr(可重复读;每第一次查询都会生成ReadView并沿用到当前事务提交,以此保证可重复读)隔离级别。(ReadView就是一个保存未提交事务Id的List列表)

MVCC原理：通过undo log日志(innodb专有，只记录insert,update,delete,保证事务回滚时数据状态的回滚和MVCC时记录更版本的信息) 每一条记录事务id(trx_id,最近修改事务id或创建当前记录事务id或最后一个修改事务id)与回滚指针(roll_pointer,也就是指向上一个版本)，最终形成版本链。

1.如果被访问版本的trx_id小于列表中最小的事务id(m_ids),证明生成版本的事务在生成ReadView前已提交，所以该版本可以被当前事务访问。

2.如果被访问版本的trx_id大于列表中最大的事务id(m_ids),证明生成版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。

3.如果被访问版本的trx_id在m_ids列表中最大的事务id和最小事务id之间，哪就需要判断一下trx_id是不是在m_ids列表中,如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问，如果不在，说明创建ReadView时生成版本的事务已经被提交，该版本可以被访问。

redo log是Innodb引擎日志，binlog日志是server层记录，redo log日志和binlog日志要保持一致，所以写入redo log日志会标记prepare状态，直到binlong日志完成，才会更新redo log日志为commit状态。

binlog 存储的是SQL语句。

redo log(采用WAL预写式日志，所有修改先写日志再更新到buffer pool。事务执行过程开始写入) 记录表空间中那个页那个位置修改什么。(保证故障后内存中丢失的数据会被恢复到磁盘中)

undo log 记录回滚日志，就是数据修改记录的版本链(实现事务的原子性)，会通过purge线程会自动清理数据。

又分为insert undo log和update undo log;

buffer pool缓存池使用LRU最近最少使用的淘汰机制)算法。

mysql事务的基本要素(ACID):

1.原子性Atomicity（要么做，要么不做）-> undo log实现。

2.一致性Consistency（事务的开始前和结束后，数据库的完整性约束没有被破坏）-> 通过原子性、隔离性、持久性来保证一致性。其他三个都保证一致性。

3.隔离性IsoIation（同一时间，只允许一个事务请求同一数据，不同的事务彼此之间互不干扰）-> mvcc(读+写)、锁(写+写)来实现。

4.持久性Durability（事务完成，保存到数据库，不能回滚）->redo log实现

快照读：读取的是MYSQL对应数据的历史版本。 -> select不加锁

当前读：读取的最新的数据结果。-> select lock in share mode(共享锁)、select for update(排他锁)、insert、update、delete

意向锁：在数据中加一个标记，表示上锁了。

innodb锁的分类:

行锁:共享锁(读锁)、排他锁(写锁)

表锁:意向共享锁、意向排他锁、自增锁(表锁)

innodb行锁主要算法：

RecordLock锁：单个行记录的锁(锁数据，不锁Gap);

间隙锁:当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时,Innodb会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但不存在的记录，叫做"间隙(GAP)",InnoDB也会对这个"间隙"加锁,这种锁机制就是所谓的间隙(Next-Key锁)。

数据库与缓存不一致，解决方案：缓存延迟双删(可以先清除缓存，在更新数据库，在延迟N(3~5)秒进行删除缓存)

mysql读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

mysql buffer pool原理:

1.free链表:记录空闲块地址

2.flush链表:记录buffer pool里面数据已经更新但磁盘数据没更新的数据块。会通过线程进行更新到磁盘之后进行释放。

3.lru链表:记录热点数据(占八分之五)；冷数据(占八分之三)；当冷数据两次访问的间隔时长大于1秒则转移到热数据中。

mysql innodb 出现表锁原因：使用了索引还使用全表扫描故而放弃索引（或没使用索引），因为表锁比行锁开销更小。使用表锁。

mysql innodb 出现表锁解决办法：重启数据库。

mysql innodb 查询语句后面加for update 的时候条件字段是主键出现行锁（没数据无锁），普通字段或主键不明（如:条件出现不等于、模糊搜索）会出现表锁。

mysql innodb 先会写入到undo log日志中，然后把数据写入到redo log buffer,同时根据一定规则刷到redo log磁盘中(保证我磁盘断电恢复未刷到磁盘中的数据)，最后写入buffer pool中在这之前是否需要写入变更缓存change buffer，最后再找时机把buffer pool数据写入到磁盘中。

char 和varchar的区别:

长度一致的时候使用char，长度不一致使用varchar

MyISAM和InnoDB区别:

1.MyISAM是非事务安全型的，表级锁，只缓存索引。

2.InnoDB是事务安全型的,行级锁,支持外键，缓存索引和真实数据，对内存要求高，支持mvcc。

Mysql开启主从同步流程(而主主同步则把从节点也开启binlog日志):

在主节点上设置:

1.开启binlog日志(在my.cnf里面加入log-bin=/var/lib/mysql/mysql-bin;log-bin-index=/var/lib/mysql/mysql-bin.index;server-id=1)；需要重启数据库(show master status)。

2.创建相应的用户与赋值相应的权限。

在从节点上设置：

3.在my.cnf配置server-id=2;relay-log=/var/lib/mysql/relay-bin;relay-log-index=/var/lib/mysql/relay-bin.index;需要重启数据库。

4.登录需要库,执行:change master to master_host='主节点ip',master_port='主节点port'，master_user='主节点账号'，master_password='主节点密码'，master_log_file='mysql-bin.000001'，master_log_pos=0;(master_log_file和master_log_pos可以通过show master status查询得到结果进行填写)。

5.在执行start slave;在执行show slave status查看运行状态;

mysql三大范式:

1.确保字段不可再分

2.确保每个字段跟主键有相关

3.确保字段跟主键直接相关，除主键外，其他字段不能有相互依赖

避免死锁：

1.加锁顺序一样。

2.基于主键或唯一索引来更新数据。

3.单次操作数据量不宜过多，表尽量少。

4.减少表上索引，减少锁定资源。

innodb死锁优化：

1.更新sql的where条件尽量用索引。