MySQL Study案例之--MySQL體系和儲存引擎

標籤：oracle

MySql Study案例之--MySql體系和儲存引擎

1、資料庫和執行個體

    資料庫：物理作業系統檔案或其他形式檔案類型的集合。在MySQL中，資料庫檔案可以是frm、myd、myi、ibd結尾的檔案。當使用NDB引擎時，資料庫檔案可能不是作業系統上的檔案，而是存放與記憶體之中的檔案，但是定義仍然不變。

     資料庫執行個體：由資料庫後台進程/線程以及一個共用記憶體區組成。共用記憶體可以被啟動並執行後台進程/線程所共用。需要牢記的是，資料庫執行個體才是真正用來操作資料庫檔案的。

    在MySQL中，執行個體和資料庫通常關係是一一對應，即一個執行個體對應一個資料庫，一個資料庫對應一個執行個體。但是，在叢集情況下可能存在一個資料庫可被多個執行個體訪問的情況。

    MySQL被設計為一個單進程多線程架構的資料庫。

2、MySQL進程

[[email protected] ~]#mysqld_safe &[[email protected] ~]# ps -ef|grep mysql |grep -v greproot      4168  4130  0 14:48 pts/1    00:00:00 /bin/sh ./mysqld_safemysql     4294  4168  0 14:48 pts/1    00:00:14 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sockroot      4643  4130  0 15:26 pts/1    00:00:00 mysql -h localhost -u root -pMySQL設定檔：[[email protected] ~]# mysql --help |grep my.cnf                      order of preference, my.cnf, $MYSQL_TCP_PORT,/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 資料檔案路徑：mysql> show variables like ‘datadir‘ \G*************************** 1. row ***************************Variable_name: datadir        Value: /var/lib/mysql/1 row in set (0.00 sec)[[email protected] bin]# ls -l /var/lib/mysql/total 28688-rw-rw---- 1 mysql mysql       56 Jan 28 17:25 auto.cnf-rw-rw---- 1 mysql mysql 18874368 Feb  2 14:48 ibdata1-rw-rw---- 1 mysql mysql  5242880 Feb  2 14:48 ib_logfile0-rw-rw---- 1 mysql mysql  5242880 Jan 28 17:21 ib_logfile1drwx------ 2 mysql root      4096 Jan 28 17:21 mysqlsrwxrwxrwx 1 mysql mysql        0 Feb  2 14:48 mysql.sockdrwx------ 2 mysql mysql     4096 Jan 28 17:21 performance_schemadrwx------ 2 mysql root      4096 Jan 28 17:21 test

3、MySQL的結構
在具體介紹MySQL的儲存引擎之前，先來介紹一下MySQL的結構。

650) this.width=650;" src="http://pic002.cnblogs.com/images/2012/111874/2012070322224517.png" />

可以看到MySQL由以下幾個部分組成：

• 串連池

• 管理服務和工具組件

• SQL介面

• 查詢分析器

• 最佳化器

• 緩衝

• 插入式儲存引擎

• 物理檔案

4、InnoDB儲存引擎：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/59/4F/wKiom1TPPmGCP5nqAAOffBwE-Vc660.jpg" title="1.png" alt="wKiom1TPPmGCP5nqAAOffBwE-Vc660.jpg" /> InnoDB是Mysql資料庫的一種儲存引擎：

   InnoDB給Mysql的表提供了 事務、復原、崩潰修複能力、多版本並發控制的事務安全、間隙鎖（可以有效防止幻讀的出現）、支援輔助索引、聚簇索引、自適應hash索引、支援熱備、行級鎖。還有InnoDB是Mysql上唯一一個提供了外鍵約束的引擎。

   InnoDB儲存引擎中，建立的表的表結構是單獨儲存的並且儲存在.frm檔案中。資料和索引儲存在一起的並且儲存在資料表空間中。但是預設情況下mysql會將資料庫的所有InnoDB表格儲存體在一個資料表空間中的。其實這種方式管理起來非常的不方便而且還不支援進階功能所以建議每個表格儲存體為一個資料表空間實現方式為：使用伺服器變數innodb_file_per_table = 1。

    如果需要頻繁的進行更新、刪除操作的資料庫也可選擇InnoDB儲存引擎。因為該儲存引擎可以實現事務提交和復原。

    InnoDB儲存引擎記憶體由以下幾個部分組成：緩衝池(buffer pool)、重做日誌緩衝池(redo log buffer)以及額外的記憶體池(additional memory pool)，分別由設定檔中的參數innodb_buffer_pool_size和innodb_log_buffer_size的大小決定。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/59/4F/wKiom1TPPs6hU3AOAAGnL8_pdKA343.jpg" title="2.png" alt="wKiom1TPPs6hU3AOAAGnL8_pdKA343.jpg" />

 InnoDB體繫結構：
    後台線程的主要作用是負責重新整理記憶體池中的資料，保證緩衝池中的記憶體緩衝的是最近的資料。此外，將已修改的資料檔案重新整理到磁碟檔案，同時保證在資料庫發生異常情況下InnoDB能恢複到正常運行狀態。
後台線程
    由於Oracle是多進程的架構（Windows下除外），因此可以通過一些很簡單的命令來得知Oracle當前啟動並執行後台進程，如ipcs命令。一般來說，Oracle的核心後台進程有CKPT、DBWn、LGWR、ARCn、PMON、SMON等。
    很多DBA問我，InnoDB儲存引擎是否也是這樣的架構，只不過是多線程版本的實現後，我決定去看InnoDB的原始碼，發現InnoDB並不是這樣對資料庫進程進行操作的。InnoDB儲存引擎是在一個被稱做master thread的線程上幾乎實現了所有的功能。
預設情況下，InnoDB儲存引擎的後台線程有7個—4個IO thread，1個master thread，1個鎖（lock）監控線程，1個錯誤監控線程。IO thread的數量由設定檔中的innodb_file_ io_threads參數控制，預設為4，如下所示。

mysql> show engine innodb status \G*************************** 1. row ***************************  Type: InnoDB  Name: Status: =====================================150202 17:15:33 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 19 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 8787 srv_idlesrv_master_thread log flush and writes: 8787----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 3OS WAIT ARRAY INFO: signal count 3Mutex spin waits 2, rounds 60, OS waits 1RW-shared spins 2, rounds 60, OS waits 2RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl------------TRANSACTIONS------------Trx id counter 2305Purge done for trx‘s n:o < 0 undo n:o < 0History list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 3, OS thread handle 0x7fe2a80c2700, query id 45 localhost root initshow engine innodb status--------FILE I/O--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (write thread)I/O thread 7 state: waiting for completed aio requests (write thread)I/O thread 8 state: waiting for completed aio requests (write thread)I/O thread 9 state: waiting for completed aio requests (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o‘s: 0, sync i/o‘s: 0Pending flushes (fsync) log: 0; buffer pool: 0161 OS file reads, 5 OS file writes, 5 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 276707, node heap has 0 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 1602871Log flushed up to   1602871Pages flushed up to 1602871Last checkpoint at  16028710 pending log writes, 0 pending chkp writes8 log i/o‘s done, 0.00 log i/o‘s/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137363456; in additional pool allocated 0Dictionary memory allocated 43148Buffer pool size   8192Free buffers       8042Database pages     150Old database pages 0Modified db pages  0Pending reads 0Pending writes: LRU 0, flush list 0 single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 150, created 0, written 10.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 150, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBMain thread process no. 4294, id 140611110070016, state: sleepingNumber of rows inserted 0, updated 0, deleted 0, read 00.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================1 row in set (0.00 sec)

附：

Oracle 體繫結構圖

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/59/4C/wKioL1TPRc2gI0bnAAKfK4JRodk509.jpg" title="3.png" alt="wKioL1TPRc2gI0bnAAKfK4JRodk509.jpg" />

本文出自 “天涯客的blog” 部落格，請務必保留此出處http://tiany.blog.51cto.com/513694/1610840

MySQL Study案例之--MySQL體系和儲存引擎