一、Mysql架构

 

 和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

 

 连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

 

 服务层：第二层是服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等。

 

 引擎层：第三层是存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

 

 存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互。

 

 MySQL 的查询流程是怎样的？或者说一条SQL语句在MySQL中是如何执行的？

 

 流程描述如下：客户端请求 —> 连接器（验证用户身份，给予权限） —> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —> 分析器（对SQL进行词法分析和语法分析操作） —> 优化器（主要对执行的sql优化选择最优的执行方案方法） —> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

 

二、存储引擎

 

 存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

 

 使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能 。

 

 MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎。

 

 1、查看存储引擎

 

-- 查看支持的存储引擎

 

SHOW ENGINES

 

-- 查看默认存储引擎

 

SHOW VARIABLES LIKE 'storage_engine'

 

--查看具体某一个表所使用的存储引擎

 

show create table tablename

 

--准确查看某个数据库中的某一表所使用的存储引擎

 

show table status like 'tablename'

 

show table status from database where name="tablename"

 

 2、设置存储引擎

 

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置

 

CREATE TABLE t1 （i INT） ENGINE = INNODB;

 

CREATE TABLE t2 （i INT） ENGINE = CSV;

 

CREATE TABLE t3 （i INT） ENGINE = MEMORY;

 

-- 修改存储引擎

 

ALTER TABLE t ENGINE = InnoDB;

 

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎

 

SET default_storage_engine=NDBCLUSTER;

 

 Tip：默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表。

 

 3、存储引擎对比

 

  常见的存储引擎有 InnoDB、MyISAM、Memory、NDB。InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键。

 

 3.1、文件存储结构对比

 

  在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，。frm 文件用来保存每个数据表的元数据（meta）信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有。frm文件，命名方式为 数据表名。frm，如user.frm。

 

  查看MySQL 数据保存在哪里：

 

show variables like 'data%'

 

1

 

  MyISAM 物理文件结构为：

 

   。frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等；

 

   。MYD （MYData） 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据；

 

   。MYI （MYIndex）文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息；

 

  InnoDB 物理文件结构为：

 

   。frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等；

 

   。ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据（独享表空间存储方式使用。ibd文件，并且每个表一个。ibd文件；共享表空间存储方式使用。ibdata文件，所有表共同使用一个（或多个，可自己配置）。ibdata文件）。

 

 3.2、Innodb和MyIsam存储引擎的区别

 

  InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

 

  InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

 

  InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

 

  InnoDB 不保存表的具体行数，执行select count（*） from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

 

  InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

 

对比项 MyIsam Innodb

 

主外键 不支持 支持

 

事务 不支持 支持

 

行表锁 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作

 

缓存 只缓存索引，不缓存真实数据 不仅缓存索引还会缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

 

表空间 小 大

 

关注点 性能 事务

 

默认安装 是 是

 

  问题1：一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15、16、17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15？

 

  如果表的引擎类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；如果表的引擎类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

 

  问题2：哪个存储引擎执行 select count（*） 更快，为什么？

 

  MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count（*） from t 时，直接返回总数据。在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count（*） from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。InnoDB 中 count（*） 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

 

三、数据类型

 

 主要包括以下五大类：

 

 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

 

 浮点数类型：FLOAT、DOUBLE、DECIMAL

 

 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

 

 日期类型：Date、DateTime、TimeStamp、Time、Year

 

 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

 

 问题：CHAR 和 VARCHAR 的区别？

 

char是固定长度，varchar长度可变：

 

char（n） 和 varchar（n） 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR（30） 就可以存储 30 个字符。存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间。

 

相同点：

 

①char（n），varchar（n）中的n都代表字符的个数

 

②超过char，varchar最大长度n的限制后，字符串会被截断

 

不同点：

 

①char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）

 

②能存储的最大空间限制不一样：char的存储上限为255字节

 

③char在存储时会截断尾部的空格，而varchar不会

 

char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。