MySQL数据库八股文

MySQL数据库八股文

第一章 数据库基础

1. 数据库概念

数据库是存储数据的仓库，数据库管理系统是操纵和管理数据库的大型软件（如MySQL，InnoDB是其默认的存储引擎），SQL是操作关系型数据库的编程语言。

2. SQL语法与分类

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性，空格和缩进的数量不限制。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释：

• 单行注释：-- 注释内容 或 # 注释内容
• 多行注释：/* 注释内容 */

数据定义语言（DDL，定义数据库、表、字段）、数据操作语言（DML，对数据库中表的数据记录进行增、删、改操作）、数据查询语言（DQL，用来查询数据库中表记录，关键字是select）、数据控制语言（DCL，管理数据库用户、控制数据库的访问权限）。

第二章 事务

1. 事务介绍

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，保证了数据的完整性和一致性。MySQL的服务器层不管理事务，事务是由存储引擎实现的。InnoDB是MySQL最为广泛的存储引擎。

2. 事务四大特性（ACID）

原子性：事务是不可分割的最小操作单位

一致性：事务完成时，必须使所有的数据都保持一致状态

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

持久性：事务一旦提交或回滚对数据库中的数据的改变就是永久的

3. 并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据

不可重复读：一个事务先后读取同一条数据，但两次读取的数据不同

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了，好像出现了幻影

4. 事务隔离级别

Read uncommitted存在脏读、不可重复读、幻读

Read commit不存在脏读，存在不可重复读、幻读

Repeatable Read（默认）不存在脏读、不可重复读，存在幻读

Serializable不存在脏读、不可重复读、幻读

从上到下性能越来越低，安全性越来越高

第三章 索引

1. 索引介绍

索引是帮助MySQL高效获取数据的数据结构，是在存储引擎层实现的。能够提高数据检索效率，降低数据库的IO成本。但索引降低了更新表的速度，且索引列占用空间。

2. 索引结构

B树是一棵多路平衡查找树，B+树与B树相比有以下特点：所有的数据都出现在叶子结点，叶子结点形成一个单向链表，非叶子结点仅起到索引数据作用，具体的数据都是在叶子结点。MySQL的B+树是在原有B+树的基础上增加了一个指向相邻链表指针，形成了带有顺序指针的B+树，利于排序。

3. 索引分类

主键索引：对于表中主键创建的索引（默认自动创建且唯一）

唯一索引：避免同一个表中某列数据中的值重复

常规索引：快速定位特定数据

全文索引：查找的是文本中的关键词，而不是比较索引值

InnoDB存储引擎又可以分为聚集索引和二级索引：

聚集索引：将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据，有且仅有一个

二级索引：将索引与数据分开，索引结构的叶子结点关联的是对应主键

4. 索引使用

最左前缀法则：如果索引了多列即联合索引，要遵循最左前缀法，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列则索引将会部分失效（后面的字段索引失效）。

5. 索引失效情况

索引列运算、字符串不加引号、模糊查询、or连接条件、数据分布影响（使用索引比全表更慢则不使用索引）

第四章 锁

1. 锁简介

锁是计算机协调多个进程或线程并发访问某一资源的机制，如何保证数据并发访问的一致性和有效性是所有数据库都必须要解决的一个问题。锁分为全局锁、表级锁、行级锁

2. 全局锁

对整个数据库加锁，加锁后整个实例处于只读状态，后续的DDL、DML、DQL都将被阻塞。典型的场景是全库的逻辑备份，但一般不在主库上备份，否则业务基本停摆。

3. 表级锁

表级锁锁住整张表。锁定粒度大，发生锁冲突概率最高，并发度最低。分为表锁、元数据锁和意向锁

表锁分为表共享读锁（不会阻塞所有客户端的读，但会阻塞所有客户端的写）和表独占锁（不会阻塞本客户端的读写，但会阻塞其他客户端的读写）。

元数据锁（MDL）：加锁是系统自动控制（一些SQL语句自动添加的），无需显示使用，当表上有活动事务时，不可以对元数据进行写入操作，保证读写的正确性。

意向锁使得表锁不用检查每行数据是否加锁，减少了表锁的检查。分为意向共享锁（与共享锁（read）兼容，与排他锁（write）互斥）和意向排他锁（与共享锁和排他锁都互斥）

4. 行级锁

行级锁每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高，通常用在InnoDB引擎。分为行锁、间隙锁和临键锁。

行锁：锁定单个行记录的锁，防止其他事务对此进行update和delete。

间隙锁：锁定记录间隙（不包含该记录），确保索引间隙不变，防止其他事务在这个间隙进行insert进而产生幻读。

临键锁：行锁和间隙锁的组合，同时锁住数据并锁住数据前的间隙

第五章 日志

更新语句涉及到undo log（回滚日志）、redo log（重做日志）和binlog（归档日志）

Undo log：是innoDB存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC（多版本并发控制）。

Redo log：是innoDB存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电故障的恢复。

Binlog：是Server层生成的日志，主要用于数据备份和主从复制。

第六章 其他

1. 范式的目的在于降低数据的冗余性和数据的一致性，而不是为了提高查询效率。
2. 在select语句中，使用关键字distinct可以把重复行屏蔽掉。
3. 在MySQL中，返回字符串长度的函数是length()
4. sql中查询关键字的书写顺序为：select、distinct、from、join、on、where、group、by、having、order by、limit。执行顺序from、on、join、where、group by、having、select、distinct、order by、limit。