MySQL存储引擎笔记

存储引擎概述

插件式存储引擎是MySQL数据库的重要特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事物等

存储引擎特性

MyISAM（ISAM→ Indexed Sequential Access Method ）引擎

a)        MyISAM

劣势：不支持事物、不支持外键

优势：访问速度快

适用：对事物完整性无要求、并发性不高或以select和insert操作为主的应用（web、数据仓库等）

b)       MyISAMy表存储格式

静态表：存储迅速、容易缓存、故障恢复容易（注：数据末尾的空格会被自动剔除）

动态表：空间占用小，删除和更新易导致碎片化（注：optimize table或myisamchk -r碎片整理）

压缩表：每条记录单独压缩、访问开支小

InnoDB引擎

a)      InnoDB

劣势：写操作效率差、占用更多磁盘（保留数据和索引）

优势：事物安全、支持外键

适用：对事物完整性和并发下得一致性要求高并且有较多更新、删除操作（InnoDB有效降低了删除更新导致的表锁定）的应用（计费、财务系统等）

b)     自动增长序列

1.      自动增长列必须是索引。

2.      如果是组合索引，则自动增长列必须是第一列（MyISAMy可为其他列，MyISAMy是按照前几列排序后递增的）

例：

create table autoincrease_demo_myisam

(t1 smallint not null auto_increment,

 t2 smallint not null,

 name varchar(20),

 index(t2,t1)

)engine =myisam;

依次插入以下数据

查看数据库的中数据：select * from autoincrease_demo_myisam order by t2,t1;

+----+----+------+

| t1 | t2 | name |

+----+----+------+

| 1 |  2 | 2    |

| 2 |  2 | 3    |

| 3 |  2 | 2    |

| 4 |  2 | 2    |

| 5 |  2 | 3    |

| 6 |  2 | 2    |

| 7 |  2 | 3    |

| 8 |  2 | 2    |

| 9 |  2 | 2    |

| 10 | 2 | 3    |

| 1 |  3 | 3    |

| 2 |  3 | 4    |

| 3 |  3 | 3    |

| 4 |  3 | 4    |

+----+----+------+

c)      外键约束

MySQL支持外键的存储引擎只有InnoDB。在创建外键的时候，要求父表必须有对应的索引，字表在创建外键的时候也会创建对应的索引。外键关联的操作主要有restrict、cascade、set null、no action.

restrict、no action：在子表有关联记录的情况父表不能更新。

cascade：父表在更新或者删除时，更新或者删除子表对应记录。

set null：父表在更新或者删除时，字表对应的字段被set null。

d)     存储方式

共享表空间存储：所有的数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。

独立表空间存储：每个表的数据和索引单独保存在自己的表空间中。（单表备份和恢复较方便）

Memory引擎

a)      优点：Memory存储引擎使用存储在内存中的内容创建表，默认使用HASH索引。表访问非常快（在启动服务的时候可以使用--init-file选项将insert into ,select 或load data infile这样的语句放入这个文件，就可以再服务启动时从持久稳定的数据源装载表。）

b)     缺点：表大小有限制，服务关闭表中的数据就会丢失

c)      适用：</strong>内容变化不频繁，作为统计操作的中间结果表。</span>