MySQL索引優(yōu)化器工作原理是什么

這篇“MySQL索引優(yōu)化器工作原理是什么”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內(nèi)容，內(nèi)容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“MySQL索引優(yōu)化器工作原理是什么”文章吧。

創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供梧州網(wǎng)站建設、梧州做網(wǎng)站、梧州網(wǎng)站設計、梧州網(wǎng)站制作等企業(yè)網(wǎng)站建設、網(wǎng)頁設計與制作、梧州企業(yè)網(wǎng)站模板建站服務，10年梧州做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡服務。

一、MySQL 優(yōu)化器是如何選擇索引的

下面我們來看這張表，SUB_ODR_ID字段創(chuàng)建了相關的 2 個索引，根據(jù)我們前面所學我們建立一個PRIMARY KEY (ID)自增主鍵索引，(LOG_ID, SUB_ODR_ID)設置為聯(lián)合索引、唯一索引，兩個時間CREATE_TIME、UPDATE_TIME分別設置兩個索引。

CREATE TABLE `***`  (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `LOG_ID` varchar(32) NOT NULL COMMENT '交易流水號',
  `ODR_ID` varchar(32) NOT NULL COMMENT '父單號',
  `SUB_ODR_ID` varchar(32) NOT NULL COMMENT '子單號',
  `CREATE_TIME` datetime(0) NOT NULL COMMENT '創(chuàng)建時間',
  `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 '更新時間',
  `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 = '分攤業(yè)務明細表' ROW_FORMAT = Dynamic;

在查詢字段 SUB_ODR_ID 中，理論上可以使用三個相關的索引：UNQ_LOG_SUBODR_ID、IDX_SUB_ID，MySQL優(yōu)化器如何從這三個索引中進行選擇？

在關系數(shù)據(jù)庫中，B+樹只是用于存儲的數(shù)據(jù)結構。

如何使用它取決于數(shù)據(jù)庫的優(yōu)化器。優(yōu)化器確定特定索引的選擇，即執(zhí)行計劃。優(yōu)化器的選擇基于成本，成本越低，首選指數(shù)越高。

1、MySQL數(shù)據(jù)庫組成

MySQL數(shù)據(jù)庫由Server（服務器）層和Engine（引擎）層組成。

Serve層有SQL分析器、SQL優(yōu)化器和SQL執(zhí)行器，負責SQL語句的具體執(zhí)行過程。

Engine層負責存儲特定數(shù)據(jù)，例如最常用的InnoDB存儲引擎，以及用于在內(nèi)存中存儲臨時結果集的TempTable引擎。

SQL優(yōu)化器將分析所有可能的執(zhí)行計劃，并選擇成本最低的執(zhí)行。這個優(yōu)化器被稱為CBO（基于成本的優(yōu)化器）。

2、MySQL數(shù)據(jù)庫成本計算

在 MySQL中，一條 SQL 的計算成本計算，很好理解，就是訪問數(shù)據(jù)庫（數(shù)據(jù)庫頁、磁盤）+處理數(shù)據(jù)。

CPU成本，表示計算成本，例如索引鍵值的比較、記錄值的比較和結果集的排序。這些操作都在服務器層完成

IO成本，表示引擎級IO的成本，MySQL 8.0可以通過區(qū)分表的數(shù)據(jù)是否在內(nèi)存中來分別計算讀取內(nèi)存IO和磁盤IO的成本。

Cost  = Server Cost + Engine Cost  = CPU Cost + IO Cost

MySQL優(yōu)化器認為，如果一段SQL需要創(chuàng)建一個基于磁盤的臨時表，那么此時的成本是最大的，是基于內(nèi)存的臨時表的20倍。比較索引鍵值和記錄的成本很低，但如果要比較的記錄很多，成本就會非常大。

MySQL 優(yōu)化器認為，從磁盤讀取的開銷是內(nèi)存開銷的 4 倍（成本不是一成不變的會根據(jù)硬件變化）。

二、MySQL查詢成本

查看各成本的值，MySQL優(yōu)化器的工作原理，我們執(zhí)行下面這行SQL語句，分析執(zhí)行過程，MySQL 索引選擇是基于 SQL 執(zhí)行成本

EXPLAIN FORMAT=json 
select * from test.fork_business_detail f where f.sub_odr_id = ''

read_cost表示從InnoDB存儲引擎讀取的成本；

eval_cost表示服務器層的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"
			]
		}
	}
}

三、SELECT 執(zhí)行過程

如何提高MySQL的查詢性能？首先，您需要了解查詢優(yōu)化器進行SQL處理的整個過程。SELECT SQL 的執(zhí)行過程為例，如下圖所示：

客戶端向服務器發(fā)送SELECT查詢；服務器首先檢查查詢緩存。如果緩存被命中，存儲在緩存中的結果將立即返回。否則，進入下一階段；

服務器執(zhí)行SQL解析、預處理，查詢優(yōu)化器生成相應的執(zhí)行計劃；MySQL根據(jù)優(yōu)化器生成的執(zhí)行計劃調(diào)用存儲引擎的API執(zhí)行查詢；結果將返回到客戶端，并同時放入查詢緩存。

以上就是關于“MySQL索引優(yōu)化器工作原理是什么”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對大家有幫助，若想了解更多相關的知識內(nèi)容，請關注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

網(wǎng)頁題目：MySQL索引優(yōu)化器工作原理是什么
當前鏈接：http://muchs.cn/article22/jpjccc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供服務器托管、企業(yè)網(wǎng)站制作、營銷型網(wǎng)站建設、移動網(wǎng)站建設、品牌網(wǎng)站設計、App設計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)