三年在线观看完整版免费高清 ,无码国产69精品久久久孕妇,涩情网址

簡介

mysql的innodb引擎查詢記錄時在無法使用索引覆蓋的場景下，需要做回表操作獲取記錄的所需字段。

mysql執(zhí)行sql前會執(zhí)行sql優(yōu)化、索引選擇等操作，mysql會預估各個索引所需要的查詢代價以及不走索引所需要的查詢代價，從中選擇一個mysql認為代價最小的方式進行sql查詢操作。而在回表數據量比較大時，經常會出現mysql對回表操作查詢代價預估代價過大而導致索引使用錯誤的情況。

案例

示例如下，在5.6版本的mysql、1CPU2G內存的Linux環(huán)境下，新建一個測試表，并創(chuàng)建將近200萬的記錄用于測試。

CREATE TABLE `salary_static` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `school_id` int(11) NOT NULL COMMENT '學校id',
  `student_id` int(11) NOT NULL COMMENT '畢業(yè)生id',
  `salary` int(11) NOT NULL DEFAULT '0' COMMENT '畢業(yè)薪水',
  `year` int(11) NOT NULL COMMENT '畢業(yè)年份',
  PRIMARY KEY (`id`),
  KEY `school_id_key` (`school_id`) USING BTREE,
  KEY `year_school_key` (`year`,`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='畢業(yè)生薪水數據統計';

delimiter  //
CREATE PROCEDURE init_salary_static() 
BEGIN 
	DECLARE year INT;
	DECLARE schid INT;
	DECLARE stuid INT;
	SET year = 2000;
	WHILE year < 2020 DO
		START TRANSACTION; 
		SET schid = 1;
		WHILE schid < 100 DO
			SET stuid = 1;
			WHILE stuid < 1000 DO
				insert into salary_static(school_id,student_id,salary,year) values (schid,stuid,floor(rand()*10000),year);
				SET stuid = stuid + 1;
			END WHILE;
			SET schid = schid + 1;
		END WHILE;
		SET year = year + 1;
		COMMIT; 
	END WHILE;
END //
delimiter ;
call init_salary_static();

測試數據創(chuàng)建完成后，執(zhí)行以下sql語句進行統計查詢。

select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;

預計該sql應該使用year_school_key索引進行查詢，但實際上通過explain命令可以發(fā)現，該sql使用的是school_id_key索引，并且由于使用了錯誤的索引，該sql進行了全表掃描導致查詢時間花費了7秒。

強制使用year_school_key索引進行查詢后發(fā)現，該sql的查詢時間花費銳減到了0.6秒，比起school_id_key索引的時間減少了10倍。

select school_id,avg(salary) from salary_static force index(year_school_key) where year between 2015 and 2019 group by school_id;

分析

使用mysql的optimizer tracing（mysql5.6版本開始支持）功能來分析sql的執(zhí)行計劃：

SET optimizer_trace="enabled=on";
select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

輸出的結果為一個json，展示了該sql在mysql內部的sql優(yōu)化過程、索引選擇過程的執(zhí)行計劃。

重點關注執(zhí)行計劃的json中range_analysis下的內容，這里展示了where范圍查詢過程中索引選擇。table_scan表示全表掃描，預估需要掃描1973546條記錄，但是由于全表掃描走聚集索引是順序IO讀，因此每條記錄的查詢成本很小，最終計算出來的查詢成本為399741。range_scan_alternatives表示使用索引的范圍查詢，year_school_key索引預估需要掃描812174條記錄，但是由于需要回表操作導致隨機IO讀，最終計算出來的查詢成本為974610。所以對于where查詢過程最終選擇全表掃描不走索引。

"range_analysis": {
  "table_scan": {
	"rows": 1973546,
	"cost": 399741
  },
  "potential_range_indices": [
	{
	  "index": "PRIMARY",
	  "usable": false,
	  "cause": "not_applicable"
	},
	{
	  "index": "school_id_key",
	  "usable": true,
	  "key_parts": [
		"school_id",
		"id"
	  ]
	},
	{
	  "index": "year_school_key",
	  "usable": true,
	  "key_parts": [
		"year",
		"school_id",
		"id"
	  ]
	}
  ],
  "setup_range_conditions": [
  ],
  "group_index_range": {
	"chosen": false,
	"cause": "not_applicable_aggregate_function"
  },
  "analyzing_range_alternatives": {
	"range_scan_alternatives": [
	  {
		"index": "year_school_key",
		"ranges": [
		  "2016 <= year <= 2019"
		],
		"index_dives_for_eq_ranges": true,
		"rowid_ordered": false,
		"using_mrr": false,
		"index_only": false,
		"rows": 812174,
		"cost": 974610,
		"chosen": false,
		"cause": "cost"
	  }
	],
	"analyzing_roworder_intersect": {
	  "usable": false,
	  "cause": "too_few_roworder_scans"
	}
  }
}

這里的查詢成本cost值完全可以手算出來，cost=I/O成本（每一次讀取記錄頁一次成本，每次成本為1.0）+CPU成本（每一條記錄一次成本，每次成本為0.2）。

全表掃描查詢成本

table_scan全表掃描時預估需要掃描1973546條記錄，通過show table status like "salary_static"命令可得全表記錄為82411520字節(jié)（Data_length），innodb每個記錄頁為16KB即全表掃描需要讀取82411520/1024/16 = 5030個記錄頁。

I/O成本

5030 * 1.0 = 5030

CPU成本

1973546 * 0.2 = 394709.2

合計查詢成本

5030 + 394709.2 = 399739.2

索引查詢成本

year_school_key索引時預估需要掃描812174條記錄，且使用該索引需要先通過索引查詢到rowId，然后通過rowId回表。mysql認為每次回表均需要一次單獨的I/O成本

CPU成本

812174 * 0.2 = 162434.8

I/O成本

812174 * 1.0 = 812174

合計查詢成本

162434.8 + 812174 = 974608.8

接著再關注reconsidering_access_paths_for_index_ordering，表示最終對排序再進行一次索引選擇優(yōu)化。這里選擇了school_id_key索引并且一票否決了上面where條件選擇的全表掃描："plan_changed": true，詳見group-by-optimization。

{
    "reconsidering_access_paths_for_index_ordering": {
      "clause": "GROUP BY",
      "index_order_summary": {
        "table": "`salary_static`",
        "index_provides_order": true,
        "order_direction": "asc",
        "index": "school_id_key",
        "plan_changed": true,
        "access_type": "index_scan"
      }
    }
}

事實上排序索引優(yōu)化也存在bug，詳見Bug＃93845。

優(yōu)化

通過分析sql執(zhí)行過程，可以發(fā)現選擇索引錯誤的是因為year_school_key索引回表記錄太多導致預估查詢成本大于全表掃描最終選擇了錯誤的索引。

因此減少該sql的執(zhí)行時間，下一步的優(yōu)化方案是減少該sql的回表操作，即讓該sql進行索引覆蓋。該sql涉及到的字段只有school_id、salary和year這3個字段，因此創(chuàng)建這3個索引的聯合索引，并注意這3個字段在聯合索引中的順序：where過濾語句最先執(zhí)行，所以year字段在聯合索引第一位；group by語句本質上和order by一樣，因此排在where后面即聯合索引第二位；salary僅僅為了減少回表因此放在聯合索引末位。

CREATE INDEX year_school_salary_key ON salary_static (year, school_id, salary);

在創(chuàng)建了聯合索引后，再執(zhí)行sql語句后效果如下，僅花費了0.2秒完成查詢，比起school_id_key索引的時間減少了35倍。

回表率計算

上述問題為sql一次性查詢數量太多，導致回表代價太大。事實上，上述現象的臨界值完全可以計算出來：

假設一行記錄的大小為a字節(jié)，表的記錄數量為b，臨界記錄數量為c，則該表的記錄頁數量為b*a/1024/16

全表掃描的查詢成本 = I/O成本 + CPU成本
= b*a/1024/16 * 1.0 + b * 0.2

索引掃描的查詢成本 = I/O成本 + CPU成本
= c * 1.0 + c * 0.2 = c * 1.2

b*a/1024/16 * 1.0 + b * 0.2 = c * 1.2
臨界比例 = c/b 
= (a/1024/16 + 0.2)/1.2
= a * 5E-5 + 0.1667

即當一條sql查詢超過表中超過大概17%的記錄且不能使用覆蓋索引時，會出現索引的回表代價太大而選擇全表掃描的現象。且這個比例隨著單行記錄的字節(jié)大小的增加而略微增大。

到此這篇關于mysql回表致索引失效案例講解的文章就介紹到這了,更多相關mysql回表致索引失效內容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站！

美國穩(wěn)定服務器

mysql學習筆記之表的基本操作

centos編譯安裝mysql 5.6及安裝多個mysql實例詳解

mysql 5.7.11 winx64.zip安裝配置方法圖文教程

mysql 5.7.17 winx64.zip安裝配置方法圖文教程

CentOS安裝mysql5.7 及簡單配置教程詳解

MySQL 5.7 zip版本(zip版)安裝配置步驟詳解

MySQL5.6.31 winx64.zip 安裝配置教程詳解

MySQL注入繞開過濾的技巧總結

一次Mysql死鎖排查過程的全紀錄