MySQL存储引擎笔记
MySQL存储引擎笔记
存储引擎概述
插件式存储引擎是MySQL数据库的重要特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事物等
|
特点 |
MyISAM |
InnoDB |
Memory |
|
事物安全 |
|
支持 |
|
|
锁机制 |
表锁 |
行锁 |
表锁 |
|
索引类型 |
B树索引 全文索引 |
B树索引 集群索引 |
B树索引 哈希索引 |
|
数据缓存 |
|
支持 |
支持 |
|
索引缓存 |
支持 |
支持 |
支持 |
|
支持外键 |
|
支持 |
|
存储引擎特性
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;
依次插入以下数据
|
t1 |
2 |
2 |
2 |
3 |
3 |
2 |
2 |
2 |
2 |
2 |
3 |
3 |
2 |
2 |
|
t2 |
2 |
3 |
2 |
3 |
4 |
2 |
3 |
2 |
3 |
2 |
3 |
4 |
2 |
3 |
查看数据库的中数据：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) 适用：内容变化不频繁，作为统计操作的中间结果表。
评论暂时关闭