mysql-存储引擎

存储引擎相当于Linux文件系统，只不过比文件系统强大；
具备数据读写、数据安全和一致性、提高性能、热备份、自动故障恢复、高可用方面支持；
innodb压缩比较高,数据插入性能极高

###存储引擎的种类
InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
mysql> select table_schema,table_name,engine from  information_schema.tables where engine <>'';##查询数据库内现有表的存储引擎
###存储引擎的查看
show engines;
存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB    #监控使用
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

InnoDB个MyISAM存储引擎的替换

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数

innodb存储引擎介绍

#innodb优点
1、支持事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )

 

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数

innodb存储引擎介绍

#innodb优点
1、支持事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )

 

锁

#“锁”顾名思义就是锁定表数据，别人不能再修改的意思。
#“锁”的作用：在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)，谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁

隔离级别

影响到数据的读取,默认的级别是 RR模式.
mysql> select @@transaction_isolation; #隔离级别(参数)
mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ | #可在线修改
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
负责的是,MVCC,读一致性问题
RU  : 读未提交,可脏读,一般部议叙出现
RC  : 读已提交,可能出现幻读,可以防止脏读.
RR  : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
## RR 在同一会话下窗口1更新数据提交后再更新窗口2查到的数据是不变的
SR   : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
例如:
[world]>select * from city where id=999 for update;
[world]>commit;

缓冲区

select @@innodb_buffer_pool_size;#查询
show engine innodb status\G
innodb_buffer_pool_size #参数
一般建议最多是物理内存的 75-80%

innodb_flush_log_at_trx_commit (双一标准之一)

#定义：主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
#参数查询 select @@innodb_flush_log_at_trx_commit;
##参数说明.是一个安全参数，在5.6以上默认的参数
1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush到操作系统的文件系统缓存  fsync到物理磁盘.（redo buffer -->os buffer --磁盘），每个事务都提交
0，表示当事务提交时，不立即进行日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且每秒fsync磁盘一次；（一秒一提交）
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。（redo buffer -->每事务>os buffer --每秒-->磁盘）#处于0和1之间的一种标准
--------
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
-------

Innodb_flush_method=(O_DIRECT, fdatasync)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method

 

#定义：控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存
#查询 show variables like '%innodb_flush%';
#查询说明
O_DIRECT  :数据缓冲区写磁盘,不走OS buffer，日志写走osbuffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer
# 修改方式：只读参数，写配置文件，重启数据库

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync
#redo日志有关的参数
innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3
Innodb_buffer_pool_size = 物理内存的50-80 左右 一个mysql实例时