建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
mysql原理
MySQL基本的邏輯架構(gòu)包含三個(gè)部分：存儲(chǔ)引擎、核心服務(wù)、客戶端層。存儲(chǔ)引擎負(fù)責(zé)MySQL中的數(shù)據(jù)存儲(chǔ)和提取。核心服務(wù)包括查詢(xún)解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)等?？蛻舳藢影B接處理、授權(quán)認(rèn)證、安全等功能，但并非MySQL所獨(dú)有。
網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)公司！專(zhuān)注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、重慶小程序開(kāi)發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了曲松免費(fèi)建站歡迎大家使用！
MySQL查詢(xún)過(guò)程是遵循一些原則讓MySQL的優(yōu)化器能夠按照預(yù)想的合理方式運(yùn)行的過(guò)程。MySQL客戶端/服務(wù)端通信協(xié)議是“半雙工”的：在任一時(shí)刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生，一旦一端開(kāi)始發(fā)送消息，另一端要接收完整個(gè)消息才能響應(yīng)它。
Mysql的工作原理是什么
Mysql?工作原理圖
Mysql是由SQL接口，解析器，優(yōu)化器，緩存，存儲(chǔ)引擎組成的。
mysql原理圖各個(gè)組件說(shuō)明：
1. connectors
與其他編程語(yǔ)言中的sql?語(yǔ)句進(jìn)行交互，如php、java等。
2. Management Serveices Utilities
系統(tǒng)管理和控制工具
3. Connection Pool (連接池)
管理緩沖用戶連接，線程處理等需要緩存的需求
4. SQL Interface (SQL接口)
接受用戶的SQL命令，并且返回用戶需要查詢(xún)的結(jié)果。比如select from就是調(diào)用SQL Interface
5. Parser?（解析器）
SQL命令傳遞到解析器的時(shí)候會(huì)被解析器驗(yàn)證和解析。
主要功能：
a .?將SQL語(yǔ)句分解成數(shù)據(jù)結(jié)構(gòu)，并將這個(gè)結(jié)構(gòu)傳遞到后續(xù)步驟，后面SQL語(yǔ)句的傳遞和處理就是基于這個(gè)結(jié)構(gòu)的
b. ?如果在分解構(gòu)成中遇到錯(cuò)誤，那么就說(shuō)明這個(gè)sql語(yǔ)句是不合理的，語(yǔ)句將不會(huì)繼續(xù)執(zhí)行下去
6. Optimizer (查詢(xún)優(yōu)化器)
SQL語(yǔ)句在查詢(xún)之前會(huì)使用查詢(xún)優(yōu)化器對(duì)查詢(xún)進(jìn)行優(yōu)化(產(chǎn)生多種執(zhí)行計(jì)劃,最終數(shù)據(jù)庫(kù)會(huì)選擇最優(yōu)化的方案去執(zhí)行,盡快返會(huì)結(jié)果)?他使用的是“選取-投影-聯(lián)接”策略進(jìn)行查詢(xún)。
用一個(gè)例子就可以理解：?select uid,name from user where gender = 1;
這個(gè)select?查詢(xún)先根據(jù)where?語(yǔ)句進(jìn)行選取，而不是先將表全部查詢(xún)出來(lái)以后再進(jìn)行g(shù)ender過(guò)濾
這個(gè)select查詢(xún)先根據(jù)uid和name進(jìn)行屬性投影，而不是將屬性全部取出以后再進(jìn)行過(guò)濾
將這兩個(gè)查詢(xún)條件聯(lián)接起來(lái)生成最終查詢(xún)結(jié)果.
7. Cache和Buffer (查詢(xún)緩存)
如果查詢(xún)緩存有命中的查詢(xún)結(jié)果，查詢(xún)語(yǔ)句就可以直接去查詢(xún)緩存中取數(shù)據(jù)。
這個(gè)緩存機(jī)制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權(quán)限緩存等
8.Engine (存儲(chǔ)引擎)
存儲(chǔ)引擎是MySql中具體的與文件打交道的子系統(tǒng)。也是Mysql最具有特色的一個(gè)地方。
Mysql的存儲(chǔ)引擎是插件式的。它根據(jù)MySql AB公司提供的文件訪問(wèn)層的一個(gè)抽象接口來(lái)定制一種文件訪問(wèn)機(jī)制（這種訪問(wèn)機(jī)制就叫存儲(chǔ)引擎）
SQL?語(yǔ)句執(zhí)行過(guò)程
數(shù)據(jù)庫(kù)通常不會(huì)被直接使用，而是由其他編程語(yǔ)言通過(guò)SQL語(yǔ)句調(diào)用mysql，由mysql處理并返回執(zhí)行結(jié)果。那么Mysql接受到SQL語(yǔ)句后，又是如何處理
首先程序的請(qǐng)求會(huì)通過(guò)mysql的connectors與其進(jìn)行交互，請(qǐng)求到處后，會(huì)暫時(shí)存放在連接池（connection pool)中并由處理器（Management Serveices Utilities）管理。當(dāng)該請(qǐng)求從等待隊(duì)列進(jìn)入到處理隊(duì)列，管理器會(huì)將該請(qǐng)求丟給SQL接口（SQL Interface）。SQL接口接收到請(qǐng)求后，它會(huì)將請(qǐng)求進(jìn)行hash處理并與緩存中的結(jié)果進(jìn)行對(duì)比，如果完全匹配則通過(guò)緩存直接返回處理結(jié)果；否則，需要完整的走一趟流程：
(1)由SQL接口丟給后面的解釋器（Parser），解釋器會(huì)判斷SQL語(yǔ)句正確與否，若正確則將其轉(zhuǎn)化為數(shù)據(jù)結(jié)構(gòu)。
(2)解釋器處理完，便來(lái)到后面的優(yōu)化器（Optimizer），它會(huì)產(chǎn)生多種執(zhí)行計(jì)劃,最終數(shù)據(jù)庫(kù)會(huì)選擇最優(yōu)化的方案去執(zhí)行,盡快返會(huì)結(jié)果。
(3)確定最優(yōu)執(zhí)行計(jì)劃后，SQL語(yǔ)句此時(shí)便可以交由存儲(chǔ)引擎（Engine）處理，存儲(chǔ)引擎將會(huì)到后端的存儲(chǔ)設(shè)備中取得相應(yīng)的數(shù)據(jù)，并原路返回給程序。
注意點(diǎn)
(1)如何緩存查詢(xún)數(shù)據(jù)
存儲(chǔ)引擎處理完數(shù)據(jù)，并將其返回給程序的同時(shí)，它還會(huì)將一份數(shù)據(jù)保留在緩存中，以便更快速的處理下一次相同的請(qǐng)求。具體情況是，mysql會(huì)將查詢(xún)的語(yǔ)句、執(zhí)行結(jié)果等進(jìn)行hash，并保留在cache中，等待下次查詢(xún)。
(2)buffer與cache的區(qū)別
從mysql原理圖可以看到，緩存那里實(shí)際上有buffer和cache兩個(gè)，那它們之間的區(qū)別：簡(jiǎn)單的說(shuō)就是，buffer是寫(xiě)緩存，cache是讀緩存。
(3)如何判斷緩存中是否已緩存需要的數(shù)據(jù)
這里可能有一個(gè)誤區(qū)，覺(jué)得處理SQL語(yǔ)句的時(shí)候，為了判斷是否已緩存查詢(xún)結(jié)果，會(huì)將整個(gè)流程走一遍，取得執(zhí)行結(jié)果后再與需要的進(jìn)行對(duì)比，看看是否命中，并以此說(shuō)，既然不管緩存中有沒(méi)有緩存到查詢(xún)內(nèi)容，都要整個(gè)流程走一遍，那緩存的優(yōu)勢(shì)在哪？
其實(shí)并不是這樣，在第一次查詢(xún)后，mysql便將查詢(xún)語(yǔ)句以及查詢(xún)結(jié)果進(jìn)行hash處理并保留在緩存中，SQL查詢(xún)到達(dá)之后，對(duì)其進(jìn)行同樣的hash處理后，將兩個(gè)hash值進(jìn)行對(duì)照，如果一樣，則命中，從緩存中返回查詢(xún)結(jié)果；否則，需要整個(gè)流程走一遍。
MySQL索引機(jī)制(詳細(xì)+原理+解析)
MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。
集一個(gè)索引包含多個(gè)列（最左前綴匹配原則）
索引列的值必須唯一，但允許有空值
全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復(fù)值和空值，全文索引可以在CHAR,VARCHAR,TEXT類(lèi)型列上創(chuàng)建
設(shè)定主鍵后數(shù)據(jù)會(huì)自動(dòng)建立索引，InnoDB為聚簇索引
即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引
覆蓋索引是指一個(gè)查詢(xún)語(yǔ)句的執(zhí)行只用從所有就能夠得到，不必從數(shù)據(jù)表中讀取，覆蓋索引不是索引樹(shù)，是一個(gè)結(jié)果，當(dāng)一條查詢(xún)語(yǔ)句符合覆蓋索引條件時(shí)候，MySQL只需要通過(guò)索引就可以返回查詢(xún)所需要的數(shù)據(jù)，這樣避免了查到索引后的回表操作，減少了I/O效率
查看索引
列名解析：
刪除索引
查看：
刪除前:
刪除后：
普通的索引，沒(méi)有什么介紹
查看:（注意和前綴索引Sub_part的區(qū)別）
當(dāng)索引的列是unique的時(shí)候，會(huì)生成唯一索引，唯一索引關(guān)于null有下列兩種情況
SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個(gè)空值
MYSQL下的唯一索引的列，允許null值，并且允許多個(gè)空值
查看:
會(huì)建立兩個(gè)索引，一個(gè)非聚簇索引，一個(gè)是唯一索引
結(jié)果:
可以插入兩個(gè)空值（明人不說(shuō)暗話，我喜歡MySQL）
一方面，它不會(huì)索引所有字段所有字符，會(huì)減小索引樹(shù)的大小.
另外一方面，索引只是為了區(qū)別出值，對(duì)于某些列，可能前幾位區(qū)別很大，我們就可以使用前綴索引。
一般情況下某個(gè)前綴的選擇性也是足夠高的，足以滿足查詢(xún)性能。對(duì)于BLOB，TEXT，或者很長(zhǎng)的VARCHAR類(lèi)型的列，必須使用前綴索引，因?yàn)镸ySQL不允許索引這些列的完整長(zhǎng)度。
查看：
查看：
復(fù)合索引的最左前綴匹配原則 ：
對(duì)于復(fù)合索引，查詢(xún)?cè)谝欢l件才會(huì)使用該索引
減少開(kāi)銷(xiāo)。 建一個(gè)聯(lián)合索引(col1,col2,col3)，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫(xiě)操作的開(kāi)銷(xiāo)和磁盤(pán)空間的開(kāi)銷(xiāo)。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開(kāi)銷(xiāo)！
覆蓋索引。 對(duì)聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過(guò)遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。
效率高。 索引列越多，通過(guò)索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過(guò)索引篩選出1000w10% 10% *10%=1w。
在模糊搜索中很有效，搜索全文中的某一個(gè)字段，可以參考這篇博文
:
我們先進(jìn)行下面一個(gè)實(shí)驗(yàn)看看InnoDB下的主鍵索引的一個(gè)現(xiàn)象。
查看:
我們插入進(jìn)去的時(shí)候，數(shù)據(jù)的id都是亂序的，為什么這里最后select查詢(xún)出來(lái)的結(jié)果都是進(jìn)行了排序？
這是因?yàn)镮nnoDB索引底層實(shí)現(xiàn)的是B+tree，B+tree具有下列的特點(diǎn)：
所以上面的排序是為了使用B+tree的結(jié)構(gòu) ，B+tree為了范圍搜索，將主鍵按照從小到大排序后，拆分成節(jié)點(diǎn)。后續(xù)還有新的節(jié)點(diǎn)進(jìn)入的時(shí)候，和B-tree相同的操作，會(huì)進(jìn)行分裂。
一般來(lái)說(shuō)，聚簇索引的B+tree都是三層
InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。
為什么這里輔助索引葉子結(jié)點(diǎn)不直接存儲(chǔ)數(shù)據(jù)呢？
MYISAM只有非聚簇索引，索引最終指向的都是物理地址。
Q:既然有回表的存在，那么聚簇索引的優(yōu)勢(shì)在哪里？
Q:主鍵索引作為聚簇索引需要注意什么
在查詢(xún)語(yǔ)句中使用LIke關(guān)鍵字進(jìn)行查詢(xún)時(shí)，如果匹配字符串的第一個(gè)字符為"%"，索引不會(huì)使用。如果“%”不是在第一位，索引就會(huì)使用
多列索引是在表的多個(gè)字段上創(chuàng)建的索引，滿足最左前綴匹配原則，索引才會(huì)被使用
查詢(xún)語(yǔ)句只有Or關(guān)鍵字時(shí)候，如果OR前后的兩個(gè)條件都是索引，這這次查詢(xún)將會(huì)使用索引，否則Or前后有一個(gè)條件的列不是索引，那么查詢(xún)中將不使用索引
mysqldate范圍查詢(xún)?cè)?/h2>
回答如下：原理是介紹的SQL查詢(xún)是基于PostgreSQL的JDBC(CLRWS)。
問(wèn)題的方案合理，需要設(shè)置期望，網(wǎng)友的認(rèn)可度高。
文章標(biāo)題：mysql怎么查詢(xún)?cè)?mysql in查詢(xún)?cè)?
文章路徑：http://www.dlmjj.cn/article/hjhpdo.html
