MYSQL 体系构架与存储引擎
发布时间：2022-03-23 15:47:37 所属栏目：MySql教程 来源：互联网
导读：MYSQL 体系结构与存储引擎： 1. 第三章MYSQL 体系结构与存储引擎 1.1. mysql体系结构 体系结构分为两大层： l mysql server层：包括连接层，SQL层； l 存储引擎层； 1.2. query cache详解及关闭 query cache只能缓存静态数据。适用于数据仓库。 5.6前默认
|
MYSQL 体系结构与存储引擎： 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 具体测试过程见书 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 重做日志文件，记录事务操作的变化，记录数据修改之后得值，不管事务是否提交都会记录下来。至少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半触发刷新。 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,则它可能被用于禁用自适应哈希索引。 （编辑：云计算网_泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330476号