表类型（存储引擎）的选择

来源：互联网发布：dnf卡史诗软件编辑：程序博客网时间：2024/05/16 14:58

存储引擎：用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等。存储引擎包括：myisam ,innodb ,bdb ,memory ,merge ,example ,ndb cluster , archive, csv,blackhole ,federated等，其中innodb和dbd提供事务安全，其他存储引擎都是非事务安全表。

查看当前的存储引擎：

mysql> show create table t8;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                  |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t8    | CREATE TABLE `t8` (  `id` int(11) NOT NULL,  `name` varchar(45) DEFAULT NULL,  `age` varchar(45) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

查看当前数据库支持的存储引擎

方法一，

mysql> show engines \g+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || 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         || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)

方法二，

mysql> show variables like 'have%';+------------------------+----------+| Variable_name          | Value    |+------------------------+----------+| have_compress          | YES      || have_crypt             | YES      || have_dynamic_loading   | YES      || have_geometry          | YES      || have_openssl           | DISABLED || have_profiling         | YES      || have_query_cache       | YES      || have_rtree_keys        | YES      || have_ssl               | DISABLED || have_statement_timeout | YES      || have_symlink           | YES      |+------------------------+----------+11 rows in set (0.01 sec)

在创建表的时候，可以通过增加engine关键字来设置新建表的存储引擎：

mysql> create table t9(id bigint(20) primary key,name varchar(63))engine=innodb default charset=gbk;mysql> show create table t9;+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| t9    | CREATE TABLE `t9` (  `id` bigint(20) NOT NULL,  `name` varchar(63) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

修改表的存储引擎：

mysql> alter table t9 engine=myisam;Query OK, 0 rows affected (0.47 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table t9;+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| t9    | CREATE TABLE `t9` (  `id` bigint(20) NOT NULL,  `name` varchar(63) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=gbk |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

二，各种存储引擎的特性

在平时我们最常用的4种存储引擎有：

mysql存储引擎对比特点myisaminnodbmemorymerge存储限制有64tb有没有事务安全支持锁机制表锁行锁表锁表锁b树索引支持支持支持支持哈希索引支持全文索引支持集群索引支持数据缓存支持支持索引缓存支持支持支持支持数据可压缩支持空间使用低高n/a低内存使用低高中等低批量插入速度高低高高支持外键支持

0 0