Mysql的两种引擎的区别

Mysql的两种引擎的区别

 作者： Suyeol 

Innodb引擎概述（非默认，支持事务，行锁和外键，适用大容量并发高的数据库）

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

acid是啥？

（数据库事务正确执行的四个基本要素的缩写）

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

MyISAM引擎概述（默认，不支持事务，不支持行锁，优点count时不用全表扫，适用读多的）

MyISAM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择。

简单介绍区别：

1、MyISAM是非事务安全的，而InnoDB是事务安全的

2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁

3、MyISAM支持全文类型索引，而InnoDB不支持全文索引

4、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM

5、MyISAM表保存成文件形式，跨平台使用更加方便

应用场景：

应用场景	读写分离
master库	InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB
slave库	MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyISAM

参考文档：https://blog.youkuaiyun.com/justdb/article/details/17331569

https://my.oschina.net/sohanyu/blog/1068534

 

读写分离场景

一般应用对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，有一个思路就是说采用数据库集群的方案， 基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中 的从数据库。 当然，主服务器也可以提供查询服务。使用读写分离最大的作用无非是环境服务器压力

读写分离的好处（资料）：

1.MySQL复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务，因此不能光看性能，也就是说1主1从也是可以的。

2.多机器(集群)的处理能力

3.对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟。

4.主从只负责各自的写和读，极大程度的缓解X锁和S锁争用

5.从库可配置myisam引擎，提升查询性能以及节约系统开销

6.从库同步主库的数据和主库直接写还是有区别的，通过主库发送来的binlog恢复数据，但是，最重要区别在于主库向从库发送binlog是异步的，从库恢复数据也是异步的

7.读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。 对于写和读比例相近的应用，应该部署双主相互复制

8.可以在从库启动是增加一些参数来提高其读的性能，例如--skip-innodb、--skip-bdb、--low-priority-updates以及--delay-key-write=ALL。当然这些设置也是需要根据具体业务需求来定得，不一定能用上

9.分摊读取。假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1 分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不 承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白 了就是拿机器和带宽换性能。MySQL官方文档中有相关演算公式：官方文档 见6.9FAQ之“MySQL复制能够何时和多大程度提高系统性能”

###原理 MySQL主(称master)从(称slave)复制的原理：

master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件，binary log events)

slave将master的binary log events拷贝到它的中继日志(relay log)

slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

###注意 主DB server和从DB server数据库的版本一致

主DB server和从DB server数据库数据一致，这里就会可以把主的备份在从上还原，也可以直接将主的数据目录拷贝到从的相应数据目录

主DB server开启二进制日志,主DB server和从DB server的server_id及auto.cnf中的UUID都必须唯一

###操作

1、主从服务器分别作以下操作：

  1.1、版本一致
  1.2、初始化表，并在后台启动mysql
  1.3、修改root的密码

2、修改主服务器Master的MySQL配置文件

$ vi /etc/my.cnf（默认）
[mysqld]
log-bin=mysql-bin     //[必须]启用二进制日志
server-id=93      //[必须]服务器唯一ID，默认是1，一般取IP最后一段
# 指定同步的数据库， 如果 不指定则同步全部数据库
binlog-do-db= testdb

3、修改从服务器slave

$ vi /etc/my.cnf
[mysqld]
server-id=211      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4、重启两台服务器的MySQL

$ /etc/init.d/mysql restart
或
$ service mysqld restart

5、在主服务器上建立帐户并授权slave:

$ /usr/local/mysql/bin/mysql -uroot -p 或直接 mysql -u root -p
mysql>GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by '123456'; 
mysql>flush privileges;
//一般不用root帐号，%表示所有客户端都可能连，只要帐号、密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。

6、登录主服务器的MySQL，查询Master的状态

   mysql>show master status\G;

   +-------------+----------+--------------+------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +-------------+----------+--------------+------------+
   | mysql-bin.000004 |      308 |              |                  |
   +-------------+----------+--------------+------------+
   1 row in set (0.00 sec)

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

7、配置从服务器Slave：

mysql>change master to master_host='192.168.145.222',master_user='slave',master_password='123456',
master_log_file='mysql-bin.000004',master_log_pos=308; 

//注意不要断开，308数字前后无单引号。
Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器(Slave)复制功能状态：

  mysql> show slave status\G

   *************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服务器地址
              Master_User: slave   //授权帐户名，尽量避免使用root
              Master_Port: 3306    //数据库端口，部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600   //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES
                    ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

以上操作过程，主从服务器配置完成。

10、监控： 据说可以编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了。

###问题 配置mysql主从时，由于是阿里云镜像系统盘拷贝的MySQL目录，导致主从MySQL UUID相同， Slave_IO无法启动，报错信息如下：

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决办法:

mysql>show variables like '%log_error%';//查看配置的日志地址，查看日志

修改MySQL data 目录下auto.cnf 文件中uuid的值，使两台MySQL不同即可，

$ find / -name auto.cnf 或 find / -name "auto.cnf"

修改后重启MySQL服务。