共计 7943 个字符，预计需要花费 20 分钟才能阅读完成。
前言
本文次要受众为开发人员, 所以不波及到 MySQL 的服务部署等操作, 且内容较多, 大家筹备好急躁和瓜子矿泉水.
前一阵零碎的学习了一下 MySQL, 也有一些实际操作教训, 偶尔看到一篇和 MySQL 相干的面试文章, 发现其中的一些问题本人也答复不好, 尽管知识点大部分都晓得, 然而无奈将常识串联起来.
因而决定搞一个 MySQL 灵魂 100 问, 试着用答复问题的形式, 让本人对知识点的了解更加深刻一点.
此文不会事无巨细的从 select 的用法开始解说 mysql, 次要针对的是开发人员须要晓得的一些 MySQL 的知识点, 次要包含索引, 事务, 优化等方面, 以在面试中高频的问句模式给出答案.
1. 什么是索引?
索引是一种数据结构, 能够帮忙咱们疾速的进行数据的查找.
2. 索引是个什么样的数据结构呢?
索引的数据结构和具体存储引擎的实现无关, 在 MySQL 中应用较多的索引有 Hash 索引,B+ 树索引等, 而咱们常常应用的 InnoDB 存储引擎的默认索引实现为:B+ 树索引.
3. Hash 索引和 B + 树所有有什么区别或者说优劣呢?
首先要晓得 Hash 索引和 B + 树索引的底层实现原理:
hash 索引底层就是 hash 表, 进行查找时, 调用一次 hash 函数就能够获取到相应的键值, 之后进行回表查问取得理论数据.B+ 树底层实现是多路均衡查找树. 对于每一次的查问都是从根节点登程, 查找到叶子节点方能够取得所查键值, 而后依据查问判断是否须要回表查问数据.
那么能够看出他们有以下的不同:
- hash 索引进行等值查问更快(个别状况下), 然而却无奈进行范畴查问.
因为在 hash 索引中通过 hash 函数建设索引之后, 索引的程序与原程序无奈保持一致, 不能反对范畴查问. 而 B + 树的的所有节点皆遵循(左节点小于父节点, 右节点大于父节点, 多叉树也相似), 人造反对范畴.
- hash 索引不反对应用索引进行排序, 原理同上.
- hash 索引不反对含糊查问以及多列索引的最左前缀匹配. 原理也是因为 hash 函数的不可预测.AAAA和 AAAAB 的索引没有相关性.
- hash 索引任何时候都防止不了回表查问数据, 而 B + 树在合乎某些条件 (聚簇索引, 笼罩索引等) 的时候能够只通过索引实现查问.
- hash 索引尽管在等值查问上较快, 然而不稳固. 性能不可预测, 当某个键值存在大量反复的时候, 产生 hash 碰撞, 此时效率可能极差. 而 B + 树的查问效率比较稳定, 对于所有的查问都是从根节点到叶子节点, 且树的高度较低.
因而, 在大多数状况下, 间接抉择 B + 树索引能够取得稳固且较好的查问速度. 而不须要应用 hash 索引.
4. 下面提到了 B + 树在满足聚簇索引和笼罩索引的时候不须要回表查问数据, 什么是聚簇索引?
在 B + 树的索引中, 叶子节点可能存储了以后的 key 值, 也可能存储了以后的 key 值以及整行的数据, 这就是聚簇索引和非聚簇索引. 在 InnoDB 中, 只有主键索引是聚簇索引, 如果没有主键, 则筛选一个惟一键建设聚簇索引. 如果没有惟一键, 则隐式的生成一个键来建设聚簇索引.
当查问应用聚簇索引时, 在对应的叶子节点, 能够获取到整行数据, 因而不必再次进行回表查问.
5. 非聚簇索引肯定会回表查问吗?
不肯定, 这波及到查问语句所要求的字段是否全副命中了索引, 如果全副命中了索引, 那么就不用再进行回表查问.
举个简略的例子, 假如咱们在员工表的年龄上建设了索引, 那么当进行 select age from employee where age < 20 的查问时, 在索引的叶子节点上, 曾经蕴含了 age 信息, 不会再次进行回表查问.
6. 在建设索引的时候, 都有哪些须要思考的因素呢?
建设索引的时候个别要思考到字段的应用频率, 常常作为条件进行查问的字段比拟适宜. 如果须要建设联结索引的话, 还须要思考联结索引中的程序. 此外也要思考其余方面, 比方避免过多的所有对表造成太大的压力. 这些都和理论的表构造以及查问形式无关.
7. 联结索引是什么? 为什么须要留神联结索引中的程序?
MySQL 能够应用多个字段同时建设一个索引, 叫做联结索引. 在联结索引中, 如果想要命中索引, 须要依照建设索引时的字段程序挨个应用, 否则无奈命中索引.
具体起因为:
MySQL 应用索引时须要索引有序, 假如当初建设了 ”name,age,school” 的联结索引, 那么索引的排序为: 先依照 name 排序, 如果 name 雷同, 则依照 age 排序, 如果 age 的值也相等, 则依照 school 进行排序.
当进行查问时, 此时索引仅仅依照 name 严格有序, 因而必须首先应用 name 字段进行等值查问, 之后对于匹配到的列而言, 其依照 age 字段严格有序, 此时能够应用 age 字段用做索引查找,,, 以此类推. 因而在建设联结索引的时候应该留神索引列的程序, 个别状况下, 将查问需要频繁或者字段选择性高的列放在后面. 此外能够依据特例的查问或者表构造进行独自的调整.
8. 创立的索引有没有被应用到? 或者说怎么才能够晓得这条语句运行很慢的起因?
MySQL 提供了 explain 命令来查看语句的执行打算,MySQL 在执行某个语句之前, 会将该语句过一遍查问优化器, 之后会拿到对语句的剖析, 也就是执行打算, 其中蕴含了许多信息. 能够通过其中和索引无关的信息来剖析是否命中了索引, 例如 possilbe_key,key,key_len 等字段, 别离阐明了此语句可能会应用的索引, 理论应用的索引以及应用的索引长度.
9. 那么在哪些状况下会产生针对该列创立了索引然而在查问的时候并没有应用呢?
- 应用不等于查问,
- 列参加了数学运算或者函数
- 在字符串 like 时右边是通配符. 相似于 ’%aaa’.
- 当 mysql 剖析全表扫描比应用索引快的时候不应用索引.
- 当应用联结索引, 后面一个条件为范畴查问, 前面的即便合乎最左前缀准则, 也无奈应用索引.
以上状况,MySQL 无奈应用索引.
事务相干
1. 什么是事务?
了解什么是事务最经典的就是转账的栗子, 置信大家也都理解, 这里就不再说一边了.
事务是一系列的操作, 他们要合乎 ACID 个性. 最常见的了解就是: 事务中的操作要么全副胜利, 要么全副失败. 然而只是这样还不够的.
2. ACID 是什么? 能够具体说一下吗?
A=Atomicity
原子性, 就是下面说的, 要么全副胜利, 要么全副失败. 不可能只执行一部分操作.
C=Consistency
零碎 (数据库) 总是从一个一致性的状态转移到另一个一致性的状态, 不会存在中间状态.
I=Isolation
隔离性: 通常来说: 一个事务在齐全提交之前, 对其余事务是不可见的. 留神后面的通常来说加了红色, 意味着有例外情况.
D=Durability
持久性, 一旦事务提交, 那么就永远是这样子了, 哪怕零碎解体也不会影响到这个事务的后果.
3. 同时有多个事务在进行会怎么样呢?
多事务的并发进行个别会造成以下几个问题:
- 脏读: A 事务读取到了 B 事务未提交的内容, 而 B 事务前面进行了回滚.
- 不可反复读: 当设置 A 事务只能读取 B 事务曾经提交的局部, 会造成在 A 事务内的两次查问, 后果居然不一样, 因为在此期间 B 事务进行了提交操作.
- 幻读: A 事务读取了一个范畴的内容, 而同时 B 事务在此期间插入了一条数据. 造成 ” 幻觉 ”.
4. 怎么解决这些问题呢?MySQL 的事务隔离级别理解吗?
MySQL 的四种隔离级别如下:
- 未提交读(READ UNCOMMITTED)
这就是下面所说的例外情况了, 这个隔离级别下, 其余事务能够看到本事务没有提交的局部批改. 因而会造成脏读的问题(读取到了其余事务未提交的局部, 而之后该事务进行了回滚).
这个级别的性能没有足够大的劣势, 然而又有很多的问题, 因而很少应用.
- 已提交读(READ COMMITTED)
其余事务只能读取到本事务曾经提交的局部. 这个隔离级别有 不可反复读的问题, 在同一个事务内的两次读取, 拿到的后果居然不一样, 因为另外一个事务对数据进行了批改.
- REPEATABLE READ(可反复读)
可反复读隔离级别解决了下面不可反复读的问题(看名字也晓得), 然而依然有一个新问题, 就是 幻读, 当你读取 id> 10 的数据行时, 对波及到的所有行加上了读锁, 此时例外一个事务新插入了一条 id=11 的数据, 因为是新插入的, 所以不会触发下面的锁的排挤, 那么进行本事务进行下一次的查问时会发现有一条 id=11 的数据, 而上次的查问操作并没有获取到, 再进行插入就会有主键抵触的问题.
- SERIALIZABLE(可串行化)
这是最高的隔离级别, 能够解决下面提到的所有问题, 因为他强制将所以的操作串行执行, 这会导致并发性能极速降落, 因而也不是很罕用.
5. Innodb 应用的是哪种隔离级别呢?
InnoDB 默认应用的是可反复读隔离级别.
6. 对 MySQL 的锁理解吗?
当数据库有并发事务的时候, 可能会产生数据的不统一, 这时候须要一些机制来保障拜访的秩序, 锁机制就是这样的一个机制.
就像酒店的房间, 如果大家随便进出, 就会呈现多人争夺同一个房间的状况, 而在房间上装上锁, 申请到钥匙的人才能够入住并且将房间锁起来, 其他人只有等他应用结束才能够再次应用.
7. MySQL 都有哪些锁呢? 像下面那样子进行锁定岂不是有点妨碍并发效率了?
从锁的类别上来讲, 有共享锁和排他锁.
共享锁: 又叫做读锁. 当用户要进行数据的读取时, 对数据加上共享锁. 共享锁能够同时加上多个.
排他锁: 又叫做写锁. 当用户要进行数据的写入时, 对数据加上排他锁. 排他锁只能够加一个, 他和其余的排他锁, 共享锁都相斥.
用下面的例子来说就是用户的行为有两种, 一种是来看房, 多个用户一起看房是能够承受的. 一种是真正的入住一晚, 在这期间, 无论是想入住的还是想看房的都不能够.
锁的粒度取决于具体的存储引擎,InnoDB 实现了行级锁, 页级锁, 表级锁.
他们的加锁开销从大大小, 并发能力也是从大到小.
表结构设计
1. 为什么要尽量设定一个主键?
主键是数据库确保数据行在整张表唯一性的保障, 即便业务上本张表没有主键, 也倡议增加一个自增长的 ID 列作为主键. 设定了主键之后, 在后续的删改查的时候可能更加疾速以及确保操作数据范畴平安.
2. 主键应用自增 ID 还是 UUID?
举荐应用自增 ID, 不要应用 UUID.
因为在 InnoDB 存储引擎中, 主键索引是作为聚簇索引存在的, 也就是说, 主键索引的 B + 树叶子节点上存储了主键索引以及全副的数据(依照程序), 如果主键索引是自增 ID, 那么只须要一直向后排列即可, 如果是 UUID, 因为到来的 ID 与原来的大小不确定, 会造成十分多的数据插入, 数据挪动, 而后导致产生很多的内存碎片, 进而造成插入性能的降落.
总之, 在数据量大一些的状况下, 用自增主键性能会好一些.
图片来源于《高性能 MySQL》: 其中默认后缀为应用自增 ID,__uuid为应用 UUID 为主键的测试, 测试了插入 100w 行和 300w 行的性能._
对于主键是聚簇索引, 如果没有主键,InnoDB 会抉择一个惟一键来作为聚簇索引, 如果没有惟一键, 会生成一个隐式的主键.
If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.
If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.
3. 字段为什么要求定义为 not null?
MySQL 官网这样介绍:
NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.
null 值会占用更多的字节, 且会在程序中造成很多与预期不符的状况.
4. 如果要存储用户的明码散列, 应该应用什么字段进行存储?
明码散列, 盐, 用户身份证号等固定长度的字符串应该应用 char 而不是 varchar 来存储, 这样能够节俭空间且进步检索效率.
存储引擎相干
1. MySQL 反对哪些存储引擎?
MySQL 反对多种存储引擎, 比方 InnoDB,MyISAM,Memory,Archive 等等. 在大多数的状况下, 间接抉择应用 InnoDB 引擎都是最合适的,InnoDB 也是 MySQL 的默认存储引擎.
- InnoDB 和 MyISAM 有什么区别?
- InnoDB 反对事物，而 MyISAM 不反对事物
- InnoDB 反对行级锁，而 MyISAM 反对表级锁
- InnoDB 反对 MVCC, 而 MyISAM 不反对
- InnoDB 反对外键，而 MyISAM 不反对
- InnoDB 不反对全文索引，而 MyISAM 反对。
零散问题
1. MySQL 中的 varchar 和 char 有什么区别.
char 是一个定长字段, 如果申请了 char(10) 的空间, 那么无论理论存储多少内容. 该字段都占用 10 个字符, 而 varchar 是变长的, 也就是说申请的只是最大长度, 占用的空间为理论字符长度 +1, 最初一个字符存储应用了多长的空间.
在检索效率上来讲,char > varchar, 因而在应用中, 如果确定某个字段的值的长度, 能够应用 char, 否则应该尽量应用 varchar. 例如存储用户 MD5 加密后的明码, 则应该应用 char.
2. varchar(10)和 int(10)代表什么含意?
varchar 的 10 代表了申请的空间长度, 也是能够存储的数据的最大长度, 而 int 的 10 只是代表了展现的长度, 有余 10 位以 0 填充. 也就是说,int(1)和 int(10)所能存储的数字大小以及占用的空间都是雷同的, 只是在展现时依照长度展现.
3. MySQL 的 binlog 有有几种录入格局? 别离有什么区别?
有三种格局,statement,row 和 mixed.
- statement 模式下, 记录单元为语句. 即每一个 sql 造成的影响会记录. 因为 sql 的执行是有上下文的, 因而在保留的时候须要保留相干的信息, 同时还有一些应用了函数之类的语句无奈被记录复制.
- row 级别下, 记录单元为每一行的改变, 根本是能够全部记下来然而因为很多操作, 会导致大量行的改变(比方 alter table), 因而这种模式的文件保留的信息太多, 日志量太大.
- mixed. 一种折中的计划, 一般操作应用 statement 记录, 当无奈应用 statement 的时候应用 row.
此外, 新版的 MySQL 中对 row 级别也做了一些优化, 当表构造发生变化的时候, 会记录语句而不是逐行记录.
4. 超大分页怎么解决?
超大的分页个别从两个方向上来解决.
- 数据库层面, 这也是咱们次要集中关注的 (尽管收效没那么大), 相似于
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, 效率也是不错的, 优化的可能性有许多种, 然而核心思想都一样, 就是缩小 load 的数据. - 从需要的角度缩小这种申请 …. 次要是不做相似的需要 (间接跳转到几百万页之后的具体某一页. 只容许逐页查看或者依照给定的路线走, 这样可预测, 可缓存) 以及避免 ID 透露且间断被人歹意攻打.
解决超大分页, 其实次要是靠缓存, 可预测性的提前查到内容, 缓存至 redis 等 k - V 数据库中, 间接返回即可.
在阿里巴巴《Java 开发手册》中, 对超大分页的解决办法是相似于下面提到的第一种.
5. 关怀过业务零碎外面的 sql 耗时吗? 统计过慢查问吗? 对慢查问都怎么优化过?
在业务零碎中, 除了应用主键进行的查问, 其余的我都会在测试库上测试其耗时, 慢查问的统计次要由运维在做, 会定期将业务中的慢查问反馈给咱们.
慢查问的优化首先要搞明确慢的起因是什么? 是查问条件没有命中索引? 是 load 了不须要的数据列? 还是数据量太大?
所以优化也是针对这三个方向来的,
- 首先剖析语句, 看看是否 load 了额定的数据, 可能是查问了多余的行并且摈弃掉了, 可能是加载了许多后果中并不需要的列, 对语句进行剖析以及重写.
- 剖析语句的执行打算, 而后取得其应用索引的状况, 之后批改语句或者批改索引, 使得语句能够尽可能的命中索引.
- 如果对语句的优化曾经无奈进行, 能够思考表中的数据量是否太大, 如果是的话能够进行横向或者纵向的分表.
6. 下面提到横向分表和纵向分表, 能够别离举一个适宜他们的例子吗?
横向分表是按行分表. 假如咱们有一张用户表, 主键是自增 ID 且同时是用户的 ID. 数据量较大, 有 1 亿多条, 那么此时放在一张表里的查问成果就不太现实. 咱们能够依据主键 ID 进行分表, 无论是按尾号分, 或者按 ID 的区间分都是能够的. 假如依照尾号 0 -99 分为 100 个表, 那么每张表中的数据就仅有 100w. 这时的查问效率无疑是能够满足要求的.
纵向分表是按列分表. 假如咱们当初有一张文章表. 蕴含字段id- 摘要 - 内容. 而零碎中的展现模式是刷新出一个列表, 列表中仅蕴含题目和摘要, 当用户点击某篇文章进入详情时才须要注释内容. 此时, 如果数据量大, 将内容这个很大且不常常应用的列放在一起会拖慢原表的查问速度. 咱们能够将下面的表分为两张.id- 摘要,id- 内容. 当用户点击详情, 那主键再来取一次内容即可. 而减少的存储量只是很小的主键字段. 代价很小.
当然, 分表其实和业务的关联度很高, 在分表之前肯定要做好调研以及 benchmark. 不要依照本人的猜测自觉操作.
7. 什么是存储过程？有哪些优缺点？
存储过程是一些预编译的 SQL 语句。1、更加直白的了解：存储过程能够说是一个记录集，它是由一些 T -SQL 语句组成的代码块，这些 T -SQL 语句代码像一个办法一样实现一些性能（对单表或多表的增删改查），而后再给这个代码块取一个名字，在用到这个性能的时候调用他就行了。2、存储过程是一个预编译的代码块，执行效率比拟高, 一个存储过程代替大量 T_SQL 语句，能够升高网络通信量，进步通信速率, 能够肯定水平上确保数据安全
然而, 在互联网我的项目中, 其实是不太举荐存储过程的, 比拟闻名的就是阿里的《Java 开发手册》中禁止应用存储过程, 我集体的了解是, 在互联网我的项目中, 迭代太快, 我的项目的生命周期也比拟短, 人员流动相比于传统的我的项目也更加频繁, 在这样的状况下, 存储过程的治理的确是没有那么不便, 同时, 复用性也没有写在服务层那么好.
8. 说一说三个范式
第一范式: 每个列都不能够再拆分. 第二范式: 非主键列齐全依赖于主键, 而不能是依赖于主键的一部分. 第三范式: 非主键列只依赖于主键, 不依赖于其余非主键.
在设计数据库构造的时候, 要尽量恪守三范式, 如果不恪守, 必须有足够的理由. 比方性能. 事实上咱们常常会为了性能而斗争数据库的设计.
9. MyBatis 中的 #和 $ 有什么区别?
乱入了一个奇怪的问题 ….. 我只是想独自记录一下这个问题, 因为呈现频率太高了.
会将传入的内容当做字符串, 而 $ 会间接将传入值拼接在 sql 语句中.
所以 #能够在肯定水平上预防 sql 注入攻打.
作者：呼延十
链接：https://juejin.im/post/5d3513…
关注 Java 编程鸭 微信公众号，后盾回复：码农大礼包 能够获取最新整顿的技术材料一份。涵盖 Java 框架学习、架构师学习等！
