首页 > 代码库 > [转]MySQL数据库引擎

[转]MySQL数据库引擎

2024-08-11 02:39:08   216人阅读

经常用MySQL数据库，但是，你在用的时候注意过没有，数据库的存储引擎，可能有注意但是并不清楚什么意思，可能根本没注意过这个问题，使用了默认的数据库引擎，当然我之前属于后者，后来成了前者，然后就有了这篇博文啦，希望可以帮助部分人了解MySQL引擎的一些特性。

 

存储引擎概念

 
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
 

有哪些存储引擎

 

存储引擎主要有： 1. MyIsam , 2. Mrg_Myisam, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 InnoDB

 

[sql] view plain copy
 
 技术分享技术分享
  1. mysql> show engines\G  
  2. *************************** 1. row ***************************  
  3.       Engine: MyISAM  
  4.      Support: YES  
  5.      Comment: MyISAM storage engine  
  6. Transactions: NO  
  7.           XA: NO  
  8.   Savepoints: NO  
  9. *************************** 2. row ***************************  
  10.       Engine: MRG_MYISAM  
  11.      Support: YES  
  12.      Comment: Collection of identical MyISAM tables  
  13. Transactions: NO  
  14.           XA: NO  
  15.   Savepoints: NO  
  16. *************************** 3. row ***************************  
  17.       Engine: MEMORY  
  18.      Support: YES  
  19.      Comment: Hash based, stored in memory, useful for temporary tables  
  20. Transactions: NO  
  21.           XA: NO  
  22.   Savepoints: NO  
  23. *************************** 4. row ***************************  
  24.       Engine: BLACKHOLE  
  25.      Support: YES  
  26.      Comment: /dev/null storage engine (anything you write to it disappears)  
  27. Transactions: NO  
  28.           XA: NO  
  29.   Savepoints: NO  
  30. *************************** 5. row ***************************  
  31.       Engine: CSV  
  32.      Support: YES  
  33.      Comment: CSV storage engine  
  34. Transactions: NO  
  35.           XA: NO  
  36.   Savepoints: NO  
  37. *************************** 6. row ***************************  
  38.       Engine: PERFORMANCE_SCHEMA  
  39.      Support: YES  
  40.      Comment: Performance Schema  
  41. Transactions: NO  
  42.           XA: NO  
  43.   Savepoints: NO  
  44. *************************** 7. row ***************************  
  45.       Engine: ARCHIVE  
  46.      Support: YES  
  47.      Comment: Archive storage engine  
  48. Transactions: NO  
  49.           XA: NO  
  50.   Savepoints: NO  
  51. *************************** 8. row ***************************  
  52.       Engine: FEDERATED  
  53.      Support: NO  
  54.      Comment: Federated MySQL storage engine  
  55. Transactions: NULL  
  56.           XA: NULL  
  57.   Savepoints: NULL  
  58. *************************** 9. row ***************************  
  59.       Engine: InnoDB  
  60.      Support: DEFAULT  
  61.      Comment: Supports transactions, row-level locking, and foreign keys  
  62. Transactions: YES  
  63.           XA: YES  
  64.   Savepoints: YES  
  65. 9 rows in set (0.00 sec)  

 

 


存储引擎的主要特性


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

 
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作为存储引擎。
 
[sql] view plain copy
 
 技术分享技术分享
  1. create table testMyIsam(  
  2.     -> id int unsigned primary key auto_increment,  
  3.     -> name varchar(20) not null  
  4.     -> )engine=myisam;  

[sql] view plain copy
 
 技术分享技术分享
  1. create table testInnoDB( id int unsigned primary key auto_increment, name varchar(20) not null )engine=innodb;  

两张表内容是一致的但是存储引擎不一样，下面我们从插入数据开始进行测试比较。
 
 

2.插入一百万数据


这里当然不能手动的插入，创建一个存储过程插入一百万的数据。
[sql] view plain copy
 
 技术分享技术分享
  1. mysql> create procedure insertMyIsam()  
  2.     -> begin  
  3.     -> set @i = 1;  
  4.     -> while @i <= 1000000  
  5.     -> do  
  6.     -> insert into testMyIsam(name) values(concat("wy", @i));  
  7.     -> set @i = @i + 1;  
  8.     -> end while;  
  9.     -> end//  
[sql] view plain copy
 
 技术分享技术分享
  1. mysql> create procedure insertInnoDB()  
  2.     -> begin  
  3.     -> set @i = 1;  
  4.     -> while @i <= 1000000  
  5.     -> do  
  6.     -> insert into testInnoDB(name) values(concat("wy", @i));  
  7.     -> set @i = @i + 1;  
  8.     -> end while;  
  9.     -> end//  


插入（一百万条）MyIsam存储引擎的表中的时间如下：
[sql] view plain copy
 
 技术分享技术分享
  1. mysql> call insertMyIsam;  
  2.     -> //  
  3. Query OK, 0 rows affected (49.69 sec)  
 
插入（一百万条）InnoDB存储引擎的表中的时间如下：
[sql] view plain copy
 
 技术分享技术分享
  1. mysql> create procedure insertInnoDB()  
  2.     -> begin  
  3.     -> set @i = 1;  
  4.     -> while @i <= 1000000  
  5.     -> do   
  6.     -> insert into testInnoDB(name) values(concat("wy", @i));  
  7.     -> set @i = @i + 1;  
  8.     -> end while;  
  9.     -> end//  
  10. Query OK, 0 rows affected (0.00 sec)  
  11.   
  12. mysql> call insertInnoDB;  
  13.     -> //  
  14. Query OK, 0 rows affected (1 hour 38 min 14.12 sec)  

我的心情是复杂的，这里当时默认的是开启了自动提交事务了，所以执行速度很慢，可以先将自动提交关闭，然后再调用存储过程插入一百万的数据，执行完成之后再开启自动提交，这样会快很多。
[sql] view plain copy
 
  1. mysql> set autocommit = 0;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.   
  4. mysql> call insertInnoDB;  
  5. Query OK, 0 rows affected (36.52 sec)  
  6.   
  7. mysql> set autocommit = 1;  
  8. Query OK, 0 rows affected (5.72 sec)  


 
 

3. 查询数据总数目

 
 
下面是InnoDB的SQL语句的分析：
[sql] view plain copy
 
 技术分享技术分享
  1. mysql> desc select count(*) from testInnoDB\G;  
  2. *************************** 1. row ***************************  
  3.            id: 1  
  4.   select_type: SIMPLE  
  5.         table: testInnoDB  
  6.          type: index  
  7. possible_keys: NULL  
  8.           key: PRIMARY  
  9.       key_len: 4  
  10.           ref: NULL  
  11.          rows: 997134  
  12.         Extra: Using index  
  13. 1 row in set (0.03 sec)  
 
下面是MyIsam（他的数据总数存储在其他的表中所以这里是没有影响行数的）的SQL语句的分析：
[sql] view plain copy
 
 技术分享技术分享
  1. mysql> desc select count(*) from testMyIsam\G;  
  2. *************************** 1. row ***************************  
  3.            id: 1  
  4.   select_type: SIMPLE  
  5.         table: NULL  
  6.          type: NULL  
  7. possible_keys: NULL  
  8.           key: NULL  
  9.       key_len: NULL  
  10.           ref: NULL  
  11.          rows: NULL  
  12.         Extra: Select tables optimized away  
  13. 1 row in set (0.00 sec)  
 


4. 查询某一范围的数据

 
 

4.1 没有索引的列

[sql] view plain copy
 
 技术分享技术分享
  1. mysql> select * from testMyIsam where name > "wy100" and name < "wy100000";+-------+---------+  
  2. | id    | name    |  
  3. +-------+---------+  
  4. |  1000 | wy1000  |  
  5. | 10000 | wy10000 |  
  6. +-------+---------+  
  7. 2 rows in set (0.43 sec)  
  8.   
  9. mysql> select * from testInnoDB where name > "wy100" and name < "wy100000";+-------+---------+  
  10. | id    | name    |  
  11. +-------+---------+  
  12. |  1000 | wy1000  |  
  13. | 10000 | wy10000 |  
  14. +-------+---------+  

4.2 有索引的列
 
对于使用MyIsam存储引擎的表：
[sql] view plain copy
 
 技术分享技术分享
  1. <pre name="code" class="sql">select * from testMyIsam where id > 10 and id < 999999;  
执行时间：
[sql] view plain copy
 
 技术分享技术分享
  1. <pre name="code" class="sql">999988 rows in set (0.91 sec)  
对于使用了InnoDB存储引擎的表：
[sql] view plain copy
 
 技术分享技术分享
  1. select * from testInnoDB where id > 10 and id < 999999;  
[sql] view plain copy
 
 技术分享技术分享
  1. 999988 rows in set (0.69 sec)  

但是好像我没看出来多大的差距，可能是数据太少的原因吧。
 
[sql] view plain copy
 
 技术分享技术分享
  1. mysql> select * from testInnoDB where name = "wy999999";  
  2. +--------+----------+  
  3. | id     | name     |  
  4. +--------+----------+  
  5. | 999999 | wy999999 |  
  6. +--------+----------+  
  7. 1 row in set (0.20 sec)  
  8.   
  9. mysql> select * from testMyIsam where name = "wy999999";  
  10. +--------+----------+  
  11. | id     | name     |  
  12. +--------+----------+  
  13. | 999999 | wy999999 |  
  14. +--------+----------+  
  15. 1 row in set (0.16 sec)  
 
[转]MySQL数据库引擎






























        

            
                
                  
                
                

                    联系

                    我们