MySQL数据库面试题

写在前面

最近快面试了，收集相关的面试题，也是对自己知识的总结

---

1. MySQL中的事务

事务具有四大特性（ACID）：
**原子性(Atomic)：**一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样
一致性(Consistency)：在事务开始之前和事务结束以后， 数据库的完整性没有被破坏
**隔离性(Isolation)：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
**持久性(Durability)：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

MYSQL 事务处理的方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始事务
ROLLBACK 事务回滚
COMMIT 事务确认
SET TRANSACTION 用来设置事务的隔离级别

2. 事务有哪些隔离级别

读未提交（Read Uncommitted）：是最低的事务隔离级别，它允许另外一个事务可以看到这个事务未提交的数据。会出现脏读，幻读，不可重复读，所有并发问题都可能遇到。
读已提交（Read Committed）：保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。不会出现脏读现象，但是会出现幻读，不可重复读。
可重复读（Repeatable Read）：这种事务隔离级别可以防止脏读，不可重复读，但是可能会出现幻象读。它除了保证一个事务不能被另外一个事务读取未提交的数据之外还避免了不可重复读。
串行化（Serializable）：这是花费最高代价但最可靠的事务隔离级别。事务被处理为顺序执行。防止脏读、不可重复读、幻象读。

3. MySQL优化

使用 explain 获取低效率 SQL 的执行计划

SQL语句的优化：

1、尽量避免使用子查询

2、用IN来替换OR

3、读取适当的记录LIMIT M,N，而不要读多余的记录

4、分组统计可以禁止排序sort，总和查询可以禁止排重用union all

5、批量INSERT插入

6、尽量不用select *

7、区分in和exists

8、优化Group By语句

索引优化：

避免索引失效
1.最佳左前缀法则

   如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。Mysql查询优化器会对查询的字段进行改进，判断查询的字段以哪种形式组合能使得查询更快，所有比如创建的是(a,b)索引，查询的是(b,a)，查询优化器会修改成(a,b)后使用索引查询。

2.不在索引列上做任何操作

   (计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。

3.存储引擎不能使用索引中范围条件右边的列。

    如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))

   如select age from user减少select *

5.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。

6.is null, is not null 也无法使用索引，在实际中尽量不要使用null。

7.like 以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。

   所以最好用右边like 'abc%'。如果两边都要用，可以用select age from user where username like '%abc%',其中age是必须是索引列，才可让索引生效

    假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用，类似于不能使用范围条件右边的列的索引

   对于一棵B+树来讲，如果根是字符def，如果通配符在后面，例如abc%，则应该搜索左面，例如efg%，则应该搜索右面，如果通配符在前面%abc，则不知道应该走哪一面，还是都扫描一遍吧。

8.字符串不加单引号索引失效

9.少用or，用它来连接时会索引失效

10.尽量避免子查询，而用join

11、在组合索引中，将有区分度的索引放在前面

   如果没有区分度，例如用性别，相当于把整个大表分成两部分，查找数据还是需要遍历半个表才能找到，使得索引失去了意义。

12、避免在 where 子句中对字段进行 null 值判断

   对于null的判断会导致引擎放弃使用索引而进行全表扫描。

数据库优化

读/写分离
经典的数据库拆分⽅案，主库负责写，从库负责读；
垂直分区
例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基 本信息，可以将⽤户表拆分成两个单独的表，甚⾄放到单独的库做分库。 简单来说垂直拆分是指数据表列的拆分，把⼀张列⽐较多的表拆分为多张表。
⽔平分区
保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同 的表或者库中， 达到了分布式的⽬的。 ⽔平拆分可以⽀撑⾮常⼤的数据量。 ⽔平拆分是指数据表⾏的拆分，表的⾏数超过200万⾏时，就会变慢，这时可以 把⼀张的表的数据拆成多张表来存放。

3. 什么是索引

索引好比一本书的目录，能加快数据库查询的速度
一般来说索引体积较大，不可能全部存放在内存中，往往是存放在磁盘的文件中
索引的种类有普通索引，唯一索引，主键索引，组合索引，覆盖索引，全文索引。默认使用B+树结构组织的索引

4. 索引对性能有哪些影响？(索引的优缺点)

优点
提高数据检索的效率，降低数据库的IO成本
通过索引对数据的排序，降低了数据排序的成本，降低CPU的消耗

缺点
索引会占据磁盘空间
索引虽然提高了查询效率，但是降低了表更新的效率。每次对表进行增删改的操作，mysql不仅要保存表的数据，也要更新索引的数据

5. 索引的数据结构

B树：
B树的背景：
mysql的数据是存储到磁盘文件中的，查询或者处理数据的时候，需要将磁盘的数据加载到内存当中，磁盘IO的操作是非常耗时的，所以优化的重点就是减少IO操作。在二叉树中，访问每个节点就会发生一次IO操作，要减少IO的操作，就需要尽量降低树的高度。
mysql用innoDB存储引擎一次IO会读取一页的数据量（16kz字节)，而二叉树一次IO的数据量只有16个字节，空间利用率极低。为了最大化的利用一次IO空间，可以再每个节点上存储尽可能多的数据。将二叉树改造成多叉树。构建100万条数据理想状态树的高度只需要两层（1000*1000），只需要2次磁盘IO就可以查询到数据，查询的效率也就提高了。

B树（多叉平衡查找树）的特点
B树的节点存储多个元素，每个元素由（键值（字段名），指针（下一个节点的地址），数据（数据库对应行的地址或者行数据））组成。（可以存储1000个元素）
节点的元素包含键值和数据，节点中的键值从小到大排序。所有节点都会存储数据
父节点中的元素不会出现在子节点中
所有的叶子节点都位于同一层。（由下而上，与平衡二叉树不同）。叶节点之间没有指针连接（B+树会有）

B树的优点
相比二叉平衡查找树，B树在查询的过程中比较次数并没有减少，但是磁盘IO的次数大大减少。因为比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度在两至三层就能够满足大部分的应用场景了，用B树构建索引可以很好的提高查询的效率

B树的局限性
B树仍然不支持范围的快速查找，范围查找仍然需要从根节点多次遍历。

B+树
B树和B+树的区别
B树的非叶子节点和叶子节点上都会存储数据
B+树的非叶子节点只会存储键值，叶子节点之间用双向指针连接，最底层的叶子节点形成了一个双向有序链表
B+树的特性
B+树最底层的叶子节点包含了所有索引。由于所有的数据都存储在最底层叶子节点上，所以查找数据都需要检索到叶子节点上，所以磁盘IO的次数和树高就有了直接联系。但因为非叶子节点上不存储数据，所以B+树在理论上是要比B树矮的
B+树的优点
B+树可以保证等值和范围的快速查找，Mysql的索引就采用了B+树的数据结构

6. MyIsam以及innoDB

InnoDB
默认事务型引擎
数据存储在共享表空间，即多个表和索引都存储在一个表空间中，可通过配置文件修改
主键查询的性能高于其他类型的存储引擎
内部做了很多优化，如：从磁盘读取数据时会自动构建hash索引，插入数据时自动构建插入缓冲区
支持崩溃后的安全恢复
支持行级锁
支持外键

MyISAM
拥有全文索引、压缩、空间函数
不支持事务和行级锁、不支持崩溃后的安全恢复
表存储在两个文件：MYD 和 MYI
设计简单，某些场景下性能很好，例如获取整个表有多少条数据，性能很高

7. MyISAM索引与InnoDB索引的区别

InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引
InnoDB 的主键索引的叶子节点存储着行数据，主键索引非常高效
MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

8. InnoDB索引如何避免回表

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？

使用联合索引这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。（覆盖索引）

9. 数据库三大范式是什么

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。