mysql面试题整理

1 myisam 和 innodb 引擎的区别

• innodb 支持事务，外键，myisam 不支持
• innodb 支持 mvcc ,myisam 不支持
• innodb 支持表锁、行锁，myisam 仅支持表锁
• innodb 必须有主键，myisam 不需要

2 mysql有几种锁

• 按锁粒度划分有三种：表锁、页锁、行锁
• 加锁机制：可分为 悲观锁和乐观锁
• 兼容性：意向所 和 排他锁
• 实现：记录锁、gap锁、next-key锁、插入意向锁

3 事务和锁

• update、delete、insert 无论是否在事务，都加锁。保证数据的一致性
• 快照读：读取的是快照版本，也就是 mvcc 历史版本 readView 里的数据 ，普通的 SELECT 就是快照读
• 当前读：读取的是最新版本，UPDATE、DELETE、INSERT、SELECT …  LOCK IN SHARE MODE、SELECT … FOR UPDATE 是当前读，需要加锁
• RC、RR、SERIALIZABLE 级别的隔离，当前读都会需要借助锁实现
• RR 隔离级别需要先 select … for update 加锁进行当前读操作，才能防止幻读
• 对于SERIALIZABLE隔离级别的事务来说，InnoDB规定使用加锁的方式来访问记录

4 什么情况会加锁

• update、delete、insert 无论是否在事务，都加锁。保证数据的一致性
• 在事务里，for update、LOCK IN SHARE MODE 会分别加一个 排他锁和共享锁，直至事务结束
• select 无论是否在事务，都不加锁

5 隔离级别，具体的实现原理是什么

• 读未提交
• 读已提交
• 可重复读
• 串行化
• 原理
  • 事务就ACID四个特性
  • 原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态
  • 持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复
  • 隔离性：通过锁以及MVCC，使事务相互隔离开
  • 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性

6 redo log 、 undo log 、 binlog

• redo log：
  • innodb 为了提高磁盘I/O读写性能，存在一个 buffer pool 的内存空间，数据页读入会缓存到 buffer pool，事务的提交则实时更新到 buffer pool，
    而不实时同步到磁盘（innodb 是按 16KB 一页同步的，一事务可涉及多个数据页，实时同步会造成浪费，随机I/O）。
    事务暂存在内存，则存在一致性问题，为了解决系统崩溃，保证事务的持久性，我们只需把事务对应的 redo 日志持久化到磁盘即可
• undo log：
  • 事务需要保证原子性，也是说事务中的操作要么全部完成，要么什么也不做。如果事务执行到一半，出错了怎么办-回滚。但是怎么回滚呢，靠 undo 日志。undo 日志就是我们执行sql的逆操作
• binlog：
  • binlog指二进制日志，它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中，它可以用来查看数据库的变更历史、数据库增量备份和恢复、MySQL的复制
• 一条更新语句，redo log 、 undo log 、 binlog的对应更新顺序流程是怎样的
  
• 数据的更新插入删除都是两阶段提交的，如果 redo 不是两阶段提交；
  • redo 先写，binlog 后写，会导致依赖 binlog 同步的从库数据缺失。
  • binlog 先写，redo log 后写，则会导致从库多出未提交的脏修改。主从库数据会不一致

7 double insert buffer 、redo log buffer 、change buffer

• double insert buffer：数据页的更新需要两次写doubleWrite，其原因：重做日志是对页层面的物理操作与备份。如果磁盘页坏了的时候，那么用重做日志去重做是没有意义的，所以需要一个副本，在页损坏的时候，用副本页去还原原本的页，然后再进行重做日志
  * 其中doubleWrite 原理结构有一个 doubleWrite buffer 和 共享128表空间页的物理磁盘
• change buffer: Change Buffer与 Insert Buffer一样，适用对象还是非唯一的辅助索引
• redo log buffer 类似 buffer pool，它是申请出来的一片连续内存，然后里面划分出了N多个空的redo log block。redo log 刷新到磁盘的时机如下
  • 后台线程每隔1秒自动刷盘
  • 关闭 mysql 服务
  • 记录达到 log buffer 空间一半时刷盘
  • 做 checkpoint 的时候
  • 事务提交时会刷盘。数据库中 innodb_flush_log_at_trx_commit 参数就控制了在事务提交时，如何将 buffer 中的日志数据刷新到磁盘file中
    • 参数值为0：提交事务也不进行刷盘操作
    • 参数值为1：提交事务一次就刷盘一次（ 默认刷盘策略）
    • 参数值为2：每次提交事务时，只会将buffer中的内容写入页面缓存中，不会在将页面缓存中的数据刷盘到file中

8 check point 是怎么确定的

• Sharp CheckPoint ： 数据库关闭时，将所有脏页刷新到磁盘，默认的工作方式。但如果运行时，也使用Sharp方式，会对数据库的可用性造成很大影响
• Fuzzy CheckPoint ：每次只刷新一部分脏页
  • Master ThreadCheckPoint master thread 会以一定的频率从缓冲池的脏页列表中刷新一定比例的页会磁盘，这个过程是异步的，不会阻塞查询
  • Flush_lru_list checkPoint
    • LRU列表要保证有差不多100个空闲页可用。
    • Page Cleaner线程中，会检查LRU列表中是否有足够的可用空间，没有的话，则移除LRU尾部的页，如果这些页中有脏页，则需要CheckPoint
  • Async/Sync Flush CheckPoint
    • 重做日志不可用的情况下，需要强制将一些页刷回到磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的LSN记为redo_lsn
    • 用来保证重做日志的循环使用。MySQL 5.6 以后，该操作移到了 Page Cleaner Thread 中，故不会阻塞用户的查询
  • Dirty Page too much CheckPoint
    • 总的来说，是为了保证缓冲池中有足够可用的页。InnoDB—max_dirty_pages_pct 标识当缓冲池中脏页的数量占据 75%时，强制 CheckPoint，刷新部分脏页回磁盘

9 mysql 优化的切入点

• explain分析sql语句，查看执行计划，优化sql
• 查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，避免返回不必要的数据
• 优化索引结构，看是否可以适当添加索引
• 数量大的表，可以考虑进行分库/分表
• 数据库主从分离，读写分离
• 查看mysql执行日志，分析是否有其他方面的问题

10 导致索引失效的几种情况

• 查询条件含 or、is null、is not null、in、exists、not in、!=、<> 都可能会导致索引失效
• 列是字符串类型，查询时条件没有使用引号扣起来
• like 前模糊匹配
• 在列字段使用内置函数，或者做加减乘除运算
• innodb 估算走全表扫描比走索引快，大概是 1/3 ?
• 连接查询，两个表用作关联的字段，其编码不一致
• 联合索引，不遵循最左前缀原则

11 varchar(20) 20 有什么意义 和 char(20) 的20 又有什么不同

• vachar(20) 指明其字段长度最大是 20，其长度可变
• char(20)，指明其字段长度是 20，长度不可变，多余部分空格填充
• int(10),指明有效长度是10 ，多余部分前面用零填充

12 给字符串列建索引 需要注意什么

• 选择固定前缀子串作为索引，可以节约空间，缺点可能需要多次IO扫描
• 如果不是字符串全长度作为索引，在索引覆盖时仍需要回表查询
• 固定前缀的离散值必须够大，不然导致索引失效
  • 倒序存储
  • 字符串计算hash值，建立一个hash值的索引列。范围查询时索引失效

13 有哪几种索引

• 索引结构上可分为两种类型
  • 聚簇索引
  • 非聚簇索引，二级索引
    • 一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多
    • 聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同
    • 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块
• innodb的索引语法分为五种
  • 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键
  • 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引
  • 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值
  • 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索
  • 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并
• innodb对索引的优化
  • 索引下推：是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，
    直接过滤掉不满足条件的记录，减少回表次数
  • 最左前缀原则：MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，
    相当于建立了(a)、(a,b)、(a,b,c)三个索引
  • 覆盖索引：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快

14 innodb 索引的底层实现

• innodb 索引分为 B+树索引 和 hash索引。哈希索引是自适应索引，由innodb 是否建立，DBA无法干预
• B+ 和 hash 索引的区别
  • B+树可以进行范围查询，Hash索引不能。
  • B+树支持联合索引的最左侧原则，Hash索引不支持。
  • B+树支持order by排序，Hash索引不支持。
  • Hash索引在等值查询上比B+树效率更高。
     – B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。

14.1 为啥使用B+作为数据索引，而不是二叉树，或者平衡树，B树

• 二叉树在特殊情况会退化为链表，查询操作是O(n)
• 平衡二叉树，树的高度太高。而mysql的数据持久化在磁盘，读取磁盘IO会非常多。而B+是矮胖型树，磁盘IO小很多
• B树会在非节点存在键值和数据，innodb的页默认是16KB，其高度不比平衡二叉树低多少。而B+在非叶子节点只存键值，树高度很低
• B+树的叶子节点会使用前后指针连起来，那B+树用来范围查找，排序，分组及去重都非常简单快速

15 now() 和 current_date() 有什么区别

• now()精确到时分秒，current_date() 只精确日期

16 blob 和 text 的区别

• Blob 用于存储二进制数据，而 Text 用于存储大字符串
• Blob 值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中的字节的数值。 
• text 值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较

17 建立索引的原则是什么，是否越多越好，为什么

• 数据量少的不适合加索引
• 更新比较频繁的也不适合加索引
• 区分度低的字段不适合加索引（如性别）
• 创建索引和更新索引要耗费时间
• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
• 以表中的数据进行增、删、改的时候，索引也要动态的维护
• 定义有外键的数据列一般要建立索引
• 在order by或者group by子句中，创建索引需要注意顺序

18 连接查询，innodb 有哪几种优化机制，了解吗

• Index Nested-Loop Join （NLJ）select * from t1 straight_join t2 on (t1.a=t2.a);；t2 的字段 a 上有索引
  • 从表 t1 中读入一行数据 R
  • 从数据行 R 中，取出 a 字段到表 t2 里去查找
  • 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分
  • 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束
  • 在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ
• Simple Nested-Loop Join select * from t1 straight_join t2 on (t1.a=t2.b);；t2 的字段 b 上没有索引
  • 因为 t2.b 没有索引，只能循环遍历比较。 如果 t1 有100行数据，t2 有 1000 行，则需要 100 * 1000 = 10 0000。
• Block Nested-Loop Join
  • Simple Nested-Loop Join 太低效了，innodb 会优化成 Block Nested-Loop Join
  • 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存
  • 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回
  • Block Nested-Loop Join 算在内存操作，速度快很多
• Multi-Range Read，这个优化的主要目的是尽量使用顺序读盘。
  • 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中
  • 将 read_rnd_buffer 中的 id 进行递增排序
  • 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回
• Batched Key Access（BKA），(BKA)是对 NLJ 算法的优化
  • 我们就把表 t1 的数据取出来一部分，先放到一个临时内存 join_buffer
  • 然后按照 MRR 思想批量去 第二张表 t2 根据 ID 顺序查询数据
• BNL 转 BKA
  • select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000; t2.b 没有索引
  • 一些情况下，我们可以直接在被驱动表上建索引(或者使用临时表)，这时就可以直接转成 BKA 算法了

19 buffer pool

• 预读：用于异步将磁盘的页读取到buffer pool中，预料这些页会马上被读取到
• 自适应哈希索引：提升热点等值查询sql的效率
• double write buffer，保障在innodb崩溃的时候，也能保证数据的一致性
• redo log buffer 保证 redo log 刷盘的效率

20 innodb 什么时候会用到临时表

• sort buffer、内存临时表和 join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助 SQL 语句的执行的。
  其中，我们在排序的时候用到了 sort buffer，在使用 join 语句的时候用到了 join buffer
• 临时表的建立有以下几个场景
  • union 执行流程
  • group by 执行流程
    • 尽量让 group by 过程用上表的索引，使用 explain 确认没有 Using temporary 和 Using filesort。此时优化成不使用临时表，不排序
    • 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表
  • group by 和 order_id 字段不一致时。（ group by order_id order by id）
  • join 语句，order by 的列不在驱动表里
  • distinct + order by
  • from 里的子查询

21 mysql 的主从同步整个流程是怎样的

• 主库的更新事件(update、insert、delete)被写到binlog
• 从库发起连接，连接到主库
• 此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
• 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
• 还会创建一个SQL线程，从relay log里面读取内容，从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件，
  将更新内容写入到slave的db
• 异步复制、半同步和全同步
  • 主库执行完提交事务后，立刻异步执行将结果返给给客户端，并不关心从库是否收到并处理。
    如果出现从库并未收到处理的情况，还是会有主从数据不一致的问题
  • 主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。
    相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟
  • 当主库提交事务之后，所有的从库节点必须收到、并且提交这些事务，然后主库线程才能继续做后续操作

22 mysql 读写分离时，存在主从延迟，有哪几种策略解决

• 网络延迟
• 当sql在进行锁表时，可能会导致大量 sql 积压，未同步到服务器，此时有主从延时
• 如果服务器硬件性能好，可以设置 sync_binlog = 1 , innodb_flush_log_at_trx_commit = 1
• 读写分离减少主库压力
• 如果读从库存在延迟
  • 强制读主库
  • 写入redis缓存中间件，从 redis 读取最新数据
  • 延迟读

23 自适应哈希索引(AHI)了解过吗，innodb 在什么情况会用到 hash index

• MySql数据库中的索引B+树的一般高度为3~4层，所以如果用索引B+树查找的话一般要进行3-4次的查找
• Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升
• 自适应哈希索引是通过缓冲池的B+树页来构造的，因此建立的速度很快，不需要对整张表构建哈希索引
• hash自适应索引会占用innodb buffer pool
• 自适应hash索引只适合搜索等值的查询，如 select * from table where index_col=’xxx’，而对于其他查找类型，如范围查找，是不能使用的
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序

24 B+树，它的优点在哪

• B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据
• B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构
• 扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据）
• B+Tree 的磁盘读写能力相对于 B Tree 来说更强，同数据量下磁盘I/0次数更少（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
• 范围查询和排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）

25 最左前缀匹配，索引下推，索引覆盖

• 索引下推：是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，
  直接过滤掉不满足条件的记录，减少回表次数
• 最左前缀原则：MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，
  相当于建立了(a)、(a,b)、(a,b,c)三个索引
• 覆盖索引：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快

26 数据库的乐观锁和悲观锁是什么，怎么实现

• 悲观锁:当前线程在修改，其他线程阻塞等待。select for update 、select in share lock
• 乐观锁:其他线程过来，先放过去修改。当前线程如果看到别的线程没修改过则修改成功，如果别的线程修改过则修改失败或者重试。
  读的时候带一个版本号，修改的时候版本等值比较，未改动则修改成功，不相等则失败

27 union all 和 union 有什么区别，哪个效率更好

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序
• UNION 的效率高于 UNION ALL

28 多表关联查询，有什么优化建议不

• 减少子查询，使用 join 代替
• in和not in也不慎重使用的话，会导致全表扫描
• 使用exists而不是in是一个不错的选择
• 避免索引失效

29 超大表数据分页，怎么处理

• select from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的。
  这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢。
  可以修改为select from table where id in (select id from table where age > 20 limit 1000000,10)。
  这样虽然也load了一百万的数据,但是由于索引覆盖，减少不必要的回表查询
• 如果使用的索引列 ID 是连续的,我们还可以 select * from table where id > 1000000 limit 10
• 业务上限制翻页次数

30 group by 产生的数据是不是有序的

• group by xxx 默认会按 xxx 排序
• 如果不想排序可以加语句 order by null，选择不排序

31 group by 和 order by 怎么优化

• group by 非执行时选取的索引列时，则额外使用临时表并默认排序，所以可以尽量 group by (index_x) 或者 group by xxx order by null
• group by xxx 和 order by yyy 不同的列时，也会使用临时表，尽量使得 xxx == yyy

32 分表分库，怎么分

• 分表方案（水平分表，垂直分表，切分规则hash等）
  • 垂直分表：把长度较大且访问不频繁的字段，拆分出来创建一个单独的扩展表 xxx_ext 进行存储
  • 水平分表：分成多个结构相同的表，而每个表只占原表一部分数据，然后按不同的条件分散到多个数据库中
    • 库内分表：库内分表虽然将表拆分，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分布到不同机器的库上，还在竞争同一个物理机的CPU、内存、网络IO
    • 分库分表：分库分表则是将切分出来的子表，分散到不同的数据库中，从而使得单个表的数据量变小，达到分布式的效果
  • 切分规则
    • ID RANGE: 从0到10000一个表，10001到20000一个表
    • HASH取模: 例如电商的 order 和 order_ext 关联一个 user_id ，然后user_id hash取模，分配到不同的数据库上
    • 地理区域：比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此
    • 时间：按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起
• 分库分表一些问题（事务问题？跨节点Join的问题）
  • 分布式事务
  • 跨节点Join的问题
  • 分页、排序，分组的坑
  • 全局唯一主键问题
• 分库分表中间件（Mycat，sharding-jdbc等）

33 MVCC了解过不，它的实现原理是怎样的的

• mvcc 是一系列的 undo log 相连行成的版本视图。它的实现原理主要是依赖数据记录中
  DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID 3个隐式字段，undo log ，read View 来实现的
• MVCC其好处是读不加锁，读写不冲突，并发性能好
• 详解可以看看这篇文章：数据库篇：mysql事务原理之MVCC视图+锁

34 limit 100000 很慢怎么办

• 如果id是连续的，筛选大于上次查询的最大记录ID，再往下limit
• 限制翻页页数
• order by 索引列。再做限制

35 百万级别或以上的数据，你是如何删除的

• 我们想要删除百万数据的时候可以先删除索引
• 然后批量删除其中无用数据
• 删除完成后重新创建索引