php沙漏技巧_MySQL高级篇MySQL逻辑架构存储引擎

这篇将从如下几点，带你攻破逻辑架构和存储引擎：

可以类比我们的MVC架构

1） 连接层 最上层是一些客户端和链接手事，包含本地sock 通信和大多数基于客户端/做事端工具实现的类似于 TCP/IP的通信。
紧张完成一些类似于连接处理、授权认证、及干系的安全方案。
在该层上引入了线程池的观点，为通过认证安全接入的客户端供应线程。
同样在该层上可以实现基于SSL的安全链接。
做事器也会为安全接入的每个客户端验证它所具有的操作权限。
​

2） 做事层 第二层架构紧张完成大多数的核心做事功能，如SQL接口，并完成缓存的查询，SQL的剖析和优化，部分内置函数的实行。
所有跨存储引擎的功能也在这一层实现，如 过程、函数等。
在该层，做事器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 末了天生相应的实行操作。
如果是select语句，做事器还会查询内部的缓存，如果缓存空间足够大，这样在办理大量读操作的环境中能够很好的提升系统的性能。
​

3） 引擎层 存储引擎层， 存储引擎真正的卖力了MySQL中数据的存储和提取，做事器通过API和存储引擎进行通信。
不同的存储引擎具有不同的功能，这样我们可以根据自己的须要，来选取得当的存储引擎。
​

4）存储层 数据存储层， 紧张是将数据存储在文件系统之上，并完成与存储引擎的交互。
​

和其他数据库比较，MySQL有点分歧凡响，它的架构可以在多种不同场景中运用并发挥良好浸染。
紧张表示在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。
这种架构可以根据业务的需求和实际须要选择得当的存储引擎。

机器键盘玩家表示老在行了，热插拔键盘yyds

比拟一下，这是传统的电焊式（非热插拔）：

这是热插拔：

图源：www.zhihu.com/question/40…

和大多数的数据库不同，MySQL中有一个存储引擎的观点，针对不同的存储需求可以选择最优的存储引擎。
​

存储引擎便是存储数据，建立索引，更新查询数据等等技能的实现办法 。
存储引擎是基于表的，而不是基于库的。
以是存储引擎也可被称为表类型。
​

Oracle，SqlServer等数据库只有一种存储引擎。
MySQL供应了插件式的存储引擎架构。
以是MySQL存在多种存储引擎，可以根据须要利用相应引擎，或者编写存储引擎。
​

MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，个中InnoDB和BDB供应事务安全表，其他存储引擎是非事务安全表。

show engines;

​查询当前数据库支持的存储引擎

Engine：存储引擎名称。
Support：是否支持该引擎以及该引擎是否为默认存储引擎，YES表示支持，NO表示不支持，DEFAULT表示为默认存储引擎。
Comment：存储引擎的大略先容。
Transactions：表示该引擎是否支持事务。
XA：解释该存储引擎是否支持分布式事务。
Savepoints：解释该存储引擎是否支持部分事务回滚。

创建新表时如果不指定存储引擎，那么系统就会利用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB，这俩有什么差异，为什么MyISAM变为了InnoDB，下文我们会商及到这一点，也是口试时常常会问到的点

-- 查看支持的存储引擎SHOW ENGINES-- 查看默认存储引擎SHOW VARIABLES LIKE 'storage_engine'--查看详细某一个表所利用的存储引擎，show create table tablename--准确查看某个数据库中的某一表所利用的存储引擎show table status like 'tablename'show table status from database where name="tablename"设置存储引擎

-- 建表时指定存储引擎。
默认的便是INNODB，不须要设置CREATE TABLE t1 (i INT) ENGINE = INNODB;CREATE TABLE t2 (i INT) ENGINE = MEMORY;-- 修正存储引擎ALTER TABLE t ENGINE = InnoDB;-- 修正默认存储引擎，或者在配置文件my.cnf中修正默认引擎SET default_storage_engine=NDBCLUSTER;查看默认存储引擎

show variables like '%storage_engine%' ；

最常利用的两种存储引擎： InnoDB、MyISAM ；

特点

INNODB

MYISAM

MEMORY

MERGE

NDB

存储限定

64TB

有

有

没有

有

事务安全

支持

锁机制

行锁(适宜高并发)

表锁

表锁

表锁

行锁

B树索引

支持

支持

支持

支持

支持

哈希索引

支持

全文索引

支持(5.6版本之后)

支持

集群索引

支持

数据索引

支持

支持

支持

索引缓存

支持

支持

支持

支持

支持

数据可压缩

支持

空间利用

高

低

N/A

低

低

内存利用

高

低

中等

低

高

批量插入速率

低

高

高

高

高

支持外键

支持

InnoDB存储引擎是Mysql的默认存储引擎。
InnoDB存储引擎供应了具有提交、回滚、崩溃规复能力的事务安全。
但是比拟MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
​

InnoDB存储引擎不同于其他存储引擎的特点，有如下几个 ：

InnoDB采取MVCC来支持高并发，并且实现了四个标准的隔离级别。
其默认级别是 REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的涌现。
间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

这四个隔离级别，间隙锁等，我们在之后的锁和事务篇章，再来详细谈谈。

create table goods_innodb(id int NOT NULL AUTO_INCREMENT,name varchar(20) NOT NULL, primary key(id))ENGINE=innodb DEFAULT CHARSET=utf8;start transaction;insert into goods_innodb(id,name)values(null,'Meta20');commit;外键约束

MySQL支持外键的存储引擎只有InnoDB ， 在创建外键的时候， 哀求父表必须有对应的索引 ， 子表在创建外键的时候， 也会自动的创建对应的索引。

这便是为什么我们没有创建索引的时候，只能选主键作为外键，由于主键会默认创建索引

​

在创建索引时， 可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。
​

如果删除的是子表的话，是没有约束的。

相称于两者同步，共死活

下面两张表中 ， country_innodb是父表 ， country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id 。
​

create table country_innodb(country_id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, primary key(country_id))ENGINE=InnoDB DEFAULT CHARSET=utf8;create table city_innodb(city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ON DELETE RESTRICT:删除主表，如果有关联记录，不删除-- ON UPDATE CASCADE:更新主表，如果子表有关联记录，更新子表记录insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

删除country_id为1 的country数据：

delete from country_innodb where country_id = 1;

show create table city_innodb ;

InnoDB 存储表和索引有以下两种办法 ：①. 利用共享表空间存储， 这种办法创建的表的表构造保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

②. 利用多表空间存储， 这种办法创建的表的表构造仍旧存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

锁机制是行锁而不是表锁

详细有关于锁方面的知识，我们后续锁与事务篇再详细磋商！
！

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

也叫主键索引和赞助索引

当然，这里也不是绝对的，看下边的剖析：

MyISAM 快的条件是：select count() 语句没有where条件，无需筛选，才能直接通过读出变量得到详细行数，如果须要筛选的话，自然没办法直接通过读取变量，就得到详细行数了。

这个可能会涌如今口试题喔！
！

Memory存储引擎将表的数据存放在内存中。
每个MEMORY表示实对应一个磁盘文件，格式是.frm ，该文件中只存储表的构造，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高全体表的效率。
​

MEMORY 类型的表访问非常地快，由于他的数据是存放在内存中的，并且默认利用HASH索引 ， 但是做事一旦关闭，表中的数据就会丢失。

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须构造完备相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

对付MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，利用FIRST 或 LAST 值使得插入操作被相应地浸染在第一或者末了一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表实行插入操作。
​

可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

1）创建3个测试表 order_1990， order_1991， order_all ， 个中order_all是前两个表的MERGE表 ：

create table order_1990(order_id int ,order_money double(10,2),order_address varchar(50),primary key (order_id))engine = myisam default charset=utf8;create table order_1991(order_id int ,order_money double(10,2),order_address varchar(50),primary key (order_id))engine = myisam default charset=utf8;create table order_all(order_id int ,order_money double(10,2),order_address varchar(50),primary key (order_id))engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;

2）分别向两张表中插入记录

insert into order_1990 values(1,100.0,'北京');insert into order_1990 values(2,100.0,'上海');insert into order_1991 values(10,200.0,'北京');insert into order_1991 values(11,200.0,'上海');

若是往order_all中插入一条记录 ，由于在MERGE表定义时，INSERT_METHOD 选择的是LAST，那么插入的数据会向末了一张表中插入。

insert into order_all values(100,10000.0,'西安')；

在选择存储引擎时，该当根据运用系统的特点选择得当的存储引擎。
对付繁芜的运用系统，还可以根据实际情形选择多种存储引擎进行组合(但一样平常不到万不得已，不要混用多种存储引擎，否则可能带来一系列繁芜的问题)。
以下是几种常用的存储引擎的利用环境： ​

作者：Melo_链接：https://juejin.cn/post/7065233922233204750