MySQL面试题

隔离级别与锁的关系

详情参考这篇MVCC解读
知道了undo 日志版本链和MVCC之后，我们看下四个隔离级别是怎么实现的。

读未提交，每次读到的都是最新的数据，也不管数据行所在的事务是否提交。实现也很简单，只需要每次都读 undo 日志版本链的链表头（最新的快照）就行了。

与读未提交不同，读提交和可重复读隔离级别都是基于 MVCC 的 read view 实现的，反过来说, MVCC 也只会出现在这两个隔离级别里。

读已提交隔离级别，每次执行普通 select，都会重新生成一个新的 read view，然后拿着这个最新的 read view到某行数据的版本链上挨个遍历，找到第一个合适的数据。这样就能做到每次都读到其他事务最新已提交的数据。

可重复读隔离级别下的事务只会在第一次执行普通 select时生成read view，后续不管执行几次普通 select，都会复用这个 read view。这样就能保持每次读的时候都是在同一标准下进行读取，那读到的数据也会是一样的。

串行化目的就是让并发事务看起来就像单线程执行一样，那实现也很简单，和读未提交隔离级别一样，串行化隔离界别下事务只读 undo 日志链的链表头，也就是最新版本的快照，并且就算是普通 select，也会在版本链的最新快照上加入读锁。这样其他事务想写，也得等这个读锁释放掉才行。所有对这行数据进行操作的事务，都老老实实地阻塞等待加锁，一个接一个进行处理，从效果上看就跟单线程处理一样。

实践中如何优化 MySQL？

详情参考这篇Mysql数据库查询好慢怎么优化？
最好是按照以下顺序优化：

SQL 语句及索引的优化
数据库表结构的优化
系统配置的优化
硬件的优化

优化子查询的方法

使用连接（JOIN）替代子查询：将子查询转换为连接操作，通常可以提高查询性能。连接操作在某些情况下比子查询更有效率，尤其是在连接的表中有适当的索引时。
使用 EXISTS 或 NOT EXISTS 替代 IN 或 NOT IN：在某些情况下，使用 EXISTS 或 NOT EXISTS 运算符可以比使用 IN 或 NOT IN 子查询更高效。EXISTS 只需找到一条匹配记录即可返回结果，而 IN 子查询需要先执行完整的子查询。
使用 JOIN 和聚合函数替代子查询：在某些情况下，可以通过使用 JOIN 和聚合函数来替代子查询。例如，可以使用 MAX()、MIN()、AVG() 等聚合函数来获取子查询结果中的最大、最小、平均值等。
优化子查询的索引：确保子查询所涉及的表有适当的索引可以显著提高性能。分析查询执行计划，并确保索引覆盖了子查询所需的列。
限制子查询的结果集大小：在可能的情况下，尽量限制子查询返回的结果集大小，以减少查询的复杂度和执行时间。

前缀索引是什么？做什么用的？

前缀索引是一种索引类型，用于在数据库中对文本字段的前几个字符进行索引，而不是对整个字段进行索引。它仅索引字段值的前缀部分，而不是整个字段值。这种索引类型常用于对长文本字段进行索引，以节省索引空间和提高查询性能。
好处：

节省索引空间：对于较长的文本字段，完整地索引每个字符可能会占用大量的索引空间。使用前缀索引可以显著减少索引占用的存储空间，从而降低数据库的存储成本。
提高查询性能：在某些情况下，只需对文本字段的前几个字符进行索引就足够支持查询需求。使用前缀索引可以降低索引的复杂度，加快查询的执行速度。
适用于模糊查询：前缀索引通常与模糊查询结合使用，以支持对文本字段的模糊匹配。例如，在搜索引擎中，用户可能只输入了关键词的前几个字符，使用前缀索引可以加速搜索结果的检索。

缺点：

信息损失：由于只索引了字段值的前几个字符，因此可能会丢失一些信息。这可能导致索引的精度降低，可能会影响查询的准确性。
适用场景限制：前缀索引通常适用于对文本字段进行简单的前缀匹配查询，对于需要精确匹配或者基于字段的其他部分进行查询的情况可能不适用。
索引长度选择：在创建前缀索引时，需要选择合适的前缀长度。选择过长的前缀长度可能会导致索引失效，选择过短的前缀长度可能会降低查询性能。

MySQL 5.6 和 MySQL 5.7 对索引做了哪些优化？

MySQL5.6 引入了索引下推优化，默认是开启的。

例子：user 表中（a,b,c）构成一个索引。

select * from user where a='23' and b like '%eqw%' and c like 'dasd'。

如果没有索引下推，则 MySQL 会通过 a='23' 先查询出一个对应的数据。然后返回到 MySQL 服务端。MySQL 服务端再基于两个 like 模糊查询来校验 and 查询出的数据是否符合条件。这个过程就设计到回表操作。
如果使用了索引下推技术，则 MySQL 会首先返回条件 a='23'的数据的索引，然后根据模糊查询的条件来校验索引行数据是否符合条件，如果符合条件，则直接根据索引来定位对应的数据，如果不符合直接 reject 掉。因此，有了索引下推优化，可以在有 like 条件的情况下，减少回表的次数。这就好比你拿到了书的目录页，直接在目录页上找到了你要的内容，而不需要翻开书页。

MySQL 有关权限的表有哪几个呢？

MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 MySQL 数据库里，由 MySQL_install_db 脚本初始化。这些权限表分别 user，db，table_priv， columns_priv 和 host。

user 权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。 2、 db 权限表：记录各个帐号在各个数据库上的操作权限。

table_priv 权限表：记录数据表级的操作权限。

columns_priv 权限表：记录数据列级的操作权限。

host 权限表：配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和 REVOKE语句的影响。

MySQL 中都有哪些触发器？

MySQ- 数据库中有六种触发器：

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

大表怎么优化？

当 MySQL 单表记录数过大时，数据库的 CRUD 性能会明显下降，一些常见的优化措施如下：

限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；
缓存：使用 MySQL 的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存。（尽量别用mysql的缓存，它在8.0版本已经去掉了，真没啥用）

分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

详情可以参考分库分表是什么
主要有垂直分表、水平分表

垂直分表

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

适用场景

如果一个表中某些列常用，另外一些列不常用
可以使数据行变小，一个数据页能存储更多数据，查询时减少 I/O 次数

缺点

有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
对于应用层来说，逻辑算法增加开发成本
管理冗余列，查询所有数据需要 join 操作

水平分表

表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数。

适用场景

表中的数据本身就有独立性，例如表中分表记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用。
需要把数据存放在多个介质上。

水平切分的缺点

给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需 UNION 操作。 - 在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数。

水平分表的常见方案

客户端代理：

分片逻辑在应用端，封装在 jar 包中，通过修改或者封装 JDBC 层来实现。当当网的 Sharding-JDBC 、阿里的 TDDL是两种比较常用的实现。

中间件代理：

在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。** 我们现在谈的 Mycat** 、360 的 Atlas、网易的 DDB 等等都是这种架构的实现。

分库分表后面临的问题

事务支持

分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库 join

只要是进行切分，跨节点 Join 的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的 id,根据这些 id 发起第二次请求得到关联数据。 数据迁移，容量规划，扩容等问题

来自淘宝综合业务平台团队，它利用对 2 的倍数取余具有向前兼容的特性（如对 4 取余得 1 的数对 2 取余也是 1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了 Sharding 扩容的难度。

ID 问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的 ID 无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得 ID,以便进行 SQL 路由、一些常见的主键生成策略，比如UUID是最简单的方案，但是缺点也是非常明显的。由于 UUID 非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。 Twitter 的分布式自增 ID 算法 Snowflake 在分布式系统中，需要生成全局 UID 的场合还是比较多的，twitter 的 snowflake 解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间 41 位机器 ID 10 位毫秒内序列 12 位。

跨分片的排序分页问题

一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如下图所示：

B+ Tree 索引和 Hash 索引区别？

hash 索引适合等值查询，但是无法进行范围查询。
hash 索引没办法利用索引完成排序。
hash 索引不支持多列联合索引的最左匹配规则。
如果有大量重复健值得情况下，hash 索引的效率会很低，因为哈希碰撞问题。

数据库索引的原理，为什么要用 B+树，为什么不用二叉树？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是 B树，而偏偏是 B+ 树呢？

为什么不是一般二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。 那为什么不是 B 树而是 B+树呢？

1）B+树非叶子节点上是不存储数据的，仅存储键值，而 B树节点中不仅存储键值，也会存储数据。innodb 中页的默认大小是 16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数有会再次减少，数据查询的效率也会更快。
2）B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

据库三大范式是什么

第一范式：每个列都不可以再拆分。可以将第一范式理解为数据库表的每个“格子”都应该是单一值，而不是多个值的集合或者组合。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。可以将第二范式理解为消除表中的冗余数据，确保每个数据只存储在一个地方，避免数据的重复。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

有哪些常见的，为了性能，违反数据库三大范式的设计？

反规范化（Denormalization）：

在关系数据库中，通常会将数据分解成多个表，以减少数据的冗余和提高数据的一致性。但是，在某些情况下，为了提高查询性能，可以将数据冗余存储在一个表中，以减少关联操作，这就是反规范化。
例如，在某个频繁查询的场景中，可以将相关联的数据合并到一个表中，避免频繁的表连接操作。在反规范化中，通常会将非主键属性冗余存储在同一个表中，从而导致数据的冗余，违反了第三范式。

超范式设计：

超范式设计是指将多个关联的数据合并到一个表中，违反了第二范式。这样做可以避免频繁的关联操作，提高查询性能。
例如，在某些特定的查询场景中，将多个实体关联的属性合并到一个表中，可以减少表连接的次数，提高查询效率。在超范式设计中，将多个关联的实体属性合并到一个表中，导致非主键属性部分依赖于候选键，违反了第二范式的要求

冗余数据存储：

有时为了提高查询性能，可以在不同的表中存储相同的数据，避免频繁的关联操作。
例如，在某个需要频繁查询订单及其相关客户信息的场景中，可以将客户信息冗余存储在订单表中，避免每次查询都需要关联客户表。将相同的数据存储在多个表中，可能导致数据的不一致性，违反了第一范式的要求。同时，由于数据的冗余存储，可能导致表中的非主键属性部分依赖于候选键，也违反了第二范式的要求。

分区键冗余：

在分区表设计中，有时候为了提高查询性能，可以在表中增加分区键的冗余存储。这样可以避免频繁的分区查找操作。
例如，在某个按照时间范围进行分区的表中，可以在表中额外存储时间信息的冗余，以避免每次查询都需要根据时间范围进行分区查找。在表中增加分区键的冗余存储，可能导致数据的不一致性，违反了第一范式的要求。同时，由于数据的冗余存储，可能导致表中的非主键属性部分依赖于候选键，也违反了第二范式的要求。

MySQL 有关权限的表都有哪几个？

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

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

MySQL 的 Binlog 有有几种录入格式？分别有什么区别？

有三种格式，statement，row和 mixed。

statement 模式下，每一条会修改数据的 sql 都会记录在 binlog 中。不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能。由于 sql 的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row级别下，不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如 alter table)，因此这种模式的文件保存的信息太多，日志量太大。
mixed，一种折中的方案，普通操作使用 statement 记录，当无法使用 statement 的时候使用 row。

MySQL 存储引擎 MyISAM 与 InnoDB 区别

锁粒度方面：由于锁粒度不同，InnoDB比 MyISAM支持更高的并发;InnoDB 的锁粒度为行锁、MyISAM的锁粒度为表锁、行锁需要对每一行进行加锁，所以锁的开销更大，但是能解决脏读和不可重复读的问题，相对来说也更容易发生死锁
可恢复性上：由于 InnoDB是有事务日志的，所以在产生由于数据库崩溃等条件后，可以根据日志文件进行恢复。而 MyISAM 则没有事务日志。
查询性能上:MylSAM要优于 InnoDB因为 InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行;而 MyISAM 可以直接定位到数据所在的内存地址，可以直接找到数据。
表结构文件上:MyISAM的表结构文件包括:frm(表结构定义),.MYI(索引),.MYD(数据);而 InnoDB的表数据文件为:ibd 和 frm(表结构定义)。

MyISAM 索引与 InnoDB 索引的区别？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B树及其变种 B+树。

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

索引有哪些优缺点？

索引的优点

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

索引的缺点

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

索引有哪几种类型？

主键索引:

数据列不允许重复，不允许为 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);创建全文索引。

MySQL 中有哪几种锁？

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

SQL标准定义的四个隔离级别为：

read uncommited：读到未提交数据
read committed：脏读，不可重复读
repeatable read：可重读
serializable：串行事物

char 和 varchar 的区别？

char 和 varchar 类型在存储和检索方面有所不同
char 列长度固定为创建表时声明的长度，长度值范围是1 到255
当 char 值被存储时，它们被用空格填充到特定长度，检索 char 值时需删除尾随空格。

主键和候选键有什么区别？

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

如何在 Unix 和 MySQL 时间戳之间进行转换？

UNIX_TIMESTAMP是从 Mysql时间戳转换为 Unix 时间戳的命令 FROM_UNIXTIME 是从 Unix 时间戳转换为 Mysql时间戳的命令。

MyISAM 表类型将在哪里存储，并且还提供其存储格式？

每个 MyISAM 表格以三种格式存储在磁盘上：

“.frm”文件存储表定义
数据文件具有“.MYD”（MYData）扩展名
索引文件具有“.MYI”（MYIndex）扩展名

MySQL 里记录货币用什么字段类型好

NUMERIC和 DECIMAL类型被 Mysql实现为同样的类型，这在 SQL92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。例如：

salary DECIMAL(9,2)

在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。因此，在这种情况下，能被存储在 salary 列中的值的范围是从-9999999.99 到9999999.99。

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

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

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

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的 INSERT，DELETE，UPDATE 将为此多付出4，5 次的磁盘 I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索

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

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

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

什么是最左前缀原则？什么是最左匹配原则

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，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 的查询优化器会帮你优化成索引可以识别的形式。

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

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

MySQL 连接器

首先需要在 MySQL客户端登陆才能使用，所以需要个连接器来连接用户和 MySQL数据库，我们一般是使用

mysql-u 用户名-p 密码

来进行 MySQL登陆，和服务端建立连接。在完成 TCP握手后，连接器会根据你输入的用户名和密码验证你的登录身份。如果用户名或者密码错误，MySQL就会提示 Access denied for user，来结束执行。如果登录成功后，MySQL会根据权限表中的记录来判定你的权限。

MySQL 查询缓存

连接完成后，你就可以执行 SQL语句了，这行逻辑就会来到第二步:查询缓存。 MySQL在得到一个执行请求后，会首先去查询缓存中查找，是否执行过这条 SQL语句，之前执行过的语句以及结果会以 key-value 对的形式，被直接放在内存中。key 是查询语句，value 是查询的结果。

如果通过 key 能够查找到这条 SQL语句，就直接妾返回 SQL的执行结果。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果就会被放入查询缓存中。

可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，效率会很高。

MySQL 分析器

如果没有命中查询，就开始执行真正的 SQL语句。

首先，MySQL会根据你写的 SQL语句进行解析，分析器会先做词法分析，你

写的 SQL就是由多个字符串和空格组成的一条 SQL语句，MySQL需要识别出里面的字符串是什么，代表什么。

然后进行语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL语句是否满足 MySQL语法。如果 SQL语句不正确，就会提示 You have an error in your SQL syntax。

MySQL 优化器

经过分析器的词法分析和语法分析后，你这条 SQL就合法了，MySQL就知道你要做什么了。但是在执行前，还需要进行优化器的处理，优化器会判断你使用了哪种索引，使用了何种连接，优化器的作用就是确定效率最高的执行方案。

MySQL 执行器

MySQL通过分析器知道了你的 SQL语句是否合法，你想要做什么操作，通过优化器知道了该怎么做效率最高，然后就进入了执行阶段，开始执行这条 SQL语句在执行阶段，MySQL首先会判断你有没有执行这条语句的权限，没有权限的话，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。对于有索引的表，执行的逻辑也差不多。

什么是临时表，何时删除临时表？

什么是临时表?MySQL在执行 SQL语句的过程中通常会临时创建一些存储中间结果集的表，临时表只对当前连接可见，在连接关闭时，临时表会被删除并释放所有表空间。

临时表分为两种:一种是内存临时表，一种是磁盘临时表，什么区别呢?内存临时表使用的是 MEMORY存储引擎，而临时表采用的是 MylSAM 存储引擎。

MySQL会在下面这几种情况产生临时表。

使用 UNION查询:UNION有两种，一种是 UNION，一种是 UNION ALL，它们都用于联合查询;区别是使用 UNION会去掉两个表中的重复数据，相当于对结果集做了一下去重(distinct)。使用 UNIONALL，则不会排重，返回所有的行。使用 UNION查询会产生临时表。
使用 TEMPTABLE算法或者是 UNION查询中的视图。TEMPTABLE算法是一种创建临时表的算法，它是将结果放置到临时表中，意味这要 MySQL要先创建好一个临时表，然后将结果放到临时表中去，然后再使用这个临时表进行相应的查询。
ORDER BY和 GROUPBY的子句不一样时也会产生临时表。
DISTINCT 查询并且加上 ORDER BY时;
SQL中用到 SQL_SMALL_RESULT选项时;如果查询结果比较小的时候，可以加上 SQL SMALL RESULT来优化，产生临时表
FROM中的子查询;
EXPLAIN 查看执行计划结果的 Extra 列中，如果使用 Using Temporary 就表示会用到临时表。

谈谈 SQL 优化的经验

查询语句无论是使用哪种判断条件等于、小于、大于，WHERE 左侧的条件查询字段不要使用函数或者表达式
使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explainsql 来分析这条 sql 语句，这样方便我们分析，进行优化。
当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1。不要直接使用 SELECT*，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT"使用的是全表扫描，也就是 type =all 。
为每一张表设置一个 ID属性。
避免在 MHERE 字句中对字段进行 NULL
判断避免在 WHERE中使用!或>操作符
使用 BETWEEN AND 替代 IN
为搜索字段创建索引
选择正确的存储引擎，InnoDB、MyISAM、MEMORY等
使用 LIKE%abc%不会走索引，而使用 LIKE abc%会走索引。
对于枚举类型的字段(即有固定罗列值的字段)，建议使用 ENUM 而不是 VARCHAR，如性别、星期、类型、类别等。
拆分大的 DELETE或 INSERT 语句
选择合适的字段类型，选择标准是尽可能小、尽可能定长、尽可能使用整数。
字段设计尽可能使用 NOT NULL
进行水平切割或者垂直分割

什么叫外链接？

外连接分为三种，分别是是左外连接(LEFT OUTER J0IN 或 LEFT JOIN 右外连接(RIGHT OUTER JOIN 或 RIC GHT JOIN、全外连接(FULL OUTER JOIN 或 FULLJOIN)。

左外连接:又称为左连接，这种连接方式会显示左表不符合条件的数据行，右边不符合条件的数据行直接显示 NULL。

右外连接:也被称为右连接，他与左连接相对，这种连接方式会显示右表不符合条件的数据行，左表不符合条件的数据行直接显示 NULL。

什么叫内链接？

结合两个表中相同的字段，返回关联字段相符的记录就是内链接。

使用 union 和 union all 时需要注意些什么？

通过 union 连接的 SQL分别单独取出的列数必须相同。

使用 union 时，多个相等的行将会被合并，由于合升比较耗时，一般不直接使用 union 进行合并，而是通常采用 union all 进行合并。

MyISAM 存储引擎的特点

在5.1 版本之前，MyISAM是 MySQL的默认存储引擎，MylSAM并发性比较差，使用的场景比较少主要特点是:

不支持事务操作，ACID的特性也就不存在了，这一设计是为了性能和效率考虑的，
不支持外键操作，如果强行增加外键，MySQL不会报错，只不过外键不起作用。
MyISAM 默认的锁粒度是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易发生死锁的情况。
MyISAM会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是 frm(存储表定义)、MYD(MYData，存储数据)、MYI(MyIndex，存储索引)。这里需要特别注意的是 MyISAM只缓存索引文件，并不缓存数据文件。
MyISAM支持的索引类型有全局索引(Full-Text)、B-Tree 索引、R-Tree 索引
- Full-Text 索引:它的出现是为了解决针对文本的模糊查询效率较低的问题。
- B-Tree 索引:所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节点
- R-Tree 索引:它的存储方式和 B-Tree 索引有一些区别，主要设计用于存储空间和多维数据的字段做索引目前的 MySQL版本仅支持 geometry 类型的字段作索引，相对于 BTREE,RTREE的优势在于范围查找。
数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难以恢复。
增删改查性能方面:SELECT性能较高，适用于查询较多的情况

InnoDB 存储引擎的特点

自从 MySQL5.1之后，默认的存储引擎变成了 InnoDB存储引擎，相对于

MylSAM，InnoDB 存储引擎有了较大的改变，它的主要特点是

支持事务操作，具有事务 ACID隔离特性，默认的隔离级别是可重复读(repetable-read)、通过 MVCC(并发版本控制)来实现的。能够解决脏读和不可重复读的问题。 InnoDB 支持外键操作。
InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。
和 MyISAM一样的是，InnoDB存储引擎也有 frm 文件存储表结构定义，但是不同的是，InnoDB的表数据与索引数据是存储在一起的，都位于 B+数的叶子节点上，而 MylSAM的表数据和索引数据是分开的。
InnoDB有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。
InnoDB和 MylSAM支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。
增删改查性能方面，果执行大量的增删改操作，推荐使用 InnoDB存储引擎，它在删除操作时是对行删除，不会重建表。

Mysql高可用方案有哪些?

Mysql高可用方案包括:

主从复制方案

这是MySQL自身提供的一种高可用解决方案，数据同步方法采用的是MySQL replication技术。MySQL replication就是从服务器到主服务器拉取二进制日志文件，然后再将日志文件解析成相应的SQL在从服务器上重新执行一遍主服务器的操作，通过这种方式保证数据的一致性。为了达到更高的可用性，在实际的应用环境中，一般都是采用MySQL replication技术配合高可用集群软件keepalived来实现自动failover，这种方式可以实现95.000%的SLA。

MMM/MHA高可用方案

MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中，典型的应用是双主多从架构，通过MySQL replication技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现MySQL的高可用。

Heartbeat/SAN高可用方案

在这个方案中，处理failover的方式是高可用集群软件Heartbeat，它监控和管理各个节点间连接的网络，并监控集群服务，当节点出现故障或者服务不可用时，自动在其他节点启动集群服务。在数据共享方面，通过SAN（Storage Area Network）存储来共享数据，这种方案可以实现99.990%的SLA。

Heartbeat/DRBD高可用方案

这个方案处理failover的方式上依旧采用Heartbeat，不同的是，在数据共享方面，采用了基于块级别的数据同步软件DRBD来实现。DRBD是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和SAN网络不同，它并不共享存储，而是通过服务器之间的网络复制数据。

NDB CLUSTER高可用方案

国内用NDB集群的公司非常少，貌似有些银行有用。NDB集群不需要依赖第三方组件，全部都使用官方组件，能保证数据的一致性，某个数据节点挂掉，其他数据节点依然可以提供服务，管理节点需要做冗余以防挂掉。缺点是：管理和配置都很复杂，而且某些SQL语句例如join语句需要避免。