首先引入一个问题。
有个最简单的表，表里只有一个 ID 字段，在执行下面这个查询语句时：
select * from T where ID = 10；
这条语句在 MySQL 内部的执行过程是怎样的？
下面引用林晓斌大佬给出的 MySQL 的基本架构示意图，从中可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。
大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括 连接器、查询缓存、分析器、优化器、执行器 等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持前面说过的 InnoDB 还有 MyISAM、Memory 等多个存储引擎。
连接器是msyql server层的第一个模块，它负责跟客户端 建立连接、获取权限、维持和管理连接。
连接命令一般是这么写的：
mysql -h$ip -P$port -u$user -p
连接命令中的 mysql 就是客户端工具，客户端和服务器端的连接使用的是TCP协议，在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。
这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
使用
show processlist
可以查看系统所有连接的信息。
其中 Command 列表示连接的状态。连接完成后，如果你没有后续的动作，这个连接就处于空闲状态（Sleep）。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。
但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
解决方案有两种：
连接建立完成，MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果对应地以 key-value 对的形式，直接缓存在内存中的引用表里，key是一个哈希值引用，这个哈希值包括了以下因素，即查询语句本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。value是查询的结果。
如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
但是大多数情况下不建议使用查询缓存，因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你好不容易花费了些时间空间把结果存起来，还没使用，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。
除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
MySQL 考虑到这点，提供了这种“按需使用”的方式：将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：
select SQL_CACHE * from T where ID = 10；
有关查询缓存的配置如下所示：
缓存命中率可以通过这个公式：Qcache_hits/(Qcache_hits + Com_select) 来计算。
需要注意的是以下几点：
如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。它通过关键字将SQL语句进行解析，并生成对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。
例子中，MySQL 从输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
做完了这些识别以后，就要做“语法分析”，也叫“预处理”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。如果你的语句不对，就会收到“++You have an error in your SQL syntax++”的错误提醒。
经过了分析器，MySQL 就知道你要做什么了。但是在开始执行之前，还要先经过优化器的处理。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是将解析树转化成这其中最好的执行计划。
例如在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
举个栗子：
select * from t1 join t2 using(ID) where t1.c = 10 and t2.d = 20;
执行逻辑可以是：
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。它会根据优化规则对关系表达式进行转换，这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式，同时原有表达式也会保留，经过一系列转换后会生成多个执行计划，然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost，从中挑选Cost最小的执行计划。由上可知，CBO中有两个依赖：统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划。
有关优化器的原理十分复杂，有时间的话我再搬运上来。
MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示。
ERROR 1142 (42000): SELECT command denied to user ‘b‘@‘localhost‘ for table ‘T‘
如果有权限，就打开表继续执行。打开表的时候，优化器就会根据表的引擎定义，去使用这个引擎提供的接口，根据上面优化后的执行计划来完成整个查询。
比如开始那个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：
至此，这个语句就执行完成了
对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。
你可以在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。但在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。
数据库底层软件组织，是用于存储、处理和保护数据的核心服务。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。
在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎。
现在最常用的存储引擎是 InnoDB，它从 MySQL5.5版本开始成为了默认存储引擎。如果要想查看数据库默认使用哪个引擎，可以通过使用命令查看：
SHOW VARIABLES LIKE ‘storage_engine‘;
如果要使用别的存储引擎，可以在建表的时候指定，比如在 create table 语句中使用 engine = memory。如果要查看mysql支持的引擎，可以使用命令：
SHOW ENGINES;
以下是Mysql中几种常见的存储引擎。
在 MySQL 5.5 之前的版本，MyISAM是默认的存储引擎。它是MySQL对 ISAM 的一种扩展格式。
ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到++数据库被查询的次数要远大于更新的次数++ 。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。
MyISAM则提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但 MyISAM 不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。正是由于 MyISAM 引擎的缘故，即使 MySQL 支持事务已经很长时间了，在很多人的概念中 MySQL 还是非事务型的数据库。
尽管 MyISAM 不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小，可以忍受 repair （修复）操作，则依然可以继续使用 MyISAM
MyISAM 会将表存储在两个文件中：
MyISAM 表可以包含动态或者静态行。 MySQL 会根据表的定义来决定采用何种格式。 MyISAM 表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。
在 MySQL 5.0 中，MyISAM 表如果是变长行，则默认配置只能处理 256TB 的数据，因为指向数据记录的指证长度是 6 个字节。而在更早的 MySQL 版本中，指针的默认长度都支持 8 字节的指针。要改变 MyISAM 表指针的长度，可以通过修改表的 MAX_ROWS 和 AVG_ROW_LENGTH 选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引，这可能要很长的时间才能完成。
作为 MySQL 最早的存储引擎之一，MyISAM 有一些已经开发出来很多年的特性，可以满足用户的实际需求。
加锁与并发
MyISAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表添加排它锁。但是在表有读取查询时， 也可以往表中插入新的记录。
修复
对于 MyISAM 表，MySQL 可以手工或者自动执行检查和修复操作，但这里说的修复和事务修复以及崩溃修复是不同的概念。执行表的修复可能会丢失一部分数据，而且修复操作是非常慢的。
索引特性
对于 MyISAM 表，即使是 BLOB 和 TEXT 等长字段，也可以基于其前 500 个字符创建索引。MyISAM 也支持全文索引，这是一种基于分词创建的索引，可以支持复制的查询。
延迟更新索引键
创建 MyISAM 表的时候，如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（in_memory key buffer）,只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键的特性，可以在全局设置，也可以为单个表设置。
如果表在创建并导入数据以后，不会在进行修改操作，那么这样的表或者适合采用 MyISAM 压缩表。
可以使用 myisampack 对 MyISAM 表进行压缩。压缩表是不能进行修改的。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘 I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。
MyISAM 引擎设计十分简单，数据以紧密格式存储，所以在某些场景下的性能很好。MyISAM 有一些服务器级别的性能扩展限制。但是 MyISAM 最典型的的性能问题还是 表锁的问题。
是目前MySQL默认的事务型引擎，使用非常广泛，极擅长处理短期事务，具有自动崩溃恢复的特性，在日常开发中，一般都要求使用该引擎。
整体分为三层：
MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。
每个基于memory存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型。该文件只存储表的结构，而其数据文件，都是存储在内存中的，这样有利于对数据的快速的处理，提高整个表的处理效率。
值得注意的是：服务器需要有足够的内存来维持memory存储引擎的表的使用。如果不需要了，可以释放这些内存，甚至可以删除不需要的表。
使用场景：
注意，MEMORY用到的很少，因为Memory表的所有数据都是存储在内存上的，如果内存出现异常会影响到数据的完整性。如果重启或者关机，所有数据都会消失，因此，基于MEMORY的表的生命周期很短，一般是一次性的。
MySQL还有一些其他特殊用途的引擎，有些可能不再支持，具体支持情况参考数据库各个版本支持的引擎。
Archive引擎支持是Insert，Select操作，现在支持索引，Archive引擎会缓存所有的写，并利用zlib对写入行进行压缩，所以比MyISAM表的磁盘IO更少。但是在每次Select查询都需要执行全表扫描。所以在Archive适合日志和数据采集应用。这类应用在分析时往往需要全表扫描忙活着更快的Insert操作场景中也可以使用。
Archive引擎支持行级锁和专用的缓存区，所以可以实现高并发写入，在查询开始到返回表存在的所有行数之前，Archive会阻止其他Select执行，用来实现一致性读。另外也实现了批量写入结束前批量写入数据对读操作不可见，这种机制模仿了事务和MVCC的特性，但是Archive不是一个事务型引擎，而是针对高写入压缩做了优化的简单引擎。
Blackhole没有实现任何存储机制，它会舍弃所有写入数据，不做任何保存，但是服务器会记录Blackhole表的日志，用于复制数据到备库，或者只是简单的记录到日志，这种特殊的存储引擎可以在一些特俗的复制架构和日志审核时发挥作用。但是不推荐。
CSV引擎可以将普通的CSV文件作为MySQL表来处理，但是这种表不支持索引，CSV可以在数据库运行时拷贝或者拷出文件，可以将Excel等电子表格中的数据存储未CSV文件，然后复制到MySQL中，就能在MySQL中打开使用。同样，如果将数据写入到一个CSV引擎表，其他外部程序也可以从表的数据文件中读取CSV的数据。因此CSV可以作为数据交换机制。非常好用。
Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如MicrosoftSQLServer和Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
Merge引擎是MyISAM引擎的一个变种。Merge表是由多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃
NDB集群存储引擎，作为SQL和NDB原生协议之间的接口。MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（MySQLCluster）。
MySQL 5.5新增一个存储引擎：命名PERFORMANCE_SCHEMA ，主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
performance_schema提供以下功能：
作为一个常考的面试题，有必要拿过来记一下。
区别：
原文：https://www.cnblogs.com/zohnn/p/14638468.html