MySQL数据库知识点大全

上传图片和排版比较麻烦，附上我的笔记链接：

文档：【MySQL数据库知识点大全】---6.29.not...
链接：http://note.youdao.com/noteshare?id=53dda8629ef583abee8a1c298381ff3c&sub=008DB5C048C14E56BD9C2496B3081851

 

一、数据库基本知识：

 

1、数据库三大范式：

（1）简单归纳：

　　第一范式（1NF）：字段不可分；保证了他的原子性。

　　第二范式（2NF）：一基础上：有主键，非主键字段完全依赖主键；

　　第三范式（3NF）：二基础上：只依赖于主键，非主键字段不能相互依赖。

 

（2）解释：

　　1NF：原子性。 字段不可再分,否则就不是关系数据库;；

　　2NF：唯一性 。一个表只说明一个事物；

　　3NF：每列都与主键有直接关系，不存在传递依赖。

 

 

（3）可以不遵守不？

反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相 应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此，决定 做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和 其他方法经常能够解决性能问题，而不必采用反规范这种方法。

 

反规范的例子：

 

增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。 增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增 加的派生列其作用是在查询时减少连接操作，避免使用集函数。 重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新 组成一个表来减少连接而提高性能。

 

2、MySQL有哪些数据类型：

2.1 类别

枚举， 字符串，整数，实数，日期。。

 

2.2 varchar 和 char

char: (定长，要pad)

CHAR是定长的，根据定义的字符串长度分配足够的空间。

CHAR会根据需要使用空格进行填充方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

CHAR存储的内容超出设置的长度时，内容同样会被截断

 

 

varchar: (变长， 要存一个长度)

VARCHAR用于存储可变长字符串，它比定长类型更节省空间。

VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，

否则使用2字节表示。

VARCHAR存储的内容超出设置的长度时，内容会被截断

 

varchar vs char:

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

 

 

3、了解MySQL基本架构：一条SQL查询语句是如何执行的？

 

3.1 MySQL基本架构：

 

MySQL分为Server层和存储引擎两部分。

 

1、Server层包括

连接器： 用来管理连接，权限验证

分析器：用来词法分析和语法分析

优化器：执行计划生成，索引选择

执行器：操作引擎，返回结果

涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

 

2、存储引擎层负责数据的存储和提取，架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

 

3、以通过指定存储引擎的类型来选择别的引擎，比如在 create table语句中使用 engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同，支持的功能也不同，不同的存储引擎共用一个Server 层，也就是从连接器到执行器的部分。

 

3.2 一条SQL语句的执行过程

3.2.1 版本一说法：（背这个！！）

1、客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配。

2、建立连接后客户端发送一条语句，mysql收到该语句后，通过命令分发器判断其是否是一条select语句，如果是，在开启查询缓存的情况下，先在查询缓存中查找该SQL是否完全匹配，如果完全匹配，验证当前用户是否具备查询权限，如果权限验证通过，直接返回结果集给客户端，该查询也就完成了。如果不匹配继续向下执行。

3、如果在查询缓存中未匹配成功，则将语句交给分析器作语法分析，MySQL需要知道到底要查哪些东西，如果语法不对，就会返回语法错误中断查询。

4、分析器的工作完成后，将语句传递给预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义等。

5、语句解析完成后，MySQL就知道要查什么了，之后会将语句传递给优化器进行优化（通过索引选择最快的查找方式），并生成执行计划。

6、之后交给执行器去具体执行该语句，在执行之前，会先检查该用户是否具有查询权限，如果有，继续执行该语句。执行器开始执行后，会逐渐将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

 

 

3.2.2 版本二说法：（《MySQL实战45讲》）

1、连接器：先连接到这个数据库上，负责跟客户端建立连接、获取权限、维持和管理连接。

 

建立好连接之后（TCP握手），连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

（这就意味着一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置）

 

连接完成之后，如果没有后续的动作，这个链接就处于空闲状态，show processlist可以看到空闲链接。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

 

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

 

但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

 

解决方法：

1、定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2、如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

 

 

2、查询缓存：

 

连接建立完成之后，就可以执行select语句了，执行逻辑就会来到第二步，查询缓存。

MySQL拿到一个查询请求之后，先到查询缓存里面看看，之前是不是执行过这条语句，之前执行过的语句及其结果，可能会以K-V对的形式，被直接缓存在内存中。KEY是查询的语句，VALUE是查询的结果。如果能和KEY对上那么就把VALUE返回。如果不在的话就执行和缓存下来。

 

查询缓存弊大于利：

咋一看好像缓存很好，能大大提高速度，但是其实是查询缓存弊大于利，需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

 

为什么呢？

 

查询缓存的失效非常频繁，只要有一个表的更新，这个表上所有的查询缓存都会被清空。所以对于更新压力大的数据库来说，查询缓存命中率会很低，除非是类似于系统配置表这样的静态表，才合适用缓存。所以在8.0之前的按需使用 查询缓存，参数是query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存，而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；

 

3、分析器：

 

如果没有命中缓存就真正执行语句了，首先，要对SQL语句进行解析。

 

分析器主要做两个事情，先做词法分析后做语法分析,词法分析主要做的是根据mysql的关键字进行验证和解析，而语法分析会在词法解析的基础上进一步做表名和字段名称的验证和解析； 

 

分析器先会做词法分析，你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

然后要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

 

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 分析器会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。

 

4、优化器：

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

 

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：

 

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

 

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。

也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

 

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等等，没关系，我会在后面的文章中单独展开说明优化器的内容。

 

 

5、执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

mysql> select * from T where ID=10;

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

 

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

没有索引的：

比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1、调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

2、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。

 

有索引的：

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。我们后面会专门有一篇文章来讲存储引擎的内部机制，里面会有详细的说明。

 

4、了解日志系统：一条SQL更新语句是如何执行的？

 

MySQL 可以恢复到半个月内任意一秒的状态，惊叹的同时，你是不是心中也会不免会好奇，这是怎样做到的呢？

 

你执行语句前要先连接数据库，这是连接器的工作。

前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。

接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

 

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。如果接触 MySQL，那这两个词肯定是绕不过的，我后面的内容里也会不断地和你强调。不过话说回来，redo log 和 binlog 在设计上有很多有意思的地方，这些设计思路也可以用到你自己的程序里。

 

4.1 redo log（重做日志）：

WAL 技术，WAL 的全称是 Write-Ahead Logging：

MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

 MySQL 里如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

 

如果今天赊账的不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间。

与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

 

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

 

4.2 重要的日志模块：binlog

前面我们讲过，MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

我想你肯定会问，为什么会有两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

 

4.3 redo log vs binlog的三点不同

1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

（（物理日志是因为它是真正伴随着物理上的改变，逻辑日志只是记录我要操作的逻辑。））

3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

 

binlog还不能去掉。

一个原因是，redolog只有InnoDB有，别的引擎没有。

另一个原因是，redolog是循环写的，不持久保存，binlog的“归档”这个功能，redolog是不具备的。

 

4.4 执行更新语句的流程：

有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

1、执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

 

 

最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

 

4.5 两阶段提交：

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。要说明这个问题，我们得从文章开头的那个问题说起：

 

4.5.1 怎样让数据库恢复到半个月内任意一秒的状态？

前面我们说过了，binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

1、首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；

2、然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

 

4.5.2 为什么日志需要“两阶段提交”？

 

这里不妨用反证法来进行解释。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

（1）先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。

但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。

然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

（2）先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写（redo写了之后才是真的更新），崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

 

4.5.3 总结：

不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

 

 

4.6 小结：

 

介绍了 MySQL 里面最重要的两个日志，即物理日志 redo log 和逻辑日志 binlog。

 

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

我还跟你介绍了与 MySQL 日志系统密切相关的“两阶段提交”。两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案，即使你不做数据库内核开发，日常开发中也有可能会用到。

 

5、mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

 

 

二、索引

 

1、定义：

索引是一种特殊的文件(MyISAM数据表的索引是一个单独的文件，InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

 

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

 

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

 

2、为什么要索引？

 

如果说没有索引的话，我们从数据库中查询数据需要对数据库进行全表扫描来查询所需的数据，将整张表的数据全部或者分批次加载到内存当中，找到我们需要的数据并返回。这种方式非常的慢，数据量小还可以，数据量大之后就不能用了，所以我们需要避免全表扫描的情况，引入索引机制。

  索引的灵感来自于字典，在字典的检索目录中，我们将文字的关键信息组织起来，比如偏旁和部首，查询的时候根据偏旁或者部首找到对应的页码，就快速找到了我们想要的数据。数据库也一样，将关键信息放在索引中，快速找到数据所在的内存地址来获取数据。

 

3、什么样的信息适合作为索引？

  能把该记录限定到一定范围内的字段，就适合用来作为索引，比如主键、唯一建和其他普通键都可以。索引的设置也需要进行相应的考虑，不是所有的字段作为索引都很高效。

 

4、索引有哪些优缺点？

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

 

5. 索引有哪几种类型？

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

• 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
• 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引：是目前搜索引擎使用的一种关键技术。

• 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

 

6. 索引使用场景（重点）？？（看不懂）

 

where

 

上图中，根据id查询记录，因为id字段仅建立了主键索引，因此此SQL执行可选的索引只有主键索引，如果有多个，最终会选一个较优的作为检索的依据。

-- 增加一个没有建立索引的字段

alter table innodb1 add sex char(1);

-- 按sex检索时可选的索引为null

EXPLAIN SELECT * from innodb1 where sex='男';

 

可以尝试在一个字段未建立索引时，根据该字段查询的效率，然后对该字段建立索引（alter table 表名 add index(字段名)），同样的SQL执行的效率，你会发现查询效率会有明显的提升（数据量越大越明显）。

 

 

order by

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。

但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

索引覆盖？？

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

 

7、数据结构：！！

 

7.1 如果用BST(二分搜索树)

 

树态比较平衡的话，搜索效率可以达到O(logN)，但是有可能会退化成一条链表（这样就是O(N)级别，跟全表扫描一样了），所以我们需要更平衡稳定的树

 

7.2 如果用AVL(平衡二叉搜索树)/红黑树

AVL和红黑树都避免了BST的链表化，保持了稳定性，但是他们的旋转都是对整棵树的调整，需要将树的整体加载到内存中去。内存是装不下大数据量级的树的

 

7.3 如果使用B树（平衡多路查找树）:

 

7.3.1 定义：

在B树中，叶子节点和非叶子节点都存储着关键字信息和数据，查询过程是靠大小比较来进行

 

7.3.2 特点：

所有叶子节点均在同一层；所有节点关键字是按递增次序排列，并遵循左小右大原则；如果一个非叶节点有n个子节点，则该节点的关键字数等于n-1。

 

7.3.3 和AVL/红黑树的区别： 

在AVL或者红黑树中，插入或者删除后不满足条件需要对树进行旋转。而B树维护自己的平衡状态是依靠分裂以及合并的操作

 

7.3.4 缺点：

但是有一个缺陷是如果我们需要遍历数据，就需要从根结点开始往下跨层进行遍历，需要不断地在内外存进行数据交互，磁盘的IO次数也会增加

 

7.4 两种MySQL索引类型（B+树和Hash）-----B+树：

7.4.1 定义：

相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加。

 

7.4.2 优点/特点：

• 效率稳定：B+树必须查找到叶子节点才能拿到命中数据，而B树在非叶子节点也可能命中数据，所以B树的查询效率没有B+树稳定，而B+树的高度一般都比B树低。

• B+树的磁盘读写代价更低：由于非叶子节点不存储数据只存储索引，使得B+树的非叶子节点可以保存更多的索引关键字，而在磁盘IO中，为了满足局部性原理，会给每个节点分配一页的存储容量，这就保证了单词IO中可以有更多的检索信息，减少了查找时的磁盘IO次数。

所以可以支持千万级别数据的快速查找

• B+树的叶子节点使用链表链接：这样如果我们需要进行遍历或者全表扫描的时候可以直接对叶子节点遍历即可，B树则需要从根结点进行层层遍历。所以，B+树可以进行区间查找，B树则无法进行区间查找。

 

7.4.3 B树和B+树

 

区别

• 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

 

使用B树的好处

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

 

使用B+树的好处

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

 

数据库为什么使用B+树而不是B树

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
• B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
• B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
• 增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

 

7.5 两种MySQL索引类型（B+树和Hash）-----Hash：

 

7.5.1 使用场景：

在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快

 

7.5.2 局限性：

仅仅能满足单条数据的查询，比如“=”，“in”，无法进行范围查询

无法避免数据的排序操作

如果一些关键字无法进行一致的哈希运算，就不能用来当做索引

 

7.5.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索引。

 

8、MyISAM和InnoDB存储引擎的实现

参考：https://www.bilibili.com/video/BV1aE41117sk?from=search&seid=15215376023815428628

 

8.1 前置知识点：

 

1、MySQL的表是存在磁盘上的：我们打开MySQL文件夹就可以看到表的内容都存在data文件夹下面了。

 

2、存储引擎是对应表的，每个表可以设置自己的存储引擎。

 

8.2 MyISAM 存储引擎实现：

1、文件：

如果一个表（名为test_myisam）设置引擎为MyISAM：

那么他就有两个文件：

test_myisam.MYI： myisam的index(索引)，保存了他索引的数据结构

test_myisam.MYD：myisam的data，保存他的数据

2、底层实现：

MYI文件：以B+树组织

MYD文件：

3、一条SQL语句怎么执行？select * from where col 1 =49?

自己走一遍流程。

底层的过程：先看col是不是索引，拿到49， 然后去MYI索引文件里面，按照B+树来搜索得到 49对应的地址， 然后拿着地址去到MYD文件里面查找对应的数据。

 

8.3 InnoDB存储引擎实现：

 

1、文件：

一个test_innodb_lock 有两个文件 FRM文件（存储表格结构）和IBD文件（索引和数据文件合在一起）。

 

2、底层实现：

表数据文件本身就是按B+Tree组织的一个索引结构文件。聚集索引：叶节点包含了完整的数据记录，叶子节点从左到右依次递增。InnoDB 的主键索引就是聚集索引， 索引和数据分开存储就是非聚集索引，索引和数据在一起就是聚集索引。

 

为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

 

因为B+树，如果往中间加的话，会有裂开、平衡等一系列操作，开销很大。如果是自增主键的话就会往后面添加就可以了。

 

8.4 MyISAM 和 InnoDB的区别：

 

版本一说法：（精简）

    1、MyISAM不支持事务，而Innodb支持事务。2、Myisam是表级锁，而Innodb是行级锁。3外键支持：mysiam表不支持外键，而InnoDB支持。4、count运算：myisam缓存有表的行数，这种缓存只是表行的总数，where筛选无效。而Innodb没有。

    MyISAM适合：(1)做很多count 的计算；(2)读密集；(3)没有事务。

InnoDB适合：(1)要求事务；(2)写密集（3）高并发

 

版本二说法：（详细）

MyISAM与InnoDB的区别是什么？

1、 存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

 

2、 存储空间

MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。

InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

 

3、 可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

 

4、 事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

 

5、 AUTO_INCREMENT

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

 

6、 表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

 

7、 全文索引

MyISAM：支持 FULLTEXT类型的全文索引

InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

 

8、 表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

 

9、 表的具体行数

MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。

InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

 

10、 CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

 

11、 外键

MyISAM：不支持

InnoDB：支持

 

通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

 

12、存储引擎选择的基本原则

 

采用MyISAM引擎

• R/W > 100:1 且update相对较少
• 并发不高
• 表数据量小
• 硬件资源有限

 

采用InnoDB引擎

• R/W比较小，频繁更新大字段
• 表数据量超过1000万，并发高
• 安全性和可用性要求高

 

 

8.5 什么是聚簇索引？何时使用聚簇索引与非聚簇索引

 

8.5.1 版本一说法：

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

 

8.5.2 版本二说法：

聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序；

非聚簇索引的解释是:索引顺序与数据物理排列顺序无关；

 

MyISAM使用的是非聚簇索引：非聚簇索引的数据表和索引表是分开存储的。主索引和辅助索引几乎是一样的，叶子节点存储的是指向数据的物理地址。

 

Innodb使用的是聚簇索引。聚簇索引的主键索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。

 

  B+树有主键索引和辅助索引两种（一个索引就是一颗B+树）： 主键索引就是按照表中主键的顺序构建一颗B+树，并在叶节点中存放表中的行记录数据，一个表只能有一个主键索引。而辅助索引，叶节点并不存储行记录数据，仅仅是主键。通过辅助索引查找到对应的主键，最后在聚集索引中使用主键获取对应的行记录。（这个叫回表查询）

 

 

8.6 回表查询：

8.6.1 B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

 

8.6.2 非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

 

8.7 InnoDB引擎的4大特性 ？？（太长了，不看，有个印象就好）

一：插入缓冲

二：二次写

三：自适应哈希

四：预读

1.插入缓冲（insert buffer)

插入缓冲（Insert Buffer/Change Buffer）：提升插入性能，change buffering是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效

只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

使用插入缓冲的条件：

* 非聚集索引

* 非唯一索引

Change buffer是作为buffer pool中的一部分存在。Innodb_change_buffering参数缓存所对应的操作：(update会被认为是delete+insert)

innodb_change_buffering，设置的值有：inserts、deletes、purges、changes（inserts和deletes）、all（默认）、none。

all: 默认值，缓存insert, delete, purges操作

none: 不缓存

inserts: 缓存insert操作

deletes: 缓存delete操作

changes: 缓存insert和delete操作

purges: 缓存后台执行的物理删除操作

可以通过参数控制其使用的大小：

innodb_change_buffer_max_size，默认是25%，即缓冲池的1/4。最大可设置为50%。当MySQL实例中有大量的修改操作时，要考虑增大innodb_change_buffer_max_size

 

上面提过在一定频率下进行合并，那所谓的频率是什么条件？

1）辅助索引页被读取到缓冲池中。正常的select先检查Insert Buffer是否有该非聚集索引页存在，若有则合并插入。

2）辅助索引页没有可用空间。空间小于1/32页的大小，则会强制合并操作。

3）Master Thread 每秒和每10秒的合并操作。

2.二次写(double write)

Doublewrite缓存是位于系统表空间的存储区域，用来缓存InnoDB的数据页从innodb buffer pool中flush之后并写入到数据文件之前，所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃，Innodb可以在doublewrite缓存中找到数据页的备份而用来执行crash恢复。数据页写入到doublewrite缓存的动作所需要的IO消耗要小于写入到数据文件的消耗，因为此写入操作会以一次大的连续块的方式写入

在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write

doublewrite组成：

内存中的doublewrite buffer,大小2M。

物理磁盘上共享表空间中连续的128个页，即2个区（extend），大小同样为2M。

对缓冲池的脏页进行刷新时，不是直接写磁盘，而是会通过memcpy()函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite 再分两次，每次1M顺序地写入共享表空间的物理磁盘上，在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer 中的页写入各个 表空间文件中，此时的写入则是离散的。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，innodb可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

3.自适应哈希索引(ahi)

Adaptive Hash index属性使得InnoDB更像是内存数据库。该属性通过innodb_adapitve_hash_index开启，也可以通过—skip-innodb_adaptive_hash_index参数

关闭

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

哈希（hash）是一种非常快的等值查找方法，在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般3-4层，故需要3-4次的查询。

innodb会监控对表上个索引页的查询。如果观察到建立哈希索引可以带来速度提升，则自动建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。

AHI有一个要求，就是对这个页的连续访问模式必须是一样的。

例如对于（a,b）访问模式情况：

where a = xxx

where a = xxx and b = xxx

特点

　　1、无序，没有树高

　　2、降低对二级索引树的频繁访问资源，索引树高<=4，访问索引：访问树、根节点、叶子节点

　　3、自适应

3、缺陷

　　1、hash自适应索引会占用innodb buffer pool；

　　2、自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'，而对于其他查找类型，如范围查找，是不能使用的；

　　3、极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

 

4.预读(read ahead)

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）

为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

线性预读（linear read-ahead）

方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数innodb_read_ahead_threshold，可以控制Innodb执行预读操作的时间。如果一个extent中的被顺序读取的page超过或者等于该参数变量时，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64的任何值，默认值为56，值越高，访问模式检查越严格

例如，如果将值设置为48，则InnoDB只有在顺序访问当前extent中的48个pages时才触发线性预读请求，将下一个extent读到内存中。如果值为8，InnoDB触发异步预读，即使程序段中只有8页被顺序访问。你可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL需要该SUPER权限的命令动态更改该参数。

在没有该变量之前，当访问到extent的最后一个page的时候，Innodb会决定是否将下一个extent放入到buffer pool中。

随机预读（randomread-ahead）

随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中，由于随机预读方式给Innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置innodb_random_read_ahead为ON。

 

9. 索引设计的原则？

1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2. 基数较小的类，索引效果较差，没有必要在此列建立索引
3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

9. 创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

 

10. 创建索引的三种方式，删除索引

 

10.1 创建索引：

第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (

\tid INT auto_increment PRIMARY KEY,

\tfirst_name VARCHAR (16),

\tlast_name VARCHAR (16),

\tid_card VARCHAR (18),

\tinformation text,

\tKEY name (first_name, last_name),

\tFULLTEXT KEY (information),

\tUNIQUE KEY (id_card)

);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

 

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

 

10.2 删除索引：

 

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;

alter table user_index drop KEY id_card;

alter table user_index drop KEY information;

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

 

需要取消自增长再行删除：

alter table user_index

-- 重新定义字段

MODIFY id int,

drop PRIMARY KEY

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

 

10.3 百万级别或以上的数据如何删除：

 

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2. 然后删除其中无用数据（此过程需要不到两分钟）
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

 

 

11. 创建索引时需要注意什么？

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

 

12. 使用索引查询一定能提高查询的性能吗？为什么

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

• 索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索

 

 

13. 联合索引，覆盖索引，聚集索引，辅助索引，前缀索引

参考：https://juejin.im/post/5da5d1966fb9a04e252c94bf

13.1 覆盖索引：

（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

 

什么叫做覆盖索引？

覆盖索引：

指从辅助索引中就能获取到需要的记录，而不需要查找聚簇索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作。

 

解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

 

什么时候用覆盖索引？

　不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。

注：遇到以下情况，执行计划不会选择覆盖查询。

select选择的字段中含有不在 索引 中的字段 ，即索引没有覆盖全部的列。

where条件中不能含有对索引进行like的操作。

 

13.2 聚集索引：

一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致。查询速度贼快，聚集索引的叶子节点上是该行的所有数据 ，数据索引能加快范围查询(聚集索引的顺序和数据存放的逻辑顺序一致)。主键!=聚集索引。

13.3 辅助索引(非聚集索引)：

一个表中可以有多个，叶子节点存放的不是一整行数据，而是键值，叶子节点的索引行中还包含了一个'书签'，这个书签就是指向聚簇索引的一个指针，从而在聚簇索引树中找到一整行数据。

聚集索引与辅助索引的区别：

叶子节点是否存放的为一整行数据

 

13.4 联合索引：

就是由多列组成的的索引。遵循最左前缀规则。对where，order by，group by 都生效。

 

13.5 前缀索引

语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

实操的难度：在于前缀截取的长度。

我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）

 

13.6 最左前缀原则（最左匹配原则）

定义：假设联合索引由列(a,b,c)组成，则一下顺序满足最左前缀规则：a、ab、abc；selece、where、order by 、group by都可以匹配最左前缀。其它情况都不满足最左前缀规则就不会用到联合索引。

顾名思义，就是最左优先，在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

 

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

 

 

三、事务：

（用例子来理解）

 

1、什么是数据库事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

 

 

2、事务的四大特性

 

2.1 介绍ACID：

 

A原子性：

要不都成功要不都失败不能只完成一部分

 

C一致性：

• 保证事务只能把数据库从一个有效（正确）的状态“转移”到另一个有效（正确）的状态。（正确是指是否满足数据库设置的约束）
• 一致性是目的，其他三个属性都是为了保证一致性而存在的

 

I隔离性：

并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库时独立的

 

D持久性：

一个事务被提交之后，改变是持久的，即使数据库发生故障也不会影响到

 

2.2 MySQL 的 ACID 以及是怎么实现的

【MySQL 的 ACID 以及是怎么实现的】.note

 

3、事务并发中常见问题

 

3.1 脏读（dirty read）：

指一个事务读取到其他事务没有提交的数据。//当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。（读取了脏的错误的数据）

 

3.2 幻读(Phantom Read)：

（无中生有）指一个事务内多次根据同一条件查询出来的记录行数不一致。//  在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中就会发现有几列数据是他先前所没有的。

 

如何解决幻读

很明显可重复读的隔离级别没有办法彻底的解决幻读的问题，如果需要解决幻读的话也有两个办法：

• 使用串行化读的隔离级别
• MVCC+next-key locks：next-key locks由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)

 

3.3 不可重复读(Non-repeatable read)：

指一个事务内多次根据同一查询条件查询出来的同一行记录的值不一致。

 

 

4、事务的隔离级别

 

4.1 定义：

• 读未提交（Read Uncommited）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取。

• 已提交读（Read Commited）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。

• 可重复读（Repeatable Read)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20；

• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。串行化隔离级别是最严格的隔离级别，所谓串行化，指的是两个事务t1和t2，最终执行的结果与先执行t1在执行t2或者先执行t2再执行t1的结果一样。

 

可以看出，如果一个事务，使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前，其他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的性能，通常情况下，不会使用这种隔离级别。

 

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

4.2 举例子理解：

读未提交（read uncommitted）：一个事务还没有提交时，它做的变更就能被其他事务看到。

读提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到。

可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

（事务的提交是指事务里的所有操作都正常完成）

 

举例说明：

假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

 

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

四、锁

 

1、对MySQL锁的理解

数据库有并发事务时候会造成数据的不一致，需要锁机制来保证访问的次序。

 

2、锁的类别

2.1 共享锁（读锁）（S锁）：

• 允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

• 又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

 

 

2.2 排他锁（写锁）（X锁）：

• 一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

• 又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

 

 

形象记忆：

用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

 

3、隔离级别与锁的关系/锁怎么来实现隔离级别的

 

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

 

4、按照粒度分为行级锁、表级锁和页级锁

 

4.1 使用的锁是引擎来实现的：MyISAM和InnoDB存储引擎

MyISAM采用表级锁(table-level locking)。

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

BDB引擎支持行级锁、表级锁、页级锁

 

4.2 行级锁（最小）

粒度最小的锁（开销也越大），只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。分为共享锁和排他锁

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

 

4.3 表级锁（最大）

粒度最大的锁，表示对当前操作的整张表加锁。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

 

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

 

4.4 页级锁（折中）

 

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

5、InnoDB的行锁的三种算法

 

5.1 Record Lock记录锁: 

 

锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，而不是记录本身，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。

 

 

5.2 Gap LocK间隙锁:

间隙锁是一种记录行与记录行之间存在空隙或在第一行记录之前或最后一行记录之后产生的锁。间隙锁可能占据的单行，多行或者是空记录。 对索引项之间的“间隙”加锁，锁定记录的范围,不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

 

5.3 Next-key lock：

 锁定索引项本身和索引范围。NK 是一种记录锁和间隙锁的组合锁。既锁住行也锁住间隙。即Record Lock和Gap Lock的结合。可解决幻读问题。

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。

MVCC 不能解决幻读的问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。

Next-Key Lock 就是用来解决幻读的问题的

在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

Next-Key Lock由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)组成。

 

不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引有(1), (3), (5), (8), (11);这4个值，那么该索引可能被Next-Key Locking的范围为（左开右闭 ）：(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)

当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

执行select * from test where xid = 8 for update

Session A执行后会锁住的范围：

(5, 8], (8, 11]

除了锁住8所在的范围，还会锁住下一个范围，所谓Next-Key。

 

5.4 相关知识点：

innodb对于行的查询使用next-key lock

Next-locking keying为了解决Phantom Problem幻读问题

当查询的索引含有唯一属性时，将next-key lock降级为record key

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

 

 

6、三种锁的并发控制机制(悲观锁、乐观锁和MVCC)----悲观锁、乐观锁

6.1 第一种版本说法：

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

 

6.2 第二种版本说法：

怎样实现隔离性,已经有很多人回答过了,原则上无非是两种类型的锁：

 

一种是悲观锁, 即当前事务将所有涉及操作的对象加锁, 操作完成后释放给其它对象使用。为了尽可能提高性能,发明了各种粒度(数据库级/表级行级 .../)种性质(共享锁/排他锁/共享意向锁/排他意向锁/共享排他意.锁. ..)的锁。为了解决死锁问题,又发明了两阶段锁协议/死锁检测等一系列的技术。

 

一种是乐观锁, 即不同的事务可以同时看到同一对象(一般是数据行)的不同历史版本。如果有两个事务同时修改了同一数据行,那么在较晚的事务提交时进行冲突检测。实现也有两种,

• 一种是通过日志UNDO的方式来获取数据行的历史版本,
• 一种是简单地在内存中保存同一数据行的多个历史版本,通过时间戳来区分。

锁也是数据库实现中最复杂的部分之一。

 

6.3 第三种版本说法：

 

锁机制

三种并发控制机制：悲观并发控制、乐观并发控制和多版本并发控制。悲观并发控制其实是最常见的并发控制机制，也就是锁；乐观并发控制其实也有另一个名字：乐观锁. MVCC多版本并发控制机制，可以与前两者中的任意一种机制结合使用，以提高数据库的读性能。

6.3.1 乐观锁：

在访问数据之前，默认不会有其他事务对此数据进行修改，所以先访问数据，然后再查找在此期间是否有事务修改数据。这不是数据库自带的，需要我们自己去实现，一般基于版本去实现。

6.3.2 悲观锁：

按照锁的粒度把数据库锁分为表级锁和行级锁。

表级锁: 对当前操作的整张表加锁,实现简单，加锁快，不死锁，但并发能力低。

行级锁: 只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

Record Lock记录锁: 锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。

Gap LocK间隙锁: 间隙锁是一种记录行与记录行之间存在空隙或在第一行记录之前或最后一行记录之后产生的锁。间隙锁可能占据的单行，多行或者是空记录。 对索引项之间的“间隙”加锁，锁定记录的范围,不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。

Next-key Lock： 锁定索引项本身和索引范围。NK 是一种记录锁和间隙锁的组合锁。既锁住行也锁住间隙。即Record Lock和Gap Lock的结合。可解决幻读问题。

根据是否独占，锁又可以分为共享锁和排他锁。

 

共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。

 

排它锁（(Exclusive lock,简记为X锁)）又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。

 

Innodb同时支持行锁和表锁。但行锁和表锁的同时存在会发生冲突，如A申请了行共享锁，而B再申请表互斥锁。这时B不仅需要查看是否已经存在其他表锁，以及逐个查看是否存在行锁，效率太低。于是又引入了意向锁。

 

意向锁：意向锁是一种表级锁，用来指示接下来的一个事务将要获取的是什么类型的锁（共享还是独占）。

意向锁分为意向共享锁（IS）和意向独占锁（IX），依次表示接下来一个事务将会获得共享锁或者独占锁。

意向共享锁（IS）：事务打算给数据行加共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。而事务B发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

而且，申请意向锁的动作是数据库自动完成的，不需要我们手动申请。

 

7、三种锁的并发控制机制(悲观锁、乐观锁和MVCC)----MVCC

 

多版本并发控制（Multiversion Concurrency Control），是一种提高并发的技术。

 

最早的数据库系统，只有读读之间可以并发，读写、写读、写写都要阻塞。引入MVCC之后，只有写写之间相互阻塞，其他三种操作都可以并行。这样就大幅度提高了InnoDB的并发度。

每一个写操作都会创建一个新版本的数据，读操作会从有限多个版本的数据中挑选一个最合适的结果直接返回；这时读写操作之间的冲突就不再需要被关注，而管理和快速挑选数据的版本就成了MVCC需要解决的主要问题。

 

各数据库中MVCC实现并不统一，MVCC只在读提交 和可重复 两种隔离级别下工作；

对于使用InnoDB存储引擎的表来说，他的聚簇索引记录中都包含两个必要的隐藏列：

trx_id(事务ID)、roll_pointer上个版本指针 ？？

 

每次对记录进行改动，都会把对应的事务id赋值给trx_id隐藏列，也会把旧版本写入到undo日志中；

 

所以在并发情况下，一个记录可能存在多个版本，通过roll_pointer形成一个版本链。MVCC的核心任务就是：判断一下版本链中哪个版本是当前事务可见的。这就有了ReadView的概念，这个ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，命名为m_ids; 根据ReadView的活跃事务id列表和版本链事务id进行比较找出可见的事务ID最大的版本：

1、如果版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。

2、如果版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。

3、被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

MVCC只在读已提交和可重复读这两个隔离机制下运行。这两个隔离机制下MVCC实现方式的区别就在于：读已提交是每次读取数据前都生成一个ReadView；而可重复读，是在第一次读取数据时生成一个ReadView，后序的重复查询就不再生产ReadView了。

 

总结：（背！！！）

MVCC指的就是在使用读提交和重复读这两种隔离级别的事务执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使得不同事务的读写、写读操作并发执行，从而提升系统性能。读提交和重复读这两个隔离级别的一个很大不同就是生产ReadView的时机不同，读提交在每一次进行普通SELECT操作前都会生产一个ReadView，而重复读只在第一次进行普通SELECT操作前生产一个ReadView，之后的查询操作都重复这个ReadView就好了

 

另一种讲解：（还没认真看？？）

 

多版本并发控制（Multiversion Concurrency Control），是一种提高并发的技术。MVCC解决幻读的方法就是利用undo log日志保存数据的多个版本，然后利用事务的id来创建快照，同一个事务中，事务只能看到其它事务在创建快照之前已经提交的修改（不管查询几次）和该事物本身提交的修改。

当前读：指读出的数据是数据库中的最新数据，加了锁的增删改查都是当前读，如update，delete，insert和显式加锁的select（select ... lock in share mode，select ... for update）。

为什么update，delete，insert也是当前读呢？明明是写操作啊。以update为例，在其更新前，实际上会有一个当前读的操作，将数据的最新版本读取出来并进行修改，修改后再更新回数据库中。

快照读：指读出的数据是创建快照前的最新数据，普通的查询语句为快照读：select。

快照读的原理

首先，InnoDB存储引擎在数据库的每行数据后面添加了三个隐藏字段：

DB_TRX_ID：记录最近一次对本行数据进行修改（写操作）的事务ID。

DB_ROLL_PTR：一个回滚指针，指向上一次当前数据行的修改undo log信息。

DB_ROW_ID：随着新的数据行插入而单调递增的一个ID信息。（当InnoDB引擎没有指定主键并且没有唯一非空键的时候，就会调用这个ID信息作为自增聚簇索引键，这个字段和快照读的关系不大。）

当数据库进行了修改后，都会被记录在undo日志当中，这是一种老版本日志，包含两种，一种insert undo日志，记录事务的inset操作，只在回滚的时候需要；另外一种是update undo日志，不仅在回滚时需要，快照读也需要用到。

RR隔离级别下，事务在开启后，第一次执行快照读，会创建一个快照（Read View），将当前系统中其他活跃的事务记录起来，以备快照读使用，此后该事物之后使用的都是此快照，直到事务结束。

RC隔离级别下，事务在开启后，每次执行快照读，快照（Read View）都会被重置，即创建一个新的快照（Read View）。

当事务进行更新的时候，会将操作行的三个隐藏字段进行填充后记录到undo日志当中，如果进行了快照读，那么InnoDB会将该记录行的DB_TRX_ID与Read View中的一些变量进行比较，然后只展现满足条件的数据。

比如事务A开始后，执行普通select语句，创建了快照；之后事务B执行insert语句；然后事务A再执行普通select语句，得到的还是之前B没有insert过的数据，因为这时候A读的数据是符合快照可见性条件的数据。这就防止了部分幻读，此时事务A是快照读。

虽然快照读利用MVCC和历史数据部分避免了幻读在一定程度上解决了幻读的问题，但还是不能完全保证不出现幻读，比如A事务在开启快照读之后，B事务进行了写操作插入了一个新行，然后A事务进行了update操作，此时A事务的update操作覆盖到了B事务刚插入的新行，那么A事务再次快照读就会读到这个新行，产生了幻读。

8、Mysql死锁

8.1 定义：

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

 

8.2 死锁发生之后怎么处理：

处理方式1、等待，直到超时，事务自动回滚。2、发起死锁检测， 回滚一个事务，让其他事务执行。

死锁检测，构建一个以事务为起点，锁为边的有向图，看是否存在环。

 

8.3 预防死锁的方法：

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

4、如果业务处理不好可以用分布式事务锁或者使用乐观锁

 

五、视图、游标、存储过程、触发器

 

1、视图

 

1.1. 为什么要使用视图？什么是视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

 

1.2. 视图有哪些特点？

视图的特点如下:

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• 视图是由基本表(实表)产生的表(虚表)。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新(添加，删除和修改)直接影响基本表。
• 当视图来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

 

1.3 视图的使用场景有哪些？

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

下面是视图的常见使用场景：

• 重用SQL语句；
• 简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；
• 使用表的组成部分而不是整个表；
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

 

 

1.4 视图的优点

1. 查询简单化。视图能简化用户的操作
2. 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
3. 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

 

1.5 视图的缺点

1. 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
2. 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVGSUMMAX等聚合函数的视图。4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

 

2. 游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

 

 

3、存储过程与函数

 

3.1 什么是存储过程？有哪些优缺点？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

3.2 优点

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，减少数据库开发人员的工作量。

 

3.3 缺点

1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

 

3.4 存储过程与函数的区别

　　本质上没区别，执行的本质都一样。

　　只是函数有如：只能返回一个变量的限制。而存储过程可以返回多个。　　

　　函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程要让sql的query 可以执行， 需要把 mysql_real_connect 的最后一个参数设置为CLIENT_MULTI_STATEMENTS。

　　函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少。

　　

　　特性区别如下：

　　1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

　

　　2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

　　3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

　　4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

 

4、触发器

4.1. 什么是触发器？触发器的使用场景有哪些？

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。
• 大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和MyISAM的区别。

4.2. MySQL中都有哪些触发器？

在MySQL数据库中有如下六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

 

 

 

六、常用SQL语句？？

 

1、需要学习的资料

（1）各种SQL语句练习：https://mp.weixin.qq.com/s/XF56o9RNdJkOgQ3fEP4yAA

（2）去做力扣题，懂得怎么设计sql

 

2. 超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

 

3、in与exists的区别

使用上，in 后面的查询返回结果只能有一个字段。而exists没有限制。 本质上： A exists B;exists相当于遍历外面A,看A中数据是否存在于B。而in,相当于将结果集B分解开，用or相连，相当于做多次的查询。 exists相当于查询筛选，in则是多次查询; 1、如果查询的两个表大小相当，那么用in和exists差别不大。 2、如果两个表中一个表大，另一个是表小，那么IN适合于外表大而子查询表小的情况。 3、如果两个表中一个表大，另一个是表小，EXISTS适合于外表小而子查询表大的情况。 in不会使用索引搜索，会全表扫描。

 

 

4、drop、delete与truncate的区别

三者都表示删除，但是三者有一些差别：

	Delete	Truncate	Drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

 

 

七、SQL优化：

 

1、 如何避免全表扫描？

    1、where字句别用or链接，可以union all.

2、in和not in也慎用，可以between and.

3.避免对字段进行null值判断。

4,where字句别用!=和<>操作符

5、别用以通配符开头的like的查询。

6、别在where子句对字段进行表达式操作和函数操作。 

7任何地方都不要使用 select * from t。

8、尽量使用数字型字段。

9、复合索引尽量满足最左前缀原则。

10,在查找唯一一条数据的时候，使用limit

11 类型不一致会导致失效，例如字符串不加单引号会导致索引失效。

 

2、索引优化：

1、根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边

2、模糊查询以%为开始的查询，只能使用全文索引来进行优化。

3、使用短索引。对串列进行索引，如果可能应该指定一个前缀长度。

 

3、数据量过大：

    1、查询时限定数据范围。2、读写分离，主写从读。3、垂直分区4、水平分区.

 

 

八、数据库优化（还没有看。。。）

 

1、MySQL的复制原理以及流程

 

2、读写分离有哪些解决方案？