mysql各种引擎对比、实战

发布于 2020-08-20 10:58:44

发布于 2020-08-20 10:58:44

文章被收录于专栏：sunskysunsky

为什么要合理选择数据库存储引擎
定义
存储引擎作用
如何修改数据库引擎
怎么查看修改成功

InnoDB
- 适用场景
- MySQL官方对InnoDB的讲解
MyIsam
- 适用场景
- 补充：ISAM索引方法–索引顺序存取方法
Memory（也叫HEAP）堆内存嘛
Mrg_Myisam：（分表的一种方式–水平分表）
Blackhole（黑洞引擎）

使用的效果与区别展示
效果对比总述
使用建议

MyIASM引擎的索引结构
InnoDB引擎的索引结构

Mrg_Myisam引擎分表
项目中如何使用MRG_MYISAM总表
MRG_MYISAM分表的优点
使用MRG_MyISAM分表的必须思考问题

create table mytbl(   
    id int primary key,   
    name varchar(50)   
)type=MyISAM;

alter table table_name type = InnoDB;

show table status from table_name;

show create table table_name

//这个就是select锁表的一种，不明确主键。增删改查都可能会导致锁全表，在以后我们会详细列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

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;

//创建存储过程
delimiter $$
drop procedure if exists ptestmyisam;
create procedure ptestmyisam()
begin
declare pid int ;
set pid = 1000000;
while pid>0 
do
insert into testmyisam(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$

//使用存储过程：
call ptestmyisam();

//创建存储过程（尽量把Innodb的数量级压低，不然，，卡在那里半天也不奇怪）
delimiter $$
drop procedure if exists ptestInndb;
create procedure ptestInndb()
begin
declare pid int ;
set pid = 1000000;
while pid>0 
do
insert into testinnodb(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$

//使用存储过程：
call ptestInndb();

//停掉事务
set autocommit = 0;  
//调用存储过程
call ptestInndb; 
//重启事务
set autocommit = 1;

//耗时3秒多
update testinnodb set name = 'fuzhu' where id>0 and id<10000;
//耗时0.171秒
update testmyisam set name = 'fuzhu' where id>0 and id<13525;

select count(*) from testInnoDB;  

select count(*) from testMyIsam;

select * from testMyIsam where name > "fuzhu100" ;

select * from testInnoDB where name > "fuzhu100" ;

select * from testMyIsam where  id > 10 ;
select * from testinnodb where  id > 10 ;

//用于把表的拷贝从主服务器转移到从属服务器。
LOAD TABLE tbl_name FROM MASTER

CREATE TABLE tbHeap (
id int unsigned primary key auto_increment,  
name varchar(20) not null  
) TYPE=Heap

//用户表一
CREATE TABLE IF NOT EXISTS `user1` (  
 `id` int(11) NOT NULL ,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

//用户表二
CREATE TABLE IF NOT EXISTS `user2` (  
 `id` int(11) NOT NULL ,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

//分别插入两条测试数据先
INSERT INTO `user1` (`name`) VALUES('辅助');  
INSERT INTO `user2` (`name`) VALUES('JackFrost');

CREATE TABLE IF NOT EXISTS `alluser` (  
  `id` int(11) NOT NULL ,  
  `name` varchar(50) DEFAULT NULL,  
   PRIMARY KEY (`id`)
) ENGINE=MRG_MYISAM  
DEFAULT CHARSET=utf8 
UNION=(user1,user2)  ;

select id,name from alluser;

//就是插入总表的时候，其实也是插入到第一个分表。
ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = FIRST;


INSERT INTO `alluser` (id,`name`) VALUES(2,'插入到第一个分表');

//就是插入总表的时候，其实也是插入到最后一个分表。
ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = LAST;

CREATE TABLE `create_id` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

insert into create_id () values();

INSERT INTO {$table_name} (id, name) VALUES ('{$id}');

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018-01-12 ，如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自作者个人站点/博客前往查看

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

mysql各种引擎对比、实战

mysql各种引擎对比、实战

1）存储引擎概述；

（2）MySQL各大存储引擎；

（3）InnoDB和MyIsam使用及其原理对比；

（4）InnoDB和MyIsam引擎原理；

（5）剩余引擎的使用DEMO（主要是Mrg_Myisam分表）；

文章目录：（例子下载在最下方）

（1）存储引擎概述

（2）MySQL各大存储引擎

（3）InnoDB和MyIsam使用及其原理对比

（4）InnoDB和MyIsam引擎原理

（5）剩余引擎的使用DEMO（主要是Mrg_Myisam分表）

一、存储引擎的概述：

基于此博客MySQL数据库引擎去扩展，不过每个我都会用例子去验证，并写出的见解。

（1）为什么要合理选择数据库存储引擎：

（2）定义：

（3）存储引擎作用：

1）设计并创建数据库以保存系统所需的关系或XML文档。

2）实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用SQL Server工具和实用工具以使用数据的过程。

3）为单位或客户部署实现的系统。

4）提供日常管理支持以优化数据库的性能。

（4）如何修改数据库引擎：

方式一：

修改配置文件my.ini

将mysql.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB

方式二：

在建表的时候指定

方式三：

建表后更改

（5）怎么查看修改成功？

方式一：

方式二：

方式三：

使用数据库管理工具啊。

二、MySQL各大存储引擎：

最好先看下你下的MySQL支持什么数据库引擎

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

但是我们主要分析使用MyIsam 和InnoDB。其余略微带过，详情请分别百度。

（1）InnoDB：

定义：（默认的存储引擎）

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

适用场景：

1）经常更新的表，适合处理多重并发的更新请求。

2）支持事务。

3）可以从灾难中恢复（通过bin-log日志等）。

4）外键约束。只有他支持外键。

5）支持自动增加列属性auto_increment。

MySQL官方对InnoDB的讲解：

1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。

2）InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。

3）InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

4）InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

5） InnoDB被用来在众多需要高性能的大型数据库站点上产生。

补充：什么叫事务？简称ACID

C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.

I 独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.

D 持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.

（2）MyIsam：

定义：

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

适用场景：

1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。

2）不支持外键的表设计。

3）查询速度很快，如果数据库insert和update的操作比较多的话比较适用。

4）整天 对表进行加锁的场景。

5）MyISAM极度强调快速读取操作。

6）MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

缺点：

就是不能在表损坏后恢复数据。（是不能主动恢复）

补充：ISAM索引方法–索引顺序存取方法

定义：

是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。

特性：

ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。

在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。—ISAM是一种静态索引结构。

缺点：

1.它不 支持事务处理

2.也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

4）整天对表进行加锁的场景。

是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。

1.它不支持事务处理

2.也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

任何写入到此引擎的数据均会被丢弃掉，不做实际存储；Select语句的内容永远是空。

6）锁的支持。MyISAM只支持表锁。InnoDB支持表锁、行锁行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的