架构技术

联系方式
架构技术

当前位置:主页 > 架构技术 >

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

发布时间:2018/11/07  作者:admin

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/,如需转载,请注明出处,否则将追查法律责任。

本文源自: 环亚娱乐

环亚娱乐_环亚娱乐AG国际厅_环亚娱乐AG最佳线路_腾讯财经 | 最近文章 | 架构说 | 架构技术 | 开发者娱乐 |

Copyright © 2005-2016 http://www.shopbwc.com 环亚娱乐_环亚娱乐AG国际厅_环亚娱乐AG最佳线路_腾讯财经版权所有 环亚娱乐_环亚娱乐AG国际厅_环亚娱乐AG最佳线路_腾讯财经