主题
Mysql 面试题
1. 什么是索引? 与索引的作用
索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构；可以快速的进行数据查找。
2.为什么 Mysql 用 B + 树做索引而不用 B 树或红黑树或者哈希索引呢？
** mysql 索引是排好序的 B+tree，所有****节点在叶子节点，叶子节点为双向链表，****同时叶子节点还加了指针（链表本身就有指针啊）NG_PLACEHOLDERmsj631uj4tf3w95e}。这样遍历**相同数目的黑色节点。
例：
红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能。
（一）B+ 树有更低的树高 平衡树的树高 O(h)=O(logdN)，其中 d 为每个节点的高度。B+ Tree 的高度一般不超过 3 层，而 红黑树 的高度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多。
（二）磁盘访问原理：操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。
（三）磁盘预读特性：为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。
1.2B-树
1.3B+ 树
a、非叶子节点不存储数据，只存储索引冗余，可以存放更多的索引
b、叶子节点包含所有节点数据
c、叶子节点用指针连接，提高区间访问的性能
d、叶子节点存放索引和数据 - 存放主键索引和数据，二级索引是单独的树，有时需要回表查询
注意：叶子节点为有序的双向链表
注意：mysql 一页的数据是 16kb，第一层的数据大小为 1 页（show global status like 'Innodb page size'）, 假如主键为 bigint 类型，占用 8 个字节，白色区域（下一个节点的文件地址）占 6 个字节，16kb/14 字节 = 1170 个，第二层为 11701170，第三层叶子节点数据大小为 1kb，相当于 16 个 11701170*16 = 2 千万条多数据。B 树未将数据放在叶子节点，那么每层存储的数据个数有限，那么树的高度会非常高
数据存储在叶子节点，是为了降低树的高度，当树的高度为 3 的时候，可存储 2 千万条数据，
如果不存储在叶子节点，每层可存储 16 条数据，则需要很多很多层，
mysql 查询的速度取决于树的高度
mysql 中存储索引用到的数据结构是 B+ 树，B+ 树的查询跟树的高度有关，是 log(n),如果用 hash 存储，那么查询时间是 O(1)。既然 hash 比 B+ 树更快，为什么 mysql 用 B+ 树来存储索引呢？
（1）这和业务场景有关。如果只选一个数据，那确实是 hash 更快。但是数据库中经常会选择多条，这时候由于 B+ 树索引有序，并且又有链表相连，它的查询效率比 hash 就快很多了；hash 不适合范围查询。
（2）数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+ 树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。
hash 索引和 B+ 数的区别：
hash 索引底层就是 hash 表,进行查找时,调用一次 hash 函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+ 树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.
那么可以看出他们有以下的不同:
- hash 索引进行等值查询更快 (一般情况下),但是却无法进行范围查询.
因为在 hash 索引中经过 hash 函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而 B+ 树的的所有节点皆遵循 (左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.
- hash 索引不支持使用索引进行排序,原理同上.
- hash 索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为 hash 函数的不可预测.AAAA和AAAAB的索引没有相关性.
- hash 索引任何时候都避免不了回表查询数据,而 B+ 树在符合某些条件 (聚簇索引,覆盖索引等) 的时候可以只通过索引完成查询.
- hash 索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生 hash 碰撞,此时效率可能极差.而 B+ 树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.
因此,在大多数情况下,直接选择 B+ 树索引可以获得稳定且较好的查询速度.而不需要使用 hash 索引.
3.在建立索引的时候,都有哪些需要考虑的因素呢?
1、字段的使用频率,经常作为条件进行查询的字段比较适合。 2、考虑联合索引中的顺序（如果需要建立联合索引） 3、字段的区分度，区分度越高越适合创建索引
注意：索引并不是创建的越多越好，索引多的话影响写入速度
4.创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
MySQL 提供了 explain 命令来查看语句的执行计划,MySQL 在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如 possilbe_key,key,key_len 等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.
关于 explain 似乎没有解释道重点。
5.那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?
1、使用不等于查询,（反向查询，不等于，不大于 不小于 not in() ） 2、索引列 列参与了数学运算或者函数 3、在字符串 like 时左边是通配符.类似于 '%aaa'. 4、当 mysql 分析全表扫描比使用索引快的时候不使用索引. 5、当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.
6.Mysql 中 MyISAM 和 InnoDB 的区别有哪些?
1.InnoDB 支持事务，MyISAM 不支持事务。 2.InnoDB 支持行级别锁，MyISAM 支持表级别锁。 3.InnoDB 是聚簇索引，数据与索引存在一起；MyISAM 是非聚集索引，数据与索引分开存储。 4.InnoDB 支持 MVCC，而 MyISAM 不支持 5.InnoDB 支持外键，MyISAM 不支持 6.InnoDB 是组织索引表，MyISAM 是堆表（ 堆表（heap table）数据插入时时存储位置是随机的，主要是数据库内部块的空闲情况决定，获取数据是按照命中率计算，全表扫表并不是先插入的数据先查到。 索引表（iot）数据存储是把表按照索引的方式存储的，数据是有序的，数据的位置是预先定好的，与插入的顺序没有关系。 ） 7.Innodb 支持崩溃后修复，MyISAM 不支持崩溃后修复 8.Innodb 内存空间不能被压缩，MyISAM 内存空间可以被压缩节省空间。
7.事务的基本要素？
原子性：同一个事务中的所有操作，要么都成功，要么都失败，不会存在一半成功一半失败的情况。例如：在银行转账业务中，我向您转账 ，我卡里扣了钱，您收到钱 这是一个完整的事务，不可能存在我向您转账，您却并未收到我转账的情况。 一致性：指的是事务从开始到结束时，数据都保持一致。例如：在银行转账业务中，不可能存在我向您转 100，您只收到 50 的情况 隔离性：指的是各个事务之间互不影响，相互独立。 持久性：数据库事务提交成功之后，即使数据库宕机。也不会对数据库数据的写入和修改产生影响
8.事物的隔离级别？
1、读未提交 (read uncommitted):一个事务还没提交，他做的变更就能被别的事务看到。也就是事务 B 读到了事务 A 未提交的数据，可能会出现脏读的情况；可以通过 " 排他锁 "，可以避免更新数据的丢失。 2、读提交 (read committed):一个事务提交之后，他做的变更才会被其他事务看到。事务 A 先读取了数据，事务 B 更新数据并提交事务，事务 A 再次读取更新的数据，出现不可重复读，避免了脏读。 3、可重复读 (repeatable read):一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。这样避免了不可重复读和脏读，但是有时可能会出现幻读。 4、串行化 (serializable): 是对于同一行记录， “ 写 ” 会加 “ 写锁 ” ， “ 读 ” 会加 “ 读锁 ” 。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。
9.Innodb 使用哪些隔离级别？
mysql 的默认隔离级别，InnoDB 默认使用的是可重复读隔离级别.
在可重复读隔离级别下可能会产生幻读，产生幻读的原因：产生幻读一部分原因是由于 MVCC 机制不完善导致，在执行 select 语句的时候，innodb 默认执行快照读，快照读可能读取的是历史的数据，不是新数据，可能会产生幻读现象。
oracle 的默认隔离级别：
10.读锁和写锁
读锁会阻塞写，写锁会阻塞读和写
例: 读与读互不影响：
1.1.2写锁与写锁相斥；写锁与读锁相斥。
例：读锁会阻塞写：
解锁：在进行下一步操作。
例：写锁会阻塞写：
解锁：
11.行锁和表锁的区别？
表锁虽然开销小，锁表快，但高并发下性能低，粒度大
行锁虽然开销大，锁表慢，锁的粒度小，发生锁冲突的概率低；处理并发的能力强
12.表中设置主键的好处？ 为什么主键设置成递增
主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的 ID 列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全.** 这个答得不太好。**
mysql 需要用唯一主键构建索引树，如果表没有主键，则 mysql 会创建隐藏字段，作为唯一索引，影响性能，用数字递增做索引，是因为比较大小快，排序快，插入索引时，树的重构变化小。
13.MySQL 中的 varchar 和 char 有什么区别
1.varchar 的长度是可变的，char 的长度是不可变的。
例：存储字符串 'abcd'，使用 char(10)，表示存储的字符串占 10 个字节（包括 6 个空字符串） 使用 varchar(10)，表示只占用 4 个字节，10 是最大值，当字符串小于 10 时，按实际字符串存储。 故而在获取数据时 char() 类型的数据需要使用 trim() 方法去掉字符串后面多余的空格。varchar() 不需要。
2.存储时，char 类型的数据比 varchar 类型的数据速度更快，因为其长度固定，方便存储、查找。故char 类型的效率比 varchar 的效率稍高。
3.从存储空间角度讲，当插入的类型数据的长度固定时，有时候需要用空格进行占位存储时会占用更大的空间。varchar 不会。char 是以空间换取时间效率，而 varchar 是以空间效率为首。
4.char 的存储方式是，对英文字符（ASCII）占用 1 个字节，对一个汉字占用两个字节；而 varchar 的存储方式是，对每个英文字符占用 2 个字节，汉字也占用 2 个字节，两者的存储数据都非 unicode 的字符数据。
14.谈谈 MySQL 优化问题
- 开启查询缓存，优化查询
- explain 你的 select 查询， 这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引 主键被如何利用的，你的数据表是如何被搜索和排序的
- 当只要一行数据时使用 limit 1, MySQL 数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据
- 为搜索字段建索引
- 使用 ENUM 而不是 VARCHAR 不用 enum 这个类型
- Prepared Statements 很像存储过程,是一种运行在后台的 SQL 语句集合,我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL 注入式” 攻击
- 垂直分表（不理解）
- 选择正确的存储引擎
这个问题太难回答
设计优化：
选择合适的字段类型，尽量不用 text 类型
合理的表设计，尽量避免多表联合查询，适当加冗余字段
控制单表字段个数，不要太多
索引（多用联合索引）
尽量避免使用外键 存储过程 触发器等
sql 优化：
sql 尽量不做业务逻辑处理
尽量不使用反向查询
查询时指明指定字段
**不使用 like %**查询
sql 尽量简单，避免多表联合查询
mysql 的优化问题完整回答：
一.数据库设计优化：
1.可以适当的设置表的冗余字段。
2.尽量避免多表查询，最好不超过 3 个表
3.设置合适的字段类型，尽量不要用 text 类型
4.主键尽量采用 int、bigint 递增。
5.创建合适的索引--单表最好不超过 5 个。
二、写 sql 的注意事项，不要让索引失效
1.尽量避免反向查询，如 not in !=等
2.尽量不要使用 is null 判断
3.尽量不要使用% like
4.不要在索引列上做计算
5.尽量避免隐式类型转换
三、explain 优化
如果发生慢查询，先看 explain 执行计划，看是否使用了索引，如果使用了索引看索引的级别，索引级别最好达到 range 级别。
查看是否由 order by 排序，看下是用的 filesort 的排序，还是 index 的索引排序，尽量走索引排序，再看索引个数，索引个数一般最好不要超过 5 个
（超过 5 个会影响写入的效率），多个索引时，查看是否满足索引的最左匹配原则。
四、sql 规范
写 sql 的时候，只查需要的字段，不要 select * ，这样可以减少网络 IO
15.谈谈你对 MVCC（多版本并发控制技术）的理解
什么是 mvcc
多版本并发控制， Multiversion Concurrency Control，简称 MVCC。是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的
作用
增删改查各种情况分析
看我的这个总结吧：https://www.yuque.com/justdoit-oriyu/kszcgs/izbeuz
这个是官方文档解释：https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html
16.MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？
（1）设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
（2）选择合适的表字段数据类型和存储引擎，适当的添加索引。
（3）MySQL 库主从读写分离。
（4）找规律分表，减少单表中的数据量提高查询速度。
（5）不经常改动的页面，生成静态页面。 -- 这个跟数据库关系不大。目前都采用静态化技术 jsp -> html
（6）书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.
这个问题很难，现阶段面试基本不会问你这个，这个也没用标准答案，以下也只是我个人经验啊
一天五万笔，100 天 500 万，一年 2000 万，这个增速，mysql 基本能支撑一年，后期优化
哲学思想：
记住一点：设计的优化才是顶层的优化，代码的优化只是修修补补。一个战略的失误，需要无数个战术去弥补，设计就是战略，其他的代码优化 数据库优化 等等都是战术
一、优化要充分考虑业务的特性，然后选择最佳方案
如果是用户数据库，大概率就是分库分表，因为用户数据不存在历史数据的情况，三年前注册的用户今天依然会使用
如果是订单库，或者是微博发布消息库，这类数据有一个特性，大家关注的都是近期的数据，很少关注几月前或者几年前的数据。所以对于这类数据库，可以选择双库，一个写库，一个读库（我在京东的时候，做钱包的时候，个人流水数据就是这么处理的），写数据的时候，写主库成功的同时，通过 kafka 等 mq 组件，写入到另外一个数据库（当然这个还需要处理数据一致性问题）
在复杂一些就是单元化啦
大的方向上我只能想到这些啦。
17.最左前缀匹配原则
**最左前缀匹配原则：**在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
**最左匹配原则：**最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询 (>、<、between、like) 就会停止匹配。
例:
联合索引的最左原则：
例：student 表字段：a 、b、c、d、e
a、b、c 为联合索引
where a and b and c 有效
where a and c 有效
where a and b 有效
where b and c 无效
where c 无效
例：匹配最左列时 这个例子演示的不太对吧
该索引遵循最左匹配原则，且使用的是联合索引中的（id）索引。
由于 id 到 name 是从左边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则。
不遵循联合索引的最左原则，type 为 All 是对整个磁盘的数据进行全表扫描。
18.数据库三范式
第一范式：.每一列属性都是不可再分的属性值，确保每一列的原子性。
第二范式：建立在第一范式的基础上，一行数据只做一件事，只描述一个事物，一个实例；通常为这个实例分配唯一的表示，即主键。
第三范式：首先是第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖，即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
注意：一般在建数据库的时候不会用到三大范式；设计数据库可能导致字段冗余，用来减少查库次数，一个系统的瓶颈一般都是数据库。数据库字段冗余最多就是浪费一些磁盘空间，但是提升了性能。
19.事务特性实现的原理
undo log 回滚日志；undo log 属于逻辑日志，不会物理删除 undo log，更新或删除日志都会记录一条 undo log；
保证了事务的原子性，undo log 是如何保证事务原子性的：unddo log 是通过回滚，来保证事务的原子性的；当 事务回滚时，可以撤销执行成功的 sql，将数据回滚成原来的样子。
undo log 主要功能：1.事务回滚 2.MVCC
redo log：重做日志，是 innodb 存储引擎独有的，记录了当前事务的修改；redo log 是 物理日志，记录的是“在某个数据页上做了什么修改”。redo log 保证了事务的持久性，redo log 采用了预写式日志，所有修改先写入日志，在更新至缓冲池，保证了数据不会因为 mysql 宕机而丢失，保证了事务的持久性。
mysql 锁及 mvcc 机制保证了事务的隔离性，MVCC 主要靠 undo log 版本链和一致性视图 read-view 实现。
上述三个特性保证了事务的一致性。