數(shù)據(jù)庫中數(shù)據(jù)管理的一種方式，MySQL支持插入式的存儲引擎，也就是說你同一數(shù)據(jù)庫中的數(shù)據(jù)表可以選擇不同存儲引擎
查看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版本默認的數(shù)據(jù)庫引擎是innoDB
最常見的數(shù)據(jù)庫引擎是InnoDB和MyISAM
MyISAM
ISAM執(zhí)行讀取操作的速度很快，而且不占用大量的內(nèi)存和存儲資源。ISAM的兩個主要不足之處在于，它不 支持事務處理，也不能夠容錯，MyISAM除了提供ISAM里所沒有的索引和字段管理的大量功能，MyISAM還使用一種表格鎖定的機制，來優(yōu)化多個并發(fā)的讀寫操作，其代價是你需要經(jīng)常運行OPTIMIZE TABLE命令，來恢復被更新機制所浪費的空間。MYISAM強調(diào)了快速讀取操作。
InnoDB
在使用MYSQL的時候，你所面對的每一個挑戰(zhàn)幾乎都源于ISAM和MyISAM數(shù)據(jù)庫引擎不支持事務處理（transaction process）也不支持外來鍵。盡管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了對事務處理和外來鍵的支持，這兩點都是前兩個引擎所沒有的。
兩種引擎的比較與使用場合
MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持。
MyISAM類型的表強調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持已經(jīng)外部鍵等高級數(shù)據(jù)庫功能。
MyISAM格式的一個重要缺陷就是不能在表損壞后恢復數(shù)據(jù)
InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當count(*)語句包含where條件時，兩種表的操作是一樣的
MyISAM的索引和數(shù)據(jù)是分開的，并且索引是有壓縮的，內(nèi)存使用率就對應提高了不少。能加載更多索引，而Innodb是索引和數(shù)據(jù)是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大
DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除
MyISAM適合：
(1)做很多count 的計算；
(2)插入不頻繁，查詢非常頻繁；
(3)沒有事務。
InnoDB適合：
(1)可靠性要求比較高，或者要求事務；
(2)表更新和查詢都相當?shù)念l繁，并且表鎖定的機會比較大的情況指定數(shù)據(jù)引擎的創(chuàng)建
更改一個表的數(shù)據(jù)庫引擎
查看表結(jié)構(gòu)
- 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
修改數(shù)據(jù)庫引擎
- 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 | | |
- +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點擊舉報。
