MySQL管理与优化(5)_MySQL

表类型(存储引擎)的选择

• 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)：

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，再重启服务器。

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

不吝指正。