91麻豆免费视频网站,国产精品夜夜春夜夜爽久久电影,国产精品老女人,奇米久久久,亚洲三级免费,好叼操这里只有精品,久久九九色,国产33页

MySQL執(zhí)行計劃

前言

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

那么，問題來了，在查詢前有沒有可能估計下查詢要掃描多少行、使用哪些索引呢？

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

什么是執(zhí)行計劃

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

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

MySQL邏輯架構(gòu)

客戶端

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

核心服務(wù)

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

存儲引擎

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

查詢緩存

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

MySQL查詢過程

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

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

MySQL查詢過程

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

執(zhí)行計劃

執(zhí)行計劃的作用

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

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

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

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

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

語法

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

比如：EXPLAIN select * from table1，重點的就是EXPLAIN后面你要分析的SQL語句。

準(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,'小沈陽',45,'沈陽',1003),(3,'劉陽',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,'沈陽',NULL),(103,'劉強東',45,'北京',NULL);

執(zhí)行計劃詳解

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

1	COPY explain select * from account;

ID列

描述select查詢的序列號,包含一組數(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)這幾個的id都是一樣的那他們就會順序向下執(zhí)行

id不同

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

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

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

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

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

id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級越高，越先執(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)有兩個id是2 的一個1 先按照從大到小先執(zhí)行id是2的 2是相同的就按照順序向下執(zhí)行先執(zhí)行 b 在執(zhí)行a z最后執(zhí)行students。

select_type列

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

SIMPLE類型

簡單的 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會遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時表里。

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：若第二個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顯示的是訪問類型，是較為重要的一個指標(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

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

NULL訪問類型

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

COPY

              EXPLAIN 
              
SELECT 
              
  MIN(sid)
              

                FROM
              
              
  `students`

System與const訪問類型

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

Const：表示通過索引一次就找到了。

const 用于比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)，所以很快如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量，讀取1次，速度比較快。

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

eq_ref訪問類型

唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。

primary key 或 unique key 索引的所有部分被連接使用，最多只會返回一條符合條件的記錄。這可能是在 const 之外最好的聯(lián)接類型了，簡單的 select 查詢不會出現(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訪問類型

非唯一性索引掃描，返回匹配某個單獨值的所有行。

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

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

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE courseid = 1003

ref_or_null訪問類型

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

index_merge訪問類型

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

Range訪問類型

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

這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結(jié)束語另一點，不用掃描全部索引。

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訪問類型

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

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

COPY

All訪問類型

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

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students 
              
WHERE address = ''

possible_keys列

這一列顯示查詢可能使用哪些索引來查找。

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

如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查 where 子句看是否可以創(chuàng)造一個適當(dāng)?shù)乃饕齺硖岣卟樵冃阅埽缓笥?explain 查看效果。

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students 
              
WHERE age BETWEEN 10 
              
  AND 100

key列

這一列顯示mysql實際采用哪個索引來優(yōu)化對該表的訪問。

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

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

COPY

key_len列

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

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

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

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

COPY

              EXPLAIN 
              
SELECT 
              
  courseid
              

                FROM
              
              
  students 
              
WHERE courseid = 1003

key_len計算規(guī)則如下

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

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

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

注意

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

字符類型

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

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

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

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

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

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

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

索引字段為char類型

n字節(jié)長度

不可為Null時

name這一列為char(10),字符集為utf-8占用3個字節(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時

name這一列為char(10),字符集為utf-8占用3個字節(jié),外加需要存入一個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é)存儲字符串長度，如果是utf-8，則長度 3n + 2

不可為Null時

Keylen=varchar(n)變長字段+不允許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時

Keylen=varchar(n)變長字段+允許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)的擴展，MySQL也支持整數(shù)類型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數(shù)類型的存儲和范圍。

類型	大小	范圍（有符號）	范圍（無符號）	用途
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)	單精度浮點數(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)	雙精度浮點數(shù)值
DECIMAL	對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個字節(jié)允許為空占用1個字節(jié)

Keylen = 1+1 =2

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

SMALLINT類型

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

Keylen = 2+1 =3

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

MEDIUMINT類型

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

Keylen = 3+1 =4

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

INT類型

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

Keylen = 4+1 =5

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

BIGINT類型

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

Keylen = 8+1 =9

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

FLOAT類型

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

Keylen = 4+1 =5

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

DOUBLE類型

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

Keylen = 8+1 =9

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

日期和時間

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

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

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

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

注意

datetime類型在5.6中字段長度是5個字節(jié)

datetime類型在5.5中字段長度是8個字節(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個字節(jié)允許為空占用1個字節(jié)

Keylen = 3+4 =4

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

time類型

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

Keylen = 3+4 =4

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

year類型

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

Keylen = 1+1 =2

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

datetime類型

datetime類型在5.6中字段長度是5個字節(jié)

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

Keylen = 5+1 =6

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

TIMESTAMP類型

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

Keylen = 4+1 =5

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

總結(jié)

字符類型

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

而NULL都需要1個字節(jié)的額外空間,所以索引字段最好不要為NULL，因為NULL讓統(tǒng)計更加復(fù)雜并且需要額外的存儲空間。

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

整數(shù)/浮點數(shù)/時間類型的索引長度

NOT NULL=字段本身的字段長度

NULL=字段本身的字段長度+1(因為需要有是否為空的標(biāo)記，這個標(biāo)記需要占用1個字節(jié))

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

Ref列

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：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匹配了一個常量，即 ‘enjoy’

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

Rows列

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

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

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

Extra列

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

Using filesort

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

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

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

沒有索引

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

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

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

加索引

建立了students_name_index索引,此時查詢時extra是using index

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

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

Using temporary

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

沒有索引

沒有索引，此時創(chuàng)建了張臨時表來distinct

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

1	COPY EXPLAIN SELECT DISTINCT sname FROM `students`;

加索引

建立了students_name_index索引，此時查詢時extra是using index,沒有用臨時表

1	COPY EXPLAIN SELECT DISTINCT sname FROM `students`;

Using index

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

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

1	COPY EXPLAIN SELECT sname FROM `students`;

Using where

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

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

impossible where

where子句的值總是false，不能用來獲取任何元組

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

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

博客內(nèi)容遵循署名-非商業(yè)性使用-相同方式共享 4.0 國際 (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