這篇“MySQL索引優(yōu)化器工作原理是什么”文章的知識(shí)點(diǎn)大部分人都不太理解,所以小編給大家總結(jié)了以下內(nèi)容,內(nèi)容詳細(xì),步驟清晰,具有一定的借鑒價(jià)值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來(lái)看看這篇“MySQL索引優(yōu)化器工作原理是什么”文章吧。
創(chuàng)新互聯(lián)專(zhuān)業(yè)為企業(yè)提供梧州網(wǎng)站建設(shè)、梧州做網(wǎng)站、梧州網(wǎng)站設(shè)計(jì)、梧州網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、梧州企業(yè)網(wǎng)站模板建站服務(wù),10年梧州做網(wǎng)站經(jīng)驗(yàn),不只是建網(wǎng)站,更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
下面我們來(lái)看這張表,SUB_ODR_ID字段創(chuàng)建了相關(guān)的 2 個(gè)索引,根據(jù)我們前面所學(xué)我們建立一個(gè)PRIMARY KEY (ID
)自增主鍵索引,(LOG_ID
, SUB_ODR_ID
)設(shè)置為聯(lián)合索引、唯一索引,兩個(gè)時(shí)間CREATE_TIME、UPDATE_TIME分別設(shè)置兩個(gè)索引。
CREATE TABLE `***` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
`LOG_ID` varchar(32) NOT NULL COMMENT '交易流水號(hào)',
`ODR_ID` varchar(32) NOT NULL COMMENT '父單號(hào)',
`SUB_ODR_ID` varchar(32) NOT NULL COMMENT '子單號(hào)',
`CREATE_TIME` datetime(0) NOT NULL COMMENT '創(chuàng)建時(shí)間',
`CREATE_BY` varchar(32) NOT NULL COMMENT ' 創(chuàng)建人',
`UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新時(shí)間',
`UPDATE_BY` varchar(32) NOT NULL COMMENT '更新人',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE INDEX `UNQ_LOG_SUBODR_ID`(`LOG_ID`, `SUB_ODR_ID`) USING BTREE,
INDEX `IDX_ODR_ID`(`ODR_ID`) USING BTREE,
INDEX `IDX_SUB_ID`(`SUB_ODR_ID`) USING BTREE,
INDEX `IDX_CREATE_TIME`(`CREATE_TIME`) USING BTREE,
INDEX `IDX_UPDATE_TIME`(`UPDATE_TIME`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 SET = utf8 COLLATE = utf8_general_ci COMMENT = '分?jǐn)倶I(yè)務(wù)明細(xì)表' ROW_FORMAT = Dynamic;
在查詢(xún)字段 SUB_ODR_ID 中,理論上可以使用三個(gè)相關(guān)的索引:UNQ_LOG_SUBODR_ID、IDX_SUB_ID,MySQL優(yōu)化器如何從這三個(gè)索引中進(jìn)行選擇?
在關(guān)系數(shù)據(jù)庫(kù)中,B+樹(shù)只是用于存儲(chǔ)的數(shù)據(jù)結(jié)構(gòu)。
如何使用它取決于數(shù)據(jù)庫(kù)的優(yōu)化器。優(yōu)化器確定特定索引的選擇,即執(zhí)行計(jì)劃。優(yōu)化器的選擇基于成本,成本越低,首選指數(shù)越高。
MySQL數(shù)據(jù)庫(kù)由Server(服務(wù)器)層和Engine(引擎)層組成。
Serve層有SQL分析器、SQL優(yōu)化器和SQL執(zhí)行器,負(fù)責(zé)SQL語(yǔ)句的具體執(zhí)行過(guò)程。
Engine層負(fù)責(zé)存儲(chǔ)特定數(shù)據(jù),例如最常用的InnoDB存儲(chǔ)引擎,以及用于在內(nèi)存中存儲(chǔ)臨時(shí)結(jié)果集的TempTable引擎。
SQL優(yōu)化器將分析所有可能的執(zhí)行計(jì)劃,并選擇成本最低的執(zhí)行。這個(gè)優(yōu)化器被稱(chēng)為CBO(基于成本的優(yōu)化器)。
在 MySQL中,一條 SQL 的計(jì)算成本計(jì)算,很好理解,就是訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)(數(shù)據(jù)庫(kù)頁(yè)、磁盤(pán))+處理數(shù)據(jù)。
CPU成本,表示計(jì)算成本,例如索引鍵值的比較、記錄值的比較和結(jié)果集的排序。這些操作都在服務(wù)器層完成
IO成本,表示引擎級(jí)IO的成本,MySQL 8.0可以通過(guò)區(qū)分表的數(shù)據(jù)是否在內(nèi)存中來(lái)分別計(jì)算讀取內(nèi)存IO和磁盤(pán)IO的成本。
Cost = Server Cost + Engine Cost = CPU Cost + IO Cost
MySQL優(yōu)化器認(rèn)為,如果一段SQL需要?jiǎng)?chuàng)建一個(gè)基于磁盤(pán)的臨時(shí)表,那么此時(shí)的成本是最大的,是基于內(nèi)存的臨時(shí)表的20倍。比較索引鍵值和記錄的成本很低,但如果要比較的記錄很多,成本就會(huì)非常大。
MySQL 優(yōu)化器認(rèn)為,從磁盤(pán)讀取的開(kāi)銷(xiāo)是內(nèi)存開(kāi)銷(xiāo)的 4 倍(成本不是一成不變的會(huì)根據(jù)硬件變化)。
查看各成本的值,MySQL優(yōu)化器的工作原理,我們執(zhí)行下面這行SQL語(yǔ)句,分析執(zhí)行過(guò)程,MySQL 索引選擇是基于 SQL 執(zhí)行成本
EXPLAIN FORMAT=json
select * from test.fork_business_detail f where f.sub_odr_id = ''
read_cost表示從InnoDB存儲(chǔ)引擎讀取的成本;
eval_cost表示服務(wù)器層的CPU成本;
prefix_cost表示SQL的總成本;
data_read_per_join 表示讀取記錄中的字節(jié)總數(shù)。
{
"query_block": {
"cost_info": {
"query_cost": "1.20"
},
"table": {
"access_type": "ref",
"possible_keys": [
"IDX_SUB_ID"
],
"key": "IDX_SUB_ID",
"used_key_parts": [
"SUB_ODR_ID"
],
"key_length": "98",
"ref": [
"const"
],
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "1K"
},
"used_columns": [
"ID",
"LOG_ID",
"ODR_ID",
"SUB_ODR_ID",
"CREATE_TIME",
"CREATE_BY",
"UPDATE_TIME",
"UPDATE_BY"
]
}
}
}
如何提高M(jìn)ySQL的查詢(xún)性能?首先,您需要了解查詢(xún)優(yōu)化器進(jìn)行SQL處理的整個(gè)過(guò)程。SELECT SQL 的執(zhí)行過(guò)程為例,如下圖所示:
客戶(hù)端向服務(wù)器發(fā)送SELECT查詢(xún);服務(wù)器首先檢查查詢(xún)緩存。如果緩存被命中,存儲(chǔ)在緩存中的結(jié)果將立即返回。否則,進(jìn)入下一階段;
服務(wù)器執(zhí)行SQL解析、預(yù)處理,查詢(xún)優(yōu)化器生成相應(yīng)的執(zhí)行計(jì)劃;MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎的API執(zhí)行查詢(xún);結(jié)果將返回到客戶(hù)端,并同時(shí)放入查詢(xún)緩存。
以上就是關(guān)于“MySQL索引優(yōu)化器工作原理是什么”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對(duì)大家有幫助,若想了解更多相關(guān)的知識(shí)內(nèi)容,請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。