ITPub博客

mysql学习6:第三章MYSQL 体系结构与存储引擎

翻译 MySQL 作者:studywell 时间:2018-11-05 18:15:47 2 删除 编辑

1.   第三章MYSQL 体系结构与存储引擎

1.1.   mysql体系结构

体系结构分为两大层:

l   mysql server层:包括连接层,SQL层;

l   存储引擎层;

 

 

 

1.2.   query cache详解及关闭

query cache只能缓存静态数据。适用于数据仓库。

5.6前默认开启,5.6后默认关闭。

彻底关闭query cache

涉及query_cache的两个核心参数

mysql>show variables like “%query_cache_size%”

mysql>show variables like “$query_cache_type%”

必须一开始就将query_cache_type设置未off

 

备注:

压力测试工具sysbench是一个开源的、模块化、跨平台的多线程性能测试工具,可进行CPU、内存、磁盘IO、线程、数据库的性能测试。,支持mysql,oracle,postgresql

下载地址:https://dev.mysql.com/downloads/benchmarks.html

具体测试过程见书  p44

1.3.   存储引擎

存储引擎MyISAM建议停用,转换到InnoDB存储引擎上。

1.4.   innoDB体系结构

1.4.1.   数据库和数据库实例

mysql数据库是单进程多线程模型的数据库

l   实例是进程加内存的组合。

l   线程:将内存数据刷新到硬盘上。

l   磁盘文件:redolog,数据文件,Undo log

1.4.2.   InnoDB的存储结构

InnoDB逻辑存储单元主要分为表空间,段,区,页。

层级关系:tablespace>segment>extent(64个page,1M)>page

表空间

       系统表空间;ibdata1

 

共享表空间  独立表空间更好

独立表空间:每表有自己表空间,可实现表空间转移,回收方便。alter table tablename engine=innodb 或pt-online_schema_change即可

共享表空间不能在线回收空间,需导出删除再导入,统计分析、日志类不适合。

mysql 5.7后多了临时表空间,通用表空间

 

 

数据段,回滚段,索引段。

每个段由N个区和32个零散的页组成。

创建一个索引会创建两个段,分别为非叶子节点和叶子节点段。

一个表有4个段,是索引个数的两倍。

 

连续的页组成,物理上连续分配的一段空间,每个区大小固定1M。

 

InnoDB的最小物理存储分配单位是page,有数据页、回滚页,一般情况一个区由64个连续的页组成,页默认16KB。

mysql5.6 可调低为8KB或4KB

mysql5.7 可调高为32KB或64KB

页记录行记录的信息

InnoDB存储引擎面向列,也就是数据按照行存储,行记录数据按照行格式进行存放。

InnoDB存储引擎有两种文件格式

1.      Antelope:有corrpact和redundant两种行记录格式

2.      Barracuda:有commpressed和dynamic两种行记录格式。

 

行溢出: 需要存储的数据再当前存储页面之外,拆分到多个页进行存储。

redundant:最早的行记录格式,比compact要消耗更多的存储空间,不建议使用。

commpressed:压缩行格式,对物理存储层面上的数据库和索引页进行压缩,内存不压缩,调用到内存需转换,消耗CPU,压缩率只有1/2,不建议使用。

compact:溢出的列只存放768各前缀字节。

dynamic:新版本默认的行记录格式,溢出数据存放溢出页中,数据页只存指针。溢出列所在的新页利用率高,推荐使用。

mysql5.7 默认使用dynamic行记录格式和Barracuda文件格式;

 

通过查看参数row_format可查看

show table status like ‘%user%’\G;

row_format

查看文件格式

show variables like ‘%innodb_file%’;

innodb_file_format  

 

1.4.3.   内存结构

内存分为SGA(系统全局区)和PGA(程序缓存区),

查看配置参数:show variables like ‘%buffer%’

参数介绍,见p54

 

SGA(系统全局区)

innodb_buffer_pool:缓存InnoDB表的数据、索引、插入缓冲、数据字典等信息;

innodb_log_buffer:事务在内存中的缓冲,即redo log buffer的大小;

Query Cache:高速查询缓存,生产环境建议关闭。只能缓存静态数据。适用于数据仓库。

key_buffer_size:只用于MyISAM存储引擎表,缓存MyISAM存储。

innodb_additional_mem_pool_size:保存数据字典信息和其他内部数据结构的内存池大小,在5.7.4中被移除。

 

SGA(程序缓冲区)

sort_buffer_size:用于SQL语句在内存中的临时排序。

join_buffer_size:表连接使用,用于BKA

read_buffer_size:表顺序扫描的缓存,只能用于MyISAM存储引擎。

read_rnd_buffer_size:随机读缓冲区大小,用于做mrr。

 

特别的

tmp_table_size:SQL语句在排序或者分组时没有用到索引,就使用临时表空间。

max_heap_table_size:管理heap、memory存储引擎表。

查询参数:

[mysql]>show variables like'%heap%';

[mysql]>show variables like'%tmp_table%'

两个参数以最小为准,建议设置一样大。

 

default_tmp_storage_engine:临时表默认的存储引擎;

interal_tmp_disk_storage_engine:磁盘临时表的管理,决定(create temporary table),5.7新增。

1.4.4.   Buffer状态及其链表结构

page是InnoDB磁盘的IO最小单位,对应到内存中是一个个buffer,每个buffer分为3种状态:

l   free buffer::未被使用。

l   clean buffer:buffer数据和磁盘page数据一致。

l   dirty buffer:内存数据还未写入到磁盘。

三种不同的buffer衍生三条链表

1.4.5.   各大刷新线程及其作用

master thread线程 :后台主线程,优先级最高。其内部有4个循环:

l   主循环loop,

l   后台循环background loop,

l   刷新循环flush loop,

l   暂停循环suspend loop。

根据数据库运行状态再4个循环间切换。在loop主循环中又包含两种操作,分为:

 

四大IO线程

l   read thread: 数据库读写请求线程,默认4个,可扩大。

l   write thread:

l   redo log thread:日志缓冲区中内容刷新到redo log中;

l   change buffer thread:把插入缓冲区内内容刷新到磁盘。

 

page cleaner thread 负责脏页刷新的线程。show variables like ‘%innodb_page%’

purge thread: 负责删除无用undo页,由于进行DML语句的操作都会生成UNDO,系统需要定期对undo页进行清理,就需要purge操作。

checkpoint: 在redo log切换时,执行checkpoint,redo log发生切换会触发把脏页刷新到磁盘。

error monitor thread: 负责数据库报错的监控线程;

lock monitor thread 负责锁的监控线程。

1.4.6.   内存刷新机制

三个内存部分刷新

redo log buffer ,data buffer ,binlog cache

 

redo log

重做日志文件,记录事务操作的变化,记录数据修改之后得值,不管事务是否提交都会记录下来。至少2个redo log,磁盘用ib_logfile(0-N)命名;影响redo log buffer刷新到磁盘得条件

a.通过innodb_flush_log_at_trx_commit控制,分别为0,1,2;

       0:redo log thread每隔1s 触发redo log buffer和数据写磁盘,但事务提交不启动刷新。

       1:每次事务提交,触发redo log buffer和数据写到磁盘。

       2:每次事务提交,触发redo log buffer写,但不刷新磁盘。

b.mater thread:每秒刷新

c.redo log buffer,使用超过1半触发刷新。

 

binlog

DML语句既写redo log,也写binlog文件。

binlog叫mysql的二进制日志文件,功能用于备份恢复和主从复制。sync_binlog参数决定刷新条件

       0 :事务提交后,不立即将binlog_cache信息刷新到磁盘,而让filesystem自行决定同步。

       n::每n次事务提交后,binlog_cache刷新到磁盘。

 

确保数据库安全的双一模式:即sync_binlog=1,innodb_flush_log_at_trx_commit=1

 

redo log 和binlog的区别

第一:记录内容不同

l   binlog是逻辑日志,记录所有数据的改变信息。

l   redo log是物理日志,记录所有InnoDB表数据的变化。

第二:记录内容的时间不同

l   binlog记录commit完毕之后的DML和DDL SQL语句。

l   redo log 记录事务发起之后的DML和DDL SQL语句。

第三:文件使用方式的不同

l   binlog不是循环使用,在写满或实例重启之后,会生成新文件;

l   relo log是循环使用,最后一个写满再写第一个。

第四:作用不同

l   binlog可以作为恢复数据使用,主从复制搭建。

l   redo log作为异常关机或介质故障后数据恢复使用。

 

 

MySQL两阶段提交过程

两阶段提交分为prepare和commit阶段。

l   准备阶段(transaction prepare):事务SQL先写入到redo log buffer,然后做一个事务准备标志,再将log buffer中数据刷新到redo log。

l   提交阶段(commit):将事务产生的binlog写入文件,刷入磁盘。

l   再在redo log中做一个事务提交标志,把binlog写成功的标记一并写入redo log文件。

总结:只要binlog写入完成,在主从复制环境中,都会正常完成事务。

 

脏页的刷新机制

l   redo log切换时,执行checkpoint,会触发脏页的刷新。

l   通过innodb_max_dirty_pages_pct参数控制,表示buffer pool中dirty page的百分百。达到阈值启动刷新。默认75%,建议设置25%-50%,为避免后期影响性能。

l   由innodb_adaptive_flushing控制每秒刷新脏页的数目,智能控制,默认开启。

1.4.7.   InnoDB的三大特性

三大特性为:插入缓冲(change buffer),两次写(double write),自适应哈希索引(adaptive hash index)

 

插入缓冲:

将普通索引上的DML操作从随机IO变成顺序IO,提高IO效率。

原理:1.先判断插入的普通索引页是否在缓冲池中,如在就直接插入;如不在就先放到change buffer中,然后进行change buffer和普通索引的合并操作,多个插入合并到一个操作中,提高普通索引插入性能。

涉及参数:

[mysql]>show variables like'%change%';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| innodb_change_buffer_max_size | 25    |

| innodb_change_buffering       | all   |

| session_track_state_change    | OFF   |

+-------------------------------+-------+

 

innodb_change_buffer_max_size:占innodb_buffer_pool的最大比例,默认25%,建议50.

innodb_change_buffering:change buffer的类型,有如下几种。

l   all:缓冲全部insert,delete标记操作和purge操作, 建议选择默认的all。

l   none:关闭insert buffer;

l   inserts:insert 标记操作

l   delete:delete标记操作

l   changes:未进行实际insert和delete,只标记,等待后续purge;

l   purges:缓冲后台进程的purges(物理删除)操作.

 

 

两次写(dourble write)

       插入缓冲带来针对普通索引插入性能上的提升,而double write就是保证吸入的安全性,防止系统宕机。InnoDB发生数据页部分写(partial page write)问题,redo log文件记录的是页的物理记录,如果页损坏就无法进行恢复,就用binlog恢复原来的页,再通过redo log恢复。

 

双写缓冲位于系统表空间中的存储区域,吸入顺序如下:

l   InnoDB缓冲池中脏页被写入数据文件之前,读先写入double write buffer。

l   然后分两次从double write buffer,每次将1MB大小数据写入磁盘共享表空间(double write).

l   最后再从double write buffer写入数据文件。

双写缓冲不占用两倍IO,double 是一个大型的连续快,会通过一次fsync()通知操作系统。

 

自使用哈希索引

InnoDB可以监控索引的搜索,如注意到查询可通过建立哈希索引得到优化,就会自动完成。通过innodb_adaptive_hash_index参数控制。

从5.7.8开始,自适应哈希索引搜索系统是分区的,每个索引绑定到一个特殊的分区上面,各个分区都有自己的锁存器来进行维护。分区通过innodb_adaptive_hash_index_parts参数控制,默认值8各,最大512,通过分区设置,可降低争用,提高并发。

通过show engine innodb status命令输出的semaphores部分监控自使用哈希索引的使用及其竞争情况。如看到许多线程等待一个再btr0sea.c中创建的RW-latch,则它可能被用于禁用自适应哈希索引。

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29519108/viewspace-2218795/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-04

  • 博文量
    278
  • 访问量
    395432