MySQL面试题总结

一、MySQL简介

MySql是关系型数据管理系统

关系型数据库
关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织
优缺点:
- 优点:
  1.易于维护，使用统一的表结构，格式一致
  2.使用方便，SQL语言通用，可用于复杂操作
  3.复杂操作，支持sql，可用于一个或多个表之间非常复杂的操作
- 缺点:
  1.读写性能差，尤其是海量数据的读写
  2.固定的表结构，灵活性低
  3.高并发的读写，对于传统的数据库来说，硬盘I/O是很大瓶颈
非关系型数据库
非关系型数据库严格意义上不是一个数据库，应该是一种数据结构化存储方法的集合，可以是文档或键值对等
优缺点:
- 优点:
  1.格式灵活，存储数据的格式可以是键值对形式，文档格式，图片格式等，使用灵活，应用场景广泛
  2.速度快，nosql可以使用磁盘或者随机存储器为载体，而关系型数据库只能使用磁盘
  3.高扩展性
  4.成本低，nosql部署简单，基本都是开源
- 缺点:
  1.不支持SQL，使用学习成本高
  2.无事务处理
  3.数据结构相对复杂，查询方面稍欠佳

二、B树和B+树

二叉树、AVL、红黑树、B树和B+树

二叉树
二叉搜索树(BST)又称二叉查找树和二叉排序树，可以使用一个链表数据结构来表示，每个节点就是一个对象。每个节点属性还包含lchild、rchild和parent，分别指向节点的左孩子，右孩子和双亲节点。
特性:
- 若任意节点的左子树不为空，则左子树上的所有节点的值均不大于根节点
- 若任意节点的右子树不为空，则右子树上的所有节点的值均大于根节点
- 任意节点的左右子树也分别为二叉搜索树
AVL
AVL树是一颗特殊的搜索二叉树，具有要保证平衡的特性，AVL树的每个节点的左子树和右子树的高度差要求最大为1。
红黑树
红黑树是一棵自平衡的二叉树
特性:
- 1.每个节点都是黑色或者红色
- 2.根节点是黑色
- 3.每个叶子节点是黑色[注意：这里的叶子节点是指为空的叶子节点]
- 4.如果一个节点是红色的，那么它的叶子节点必是黑色的
- 5.从一个节点到该节点的子孙节点的所有路径包含相同的黑节点
B树
B树是为实现高效的磁盘存取而设计的多叉平衡搜索树，这里的B树，也就是英文中的Balanced Tree，一棵m阶的B树满足的条件
- 1.根节点至少有两个子节点
- 2.除了根节点外，每个非叶几点至少有m/2(向下取整)个子节点
- 3.非叶子节点的根节点至少有两个子节点
- 4.B树中，所有子节点都在同一层平衡

B+树
B+树是B树的变形体，性质和B树是一致，B+树具有不通的存储结构

B树	B+树
有m棵子树的节点含有m-1个关键码	有m棵子树的节点含有m-1个关键码
B树中非叶子节点关键码和叶子节点关键码不重复，它们共同构成索引信息	所有叶子节点包含了完整的索引信息，中间的节点每个元素不存数据，只用来索引
非叶子节点包含需要查找的有效信息	所有非叶子节点可以看成高层索引，节点含有对其子树根节点的最大(或最小)关键字

三、MySQL索引

引述MySQL官方定义：索引是帮助MySQL高效获取数据的结构。在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据以某种方式引用数据，这样就可以在这些数据结构上实现高级算法，这中数据结构就是索引。

3.1、索引数据结构

hash:
hash索引的的底层原理就是hash表，进行数据查询时，调用hash函数可以获取到对应的值，之后回表查询具体的数据。
B+树：
B+树底层是多路平衡二叉树。每次查询从根节点出发，查找到叶子节点可以获得键值，根据查询判断是否需要回表。
hash和B+树区别：
- 1.hash索引无法进行范围查找，因为直接用hash函数定位到id；而B+树维护了主键的顺序(树结构的天然优势+非叶子节点按页存储主键值+叶子节点链表相连)
- 2.hash索引不知道使用索引排序
- 3.hash索引不支持模糊查询及多列索引的最左匹配，原因是hash函数的不可预测性
- 4.hash索引任何时候避免不了回表查询，而B+树在某些条件（聚簇索引，覆盖索引）时，可以避免回表
- 5.hash索引虽然在等值查询快，但是不可预测，当某个键值存在大量重复时，发生hash碰撞会导致效率下降

3.2、索引类型

聚簇索引基于主键创建的索引就是聚簇索引（或非空唯一索引实现的），叶子节点存的就是整行数据
二级索引/非聚簇索引用非主键进行索引，叶子节点存储的非主键索引和主键，再根据主键从聚簇索引查询数据
联合索引两个或两个以上字段联合组成一个索引。使用时要注意最左匹配原则

-- 当没有未指定primary key时，mysql会默认创建主键索引，当没有主键时，会有个隐藏的rowid创建索引
create tale `user` {
    `id` int not null default 0 comment 'id',
    `name` varchar(20) not null default '' comment 'name'，
    `age` int not null default 0 comment 'age',
    `email` varchar(30) default '' comment 'email',
    primay key (`id`),
    key `idx_email`(`email`) using btree
    key `idx_name_age`(`name`, `age`) using btree
}engine = innodb comment 'user';
-- key `idx_email`为二级索引
-- key `idx_age_sex`为联合索引 
-- 覆盖索引: 索引包含了要查询的字段，如：执行select name from user where name = 'aaa'时会走索引`idx_name_age`，此时查询列存在联合索引中，此时不需要回表查询
-- 索引下推: MySQL在5.6版本之后引入索引下推优化，可以在遍历索引过程中，对索引中包含的字段优先判断，直接过滤掉不满足条件的数据，减少回表次数

3.3、索引失效及最佳索引

索引失效场景
- 对索引列进行左或者右模糊匹配，也就是like '%xxx'或like 'xxx%'都会导致索引失效，查询的结果是多个，不知道从哪个索引值比较，只能进行全表扫描
- 对索引列进行函数或表达式计算
- 对索引进行隐式转换
- is null或is not null
- 范围查找后的列索引失效，如select * from t where name = 'x' and age > 18 and address = ''，age条件后的address索引失效
- or分割的条件，如select * from t where name = 'x' or age = ''，name列有索引，age列无索引，则name索引失效
- in走索引，not in不走索引，如select * from t where name in ('x', 'y')走索引，select * from t where name in ('x', 'y')则不走索引
最佳索引
- 查询频次高且数据量大的表建立索引
- 一个表的索引要适量，最多不要超过5个(阿里开发规范)
- 使用聚簇索引和覆盖索引大大提升性能（聚簇和覆盖的索引树上包含了要查询的列，避免回表）
- 尽量避免select *
- 前缀索引，长字符串查询时，只需匹配一个前缀长度，节省大量索引空间（无法进行order by和group by）

四、MySQL引擎

MySQL常用的搜索引擎有MyISAM,InnoDB,Memory和CSV，这里主要考虑两种常用的引擎：MyISAM和InnoDB

MyISAM是MySQL5.6之前默认的引擎，5.6之后默认是InnoDB。MyISAM具有较高的插入和查询速度，但是不支持事务。
MyISAM特性
MyISAM不支持事务，不支持外键，默认的锁粒度是表级锁(并发度差)，支持全文索引(并不常用，一般使用ES、Solr等)，MyISAM使用的索引为B+树，树的叶子节点data域存储的数据的地址；MyISAM的表结构文件为：.frm(表结构),MYI(索引),MYD(数据)
InnoDB是事务型数据库的首选，支持事务安全(ACID)，5.6之后是默认引擎。
InnoDB
InnoDB在5.6版本后是数据库的默认引擎，支持事务，锁粒度有表锁，行锁(可支持高并发，会引起死锁)，支持外键，索引使用B+锁，聚簇索引，B+树的叶子节点data域存储行数据；表数据文件：.idb(数据),.frm(表结构)，5.6之后支持全文索引
MyISAM&InnoDB
其他引擎
1.Memory引擎：不支持事务，内存读写，临时存储，表级锁
2.Archive引擎：用于归档，支持支select和insert，支持行级锁，以zlib对表进行压缩，减少磁盘I/O

五、MySQL事务和事务隔离级别

5.1、MySQL事务特性

事务是一个最小的不可再分的工作单元，一个事务中所有操作，要么全部成功，要么全部失败。事务有四个特性：原子性(automic)、一致性(consistency)、隔离性(isolation)、持久性(duration)，简称ACID。

原子性(Automic):一个事务是不可分割的整体，要么成功，要么事变
一致性(Consistency):一个事务开始之前和结束之后，数据库的完整性不被破坏
隔离性(Isolation):多个事务之间相互隔离，互不干扰
持久性(Duration):一个事务一旦提交，数据的改变是永久的

5.2、事务并发问题

当存在多个事务，应当让多个事务同时执行，这就是事务的并发。存在事务并发，必然存在两个事务操作同一个数据引起的冲突。事务的并发会引起脏读、不可重复读和幻读。

脏读(读未提交)：在一个事务里，由于其他事务中修改了且没有提交事务，而导致的两次读取的数据不一致的问题，这种事务的并发问题称之为“脏读”
不可重复读(读已提交)：一个事务A多次读取同一个数据，该事务A未结束时，另一个事务B访问同一数据，并对数据进行修改，可能造成事务A前后读取的数据不一致，这种称为“不可重复读”
幻读(可重复读)：事务A读取了几行数据，事务还未结束，此时事务B插入了一些数据，事务A在随后的查询中获取到的数据会比之前的数据行数多，这种称之为“幻读”

5.3、事务的隔离级别

读未提交(read uncommitted):两个事务A和事务B，事务A未提交，事务B可以读取到事务A未提价的数据。这种事务隔离级别最低，事务的三种并发问题都没解决
读已提交(read committed):事务A能读取到事务B提交的数据，可以避免脏数据，这种级别会导致不可重复读，oracle默认隔离级别
可重复读(repeatable read):事务A和事务B，事务A提交之后的数据，在事务B中读取不到，事务B可以重复读取数据。这种事务隔离级别可以避免不可重复读，InnoDB默认的隔离级别
串行化(serializable)事务A和事务B，事务A操作数据库时，事务B只能排队等待，事务A和事务B串行执行，可避免幻像读
隔离级别一致性关系
MySQL如何保证事务执行
事务的四大特性，实际分为两个部分。其中原子性、一致性、持久性是通过InnoDB中的两份日志保证，一个是redo log，一个是undo log。而隔离性是通过数据库的锁，加上MVCC来保证。

redo log：重做日志，记录的是事务提交时数据页的物理修改，用来实现事务的持久性。redo log由两部分组成：重做日志缓冲(redo log buffer)和重做日志文件(redo log file)，前者在内存中，后者是在磁盘中。
undo log：回滚日志，记录数据修改之前的信息，作用包含两个：提供回滚(保证事务的一致性)和MVCC(多版本并发控制)。undo log不是物理日志，而是逻辑日志，可以认为delete一条数据时，undo log会记录一个insert语句，反之亦然，当执行update时，记录一条相反的update。当事务回滚是，从undo log中的逻辑日志读取响应的记录内容并回滚
- undo log销毁：undo log执行事务时产生，事务提交时并不会立即删除，这些日志还可以用于MVCC
- undo log存储：undo log采用段的方式进行管理和记录，存放在rollback segment中，内容包含1024个undo log segment
MVCC
- 当前读：读取的当前的最新记录，读取时还要保证其他事务不能修改当前记录，会对读取的记录加锁。对于日常的操作如：select ... for update,update,insert都是一种当前读。
- 快照读：简单的select就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁
  read commited:每次select，都生成一个快照读
  repeatable read:开始事务后，第一个select时是快照读
  serializable:快照读会退化为当前读
- MVCC：多版本并发控制，维护一个数据的多个版本，使得读写没有冲突。MVCC的具体实现依赖于数据库的三个隐式字段，undo log，readview
  原理：
  - 1：隐式字段：除了自己创建的字段外，InnoDB还会自动给我们添加三个隐藏字段：
  - undo log：在insert、update和delete时生成的便于回滚数据的日志。insert时，产生的undo log日志只在事务回滚时需要，事务提交后可立即删除；而update、delete时，产生的undo log不仅回滚时需要，快照读时也会需要，不会立即删除。
  - readview：读视图是快照读时MVCC提取数据的依据，记录并维护当前活跃的事务(未提价)id，readview包含了4个核心字段：
    readview规定了版本链的数据访问规则，trx_id代表当前undo log版本链的事务id: 不同的隔离级别生成readview的时机不同： 1.read committed:在事务中每次执行快照读时都会生成readview
    2.repeatable read:在事务中第一次执行快照读时生成readview，后续复用该readview
    如何保证可重复读隔离级别
    InnoDB通过MVCC来保证可重复读，实现原理是通过InnoDB表隐藏的字段，undo log版本链，readview来实现的。而MVCC+锁，实现了事务的隔离性，一致性则由redolog和undolog保证

六、MySQL集群

为什么需要集群？

高可用性：故障检测和转移，多节点备份
可伸缩性：新增数据库节点便利，方便扩容
负载均衡：分摊各个节点的数据库压力

6.1、官方方式

MySQL官方的集群实现方式：一主一从，一主多从，cluster集群

一主一从-MySQL Replication：一个主库用来写，从库用来读，从而实现数据的多点备份(没有故障自动转移和负载均衡)。通过重放binlog来实现主库数据的异步复制，主库的执行一条命令，需要在从库再执行一次。在这个过程中，master对数据的操作记录放入binlog，生成一个log dump进程，用来给从库的I/O进程读取binlog。而从库的I/O线程读取主库的binlog，然后放入中继日志(relay log)中，从库sql进程读取relaylog文件，解析成具体操作，达到主从数据一致。
- 优势：数据被删除，可从binlog恢复；配置简单
- 劣势：slave需要从binlog获取数据并重放，与master存在时延；对master和slave之间的网络延迟要求高；单点挂了之后，不能对外提供服务
一主多从-MySQL Fabirc：一个主节点，多个从节点；在一主一从的基础上，增加了故障检测与转移，自动数据分片功能。
- 优势：数据删除可从binlog恢复；主节点挂了，可以选择其中一个从节点来当主节点，不影响对外服务
- 劣势：同一主一从一样，master和slave节点之间数据存在时延；事务及查询只能在一个分片之内，事务更新的数据不能跨分片；节点故障恢复时间长
cluster集群-MySQL Cluster：该方式为多主多从。MySQL分为两层，一层是mysql shell(每一个对应的IP地址)，包含网络、SQL语句的解析、优化等，下一层是存储引擎。cluster多出了一个NDB存储引擎，每个集群中可以有多个机器，每一个NDB就是一个节点，它们之间会同步，相当于每一段管理一段数据，没有所谓的主从。
- 优势：高可用优秀，伸缩性优秀，能自动切分数据，方便数据库的水平扩展；多负载均衡；多个主节点，没有单点故障的问题，服务恢复速度快
- 劣势：架构模式和原理复杂；只能使用存储事务引擎NDB；各个节点存在大量的数据通讯，对带宽要求高；Data Node数据会尽量放在内存中，对内存要求大，重启时，数据节点将数据加载到内存耗时较长。

6.2、MySQL主从数据同步

复制机制

全异步复制：主库在执行完客户端提交的事务后立即响应客户端，并不关心从库是否已经接受并处理
全同步复制：主库执行事务后，所有的从库都执行了该事务后才返回客户端
半同步复制：半同步介于全异步和全同步之间，主库要求等待至少一个从库接收并写到relay log中才返回给客户端 复制方式
基于行的复制：主库记录的是实际数据的变化
基于语句的的复制(逻辑复制)：主库记录的是改变数据的查询，从库执行的语句和主库一致
混合复制：根据事件的类型实时改变binlog的格式。默认基于语句的复制，在特定情况下会自动转换为基于行的模式

6.3、第三方集群方式

MMM(Master Replication Manager for MySQL)-双主多从:在一主一从的基础上进行优化，Google开源项目，Perl语言对MySQL Replication做扩展，提供一套支持双主故障自动切换和双主日常管理的脚本程序，主要用来监控MySQL主主复制并失败转移
MHA(Master High Availability)-多主多从:MySQL Replication基础上优化。主要是提供更多的主节点，但缺少VIP(虚拟ip)，需要配合keeplived等一起使用
Galera Cluster: Codership 开发的 MySQL 多主结构集群，这些主节点互为其它节点的从节点。

七、MySQL的锁类型及SQL执行计划

7.1、锁的类型

MySQL中锁的分类按照不同类型的划分可以分为不同不的锁，按照锁的粒度可以分为表锁，行锁和页锁；按照锁的思想可分为乐观锁和悲观锁

7.1.1 行锁：行锁是最大的特点就是锁的粒度很小，也是各数据库管理软件实现的最小的粒度。行锁的粒度很小，发生锁资定资源争用的概率也很小。但是行锁也带来了弊端，由于锁定资源的粒度最小，所以每次获取锁和释放锁带来的消耗也就更大了。使用行锁的主要是InnoDB引擎

InnoDB的行锁根据使用方式又分为共享锁和排它锁，而在实现锁定机制的实现过程中为了让行锁和表锁共存，InnoDB也同样使用了意向锁，也分为意向共享锁和意向排它锁。意向锁是InnoDB自动加的，不需要用户干预。

共享锁：又称为读锁(read lock)，读取数据加的锁。其他用户的都可并发读取数据，但是任何事务都不能修改数据(获取数据上的排他锁)，直到已释放所有共享锁。
排它锁：又称为写锁(write lock)，排它锁是一种悲观锁的实现。一个事务加上了排它锁之后，其他事务不能对其加任何的锁，直到该事务释放锁。
行锁的实现方式(Record lock-记录锁、Gap lock-间隙锁、Next-key lock)
Record lock(记录锁)：单条索引上加锁，record lock永远锁的是索引，而非数据本身。如果表中没有索引，InnoDB会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。
Gap lock(间隙锁)：表示只锁住一段范围，不锁记录本身。通常表示两个索引记录之间，或者索引上的第一条记录之前，或者最后一条记录后的锁。
Next-key lock:是Record lock+Gap lock，锁定范围及锁定记录本身。如一个索引有10、11、13、20几个值，那么这个索引可以被next-key lock的区间为：(负无穷,10)、(10, 11)、(11, 13)、(13, 20)、(20, 正无穷)。
MySQL默认情况下使用RR的隔离级别，而Next-key lock正是为了解决RR级别下的不可重复读和幻读问题。InnoDB对行的查询默认采用Next-key，当等值查询且索引有唯一索引时(就是只锁定一条记录)，InnoDB会对next-key lock进行优化，将其降级为Record Lock。

7.1.2 表锁：表级锁是MySQL各引擎默认的最大颗粒度的锁机制。该锁定机制最大的优点就是实现逻辑简单，带来的系统负载低，获取和释放的锁速度很快。使用表级锁的主要是MyISAM，MEMORY，CSV等非事务型引擎。

MyISAM表级锁的类型有两种模式：

表共享读锁：对表的读操作不会阻塞其他用户对同一表的读请求，但是会阻塞对同一表的写请求
表独占写锁：对表的写操作，会阻塞其他用户的读和写请求

7.2、SQL执行计划

使用Explain可以模拟优化器执行SQL语句，分析查询语句或者结构的瓶颈。查看一个语句的性能，只需要在SQL查询语句前加上Explain关键字即可，如：explain select XXX from XXX where yyy。使用Explain执行查看计划时，会输出id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra。

id：select查询序号：id相同，从上往下一次执行；id不同，id越大优先级越高，越被先执行。
select_type：查询语句类型
- SIMPLE(简单select，不使用union和子查询)
- PRIMARY(若查询中包含子查询，最外层的select被标记为PRIMARY)
- UNION(UNION中的第二个或者后面的SELECT语句)
- DEPENDENT UNION(UNION中的第二个或者后面的SELECT，取决于外面的查询)
- UNION RESULT(UNION的结果)
- SUBQUERY(子查询中的第一个select，取决于外面的查询)
- DERIVED(派生/衍生表的SELECT, FROM子句的子查询)
- MATERIALIZED(物化子查询) 在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表，后续对子查询结果集的访问将直接通过临时表获得
- UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
- UNCACHEABLE UNION(UNION查询的结果不能被缓存)
table：查询涉及的表或衍生表
partitions：查询涉及的区
type：提供了判断查询是否高效的重要依据依据，通过type字段我们来判断这次查询是全表还是索引扫描
- system：表中只有一条数据，相当于系统表，这个类型是特殊的const类型
- const：主键或者唯一索引的常量查询，表格最多只有一行符合的数据，通常const使用的主键或者唯一索引进行查询
- eq_ref：除了system和const之外，效率最高的连接类型
- ref：此类型通常出现在多表的jion查询，这对非唯一或非主键索引，或者是使用了最左前缀规则索引的查询
- range：表示使用索引范围查找，通过索引字段范围获取表中的数据记录，通常出现在：=、<>、>=、<、<=、between等操作中
- index：扫描索引树，如果索引是复合索引，并且查询列满足select所需的所有列，则仅扫描索引树(覆盖索引)，在这种情况下，extra列会显示：using indx
- all：全表扫描，没有任何索引可以使用，最坏的情况
possible_keys：指示MySQL可以从中选择查找此表中的行的索引
key：MySQL查询实际使用到的索引
key_len：表示索引中使用的字节数(只计算利用索引作为index key的索引长度，不包括group by和order by的索引长度)
- 一般，key_len等于索引列类型字节长度，如：int是4个字节，bigint为8个字节
- 如果是字符串类型，还需要考虑字符集因素，如utf8一个字符3个字节，gbk一个字符占两个字节
- 若该列允许为null，则key_len要再加一个字段
- 若该列类型为变长类型，例如varchar(text/blob不允许整列创建索引，如果创建部分索引也视为动态类型)，其中key_len需要再加两个字节
ref：显示该表的索引字段关联了哪张表的哪个字段
rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
filtered：返回结果的行数占读取行数的百分比，值越大越好
extra：包含不适合在其他列中显示但十分重要的额外信息
- use filesort：MySQL会对数据使用非索引列进行排序，而不是按照索引的顺序读取数据；若出现该值，则优化
- use temporary：使用临时表保存查询结果，如：MySQL对查询结果排序时使用临时表，常见于order by和group by；若出现该值，则优化
- use index：表示使用了索引覆盖，避免了回表操作
- use where：where子句用于限制哪一行

MySQL小结

MySQL面试题总结

一、MySQL简介

二、B树和B+树

三、MySQL索引

3.1、索引数据结构

3.2、索引类型

3.3、索引失效及最佳索引

四、MySQL引擎

五、MySQL事务和事务隔离级别

5.1、MySQL事务特性

5.2、事务并发问题

5.3、事务的隔离级别

六、MySQL集群

6.1、官方方式

6.2、MySQL主从数据同步

6.3、第三方集群方式

七、MySQL的锁类型及SQL执行计划

7.1、锁的类型

7.1.2 表锁：表级锁是MySQL各引擎默认的最大颗粒度的锁机制。该锁定机制最大的优点就是实现逻辑简单，带来的系统负载低，获取和释放的锁速度很快。使用表级锁的主要是MyISAM，MEMORY，CSV等非事务型引擎。

7.2、SQL执行计划