13000字带你吃透 MySQL 八股文，吊打面试官

1.如何设计一个数据库

首先得有两部分:

1）存储（文件系统）：用于存储数据到硬盘中；

2）程序实例：通知程序把数据存储到文件系统中，程序实例由大致的8个模块组成：

2.一个请求进来, mysql是怎么运行工作的

1,当我们执行启动 MySQL 命令之后，MySQL 初始化整个系统，同时各个存储引擎也被启动，当整个系统初始化结束后，由连接管理模块接手。准备好接受客户端请求了。

2, 当连接管理模块监听到客户端的连接请求（借助网络交互模块的相关功能）连接管理模块就会将连接请求转发给线程管理模块，去请求一个连接线程。线程管理模块马上又会将控制交给连接线程模块，告诉连接线程模块：现在我这边有连接请求过来了，需要建立连接，连接线程池中如果有空闲连接线程，就取出一个和客户端请求连接上，如果没有空闲的连接线程，则建立一个新的连接线程与客户端请求连接。

3, 在 MySQL 中，将客户端请求分为了两种类型：一种是 query，需要调用Query 解析和转发模块的解析才能够执行的请求；一种是 command，可以直接执行的请求。如果我们的初始化配置中打开了日志的功能，那么Query 解析与转发模块会调用日志记录模块将请求计入日志，不管是一个 Query 类型的请求还是一个 command 类型的请求，都会被记录进入日志，所以出于性能考虑，一般很少打开日志的功能。

4, 当客户端请求和连接线程接上之后，连接线程就开始处理客户端请求发送过来的各种命令（或者 query），接受相关请求。

5, 如果是一个 Query 类型的请求，会将控制权交给 Query 解析器。Query 解析器首先分析看是不是一个 select 类型的 query，如果是，则调用查询缓存模块，让它检查该 query 在query cache 中是否已经存在。如果有，则直接将 cache 中的数据返回给连接线程模块，然后通过与客户端的连接的线程将数据传输给客户端。如果 cache 中没有该 query 的数据，那么 query 将被继续传回 query 解析器，让 query解析器进行相应处理，再通过 query 分发器分发给相关处理模块。

6, 如果解析器解析结果是一条未被 cache 的 select 语句，则将控制权Query 优化器模块，如果是 DML 或者是 DDL 语句，则会交给表变更管理模块，还有其他模块这里不一一说明;

7, 在各个模块收到 Query 解析与分发模块分发过来的请求后，就会调用表管理模块请求相应的表，并获取对应的锁。然后将打开的表交给表变更管理模块。

8, 当表变更管理模块“获取”打开的表之后，调用对应的存储引擎实现模块，进行相应处理。

9, 当一条 query 或者一个 command 处理完成（成功或者失败）之后，控制权都会交还给连接线程模块。如果处理成功，则将处理结果（可能是一个 Result set，也可能是成功或者失败的标识）通过连接线程反馈给客户端。如果处理过程中发生错误，也会将相应的错误信息发送给客户端，然后连接线程模块会进行相应的清理工作，并继续等待后面的请求，重复上面提到的过程，或者完成客户端断开连接的请求。

3.什么是回表？

一个查询中，如果查询条件为二级索引，如下图的name条件, 匹配到 name 后，再根据name去查询另一个B+ 树，这就是回表， 如下图 Innodb 二级索引查询流程，存在回表问题。

4.不同引擎的索引效率区别

Innodb 用主索引查询的时候会比 myisam 快, 因为主索引查询的时候, Innodb 他的索引和数据是放在一个文件夹的，myisam 二级索引查询的时候会比 Innodb 的二级索引查询要快, 他虽然要根据叶子节点的地址去查询另一个文件 myd 里的data数据, 但是他没有回表的操作, 所以用二级索引查询的时候 myisam 比 Innodb 要快。myisam 查询流程如下图：

5.给字段添加索引需要注意什么？

0：读比写多很多的情况下, 才要去加索引；

1：较频繁的作为查询条件的字段应该创建索引；
2：唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
3：更新非常频繁的字段不适合创建索引；
4：不会出现在WHERE 子句中的字段不该创建索引；

6.Explain命令有什么作用？

MySQL的 EXPLAIN 命令用于 SQL 语句执行计划的查询。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行 SQL 语句的。这条命令并没有提供任何调整建议，但它能够提供重要的信息帮助你做出调优决策。

7.Profiling 命令有什么作用？

能够查出最近执行的SQL语句的运行状态，包括在运行过程中执行了哪些操作，各占用了多少时间，利用proflie 功能，可以分析一个SQL具体的执行代价是怎么样的，尤其是可以分析它的最大瓶颈在哪里。目前profile 功能可提供除了内存以外的其他资源消耗统计，例如CPU、I/O、CONTEXT、SWAP等。大部分情况下，profile 的结果我们主要关注两列：Status、Duration，前者表示的是profile 里的状态，后者是该状态的耗时。因此，我们最主要的是关注处于哪个状态耗时最久，这些状态中，哪些是可以进一步优化的。

8.join 的原理

8.1.Simple Nested-Loop join(简单嵌套循环)

简单嵌套循环连接实际上就是简单粗暴的嵌套循环， 如果外层表table1有1万条数据， 内层表table2有1万条数据， 那么所有数据比较的次数就是1w乘以1W=1亿， 这种查询是非常慢的；

**优化：**利用小的结果集驱动大的结果集，减少对内层表（table2）的访问, 就是访问的表的数据要小于被访问表的数据；这种方式在查询优化器里他默认已经帮我们做了,不需要我们自己去做

虽然可以像上面这么去优化， 但就算优化了， 数据量大的话他的查询速度还是很慢的，

8.2.index Nested-Loop join：（索引嵌套循环,性能最高）

这种方式他在连表的时候不是直接去连table2表， 而是去找table2 的索引，这种方式就要比Simple快的多， 因为被驱动的表的查询数变少了， 所以我们在join表的时候， 被join的字段尽量要加上索引,减少内层表数据的匹配次数;

索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数，优势极大的提升了 join的性能：

原来的匹配次数 = 外层表行数 * 内层表行数
优化后的匹配次数= 外层表的行数 * 内层表索引的高度

8.3.Block Nested-Loop join(缓存块嵌套循环)

这里有一个join buffer 他相当于一个缓冲区，以前只能让表1一个一个的去访问表2，现在有了缓冲区，可以同时多个表1的数据去访问表2，访问次数减少了，以前六个数据，每一个都要去访问表二，所以要访问6次，现在有了join buffer，他是每次三个数据一起去访问，那么总共6个数据，只需要访问2次就完成了；

9.join 怎么优化？

9.1.小结果集驱动大结果集

默认情况下用INNER JOIN连表的时候,都是会小结果集去驱动大结果集, 而用left join就不会, 他会强制用左边的结果集去驱动右边结果集。

9.2.不遵循小结果集驱动大结果集, 优化唯一主键索引

在优化器已经帮我们优化过了,以小结果集去驱动大结果集的方式, 我们还能怎么优化sql?

STRAIGHT_JOIN不会遵循小结果集驱大结果集, 改变索引 type(从ref优化到eq_ref)，a.emp_no用的不是唯一索引 ref，也就是说这个索引可能不止一个值, 可以定位到两个数据, 所以他需要继续往下去查询, 性能上是比走唯一索引(eq_ref)要差。

把 dept_emp 做为驱动表, 两个表连接是以主键做为条件的, 驱动表的主键有两个组成, 而被驱动表的主键(b.emp_no)是唯一的, 所以当用 STRAIGHT_JOIN 虽然变成大结果集驱动小结果集了, 但是我们让非唯一索引变成了唯一索引, 所以这种方式在性能上是比上图的 SQL 要好的。

上面这种方式就是交换一下驱动表和被驱动表, 让被关联的索引类型从ref变成eq_ref, 从而让性能得到一定的提升, 所以以后当我们遇到内层表和外层表数据相差并不大的情况, 我们把连表的方式交换一下, 可能就会有意想不到的收获。

9.3.为匹配条件增加索引

也就是从Simple Nested-Loop join优化成index Nested-Loop join就是在为匹配条件增加索引

9.4.增大join buffer size的大小,

当使用BNLJ时，一次缓存的数据越多，那么外层表循环的次数就越少
缓冲区默认大小: 256K
查看缓冲区大小:
show variables like ‘join_%’;

可以再mysql的配置文件my.ini中配置缓存区大小:
在[mysqld] 这个节点下配置:
join_buffer_size = 8M

9.5.减少不必要的字段查询

比如select * …

10.order by 怎么优化？

排序算法：分为单路排序、双路排序。

1.能不写select * 就不写, 尽量减少元组的的长度, 使用单路排序。

2.也可以增大 max_length_for_sort_data 的长度，默认是1024，增大后就不至于因为超了1024就走双路排序。

3.增大 sort_buffer_size 参数, 让这个空间可以容纳更多的数据, 从而减少产生临时文件, 因为临时文件多,在归并排序的时候他的耗时时间就变长了。

11.如何优化 Join 速度回答

用小结果集驱动大结果集，减少外层循环的数据量，从而减少内层循环次数。
如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快。
为匹配的条件增加索引：争取使用 INLJ，减少内层表的循环次数。
增大 join buffer size 的大小：当使用 BNLJ 时，一次缓存的数据越多，那么内层表循环的次数就越少。
减少不必要的字段查询：当用到 BNLJ 时，字段越少 join buffer 所缓存的数据就越多，内层表的循环次数就越少。

12.谈谈影响性能的相关因素

讲一下不合理的设计，如：表设计, 字段设计, 索引设计等的规范, 如果设计不规范就会有可能出现我们刚讲的这些问题。

12.1.字段设计

1. 优先选择符合存储需要的最小的数据类型。
2. 尽可能把所有列定义为 NOT NULL: Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被定义为索引后，每条记录都需要一个额外的字节，还能导致 myisam 中固定大小的索引变成可变大小的索引。--出自《高性能mysql第二版》

12.2.索引设计规范

1. 限制每张表上的索引数量,建议单张表索引不超过 5 个。
2. 每个 Innodb 表必须有个主键，中间表除外。
3. 对于频繁的查询优先考虑使用覆盖索引。

12.3.数据库 SQL 开发规范

1. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询。
2. 避免使用子查询，可以把子查询优化为 join 操作。
3. 避免使用 JOIN 关联太多的表，一般控制在3个以内。
4. 对应同一列进行 or 判断时，使用 in 代替 or。

13.MySQL 底层实现

13.1.mysql 的存储

mysql 是存储在文件系统，就是会落地到磁盘的，我们在读取数据的时候并不是直接在磁盘里读的，而是先从磁盘里的数据加载到内存，加载到内存后我们才从内存里取到对应的数据，那么就存在磁盘和内存交互的问题，磁盘的读写Io流是很慢的，那我们怎么提高IO的效率呢? 有两种方式: 第一个是减少IO的量(大小), 第二个是减少IO的次数。

磁盘预读: 当磁盘和内存进行交互的时候, 他的数据不是一字节一字节的读的, 他是几页几页的读, 页的大小通常为4k, 读的时候通常为页的倍数,8k,16k等, 当然这个也是可以设置的。

13.2.索引的文件存储形式与存储引擎有关

存储引擎: 不同的数据文件在物理磁盘上有不同的组织形式。

innodb存储引擎他的数据文件和索引文件是放在一起的，也就是 emp.ibd 里放的即有数据也有索引。

MYD:数据文件, MYI:索引文件, frm:定义字段长度,他们是分开放的, 他这种用的是 MyISAM 存储引擎。

不同的存储引擎在存储文件的时候，存储文件的格式是不一样的，具体如下。

13.4.索引文件的结构

mysql为什么选择B+树, 而不用hash、二叉树、红黑树或B树?

1）为什么不用hash?

1、hash冲突,需要去设计一个hash算法来规避, 这点很麻烦。

2、我在存储hashMap的时候,当我存完了, 当我要从里面取数据, 我们要不要把hashMap整体的数据都放到内存里面, 肯定是要的, 但是把他这些数据放到内存里是非常占用我们的内存空间的。

3、hash在进行查找的时候是根据某个key值定位到某一个数据结果,如果是等值查询的话, 确实是很快, 但是我们在工作中,等值查询的并不是很多, 相对来说的话范围查询更多一些, 当如果是范围查询的话,那么用hash,效率也就没那么好了, 这也就是hash表的索引格式不好的原因。

2）为什么不用二叉树或红黑树?

我们mysql的数据是要落地到磁盘的, 我们每次去磁盘里取数据是以页为单位,一页默认是16k的数据, 当把这数据放到树的节点上的时候, 那么一个完整的数据可能会放很多个节点, 节点越多那么树的结构就越深, 树的结构如果无限制的加深, 就存在一个树的深度问题, 也就意味着IO越频繁, 磁盘和内存交互的时候就需要n多次的访问, 无论是二叉树还是红黑树,都会因为树的深度过深而造成io次数变多, 从而影响数据读取的效率;

磁盘的读写速度是固定好的,如果你想提高磁盘的读写速度,有两种方式,也就是减少io的量(大小)和减少io的次数。

3）B树

二叉树和红黑树的节点放的是一个值, 而B树的节点里放的是一个值的集合

集合有一个概念叫做阶(Degree)，单个数据节点里面可以放多少个数据元素,3阶2个元素, 4阶3个元素, 5阶4个元素。

下面是一个B树的结构图：

一个磁盘块就是一页的数据, 一页数据就是16k（16384），也就是一个节点的大小，MySQL 每执行一次 IO 读写就是一阶为16K。

如上图，当我们要查找一个数据28, 他就会判断他在16和34之间, 16和34之间有个p2的指针,指向下一个节点, 而下一个节点他就又会判断他在25和31之间, 又有一个p2指针, 这个指针又指向下一个节点也就是磁盘块8, 然后找到28并返回, 他总共读取了三次数据, 每一次16k, 总共就是48k。

那么我们的阶是多少呢?

阶根据你的数据data有多少做判断。

假如我们每一个data的数据是1k, 一个磁盘块是固定的16k, 如果忽略key和指针所占用的空间, 那么就是一个根节点可以存放16条数据, 那么一个三层的B树可以存多少数据, 我们可以大概的估算一下, 当磁盘块1有16个元素的时候,他下面就有n多个分支, 也就是16乘16乘16,也就是2的12次方等于4096,当然这种算法只是一个大概,因为我们没有算key所占用的空间, 虽然不精准但是我们粗略算出了他大概能存多少数据, 三层的数据只能存4000条数据, 是不是太少了? 我们在公司的数据一般是百万级kw级的数据量, 那问题来了,为什么三层的B树他存的东西这么少, 空间浪费在什么地方了? 空间浪费在我们的data上面了, 这一块比较占用我们的空间, 所以我们需要把data干掉, 干掉了data也就是从B树演化到了B+树了。

4）B+树

B+树的结构是把除了叶子节点之处的其他节点上的的数据都给干掉了, 所有的数据都放在叶子节点上, 而上层节点是不再放数据, 只放key值, 那么他的的根节点范围就越来越大了, 根节点范围越大, 那么就意味着第二层的节点数就越多, 第三层也越多,那么他的计算就不是16乘16乘16了,

如果按照我们刚刚指定好的每个磁盘块16k的话, 首层磁盘块里能放多次数据, p1指向一个数据值, p2,p3都同样指向一个数据值, 假如他们一组数据占10个字节(1k 1024字节,大致算,所以去掉24), 那么16k的话是16000字节, 除以10的话就是1600个数据, 也就是第一层磁盘块里有1600条, 第二层也是1600, 第三层的是还是16因为他要存放数据data; 那么也就是1600乘1600乘16=40860000, 这是一个大致的算法, 这个数据跟刚才B树相比他是呈指数据上升的, 所以如果我有一个三层的B+树的话, 几乎可以支撑一个kw级别的数据量, 所以我们最终得到一个结论,mysql选择了B+树的存储结构。

MySQL 的 B+ 树到底是三层还是四层?

这个三层和四层不是我们规定好的, 这个东西是取决于我们的数据量的, Degree 如果等于4的话意味着只能放三个数据, 如果 Degree 等于5的话意味着只能放四个数据, 我们的根节点的磁盘块只能放16k的数据, 意思就是我们在根节子的这16k里存的数据越多, 他子节点就越多,能存储的数据量就越大,所以说索引越小越好,就是这个原因, 我们索引一般用 int 类型去设计。

B+树他支持两种查询方式:

一种是我们刚才讲的从根节点开始往下查。

另一种是他底层是一个双向链表，我们可以根据主键范围做查找，也就是可以从一个叶子节点开始，到另一个叶子节点再到另一个叶子节点这样去查找，所以我们要用的时候需要去选择一种方式去做合理的查询

数据库的底层数据结构选择小结：

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级, 增加索引就是目的就是为了减少IO次数。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

13.5.详解b+树

b+树结构

如上图，是一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如上图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

14.MySQL 名词解释

14.1.什么是回表？

回表的意思就是当们我做查询的时候使用的是一个二级索引, 下图 ,比如ma这个二级索引, 查出来的是一个id的列, 然后拿着这个id再去另一个B+树里用这个id去查询到对应 的信息(图二), 然后把这个数据再返回回去, 也就是他总共查询了两个B+树, 第一个是name的B+树, 第二个是id的B+树,这个过程就称之为回表, 所以一般在我们使用二级索引的时候才会用回表, 那么这么查询会不会很慢?答案是会的, 假如我一次B+树查询要走三次IO, 那么我两次B+树查询就是六次IO。

14.2.什么是索引覆盖？

观察下面两条sql语句, 第一条还是一样会触发回表的查询, 第二条则不会,因为他已经指定了要查询的字段就是id, 所以你根据name去查询返回的就是id,他就不需要再去查别的数据了,也就用不到回表的操作, 这就是索引覆盖

14.3.什么是索引下推？

这是一个5.6版本之后才有的一个特性。

数据存储在磁盘, mysql 有自己的内存服务, 当我们去访问数据的时候 mysql 服务要跟磁盘发生交互。

没有索引下推的时候: mysql 要先从存储引擎中拉取数据，根据name筛选数据，再回到内存中的mysql服务中再根据 age 进行数据的筛选。

有索引下推: mysql 会在拉取数据的时候直接根据 name、age 来获取数据，不需要在 mysql 服务中做任何数据的筛选。

select * from table where name=? and age=?

使用索引下推的优点:

很明显当没有使用索引下推的时候IO量会比使用索引下推的时候要大的，因为 name 只过滤了一部分数据，如果name 和 age 同时在磁盘中一次过滤掉，那么他的IO量就大大的减少了，不需要去 mysql 服务中做筛选。

使用索引下推的缺点:

索引下推的缺点就是把本在在内存中过滤的数据拿到磁盘中去完成，可以在过滤的时间上相对于内存会慢一点，但是对比不使用索引下推，那么将会大大的增加IO的量，所以性能上不会有什么影响反而会有所提升。

14.4.什么是组合索引最左匹配？

下图的 sql 最左匹配能匹配到的有三条, 1、2、4，单单 age 一个条件的索引会无法使用。

第四条能匹配到是因为 mysql 有一个内置的优化器, 他会提前把你的 name 和 age 做一个位置的调换。

演示:

创建一个表并创建一个 name 和 age 的组合索引。

key_name 就是我们创建的索引名称。

查看执行计划，可以发现单单用 age 为查询条件的时候都是不会用到索引的,这就是组合索引的最左匹配。

15.索引匹配方式

15.1.全值匹配

全值匹配指的是和组合索引中的所有列进行匹配, name、age、pos

15.2.匹配最左前缀, 只匹配索引的前面几列

15.3.匹配列前缀, 可以匹配某一列的开头部分

15.4.索引失效情况之模糊查询

%J%这种匹配方式会让索引失效，只能匹配开头不能匹配中间，%号在前面的话，索引就没有意义了。

15.4.匹配范围值

可以查找某一个范围的数据，大于号和小于号都可以使用索引，索引在进行数据存储的时候本身就是有序的, 即然有序我们就可以使用对应的索引。

between 本质上就是一个in等值查询, 而不是范围查询,所以后面的索引不会失效。

15.5.精确匹配某一列并范围匹配另外一列

索引失效之范围查询：

当我们在用一个组合索引的时候, 如果中间的某他环节出现了范围查找, 那么后续的索引都将失效,下图的pos索引是失效的, 观察 key_len 就可以知道。

15.6.只访问索引的查询（覆盖索引）

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引，没有回表问题，Using index 表示用到了索引覆盖。

16.索引的分类及使用

1.主键索引

2.唯一索引

3.普通索引

4.全文索引

5.组合索引

16.1.组合索引

当包含多个列作为索引, 需要注意的是正确的顺序依赖于该索引的查询, 同时需要考虑如何更好的满足排序和分组的需要。

建立组合索引a_b_c，观察其使用情况，以及失效场景。

语名	索引是否发挥作用
where a = 3	是,只使用了a
where a= 3 and b = 5	是, 使用了a, b
where a= 3 and b = 5 and c = 4	是, 使用了a, b, c
where b = 3 or where c = 4	否
where a = 3 and c = 4	是,仅用了a
where a = 3 and b > 10 and c = 7	是,使用了a, b
where a = 3 and b like %xx% and c = 7	使用了a

16.2.聚簇索引和非聚簇索引

聚簇索引:是指数据和相邻的索引是组织存储在一起的。

非聚簇索引:是指数据和相邻的索引是没有组织存储在一起的。

所以你会发现Innodb用的就是聚簇索引，而myISam用的就是非聚簇索引。

17.索引的优化小细节

17.1.不在 sql 中使用表达式

当使用索引列进行查询的时候，尽量不要使用表达式，把计算放到业务层，而不是数据库层。

例子:

actor 表里有两个字段, 一个 id 和一个 name, 有一个主键索引和一个普通索引

当我在 sql 中用表达式的时候,你会发现, 我查询的是id值, 为什么会用到我的name的索引? 有点解释不通, 所以在搞不明白的时候, 不要去用。

下面这种不算表达式, 所以他是正常的用的主键索引, 不会造成索引失效

17.2.优先使用主键做查询

尽量使用主键查询, 而不是其他索引, 因为主键查询不会触发回表问题。

自然主键:跟我当前的业务系统是挂钩的主键。

代理主键:跟我当前的业务系统无关的列做的主键。

开发中建议使用代理主键, 不要跟业务有挂钩。

17.3.使用前缀索引

有时候需要用一些很长的字符串做索引，这会让索引变的大且慢，通常情况下可以使用字符串的前部分，这样大大的节约索引的空间，从而提高索引的效率，不过这样会降低索引的选择性，索引选择性越低过滤掉的数据行就越少。

案例:

创建一个城市表, 有一个字段city

查询出来的多个城市

假如我要给他建索引,怎么建, 如果拿他的整个城市名称来当索引的话那他就长了, 我们可取他的前面一小部分, 也就是取前缀的方式, 但是取多少合适呢, 他这里就有一个索引长度选择性问题, 当执行下面的 sql 慢慢的去增加 left 函数里的 city 个数, 得的结果基本不变的时候就是合适的索引长度。

17.4.使用索引扫描来排序

做排序是比较浪费时间的, 索引在使用数据结构的时候有什么好处? 我们在做索引存储数据的时候,索引本身就是有序的, 我们可以利用索引进行排序,

using filesort:使用文件进行排序，当我们在做排序的时候, 要尽量让他不要出现using filesort,

17.5.union、all、in、or 都能够使用索引, 但是推荐使用 in。

union 使用索引

in 使用索引

or 使用索引

这里的 or 如果他是一个单列索引而不是组合索引的话 or 就会走索引，但一般不会这么用。

如果是单列索引， or 会使用索引。

如果是组合索引：

1）返回的全部列都是组合索引的话，那么会使用全部列所对应的索引。

2）如果部分列是组合索引，那么不会走索引。

17.6.索引失效之隐式类型转换

隐式类型转换会导致索引失效，从而触发全表扫描。

以下 sql 条件中带有单引号，他会使用索引。

不带单引号，他不会使用索引。

因为 MySQL 会有帮我们进行隐式类型转换，隐式类型转换也会导致索引失效，所以我们在编码时需要避免这种情况。

17.7.更新十分频繁, 数据区分度不高的字段上不宜建立索引

更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。

类似于性别这类区分度不大的属性，建立索引是没有意义的，不能有效的过滤数据。

17.8.join 之索引失效

当需要进行表连接的时候, 最好不要超过三张表,。并且需要 join 的字段, 数据类型必须一致 , 如果数据类型不一致也会造成索引失效。

17.9.limit 的使用

能使用 limit 的时候尽量使用 limit，关于 limit 最严谨的解释叫做限制输出，分页只是其中的一个用处而已，使用 limit 需要注意一件事，当你的数据量很大的时候，需要注意深度分页的问题。

17.10.索引个数建议

单表索引建议控制在5个以内，但是现在没有太多的限制，超了5个影响也不大，因为现在的内存和硬盘跟以前也不是一个级别的了。

17.11.组合索引字段建议不超过5个

我们在建 key 的时候，会用到组合索引的值，当组合索引多了, key 值也随之变大, key 值变大了也就会影响效率。

17.12.创建索引的时候应该避免以下错误观念

1）索引越多越好, 索引越多就意味着你存储索引的文件越多，你存储的文件越多，就意味着你的 io 量越大，io 量越大，就会影响我们整体的一个查询及更新的性能。

2）过早优化, 在不了解系统的情况下进行优化。

18.索引设计规范

1. 限制每张表上的索引数量,建议单张表索引不超过 5 个。
2. 禁止给表中的每一列都建立单独的索引。
3. 每个 Innodb 表必须有个主键。
4. 常见索引列建议。
5. 如何选择索引列的顺序。
6. 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）。
7. 对于频繁的查询优先考虑使用覆盖索引。

19.数据库 SQL 开发规范

1. 建议使用预编译语句进行数据库操作。
2. 避免数据类型的隐式转换。
3. 充分利用表上已经存在的索引。
4. 数据库设计时，应该要对以后扩展进行考虑。
5. 程序连接不同的数据库使用不同的账号，禁止跨库查询。
6. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询。
7. 禁止使用不含字段列表的 INSERT 语句。
8. 避免使用子查询，可以把子查询优化为 join 操作。
9. 避免使用 JOIN 关联太多的表。
10. 减少同数据库的交互次数。
11. 对应同一列进行 or 判断时，使用 in 代替 or。
12. 禁止使用 order by rand() 进行随机排序。
13. WHERE 从句中禁止对列进行函数转换和计算。
14. 在明显不会有重复值时使用 UNION ALL 而不是 UNION。
15. 拆分复杂的大 SQL 为多个小 SQL。

20.数据库操作行为规范

1. 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作。
2. 对于大表使用 pt-online-schema-change 修改表结构。
3. 禁止为程序使用的账号赋予 super 权限。
4. 对于程序连接数据库账号，遵循权限最小原则。

21.大数据量的分页优化

分页查询在数据不多的情况下没什么问题，但如果当分页所返回的数据在10w起步的时候明显会慢了很多，比如查询的数据：limit 100000,100; 这个分页需要我们在先找到第100000条数据，然后在第100000条开始返回前100条，这就需要MySQL先遍历到第100000条，这个IO的量是非常大的，所以会导致慢 SQL 的出现。

前提：id为自增主键

优化前:

select * from drama_info order by id asc limit 0,5;
select * from drama_info order by id asc limit 5,5;

优化后：

#子查询中只会返回一条offset偏移量的第一条数据的id, 然后外层查询从这一条 id 开始到后面的 5 条数据
select * from drama_info where id >= (select id from drama_info order by id asc limit 0,1) limit 5;
select * from drama_info where id >= (select id from drama_info order by id asc limit 5,1) limit 5;

22.MySQL 为什么建议关联查询不要超过三张

大多数业务都会考虑把这种操作放到service层，一般是有以下几方面考虑：

第一：单机数据库计算资源很贵，数据库同时要服务写和读，都需要消耗CPU，为了能让数据库的吞吐变得更高，而业务又不在乎那几百微妙到毫秒级的延时差距，业务会把更多计算放到service层做，毕竟计算资源很好水平扩展，数据库很难啊，所以大多数业务会把纯计算操作放到service层做，而将数据库当成一种带事务能力的kv系统来使用，这是一种重业务，轻DB的架构思路。
第二：对于一些大型公司由于数据规模庞大，不得不对数据库进行分库分表，对于分库分表的应用，使用join也受到了很多限制，除非业务上能够允许要 join 的两个表在同一个物理库中。而中间件一般对跨库join都支持不好。所以这种情况下的 join 也会放在业务层处理。

23.事务四大特性（ACID）

原子性（Atomicity）：化学中的原子指不可再分的基本微粒，数据库中原子性强调事务是一个不可分割的整体，事务开始后所有操作要么全部成功，要么全部失败，不可能停滞在中间某个环节。如果事务执行过程中出错就会回滚到事务开始前的状态，所有的操作就像没有发生一样不会对数据库有任何影响。
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态，即一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还是5000，这就是事务的一致性。
隔离性（Isolation）：当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离，比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转入钱。
持久性（Durability）：一个事务一旦被提交，则对数据库的所有更新将被保存到数据库中，不能回滚

24.数据库事务隔离级别

24.1.隔离级别概念

在事务ACID特性中，隔离性（Isolation）使得多事务之间的访问（事务并发）保持独立和隔离。然而完全的隔离是不现实，会严重影响性能，这样就牵涉到事务的隔离等级和机制。

SQL标准定义了四类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

以上四个隔离等级由低到高依次为：Read uncommitted 、Read committed 、Repeatable read 、Serializable，从 Read committed 开始可以逐个解决脏读 、不可重复读 、幻读问题。当然它在解决问题的同时也带来了性能问题，所以在选择时需要考虑是否有必要使用更高的隔离级别。

问题解释：

# 脏读：
即事务A可以读取事务B未提交的执行结果；
# 不可重复读：
重复读数据的情况下，会存在数据不一致的问题（A事务第一次读的数据跟第二次读的不一样，因为数据被其他事务给修改了）；
# 幻读：
幻读指当用户读取某范围数据后，这时另一个事务在这个范围中插入了一些数据，当用户再读取该范围的数据行时，会发现存在其他的数据，这就产生了幻读。

24.2.Read Uncommitted（读未提交）

存在脏读问题，可以读取到其他事务未提交的数据；

24.3.Read Committed（读已提交）

解决了脏读的问题，但是它存在重复读取同一数据时返回的结果不相同的情况(也就是说有可能会读到别人已经提交的数据)，所以它是不可重复读的；

24.4.Repeatable Read（可重复读）

解决读已提交存在的不可重复读的问题，它是可重复读的， 但是还存在幻读的问题；（MySQL的默认事务隔离级别）

24.5.Serializable（串行化读）

最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现。

查看当前会话隔离级别：select @@tx_isolation;
查看系统当前隔离级别：select @@global.tx_isolation;
设置当前会话隔离级别：set session transaction isolation level repeatable read;
设置系统当前隔离级别：set global transaction isolation level repeatable read;

25.MVCC

25.1.MVCC解决的问题是什么？

数据库并发场景有三种，分别为：

1）读读：不存在任何问题，也不需要并发控制。

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

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

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决以下问题：

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

2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题。

25.2.MVCC总结（记）：

MVCC 全称 Multi-Version Concurrency Control，即多版本并发控制。一般在数据库管理系统中，实现对数据库的并发访问。它会为每个修改保存一个版本到 undolog 日志中。

解决的问题：

1、在并发读写数据库时，可以做到在读写操作不会相互阻塞，提高了数据库并发读写的性能。

2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题。

MVCC实现的原理：

mvcc的实现原理主要依赖于以下的设计实现：

1.记录中的三个隐藏字段(DB_TRX_ID：最近修改事务id、DB_ROLL_PTR：回滚指针、DB_ROW_JD：隐藏的主键)

2.undolog

3.read view

我们在存储数据的时候会多存储三个隐藏字段，然后 MySQL 每一次执行修改在 undolog 里都会加一条记录，新的记录会指向上一条 undolog (回滚日志)中旧的记录（多条记录形成一个链表），在 read view 快照读（执行查询语句）的时候就会先获取最新的一条记录的事务的id，去根据可见性算法与当前其他活跃事务的id去做比较，如果不符合可见性，那么就通过回滚指针回去取出 undolog 中上上条的事务id做比较，即遍历链表中的DB_TRX_ID(事务id)，直到找到满足可见性条件的事务id的记录。（具体的可见性判断方式记不清）

以上的 MVCC 的流程不管是在读已提交还是可重复读的隔离级别下都是一样的。不一样的是在读已提交隔离级别中，是每个快照读都会生成并获取最新的Read View,而在可重复读隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View。

26.MyISAM与InnoDB关于锁方面的区别是什么？

26.1.MyISAM

MyISAM 默认用的是表级锁，不支持行级锁。

可以通过打开两个会话去操作同一个数据库测试什么时候会开启表级锁或行级锁；测试的时候需要表中有大量的数据（200W条以上这样比较容易测试的出来）；

MyISAM两个查询语句依次执行的时候会上一个读锁，这个读锁是一个共享锁，多个查询语句可以同时执行（也可修改为排他锁，在语句最后加上for update），

MyISAM先执行查询再执行更新的时候也会上一个读锁，不过这个读锁是一个排他锁，就是在查询的时候不能同时进行更新；

共享锁：多个语句可以同时执行，不需要等待上一个语句执行完；

排他锁：多个语句不可以同时执行，需要等上一个语句执行完；

MyISAM只要先执行写的操作（写锁），那么后面不管再执行写还是读，都需要等待上一个写操作执行完，所以写锁也叫排他锁；

证明 MyISAM 是表级锁：

执行以下两个语句会发现语句二会被锁住，查询1到200000，但是第200001条数据更新不了；

26.2.InnoDB

InnoDB默认用的是行级锁，也支持表级锁。

当不走索引的时候查询用的是表级锁，当走索引的时候查询用的才是行级锁和get锁？（get锁走普通索引） ；

InnoDB是支持事务的，我们可以通过设置手动提交来测试InnoDB的锁；

show variables like ‘autocommit’; //查看是否自动提交

set autocommit = 0;//设置自动提交为OFF， 执行语句的时候用commit手动提交

26.3.MyISAM 和 InnoDB 的选择

MyISAM： 对数据进行增删改的频率不高，查询非常频繁，不要求事务。

InnoDB： 数据增删改查都相当频繁，可靠性要求较高，要求支持事务。

27.数据库锁的分类