大厂面试之MySQL终极八股

1.数据库和数据库管理系统区别

• 数据库是指数据集合&＃xff0c;数据库管理系统是指管理数据的系统&＃xff0c;例如MySQL、Oracle等。

2.ER图的作用

• ER图用于描述数据库的需求分析进行概念化&＃xff0c;概念设计的产物。

3.存储过程是啥&＃xff1f;

• 存储过程是指将常用的sql语句进行预编译&＃xff0c;下次进行执行的时候速率快。

4.delete、drop、truncate之间的区别

• 1.delete是DML&＃xff0c;删除表的表项&＃xff0c;支持回滚。
• 2.drop是删除表和表结构、
• 3.truncate是清空表的数据&＃xff0c;不删除表的结构。

5. 介绍一下MySQL中的字符集和编码

• 1.原本计算机中只要ASCII字符集&＃xff0c;只存储着英文字母和一标点符号。
• 2.GBK字符集&＃xff0c;存储着全国的大部分文字&＃xff0c;包括中文&＃xff0c;但是不全。
• 3.Unicode字符集&＃xff1a;存储所有的文字的集合&＃xff0c;是按照二进制的方式进行存储的。
• 4.编码&＃xff1a;因为Unicode是按照二进制的方式进行存储的&＃xff0c;所以如何进行编码也是很关键的。1.UTF-8编码&＃xff0c;英文和符号按照1字节、中文按照3个字节进行编码。2.UTF-8mb4编码&＃xff0c;在UTF-8的基础上面增加了四字节编码中文的繁体字和emoji表情。

6.MySQL中的存储引擎

6.1 MyISAM

• MySQL5.5之前的存储引擎&＃xff0c;只有表锁&＃xff0c;不支持外键&＃xff0c;不支持行锁&＃xff0c;不支持系统宕机恢复&＃xff0c;不支持事务&＃xff0c;是非聚集索引。

6.2 InnoDB

• MySQL5.5及其以后的默认存储引擎&＃xff0c;支持行锁&＃xff0c;支持事务&＃xff0c;支持宕机恢复&＃xff08;引入了redo log&＃xff09;&＃xff0c;支持外键。

7.MySQL中锁

7.1 悲观锁和乐观锁

• 悲观锁&＃xff1a;以预防的思想对数据进行操作之前会加锁&＃xff0c;其他用户无法进行操作&＃xff0c;操作完之后进行解锁。
  手动加悲观锁&＃xff1a;
  读锁LOCK tables 数据库表read释放锁UNLOCK TABLES; 写锁&＃xff1a;LOCK tables test_db WRITE释放锁UNLOCK TABLES;
  分类&＃xff1a;全局锁、表锁、行锁、间隙锁、临键锁
  
• 乐观锁&＃xff1a;是对数据冲突保持一种热观的态度&＃xff0c;操作数据的时候不会进行加锁&＃xff0c;可以并行的处理数据&＃xff0c;只要数据的提交的时候通过版本的比较验证数据时候冲突。
  

7.2 读锁和写锁

• 读锁&＃xff1a;共享锁称之为读锁&＃xff0c;事务A对数据加读锁了&＃xff0c;其他事务只能对该数据加读锁&＃xff0c;都只能进行读操作&＃xff0c;一个数据上可以由多个事务加读锁&＃xff0c;等到该数据上所有读锁全部释放之后才可以加写锁。语法&＃xff1a;SELECT … LOCK IN SHARE MODE;
• 写锁&＃xff1a;排他锁也称之为写锁&＃xff0c;事务A对数据加写锁&＃xff0c;既可以读取也可以进行修改数据&＃xff0c;其他事务不可以加任何锁&＃xff0c;直到A事务释放。SELECT … FOR UPDATE。

7.3全局锁

• 作用&＃xff1a;按照只读的方式锁住数据库。在数据库备份的时候或者数据库同步的时候可以使用。

7.4表锁

• 加锁&＃xff1a;lock table tableName read&＃xff0f;write; 解锁&＃xff1a;unlock table&＃xff1a;

7.5意向锁

• 意向锁&＃xff0c;innodb特有&＃xff0c;加在表级别上的锁&＃xff0c;意向锁是InnoDB自动加的&＃xff0c;不需要用户干预。
• 意向共享锁&＃xff08;IS&＃xff09;&＃xff1a;对表进行增删改查的时候&＃xff0c;需要加一个读共享锁&＃xff0c;表示事务准备给数据行加入共享锁&＃xff0c;也就是说一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁&＃xff08;IX&＃xff09;&＃xff1a;对表结构进行修改的时候&＃xff0c;需要加一个写意向锁&＃xff0c;表示事务准备给数据行加入排他锁&＃xff0c;说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
• mysql InnoDB锁兼容阵列
  
• 作用&＃xff1a;
  1.防止DDL和DML之间产生冲突&＃xff08;MDL的&＃xff08;增查改删&＃xff09;读锁和 DDL之间不互斥&＃xff1b;MDL的写锁和DDL的写锁修改表结构之间互斥&＃xff1b;在对表结构进行修改时&＃xff0c;加MDL锁&＃xff0c;另外线程只能等待。&＃xff09;
  2.提高锁的效率。&＃xff08;加了意向锁之后可以避免加行锁的时候进行全表扫描&＃xff09;

7.6行锁

• 行锁是基于索引实现的&＃xff0c;所以没有建立索引的更新将会扫描全表&＃xff0c;使用的是表锁&＃xff1b;通过锁住索引实现行及锁。如果select …for update没走索引&＃xff0c;就会锁表&＃xff0c;innodb内部是全表根据主键索引逐行扫描&＃xff0c;逐行加锁&＃xff0c;最后统一释放。
• 行锁是需要的时候加上的&＃xff0c;不是不需要了就立即释放&＃xff0c;在commit后才释放&＃xff0c;释放时机如下:1. 执行commit语句或者rollback&＃xff1b;2. 退出数据库&＃xff1b;3. 客户端断开连接&＃xff1b;

7.7 三种细粒度锁的实现

• Record lock&＃xff1a;记录锁&＃xff0c;单个行记录上的锁,实现&＃xff1a;通过索引实现&＃xff0c;锁住一行记录&＃xff0c;锁粒度最小&＃xff0c;并发度高。使用条件&＃xff1a;等值查询&＃xff1b;
• Gap lock&＃xff1a;间隙锁&＃xff0c;锁定一个范围&＃xff0c;不包括记录本身,使用条件&＃xff1a;查询一个不存在的值&＃xff0c;锁定就是该值左右的区间&＃xff0c;就是间隙锁。间隙锁与间隙锁之间是不冲突的&＃xff0c;跟间隙锁冲突的是&＃xff1a; 往间隙之间插入一条数据的这个操作 。
• Next-key lock&＃xff1a;record&＃43;gap 临键锁&＃xff0c;锁定一个范围&＃xff0c;包含记录本身.使用条件&＃xff1a;范围查询&＃xff0c;包含等值和区间锁住的是下一个记录的左开右闭的区间&＃xff1b;解决了RR级别下的数据幻读&＃xff1b;
  

7.8MySQL如何解决幻读

• 幻读产生&＃xff1a;一个事务在读取的时候&＃xff0c;读取到数据使得用户觉得产生了幻觉一样&＃xff0c;根据原因是锁住了行&＃xff0c;但是新增数据被事务读到依然会产生幻觉。
• 解决办法&＃xff1a;在RR的基础上&＃43;Next Key Lock。

8.MySQL中的缓存

• MySQL在8.0版本之前存在缓存的功能&＃xff0c;缓存的是SQl语句&＃xff0c;当查询过来的时候&＃xff0c;会直接查缓存&＃xff0c;命中的话直接返回。

9.一席话讲给面试官什么叫事务

• 1.事务是指一个SQL操作集合&＃xff0c;这个集合要么全部执行&＃xff0c;要不全部都不执行&＃xff0c;满足用户逻辑和物理的一致性。
• 2.数据库的特性&＃xff1a;原子性&＃xff1a;原子性是指事务包含的sql语句集要么全部执行&＃xff0c;要么全部都不执行&＃xff0c;原子性是由undo log来保证的&＃xff0c;undo log记录数据修改之前的旧值&＃xff0c;当事务进行回滚的时候&＃xff0c;数据可以恢复到原来的旧值。隔离性·&＃xff1a;事务与事务之间如果对同一数据进行操作的话&＃xff0c;会造成一系列的问题&＃xff1a;比如脏读&＃xff0c;不可重复读&＃xff0c;幻读等问题。所以事务与事务之间应该存在隔离&＃xff0c;隔离级别有&＃xff1a;读未提交&＃xff0c;读已提交&＃xff0c;可重复读&＃xff0c;可串行化读&＃xff0c;隔离级别是基于MVVC来实现的。事务隔离界别等下在谈。持久性&＃xff1a;是指数据库可以及时更新到磁盘中&＃xff0c;数据库宕机恢复&＃xff0c;持久性是基于redo log来实现的&＃xff0c;是InnoDB特有的&＃xff0c;会记录数据改变后的新值&＃xff0c;当重启宕机的数据库会将redo log修改值进行持久化到磁盘中。一致性&＃xff1a;一致性是MySQL事务的目的&＃xff0c;其他三个条件是基础&＃xff0c;满足用户的逻辑和物理要达到一致性。
• 3.事务的隔离级别&＃xff1a;1.读未提交&＃xff0c;一个事务可以读到另一个事务未提交的内容&＃xff0c;会造成脏读、不可重复度、幻读等问题。2.读已提交&＃xff0c;一个事务可以读取另一个事务提交的内容&＃xff0c;会造成一个事务中多次读取同一个数据不同结果&＃xff0c;采用MVVC多版本并发控制实现的&＃xff0c;每次读都会读取最新的版本。3.可重复读&＃xff0c;事务在读取的过程中始终读取最开始的版本数据&＃xff0c;可以解决可重复读的问题&＃xff0c;但是会存在幻读的问题&＃xff0c;可重复读只能锁住单个记录&＃xff0c;若要解决幻读&＃xff0c;需要加上Next Key Lock就能保证解决幻读。

10.时间选择timestamp、datetime和时间戳

• DATETIME&＃xff1a;范围大&＃xff0c;但是时区需要指定。
• TIMESTAMP&＃xff1a;范围小&＃xff0c;自动时区。
• 时间戳&＃xff1a;存储long类型&＃xff0c;没有时区的文体&＃xff0c;但是需要转换。

11.主键索引和非主键索引

• 索引存在的意义&＃xff1a;索引的存在是为了加快检索的速度&＃xff0c;索引的结构采用B&＃43;树实现的。
• 为什么要用B&＃43;树当做存储结构&＃xff1a;B&＃43;树高度比较低&＃xff0c;宽度比较宽&＃xff0c;从而IO的次数就会少&＃xff0c;加快检索效率。
• 主键索引&＃xff1a;主键索引的存储结构是用聚集所用&＃xff0c;叶子结点包含一行的所有数据。
• 非主键索引&＃xff1a;采用的非聚集索引&＃xff0c;叶子结点包含索引值&＃43;主键&＃xff0c;可能会要回表查询&＃xff0c;尽量采用覆盖查询避免进行回表。

12.MySQL中的三大日志

12.1 undo log

• undo log是MySQL事务原子性的基础&＃xff0c;undo log保存了数据修改前的旧值&＃xff0c;当事务进行回滚的时候则回到事务执行之前的状态。

12.2 binlog

• binlog是基于快照的&＃xff0c;记录的是service层的修改记录&＃xff0c;用于数据库的主主、主从一致性。

12.3 redo log

• redo log是事务持久化的保证&＃xff0c;redo log中保存着数据的修改&＃xff0c;保存着修改记录&＃xff0c;和binlog不同。