Mysql面试常见题

一、Sql优化、数据库锁、事务

1、锁

数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。

• 乐观锁，通常用于数据竞争不激烈的场景，多读少写，通过版本号和时间戳实现。
• 悲观锁，通常用于数据竞争激烈的场景，每次操作都会锁定数据。

要锁定数据需要一定的锁策略来配合。

• 表锁，锁定整张表，开销最小，但是会加剧锁竞争。
• 行锁，锁定行级别，开销最大，但是可以最大程度的支持并发。

但是MySql的存储引擎的真实实现不是简单的行级锁，一般都是实现了多版本并发控制（MVCC）。MVCC是行级锁的变种，多数情况下避免了加锁操作，开销更低。MVCC是通过保存数据的某个时间点快照实现的。

2、事务

事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。MySql采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。

隔离级别控制了一个事务中的修改，哪些在事务内和事务间是可见的。四种常见的隔离级别：

• 未提交读（Read UnCommitted），事务中的修改，即使没提交对其他事务也是可见的。事务可能读取未提交的数据，造成脏读。
• 提交读（Read Committed），一个事务开始时，只能看见已提交的事务所做的修改。事务未提交之前，所做的修改对其他事务是不可见的。也叫不可重复读，同一个事务多次读取同样记录可能不同。
• 可重复读（RepeatTable Read），同一个事务中多次读取同样的记录结果时结果相同。（MySQL默认）
• 可串行化（Serializable），最高隔离级别，强制事务串行执行。

3、存储引擎

InnoDB引擎，最重要，使用最广泛的存储引擎。被用来设计处理大量短期事务，具有高性能和自动崩溃恢复的特性。

4、创建时优化

（1）数据类型优化

• 尽量使用对应的数据类型。比如，不要用字符串类型保存时间，用整型保存IP。
• 选择更小的数据类型。能用TinyInt不用Int。
• 标识列（identifier column），建议使用整型，不推荐字符串类型，占用更多空间，而且计算速度比整型慢。
• 不推荐ORM系统自动生成的Schema，通常具有不注重数据类型，使用很大的VarChar类型，索引利用不合理等问题。
• 真实场景混用范式和反范式。冗余高查询效率高，插入更新效率低；冗余低插入更新效率高，查询效率低。
• 创建完全的独立的汇总表\缓存表，定时生成数据，用于用户耗时时间长的操作。对于精确度要求高的汇总操作，可以采用 历史结果+最新记录的结果 来达到快速查询的目的。
• 数据迁移，表升级的过程中可以使用影子表的方式，通过修改原表的表名，达到保存历史数据，同时不影响新表使用的目的。

（2）加索引

索引包含一个或多个列的值。MySql只能高效的利用索引的最左前缀列。索引的优势：

• 减少查询扫描的数据量
• 避免排序和零时表
• 将随机IO变为顺序IO （顺序IO的效率高于随机IO）

（3）查询时优化

• 避免查询无关的列，如使用Select * 返回所有的列。
• 避免查询无关的行
• 切分查询。将一个对服务器压力较大的任务，分解到一个较长的时间中，并分多次执行。如要删除一万条数据，可以分10次执行，每次执行完成后暂停一段时间，再继续执行。过程中可以释放服务器资源给其他任务。
• 分解关联查询。将多表关联查询的一次查询，分解成对单表的多次查询。可以减少锁竞争，查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作，不会由于查询拆分为多次，造成效率问题。
• 注意count的操作只能统计不为null的列，所以统计总的行数使用count（*）。
• group by 按照标识列分组效率高，分组结果不宜出行分组列之外的列。
• 关联查询延迟关联，可以根据查询条件先缩小各自要查询的范围，再关联。
• Limit分页优化。可以根据索引覆盖扫描，再根据索引列关联自身查询其他列
• Union查询默认去重，如果不是业务必须，建议使用效率更高的Union All

二、索引

1、索引的优缺点和使用场景

优点：

• 可以提高数据库的检索效率
• 创建唯一索引可以保证数据表中的每一行数据的唯一性
• 可以使用索引在查询中使用优化，用来提高系统的性能。

缺点：

• 创建索引和维护索引需要时间，而且这个时间是随着你的数据量增大增加的。
• 创建索引也是需要物理空间，除了数据表占用一定的空间之外，每一个索引还要占用一定的空间，如果建立聚簇索引，占用的物理空间还会更大。
• 当对表中的数据进行增加删除和修改时，索引也需要维护，降低了你维护数据的速度。

建立索引的原则：

• 对查询频率比较高的字段添加索引

2、索引的分类

数据结构角度：

• 哈希索引
• 树索引

物理角度：

• 聚簇索引
• 非聚簇索引

逻辑角度：

• 唯一索引
• 联合索引
• 主键索引
• 全文索引

3、索引的底层使用的是什么数据结构？

索引的数据结构和具体存储引擎的实现有关,，在MySQL中使用较多的索引有 Hash 索引、B+树索引等。而我们经常使用的 InnoDB 存储引擎的默认索引实现为 B+ 树索引。

4、为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？

用 B+ 树不用 B 树考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

5、谈谈你对 B+ 树的理解？(和B树的区别)

1. B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

2. 在 B+ 树中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 key i 和 key i+1，且不为 null，则该指针指向节点的所有 key 大于等于 key i 且小于等于 key i+1。

3. 进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

4. 插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

6、同等情况下为什么B树比B+树高度高？

因为在B树中每个节点都是存储的数据，B+ 树中只有叶子节点存储数据（B+树中有两个指针，一个指向根节点，另一个指向关键字的最小叶子节点），所以在B+树中可以在非叶子节点中存储更多的子节点的引用。

7、常见的树型数据结构

（1）二叉搜索树

二叉搜索树，以一个节点为根节点，比该节点小的值走左边，大的值走右边。搜索数据时，只要通过它的数据链接就可找到相应的数据。右边为一个节点里的具体组成部分。

二叉搜索树的致命缺点就是当插入的数据顺序为递增或者是递减时，形成的二叉树就变成了单边链条。

（2）平衡二叉查找树

平衡二叉查找树作为二叉查找树的进阶版，改进了二叉查找树的缺点。

定义：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。即，当平衡二叉查找树数据插入时，如果发现形成的树结构违反了它的定义，那么此时树会发生形变，组成一颗相对平衡的查找树。

红黑树也是平衡二叉树的一种
特性：

​ 1.每个节点只能是红色或者黑色。

​ 2.根节点必须是黑色。

​ 3.红色的节点，它的叶节点只能是黑色。

​ 4.从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

所以不建议对数据频繁更改的列建立索引，因为在这个过程中，索引结构的变更必然会带来IO和CPU的消耗。

缺点：

• 搜索效率不高：因为说到底还是树结构，所以在数据量大的时候，树的高度很高，带来的结果就是可能一次的查找要进行多次的IO。
• 节点的数据内容太少： 每一个节点中保存的数据远远不够填满一次内存和磁盘交互的值。如果一次内存和磁盘的交互为4KB，那么一个节点中保存的数据其实就大大浪费了这个空间。

（3）B树（多路平衡查找树，绝对平衡）

B树如何解决平衡二叉树的缺点呢？

上图是一个三路平衡查找树，因为Btree是多路平衡查找树的原因，它可以是三路，四路，五路六路，路越多，就意味着树的高度越低，那么一次搜索的最大IO次数也就越少。
B树的优点： B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

（4）B+树（加强版多路绝对平衡查找树）

特性：

1、非叶节点不保存数据相关信息，只保存关键字和子节点的引用。

2、所有的数据都保存的叶子节点中。

3、采用左闭合区间。

4、叶子节点中的数据顺序排列，并且相邻节点具有顺序引用的关系。

三、为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？

用 B+ 树不用 B 树考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

四、InnoDB和MYISAM的区别

1、 事务：MyISAM不支持事务，InnoDB支持事务；

2、全文索引：MyISAM 支持全文索引，InnoDB 5.6 之前不支持全文索引；

3、关于 count()：MyISAM会直接存储总行数，InnoDB 则不会，需要按行扫描。意思就是对于 select count() from table; 如果数据量大，MyISAM 会瞬间返回，而 InnoDB 则会一行行扫描；

4、外键：MyISAM 不支持外键，InnoDB 支持外键；

5、 锁：MyISAM 只支持表锁，InnoDB 可以支持行锁。

五、谈谈你对数据库读写分离的理解

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

1. 主从服务器负责各自的读和写，极大程度缓解了锁的争用；

2. 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；

3. 增加冗余，提高可用性。

六、请你描述下事务的特性？

1、原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

2、一致性：执行事务前后，数据库从一个一致性状态转换到另一个一致性状态。

3、隔离性：并发访问数据库时，一个用户的事物不被其他事务所干扰，各并发事务之间数据库是独立的；

4、持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库 发生故障也不应该对其有任何影响。

七、事务的隔离级别

1. READ_UNCOMMITTED（未提交读）: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读；

2. READ_COMMITTED（提交读）: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生；

3. REPEATABLE_READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生；

4. SERIALIZABLE（串行化）: 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

八、解释下什么叫脏读、不可重复读和幻读？

• 脏读： 表示一个事务能够读取另一个事务中还未提交的数据。比如：某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。
• 不可重复读 ： 是指在一个事务内，多次读同一数据。
• 幻读： 指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

九、索引的最左匹配原则

MySQL 使用联合索引时，需要满足最左前缀原则。下面举例对其进行说明：

 一个 2 列的索引 (name, age)，对 (name)、(name, age) 上建立了索引；
 一个 3 列的索引 (name, age, sex)，对 (name)、(name, age)、(name, age, sex) 上建立了索引。

1、 B+ 树的数据项是复合的数据结构，比如：(name, age, sex) 的时候，B+ 树是按照从左到右的顺序来建立搜索树的，比如：当(小明, 22, 男)这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据。但当 (22, 男) 这样没有 name 的数据来的时候，B+ 树就不知道第一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。

2、 当 (小明, 男) 这样的数据来检索时，B+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于小明的数据都找到，然后再匹配性别是男的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

十、请你讲讲 Statement 和 PreparedStatement 的区别？哪个性能更好？

与Statement相比，①PreparedStatement接口代表预编译的语句，它主要的优势在于可以减少SQL的编译错误并增加SQL的安全性（减少SQL注射攻击的可能性）；②PreparedStatement中的SQL语句是可以带参数的，避免了用字符串连接拼接SQL语句的麻烦和不安全；③当批量处理SQL或频繁执行相同的查询时，PreparedStatement有明显的性能上的优势，由于数据库可以将编译优化后的SQL语句缓存起来，下次执行相同结构的语句时就会很快（不用再次编译和生成执行计划）。

为了提供对存储过程的调用，JDBC API中还提供了CallableStatement接口。存储过程（Stored Procedure）是数据库中一组为了完成特定功能的SQL语句的集合，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。虽然调用存储过程会在网络开销、安全性、性能上获得很多好处，但是存在如果底层数据库发生迁移时就会有很多麻烦，因为每种数据库的存储过程在书写上存在不少的差别。

十一、如何防止Sql注入攻击？

用于攻击数据驱动的应用，恶意的SQL语句被插入到执行的实体字段中（例如，为了转储数据库内容给攻击者），大家都不陌生，是一种常见的攻击方式。攻击者在界面的表单信息或URL上输入一些奇怪的SQL片段（例如or ‘1’=’1’这样的语句），有可能入侵参数检验不足的应用程序。所以，在我们的应用中需要做一些工作，来防备这样的攻击方式。在一些安全性要求很高的应用中（比如银行软件），经常使用将SQL语句全部替换为存储过程这样的方式，来防止SQL注入。这当然是一种很安全的方式，但我们平时开发中，可能不需要这种死板的方式。

在MyBatis中所有的sql语句都需要自己手动编写，MyBatis的SQL是一个具有“输入+输出”的功能，类似于函数的结构。其中，parameterType表示了输入的参数类型，resultType表示了输出的参数类型。如果我们想防止SQL注入，理所当然地要在输入参数上下功夫。上面代码中使用#的即输入参数在SQL中拼接的部分，传入参数后，打印出执行的SQL语句，会看到SQL是这样的：

sqlselect id, username, password, role from user where username=? and password=?

不管输入什么参数，打印出的SQL都是这样的。这是因为MyBatis启用了预编译功能，在SQL执行前，会先将上面的SQL发送给数据库进行编译；执行时，直接使用编译好的SQL，替换占位符“?”就可以了。因为SQL注入只能对编译过程起作用，所以这样的方式就很好地避免了SQL注入的问题。
底层实现原理： MyBatis是如何做到SQL预编译的呢？其实在框架底层，是JDBC中的PreparedStatement类在起作用，PreparedStatement是我们很熟悉的Statement的子类，它的对象包含了编译好的SQL语句。这种“准备好”的方式不仅能提高安全性，而且在多次执行同一个SQL时，能够提高效率。原因是SQL已编译好，再次执行时无需再编译。

十二、谈谈你对MVCC的了解？

• 数据库并发场景：

1. 读-读：不存在任何问题，也不需要并发控制；

2. 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读；

3. 写-写：有线程安全问题，可能会存在更新丢失问题。

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
在 MySQL中，多版本并发控制是 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC；可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

• MVCC 可以为数据库解决以下问题：

1. 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能；

2. 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。

十三、MySQL 问题排查都有哪些手段？

• 使用 show processlist 命令查看当前所有连接信息；

• 使用 Explain 命令查询 SQL 语句执行计划；

• 开启慢查询日志，查看慢查询的 SQL。

十四、 说一下 MySQL 的行锁和表锁？

• MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

• 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。

• 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。