网站开发网站页面,许昌做网站九零后,网页升级中紧急自动转跳中,wordpress版本下载一、MySQL
1.1 MySql 体系结构 MySQL 架构总共四层#xff0c;在上图中以虚线作为划分。 1. 最上层的服务并不是 MySQL 独有的#xff0c;大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如#xff1a;连接处理、授权认证、安全等。 2. 第二层的架构包括…一、MySQL
1.1 MySql 体系结构 MySQL 架构总共四层在上图中以虚线作为划分。 1. 最上层的服务并不是 MySQL 独有的大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等。 2. 第二层的架构包括大多数的 MySQL 的核心服务。包括查询解析、分析、优化、缓存以及所有的内置函数例如日期、时间、数学和加密函数。同时所有的跨存储引擎的功能都在这一层实现存储过程、触发器、视图等。 3. 第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异使得这些差异对上层的查询过程透明化。存储引擎 API 包含十几个底层函数用于执行“开始一个事务”等操作。但存储引擎一般不会去解析 SQLInnoDB 会解析外键定义因为其本身没有实现该功能不同存储引擎之间也不会相互通信而只是简单的响应上层的服务器请求。 4. 第四层包含了文件系统所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。 1.1.1 SQL 语句的执行流程 1. 建立连接 连接数默认 151 最大 10000。 2. 查询缓存 默认关闭 8.0 删除 比较鸡肋因为缓存触发条件苛刻eg查询语句完全一样数据库有数据更新缓存会清空。 3. 解析器 词法解析 将 sql 打散成一个一个的词。 语法解析 对 sql 进行语法检验同时将词法解析成的词语 按照语法规则生成特定的数据结构 ----解析树。 4. 预处理器 检查生成的解析树处理解析器无法解析的语义比如表名、列名是否存在检查名字和别名保证没有歧义 同时会生成新的解析树。 5. 查询优化器 一条 Sql 语句可以有很多种执行方式但是返回的结果是一样的。查询优化器的目的就是基于解析树生成不同的解析计划从中选择一个最优的执行计划MYSQL 里面使用的是基于开销的优化器那种执行计划开销最小就是用哪种。 比如多表关联查询基准表的选择多个索引可以使用时候选择哪个索引 6. 执行计划 优化器优化解析树会得到另一个数据结构的数据-----查询执行计划使用 EXPLAIN 可以查看。 7. 执行引擎 使用执行计划操作存储引擎通过存储引擎提供的 API 完成得到结果最后返回给客户端 8. 存储引擎 存储数据的形式。 MYISM 适用只读的场景 支持表锁、记录数据行数、插入和查询较快。 INNODB 支持表锁、行锁、外键、事务支持读写并发写不阻塞读(MVCC)特殊的索引存放可以减少 IO提升查询效率。 MEMORY 适用临时表 数据存在内存中读写快数据库崩溃或者宕机数据消失。 1.1.2 一条更新 SQL 的执行 1. 建立连接事务开启查询缓存可跳过默认不开启然后通过解析器词法解析语法分析预处理等生成解析树然后通过查询优化器获得一个开销最小的执行计划然后通过执行引擎调用 API 接口操作存储引擎获取数据返回查询结果给 server 的执行器 2. server 的执行器修改数据页中的一行数据 3. 记录修改日志到 undo log 4. 记录日志到 redo log 5. 调用存储引擎接口记录数据页到 buffer pool 中 6. 事务提交 修改数据的时候会先写入缓冲区的数据页中因此缓冲区的数据页与磁盘中的数据出现了数据不一致的现象此时的数据页被称为脏页将脏页数据更新到磁盘的过程称为刷脏 1.2 MySQL 存储引擎 1.2.1 存储引擎 MySQL 中的数据用各种不同的技术存储在文件或者内存中。 这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。 通过选择不同的技术你能够获得额外的速度或者功能从而改善你的应用的整体功能。 例如如果研究大量的临时数据你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。 这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作表类型)。 MySQL 默认配置了许多不同的存储引擎可以预先设置或者在 MySQL 服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。 选择如何存储和检索你的数据的这种灵活性是 MySQL 为什么如此受欢迎的主要原因。其它数据库系统 (包括大多数商业选择)仅支持一种类型的数据存储 。 1.2.2 MySQL 支持的存储引擎 MySQL5.6 支持的存储引擎包括 1. InnoDB 2. MyISAM 3. MEMORY 4. CSV 5. BLACKHOLE 6. FEDERATED 7. MRG_MYISAM 8. ARCHIVE 9. PERFORMANCE_SCHEMA。 其中 NDB 和 InnoDB 提供事务安全表其他存储引擎都是非事务安全表。 1.2.3 各种存储引擎的特性 1. 并发性某些应用程序比其他应用程序具有很多的颗粒级锁定要求如行级锁定。 2. 事务支持并非所有的应用程序都需要事务但对的确需要事务的应用程序来说有着定义良好的需求如 ACID 兼容等。 3. 引用完整性通过 DDL 定义的外键服务器需要强制保持关联数据库的引用完整性。 4. 物理存储它包括各种各样的事项从表和索引的总的页大小到存储数据所需的格式到物理磁盘。 5. 索引支持不同的应用程序倾向于采用不同的索引策略每种存储引擎通常有自己的编制索引方法但某些索引方法如 B-tree 索引对几乎所有的存储引擎来说是共同的。 6. 内存高速缓冲与其他应用程序相比不同的应用程序对某些内存高速缓冲策略的响应更好因此尽管某些内存高速缓冲对所有存储引擎来说是共同的如用于用户连接的高速缓冲MySQL 的高速查询高速缓冲等其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。 7. 性能帮助包括针对并行操作的多 I/O 线程线程并发性数据库检查点成批插入处理等。 8. 其他目标特性可能包括对地理空间操作的支持对特定数据处理操作的安全限制等。 1.2.4 各种搜索引擎介绍 - InnoDBMySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。 InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性InnoDB 还支持外键约束。 - MyISAMMyISAM 既不支持事务、也不支持外键、其优势是访问速度快但是表级别的锁定限制了它在读写负载方面的性能因此它经常应用于只读或者以读为主的数据场景。 - Memory在内存中存储所有数据应用于对非关键数据由快速查找的场景。Memory 类型的表访问数据非常快因为它的数据是存放在内存中的并且默认使用 HASH 索引但是一旦服务关闭表中的数据就会丢失。 - BLACKHOLE黑洞存储引擎类似于 Unix 的 /dev/nullArchive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于DML 语句需要发送到从服务器但主服务器并不会保留这种数据的备份的主从配置中。 - CSV它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引你最好是在普通操作中将数据放在 InnoDB 表里只有在导入或导出阶段使用一下 CSV 表。 - NDB(又名 NDBCLUSTER)——这种集群数据引擎尤其适合于需要最高程度的正常运行时间和可用性的应用。注意NDB 存储引擎在标准 MySql 5.6 版本里并不被支持。 - MySql 集群的版本有基于 MySql 5.1 的 MySQL Cluster NDB 7.1基于 MySql 5.5 的 MySQL Cluster NDB 7.2基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同样基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正处于研发阶段。 - Merge允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组并把它们作为一个对象进行引用。适用于超大规模数据场景如数据仓库。 - Federated提供了从多个物理机上联接不同的 MySql 服务器来创建一个逻辑数据库的能力。适用于分布式或者数据市场的场景。 - Example这种存储引擎用以保存阐明如何开始写新的存储引擎的 MySql 源码的例子。它主要针对于有兴趣的开发人员。这种存储引擎就是一个啥事也不做的“存根”。你可以使用这种引擎创建表但是你无法向其保存任何数据也无法从它们检索任何索引。 1.2.5 存储引擎相关 sql 语句 # 查看当前的默认存储引擎:
mysql show variables like default_storage_engine;
# 查询当前数据库支持的存储引擎
mysql show engines \G; mysql create table ai(id bigint(12),name varchar(200)) ENGINEMyISAM;
mysql create table country(id int(4),cname varchar(50)) ENGINEInnoDB;
# 也可以使用alter table语句修改一个已经存在的表的存储引擎。
mysql alter table ai engine innodb; # my.ini文件
[mysqld]
default-storage-engineINNODB 1.3 MySQL 内存结构 1.3.1 Innodb 内存架构 主要分以下几个要点 - Buffer Poolbuffer 池 - Change Buffer更改 buffer - Adaptive Hash Index自适应 hash 索引 - Log Buffer日志 buffer 1.3.2 Buffer Pool Buffer Pool 是 mysql 运行时使用的一块内存区域用来存储/修改/访问 Table 和 Index 数据的内存区域。mysql 用它存储了被频繁访问的数据。 理论上来说它能使用的内存空间越大mysql 性能越好所以在专门的 mysql 服务器上一般分配物理内存的 80%给 Buffer Pool。 为了提升大容量数据的访问性能Buffer Pool 内部分为可以容纳多行的页Page。 为了方便缓存数据的管理Buffer Pool 又实现了由页组成的链表linked list数据的过期策略采用 LRU 算法实现。 了解如何利用缓冲池将经常访问的数据保存在内存中是 MySQL 调优的一个重要方面。 新链表与老链表以下称 new 链表和 old 链表。 在单链表的基础上Buffer Pool 又划分了 new 子链表和 old 子链表。 - new 子链表处于单链表的头部占 5/8old 处于尾部占 3/8。 - new 是经常被访问部分old 反之。 - new 和 old 分开管理。 - 图中的 midpoint 是 new 和 old 的交接点 - 当从磁盘中读取的数据被插入链表时第一次是插入 old 子链表的头部old 子链表中的数据被访问时会被移动到 new 子链表的头部预读操作除外 - new 和 old 子链表中的页面会随着其他页面的更新而老化未使用或少使用的页逐渐到达 old 子链表的尾部然后并被驱逐(evicted)。 为什么要用双链表 单链表情况下的缓存池污染 1. 默认的机制是page 只要被读取就会被移动到链表头部。这在某些情况下会造成缓冲池污染比如 mysqldump 操作或者不带 WHERE 的 SELECT 查询会一次性往 bp 页链表中存入大量的数据并导致等量的数据老化失效。而这些操作是临时性的读取的大量数据基本很长时间都不会被再次读取这就造成了 bp 池污染严重降低了 mysql 性能 2. 同样的原理预读操作也会造成 bp 池污染 双链表方案 1. 区分 new 和 old 链表使用 old 链表存储那些刚从磁盘加载的数据 2. 不管是什么操作数据从磁盘加载后都是先存储 old 链表中若这些数据在淘汰前再次被读取说明它们的确是热数据需要移到 new 链表的头部否则就一直处于 old 链表会以比 new 链表更快的速度被淘汰。 1.3.3 Change Buffer Change Buffer下文简称 CB是一种由多个二级索引页和一颗 B树存在于共享表空间ibdata1 文件构成的内存空间用于缓存针对不存在于 buffer pool 中的二级索引页的 DML 操作INSERT, UPDATE, DELETE稍后这些变更根据各种机制合并到 buffer pool 中。 下图是 CB 与 buffer pool 的交互图 Change Buffer 内部实现 CB 是由一个 B树构成的它负责对所有表的二级索引更改进行记录。树的非叶节点存放的是 search key其构造如下图 search key 一共占用 9 个字节其中 space 表示待插入记录所在表的表空间 id在 InnoDB 存储引擎中每个表有一个唯一的 space id可以通过 space id 查询得知是哪张表。space 占用 4 字节。marker 占用 1 字节它是用来兼容老版本的 Insert Buffer。offset 表示页所在的偏移量占用 4 字节。当一个二级索引记录要 insert 到页( spaceoffset时如果这个页不在缓冲池中那么 InnoDB 引擎首先根据上述规则构造一个 search key接下来查询 Change Buffer 这棵 B树然后再将这条记录插入到 Change Buffer B树的叶子节点中。 对于插人到 Change Buffer B树叶子节点的记录并不是直接将待插入的记录插入而是需要根据如下的规则进行构造 前几个字段和 searchkey 一样就不说了metadata 占 4 字节记录了这条变更记录的元数据比如包含了插入这棵树的顺序值用于 replay 恢复数据其他的不做 DB 开发不需要掌握。后面的 secondary index record 部分就是描述本次操作(DML)的内容了。 1.3.4 自适应 Hash 索引Adaptive Hash Index 简称 AHI具体来说是给经常被访问的索引页热点页建立 hash 索引可以描述为索引的索引。 它加速了对热点页的查找过程常规是通过 Btree 查找可能需要 2-3 次 I/O而通过 hash 索引可以直接找到随意的索引页不再需要树查找。 它有几个特点如下 - 限制场景使用比如仅包含 和 IN 操作符的等值查询。 - hash 索引的 key 值是 where 条件中的索引字段如果是组合索引字段可以只包含部分字段。 - 在部分场景下对自适应哈希索引的访问有时会导致严重的锁竞争如高并发 join 查询。 - MySQL 自动调整无法干预。 5.7 版本及以上中AHI 使用了分段锁来减少高并发场景下锁的竞争提高性能 。 官 文 中 描 述 为 分 区 (partitioned) 其 实 一 个 意 思 。 它 由 参 数 innodb_adaptive_hash_index_parts控制默认 8最高 512。 查看相关配置 show variables like %hash_index% 关闭 set global innodb_adaptive_hash_indexoff 1.3.5 Log buffer 是一块用来作为 redo log 在内存缓冲区的内存区域增加 log buffer size 可以提升事务并发性能减少磁盘 I/Oredo log 会在 log buffer 不够用时刷盘。另外log buffer 也会定期刷盘。 相关可调变量 - innodb_log_buffer_size默认 16MB。 - innodb_flush_log_at_trx_commit控制如何将日志缓冲区的内容写入并刷到磁盘具体细节涉及到 redo log 部分暂不细讲。 - innodb_flush_log_at_timeout控制日志刷新频率。 另外还有几个变量是调整 redo log 的物理文件大小。 - innodb_log_file_size 控制单个 redo log 文件大小默认 48MB最大 512GB文件名 ib_logfile0 和 ib_logfile1这两个文件是循环写入的值越大能存的 redo log 越多就减少了 redo log 写入到数据页的 I/O 次数同时 mysql 启动时的恢复时间越长可参考的时间是 1G 的 redo log 文件需要 5 分钟的恢复时间。 - innodb_log_files_in_group 控制 redo log 文件数量默认 2 - innodb_log_group_home_dir 控制 redo log 的位置默认 data 目录 1.4 MySQL 磁盘结构 InnoDB 磁盘主要包含【TablespacesInnoDB Data DictionaryDoublewrite Buffer、Redo Log 和 Undo Logs】五部分组成。 1.4.1 表空间Tablespaces innodb 存储引擎在存储设计上模仿了 Oracle 的存储结构其数据是按照表空间进行管理的。表空间用于存储表结构和数据。表空间又分为系统表空间、独立表空间、 通用表空间、临时表空间、Undo 表空间等多种类型。 1. 表空间组成 - 物理结构组成 在系统表空间由于所有的表公用一个.ibdatat1 数据文件所以针对每个表只有一个.frm 表结构文件。 在独立表空间中每个表分别都有一个.frm 表结构文件一个.ibd 数据文件。innodb 存储引擎物理组织形式可以理解为其在磁盘的存储形式表现为各种文件其分类大概为 | 文件 | 功能 | 描述 |
| :------------ | -------------- | -------------------------------------------------- |
| ibdatat1 | 共享表空间文件 | 系统/共享表空间存储各种缓冲数据 |
| .frm | 表定义文件 | 记录表的定义列名以及列的数据类型 |
| .ibd | 表数据存储文件 | 独立表空间存储数据表的数据按行存储 |
| ib_logfile0/1 | redo日志文件 | 重做日志文件一共两个循环使用一个写完即写另一个 | 表空间是 innodb 存储引擎逻辑结构的最高层所有的数据都存储在表空间中默认 innodb 有一个共享表空间所有的数据都存储在共享表空间中可以通参数 innodb_per_table 设置每张表单独存放在一个表空间中。 - 独立表空间内存储的只是数据、索引、插入缓冲页。 - 回滚日志、插入缓冲索引页、事务信息、二次写缓冲等其他数据还是存放在共享表空间。 1.4.2 表空间的五种类型 - 系统表空间The System Tablespace - 包含 InnoDB 数据字典Doublewrite BufferChange BufferUndo Logs 的存储区域。 - 系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。 - 系统表空间是一个共享的表空间因为它是被多个表共享的。 innodb_data_file_path 用来指定 innodb tablespace 文件如果我们不在 My.cnf 文件中指定 innodb_data_home_dir 和 innodb_data_file_path 那么默认会在 datadir 目录下创建 ibdata1 作为 innodb tablespace。 #默认值:
innodb_data_file_path ibdata1:12M:autoextend
# ibdata1 : 文件名为 ibdata1
# 12M : 大小为 12M
# autoextend : 自动扩展 - 独立表空间File-Per-Table Tablespaces 默认开启独立表空间是一个单表表空间该表创建于自己的数据文件中而非创建于系统表空间中。 开启独立表空间参数为innodb_file_per_table innodb_file_per_table ON独立表空间tablename.ibd innodb_file_per_table OFF系统表空间ibdataX - 【innodb_file_per_table ON】 新建表被创建于【表空间】中,每一个表建立ibd的扩展文件,文件名为表名.ibd该文件默认被创建于数据库目录中,表空间的表文件支持动态和压缩行格式。 - 【innodb_file_per_table OFF】 innodb将被创建于【系统表空间】中即ibdataX中。X代表从1开始的一个数字 - 【查看表的存储表空间的存储方式值】 show variables like innodb_file_per_table; - 【修改存储表空间的存储方式值为OFF】 set global innodb_file_per_tableoff; - 通用表空间General Tablespaces 通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于 mysql数据目录外的其他表空间其可以容纳多张表且其支持所有的行格式。 #创建表空间tablespaces1
CREATE TABLESPACE tablespaces1 ADD DATAFILE tablespaces1.ibd EngineInnoDB;
#将表添加到test1表空间
CREATE TABLE test1 (c1 INT PRIMARY KEY) TABLESPACE tablespaces1; - 撤销表空间Undo Tablespaces 撤销表空间由一个或多个包含Undo日志文件组成。 在MySQL 5.7版本之前Undo占用的是System Tablespace共享区从5.7开始将Undo从System Tablespace分离了出来。 - 【innodb_undo_tablespaces】
innodb_undo_tablespaces 0 默认值表示使用系统表空间ibdata1
innodb_undo_tablespaces 1大于0表示使用undo表空间undo_001、 undo_002等- 临时表空间Temporary Tablespaces mysql服务器正常关闭或异常终止时临时表空间将被移除每次启动时会被重新创建。 临时表空间分为两种 - 【session temporary tablespaces】 存储的是用户创建的临时表和磁盘内部的临时表。 - 【global temporary tablespace】 储存用户临时表的回滚段rollback segments。 1.4.3 数据字典InnoDB Data Dictionary InnoDB 数据字典由内部系统表组成。这些表包含用于查找表、索引和表字段等对象的元数据。 元数据物理上位于 InnoDB 系统表空间中。数据字典元数据在一定程度上与 InnoDB 表元数据文件.frm 文件中存储的信息重叠。 1.4.4 双写缓冲区Doublewrite Buffer 位于系统表空间是一个存储区域。 在 BufferPage 的 page 页刷新到磁盘真正的位置前会先将数据存在 Doublewrite 缓冲区。如果在 page 页写入过程中出现操作系统、存储子系统或 mysqld 进程崩溃InnoDB 可以在崩溃恢复期间从 Doublewrite 缓冲区中找到 page 页备份。在大多数情况下默认情况下启用双写缓冲区。 - innodb_doublewrite 0 禁用 Doublewrite 缓冲区 - innodb_flush_method O_DIRECT 数据文件写入操作会通知操作系统不要缓存数据也不要用预读。 innodb_flush_method 控制 innodb 数据文件及 redo log 的打开、 刷写模式。 1.4.5 重做日志Redo Log - 重做日志是一种基于磁盘的数据结构用于在崩溃恢复期间修正不完整事务写入的数据。 - MySQL 以循环方式写入重做日志文件记录 InnoDB 中所有对 Buffer Pool 修改的日志。 当出现实例故障导致数据未能更新到数据文件则数据库重启时须 redo重新把数据更新到数据文件。读写事务在执行的过程中都会不断的产生 redo log。默认情况下重做日志在磁盘上由两个名为 ib_logfile0 和 ib_logfile1的文件物理表示。 1.4.6 撤销日志Undo Logs 撤消日志是在事务开始之前保存的被修改数据的备份用于回滚事务。 撤消日志属于逻辑日志根据每行记录进行记录。 撤消日志存在于系统表空间、撤消表空间和临时表空间中。
