首頁 >資料庫 >mysql教程 >【MySQL資料庫】第一章解讀:MySQL架構與歷史

【MySQL資料庫】第一章解讀:MySQL架構與歷史

php是最好的语言
php是最好的语言原創
2018-08-07 11:48:192350瀏覽

前言:

      本章摘要描述MySQL伺服器架構、各種儲存引擎間的主要差異及差異的重要性

      回顧MySQL歷史背景、基準測試,透過簡化細節與簡報案例來討論MySQL的原理

正文:

      MySQL架構可在多種不同場景中應用,可嵌入應用程式中農,支援資料倉儲、內容索引、部署軟體、高可用冗餘系統、線上事務處理系統等;

       MySQL最重要的功能是他的儲存引擎架構,使得查詢處理及其他系統任務和資料儲存、提取分離;

1.1MySQL邏輯架構

【MySQL資料庫】第一章解讀:MySQL架構與歷史

1.2並發控制

鎖定粒度:

鎖定策略:在鎖定開銷和資料安全性間尋求平衡,每個儲存引擎可實現指定鎖定策略和粒度

表鎖定:table lock  最基本的開銷最小鎖定整表 

行級鎖定:row lock 最大程度支援並發 最大的鎖開銷在儲存引擎層(以自己的方式)實作

1.3交易

獨立工作單元,一組原子性SQL查詢

隔離等級:

四種,每種規定了交易中所做的修改,較低的隔離可以執行更高的並發、開銷也更低

READ UNCOMMITTED未提交讀取

        事務中的修改及時沒有提交,對其他事務也是可見的;事務讀取未提交的資料:髒讀;很少使用

READ COMMITTED提交讀取

        almost庫預設隔離級別,非MySQL;事務從開始到結束只可見已提交的事務所作的修改,本身所做的修改對其他事務不可見;不可重複讀取:兩次執行相同的查詢,結果可能不一樣(其他事務的修改)

##REPEATABLE READ可重複讀取

        MySQL默認,解決了髒讀,同一事務多次讀取同樣結果;

幻讀:當某個事務在讀取某個範圍內的記錄時、另一個事務在該範圍內插入新的記錄,目前事務再次讀取該範圍記錄、幻行

##SERIALIZABLE:可串列化

        最高,強制事務串列執行,避免幻讀問題,讀取每行資料時加鎖(可導致大量逾時和鎖爭用),很少使用

【MySQL資料庫】第一章解讀:MySQL架構與歷史#死鎖

##1、兩個多個事務在同一個資源上相互佔用並請求鎖定對方佔用的資源;

2、多個事務試圖以不同的順序鎖定資源,可能產生死鎖;

3、多個事務同時鎖定同一個資源;

鎖定的行為和順序和存取引擎相關,同樣的順序執行語句,有些儲存引擎會產生死鎖一些不會;

死鎖產生的雙重原因:因為真正的資料衝突(很難避免),因為儲存引擎的實現方式導致;

死鎖發送後,只有部分或完全回滾其中一個事務,才能打破死鎖:InnoDB即回滾持有最少行級排他鎖定的事務; 

1.3.4MySQL中的事務:儲存引擎實作

MySQL兩種事務型儲存引擎:InnoDB、NDB Cluster

自動提交AUTOCOMMIT;

      預設採用自動提交模式,如果不明確開始一個事務,則每個查詢都被當做一個事務執行提交操作,可透過AUTOCOMMIT變數來啟用=1 =ON 、停用=0  =OFF(all查詢都在一個事務中直到明確commit rollback)事務結束同時開始新的事務,修改這個變數對非事務型表沒有任何影響;

MySQL可以透過set transaction isolation level設定隔離級別,新的等級在下一個交易開始時生效,設定檔設定整個函式庫的,也可只改變目前會話的隔離等級

set session transaction isolation level read committed;

建議:不管何時都不要顯示執行LOCK TABLES ,不管使用的是什麼儲存引擎

1.4多版本並發控制MVCC

        資料庫MySQL、Oracle、postgresql等都實作了MVCC,各自實作機制不同【來源】

        MVCC:每個連接到資料庫的讀取、在某個瞬間看到的是資料庫的

快照

,寫入作業在提交之前對外不可見;【來源】

更新時,將舊數據標記為過時且在別處增加新版本的數據(多個版本的數據,只有一個最新),容許讀取之前的數據

特點:

1、每行資料都存在一個版本,每次資料更新時都更新該版本

2、修改時copy出目前版本、隨意修改,各事務間不干擾

#

3、保存时比较版本号,成功commit则覆盖原纪录,失败则放弃rollback

4、只在REPEATABLE READ 和READ COMMITTED两个隔离级别下工作

1.5MySQL存储引擎

     mysql将每个数据库保存位数据目录下的一个子目录,创建表示,mysql在子目录下创建与表同名的.frm文件保存表的定义,不同存储引擎保存数据和索引的方式不同,但表的定义在MySQL服务层同一处理;

InnoDB:默认事务型引擎、最重要、广泛使用

     处理大量短期事务;其性能和自动崩溃恢复特性、非事务型存储的需求中也很流行

     数据存储在由InnoDB管理的表空间中,由一系列数据文件组成;

     使用MVCC支持高并发,并实现了四个标准的隔离级别,默认是REPEATABLE READ可重复读,通过间隙锁next-key locking防止幻读,间隙锁使得InnoDB锁定查询设计的行还锁定索引中的间隙防止唤影行;

间隙锁:

  当使用范围条件并请求锁时,InnoDB给符合条件的已有数据记录的索引项加锁,对应键值在条件范围内但是不存在的记录(间隙)加锁,间隙锁:【源】

//如emp表中有101条记录,其empid的值分别是 1,2,...,100,101
Select * from  emp where empid > 100 for update;

    InnoDB对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁;

      1、上面的例子,如果不使用间隙锁,如果其他事务插入大于100的记录,本事务再次执行则幻读,但是会造成锁等待,在并发插入比较多时、要尽量优化业务逻辑,使用相等条件来访问更新数据,避免使用范围条件;

      2、 在使用相等条件请求给一个不存在的记录加锁时,也会使用间隙锁,当我们通过参数删除一条记录时,如果参数在数据库中不存在,库会扫描索引,发现不存在,delete语句获得一个间隙锁,库向左扫描扫到第一个比给定参数小的值,向右扫描到第一个比给定参数大的值,构建一个区间,锁住整个区间内数据;【源】

1.5.2MyIsSAM存储引擎

   全文索引、压缩、空间函数,不支持事务和行级锁,崩溃后无法安全恢复

存储:

    将表存储在两个文件中:数据.MYD、索引文件.MYI

    表可以包含动态或静态(长度固定)行,MySQL据表定义来决定采用何种行格式

    表如是变长行,默认配置只能处理256TB数据(指向记录的指针长度6字节),改变表指针长度,修改表的MAX_ROWS和AVG_ROW_LENGTH,两者相乘=表可到达的max大小,修改会导致重建整个表、表all索引;

特性:

    1、对整张表加锁,读、共享锁,写、排他锁,但在读的同时可从表中插入新记录:并发插入

    2、修复:可手工、自动执行检查和修复操作,CHECK TABLE mytable检查表错误,REPAIR TABLE mytable进行修复,执行修复可能会丢失些数据,如果服务器关闭,myisamchk命令行根据检查和修复操作;

    3、索引特性:支持全文索引,基于分词创建的索引,支持复杂查询

    4、延迟更新索引键Delayed Key Write,如果指定了DELAY_KEY_WRITE选项,每次修改完,不会立即将修改的索引数据写入磁盘,写入到内存的键缓冲区,清理此区或关闭表时将对应的索引块写入到磁盘,提升写性能,但是在库或主机崩溃时造成索引损坏、需要执行修复操作

压缩表:

    表在创建并导入数据后,不再修改,比较适合,可使用myisampack对MyISAM表压缩(打包),压缩表不能修改(除非先解除压缩、修改数据、再次压缩);减少磁盘空间占用、磁盘IO,提升查询性能,也支持只读索引;

    现在的硬件能力,读取压缩表数据时解压的开销不大,减少IO带来的好处大得多,压缩时表记录独立压缩,读取单行时不需要解压整个表

性能:

   设计简单,紧密格式存储;典型的性能问题是表锁的问题,长期处于locked状态:找表锁

1.5.3内建的其他存储引擎

Archive:适合日志和数据采集类应用,针对高速插入和压缩优化,支持行级锁和专业缓存区,缓存写利用zlib压缩插入的行,select扫描全表;

Blackhole:复制架构和日志审核,其服务器记录blackhole表日志,可复制数据到备库 日志;

CSV:数据交换机制,将CSV文件作为MySQL表来处理,不支持索引;

Federated:访问其他MySQL服务器的代理,创建远程mysql的客户端连接将查询传输到远程服务器执行,提取发送需要的数据,默认禁用;

Memory:快速访问不会被修改的数据,数据保存在内存、不IO,表结构重启后还在但数据没了

     1、查找 或 映射 表 ,2、缓存周期性聚合数据, 3、保存数据分析中产生的中间数据

     支持hash索引,表级锁,查找快并发写入性能低,不支持BLOB/TEXT类型的列,每行长度固定,内存浪费

Merge:myisam变种,多个myisam合并的虚拟表

NDB集群引擎:

1.5.4第三方存储引擎

OLTP类:

XtraDB基于InnoDB改进,性能、可测量性、操作灵活

PBXT:ACID/MVCC,引擎级别的复制、外键约束,较复杂架构对固态存储SSD适当支持,较大值类型BLOB优化

TokuDB:大数据,高压缩比,大数据量创大量索引

RethinkDB:固态存储

面向列的

列单独存储,压缩效率高

Infobright:大数据量,数据分析、仓库应用设计的,高度压缩,按照块(一组元数据)排序;块结构准索引,不支持索引(量大索引也没用),如查询无法再存储层使用面向列的模式执行,则需要在服务器层转换成按行处理

社区存储引擎:***

1.5.5选择合适的引擎

 除非需要用到某些InnoDB不具备的特性,且无办法可以替代,否则优先选择InnoDB引擎

不要混合使用多种存储引擎,如果需要不同的存储引擎:

1、事务:需要事务支出,InnoDB XtraDB;不需要 主要是select insert 那MyISAM

2、备份:定期关闭服务器来执行备份,该因素可忽略;在线热备份,InnoDB

3、崩溃恢复:数据量较大,MyISAM崩后损坏概率比InnoDB高很多、恢复速度慢

4、持有的特性:

1.5.6转换表的引擎

ALTER TABLE:最简单

ALTER TABLE mytable ENGINE=InnoDB

此会执行很长时间,MySQL按行将数据从原表复制到新表中,在复制期间可能会消耗掉系统all的I/O能力,同时原表上加读锁;会失去和原引擎相关的all特性

导出与导入:

mysqldump工具将数据导出到文件,修改文件中CREATE_TABLE语句的存储引擎选项,同时修改表名(同一个库不能存在相同的表名),mysqldump默认会自动在CREATE_TABLE语句前加上DROP TABLE语句

创建与查询:CREATE SELECT 

综合上述两种方法:先建新存储引擎表,利用INSERT……SELECT语法导数

CREATE TABLE innodb_table LIKE myisam_table
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
数据量大的话,分批处理(放事务中)

1.6MySQL时间线Timeline

 早期MySQL破坏性创新,有诸多限制,且很多功能只能说是二流的,但特性支持和较低的使用成本,使受欢迎;5.x早起引入视图、存储过程等,期望成为“企业级”数据库,但不算成功,5.5显著改善

【MySQL資料庫】第一章解讀:MySQL架構與歷史

1.7MySQL开发模式

遵循GPL开源协议,全部源代码开发给社区,部分插件收费;

1.8总结

mysql分层架构,上层是服务器层的访问和查询执行引擎,下层存储引擎(最重要)

相关文章:

【MySQL数据库】第二章解读:MySQL基准测试

【MySQL数据库】第三章解读:服务器性能剖析(上)

以上是【MySQL資料庫】第一章解讀:MySQL架構與歷史的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn