MySQL架构

MySQL体系结构

mysqld是MySQL服务器进程，分为服务层(Server)和存储引擎层。

服务层包含连接器、查询缓存、分析器、优化器、执行器。 服务层为客户端提供交互，接收请求、返回响应。 存储引擎层则进行数据的存储和读取，提供了读写接口。

MySQL存储引擎层是插拔式的。在存储引擎层有一层接口，定义了读写方法，各大存储引擎只需要实现接口，之后就可以调用不同的读写方法来进行读写操作。

一条SQL语句是怎么执行的？

1、客户端通过TCP三次握手连接到MySQL服务器。然后给服务层的连接器发送SQL语句。

2、连接器会查看它是什么类型的语句，如果是查询语句DQL，就去查缓存，如果命中了结果，就直接返回给客户端。反之，则发给分析器。分析器会进行词法分析，构建语法树，再进行语法分析，知道这条SQL要做什么。

3、之后发给优化器，优化器会选择最优执行方案，然后给执行器执行。

4、执行器会调用存储引擎层的接口来进行数据的读写操作。最后把结果返回给客户端。

存储引擎

什么是存储引擎？

是MySQL特有的，一个表存储数据的方式。 不同的存储引擎，存储数据的方式不同。

MySQL有哪些存储引擎？

一共九大存储引擎。MySQL版本不同，支持的存储引擎就不同。

常用的

InnoDB、MyISAM、Memory。

MyISAM怎么读？ my I sam。 个人喜欢读my I sum。

其他

csv、archive、blackhole、NDB Cluster。

怎么查看MySQL支持的存储引擎？

mysql下执行命令：show engines\G;

InnoDB和MyISAM有什么区别？

数据的存储方式不同

创建一张表，如果使用InnoDB存储引擎，在本地会生成两个文件。 .frm文件存表结构，.ibd文件存表的行数据和索引数据。

如果使用MyISAM存储引擎，在本地会生成三个文件。 .frm文件存表结构，.MYI文件存表的索引数据，.MYD文件存表的行数据。

支持的索引不同

正是因为两者存储数据方式的不同，导致支持的索引也不同。

MyISAM是不支持聚簇索引的，因为聚簇索引的索引数据和行数据在物理上是连续的，而MyISAM用两个文件单独存储索引数据和行数据，所以不支持。

MyISAM只支持非聚簇索引。（话说回来，MyISAM可是MySQL的亲儿子，它不支持聚簇索引这件事，导致后面很多技术都没法实现。这下，支持聚簇索引的InnoDB就占了上分） 不过这也是有好处的，使用MyISAM的表，无论用什么索引，都只需要查一遍就能拿到数据。 而使用InnoDB的表，如果走聚簇索引，只需要查一遍，因为聚簇索引的叶子节点就包含了完整的数据行信息。 如果不走聚簇索引，就得查两遍，先从非聚簇索引里找到对应数据行的主键值，然后进行回表操作，根据主键值在聚簇索引中找到完整的数据行。

举个栗子吧。 假设有一张学生表，其中有学生ID、姓名和年龄等字段，其中学生ID是主键，以学生ID为索引构建了聚簇索引。

如果需要查询姓名为“张三”的学生记录 select * from student where name='张三'，可以使用非聚簇索引(比如姓名字段上的普通索引)进行查询，找到姓名为“张三”的记录，但是这些记录只包含主键值(学生ID)和索引列值(姓名)。

因此，MySQL需要根据主键值从聚簇索引中查询对应的数据行，才能获取到完整记录。这个过程，就是上面提到的回表，也称为二次查询。

是否支持事务机制

InnoDB支持事务机制，MyISAM不支持。

首先我们知道，事务有四特性：原子性、一致性、隔离性和持久性。InnoDB的undolog保证了原子性，redo log保证了持久性，mvcc和锁保证了隔离性。这三个特性一起保证了一致性。也就实现了事务。

而MyISAM没有undo log和redo log。

是否支持故障恢复

InnoDB支持故障恢复、容错能力强。但是MyISAM是没有容错能力的。

InnoDB为什么支持？因为在磁盘里有redo log日志，如果宕机了，可以拿来恢复数据。而MyISAM没有redo log，所以不支持故障恢复。而且如果宕机了或者出现故障，数据可能会丢。

支持的锁粒度不同

InnoDB支持多粒度锁，而MyISAM只支持表级锁。 锁粒度越小，发生并发冲突的概率就越低，并发性能就越高。 用户操作行数据时，只锁住一行数据；反之，如果是表级锁，则用户在操作表内的行数据时，会锁住整张表，并发性就低了，因为你在操作这张表内数据的时候，其他用户只能等待。

内存利用率

InnoDB的内存利用率更高，因为在InnoDB里，能放在内存里干的活都在内存里干了。 InnoDB在内存里有buffer pool、log buffer、page cache。MyISAM则过度依赖MySQL server了。

是否支持外键

InnoDB支持外键约束，MyISAM不支持。

那什么时候用MyISAM？

用主从架构实现读写分离的时候，一般是从库做读库，主库做写库。这时候就可以把从库的表结构改成MyISAM，因为不需要回表，速度更快。