數據庫學習之--Oracle 架構與MySQL架構對比
一、Oracle 、MySQL應用對比
如果要說明三者的區別，首先就要從歷史入手。
Oracle：中文譯作甲骨文，這是一家傳奇的公司，有一個傳奇的大老板Larry Ellision。 Ellision 32歲還一事無成，讀了三個大學，沒得到一個學位文憑，換了十幾家公司，老婆也離他而去。開始創業時只有1200美元，卻使得Oracle公司連續12年銷售額每年翻一番。
Oracle成立於1977年，早期的理論基礎，反而來自於一篇IBM的論文《A Relational Model of Data for Large Shared Data Banks》【1】。作者CODD選取了關系代數的五種運算，並基於運算，架構了一種新型的數據存儲模型。基於這種模型，Oracle成為了一個非常典型的關系數據庫。因此也變的嚴謹、安全、高速、穩定，並且變的越來越龐大。
由於其誕生早、結構嚴謹、高可用、高性能等特點，使其在傳統數據庫應用中大殺四方，金融、通信、能源、運輸、零售、制造等各個行業的大型公司基本都是用了Oracle，早些年的時候，世界500強幾乎100%都是Oracle的用戶。
MySQL ：MySQL的最初的核心思想，主要是開源、簡便易用。其開發可追溯至1985年，而第一個內部發行版本誕生，已經是1995年。到1998年，MySQL已經可以支持10中操作系統了，其中就包括win平台。但依然問題多多，如不支持事務操作、子查詢 、外鍵、存儲過程和視圖等功能。下圖是一個截止至2006年的數據庫市場占有率【2】：
圖中可以看出，MySQL的爆發實際是在01、02年，尤其是02年發布的4.0 Beta版，正式選定InnoDB作為默認引擎，對事務處理能力及數據緩存能力有了極大的提高。同年4.1版開始支持子查詢，至此MySQL終於蛻變成一個成熟的關系型數據庫系統。05年的5.0版本又添加了存儲過程、服務端游標、觸發器、查詢優化以及分布式事務功能，但同年被Oracle抄了後路，InnoDB被Oracle收編。08年，MySQL被Sun收購，09年，Oracle收購了Sun和MySQL。
由於MySQL的早期定位，其主要應用場景就是互聯網開發。基本上，互聯網的爆發成就了MySQL，LAMP架構風靡天下。而由於MySQL更多的的追求輕量、易用，以及早期的事物操作及復雜查詢優化的缺失，在傳統的數據庫應用場景中，份額極少。
SQL Server：一提到SQL Server，大家一般都只想到Microsoft SQL Server，而非Sybase SQL Server。SQL Server最初是由Microsoft, Sybase and Ashton-Tate三家公司攔下的生意，是為IBM（又出現了）公司的OS/2操作系統開發的。隨著OS/2項目的失敗，大家也分道揚镳。 Microsoft自然轉向自己的win操作系統，作為windows NT軟件方案的一部分。而Sybase則專注於Linux/Unix方向的數據庫開發。
MS SQL Server:主要面向中小企業。其最大的優勢就是在於集成了MS公司的各類產品及資源，提供了強大的可視化界面、高度集成的管理開發工具，在快速構建商業智能（BI）方面頗有建樹。 MS SQL Server是MS公司在軟件集成方案中的重要一環，也為WIN系統在企業級應用中的普及做出了很大貢獻。
典型應用場景
關於“大型數據庫”，並沒有嚴格的界定，有說以數據量為准，有說以恢復時間為准。如果綜合數據庫應用場景來說，大型數據庫應用有以下特點：海量數據、高吞吐量；復雜邏輯、高計算量，以及高可用性。從這點上來說，Oracle，DB2就是比較典型的大型數據庫，Sybase SQL Server也算是吧。下面分別說明之前三種數據庫的應用場景。
Oracle。Oracle的應用，主要在傳統行業的數據化業務中，比如：銀行、金融這樣的對可用性、健壯性、安全性、實時性要求極高的業務；零售、物流這樣對海量數據存儲分析要求很高的業務。此外，高新制造業如芯片廠也基本都離不開Oracle；電商也有很多使用者，如京東（正在投奔Oracle）、阿裡巴巴（計劃去Oracle化）。而且由於Oracle對復雜計算、統計分析的強大支持，在互聯網數據分析、數據挖掘方面的應用也越來越多。一個典型場景是這樣的：
某電信公司（非國內）下屬某分公司的數據中心，有4台Oracle Sun的大型服務器用來安裝Solaris操作系統和Oracle並提供計算服務，3台Sun Storage磁盤陣列來提供Oracle數據存儲，12台IBM小型機，一台Oracle Exadata服務器，一台500T的磁帶機用來存儲歷史數據，San連接內網，使用Tuxedo中間件來保證擴展性和無損遷移。建立支持高並發的Oracle數據庫，通過OLTP系統用來對海量數據實時處理、操作，建立高運算量的Oracle數據倉庫，用OLAP系統用來分析營收數據及提供自動報表。總預算約750萬美金。
MySQL。MySQL基本是生於互聯網，長於互聯網。其應用實例也大都集中於互聯網方向，MySQL的高並發存取能力並不比大型數據庫差，同時價格便宜，安裝使用簡便快捷，深受廣大互聯網公司的喜愛。並且由於MySQL的開源特性，針對一些對數據庫有特別要求的應用，可以通過修改代碼來實現定向優化，例如SNS、LBS等互聯網業務。一個典型的應用場景是：
某互聯網公司，成立之初，僅有PC數台，通過LAMP架構迅速搭起網站框架。隨著業務擴張、市場擴大，迅速發展成為6台Dell小型機的中型網站。現在花了三年，終於成為垂直領域的最大網站，計劃中的數據中心，擁有Dell機架式服務器40台，總預算20萬美金。
MS SQL Server,windows生態系統的產品，好處壞處都很分明。好處就是，高度集成化，微軟也提供了整套的軟件方案，基本上一套win系統裝下來就齊活了。因此，不那麼缺錢，但很缺IT人才的中小企業，會偏愛 MS SQL Server 。例如，自建ERP系統、商業智能、垂直領域零售商、餐飲、事業單位等等。
1996年，Bill Gates親自出手，從Borland挖來了大牛Anders，搞定了C#語言。微軟02年搞定了http://ASP.NET。成熟的.NET、Silverlight技術，為 MS SQL Server贏得了部分互聯網市場，其中就有曾經的全球最大社交網站MySpace，其發展歷程很有代表性，可作為一個比較特別的例子【3】。其巅峰時有超過1.5億的注冊用戶及每月400億的訪問量。應該算是MS SQL Server支撐的最大的數據應用了。
架構。其實要說執行的區別，主要還是架構的區別。正是架構導致了相同SQL在執行過程中的解釋、優化、效率的差異。這裡只做粗略說明，就不細說了：
Oracle： 數據文件包括：控制文件、數據文件、重做日志文件、參數文件、歸檔文件、密碼文件。這是根據文件功能行進行劃分，並且所有文件都是二進制編碼後的文件，對數據庫算法效率有極大的提高。由於Oracle文件管理的統一性，就可以對SQL執行過程中的解析和優化，指定統一的標准：
RBO（基於規則的優化器）、CBO（基於成本的優化器）
通過優化器的選擇，以及無敵的HINT規則，給與了SQL優化極大的自由，對CPU、內存、IO資源進行方方面面的優化。
MySQL：最大的一個特色，就是自由選擇存儲引擎。每個表都是一個文件，都可以選擇合適的存儲引擎。常見的引擎有 InnoDB、 MyISAM、 NDBCluster等。但由於這種開放插件式的存儲引擎，比如要求數據庫與引擎之間的松耦合關系。從而導致文件的一致性大大降低。在SQL執行優化方面，也就有著一些不可避免的瓶頸。在多表關聯、子查詢優化、統計函數等方面是軟肋，而且只支持極簡單的HINT。
SQL Server ：數據架構基本是縱向劃分，分為：Protocol Layer（協議層）， Relational Engine（關系引擎）， Storage Engine（存儲引擎）， SQLOS。SQL執行過程就是逐層解析的過程，其中Relational Engine中的優化器，是基於成本的（CBO），其工作過程跟Oracle是非常相似的。在成本之上也是支持很豐富的HINT，包括：連接提示、查詢提示、表提示
安裝
Oracle采用OUI的安裝模式，在任何操作系統上安裝界面一致
MySQL安裝可以選擇源碼包安裝和二進制安裝模式，操作系統不同安裝方式也不同
http://blog.csdn.net/lqx0405/article/details/46443259
Binary 版本安裝：
shell> groupadd mysql shell> useradd -r -g mysql mysql shell> cd /usr/local shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz shell> ln -s full-path-to-mysql-VERSION-OS mysql shell> cd mysql shell> chown -R mysql . shell> chgrp -R mysql . shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data # Next command is optional shell> cp support-files/my-medium.cnf /etc/my.cnf shell> bin/mysqld_safe --user=mysql &
源碼包安裝：
shell> groupadd mysql shell> useradd -r -g mysql mysql # Beginning of source-build specific instructions shell> tar zxvf mysql-VERSION.tar.gz shell> cd mysql-VERSION shell> cmake . shell> make shell> make install # End of source-build specific instructions # Postinstallation setup shell> cd /usr/local/mysql shell> chown -R mysql . shell> chgrp -R mysql . shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data # Next command is optional shell> cp support-files/my-medium.cnf /etc/my.cnf shell> bin/mysqld_safe --user=mysql &
三、系統架構
1、Oracle 體系架構
2、MySQL體系架構
由:連接池組件、管理服務和?工具組件、sql接口組件、查詢分析器組件、優化器組件、緩沖組件、插件式存儲引擎、物理?文件組成。mysql是獨有的插件式體系結構,各個存儲引擎有自己的特點。
innodb存儲引擎:
面向oltp(online transaction processing)、行鎖、支持外鍵、非鎖定讀、默認采用repeaable級別(可重復讀)通過next- keylocking策略避免幻讀、插入緩沖、二次寫、自適應哈希索引、預讀myisam存儲引擎:不支持事務、表鎖、全文索引、適合olap(在線分析處理),其中myd:放數據文件,myi:放索引文件ndb存儲引擎:集群存儲引擎,share nothing,可提高可用性memory存儲引擎:數據存放在內存中,表鎖,並發性能差,默認使用哈希索引
InnoDB 存儲引擎的線程與內存池
InnoDB體系結構如下：
後台線程：
1、後台線程的主要作用是負責刷新內存池中的數據，保證緩沖池中的內存緩存的是最近的數據；
2、另外，將以修改的數據文件刷新到磁盤文件；
3、同時，保證在數據庫發生異常的情況下，InnoDB能恢復到正常運行狀態。
內存池：InnoDB有多個內存塊，這些內存塊組成了一個大的內存池。這些內存塊包括有：緩沖池（innodb_buffer_pool）和日志緩沖(log_buffer)以及額外內存池（innodb_addtional_mem_pool_size）。
後台線程：後台默認的線程有7個-----4個IO thread,1個master thread,1個鎖監控線程，1個錯誤監控線程，但是在mysql5.5以及innodb plugin版本中，默認IO線程均增加到了4個，讀寫線程分別用參數innodb_read_io_thread和innodb_write_io_thread來表示。
Mysql5.1 innodb_version 為1.0.7
可以看到有1個Insert buffer thread ，1個log thread，4個read thread，4個write thread。
或者直接通過查看變量 ‘innodb_%threads’來查看。
Mysql5.5 innodb_version 為1.1.8
Mysql5.5 innodb的線程和Innodb plugin的情況類似。
Master thread在主循環中，分兩大部分操作：每秒鐘的操作和每10秒鐘的操作：
每秒一次的操作包括：
1、日志緩沖刷新到磁盤，即使這個事務還沒有提交（總是），這點解釋了為什麼再大的事務commit時都很快！
2、合並插入緩沖（可能）
合並插入並不是每秒都發生，InnoDB會判斷當前一秒內發生的IO次數是否小於5，如果是，則系統認為當前的IO壓力很小，可以執行合並插入緩沖的操作。
3、至多刷新100個InnoDB的緩沖池的髒頁到磁盤（可能）
這個，刷新100個髒頁也不是每秒都在做。
每10秒一次的操作包括:
1、刷新100個髒頁到磁盤（可能）。
2、合並至多5個插入緩沖（總是）。
3、將日志緩沖刷新到磁盤（總是）。
4、刪除無用的undo頁（總是）。
5、產生一個檢查點（checkpoing）。
四、innodb 表空間
Oracle的數據存儲有表空間、段、區、塊、數據文件；MySQL InnoDB的存儲管理也類似，但是MySQL增加了一個共享表空間和獨立表空間的概念；
完整的表空間，會被分成如下結構供給InnoDB使用。
最小單位是page，每個page為16K；64個連續的page組成一個extent； 多個extent和page構成一個segment。Segment初始時InnoDB會為它分配32個pages，之後根據需要會將extent分配給 segment，單次最多會分配4個extents給segment。
1、共享表空間
某一個數據庫的所有的表數據，索引文件全部放在一個文件中，默認這個共享表空間的文件路徑在data目錄下。 默認的文件名為ibdata1 初始化為10M。
由於是默認的方式，就暫且理解為Mysql官方推薦的方式。相對而言所有的數據都在一個（或幾個）文件中，比較利於管理，而且在操作的時候只需要open這一個（或幾個）文件即可，相對來說代價很低。
2、獨占表空間
每一個表都將會以獨立的文件方式來進行存儲，每一個表都有一個.frm表描述文件，還有一個.ibd文件。 其中這個文件包括了單獨一個表的數據內容以及索引內容。
五、重做日志（redo log）
前言:之前一直弄不清楚mysql裡面bin log和innodb log文件的區別，在腦子裡面一直有個疑問binlog日志文件已經可以用來進行數據庫的日志備份恢復了，怎麼又多了一個redo log文件了。相信也有很多人有這個疑惑，現在把整個過程文檔整理出來，希望對大家有所幫忙。
如果對Oracle很了解,那麼在整個學習innoDB log的時候,可以把聯機重做日志的那套理論套在學習innoDB log上面,幾乎是一樣的;
1、innodb log的基礎知識
innodb log顧名思義：即innodb存儲引擎產生的日志，也可以稱為重做日志文件，默認在innodb_data_home_dir下面有兩個文件ib_logfile0和ib_logfile1。MySQL官方手冊中將這兩個文件叫文InnoDB存儲引擎的日志文件；
查看日志文件位置：
[root@rh55 ~]# cat /etc/my.cnf
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[root@rh55 ~]# cd /usr/local/mysql/data/
[root@rh55 data]# ls -l
total 28732
-rw-rw---- 1 mysql mysql 18874368 Jun 10 16:52 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jun 10 16:52 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 10 16:52 ib_logfile1
drwxr-xr-x 5 mysql mysql 4096 Jun 19 14:12 mysql
drwx------ 2 mysql mysql 4096 Jun 10 16:52 performance_schema
drwxr-xr-x 2 mysql mysql 4096 Jun 10 16:18 test
innodb log的作用：當MySQL的實例和介質失敗的時候，Innodb存儲引擎就會使用innodb log文件進行恢復，保證數據庫的完整性；
innodb log的寫原理：（請容許我再放下InnoDB的原理圖，並且建議把這張圖看到吐）
看紅色框框的那部分
每個InnDB存儲引擎至少有1個重做日志文件組（group），每個文件組下至少有兩個重做日志文件，默認的為ib_logfile0、ib_logfile1；
日志組中每個重做日志的大小一致，並循環使用；
InnoDB存儲引擎先寫重做日志文件，當文件滿了的時候，會自動切換到日志文件2，當重做日志文件2也寫滿時，會再切換到重做日志文件1；
為了保證安全和性能，請設置每個重做日志文件設置鏡像，並分配到不同的磁盤上面；
（發現以上特性跟ORACLE的連接重做日志文件簡直是一樣的）
2、innodb log的相關參數
運行腳本：show variables like 'innodb%log%'; 查看重做日志的相關參數
mysql> show variables like 'innodb%log%';
常用設置的參數有：
innodb_mirrored_log_groups 鏡像組的數量，默認為1，沒有鏡像；
innodb_log_group_home_dir 日志組所在的路徑，默認為data的home目錄；
innodb_log_files_in_group 日志組的數量，默認為2；
innodb_log_file_size 日志組的大小,默認為5M；
innodb_log_buffer_size 日志緩沖池的大小，圖上為30M
3、參數的相關調優
3.1 重做日志文件的大小設置跟ORACLE一樣，面臨的問題是相似的。
當innodb log設置過大的時候，可能會導致系統崩潰後恢復需要很長的時間；
當innodb log設置過小的時候，當一個事務產生大量的日志的時候，需要多次切換重做日志文件，會產生類似如下的報警；
130702 12:53:13 InnoDB: ERROR: the age of the last checkpoint is 2863217109,
InnoDB: which exceeds the log group capacity 566222311.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
4、重做日志與二進制日志的區別
4.1 記錄的范圍不同：二進制日志會記錄MySQL的所有存儲引擎的日志記錄（包括InnoDB、MyISAM等)，
而InnoDB存儲引擎的重做日志只會記錄其本身的事務日志。
4.2 記錄的內容不同：二進制日志文件記錄的格式可以為STATEMENT或者ROW也可以是MIXED，其記錄的都是關於一個事務的具體操作內容。
InnoDB存儲引擎的重做日志文件記錄的關於每個頁的更改的物理情況。
4.3 寫入的時間也不同：二進制日志文件是在事務提交前進行記錄的，而在事務進行的過程中，不斷有重做日志條目被寫入到重做日志文件中。
總結：理論指導實踐，理論的知識雖然比較枯燥，學習跟建房子一樣，看著很漂亮的高樓大廈，地下往往要有一個堅實的基礎，基礎打好了，高樓也能建得穩了。
