mysql一个比较重要的特性就是使用插件式存储引擎,这个特性允许用户可以根据应用的需要来选择存储引擎来处理数据,从而更好的支持应用。
现在的mysql支持很多的存储引擎,以5.5.18为例:
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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 |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| 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 |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
当前数据库默认使用那个存储引擎,可以通过参数default_store_engine指定;
mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | ON |
| storage_engine | InnoDB |
+---------------------------+--------+
3 rows in set (0.00 sec)
修改这个值,如果创建数据库没有明确指定存储引擎的话,创建的数据库表使用的就是该存储引擎。
mysql> create table test (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到,我们当前默认的存储引擎是innodb,创建数据库表的时候没有指定存储引擎,那么我们创建的表使用就是默认存储引擎。
如果要创建其他存储引擎的数据库表,可以在create语句中明确指定存储引擎或者修改默认的存储引擎。
mysql> create table test1 (id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> set session default_storage_engine='myisam';
Query OK, 0 rows affected (0.00 sec)
mysql> create table test2 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table test2;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到我们创建的数据库表test1和test2现在使用的都是myisam存储引擎。
myisam存储引擎和innodb存储引擎应该是现在使用比多的。
myisam存储引擎不支持事务,也不支持外键,特点是操作速度快。
myisam存储引擎的数据库表有三个文件构成,数据结构是一个以frm结尾的文件,数据文件是一个MYD结尾的文件,索引文件是一个MYI结尾的文件。
这些文件的文件名是相同的。
myisam存储引擎数据库表可以指定数据文件和索引文件中不同的目录,这样可以平均分配IO,提高性能。
在创建表的时候指定data directory放置数据文件,指定index directory放置索引文件。
mysql> create table test3 (id int(2),name varchar(10)) DATA DIRECTORY='/tmp' index DIRECTORY='/opt/index';
Query OK, 0 rows affected (0.01 sec)
这样就完成了把数据文件和索引文件放置在不同目录了。
数据文件和索引文件分开放置时,这两个文件不能放在datadir目录下的子目录内,需要mysql用户具有读写权限,还要注意SElinux的权限。
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> create table test5 (id int(2),name varchar(10)) DATA DIRECTORY='/var/lib/mysql/test1' index DIRECTORY='/var/lib/mysql';
ERROR 1210 (HY000): Incorrect arguments to DATA DIRECTORY
mysql>
如果使用的话,创建表就不能成功。
mysql的myisam存储引擎支持动态表,静态表和压缩表。这个写不同的表各有特长。
其中静态表是myisam存储引擎默认的存储格式,字段都是非变长字段,字段长度固定,长度不足的字段以空格自动填充。这种表读写速度快,便于缓存和修复,但是比较占空间。
动态表是字段长度不固定的,比较容易产生碎片,速度不如静态表,出问题是不容易修复,但是节省空间。
压缩表是由myisampack工具创建的,占用更少的磁盘空间。
mysql> create table test6 (username char(20)) engine=myiisam;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
这样我们创建了一个静态表.
ysql> insert into test6 values('aaa'),(' aaa'),('aaa '),(' aaa ');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
我们往表里面插入了4条数据。
mysql> select username,length(username) from test6;
+----------+------------------+
| username | length(username) |
+----------+------------------+
| aaa | 3 |
| aaa | 4 |
| aaa | 3 |
| aaa | 4 |
+----------+------------------+
4 rows in set (0.00 sec)
查询到结果看到字段后面的空格已经被自动去掉了。
默认的myisam存储引擎表是静态表,但是如果这个字段是一个可变长的自动(如varchar,varbinary,blob或者text)会有个警告信息,指定的表格是将被忽略。
在创建表的时候可以使用row_format=fixed或者dynamic参数,指定是静态表还是动态表。前提是字段的类型要是正确的。
动态表使用过程中容易产生碎片,需要使用optimize table语句,或者myisamchk命令消除碎片,提高性能。
mysql> select table_name,engine,data_free from information_schema.tables where engine='myisam'and data_free>0;
+------------+--------+-----------+
| table_name | engine | data_free |
+------------+--------+-----------+
| user | MyISAM | 88 |
+------------+--------+-----------+
1 row in set, 1 warning (1.12 sec)
mysql> optimize table mysql.user;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| mysql.user | optimize | status | OK |
+------------+----------+----------+----------+
1 row in set (0.00 sec)
mysql> select table_name,engine,data_free from information_schema.tables where engine='myisam'and data_free>0;
Empty set, 1 warning (0.62 sec)
mysql>
user权限表data_free大于0说明有碎片,optimize table执行过后碎片消除。
myisam存储引擎还是有一些其他的特性,比如一个表限制有(1.844E+19)行,每个表支持64给索引等等。
转载于:https://blog.51cto.com/itlaowu/1059564