mysql匯總

聲明：本文大多數(shù)借鑒于網(wǎng)絡(luò)，如有雷同，望見諒?。。?br> 一：MySql邏輯架構(gòu)


mysql架構(gòu)圖.png

二：MySql邏輯架構(gòu)-連接層


連接層.png

當(dāng)MySQL啟動（MySQL服務(wù)器就是一個進程），等待客戶端連接，每一個客戶端連接請求，服務(wù)器都會新建一個線程處理（如果是線程池的話，則是分配一個空的線程），每個線程獨立，擁有各自的內(nèi)存處理空間，但是，如果這個請求只是查詢，沒關(guān)系，但是若是修改數(shù)據(jù)，很顯然，當(dāng)兩個線程修改同一塊內(nèi)存是會引發(fā)數(shù)據(jù)同步問題的。
三：MySql邏輯架構(gòu)-SQL處理層


SQL處理層.png

連接到服務(wù)器，服務(wù)器需要對其進行驗證，也就是用戶名、IP、密碼驗證，一旦連接成功，還要驗證是否具有執(zhí)行某個特定查詢的權(quán)限（例如，是否允許客戶端對某個數(shù)據(jù)庫某個表的某個操作）


SQL處理層.png

這一層主要功能有：SQL語句的解析、優(yōu)化，緩存的查詢，MySQL內(nèi)置函數(shù)的實現(xiàn)，跨存儲引擎功能（所謂跨存儲引擎就是說每個引擎都需提供的功能（引擎需對外提供接口）），例如：存儲過程、觸發(fā)器、視圖等。

1.如果是查詢語句（select語句），首先會查詢緩存是否已有相應(yīng)結(jié)果，有則返回結(jié)果，無則進行下一步（如果不是查詢語句，同樣調(diào)到下一步）

2.解析查詢，創(chuàng)建一個內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹），這個解析樹主要用來SQL語句的語義與語法解析；

3.優(yōu)化：優(yōu)化SQL語句，例如重寫查詢，決定表的讀取順序，以及選擇需要的索引等。這一階段用戶是可以查詢的，查詢服務(wù)器優(yōu)化器是如何進行優(yōu)化的，便于用戶重構(gòu)查詢和修改相關(guān)配置，達到最優(yōu)化。這一階段還涉及到存儲引擎，優(yōu)化器會詢問存儲引擎，比如某個操作的開銷信息、是否對特定索引有查詢優(yōu)化等。
四：MySQL緩存
轉(zhuǎn) https://blog.csdn.net/qzqanzc/article/details/80418125
mysql緩存機制就是緩存sql 文本及緩存結(jié)果，用KV形式保存在服務(wù)器內(nèi)存中，如果運行相同的sql,服務(wù)器直接從緩存中去獲取結(jié)果，不需要在再去解析、優(yōu)化、執(zhí)行sql。 如果這個表修改了，那么使用這個表中的所有緩存將不再有效，查詢緩存只得相關(guān)條目將被清空。表中得任何改變是值表中任何數(shù)據(jù)或者是結(jié)構(gòu)的改變，包括insert,update,delete,truncate,alter table,drop table或者是drop database 包括那些映射到改變了的表的使用merge表的查詢，顯然，對于頻繁更新的表，查詢緩存不合適，對于一些不變的數(shù)據(jù)且有大量相同sql查詢的表，查詢緩存會節(jié)省很大的性能
當(dāng)某個表正在寫入數(shù)據(jù)，則這個表的緩存（命中緩存，緩存寫入等）將會處于失效狀態(tài)，在Innodb中，如果某個事務(wù)修改了這張表，則這個表的緩存在事務(wù)提交前都會處于失效狀態(tài)，在這個事務(wù)提交前，這個表的相關(guān)查詢都無法被緩存
緩存存在一個hash表中，SQL上的任何字符的不同，如空格，注釋，都會導(dǎo)致緩存不命中。如果查詢有不確定的數(shù)據(jù)like now(),current_date()，那么查詢完成后結(jié)果集不會被緩存，包含不確定的數(shù)的是不會放置到緩存中
緩存會在內(nèi)存中開辟一塊內(nèi)存（query_cache_size）來維護緩存數(shù)據(jù)，其中大概有40K的空間是用來維護緩存數(shù)據(jù)的元數(shù)據(jù)的，例如空間內(nèi)存，例如空間內(nèi)存，數(shù)據(jù)表和查詢結(jié)果映射，SQL和查詢結(jié)果映射的。
mysql將這個大內(nèi)存塊分為小內(nèi)存塊（query_cache_min_res_unit),每個小塊中存儲自身的類型、大小和查詢結(jié)果數(shù)據(jù)，還有前后內(nèi)存塊的指針。
mysql需要設(shè)置單個小存儲塊大小，在SQL查詢開始（還未得到結(jié)果）時就去申請一塊內(nèi)存空間，所以即使你的緩存數(shù)據(jù)沒有達到這個大小也需要這個大小的數(shù)據(jù)塊去保存（like linux filesystem’s block)。如果超出這個內(nèi)存塊的大小，則需要再申請一個內(nèi)存塊。當(dāng)查詢完成發(fā)現(xiàn)申請的內(nèi)存有富余，則會將富余的內(nèi)存空間是放點，這就會造成內(nèi)存碎片的問題
show variables like '%query_cache_type%'--------查看緩存是否開啟
OFF: 關(guān)閉
ON: 總是打開
DEMAND: 只有明確寫了SQL_CACHE的查詢才會吸入緩存

 SET GLOBAL query_cache_size = 4000;------設(shè)置緩存內(nèi)存

五：MySQL存儲引擎
在mysql5.5.5以前，默認的存儲引擎為MyISAM，之后版本默認為InnoDB
看你的mysql現(xiàn)在已提供什么存儲引擎:
mysql> show engines;
看你的mysql當(dāng)前默認的存儲引擎:
mysql> show variables like '%storage_engine%';

MyISAM存儲引擎

適用場景：
非事務(wù)型應(yīng)用（數(shù)據(jù)倉庫，報表，日志數(shù)據(jù)）
只讀類應(yīng)用
空間類應(yīng)用（空間函數(shù)，坐標(biāo)）
MyISAM采用的是索引與數(shù)據(jù)分離的形式，將數(shù)據(jù)保存在三個文件中.frm.MYD,.MYI
MyISAM不支持行鎖，所以讀取時對表加上共享鎖，在寫入是對表加上排他鎖。由于是對整張表加鎖，相比InnoDB，在并發(fā)寫入時效率很低。
MyISAM不支持事務(wù)。
MyISAM是基于非聚簇索引進行存儲的。
MyISAM提供了大量的特性，包括全文索引，壓縮，空間函數(shù)，延遲更新索引鍵等。
進行壓縮后的表是不能進行修改的，但是壓縮表可以極大減少磁盤占用空間，因此也可以減少磁盤IO，從而提供查詢性能。
全文索引，是一種基于分詞創(chuàng)建的索引，可以支持復(fù)雜的查詢
延遲更新索引鍵，不會將更新的索引數(shù)據(jù)立即寫入到磁盤，而是會寫到內(nèi)存中的緩沖區(qū)中，只有在清除緩沖區(qū)時候才會將對應(yīng)的索引寫入磁盤，這種方式大大提升了寫入性能

存儲引擎-Innodb

Innodb是一種事務(wù)性存儲引擎， 完全支持事務(wù)的ACID特性
Redo Log 和 Undo Log，Innodb支持行級鎖（并發(fā)程度更高）

innodb_file_per_table
    ON:獨立的表空間：tablename.ibd
    OFF:系統(tǒng)表空間：ibdataX

mysql5.6以前默認為系統(tǒng)表空間
系統(tǒng)表空間和獨立表空間
①.系統(tǒng)表空間無法簡單的收縮文件大小
②.獨立表空間可以通過optimize table 收縮系統(tǒng)文件
③.系統(tǒng)表空間會產(chǎn)生IO瓶頸
④.獨立表空間可以同時向多個文件刷新數(shù)據(jù)
建議：Innodb使用獨立表空間


圖片.png

如何在兩種存儲引擎中進行選擇？

① 是否有事務(wù)操作？有，InnoDB。

②是否存儲并發(fā)修改？有，InnoDB。

③是否追求快速查詢，且數(shù)據(jù)修改較少？是，MyISAM。

④是否使用全文索引？如果不引用第三方框架，可以選擇MyISAM，但是可以選用第三方框架和InnDB效率會更高。

存儲引擎-CSV

組成：
數(shù)據(jù)以文本方式存儲在文件
① .csv文件存儲內(nèi)容
② .csm文件存儲表得元數(shù)據(jù)如表狀態(tài)和數(shù)據(jù)量
③ .frm 表結(jié)構(gòu)


圖片.png

特點：
以csv格式進行數(shù)據(jù)存儲
所有列都不能為null的
不支持索引（不適合大表，不適合在線處理）
可以對數(shù)據(jù)文件直接編輯（保存文本文件內(nèi)容）

存儲引擎-Archive

組成
以zlib對表數(shù)據(jù)進行壓縮，磁盤I/O更少
數(shù)據(jù)存儲在ARZ為后綴的文件中

特點：
只支持insert和select操作
只允許在自增ID列上加索引
使用場景
日志和數(shù)據(jù)采集應(yīng)用

存儲引擎-Memory

文件系統(tǒng)存儲特點
也稱HEAP存儲引擎，所以數(shù)據(jù)保存在內(nèi)存中
支持HASH索引和BTree索引
所有字段都是固定長度 varchar(10) = char(10)
不支持Blog和Text等大字段
Memory存儲引擎使用表級鎖
最大大小由max_heap_table_size參數(shù)決定


圖片.png

使用場景
hash索引用于查找或者是映射表（郵編和地區(qū)的對應(yīng)表）
用于保存數(shù)據(jù)分析中產(chǎn)生的中間表
用于緩存周期性聚合數(shù)據(jù)的結(jié)果表
memory數(shù)據(jù)易丟失，所以要求數(shù)據(jù)可再生

存儲引擎-Ferderated

特點
提供了訪問遠程MySQL服務(wù)器上表的方法
本地不存儲數(shù)據(jù)，數(shù)據(jù)全部放到遠程服務(wù)器上
本地需要保存表結(jié)構(gòu)和遠程服務(wù)器的連接信息
使用場景
偶爾的統(tǒng)計分析及手工查詢
如何使用
默認禁止，啟用需要再啟動時增加federated參數(shù)
mysql://user_name[:password]@hostname[:port_num]/db_name/table_name

圖片.png

MySQL鎖

鎖的概念

鎖是計算機協(xié)調(diào)多個進程或線程并發(fā)訪問某一資源的機制。
在數(shù)據(jù)庫中，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。
鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。

MySQL中的鎖

MySQL的鎖機制比較簡單
其最顯著的特點是不同的存儲引擎支持不同的鎖機制
比如：
MyISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；
InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是采用行級鎖
①表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。
②行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。
③頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
僅從鎖的角度來說：
表級鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如OLAP系統(tǒng)

行級鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

MyISAM的表鎖

MySQL的表級鎖有兩種模式：
表共享讀鎖（Table Read Lock）
表獨占寫鎖（Table Write Lock）

圖片.png

共享讀鎖語法：
加共享讀鎖 lock table 表名 read
表獨占寫鎖語法：
加獨占寫鎖 lock table 表名 write
總結(jié)：
①對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求

②對MyISAM表的讀操作，不會阻塞當(dāng)前session對表讀，當(dāng)對表進行修改會保存

③一個session使用LOCK TABLE命令給表f加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；

④另外一個session可以查詢表中的記錄，但更新就會出現(xiàn)鎖等待

⑤對 MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；

⑥對 MyISAM表的寫操作，當(dāng)前session可以對本表做CRUD,但對其他表進行操作會報錯

InnoDb行鎖

在mysql 的 InnoDB引擎支持行鎖

行鎖:

共享鎖又稱：讀鎖。當(dāng)一個事務(wù)對某幾行上讀鎖時，允許其他事務(wù)對這幾行進行讀操作，但不允許其進行寫操作，也不允許其他事務(wù)給這幾行上排它鎖，但允許上讀鎖。

排它鎖又稱：寫鎖。當(dāng)一個事務(wù)對某幾個上寫鎖時，不允許其他事務(wù)寫，但允許讀。更不允許其他事務(wù)給這幾行上任何鎖。包括寫鎖。
語法
上共享鎖的寫法：lock in share mode
例如：select * from 表 where 條件 lock in share mode；
上排它鎖的寫法：for update
例如：select * from 表 where 條件 for update；
注意：

1.兩個事務(wù)不能鎖同一個索引。

2.insert ，delete ， update在事務(wù)中都會自動默認加上排它鎖。

3.行鎖必須有索引才能實現(xiàn)，否則會自動鎖全表，那么就不是行鎖了。

表鎖

和MyISAM差別不大

注意：開啟一個新事務(wù)的時候會解鎖表
面試題：系統(tǒng)運行一段時間，數(shù)據(jù)量已經(jīng)很大，這時候系統(tǒng)升級，有張表A需要增加個字段，并發(fā)量白天晚上都很大，請問怎么修改表結(jié)構(gòu)

  1. 首先創(chuàng)建一個和你要執(zhí)行的alter操作的表一樣的空的表結(jié)構(gòu)。

  2. 執(zhí)行我們賦予的表結(jié)構(gòu)的修改，然后copy原表中的數(shù)據(jù)到新表里面。

  3. 在原表上創(chuàng)建一個觸發(fā)器在數(shù)據(jù)copy的過程中，將原表的更新數(shù)據(jù)的操作全部更新到新的表中來。

  4. copy完成之后，用rename table 新表代替原表，默認刪除原表。

MySQL事務(wù)

什么存儲引擎支持事務(wù)

1.查看數(shù)據(jù)庫是否支持事務(wù)（InnoDB支持）？
show engines;

2.查看mysql當(dāng)前默認的存儲引擎？
show variables like '%storage_engine%';

3.查看某張表的存儲引擎？
show create table 表名 ;

4.對于表的存儲結(jié)構(gòu)的修改？
建立InnoDB 表：Create table .... type=InnoDB； Alter table table_name type=InnoDB;

事務(wù)的特性

事務(wù)應(yīng)該具有4個屬性：原子性、一致性、隔離性、持久性。這四個屬性通常稱為ACID特性。

①原子性（atomicity）。一個事務(wù)是一個不可分割的工作單位，事務(wù)中包括的諸操作要么都做，要么都不做。

②一致性（consistency）。事務(wù)必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)。一致性與原子性是密切相關(guān)的。

③隔離性（isolation）。一個事務(wù)的執(zhí)行不能被其他事務(wù)干擾。即一個事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個事務(wù)之間不能互相干擾。

④持久性（durability）。持久性也稱永久性（permanence），指一個事務(wù)一旦提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久性的。接下來的其他操作或故障不應(yīng)該對其有任何影響。

事務(wù)并發(fā)問題

①臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)

②不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)，事務(wù) B 在事務(wù)A多次讀取的過程中，對數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時，結(jié)果 不一致。

③幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫中所有學(xué)生的成績從具體分數(shù)改為ABCDE等級，但是系統(tǒng)管理員B就在這個時候插入了一條具體分數(shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，這就叫幻讀。

不可重復(fù)讀的和幻讀很容易混淆，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除。解決不可重復(fù)讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

①未提交讀（READ UNCOMMITED）臟讀

set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

②已提交讀 （READ COMMITED）不可重復(fù)讀

set SESSION TRANSACTION ISOLATION LEVEL read committed;

③可重復(fù)讀（REPEATABLE READ）

set SESSION TRANSACTION ISOLATION LEVEL repeatable read;

④可串行化（SERIALIZABLE）

set SESSION TRANSACTION ISOLATION LEVEL serializable;

事務(wù)隔離級別
圖片.png

事務(wù)隔離級別為可重復(fù)讀時，如果有索引（包括主鍵索引）的時候，以索引列為條件更新數(shù)據(jù)，會存在間隙鎖間、行鎖、頁鎖的問題，從而鎖住一些行；如果沒有索引，更新數(shù)據(jù)時會鎖住整張表

事務(wù)隔離級別為串行化時，讀寫數(shù)據(jù)都會鎖住整張表

隔離級別越高，越能保證數(shù)據(jù)的完整性和一致性，但是對并發(fā)性能的影響也越大，對于多數(shù)應(yīng)用程序，可以優(yōu)先考慮把數(shù)據(jù)庫系統(tǒng)的隔離級別設(shè)為Read Committed，它能夠避免臟讀取，而且具有較好的并發(fā)性能

事務(wù)的語法

開啟事務(wù)

1、begin
2、START TRANSACTION（推薦）
3、begin work

事務(wù)回滾 rollback
事務(wù)提交 commit
還原點savepoint

三大范式

①第一范式： 數(shù)據(jù)庫表中的所有字段都只具有單一屬性
單一屬性的列是由基本數(shù)據(jù)類型所構(gòu)成的
設(shè)計出來的表都是簡單的二維表
②：第二范式：要求表中只具有一個業(yè)務(wù)主鍵，也就是說符合第二范式的表不能存在非主鍵列只對部分主鍵的依賴關(guān)系
③：第三范式：指每一個非主屬性既不部分依賴于也不傳遞依賴于業(yè)務(wù)主鍵，也就是在第二范式的基礎(chǔ)上相處了非主鍵對主鍵的傳遞依賴

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 一、MySQL優(yōu)化 MySQL優(yōu)化從哪些方面入手： （1）存儲層（數(shù)據(jù)） 構(gòu)建良好的數(shù)據(jù)結(jié)構(gòu)?？梢源蟠蟮奶嵘覀僑...
    寵辱不驚丶歲月靜好閱讀 2,654評論 1贊 8
  • MySQL不權(quán)威總結(jié) 歡迎閱讀 本文并非事無巨細的mysql學(xué)習(xí)資料，而是選擇其中重要、困難、易錯的部分進行系統(tǒng)地...
    liufxlucky365閱讀 2,812評論 0贊 26
  • 索引 數(shù)據(jù)庫中的查詢操作非常普遍，索引就是提升查找速度的一種手段 索引的類型 從數(shù)據(jù)結(jié)構(gòu)角度分 1.B+索引：傳統(tǒng)...
    一凡呀閱讀 3,217評論 0贊 8
  • MySQL數(shù) 據(jù)庫引擎取決于MySQL在安裝的時候是如何被編譯的。要添加一個新的引擎，就必須重新編譯MYSQL。在...
    時待吾閱讀 1,718評論 0贊 4
  • 冰雪清透閱讀 252評論 0贊 0

友情鏈接更多精彩內(nèi)容