亚洲在钱免费观看,四虎无码人妻三区,久久国产精品波多野结衣AV,成A人无码AV无码免费,国产精品自产拍在线观看,91蜜桃婷婷狠狠久久综合,日韩禁片一区二区,人妻无码中文字幕蜜桃

MySQL執(zhí)行計(jì)劃

前言

在實(shí)際數(shù)據(jù)庫(kù)項(xiàng)目開(kāi)發(fā)中，由于我們不知道實(shí)際查詢時(shí)數(shù)據(jù)庫(kù)里發(fā)生了什么，也不知道數(shù)據(jù)庫(kù)是如何掃描表、如何使用索引的，因此，我們能感知到的就只有SQL語(yǔ)句的執(zhí)行時(shí)間。尤其在數(shù)據(jù)規(guī)模比較大的場(chǎng)景下，如何寫(xiě)查詢、優(yōu)化查詢、如何使用索引就顯得很重要了。

那么，問(wèn)題來(lái)了，在查詢前有沒(méi)有可能估計(jì)下查詢要掃描多少行、使用哪些索引呢？

答案是肯定的。以MySQL為例，MySQL通過(guò)explain命令輸出執(zhí)行計(jì)劃，對(duì)要執(zhí)行的查詢進(jìn)行分析。

什么是執(zhí)行計(jì)劃

簡(jiǎn)單來(lái)說(shuō)，就是SQL在數(shù)據(jù)庫(kù)中執(zhí)行時(shí)的表現(xiàn)情況，通常用于SQL性能分析、優(yōu)化等場(chǎng)景。

從MySQL的邏輯結(jié)構(gòu)講解，過(guò)渡到MySQL的查詢過(guò)程，然后給出執(zhí)行計(jì)劃的例子并重點(diǎn)介紹執(zhí)行計(jì)劃的輸出參數(shù)，從而理解為什么我們會(huì)選擇文中建議的方案。

MySQL邏輯架構(gòu)

客戶端

如，連接處理、授權(quán)認(rèn)證、安全等功能

核心服務(wù)

MySQL大多數(shù)核心服務(wù)均在這一層
包括查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)（如，時(shí)間、數(shù)學(xué)、加密等）
所有的跨存儲(chǔ)引擎的功能也在這一層，如，存儲(chǔ)過(guò)程、觸發(fā)器、視圖等

存儲(chǔ)引擎

負(fù)責(zé)MySQL中的數(shù)據(jù)存儲(chǔ)和讀取
中間的服務(wù)層通過(guò)API與存儲(chǔ)引擎通信，這些API屏蔽了不同存儲(chǔ)引擎間的差異

查詢緩存

對(duì)于select語(yǔ)句，在解析查詢之前，服務(wù)器會(huì)先檢查查詢緩存（Query Cache）。如果命中，服務(wù)器便不再執(zhí)行查詢解析、優(yōu)化和執(zhí)行的過(guò)程，而是直接返回緩存中的結(jié)果集。

MySQL查詢過(guò)程

如果能搞清楚MySQL是如何優(yōu)化和執(zhí)行查詢的，對(duì)優(yōu)化查詢一定會(huì)有幫助。很多查詢優(yōu)化實(shí)際上就是遵循一些原則讓優(yōu)化器能夠按期望的合理的方式運(yùn)行。

下圖是MySQL執(zhí)行一個(gè)查詢的過(guò)程。實(shí)際上每一步都比想象中的復(fù)雜，尤其優(yōu)化器，更復(fù)雜也更難理解。本文只給予簡(jiǎn)單的介紹。

MySQL查詢過(guò)程

客戶端將查詢發(fā)送到MySQL服務(wù)器
服務(wù)器先檢查查詢緩存，如果命中，立即返回緩存中的結(jié)果；否則進(jìn)入下一階段
服務(wù)器對(duì)SQL進(jìn)行解析、預(yù)處理，再由優(yōu)化器生成對(duì)象的執(zhí)行計(jì)劃
MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎API來(lái)執(zhí)行查詢
服務(wù)器將結(jié)果返回給客戶端，同時(shí)緩存查詢結(jié)果

執(zhí)行計(jì)劃

執(zhí)行計(jì)劃的作用

表的讀取順序
數(shù)據(jù)讀取操作的操作類型
哪些索引可以使用
哪些索引被實(shí)際使用
表之間的引用
每張表有多少行被優(yōu)化器查詢

以上的這些作用會(huì)在執(zhí)行計(jì)劃詳解里面介紹到，在這里不做解釋。

優(yōu)化與執(zhí)行

MySQL會(huì)解析查詢，并創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹(shù)），并對(duì)其進(jìn)行各種優(yōu)化，包括重寫(xiě)查詢、決定表的讀取順序、選擇合適的索引等。

用戶可通過(guò)關(guān)鍵字提示（hint）優(yōu)化器，從而影響優(yōu)化器的決策過(guò)程。也可以通過(guò)通過(guò)優(yōu)化器解釋（explain）優(yōu)化過(guò)程的各個(gè)因素，使用戶知道數(shù)據(jù)庫(kù)是如何進(jìn)行優(yōu)化決策的，并提供一個(gè)參考基準(zhǔn)，便于用戶重構(gòu)查詢和數(shù)據(jù)庫(kù)表的schema、修改數(shù)據(jù)庫(kù)配置等，使查詢盡可能高效。

語(yǔ)法

執(zhí)行計(jì)劃的語(yǔ)法其實(shí)非常簡(jiǎn)單：在SQL查詢的前面加上EXPLAIN關(guān)鍵字就行。

比如：EXPLAIN select * from table1，重點(diǎn)的就是EXPLAIN后面你要分析的SQL語(yǔ)句。

準(zhǔn)備工作

導(dǎo)入數(shù)據(jù)表

COPY

              
              
DROP TABLE IF EXISTS `course`;
              

              
CREATE TABLE `course` (
              
  `cid` int(11) NOT NULL AUTO_INCREMENT,
              
  `cname` varchar(100) NOT NULL,
              
  `xuefen` int(11) DEFAULT NULL,
              
  `tid` int(11) DEFAULT NULL,
              
  PRIMARY KEY (`cid`)
              
) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8mb4;
              

              
/*Data for the table `course` */
              

              
insert  into `course`(`cid`,`cname`,`xuefen`,`tid`) values (1001,'C++',3,101),(1002,'java',5,101),(1003,'相聲表演',2,102),(1004,'電子商務(wù)',3,103);
              

              
/*Table structure for table `students` */
              

              
DROP TABLE IF EXISTS `students`;
              

              
CREATE TABLE `students` (
              
  `sid` int(11) NOT NULL AUTO_INCREMENT,
              
  `sname` varchar(100) NOT NULL,
              
  `age` int(11) DEFAULT NULL,
              
  `address` varchar(100) DEFAULT NULL,
              
  `courseid` int(11) DEFAULT NULL,
              
  PRIMARY KEY (`sid`)
              
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
              

              
/*Data for the table `students` */
              

              
insert  into `students`(`sid`,`sname`,`age`,`address`,`courseid`) values (1,'小海子',23,'北京',1003),(2,'小沈陽(yáng)',45,'沈陽(yáng)',1003),(3,'劉陽(yáng)',25,'山東',1002),(4,'甘能',22,'廣東',1002);
              

              
/*Table structure for table `teacher` */
              

              
DROP TABLE IF EXISTS `teacher`;
              

              
CREATE TABLE `teacher` (
              
  `tid` int(5) NOT NULL AUTO_INCREMENT,
              
  `tname` varchar(100) NOT NULL,
              
  `age` int(4) DEFAULT NULL,
              
  `address` varchar(100) DEFAULT NULL,
              
  `courseid` int(11) DEFAULT NULL,
              
  PRIMARY KEY (`tid`)
              
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8mb4;
              

              
/*Data for the table `teacher` */
              

              
insert  into `teacher`(`tid`,`tname`,`age`,`address`,`courseid`) values (101,'馬云',50,'杭州',NULL),(102,'趙本山',52,'沈陽(yáng)',NULL),(103,'劉強(qiáng)東',45,'北京',NULL);

執(zhí)行計(jì)劃詳解

通過(guò)EXPLAIN關(guān)鍵分析的結(jié)果由以下列組成，接下來(lái)挨個(gè)分析每一個(gè)列

1	COPY explain select * from account;

ID列

描述select查詢的序列號(hào),包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序

根據(jù)ID的數(shù)值結(jié)果可以分成一下三種情況

id相同

執(zhí)行順序由上至下

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` a 
              
  LEFT JOIN `course` b 
              
    ON a.courseid = b.cid 
              
  LEFT JOIN `teacher` c 
              
    ON b.tid = c.tid

我們發(fā)現(xiàn)這幾個(gè)的id都是一樣的那他們就會(huì)順序向下執(zhí)行

id不同

如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE courseid = 
              
  (SELECT 
              
    cid 
              
  FROM
              
    `course` 
              
  WHERE tid = 
              
    (SELECT 
              
      tid 
              
    FROM
              
      `teacher` 
              
    WHERE tname = '馬云'))

我們發(fā)現(xiàn)這幾個(gè)id是從小到大的，那么按照?qǐng)?zhí)行順序應(yīng)該是從大到小先執(zhí)行teacher然后course?最后是students

id相同不同(兩種情況同時(shí)存在)

id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE courseid = 
              
  (SELECT 
              
    cid 
              
  FROM
              
    `course` a 
              
    LEFT JOIN teacher b 
              
      ON a.tid = b.tid 
              
  WHERE b.tname = '趙本山')

我們發(fā)現(xiàn)有兩個(gè)id是2 的一個(gè)1 先按照從大到小先執(zhí)行id是2的 2是相同的就按照順序向下執(zhí)行先執(zhí)行 b 在執(zhí)行a z最后執(zhí)行students。

select_type列

查詢的類型，主要是用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢

SIMPLE類型

簡(jiǎn)單的 select 查詢，查詢中不包含子查詢或者UNION

COPY

PRIMARY與SUBQUERY類型

PRIMARY：查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為

SUBQUERY：在SELECT或WHERE列表中包含了子查詢

COPY

              EXPLAIN 
              
SELECT 
              
  teacher.*,
              
  (SELECT 
              
    students.sid 
              
  FROM
              
    students 
              
  WHERE students.sid = 1) 
              

                FROM
              
              
  teacher

DERIVED類型

在FROM列表中包含的子查詢被標(biāo)記為DERIVED(衍生)，MySQL會(huì)遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時(shí)表里。

COPY

              EXPLAIN 
              
SELECT 
              
  t1.* 
              

                FROM
              
              
  students t1,
              
  (SELECT 
              
    t2.* 
              
  FROM
              
    course t2 
              
  WHERE t2.cid NOT IN (1) 
              
  LIMIT 1) s2 
              
WHERE t1.courseid = s2.cid ;

UNION RESULT 與UNION類型

UNION：若第二個(gè)SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION；

UNION RESULT：從UNION表獲取結(jié)果的SELECT

COPY

              EXPLAIN SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE sid = 1 
              

                UNION
              
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE sid = 2

table列

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

COPY

Type列

type顯示的是訪問(wèn)類型，是較為重要的一個(gè)指標(biāo)，結(jié)果值從最好到最壞依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要記憶的

system>const>eq_ref>ref>range>index>ALL

一般來(lái)說(shuō)，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref。

NULL訪問(wèn)類型

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

COPY

              EXPLAIN 
              
SELECT 
              
  MIN(sid)
              

                FROM
              
              
  `students`

System與const訪問(wèn)類型

System：表只有一行記錄(等于系統(tǒng)表)，這是const類型的特列，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可以忽略不計(jì)

Const：表示通過(guò)索引一次就找到了。

const 用于比較primary key或者unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量，讀取1次，速度比較快。

1	COPY EXPLAIN SELECT * FROM (SELECT * FROM `teacher` WHERE tid = 101 LIMIT 1) d1;

eq_ref訪問(wèn)類型

唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見(jiàn)于主鍵或唯一索引掃描。

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

COPY

              -- 增加索引
              
ALTER TABLE `innodatabase`.`students` 
              
  ADD INDEX `students_courseid_index` (`courseid`) ;
              

              
EXPLAIN 
              
SELECT 
              
  a.* 
              

                FROM
              
              
  `students` a 
              
  LEFT JOIN `course` b 
              
    ON a.courseid = b.cid

Ref訪問(wèn)類型

非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。

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

本質(zhì)上也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體。

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE courseid = 1003

ref_or_null訪問(wèn)類型

類似ref，但是可以搜索值為NULL的行。

index_merge訪問(wèn)類型

表示使用了索引合并的優(yōu)化方法

Range訪問(wèn)類型

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

這種范圍掃描索引掃描比全表掃描要好，因?yàn)樗恍枰_(kāi)始于索引的某一點(diǎn)，而結(jié)束語(yǔ)另一點(diǎn)，不用掃描全部索引。

COPY

              EXPLAIN 
              
SELECT 
              
  age 
              

                FROM
              
              
  students 
              
WHERE age IN(10,20,45,50)

COPY

              EXPLAIN 
              
SELECT 
              
  age 
              

                FROM
              
              
  students 
              
WHERE age BETWEEN 10 
              
  AND 50

Index訪問(wèn)類型

和ALL一樣，不同就是mysql只需掃描索引樹(shù)，這通常比ALL快一些。

當(dāng)查詢的結(jié)果全為索引列的時(shí)候，雖然也是全部掃描，但是只查詢的索引庫(kù)，而沒(méi)有去查詢數(shù)據(jù)。

COPY

All訪問(wèn)類型

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

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students 
              
WHERE address = ''

possible_keys列

這一列顯示查詢可能使用哪些索引來(lái)查找。

explain 時(shí)可能出現(xiàn) possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，mysql認(rèn)為索引對(duì)此查詢幫助不大，選擇了全表查詢。

如果該列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，可以通過(guò)檢查 where 子句看是否可以創(chuàng)造一個(gè)適當(dāng)?shù)乃饕齺?lái)提高查詢性能，然后用 explain 查看效果。

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students 
              
WHERE age BETWEEN 10 
              
  AND 100

key列

這一列顯示mysql實(shí)際采用哪個(gè)索引來(lái)優(yōu)化對(duì)該表的訪問(wèn)。

如果沒(méi)有使用索引，則該列是 NULL。如果想強(qiáng)制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index、ignore index。

查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊

COPY

key_len列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過(guò)這個(gè)值可以算出具體使用了索引中的哪些列。

Key_len表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好。

key_len顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得，不是通過(guò)表內(nèi)檢索出的

舉例來(lái)說(shuō)，students索引 students_courseid_index由 courseid 一個(gè)個(gè)int列組成，并且每個(gè)int是4字節(jié)，并且是可以為null占用一個(gè)字節(jié)。通過(guò)結(jié)果中的key_len=4+1=5可推斷出查詢使用了courseid 列來(lái)執(zhí)行索引查找。

COPY

              EXPLAIN 
              
SELECT 
              
  courseid
              

                FROM
              
              
  students 
              
WHERE courseid = 1003

key_len計(jì)算規(guī)則如下

字符串
- char(n)：n字節(jié)長(zhǎng)度
- varchar(n)：2字節(jié)存儲(chǔ)字符串長(zhǎng)度，如果是utf-8，則長(zhǎng)度 3n + 2
數(shù)值類型

　　
- tinyint：1字節(jié)
- smallint：2字節(jié)
- int：4字節(jié)
- bigint：8字節(jié)
時(shí)間類型　
- date：3字節(jié)
- time：3字節(jié)
- year：1字節(jié)
- timestamp：4字節(jié)
- datetime：8字節(jié)
latin1占用1個(gè)字節(jié)，gbk占用2個(gè)字節(jié)，utf8占用3個(gè)字節(jié)。（不同字符編碼占用的存儲(chǔ)空間不同）****
如果字段允許為 NULL，需要1字節(jié)記錄是否為 NULL
編碼（不同字符編碼占用的存儲(chǔ)空間不同）
- latin1：1字節(jié)
- gbk：2字節(jié)
- utf8：3字節(jié)

索引最大長(zhǎng)度是768字節(jié)，當(dāng)字符串過(guò)長(zhǎng)時(shí)，mysql會(huì)做一個(gè)類似左前綴索引的處理，將前半部分的字符提取出來(lái)做索引。

注意

根據(jù)底層使用的不通存儲(chǔ)引擎，受影響的行數(shù)這個(gè)指標(biāo)可能是一個(gè)估計(jì)值，也可能是一個(gè)精確值。及時(shí)受影響的行數(shù)是一個(gè)估計(jì)值（例如當(dāng)使用InnoDB存儲(chǔ)引擎管理表存儲(chǔ)時(shí)），通常情況下這個(gè)估計(jì)值也足以使優(yōu)化器做出一個(gè)有充分依據(jù)的決定。

字符類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型。

類型	大小	用途
CHAR	0-255字節(jié)	定長(zhǎng)字符串
VARCHAR	0-65535 字節(jié)	變長(zhǎng)字符串
TINYBLOB	0-255字節(jié)	不超過(guò) 255 個(gè)字符的二進(jìn)制字符串
TINYTEXT	0-255字節(jié)	短文本字符串
BLOB	0-65 535字節(jié)	二進(jìn)制形式的長(zhǎng)文本數(shù)據(jù)
TEXT	0-65 535字節(jié)	長(zhǎng)文本數(shù)據(jù)
MEDIUMBLOB	0-16 777 215字節(jié)	二進(jìn)制形式的中等長(zhǎng)度文本數(shù)據(jù)
MEDIUMTEXT	0-16 777 215字節(jié)	中等長(zhǎng)度文本數(shù)據(jù)
LONGBLOB	0-4 294 967 295字節(jié)	二進(jìn)制形式的極大文本數(shù)據(jù)
LONGTEXT	0-4 294 967 295字節(jié)	極大文本數(shù)據(jù)

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長(zhǎng)度和是否尾部空格被保留等方面也不同。在存儲(chǔ)或檢索過(guò)程中不進(jìn)行大小寫(xiě)轉(zhuǎn)換。

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說(shuō)，它們包含字節(jié)字符串而不是字符字符串。這說(shuō)明它們沒(méi)有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。

BLOB 是一個(gè)二進(jìn)制大對(duì)象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲(chǔ)范圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對(duì)應(yīng)的這 4 種 BLOB 類型，可存儲(chǔ)的最大長(zhǎng)度不同，可根據(jù)實(shí)際情況選擇。

以上這個(gè)表列出了所有字符類型，但真正建所有的類型常用情況只是CHAR、VARCHAR

索引字段為char類型

n字節(jié)長(zhǎng)度

不可為Null時(shí)

name這一列為char(10),字符集為utf-8占用3個(gè)字節(jié)

Keylen=10*3

COPY

              CREATE TABLE `s1` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` CHAR(10) NOT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s1 
              
WHERE NAME = 'enjoy' ;

允許為Null時(shí)

name這一列為char(10),字符集為utf-8占用3個(gè)字節(jié),外加需要存入一個(gè)null值

Keylen=10*3+1(null) 結(jié)果為31

COPY

              CREATE TABLE `s2` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` CHAR(10) DEFAULT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s2 
              
WHERE NAME = 'enjoyedu' ;

索引字段為varchar類型

2字節(jié)存儲(chǔ)字符串長(zhǎng)度，如果是utf-8，則長(zhǎng)度 3n + 2

不可為Null時(shí)

Keylen=varchar(n)變長(zhǎng)字段+不允許Null=n*(utf8=3,gbk=2,latin1=1)+2

COPY

              CREATE TABLE `s3` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` VARCHAR(10) NOT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s3 
              
WHERE NAME = 'enjoyeud' ;

可為Null時(shí)

Keylen=varchar(n)變長(zhǎng)字段+允許Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

COPY

              CREATE TABLE `s4` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` VARCHAR(10) DEFAULT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s4 
              
WHERE NAME = 'enjoyeud' ;

數(shù)值類型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。

這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)。

關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。

BIT數(shù)據(jù)類型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標(biāo)準(zhǔn)的擴(kuò)展，MySQL也支持整數(shù)類型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個(gè)整數(shù)類型的存儲(chǔ)和范圍。

類型	大小	范圍（有符號(hào)）	范圍（無(wú)符號(hào)）	用途
TINYINT	1 字節(jié)	(-128，127)	(0，255)	小整數(shù)值
SMALLINT	2 字節(jié)	(-32 768，32 767)	(0，65 535)	大整數(shù)值
MEDIUMINT	3 字節(jié)	(-8 388 608，8 388 607)	(0，16 777 215)	大整數(shù)值
INT或INTEGER	4 字節(jié)	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數(shù)值
BIGINT	8 字節(jié)	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數(shù)值
FLOAT	4 字節(jié)	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度浮點(diǎn)數(shù)值
DOUBLE	8 字節(jié)	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度浮點(diǎn)數(shù)值
DECIMAL	對(duì)DECIMAL(M,D) ，如果M>D，為M+2否則為D+2	依賴于M和D的值	依賴于M和D的值	小數(shù)值

創(chuàng)建表

COPY

              CREATE TABLE `numberKeyLen` (
              
`c0`  INT(255) NOT NULL ,
              
`c1`  TINYINT(255) NULL DEFAULT NULL ,
              
`c2`  SMALLINT(255) NULL DEFAULT NULL ,
              
`c3`  MEDIUMINT(255) NULL DEFAULT NULL ,
              
`c4`  INT(255) NULL DEFAULT NULL ,
              
`c5`  BIGINT(255) NULL DEFAULT NULL ,
              
`c6`  FLOAT(255,0) NULL DEFAULT NULL ,
              
`c7`  DOUBLE(255,0) NULL DEFAULT NULL ,
              
PRIMARY KEY (`c0`),
              
INDEX `index_tinyint` (`c1`) USING BTREE ,
              
INDEX `index_smallint` (`c2`) USING BTREE ,
              
INDEX `index_mediumint` (`c3`) USING BTREE ,
              
INDEX `index_int` (`c4`) USING BTREE ,
              
INDEX `index_bigint` (`c5`) USING BTREE ,
              
INDEX `index_float` (`c6`) USING BTREE ,
              
INDEX `index_double` (`c7`) USING BTREE 
              
)
              
ENGINE=INNODB
              
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
              
ROW_FORMAT=COMPACT
              
;

TINYINT類型

TINYINT類型占用1個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 1+1 =2

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c1=1;

SMALLINT類型

SMALLINT類型占用2個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 2+1 =3

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c2=1;

MEDIUMINT類型

MEDIUMINT類型占用3個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 3+1 =4

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c3=1;

INT類型

INT類型占用4個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 4+1 =5

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c4=1;

BIGINT類型

BIGINT類型占用8個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 8+1 =9

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c5=1;

FLOAT類型

FLOAT類型占用4個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 4+1 =5

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c6=1;

DOUBLE類型

DOUBLE類型占用8個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 8+1 =9

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c7=1;

日期和時(shí)間

表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)”零”值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用”零”值。

TIMESTAMP類型有專有的自動(dòng)更新特性，將在后面描述。

類型	大小 (字節(jié))	范圍	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	時(shí)間值或持續(xù)時(shí)間
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時(shí)間值
TIMESTAMP	4	1970-01-01 00:00:00/2038結(jié)束時(shí)間是第?2147483647?秒，北京時(shí)間?2038-1-19 11:14:07，格林尼治時(shí)間 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和時(shí)間值，時(shí)間戳

注意

datetime類型在5.6中字段長(zhǎng)度是5個(gè)字節(jié)

datetime類型在5.5中字段長(zhǎng)度是8個(gè)字節(jié)

創(chuàng)建表

COPY

              CREATE TABLE `datatimekeylen` (
              
`c1` DATE NULL DEFAULT NULL ,
              
`c2` TIME NULL DEFAULT NULL ,
              
`c3` YEAR NULL DEFAULT NULL ,
              
`c4` DATETIME NULL DEFAULT NULL ,
              
`c5`  TIMESTAMP NULL DEFAULT NULL ,
              
INDEX `index_date` (`c1`) USING BTREE ,
              
INDEX `index_time` (`c2`) USING BTREE ,
              
INDEX `index_year` (`c3`) USING BTREE ,
              
INDEX `index_datetime` (`c4`) USING BTREE ,
              
INDEX `index_timestamp` (`c5`) USING BTREE 
              
)
              
ENGINE=INNODB
              
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
              
ROW_FORMAT=COMPACT
              
;

date類型

date 類型占用3個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 3+4 =4

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c1 = 1;

time類型

time 類型占用3個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 3+4 =4

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c2 = 1;

year類型

time 類型占用1個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 1+1 =2

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c3 = 1;

datetime類型

datetime類型在5.6中字段長(zhǎng)度是5個(gè)字節(jié)

datetime類型占用5個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 5+1 =6

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c4 = 1;

TIMESTAMP類型

TIMESTAMP類型占用4個(gè)字節(jié)允許為空占用1個(gè)字節(jié)

Keylen = 4+1 =5

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c5 = 1;

總結(jié)

字符類型

變長(zhǎng)字段需要額外的2個(gè)字節(jié)（VARCHAR值保存時(shí)只保存需要的字符數(shù)，另加一個(gè)字節(jié)來(lái)記錄長(zhǎng)度(如果列聲明的長(zhǎng)度超過(guò)255，則使用兩個(gè)字節(jié))，所以VARCAHR索引長(zhǎng)度計(jì)算時(shí)候要加2），固定長(zhǎng)度字段不需要額外的字節(jié)。

而NULL都需要1個(gè)字節(jié)的額外空間,所以索引字段最好不要為NULL，因?yàn)镹ULL讓統(tǒng)計(jì)更加復(fù)雜并且需要額外的存儲(chǔ)空間。

復(fù)合索引有最左前綴的特性，如果復(fù)合索引能全部使用上，則是復(fù)合索引字段的索引長(zhǎng)度之和，這也可以用來(lái)判定復(fù)合索引是否部分使用，還是全部使用。

整數(shù)/浮點(diǎn)數(shù)/時(shí)間類型的索引長(zhǎng)度

NOT NULL=字段本身的字段長(zhǎng)度

NULL=字段本身的字段長(zhǎng)度+1(因?yàn)樾枰惺欠駷榭盏臉?biāo)記，這個(gè)標(biāo)記需要占用1個(gè)字節(jié))

datetime類型在5.6中字段長(zhǎng)度是5個(gè)字節(jié)，datetime類型在5.5中字段長(zhǎng)度是8個(gè)字節(jié)

Ref列

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見(jiàn)的有：const（常量），func，NULL，字段名（例：innodatabase.s1.id）

1 2	COPY EXPLAIN SELECT * FROM s1 ,s2 WHERE s1.id = s2.id AND s1.name = 'enjoy'

由key_len可知s1表的PRIMARY被充分使用，name匹配s2表的name，name匹配了一個(gè)常量，即 ‘enjoy’

其中【shared.t2.col1】為【數(shù)據(jù)庫(kù).表.列】

Rows列

根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)。

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students s1,
              
  course s2 
              
WHERE s1.courseid = s2.cid 
              
  AND s1.sname = 'enjoy'

Extra列

包含不適合在其他列中顯示但十分重要的額外信息

Using filesort

說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。

MySQL中無(wú)法利用索引完成的排序操作稱為“文件排序”，當(dāng)發(fā)現(xiàn)有Using filesort 后，實(shí)際上就是發(fā)現(xiàn)了可以優(yōu)化的地方。

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

沒(méi)有索引

未創(chuàng)建索引，會(huì)瀏覽students整個(gè)表，保存排序關(guān)鍵字name和對(duì)應(yīng)的id，然后排序name并檢索行記錄

1	COPY EXPLAIN SELECT * FROM `students` ORDER BY sname;

上圖其實(shí)是一種索引失效的情況，發(fā)現(xiàn)沒(méi)使用索引建立students.name的索引并使用

加索引

建立了students_name_index索引,此時(shí)查詢時(shí)extra是using index

1	COPY EXPLAIN SELECT sname FROM `students` ORDER BY sname;

我們發(fā)現(xiàn)使用了索引，并且索引就是我們創(chuàng)建的students_name_index

Using temporary

mysql需要?jiǎng)?chuàng)建一張臨時(shí)表來(lái)處理查詢。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，首先是想到用索引來(lái)優(yōu)化，常見(jiàn)于排序 order by 和分組查詢 group by。

沒(méi)有索引

沒(méi)有索引，此時(shí)創(chuàng)建了張臨時(shí)表來(lái)distinct

尤其發(fā)現(xiàn)在執(zhí)行計(jì)劃里面有using filesort而且還有Using temporary的時(shí)候，特別需要注意

1	COPY EXPLAIN SELECT DISTINCT sname FROM `students`;

加索引

建立了students_name_index索引，此時(shí)查詢時(shí)extra是using index,沒(méi)有用臨時(shí)表

1	COPY EXPLAIN SELECT DISTINCT sname FROM `students`;

Using index

表示相應(yīng)的select操作中使用了覆蓋索引(Covering Index)，避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)！

這發(fā)生在對(duì)表的請(qǐng)求列都是同一索引的部分的時(shí)候，返回的列數(shù)據(jù)只使用了索引中的信息，而沒(méi)有再去訪問(wèn)表中的行記錄。是性能高的表現(xiàn)。

1	COPY EXPLAIN SELECT sname FROM `students`;

Using where

mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾。就是先讀取整行數(shù)據(jù)，再按 where 條件進(jìn)行檢查，符合就留下，不符合就丟棄。

1	COPY EXPLAIN SELECT * FROM `students` WHERE sid > 1;

impossible where

where子句的值總是false，不能用來(lái)獲取任何元組

1	COPY EXPLAIN SELECT * FROM `students` WHERE 1=2

1	COPY EXPLAIN SELECT * FROM students WHERE sname ='張三' AND sname = '李四';

博客內(nèi)容遵循署名-非商業(yè)性使用-相同方式共享 4.0 國(guó)際 (CC BY-NC-SA 4.0) 協(xié)議

本文永久鏈接是：http://www.baiyp.ren/MySQL%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92.html