存储引擎概念
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。
每种数据库的数据格式,内部实现机制都是不同的,
要利用一种开发工具访问一种数据库,就必须通过一种中介程序,
这种开发工具与数据库之间的中介程序就叫数据库引擎
。
有哪些存储引擎
存储引擎主要有: 1. MyIsam , 2. Mrg_Myisam, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 InnoDB
mysql> show engines\G
*************************** 1. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 9. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
9 rows in set (0.00 sec)
存储引擎的主要特性
数据库事务(Database Transaction) ,是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元,可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务,必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。事务是数据库运行中的逻辑工作单位,由DBMS中的事务管理子系统负责事务的处理。
二、主键、外键和索引的区别
定义:
主键--唯一标识一条记录,不能有重复的,不允许为空
外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引--该字段没有重复值,但可以有一个空值
作用:
主键--用来保证数据完整性
外键--用来和其他表建立联系用的
索引--是提高查询排序的速度
个数:
主键--主键只能有一个
外键--一个表可以有多个外键
索引--一个表可以有多个唯一索引
1. MyIsam
MyIsam 存储引擎独立于操作系统,也就是可以在windows上使用,也可以比较简单的将数据转移到linux操作系统上去。这种存储引擎在创建表的时候,会创建三个文件,一个是.frm文件用于存储表的定义,一个是.MYD文件用于存储表的数据,另一个是.MYI文件,存储的是索引。操作系统对大文件的操作是比较慢的,这样将表分为三个文件,那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。
1. 不支持事务,但是并不代表着有事务操作的项目不能用MyIsam存储引擎,可以在service层进行根据自己的业务需求进行相应的控制。
2. 不支持外键。
3. 查询速度很快,如果数据库insert和update的操作比较多的话比较适用。
4. 对表进行加锁。
2. Mrg_Myisam
Merge存储引擎,是一组MyIsam的组合,也就是说,他将MyIsam引擎的多个表聚合起来,但是他的内部没有数据,真正的数据依然是MyIsam引擎的表中,但是可以直接进行查询、删除更新等操作。
比如:我们可能会遇到这样的问题,同一种类的数据会根据数据的时间分为多个表,如果这时候进行查询的话,就会比较麻烦,Merge可以直接将多个表聚合成一个表统一查询,然后再删除Merge表(删除的是定义),原来的数据不会影响。
3. Memory:功能上等于Misam
Memory采用的逻辑介质是内存,响应速度应该是很快的,但是当mysqld守护进程崩溃的时候数据会丢失,另外,要求存储的数据是数据长度不变的格式,比如,Blob和Text类型的数据不可用(长度不固定的)。
使用Memory存储引擎情况:
1. 目标数据比较小,而且非常频繁的进行访问,在内存中存放数据,如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小,限制Memory表的最大的大小。
2. 如果数据是临时的,而且必须立即可用得到,那么就可以放在内存中。
3. 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。
【注】 Memory同时支持散列索引和B树索引,B树索引可以使用部分查询和通配查询,也可以使用<,>和>=等操作符方便数据挖掘,散列索引相等的比较快但是对于范围的比较慢很多。
4. Blackhole
“黑洞”存储引擎,他会丢弃所有的插入的数据,服务器会记录下Blackhole表的日志,所以可以用于复制数据到备份数据库。看其他的一些资料说:可以用来充当dummy master,利用blackHole充当一个“dummy master”来减轻master的负载,对于master来说“dummy master” 还是一个slave的角色,还有充当日志服务器等等。
5. CSV
可以将scv文件作为MySql的表来使用,但是不支持索引。CSV引擎表所有的字段都必须为非空的,创建的表有两个一个是CSV文件和CSM文件。
6. Performance_Schema
MySQL5.5以后新增了一个存储引擎,就是Performance_Schema,他主要是用来收集数据库服务器的性能参数。MySQL用户不能创建存储该类型的表。
他提供了以下的功能:
1. 提供进程等待的详细信息,包括锁、互斥变量、文件信息。
2. 保存历史的事件汇总信息,为Mysql服务器的性能做出详细的判断。
3. 对于新增和删除监控时间点都非常容易,并可以随意的改变Mysql服务器的监控周期
需要在配置文件my.cnf中进行配置才能开启。
7. Archive
archive是归档的意思,仅仅支持插入和查询两种功能,在MySQL5.5以后支持索引功能,他拥有很好的压缩机制,使用zlib压缩库,在记录请求的时候实时的进行压缩,经常被用来作为仓库使用。适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度但是对查询的支持较差。
8. Federated
Federated存储引擎是访问MySQL服务器的一个代理,尽管该引擎看起来提供了一个很好的跨服务器的灵活性,但是经常带来问题,默认是禁用的。
9. InnoDB
InnoDB是一个事务型的存储引擎,有行级锁定和外键约束,适用于以下的场合:
1. 更新多的表,适合处理多重并发的更新请求。
2. 支持事务。
3. 可以从灾难中恢复(通过bin-log日志等)。
4. 外键约束。只有他支持外键。
5. 支持自动增加列属性auto_increment。
MyISam和InnoDB实例比较
这里我选择两个比较重点的存储引擎实验下速度之类的性能,对比一下看看。
1. 创建两张表分别以MyIsam和InnoDB作为存储引擎。
- create table testMyIsam(
- -> id int unsigned primary key auto_increment,
- -> name varchar(20) not null
- -> )engine=myisam;
- create table testInnoDB( id int unsigned primary key auto_increment, name varchar(20) not null )engine=innodb;
两张表内容是一致的但是存储引擎不一样,下面我们从插入数据开始进行测试比较。
这里当然不能手动的插入,创建一个存储过程插入一百万的数据。
- mysql> create procedure insertMyIsam()
- -> begin
- -> set @i = 1;
- -> while @i <= 1000000
- -> do
- -> insert into testMyIsam(name) values(concat("wy", @i));
- -> set @i = @i + 1;
- -> end while;
- -> end//
- mysql> create procedure insertInnoDB()
- -> begin
- -> set @i = 1;
- -> while @i <= 1000000
- -> do
- -> insert into testInnoDB(name) values(concat("wy", @i));
- -> set @i = @i + 1;
- -> end while;
- -> end//
插入(一百万条)MyIsam存储引擎的表中的时间如下:
- mysql> call insertMyIsam;
- -> //
- Query OK, 0 rows affected (49.69 sec)
插入(一百万条)InnoDB存储引擎的表中的时间如下:
- mysql> create procedure insertInnoDB()
- -> begin
- -> set @i = 1;
- -> while @i <= 1000000
- -> do
- -> insert into testInnoDB(name) values(concat("wy", @i));
- -> set @i = @i + 1;
- -> end while;
- -> end//
- Query OK, 0 rows affected (0.00 sec)
- mysql> call insertInnoDB;
- -> //
- Query OK, 0 rows affected (1 hour 38 min 14.12 sec)
我的心情是复杂的,这里当时默认的是开启了自动提交事务了,所以执行速度很慢,可以先将自动提交关闭,然后再调用存储过程插入一百万的数据,执行完成之后再开启自动提交,这样会快很多。
- mysql> set autocommit = 0;
- Query OK, 0 rows affected (0.00 sec)
- mysql> call insertInnoDB;
- Query OK, 0 rows affected (36.52 sec)
- mysql> set autocommit = 1;
- Query OK, 0 rows affected (5.72 sec)
3. 查询数据总数目
下面是InnoDB的SQL语句的分析:
- mysql> desc select count(*) from testInnoDB\G;
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: testInnoDB
- type: index
- possible_keys: NULL
- key: PRIMARY
- key_len: 4
- ref: NULL
- rows: 997134
- Extra: Using index
- 1 row in set (0.03 sec)
- mysql> desc select count(*) from testMyIsam\G;
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: NULL
- type: NULL
- possible_keys: NULL
- key: NULL
- key_len: NULL
- ref: NULL
- rows: NULL
- Extra: Select tables optimized away
- 1 row in set (0.00 sec)
4. 查询某一范围的数据
4.1 没有索引的列
- mysql> select * from testMyIsam where name > "wy100" and name < "wy100000";+-------+---------+
- | id | name |
- +-------+---------+
- | 1000 | wy1000 |
- | 10000 | wy10000 |
- +-------+---------+
- 2 rows in set (0.43 sec)
- mysql> select * from testInnoDB where name > "wy100" and name < "wy100000";+-------+---------+
- | id | name |
- +-------+---------+
- | 1000 | wy1000 |
- | 10000 | wy10000 |
- +-------+---------+
4.2 有索引的列
对于使用MyIsam存储引擎的表:
- <pre name="code" class="sql">select * from testMyIsam where id > 10 and id < 999999;
- <pre name="code" class="sql">999988 rows in set (0.91 sec)
- select * from testInnoDB where id > 10 and id < 999999;
- 999988 rows in set (0.69 sec)
但是好像我没看出来多大的差距,可能是数据太少的原因吧。
- mysql> select * from testInnoDB where name = "wy999999";
- +--------+----------+
- | id | name |
- +--------+----------+
- | 999999 | wy999999 |
- +--------+----------+
- 1 row in set (0.20 sec)
- mysql> select * from testMyIsam where name = "wy999999";
- +--------+----------+
- | id | name |
- +--------+----------+
- | 999999 | wy999999 |
- +--------+----------+
- 1 row in set (0.16 sec)