MySQL管理与优化(5)_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL管理与优化(5)_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11800字，纯文字阅读大概需要17分钟。
内容图文
表类型(存储引擎)的选择
- 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，再重启服务器。
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
不吝指正。
内容总结
以上是互联网集市为您收集整理的MySQL管理与优化(5)_MySQL全部内容，希望文章能够帮你解决MySQL管理与优化(5)_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】