MySQL存储引擎概述
7.表类型 (存储引擎)的选择
7.1 MySQL 存储引擎概述
默认的存储引擎
创建新表不指定表的存储引擎,则新表是默认存储引擎的
修改默认存储引擎:
可修改参数文件中设置 default_table_type
查看当前的默认存储引擎,可以使用以下命令:
mysql> show variables like 'table_type';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| table_type | MyISAM |
查看当前数据库支持的引擎
方法一:
mysql> SHOW ENGINES \G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
.......
第二种方法:
mysql> SHOW VARIABLES LIKE 'have%';
+----------------------------+-------+
| Variable_name | Value |
Linux公社 www.linuxidc.com
109
+----------------------------+-------+
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dlopen | YES |
| have_example_engine | NO |
..........
,其中 Value 显示为“DISABLED”的记录表示支持该存储引擎,但是数据库启动的时候被禁用。
创建表时设置引擎
在创建新表的时候,可以通过增加 ENGINE 关键字设置新建表的存储引擎
CREATE TABLE ai (
i bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (i)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
修改已有表引擎
使用 ALTER TABLE 语句,将一个已经存在的表修改成其他的存储引擎
mysql> alter table ai engine = innodb;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table ai \G
*************************** 1. row ***************************
Table: ai
Create Table: CREATE TABLE `ai` (
`i` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
7.2 各种存储引擎的特性
MyISAM
存储限制:有
事务安全:不支持
锁机制:表锁
B树索引:支持
哈希索引:不支持
全文索引:支持
集群索引:不支持
数据缓存:不支持
索引缓存:支持
数据可压缩:支持
空间使用:低
内存使用:低
批量插入数据的速度:高
支持外键:不支持
特点:
MyISAM 不支持事务,不支持外键。
访问速度快,对事务完整性没有要求,或者以INSERT 或者SELECT 为主的应用可以使用该引擎。
每个MyISAM 存储三个文件:
.frm(存储表定义)
.MYD(MYDate, 存储数据)
.MYI(MYIndex,存储索引)
数据文件和索引文件可以放置在不同的目录,平均分布IO,获得更快的速度。
指定数据文件和索引文件的目录,需要在创建表时,可以通过DATA_DIRECTORY和INDEX_DIRECTORY来设定。文件路径需要时绝对路径,并有访问权限。
MyISAM表可能损坏,可以通过 CHECK TABLE检查表健康状态,REPIRE TATBLE 修复,详细步骤31章
MyISAM 支持的三种不同存储格式
静态(固定长度)表
动态表
压缩表
静态表是默认存储格式,静态表中的字段都是非变长度字段,这样每个记录都是固定长度。
优点: 存储迅速,容易缓存,出现故障容易恢复。
缺点:占用空间比动态表多。
静态表存储时会按照列的宽度补足空格,应用访问是并不会得到这些空格,这些空格在返回应用前已经去掉。当存储时尾部真的需要空格时 也会被去掉。
动态表中包含变长字段,记录不是固定长度的。
占用空间相对较少,但是频繁的更新个删除记录会产生碎片,需要定期执行OPTIMIZE TABLE 或者 myisamchk -r命令来改善性能。并且在出现故障时恢复相对较困难。
压缩表由myisampack 工具创建,占据非常小的空间,因为每条记录是单独压缩的,所以有非常小的访问开支。
InnoDB
存储限制:64TB
事务安全:支持
锁机制:行锁
B树索引:支持
哈希索引:NO
全文索引:NO
集群索引:支持
数据缓存:支持
索引缓存:支持
数据可压缩:NO
空间使用:高
内存使用:高
批量插入数据的速度:低
支持外键:支持
特点 :
具有提交、回滚、和崩溃恢复能力的事务安全。
但是相对于MyISAM ,InnoDB写的效率差一些,并且会占用更多的磁盘空间以保存数据和索引。
自动增长列:
InnoDB的自动增长列可以手工插入,但是插入的值如果是0或者空时 将自动增长。可以通过ALTER TABLE tablename AUTO_INCREMENT = n 来设置自动增长的初始值。但是该值是保留在内存中的。如果重新启动 数据库,该值将丢失。
可以使用LAST_INSERT_ID(),来查询当前线程最后插入使用的值。如果插入多条,则返回第一条记录使用的值。
对于InnoDB的自增列必须是索引,如果是组合索引,必须是组合索引的第一列。但是对于MyISAM表,自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照前几列进行排序后自增的。
外键约束
只有InnoDB支持外键约束。创建外键时,父表必须有对应的索引,字表在创建外建时也会创建对应的索引
修改:
ALTER TABLE tablename ADD CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 关联表名(关联字段名);
创建:
CREATE TABEL table_name (
.....,
CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 关联表名(关联字段名)
) ENGINE = InnoDB;
创建外键时,可以指定在删除 更新 父表时对子表的操作。
RESTRICT
NO ACTION
CASCADE
SET NULL
例子:
CREATE TABLE city (
city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
city VARCHAR(50) NOT NULL,
country_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (city_id),
KEY idx_fk_country_id (country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON
DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
在导入多表数据时,可以暂时关闭外键检查 SET FOREIGN_KEY_CHECK = 0 :导入完成再开启 SET FOREIGN_KEY_CHECK = 1;
存储方式
使用共享表空间存储
使用多表空间存储
MEMEORY
存储限制:有
事务安全:NO
锁机制:表锁
B树索引:支持
哈希索引:支持
全文索引:NO
集群索引:NO
数据缓存:支持
索引缓存:支持
数据可压缩:NO
空间使用:N/A
内存使用:中等
批量插入数据的速度:高
支持外键:NO
特点:memory 存储引擎使用存在于内存中的内容来创建表,每个memory实际只对应一个磁盘文件,格式是.frm。 memory 表访问速度非常快,因为他数据存储在内存中的,并默认使用HASH索引。 但是服务一旦关闭 表中的数据就会丢失。
创建索引的时候可以指定使用HASH还是BTREE:
CREATE INDEX mem_hash USING HASH ON tablename(col_name)
持久数据:
在启动mysql时使用 --init-file选项,把INSERT INTO ....SELECT 或者LOAD DATA INFILE 这样的语句放入文件中,这样就可以将持久的数据载入表。
数据表的大小:
每个memory 表可以放置的数据大小,收到max_heap_table_size限制,系统默认是16Mb,可以根据需要加大。此外定义表的时候可以设置MAX_ROWS子句指定表的最大长度。
适用环境:
memory主要适用那些内容变化不频繁,或者作为那么统计操作的中间结果表。
要注意数据并没有写入硬盘,重启时数据会丢失。
MERGE [m?:rd?]
存储限制:没有
事务安全:NO
锁机制:表锁
B树索引:支持
哈希索引:NO
全文索引:NO
集群索引:NO
数据缓存:NO
索引缓存:支持
数据可压缩:NO
空间使用:低
内存使用:低
批量插入数据的速度:高
支持外键:NO
是什么
MERGE 存储引擎是一组MyISAM表的组合,这些MyISAM表的结构必须完全相同,MERGE表本身并没有数据,对MERGE标的可以进行查询、更新、删除操作,这些操作实际是对MyISAM标的操作。
定义MERGE表
语法
CRATE TABLE tablename (
....
)ENGINE = MERGE UNION(tablename1,tablename2, ,,) INSERT_METHOD=LAST
范例
(1)创建 3 个测试表 payment_2006、payment_2007 和 payment_all,其中 payment_all是前两个表的 MERGE 表:
mysql> create table payment_2006(
-> country_id smallint,
-> payment_date datetime,
-> amount DECIMAL(15,2),
-> KEY idx_fk_country_id (country_id)
-> )engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql> create table payment_2007(
-> country_id smallint,
-> payment_date datetime,
-> amount DECIMAL(15,2),
-> KEY idx_fk_country_id (country_id)
-> )engine=myisam;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE payment_all(
-> country_id smallint,
-> payment_date datetime,
-> amount DECIMAL(15,2),
-> INDEX(country_id)
-> )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.04 sec)
插入操作:
对MERGE表进行插入操作,是通过INSERT_METHOD 定义插入的表,有三个值:LAST 插入到最后一张表,FIRST 插入到第一张表,NO 不允许插入
NDB
存储限制:有
事务安全:NO
锁机制:表锁
B树索引:支持
哈希索引:支持
全文索引:NO
集群索引:NO
数据缓存:支持
索引缓存:支持
数据可压缩:NO
空间使用:N/A
内存使用:中等
批量插入数据的速度:高
支持外键:NO
第三方存储引擎: TokuDB
特点:高写 高压缩性能,
7.3 如何选择合适的存储引擎
MyISAM:
如果应用是读和插入操作为主,只有很少的更新和删除操作。对事务的完整性和并发要求不高,MyISAM 非常合适
InnoDB
用于事务处理程序,支持外键。如果应用对事务完整性有较高的要求,在并发的条件下要保证数据的一致性,数据除了查询和插入操作外还有很多的更新个删除操作,那么InnoDB比较合适,
MEMORY
将所有的数据保存在RAM中,在需要快速定位数据和其他类似的数据环境下,可提供快速访问,
缺陷是对表的大小有限制,太大的表无法缓存在内存中,其次要确保表的可恢复。
用户更新不频繁的小表,用户快速得到访问结果。
MERGE:
用于将一系列等同的表逻辑的组合在一起,
MERGE表的优点在于可以突破单个MyISAM的大小限制,并通过将不同的表分布在不同的磁盘上,可以有效的改善MERGE表的访问效率。
对于数据仓库VLDB非常合适。
上一篇: jQuery仿迅雷图片轮换效果
下一篇: 怎样才能学好WEB前端