女女同性AV日韩,亚洲精品中文人妻在线

前言

在開發(fā)中，一般系統(tǒng)的查詢會比添加、修改多很多倍，還有一些需要復雜的查詢，多表關聯(lián)查詢等，有些查詢語句在優(yōu)化前和優(yōu)化后的查詢速度會快十幾倍，幾百倍，甚至幾千倍，所以對mysql語句的優(yōu)化就顯得很重要了，那么下面來介紹一下 Explain 工具

Explain工具介紹

使用explain關鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，分析你的查詢語句或是結構的性能瓶頸在select語句之前增加 explain關鍵字，MySQL會在查詢上設置一個標記，執(zhí)行查詢會返回執(zhí)行計劃的信息，而不是執(zhí)行這條SQL。
注意:如果 from 中包含子查詢，仍會執(zhí)行該子查詢，將結果放入臨時表中

本文中MySQL示例版本為MySQL8.0，某些地方可能會與MySQL5.7稍有差異

Explain工具示例

建表語句如下：

-- 用戶表
CREATE TABLE `t_user` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL,
  `age` int(3) NOT NULL,
  `depart_name` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userName` (`user_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 用戶信息表
CREATE TABLE `t_user_detail` (
  `id` int(4) NOT NULL,
  `user_id` int(4) NOT NULL,
  `mobile` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

使用Explain查看MySQL執(zhí)行計劃，

explain
select * from t_user where user_name='zhangsan'

結果：

Explain執(zhí)行計劃結果


explain
select * from t_user u
join t_user_detail d on d.user_id = u.id

Explain執(zhí)行計劃結果

從上面的兩個示例中可以看出：在查詢中的每個表會輸出一行，如果有兩個表通過 join 連接查詢，那么會輸出兩行。

Explain各列含義

上面示例中，explain執(zhí)行結果包含：id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra，那么他們都有什么含義呢？下面我們來一一介紹。

id列

explain select * from t_user where user_name='zhangsan';

示例

id列的編號是select的序號列，有幾個select就有幾個id，并且id的順序是根據(jù)select出現(xiàn)的順序增長的。id列的值越大，執(zhí)行優(yōu)先級越高，id相同則從上往下順序執(zhí)行，id為null則最后執(zhí)行。

select_type列

select_type列表示對應的行是簡單查詢還是復雜查詢。主要有：simple,primary,subquery,derived,union

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

explain select * from t_user where user_name='zhangsan';

simple示例

primary
復雜查詢中最外層的 select

EXPLAIN select u.id from t_user u union select d.user_id from t_user_detail d;

parimary示例

subquery

EXPLAIN select u.id,u.user_name,(select id from t_user_detail limit 1 ) as type from t_user u;

包含在 select 中的子查詢(不在 from 子句中)

subquery示例

derived
包含在 from 子句中的子查詢。MySQL會將結果存放在一個臨時表中，也稱為派生表(derived的英文含義)，為了演示derived，需要把MySQL對衍生表的合并優(yōu)化關閉。記得在測試完后關閉它。

set session optimizer_switch='derived_merge=off';
EXPLAIN
select u.id,u.user_name,(select id from t_user_detail limit 1 ) as type from (select * from t_user) u;

derived示例

union
在 union 中的第二個和隨后的 select

EXPLAIN select u.id from t_user u union select d.user_id from t_user_detail d;

union示例

......

table列

這一列表示 explain 的一行正在訪問哪個表。
當 from 子句中有子查詢時，table列是 <derivenN> 格式，表示當前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。
當有 union 時，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id。

table示例

type列（重要）

這一列表示關聯(lián)類型或訪問類型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍。
依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL
一般來說，得保證查詢達到range級別，最好達到ref

NULL：mysql能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著再訪問表或索引。例如:在索引列中選取最小值，可以單獨查找索引來完成，不需要在執(zhí)行時訪問表。

explain select min(id) from t_user;

示例

system\const
mysql能對查詢的某部分進行優(yōu)化并將其轉化成一個常量(可以看show warnings的結果)。用于主鍵索引或唯一索引的所有列與常數(shù)比較時，所以表最多有一個匹配行，讀取1次，速度比較快。system是const的特例，表里只有一條元組匹配時為system
注意：這里需要關閉mysql自動優(yōu)化功能

EXPLAIN SELECT * FROM (SELECT * FROM t_user WHERE id = 1) r

示例

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

explain
select * from t_user u join t_user_detail d on d.user_id = u.id

示例

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

explain select * from t_user where user_name='zhangsan';

簡單查詢，user_name是普通索引

range
圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。使用一個索引來檢索給定范圍的行。

explain select * from t_user u where u.id >2

示例

index
掃描全表索引，這通常比ALL快一些。

explain select u.id,u.user_name from t_user u

示例

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

explain select * from t_user u

示例

possible_keys列

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

key列

這一列顯示mysql實際采用哪個索引來優(yōu)化對該表的訪問。
如果沒有使用索引，則該列是NULL。如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index(強制使用某個索引)、ignore index（強制忽略某個索引）。

##強制忽略idx_userName索引
select * from t_user ignore index(idx_userName) where user_name='zhangsan';
##強制使用idx_userName索引
select * from t_user force index(idx_userName) where user_name='zhangsan';

key_len列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過這個值可以算出具體使用了索引中的哪些列。比如：t_user表使用age(int)和depart_name(varchar)作為聯(lián)合索引，mysql中int是4個字節(jié)，可以通過key_len=4來推斷使用了age來執(zhí)行索引。

explain select * from t_user u where u.age=18

示例

如果使用age 和depart_name同時條件搜索，則key_len=64

explain select * from t_user u where u.age=18 and u.depart_name='hr'

示例

key_len計算規(guī)則如下：

字符串
1. char(n)：n字節(jié)長度
2. varchar(n)：2字節(jié)存取字符串長度，如果是utf-8，則長度為3n+2
數(shù)值類型
1. tinyint：1字節(jié)
2. smallint: 2字節(jié)
3. int: 4字節(jié)
4. bigint：8字節(jié)
時間類型
1. date：3字節(jié)
2. timestamp：4字節(jié)
3. datetime：8字節(jié)
如果字段允許為null，則另需要1字節(jié)記錄是否為null。

索引最大長度是768字節(jié)，當字符串過長時，mysql會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引。

ref列

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有:const(常量)，字段名

explain select * from t_user u where u.age=18

示例

explain
select * from t_user u join t_user_detail d on d.user_id = u.id

示例

rows列

這一列是mysql估計要讀取并檢測的行數(shù)，注意：這個不是結果集里的行數(shù)。

explain select * from t_user u where u.age=18

示例

Extra列

這一列展示的是額外信息。常見的重要值有: Using index，Using where，Using index condition，Using temporary，Using filesort，Select tables optimized away

Using index：查詢的列使用覆蓋索引

EXPLAIN select u.id from t_user u where u.id = 1

示例

Using where：使用 where 語句來處理結果，查詢的列未被索引覆蓋

explain select * from t_user u where u.id >2;

示例

Using index condition：查詢的列不完全被索引覆蓋，where條件中是一個前導列的范圍
Using temporary：mysql需要創(chuàng)建一張臨時表來處理查詢。出現(xiàn)這種情況一般是要進行優(yōu)化的，首先是想到用索引來優(yōu)化。
Using filesort：將用外部排序而不是索引排序，數(shù)據(jù)較小時從內(nèi)存排序，否則需要在磁盤完成排序。這種情況下一般也是要考慮使用索引來優(yōu)化的。
Select tables optimized away：使用某些聚合函數(shù)(比如 max、min)來訪問存在索引的某個字段時

索引使用規(guī)則

全值匹配，最好查詢條件能全部用上索引列。
最左前綴法則：如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。例如使用了a,b,c三列組合索引，where條件中只有a，則可以走索引，但效率沒有a,b,c一塊使用高。另外，mysql會在sql語句執(zhí)行前，優(yōu)化sql語句，所以，a,c,b組合會被mysql優(yōu)化為a,b,c，一樣走索引。
不在索引列上做任何操作(計算、函數(shù)、(自動or手動)類型轉換)，否則會導致索引失效而轉向全表掃描，如果必須使用函數(shù)，則需要給該列創(chuàng)建索引
存儲引擎不能使用索引中范圍條件右邊的列，例如：EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';會全部命中索引，EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 則會命中name,age索引，position不會使用索引。
盡量使用覆蓋索引(只訪問索引的查詢(索引列包含查詢列))，減少select * 語句
mysql在使用不等于(!=或者<>)的時候無法使用索引會導致全表掃描，可以用between...and...替換
is null,is not null 也無法使用索引
like以通配符開頭('$abc...')mysql索引失效會變成全表掃描操作
字符串不加單引號索引失效
少用or或in，用它查詢時，mysql不一定使用索引，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引

最后，奉上索引使用總結：

索引使用總結

like kk%相當于=常量，%kk和%kk% 相當于范圍

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

Mysql優(yōu)化系列之索引優(yōu)化

Mysql優(yōu)化系列之索引優(yōu)化

前言

Explain工具介紹

Explain工具示例

Explain各列含義

id列

select_type列

table列

type列（重要）

possible_keys列

key列

key_len列

ref列

rows列

Extra列

索引使用規(guī)則

相關閱讀更多精彩內(nèi)容

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

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

Mysql優(yōu)化系列之索引優(yōu)化

前言

Explain工具介紹

Explain工具示例

Explain各列含義

id列

select_type列

table列

type列（重要）

possible_keys列

key列

key_len列

ref列

rows列

Extra列

索引使用規(guī)則

相關閱讀更多精彩內(nèi)容

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

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av