9、MySQL邏輯架構介紹

一、概述:

MySQL按照邏輯劃分主要分為三層：服務層，核心層和存儲引擎層其各層在整個架構中所扮演的角色如下：

服務層：為用戶端做連接配接處理，授權和安全認證相關工作

核心層：查詢解析，分析，優化SQL語句，緩存結果等等

存儲引擎層：存儲和提取資料及事物的處理

角色分擔

用戶端：MySQL用戶端指的是不同程式語言編寫的前端應用程式，及所調用的API接口(Java語言：JDBC是一種用于執行SQL語句的javaAPI)，php語言的PDO擴充為PHP通路資料庫定義了一個輕量級的異質性的接口，它提供了一個資料通路抽象層，這樣無論使用什麼資料庫都可以通過一緻的函數執行查詢和提取資料。

服務層：MySQL屬于單程序多線層結構。

核心層：完成資料的查詢，緩存及優化的操作。

存儲引擎層：MyISAMM，InnoDB，CLuster和Memory

存儲引擎層是最貼近檔案系統的功能層。

MySQL總體架構：

二、MySQL常用的存儲引擎

檢視目前MySQL所支援的存儲引擎

mysql> show engines;

2.1 MyISAM存儲引擎：

特點：支援全文索引，

能夠對整張表進行加鎖，不支援行鎖，及鎖粒度較大。

缺點：不支援事物，及沒有rollback功能。

應用：日志系統，讀表的操作，沒有事物第并發的網站

2.2 InnoDB存儲引擎：

特點：MySQL5.7之前的版本不支援全文索引，5.7及以後的支援

支援行級鎖，鎖粒度小

支援ACID(事物完整性和異質性)

獨有的聚集索引主鍵設計方式，可大發提升并發讀寫性能

支援外鍵，支援崩潰資料的自我修複

注意問題

a) 所有InnoDB資料表都建立一個和業務無關的自增數字型作為主鍵，對保證性能很有幫助；

b) 杜絕使用text/blob，确實需要使用的，盡可能拆分出去成一個獨立的表；

c) 時間建議使用 TIMESTAMP 類型存儲；

d) IPV4 位址建議用 INT UNSIGNED 類型存儲；

e) 性别等非是即非的邏輯，建議采用 TINYINT 存儲，而不是 CHAR(1)；bool

f) 存儲較長文本内容時，建議采用JSON/BSON格式存儲；

2．3memory存儲引擎：

（1）memory存儲引擎相比前面的一些存儲引擎，有點不一樣，其使用存儲在記憶體中的資料來建立表，而且所有的資料也都存儲在記憶體中。

（2）每個基于memory存儲引擎的表實際對應一個磁盤檔案，該檔案的檔案名和表名是相同的，類型為.frm。該檔案隻存儲表的結構，而其資料檔案，都是存儲在記憶體中，這樣有利于對資料的快速處理，提高整個表的處理能力。

（3）memory存儲引擎預設使用哈希（HASH）索引，其速度比使用B-+Tree型要快，如果讀者希望使用B樹型，則在建立的時候可以引用。

（4）memory存儲引擎檔案資料都存儲在記憶體中，如果mysqld程序發生異常，重新開機或關閉機器這些資料都會消失。是以memory存儲引擎中的表的生命周期很短，一般隻使用一次。

主：表級鎖：分為讀鎖和寫鎖，其中讀鎖是共享的，可以供多人使用，而寫鎖是排他的，當目前表被寫鎖鎖定的時候，其他無法讀取和修改，如果是讀鎖則是可以其他程式可以檢視。

注意：行級鎖可能會造成“死鎖”現象

原因分析：

1、MySQL行級鎖并不是直接鎖記錄，而是鎖索引，索引分為主鍵和非主鍵索引兩種，如果一條SQL語句操作了主鍵索引，那麼MySQL就會鎖定這個主鍵索引，如果SQL語句操作的事非主鍵索引，那麼MySQL會先鎖定這個非主鍵索引，再去鎖定主鍵索引。

2、在update和delete操作時MySQL不僅會鎖定所有where條件掃描過的索引,還會鎖定相鄰索引(被修改的字段)。

3、MySQL鎖定狀态

show processlist指令可以檢視目前那些線程正在運作，如果使用者擁有SUPER權限可以檢視所用線程，如果線程的狀态有update或init某個表，則說明此事程序的status為updateing data或在sending data。

對于“too many connections”的錯誤資訊，并且想要了解正在發生的情況則show processlist則非常有用。

相關檢視指令：

show processlist；預設顯示100條資訊

show full processlist；顯示全部線程資訊

show open tables；檢視哪些表是打開的。

檢視伺服器狀态

mysql> show status like '%lock%';

檢視innodb運作時的資訊

mysql> show engine innodb status\G

檢視伺服器配置參數：

mysql> show variables like '%timeout%'；

四、指定MySQL存儲引擎

4.1 修改配置檔案是設定預設存儲引擎

vim /etc/my.cnf

default-storage-engine=innodb

儲存之後重新開機存儲引擎

建立表時指定存儲引擎

create table <tb_name>(<col> ) ennine=Innodb default charset=utf8;

本文轉自 CARYFLASH 51CTO部落格，原文連結:http://blog.51cto.com/maoxiaoxiong/2044730