62 mysql 定位慢查詢和索引優(yōu)化

1 什么是慢查詢？ 如何定位數(shù)據(jù)庫慢查詢？

2，如何使用執(zhí)行計劃EXPLAIN 分析sql語句？

3，EXPLAIN select_type , type,key_len, Extra 有哪些作用？

4， 索引優(yōu)化實戰(zhàn)聯(lián)合索需要遵循最佳左前綴法則。

5， like 查詢?nèi)绾问褂盟饕苊馊頀呙?/p>

聯(lián)合索引查詢需要注意的事項？
遵循最佳左前綴法則，

6， mysql索引優(yōu)化方案注意事項。
導(dǎo)入的mysql https://note.youdao.com/ynoteshare1/index.html?id=7e550bfe5c734cec8413985b8351d9f8&type=note
慢查詢：
SHOW VARIABLES LIKE '%query%' 查詢慢日志相關(guān)信息
slow_query_log 默認(rèn)是off關(guān)閉的，使用時，需要改為on 打開　　　　　　
slow_query_log_file 記錄的是慢日志的記錄文件
long_query_time 默認(rèn)是10S，每次執(zhí)行的sql達(dá)到這個時長，就會被記錄
SHOW STATUS LIKE '%slow_queries%' 查看慢查詢狀態(tài)

set global long_query_time = 1 修改慢查詢時間1s
set global slow_query_log ='ON' #開啟慢查詢

注意：修改慢查詢時間后，記得需要重新連接才可以生效

mysql sql 語句性能調(diào)優(yōu)。
優(yōu)化查詢語句 ： select
定位慢查詢。
慢查詢語句的定義： 在規(guī)定的時間內(nèi)沒有查詢到結(jié)果。
通過執(zhí)行計劃 EXPLAIN

如何定位sql慢查詢
SHOW VARIABLES LIKE '%query%'

執(zhí)行計劃EXPLAIN

通過慢查詢定位一些查詢比較慢的sql語句，在使用explain 工具排查該sql語句索引是否有生效。
EXPLAIN select * from mayikt_user where id=1;
EXPLAIN select * from mayikt_user where user_name='mayikt001';

id:選擇標(biāo)識符
select_type:表示查詢的類型。
table:輸出結(jié)果集的表
partitions:匹配的分區(qū)
type:表示表的連接類型
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引字段的長度
ref:列與索引的比較
rows:掃描出的行數(shù)(估算的行數(shù))
filtered:按表條件過濾的行百分比
Extra:執(zhí)行情況的描述和說明詢，又是上述三種復(fù)雜查詢中

explain 中的列

Id id列的編號是select 的序列號 id如果越大表示越優(yōu)先執(zhí)行。
id列的編號是 select 的序列號，有幾個selcet 就有幾個id，并且id的順序是按 select 出現(xiàn)的順序增長的。 MySql 將 select 查詢分為簡單查詢(SIMPLE) 和復(fù)雜查詢(PRIMARY)。
復(fù)雜查詢分為三類： 簡單子查詢，派生表(from 語句中的子查詢),union查詢。
id 列越大執(zhí)行優(yōu)先級越高，id 相同則從上往下執(zhí)行，id為null 最后執(zhí)行。

EXPLAIN SELECT (SELECT 1 from mayikt_student WHERE id=1) from(SELECT * from mayikt_class WHERE id=1) m;
1 簡單的子查詢
EXPLAIN SELECT (SELECT 1 from mayikt_student LIMIT 1) from mayikt_class


image.png

2 from 的子查詢

explain select id from (select id from mayikt_student) as mayikt_temp;

image.png

這個查詢執(zhí)行時有個臨時表別名 mayikt_temp.外部 select 查詢引用了這個臨時表。
3，union 查詢。
explain select 1 union all select 1

union結(jié)果總是放在一個匿名臨時表中，臨時表不在SQL中出現(xiàn)，因此它的id是NULL。
union查詢就是把2條或者多條sql的查詢結(jié)果合并成1個結(jié)果集。

2, select_type 列
表示查詢的類型。
1， SIMPLE（簡單select 不使用UNION查詢或者子查詢等）

  1. PRIMARY （子查詢中的最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select 被標(biāo)記為PRIMARY）
    3，UNION(UNION中的第二個或后面的SELECT語句)

4， DEPENDENT UNION(UNION 中的第二個或后面的SELECT語句，取決于外面的查詢)
5，UNION RESULT（UNION 的結(jié)果，union語句中第二個select開始后面所有的select）
6,SUBQUERY（子查詢中的第一個SELECT，結(jié)果不依賴于外部查詢）
7， DEPENDENT SUBQUERY(子查詢中的第一個select, 依賴于外部查詢)
8，DERIVED（派生表的 SELECT ,FROm 字句的子查詢）
9，UNCACHEABLE SUBQUERY(一個子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行)

simple：簡單查詢。查詢中不包含子查詢和union

 
 explain select * from mayikt_student where id=1
image.png

2,primary: 復(fù)雜查詢中最外層的select

  1. subquery: 包含在select 中 的子查詢(不在from 子句中)
    4， derived： 包含在 from 子句中的子查詢。MYSQL會將結(jié)果存放在一個臨時表中，也稱為派生表(derived 的英文含義)
 EXPLAIN SELECT(select 1 from  mayikt_student WHERE  id=1) FROM (SELECT * from mayikt_class where id=1) ma
image.png

union： 在 union 中的第二個和隨后的select
union result： 從union 臨時表檢索出來的結(jié)果的 select


explain  select 1 union all  select 1
image.png

Mysql UNION 操作符用于連接2個以上的select 語句的結(jié)果組合到一個結(jié)果中，多個 select 語句會刪除重復(fù)的語句。

Table
這一列表示explain 的一行正在訪問那個表
當(dāng)from 子句中有子查詢的時候，table 列是<derivenN>格式，表示當(dāng)前查詢依賴于

id=n的查詢，于是先執(zhí)行 id=n的查詢。
當(dāng)有 union 時，UNION RESULT 的table 列的值為<union1,2> ,1和2 表示參與 union的select 行id

 explain select * from mayikt_student where id=1
image.png

type 列
這一列表示 關(guān)聯(lián)類型或訪問類型，即 MYSql 決定如何查詢表中的行，查詢數(shù)據(jù)行記錄的大概范圍。
依次從最優(yōu)到最差： system>const>eq_ref>ref>rang>index>ALL
一般來說，得保證查詢達(dá)到rang 級別，，最好達(dá)到ref級別。
NuLL： mysql 能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著在訪問表或者索引，例如，在索引列中選取最小值。可以單獨查詢索引來完成。不需要在執(zhí)行時訪問表。
1, ALL： Full Table Scan， MySql 將遍歷全表以找到匹配的行
2，index: Full Index Scan index與All 的區(qū)別為index類型值遍歷索引樹
3，rang： 只檢索給定范圍的行，使用一個索引來選擇行
4， ref： 表示上述表的連接匹配條件，即那些列或常量被用于查找索引列上的值。
5， eq_ref 類似ref，區(qū)別在于使用的索引是唯一 的索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key 或者unique key 作為關(guān)聯(lián)條件。
6， const ,system: 當(dāng)mysql對查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換一個常量時候，使用這些類型進(jìn)行訪問，如將主鍵置于 where 列表中，mysql 就能將該查詢轉(zhuǎn)換為一個常量，system是const類型，當(dāng)查詢的表只有一行的情況下，使用system

7， null： mysql 在優(yōu)化的過程中分解語句，執(zhí)行時候甚至不用訪問表或者索引。例如一個索引選取最小值可以通過單獨索引查找完成。

1，const,system: mysql 能對查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)換成一個常量(可以看show warnings的結(jié)果)，用于primarykey 或unique key 的所有列與常數(shù)比較時，所以表最多有一個匹配行。讀取一次，速度比較快，system是const的特例，表里只有一條元組匹配時為system


explain  select * from (select * from mayikt_student where id=1) m
image.png

2, eq_ref: primary key 或unique key 索引的所有部分被連接使用，最多只會返回一條符合條件的記錄，這可能是在const 之外最好的連接類型了， 簡單的select 查詢不會出現(xiàn)這種type。



explain  select  * from  mayikt_student_class left  join mayikt_class  on  mayikt_student_class.id=mayikt_class.id
image.png

3, ref相比 eq_ref， 不使用唯一的索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值比較，可能會找到多個符合條件的行。

explain select * from mayikt_class where name = "第一期";
image.png

4,rang 范圍掃描通常出現(xiàn)在 in() ,between,>,<,>= 等操作中，使用一個索引來檢索給定范圍的行。
···
explain select * from mayikt_student where id>10
···

image.png

5, index 掃描全表索引，這通常比all 快一些，(index 是從索引中讀取的。而all 是從硬盤中讀取的)

image.png

6， All: 既全表掃描，意味著mysql 需要從頭到尾去查找所需要的行，通常情況下需要增加索引來優(yōu)化了。


EXPLAIN SELECT * from mayikt_student
image.png

7, possible_keys
這一列顯示查詢可能使用安歇索引來查找。
explain 時可能 出現(xiàn)possible_keys 列 ，而key顯示null 的情況，這種情況是因為表中數(shù)據(jù)不多，mysql 認(rèn)為索引對此查詢幫助不大，選擇了全表查詢。
如果該列是null ，則沒有相關(guān)的索引，在這種情況下可以通過堅持where字句看是否可以創(chuàng)造個適當(dāng)?shù)乃饕齺硖岣卟樵冃阅埽?然后explain 查看效果

explain select * from mayikt_student where id>10

explain select * from mayikt_student_class where class_id='1';
image.png

8, key_len 計算規(guī)則如下：
字符串
char(n) n 字節(jié)長度
varchar(n) 2字節(jié)存儲字符串長度，如果是utf-8,，則長度是3n+2
數(shù)值類型
tinyint : 1字節(jié)
smallint : 2字節(jié)
bigin: 8個字節(jié)：

時間類型
date: 3個字節(jié)
timestamp: 4個字節(jié)
datetime: 8個字節(jié)
如果字段允許null ，需要1字節(jié)記錄是否為null
索引最大長度是768字節(jié)，當(dāng)字符串過長時,mysql會做一個類似左前綴索引的處理，將前半部分的字符串提取出來做索引。

explain select * from mayikt_student_class where class_id='1' and student_id='1';
image.png

9,Extra列：
Extra 表示附加信息，常見的有幾種（也按照查詢效率從高到低排列）；
1， Using index: 表示使用索引， 如果只有Using index，說明他沒有查詢到數(shù)據(jù)表，只用索引表就完成了這個查詢，這個叫做覆蓋索引。如果同時出現(xiàn)Using where 代表使用索引來查找讀取記錄，也可以

2.Using where：表示條件查詢，如果不讀取表的所有數(shù)據(jù)，或不是僅僅通過索引就可以獲取所有需要的數(shù)據(jù)，則會出現(xiàn) Using where。如果type列是ALL或index，而沒有出現(xiàn)該信息，則你有可能在執(zhí)行錯誤的查詢：返回所有數(shù)據(jù)。
3.Using filesort：不是“使用文件索引”的含義！filesort是MySQL所實現(xiàn)的一種排序策略，通常在使用到排序語句ORDER BY的時候，會出現(xiàn)該信息。
4.Using temporary：表示為了得到結(jié)果，使用了臨時表，這通常是出現(xiàn)在多表聯(lián)合查詢，結(jié)果排序的場合。

Using index: 查詢的列被索引覆蓋，并且where篩選條件時索引的前導(dǎo)列，是性能高的表現(xiàn)。一般使用了覆蓋索引，對于innodb來說 ，如果是輔助索引性能會題海不少。
explain select class_id,id from mayikt_student_class where class_id='1';

[圖片上傳失敗...(image-6ecde5-1603242755319)]

Using where：查詢的列未被索引覆蓋，where篩選條件非索引的前導(dǎo)列

Using where Using index：查詢的列被索引覆蓋，并且where篩選條件是索引列之一但是不是索引的前導(dǎo)列，意味著無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)

explain select class_id from mayikt_student_class where student_id='1';

explain select * from mayikt_student_class where student_id='1';

Using filesort mysql 會對結(jié)果使用一個外部索引排序，而不是按索引次序從表里讀取行。此時mysql會根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優(yōu)化的。

explain select * from mayikt_student ORDER BY name ;

注意：誤區(qū)

如果問Using filesort是什么意思，大多數(shù)人應(yīng)該會回答“基于硬盤的排序”或者“數(shù)據(jù)太多不適合內(nèi)存，所以在硬盤上排序”。然而這些解釋是錯誤的。

Using filesort僅僅表示沒有使用索引的排序，事實上filesort這個名字很糟糕，并不意味著在硬盤上排序，filesort與文件無關(guān)。因此消除Using filesort的方法就是讓查詢sql的排序走索引。

explain select * from mayikt_class order by name;

索引優(yōu)化：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('4', 'meite', '22', 'ceo', '2020-10-13 19:33:27');
INSERT INTO `employees` VALUES ('5', 'xiaowei', '23', 'kefu', '2020-10-13 19:33:27');
INSERT INTO `employees` VALUES ('6', 'xiaomin', '23', 'kefu', '2020-10-13 19:33:27');

1.全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'meite'; 74
EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND age = 22; 78
EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND age = 22 AND position ='ceo'; 140

2, 遵循最佳左前綴法則：


EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='ceo';
EXPLAIN SELECT * FROM employees WHERE position = 'ceo';
EXPLAIN SELECT * FROM employees WHERE name = 'meite';
EXPLAIN SELECT * FROM employees WHERE name= 'meite'  AND position ='ceo';

3,不要在索引上做任何操作（計算，函數(shù)，（自動or手動）類型轉(zhuǎn)換，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描）

4， 盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少select*語句。

5，mysql在使用不等于(!= 或者<>)的時候無法使用索引導(dǎo)致全表掃描

6， isnull is not null 也無法使用索引。

8.like以通配符開頭（'$abc...'）mysql索引失效會變成全表掃描操作
EXPLAIN SELECT * FROM employees WHERE name like '%mei';
EXPLAIN SELECT * FROM employees WHERE name like 'mei%';

問題：解決like'%字符串%'索引不被使用的方法？
a）使用覆蓋索引，查詢字段必須是建立覆蓋索引字段
EXPLAIN SELECT name FROM employees WHERE name like '%mei';

9.字符串不加單引號索引失效
EXPLAIN SELECT * FROM employees WHERE name = 644064779;
EXPLAIN SELECT * FROM employees WHERE name = '644064779';

10.少用or,用它連接時很多情況下索引會失效
EXPLAIN SELECT * FROM employees WHERE name = 'meite' or name = 'wangmazi';

最后編輯于 ：
?著作權(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ù)。

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