表类型(存储引擎)的选择
- MySQL5.5支持的存储引擎及其特性
mysql> SHOW ENGINES/G*************************** 1. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engineTransactions: NULL XA: NULL Savepoints: NULL*************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO*************************** 3. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO*************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears)Transactions: NO XA: NO Savepoints: NO*************************** 5. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO*************************** 6. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO XA: NO Savepoints: NO*************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engineTransactions: NO XA: NO Savepoints: NO*************************** 8. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 9. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance SchemaTransactions: NO XA: NO Savepoints: NO
- 可见，这里仅InnoDB支持事务，且为默认的存储引擎。
- 我们在创建表时可指定表的存储类型，如：
mysql> CREATE TABLE ai( -> id bigint(20) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(id)) ENGINE=MyISAM DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.51 sec)mysql> ALTER TABLE ai ENGINE=InnoDB;Query OK, 0 rows affected (0.23 sec)Records: 0 Duplicates: 0 Warnings: 0
- 对于各种存储引擎的特性如下(MySQL5.7)：
| Feature | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| Storage limits | 256TB | RAM | 64TB | None | 384EB |
| Transactions | No | No | Yes | No | Yes |
| Locking granularity | Table | Table | Row | Table | Row |
| MVCC | No | No | Yes | No | No |
| Geospatial data type support | Yes | No | Yes | Yes | Yes |
| Geospatial indexing support | Yes | No | No | No | No |
| B-tree indexes | Yes | Yes | Yes | No | No |
| T-tree indexes | No | No | No | No | Yes |
| Hash indexes | No | Yes | No[a] | No | Yes |
| Full-text search indexes | Yes | No | Yes[b] | No | No |
| Clustered indexes | No | No | Yes | No | No |
| Data caches | No | N/A | Yes | No | Yes |
| Index caches | Yes | N/A | Yes | No | Yes |
| Compressed data | Yes[c] | No | Yes[d] | Yes | No |
| Encrypted data[e] | Yes | Yes | Yes | Yes | Yes |
| Cluster database support | No | No | No | No | Yes |
| Replication support[f] | Yes | Yes | Yes | Yes | Yes |
| Foreign key support | No | No | Yes | No | No |
| Backup / point-in-time recovery[g] | Yes | Yes | Yes | Yes | Yes |
| Query cache support | Yes | Yes | Yes | Yes | Yes |
| Update statistics for data dictionary | Yes | Yes | Yes | Yes | Yes |
| [a] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. [b] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher. [c] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. [d] Compressed InnoDB tables require the InnoDB Barracuda file format. [e] Implemented in the server (via encryption functions), rather than in the storage engine. [f] Implemented in the server, rather than in the storage engine. [g] Implemented in the server, rather than in the storage engine. |
MyISAM:
- 特点：不支持事务，不支持外键，访问速度快。
- 每个MyISAM类型的表会被存储为3种文件：
1. frm(存储表定义)；
2. MYD(MYData, 存储数据)；
3. MYI(MYIndex，存储索引)
- MyISAM的表还支持3种不同的存储格式：静态(固定长度)表，动态表，压缩表。
1. 静态表：字段都非变长，存取快，占用空间多，返回数据会丢失尾部的空格。如，
mysql> CREATE TABLE test_myisam(name CHAR(10)) ENGINE=MYISAM;Query OK, 0 rows affected (0.10 sec)mysql> INSERT INTO test_myisam values('abc'),('abc '), (' ahc');mysql> SELECT name, length(name) FROM test_myisam;+-------+--------------+| name | length(name) |+-------+--------------+| abc | 3 || abc | 3 || ahc | 5 |+-------+--------------+3 rows in set (0.06 sec) 2. 动态表：字段可变长，记录不是固定的长度，占用空间较少，碎片多，可通过myisamchk -r或OPTIMIZE TABLE来优化。
3. 压缩表：可通过myisampack来创建，占用空间少，访问开销小。
InnoDB:
- 特点：支持事务，写速度较MyISAM慢，占用空间比MyISAM大。
- 自动增长列：即AUTO_INCREMENT。InnoDB的自动增长列必须是索引的或组合索引的第一列(MyISAM可不是第一列)
- 外键约束 : 只有InnoDB支持外键约束, 创建外键时父表必须有对应的索引, 子表在创建外键的时候也会自动创建对应的索引
范例:
-- 主表mysql> CREATE TABLE country ( -> country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> country VARCHAR(50) NOT NULL, -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (country_id))ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.09 sec)-- 从表mysql> 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 FOREIGN KEY (country_id) REFERENCES country(country_id) ON DELETE RESTRICT ON UPDATE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.07 sec)
- 存储方式：两种方式,
1. 共享表空间存储。.frm文件保存表结构信息，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间里，可以是多个文件。
2. 多表空间存储。.frm文件保存表结构信息，每个表的数据和索引单独保存在.ibd的文件中，如果是分区表，每个分区对应单独的.ibd文件，文件名为“表名+分区名”。若要使用多表存储，需要设置参数innodb_file_per_table，再重启服务器。
magento(麦进斗)
下载
Magento是一套专业开源的PHP电子商务系统。Magento设计得非常灵活，具有模块化架构体系和丰富的功能。易于与第三方应用系统无缝集成。Magento开源网店系统的特点主要分以下几大类，网站管理促销和工具国际化支持SEO搜索引擎优化结账方式运输快递支付方式客户服务用户帐户目录管理目录浏览产品展示分析和报表Magento 1.6 主要包含以下新特性：•持久性购物 - 为不同的
MEMORY:
- 特点：每个memory表实际只对应1个磁盘文件，格式为.frm文件。由于数据在内存中，访问速度非常快，默认使用HASH索引，但如果服务挂了，数据也就没了。
- 启动MySQL服务时，可是使用--init-file选项，把INSERT...SELECT或LOAD DATA INFILE语句放入该文件，可加载数据。
MERGE:
- 特点：是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。
- 对MERGE类型的表进行查询，更新，删除操作，这些操作实际上是对内部的实际的MyISAM表进行操作。
- 插入操作通过INSERT_METHODZ子句定义的，其可以有三个值：FIRST, LAST, NO。FIRST作用在第一张表，LAST作用在最后一张表，NO不作用表。
- 对MERGE表进行DROP操作，只是删除MERGE定义，对内部表无影响。
- MERGE表被保存为.frm文件(存储表定义)和.MRG(存储组合表信息)。
- 范例：
-- 初始化表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.13 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.05 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.05 sec)-- 插入数据mysql> INSERT INTO payment_2006 VALUES (1, '2006-05-01', 100000), (2, '2006-08-15', 150000);Query OK, 2 rows affected (0.04 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> INSERT INTO payment_2007 VALUES (1, '2007-02-20', 35000), (2, '2007-07-15', 220000);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0-- 查询3张表的数据mysql> SELECT * FROM payment_2006;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2006-05-01 00:00:00 | 100000.00 || 2 | 2006-08-15 00:00:00 | 150000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec)mysql> SELECT * FROM payment_2007;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec)mysql> SELECT * FROM payment_all;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2006-05-01 00:00:00 | 100000.00 || 2 | 2006-08-15 00:00:00 | 150000.00 || 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 |+------------+---------------------+-----------+-- 向MERGE表插入数据，仅仅会作用于最后一张表，即payment_2007mysql> INSERT INTO payment_all VALUES(3, '2014-06-15', 10000);Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM payment_2006;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2006-05-01 00:00:00 | 100000.00 || 2 | 2006-08-15 00:00:00 | 150000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec)mysql> SELECT * FROM payment_2007;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 || 3 | 2014-06-15 00:00:00 | 10000.00 |+------------+---------------------+-----------+3 rows in set (0.00 sec)mysql> SELECT * FROM payment_all;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2006-05-01 00:00:00 | 100000.00 || 2 | 2006-08-15 00:00:00 | 150000.00 || 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 || 3 | 2014-06-15 00:00:00 | 10000.00 |+------------+---------------------+-----------+
如何选择合适的存储引擎：
具体存储引擎细节可参考：
http://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
不吝指正。
