MySQL數(shù)據(jù)表使用的SQL語句整理

更新時(shí)間：2022年05月20日 17:18:05 作者：??三苗同學(xué)????

這篇文章主要介紹了MySQL數(shù)據(jù)表使用的SQL語句整理，文章基于MySQL的相關(guān)資料展開舉例說明，具有一定的參考價(jià)值，需要的小伙伴可以參考一下

EXPLAIN 語句

分析SQL索引使用，關(guān)鍵詞EXPLAIN： SQL舉例：

CREATE TABLE `my_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名字',
  `sex` enum('0','1') NOT NULL COMMENT '性別',
  `tag_ids` varchar(255) NOT NULL COMMENT '標(biāo)簽',
  `score` decimal(5,2) NOT NULL DEFAULT '0.00' COMMENT '分?jǐn)?shù)',
  `class_rome` tinyint(2) NOT NULL DEFAULT '0' COMMENT '班級(jí)號(hào)',
  PRIMARY KEY (`id`),
  KEY `score` (`score`) USING BTREE COMMENT '分?jǐn)?shù)索引',
  KEY `class_rome` (`class_rome`) USING BTREE COMMENT '班級(jí)索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

舉例：

EXPLAIN SELECT * FROM my_user WHERE score =10;

結(jié)果如下圖：

接下來展示 explain 中每個(gè)列的信息。

列字段	說明
id	id列的編號(hào)是 select 的序列號(hào)，有幾個(gè) select 就有幾個(gè)id，并且id的順序是按 select 出現(xiàn)的順序增長(zhǎng)的。MySQL將 select 查詢分為簡(jiǎn)單查詢和復(fù)雜查詢。復(fù)雜查詢分為三類：簡(jiǎn)單子查詢、派生表（from語句中的子查詢）、union 查詢。
select_type	select_type 表示對(duì)應(yīng)行是是簡(jiǎn)單還是復(fù)雜的查詢，如果是復(fù)雜的查詢，又是上述三種復(fù)雜查詢中的哪一種。`simple`：簡(jiǎn)單查詢；`primary`：復(fù)雜查詢中最外層的 select；`subquery`：包含在 select 中的子查詢（不在 from 子句中）;`derived`：包含在 from 子句中的子查詢。MySQL會(huì)將結(jié)果存放在一個(gè)臨時(shí)表中，也稱為派生表;`union`：在 union 中的第二個(gè)和隨后的 select;`union result`：從 union 臨時(shí)表檢索結(jié)果的 select
table	這一列表示 explain 的一行正在訪問哪個(gè)表。當(dāng) from 子句中有子查詢時(shí)，table列是 `<derivenN>` 格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。當(dāng)有 union 時(shí)，UNION RESULT 的 table 列的值為 `<union1,2>`，1和2表示參與 union 的 select 行id。
partitions	如果查詢是基于分區(qū)表的話，會(huì)顯示查詢將訪問的分區(qū)。
type	這一列表示關(guān)聯(lián)類型或訪問類型，即MySQL決定如何查找表中的行。依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL； `NULL`：mysql能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著再訪問表或索引。例如：在索引列中選取最小值，可以單獨(dú)查找索引來完成，不需要在執(zhí)行時(shí)訪問表。 `system`，`const`：mysql能對(duì)查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個(gè)常量（可以看show warnings 的結(jié)果）。用于 primary key 或 unique key 的所有列與常數(shù)比較時(shí)，所以表最多有一個(gè)匹配行，讀取1次，速度比較快。 `eq_ref`：primary key 或 unique key 索引的所有部分被連接使用，最多只會(huì)返回一條符合條件的記錄。這可能是在 const 之外最好的聯(lián)接類型了，簡(jiǎn)單的 select 查詢不會(huì)出現(xiàn)這種 type。 `ref`：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個(gè)值相比較，可能會(huì)找到多個(gè)符合條件的行。 `ref_or_null`：類似ref，但是可以搜索值為NULL的行。 `index_merge`：表示使用了索引合并的優(yōu)化方法。 `range`：范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。使用一個(gè)索引來檢索給定范圍的行。 `index`：和ALL一樣，不同就是mysql只需掃描索引樹，這通常比ALL快一些。 `ALL`：即全表掃描，意味著mysql需要從頭到尾去查找所需要的行。通常情況下這需要增加索引來進(jìn)行優(yōu)化了;
possible_keys	這一列顯示查詢可能使用哪些索引來查找。 explain 時(shí)可能出現(xiàn) possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，mysql認(rèn)為索引對(duì)此查詢幫助不大，選擇了全表查詢。如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查 where 子句看是否可以創(chuàng)造一個(gè)適當(dāng)?shù)乃饕齺硖岣卟樵冃阅?，然后?explain 查看效果。
key	這一列顯示mysql實(shí)際采用哪個(gè)索引來優(yōu)化對(duì)該表的訪問。如果沒有使用索引，則該列是 NULL。如果想強(qiáng)制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index、ignore index。
key_len	這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過這個(gè)值可以算出具體使用了索引中的哪些列。
ref列	這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），func，NULL，字段名（例：id）
rows	這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù)，【注意】這個(gè)不是結(jié)果集里的行數(shù)。
Extra	這一列展示的是額外信息。常見的重要值如下： `distinct`: 一旦mysql找到了與行相聯(lián)合匹配的行，就不再搜索了 `Using index`：這發(fā)生在對(duì)表的請(qǐng)求列都是同一索引的部分的時(shí)候，返回的列數(shù)據(jù)只使用了索引中的信息，而沒有再去訪問表中的行記錄。是性能高的表現(xiàn)。比如: `EXPLAIN SELECT score FROM my_user WHERE score =10;` `Using where`：mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過濾。就是先讀取整行數(shù)據(jù)，再按 where 條件進(jìn)行檢查，符合就留下，不符合就丟棄。比如： `EXPLAIN SELECT * FROM my_user WHERE score >10;` `Using temporary`：mysql需要?jiǎng)?chuàng)建一張臨時(shí)表來處理查詢。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，首先是想到用索引來優(yōu)化。比如：`EXPLAIN SELECT distinct name FROM my_user;` `Using filesort`：mysql 會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按索引次序從表里讀取行。此時(shí)mysql會(huì)根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優(yōu)化的。但事實(shí)上Using filesort是一個(gè)非常差的命名。真實(shí)的情況是，如果一個(gè)排序操作不能通過索引來完成，那這次排序操作就叫做filesort，這跟file沒有任何關(guān)系。filesort應(yīng)該叫做sort，而它的實(shí)現(xiàn)，就是大家熟悉的`快速排序`。比如： `EXPLAIN SELECT * FROM my_user order by score;`

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

類型	字節(jié)長(zhǎng)度
char(n)	n字節(jié)長(zhǎng)度
varchar(n)	2字節(jié)存儲(chǔ)字符串長(zhǎng)度，如果是utf-8，則長(zhǎng)度 3n + 2
tinyint	1字節(jié)
smallint	2字節(jié)
int	4字節(jié)
bigint	8字節(jié)
date	3字節(jié)
timestamp	4字節(jié)
datetime	8字節(jié)
decimal(m,n)	m/2（已截?cái)啵? 1 = 需要的空間字節(jié)數(shù)。比如，decimal(5,2) ,（5/2 = 2.5（截?cái)酁?2）; 2 + 1 = 3）
NULL	1字節(jié)記錄是否為 NULL

SHOW INDEX 語句

語法格式有兩種，格式如下：

SHOW INDEX FROM <表名> [ FROM <數(shù)據(jù)庫名>]
SHOW INDEX FROM <數(shù)據(jù)庫名>.<表名>

語法說明如下：
<表名>：指定需要查看索引的數(shù)據(jù)表名。
<數(shù)據(jù)庫名>：指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫，可省略。比如，SHOW INDEX FROM my_user FROM test; 語句表示查看 test 數(shù)據(jù)庫中 student 數(shù)據(jù)表的索引。

下面來舉例說明。

mysql> SHOW INDEX FROM my_user;

SHOW INDEX 返回以下字段：

字段	說明
Table	表的名稱
Non_unique	如果索引不能包含重復(fù)項(xiàng)，則為 0，如果可以，則為 1。
Key_name	索引的名稱。如果索引是主鍵，則名稱始終為PRIMARY。
Seq_in_index	索引中的列序號(hào)，從 1 開始。
Column_name	索引列名。另請(qǐng)參閱Expression列的說明。
Collation	列在索引中的排序方式。這可以有值 A（升序）、D （降序）或NULL（未排序）。
Cardinality	對(duì)索引中唯一值數(shù)量的估計(jì)。要更新此數(shù)字，請(qǐng)運(yùn)行ANALYZE TABLE或（對(duì)于MyISAM表） myisamchk -a。Cardinality根據(jù)存儲(chǔ)為整數(shù)的統(tǒng)計(jì)信息進(jìn)行計(jì)數(shù)，因此即使對(duì)于小表，該值也不一定準(zhǔn)確?；鶖?shù)越高，MySQL 在進(jìn)行連接時(shí)使用索引的機(jī)會(huì)就越大。
Sub_part	索引前綴。也就是說，如果列僅部分索引，NULL則索引字符數(shù)，如果整個(gè)列都被索引。前綴限制以字節(jié)為單位。然而，前綴長(zhǎng)度為索引規(guī)范CREATE TABLE，ALTER TABLE和CREATE INDEX語句解釋為非二進(jìn)制字符串類型（CHAR、VARCHAR、TEXT）的字符數(shù)和二進(jìn)制字符串類型（binary、VARBINARY、BLOB）的字節(jié)數(shù)。使用多字節(jié)字符集的非二進(jìn)制字符串列指定前綴長(zhǎng)度時(shí)，請(qǐng)考慮這一點(diǎn)。
Packe	指示密鑰的打包方式。NULL表示沒有。
Null	如果列含有NULL，則含有YES。如果沒有，則該列含有NO。
Index_type	使用的索引方法 ( BTREE, FULLTEXT, HASH, RTREE)。
Comment	有關(guān)索引未在其自己的列中描述的信息，例如disabled索引是否已禁用。
Index_comment	COMMENT創(chuàng)建索引時(shí)為具有屬性的索引提供的注釋。

ANALYZE TABLE 語句

語法格式如下：

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE TABLE 支持InnoDB，NDB和 MyISAM表，不適用于視圖。

ANALYZE TABLE支持分區(qū)表。

默認(rèn)情況下，服務(wù)器將ANALYZE TABLE語句寫入二進(jìn)制日志，以便它們復(fù)制到副本。要禁止日志記錄，請(qǐng)指定可選 NO_WRITE_TO_BINLOG關(guān)鍵字或其別名 LOCAL。

舉例：

mysql> ANALYZE TABLE  my_user;

ANALYZE TABLE 返回包含下表中顯示的列的結(jié)果集。

字段	說明
Table	表名
Op	總是 analyze
Msg_type	status, error, info, note, 或 warning
Msg_text	信息性消息

MySQL 在連接優(yōu)化中使用索引基數(shù)估計(jì)。如果連接沒有以正確的方式優(yōu)化，請(qǐng)嘗試運(yùn)行 ANALYZE TABLE。在少數(shù)情況下，ANALYZE TABLE不會(huì)為您的特定表生成足夠好的值，您可以使用FORCE INDEX查詢來強(qiáng)制使用特定索引，或設(shè)置 max_seeks_for_key系統(tǒng)變量以確保 MySQL 更喜歡索引查找而不是表掃描。

到此這篇關(guān)于MySQL數(shù)據(jù)表使用的SQL語句整理的文章就介紹到這了,更多相關(guān)SQL語句整理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: