mysql数据库引擎
数据库引擎
数据库中数据管理的一种方式,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
MyISAM
ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于,它不 支持事务处理,也不能够容错,MyISAM除了提供ISAM里所没有的索引和字段管理的大量功能,MyISAM还使用一种表格锁定的机制,来优化多个并发的读写操作,其代价是你需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间。MYISAM强调了快速读取操作。
InnoDB
在使用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 | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+