mysql 存储引擎

优化结构介绍：

类型	含义
存储层	存储引擎、字段类型选择、范式设计
设计层	索引、缓存、分区(分表)
架构层	多个mysql服务器设置，读写分离(主从模式)
sql语句层	多个sql语句都可以达到目的的情况下，要选择性能高、速度快的sql语句

存储引擎

存储引擎：我们使用的数据是通过一定的技术存储在数据库当中的，数据库的数据是以文件形式存储到硬盘当中的。技术不只一种，并且每种技术有自己独特的性能和功能体现。存储数据的技术和其功能的合并就称为“存储引擎”。

• 在mysql中经常使用的存储引擎：Myisam或Innodb等等。
  

数据库的数据存储在不同的存储引擎里边，所有的特性就与当前的存储引擎有一定关联。 需要按照项目的需求、特点选择不同的存储引擎。

• 查看mysql中支持的全部存储引擎：
  

innodb

数据库每个数据表的数据设计三方面信息：表结构、数据、索引

• 技术特点：支持事务、行级锁定、外键

表结构、数据、索引的物理存储

• 创建一个innodb数据表：
  
• 表结构文件：

该类型 数据、索引 的物理文件位置：

• 所有innodb表的数据和索引信息都存储在以下ibdata1文件中

给innodb类型表 的数据和索引创建自己对应的存储空间：

• 默认情况下每个innodb表的 数据和索引 不会创建单独的文件存储

• 设置变量，使得每个innodb表有独特的数据和索引 存储文件：

• 重新创建order2数据表：

• 此时order2数据表有单独的数据和索引存储文件：

• 后期无论innodb_file_per_table的设置状态如何变化，order2的数据和索引都有独立的存储位置

数据存储顺序

• innodb表数据的存储是按照主键的顺序排列每个写入的数据。

该特点决定了该类型表的写入操作较慢。

事务、外键

该类型数据表支持事务、外键

• 事务：http://blog.csdn.net/change_any_time/article/details/79488020

外键：两个数据表A和B，B表的主键是A表的普通字段，在A表看这个普通的字段就是该表的“外键”，外键的使用有”约束”。
约束：以上两个表，必须先写B表的数据，再写A表的数据，并且 A表的外键取值必须来之B表的主键id值，不能超过其范围。

• 真实项目里边很少使用“外键”，因为有约束。

并发性

该类型表的并发性非常高，多人同时操作该数据表，为了操作数据表的时候，数据内容不会随便发生变化，要对信息进行“锁定”
该类型锁定级别为：行锁。只锁定被操作的当前记录。

补充：

• 不保存表的具体行数，COUNT时通过扫描表来计算有多少行
• DELETE 表时，是一行一行的删除
• 跨平台可直接拷贝使用

Myisam

结构、数据、索引独立存储 ，该类型的数据表 表结构、数据、索引 都有独立的存储文件：

• 创建Myisam数据表
  
• 每个myisam数据表的 结构、数据、索引 都有独立的存储文件
  

表文件类型	含义
*.frm	表结构文件
*.MYD	表数据文件
*.MYI	表索引文件

特点：独立的存储文件可以单独备份、还原。

数据存储顺序

• myisam表数据的存储是按照自然顺序排列每个写入的数据。
  

该特点决定了该类型表的写入操作较快。

并发性

该类型并发性较低， 该类型的锁定级别为：表锁

压缩机制

如果一个数据表的数据非常多，为了节省存储空间，需要对该表进行压缩处理。

• 复制当前数据表的数据：
  
• 不断复制使得order3数据表的数据变为200多万条：
  
• 对应的存储该200万条信息的文件的物理大小为40多兆：
  

开始压缩order3数据表的数据

• 压缩工具：myisampack.exe 表名
  
• 重建索引：myisamchk.exe -rq 表名
  
• 解压缩工具：myisamchk.exe –unpack 表名
  
• order3表信息被压缩的60%的空间：
  
• order3数据表有压缩，但是索引没有了：
  
• 重建索引：
  
• 索引果然被重建完毕：
  
• 刷新数据表：flush table 表名
  
• 出现情况：压缩的数据表是只读表，不能写信息：
  

压缩的数据表有特点：不能频繁的写入操作，只是内容固定的数据表可以做压缩处理，例如（存储全国地区信息的数据表等）
如果必须要写数据：就解压该数据表，写入数据，再压缩

• 解压order3数据表，使得其可以写入数据：(解压同时索引自动重建)
  
• 数据解压完毕：
  
• 执行flush操作，更新解压后的数据：flush table 表名；该操作同时会删除order3.MYD.00996D46.deleted的压缩备份文件
  
• 此时允许给order3继续写入数据：
  

补充：

• 保存表的具体行数,COUNT表时，不带where条件，直接返回保存的行数
• DELETE 表时，先drop表，然后重建表
• 跨平台很难直接拷贝

区别：

innodb存储引擎：适合做修改、删除
Myisam存储引擎：适合做查询、写入

Archive

归档型存储引擎，该引擎只有写入、查询操作，没有修改、删除操作，比较适合存储“日志”性质的信息。

memory

内存型存储引擎，操作速度非常快速，比较适合存储临时信息，服务器断电，给存储引擎的数据立即丢失。

存储引擎的选择

Myisam和innodb

1. 网站大多数情况下“读和写”操作非常多，适合选择Myisam类型（例如： dedecms、phpcms内容管理系统(新闻网站)、discuz论坛）
2. 网站对业务逻辑有一定要求(办公网站、商城)适合选择innodb （Mysql5.5默认存储引擎都是innodb）