数据库中数据管理的一种方式,MySQL支持插入式的存储引擎,也就是说你同一数据库中的数据表可以选择不同存储引擎
查看mysql版本
- mysql> select version();
- +-----------+
- | version() |
- +-----------+
- | 5.5.25a |
- +-----------+
- 1 row in set
查看mysql引擎
- mysql> show engines;
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
- | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
- | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
- | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
- | CSV | YES | CSV storage engine | NO | NO | NO |
- | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
- | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
- | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
- | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql5.5版本默认的数据库引擎是innoDB
最常见的数据库引擎是InnoDB和MyISAM
ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于,它不 支持事务处理,也不能够容错,MyISAM除了提供ISAM里所没有的索引和字段管理的大量功能,MyISAM还使用一种表格锁定的机制,来优化多个并发的读写操作,其代价是你需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间。MYISAM强调了快速读取操作。
在使用MYSQL的时候,你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理(transaction process)也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多,但是InnoDB包括了对事务处理和外来键的支持,这两点都是前两个引擎所没有的。
MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。
MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。
MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据
InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含where条件时,两种表的操作是一样的
MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。能加载更多索引,而Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb比MyISAM体积庞大
DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除
MyISAM适合:
(1)做很多count 的计算;
(2)插入不频繁,查询非常频繁;
(3)没有事务。
InnoDB适合:
(1)可靠性要求比较高,或者要求事务;
(2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况指定数据引擎的创建
查看表结构
- mysql> describe cds;
- +-----------+---------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-----------+---------------------+------+-----+---------+----------------+
- | titel | varchar(200) | YES | | NULL | |
- | interpret | varchar(200) | YES | | NULL | |
- | jahr | int(11) | YES | | NULL | |
- | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
- +-----------+---------------------+------+-----+---------+----------------+
- 4 rows in set
查看建表语句
- mysql> show create table cds;
- +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | cds | CREATE TABLE `cds` (
- `titel` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
- `interpret` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
- `jahr` int(11) DEFAULT NULL,
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
- +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row in set
查看表的详细信息
- mysql> show table status like 'cds';
- +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
- +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
- | cds | MyISAM | 10 | Dynamic | 3 | 49 | 176 | 281474976710655 | 2048 | 28 | 7 | 2005-10-25 15:47:49 | 2012-04-16 23:30:18 | NULL | latin1_general_ci | NULL | | |
- +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
- 1 row in set
修改数据库引擎
- mysql > alter table cds engine = InnoDB;
- Query OK,
- 3 rows affected Records: 3 Duplicates: 0 Warnings: 0
再次查看表的详细信息
- mysql> show table status like 'cds';
- +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
- +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
- | cds | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 6291456 | 7 | 2012-04-16 23:30:18 | NULL | NULL | latin1_general_ci | NULL | | |
- +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
来源: http://blog.csdn.net/u010942020/article/details/51925648