一、架構(gòu)

MySQL邏輯架構(gòu)整體分為三層，最上層為客戶端，并非MySQL獨(dú)有，諸如：連接處理，授權(quán)認(rèn)證，安全等功能均在這一層處理
MySQL大多數(shù)核心服務(wù)均在中間這一層，包括查詢解析，分析優(yōu)化，緩存，內(nèi)置函數(shù)，所有的跨存儲引擎功能也在這一層實(shí)現(xiàn)：存儲過程，觸發(fā)器，視圖等。
最下層為存儲引擎，負(fù)責(zé)MySQL中的數(shù)據(jù)存儲和提取

1.MySQL查詢過程

連接層（客戶端/服務(wù)端通信協(xié)議）

• MySQL客戶端/服務(wù)端的通信協(xié)議通常是半雙工，要么服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么客戶端向服務(wù)器發(fā)送數(shù)據(jù)，當(dāng)客戶端要查詢的時候向服務(wù)端發(fā)送消息，進(jìn)行TCP/IP連接或者socket連接，并且向服務(wù)端提供自己的用戶名和密碼，服務(wù)端提供專用連接線程，接受用戶的SQL，后返回結(jié)果

SQL層

• 接受上層傳送的SQL語句
• 進(jìn)行語義檢查：判斷sql語句的類型
• 權(quán)限檢查
• 對sql語句進(jìn)行解析
  • 通過關(guān)鍵字將sql語句進(jìn)行解析，并生成一顆對應(yīng)的解析樹。這個過程解析器主要通過語法規(guī)則來驗(yàn)證和校驗(yàn)，比如sql中是否使用了錯誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等。預(yù)處理會根據(jù)M有SQL規(guī)則進(jìn)一步檢查解析樹是否合法等
• 進(jìn)行查詢優(yōu)化
  • 重新定義表的關(guān)聯(lián)順序
  • 優(yōu)化MIN()和MAX()函數(shù)（找出某列的最小值，如果該列有索引，只需要查找B+tree索引最左端）
  • 提前終止查詢（比如：使用limit時，查找到滿足數(shù)量的結(jié)果集合后立即終止查詢）
  • 優(yōu)化排序（老版本中先讀取行指針和需要排序的字段你在內(nèi)存中對其排序，然后再更具排序結(jié)果去讀取數(shù)據(jù)行，新版本中采用單詞傳輸排序，一次性讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序，對于I/O密集型應(yīng)用，會提高效率）
• 性能優(yōu)化
  • 用多個小表代替一個大表，不要過度設(shè)計(jì)
  • 批量插入代替循環(huán)單挑插入
  • 合理控制緩存空間大小，一般來說其大小設(shè)置為及時找比較合適
• 查詢緩存（不建議輕易打開查詢緩存）
  • 在解析一個查詢語句錢，如果查詢緩存時打開的，那么mysql會檢查這個查詢語句是否名字拆線呢緩存中的數(shù)據(jù)，如果當(dāng)前查詢恰好命中查詢緩存，在檢查一次用戶權(quán)限后直接返回緩存中的結(jié)果
  • mysql將緩存放在一個引用表中，可以理解為一個類似于HashMap的數(shù)據(jù)結(jié)構(gòu)
  • 任何的查詢語句在開始之前都必須經(jīng)過檢查，即使這條SQL語句永遠(yuǎn)不會命中緩存
  • 如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后，會將結(jié)果存入緩存，也會帶來額外的系統(tǒng)消耗

存儲引擎層（類似于linux中的文件）
負(fù)責(zé)根據(jù)SQL層的執(zhí)行結(jié)果，從磁盤上拿數(shù)據(jù)，返回給客戶端

2.MyISAM和InnoDB的特點(diǎn)

MyISAM引擎特點(diǎn)：

• 不支持事務(wù)（保證數(shù)據(jù)安全ACID）
• 表級鎖定
• 讀寫相互阻塞，寫時不能讀，讀時不能寫
• 只緩存索引
• 讀取數(shù)據(jù)較快，占用資源較少
• 不支持MVCC（多版本并發(fā)控制機(jī)制）高并發(fā)
• 奔潰恢復(fù)性較差

使用場景：
只讀（或者寫較少時），表較小
InnoDB引擎特點(diǎn)

• 行級鎖
• 支持事務(wù)，適合處理大量短期事務(wù)
• 讀寫阻塞與事務(wù)隔離級別有關(guān)
• 可緩存數(shù)據(jù)和索引
• 奔潰后恢復(fù)性較好
• 支持MVCC高并發(fā)
• MYSQL5.5后支持全文索引，也是5.5后的默認(rèn)搜索引擎

備份：備份的時候備份業(yè)務(wù)數(shù)據(jù)庫和mysql數(shù)據(jù)庫（因?yàn)閙ysql數(shù)據(jù)庫中有用戶及權(quán)限設(shè)置）

3.修改mysql的最大并發(fā)連接數(shù)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global max_connections =2000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)
# 也可在配置文件中修改
[root@server ~]# vim /etc/my.cnf

4.服務(wù)器狀態(tài)變量

服務(wù)器狀態(tài)變量：分全局和會話兩種
狀態(tài)變量（只讀）：用于保存mysqld運(yùn)行中的統(tǒng)計(jì)數(shù)據(jù)變量，不可更改

show global status;
show [session] status;

5.服務(wù)器變量SQL_MODE

SQL_MODE：對其設(shè)置可以完成一些約束檢查的工作,可分別進(jìn)行全局的設(shè)置或當(dāng)前會話的設(shè)置
常見MODE:

• NO_AUTO_CREATE_USER： 禁止GRANT創(chuàng)建密碼為空的用戶
• NO_ZERO_DATE：在嚴(yán)格模式，不允許使用’0000-00-00’的時間
• ONLY_FULL_GROUP_BY： 對于GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么將認(rèn)為這個SQL是不合法的
• NO_BACKSLASH_ESCAPES： 反斜杠""作為普通字符而非轉(zhuǎn)義字符
• PIPES_AS_CONCAT： 將"||"視為連接操作符而非"或"運(yùn)算符

二、索引

1.index索引介紹

索引：索引是排序的快速查找的特殊數(shù)據(jù)結(jié)構(gòu)，定義作為查找條件的字段，又稱為key，索引通過存儲引擎實(shí)現(xiàn)，加快查詢速度

2.索引優(yōu)缺點(diǎn)

索引的優(yōu)點(diǎn)是可以提高檢索數(shù)據(jù)的速度，這是創(chuàng)建索引的最主要的原因；對于有依賴關(guān)系的子表和父表之間的；聯(lián)合查詢時，可以提高查詢速度；使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時，同樣可以顯著節(jié)省查詢中分組和排序的時間。
索引的缺點(diǎn)是創(chuàng)建和維護(hù)索引需要耗費(fèi)時間，耗費(fèi)時間的數(shù)量隨著數(shù)據(jù)量的增加而增加；索引需要占用物理空間，每一個索引要占一定的物理空間；增加、刪除和修改數(shù)據(jù)時，要動態(tài)的維護(hù)索引，造成數(shù)據(jù)的維護(hù)速度降低了
優(yōu)點(diǎn)

• 降低服務(wù)需要掃描的數(shù)據(jù)量，減少I/O次數(shù)
• 索引可以幫助服務(wù)器避免排序和使用臨時表
• 索引可以幫助將隨機(jī)I/O轉(zhuǎn)為順序 I/O

缺點(diǎn)：

• 占用額外空間
• 影響插入速度

索引類型：

• B+ TREE、HASH、R TREE、FULL TEXT
• 聚簇（集）索引、非聚簇索引：數(shù)據(jù)和索引是否存儲在一起
• 主鍵索引、二級（輔助）索引
• 稠密索引、稀疏索引：是否索引了每一個數(shù)據(jù)項(xiàng)
• 簡單索引、組合索引: 是否是多個字段的索引
• 左前綴索引：取前面的字符做索引
• 覆蓋索引：從索引中即可取出要查詢的數(shù)據(jù)，性能高

3.B+TREE

B+tree索引：按順序儲存，每一個葉子節(jié)點(diǎn)到根節(jié)點(diǎn)的舉例是相同的；左前綴索引，適合查詢范圍類的索引；
B-TREE

B+TREE

頁面搜索嚴(yán)禁做模糊或者全模糊，如果需要擇走搜索引擎來解決

說明：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引

可以使用B+TREE索引的查詢類型

• 全值匹配：精確所有索引列
• 匹配最左前綴：只使用索引的第一列
• 匹配列前綴：只匹配一列值開頭部分
• 精確匹配某一列范圍并匹配另一列
• 只訪問索引的查詢

B+TREE索引的限制

• 如不從最左列開始，則無法使用索引
• 不能跳過索引中的列

4.索引優(yōu)化

• 獨(dú)立的使用列：經(jīng)歷避免其參與運(yùn)算，獨(dú)立的列指索引列不能是表達(dá)式的而一部分，也不能是函數(shù)的參數(shù)，在where條件中，始終將索引列單獨(dú)放在比較符號的一側(cè)，盡量不在列上進(jìn)行運(yùn)算
• 左前綴索引：構(gòu)建指定索引字段的左側(cè)的字符數(shù)，要通過索引選擇（不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值）來評估，盡量使用短索引，如果可以，指定一個前綴長度
• 多列索引和索引順序：AND操作時更適合使用多列索引，而非為每個列創(chuàng)建單獨(dú)的索引；
  • 當(dāng)出現(xiàn)多個索引做相交操作時（多個AND條件），通常來說一個包含所有相關(guān)列的索引要優(yōu)于多個獨(dú)立索引
  • 當(dāng)出現(xiàn)多個索引做聯(lián)合操作時（多個OR條件），對結(jié)果集的合并、排序等操作需要耗費(fèi)大量的CPU和內(nèi)存資源，特別是當(dāng)其中的某些索引的選擇性不高，需要返回大量合并數(shù)據(jù)時，查詢成本更高
• 選擇合適的索引列順序：無排序和分組時。將選擇性最高放左側(cè)
• 只要列中含有NULL值，就最好不要在此列設(shè)置索引，復(fù)合索引如果有null值，此列在使用時也不會使用索引
• 對于經(jīng)常在where子句使用的列，最好設(shè)置索引
• 對于有多個列where或者order by子句，應(yīng)該建立復(fù)合索引
• 對于like語句，以%或者_(dá)開頭的不會使用索引，以%結(jié)尾會使用索引
• 盡量不要使用not in 和<>操作，雖然可能使用索引，但性能不高
• 不要使用RLIK正則表達(dá)式會導(dǎo)致索引失效
• 查詢時，能不用就不用，盡量寫全字段名，比如select id，name,age from students;
• 大部分情況連接效率遠(yuǎn)大于子查詢
• 在有大量記錄的表分頁時使用limit
• 對于經(jīng)常使用的查詢，可以開啟查詢緩存
• 多使用explain和profile分析查詢語句
• 查看慢日志，找出執(zhí)行時間長的sql語句優(yōu)化

三、并發(fā)控制

1.鎖機(jī)制

類型：

  - 讀鎖：共享鎖，也成為S鎖，只讀不可寫（包括當(dāng)前事務(wù)），多個讀互不阻塞
  - 寫鎖：獨(dú)占鎖，排它鎖，也成為X鎖，寫鎖會阻塞其他事務(wù)（不包括當(dāng)前事務(wù)）的的讀和寫
  - S鎖和S鎖是兼容的，X鎖和其他鎖都不兼容
     - 例：事務(wù) T1 獲取了一個行 r1 的 S 鎖，另外事務(wù) T2 可以立即獲得行 r1 的 S 鎖，此時 T1 和 T2 共同獲得行 r1 的 S 鎖，此種情況稱為鎖兼容，但是另外一個事務(wù) T2 此時如果想獲得行 r1 的 X 鎖，則必須等待 T1 對行 r1 鎖的釋放，此種情況也稱為鎖沖突

鎖粒度：

  - 表級鎖：MyISAM
  - 行級鎖：InnoDB

實(shí)現(xiàn)

  - 存儲引擎：自行實(shí)現(xiàn)其鎖策略和鎖粒度
  - 服務(wù)器級：實(shí)現(xiàn)了鎖、表級鎖、用戶可顯示請求

分類：

  - 隱式鎖：由于存儲引擎自動施加鎖
  - 顯式鎖：用戶手動請求

鎖策略：在鎖粒度以及數(shù)據(jù)安全性尋求的平衡機(jī)制

四、事務(wù)

簡介：事務(wù)Transactios：一組原子性的sql語句，或一個獨(dú)立工作單元
事務(wù)日志：記錄事務(wù)信息，實(shí)現(xiàn)undo，redo故障恢復(fù)功能

1.ACID特性：

• A：automictiy原子性：整個事務(wù)中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾
• C：consistency一致性：數(shù)據(jù)庫總是從一個狀態(tài)轉(zhuǎn)換為另一個一致性狀態(tài)，類似于能量守恒定律
• I：isolation隔離性：一個事務(wù)所作出的操作是在提交之前，是不能為其他事務(wù)所見；隔離有多種隔離級別，實(shí)現(xiàn)并發(fā)（事務(wù)的隔離性是多個用戶并發(fā)訪問數(shù)據(jù)庫時，數(shù)據(jù)庫為每一個用戶開啟的事務(wù)，不能被其他事務(wù)的操作數(shù)據(jù)所干擾，多個并發(fā)事務(wù)之間要相互隔離。）
• D：Durability持久性：持久性是指一個事務(wù)一旦被提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的，所做的修改永久保存于數(shù)據(jù)庫中，接下來即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響，

begin
說明:在5.5 以上的版本，不需要手工begin，只要你執(zhí)行的是一個DML，會自動在前面加一個begin命令。
commit：提交事務(wù)
完成一個事務(wù)，一旦事務(wù)提交成功 ，就說明具備ACID特性了。
rollback ：回滾事務(wù)
將內(nèi)存中，已執(zhí)行過的操作，回滾回去

注意：只有事務(wù)型存儲引擎中的DML語句方能支持此類操作
自動提交：
set autocommit={1|0} 默認(rèn)未1，未0時設(shè)為非自動提交，為0可以提高數(shù)據(jù)庫性能
死鎖：
兩個或多個事務(wù)在同一資源相互占用，并請求鎖定對方占用的資源的狀態(tài)
開始事務(wù)流程：

1、檢查autocommit是否為關(guān)閉狀態(tài)
select @@autocommit;
或者：
show variables like 'autocommit';

2、開啟事務(wù),并結(jié)束事務(wù)
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback;

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;

對于使用 InnoDB 存儲引擎的表來說，它的聚簇索引記錄中都包含 3 個隱藏列

• db_row_id：隱藏的行 ID。在沒有自定義主鍵也沒有 Unique 鍵的情況下，會使用該隱藏列作為主鍵。
• db_trx_id：操作這個數(shù)據(jù)的事務(wù) ID，也就是最后一個對該數(shù)據(jù)進(jìn)行插入或更新的事務(wù) ID。
• dbroll_ptr：回滾指針，也就是指向這個記錄的 Undo Log 信息。Undo Log 中存儲了回滾需要的數(shù)據(jù)

2.事務(wù)的隔離級別

多個線程開啟各自事務(wù)操作數(shù)據(jù)庫中數(shù)據(jù)時，數(shù)據(jù)庫系統(tǒng)要負(fù)責(zé)隔離操作，以保證各個線程在獲取數(shù)據(jù)時的準(zhǔn)確性。
如果不考慮隔離性，可能會引發(fā)如下問題：

• 幻讀 : 是指在一個事務(wù)內(nèi)讀取到了別的事務(wù)插入的數(shù)據(jù)，導(dǎo)致前后讀取不一致。
  • 事務(wù)A 按照一定條件進(jìn)行數(shù)據(jù)讀取， 期間事務(wù)B 插入了相同搜索條件的新數(shù)據(jù)，事務(wù)A再次按照原先條件進(jìn)行讀取時，發(fā)現(xiàn)了事務(wù)B 新插入的數(shù)據(jù) 稱為幻讀。
• 不可重復(fù)讀取 ：是指在數(shù)據(jù)庫訪問中，一個事務(wù)范圍內(nèi)兩個相同的查詢卻返回了不同數(shù)據(jù)。
  • 在一個事務(wù)內(nèi)，多次讀同一個數(shù)據(jù)。在這個事務(wù)還沒有結(jié)束時，另一個事務(wù)也訪問該同一數(shù)據(jù)并修改數(shù)據(jù)。那么，在第一個事務(wù)的兩次讀數(shù)據(jù)之間。由于另一個事務(wù)的修改，那么第一個事務(wù)兩次讀到的數(shù)據(jù)可能不一樣，這樣就發(fā)生了在一個事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為不可重復(fù)讀，即原始讀取不可重復(fù)。
• 臟讀：指一個事務(wù)讀取了另外一個事務(wù)未提交的數(shù)據(jù)

# 這是非常危險(xiǎn)的，假設(shè)Ａ向Ｂ轉(zhuǎn)帳100元，對應(yīng)sql語句如下所示
1. update account set money=money+100 where name=‘b’;    
2. update account set money=money-100 where name=‘a(chǎn)’;

數(shù)據(jù)庫共定義了四種隔離級別：

• Serializable：可避免臟讀、不可重復(fù)讀、虛讀情況的發(fā)生。（串行化）
• Repeatable read：可避免臟讀、不可重復(fù)讀情況的發(fā)生。（可重復(fù)讀）
• Read committed：可避免臟讀情況發(fā)生（讀已提交）?？勺x取到提交數(shù)據(jù)，但未提交數(shù)據(jù)不可讀，產(chǎn)生不可重復(fù)讀，即可讀取到多個提交數(shù)據(jù)，導(dǎo)致每次
  讀取數(shù)據(jù)不一致
• Read uncommitted：最低級別，以上情況均無法保證。(讀未提交)

MVCC和事務(wù)的隔離級別：
MVCC（多版本并發(fā)控制機(jī)制）只在READ COMMITTED和REPEATABLE READ兩個隔離級別下工作。其
他兩個隔離級別都和MVCC不兼容,因?yàn)镽EAD UNCOMMITTED總是讀取最新的數(shù)據(jù)行，而不是符合當(dāng)前
事務(wù)版本的數(shù)據(jù)行。而SERIALIZABLE則會對所有讀取的行都加鎖文章來源地址http://www.zghlxwxcb.cn/news/detail-707660.html

到了這里，關(guān)于MySQL的架構(gòu)和性能優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！