優(yōu)化是一個(gè)綜合復(fù)雜的問題，涉及到硬軟件平臺(tái)。這里僅就 MySQL5.0 SQL 查詢語句方面作個(gè)小結(jié)。先舉個(gè)例子：

現(xiàn)在表register(PRIMARY KEY regId),service(PRIMARY KEY servId, index serviceId )和subscribe(PRIMARY KEY subId,FOREIGN KEY regId/servId)，且前兩表跟后張表均為一對(duì)多關(guān)聯(lián)，假設(shè)有如下表格結(jié)構(gòu)：

    CREATE TABLE `subscribe` (
  `subId` int(10) unsigned NOT NULL auto_increment,
  `subcribeTime` datetime NOT NULL,
  `expireTime` datetime NOT NULL,
  `cancelTime` datetime default NULL,
  `paymoney` double NOT NULL,
  `paymentTime` datetime NOT NULL,
  `payWay` tinyint(3) unsigned NOT NULL,
  `subcribeStatus` tinyint(3) unsigned NOT NULL,
  `paymentStatus` tinyint(3) unsigned NOT NULL,
  `createTime` datetime NOT NULL,
  `regId` int(10) default NULL,
  `servId` int(10) default NULL,
  PRIMARY KEY  (`subId`),
  KEY `FK_REGID` (`regId`),
  KEY `FK_SERVID` (`servId`),
  CONSTRAINT `FK_REGID` FOREIGN KEY (`regId`) REFERENCES `register` (`regId`) 
ON DELETE CASCADE,
  CONSTRAINT `FK_SERVID` FOREIGN KEY (`servId`) REFERENCES `service` (`servId`) 
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

現(xiàn)在一業(yè)務(wù)根據(jù)條件 subscribe.subcribeStatus=1 或 >=3 AND subscribe.expireTime <= now() 要取得 subscribe.subId,register.phoneNum,service.serviceId 三個(gè)字段信息列表。測(cè)試中三表中各有三條數(shù)據(jù)具結(jié)果亦為3，初始 sql 語句及執(zhí)行結(jié)果如下：

這是一條最容易想到的 SQL 語句，雖然優(yōu)化器默認(rèn)采用 type 快速的 index 和 eq_ref 類型，但 s 和 u 的 ref 均為 NULL 意味著將全表掃描，并 產(chǎn)生一個(gè)笛卡爾乘積。對(duì)于這個(gè)實(shí)例由于各表都有3行數(shù)據(jù)，即掃描 3*3*1=9 行。如果表中數(shù)據(jù)是99999,那么將是 99999*99999*1=? ，你可以想象它將花多長時(shí)間……，如果是關(guān)鍵業(yè)務(wù)這將是一場(chǎng)惡夢(mèng)。 那有沒什么辦法盡最大可能地減少這個(gè)笛卡爾乘積呢？先看看優(yōu)化器對(duì)上面最終執(zhí)行的語句：

可以看到優(yōu)化器將使用內(nèi)連接來執(zhí)行這個(gè)語句，這將是一個(gè)考慮的優(yōu)化點(diǎn)，再看看這有許多 WHERE 條件，這可不可以做反應(yīng)優(yōu)化呢？

分析一下，可以看到這些 WHERE 條件中起實(shí)質(zhì)限定作用的 均與 subscribe 這張表有關(guān) ，那就從這開始吧， u.subcribeStatus>=3 OR u.subcribeStatus=1 這個(gè)語句看起來有點(diǎn)不順眼 ( 可能是因?yàn)橛袀€(gè)OR，呵呵 ~) ，既然1到3中只2除外，那有沒可能去掉這個(gè) OR 呢？ 看一下業(yè)務(wù)需求，原來 subscribeStatus 只有1至4的值，很明顯這個(gè)寫法不妥。 可以改為 u.subcribeStatus <>2 其它條件看起來沒什么問題。再來看看聯(lián)接查詢方面的。 既然所有限定條件都是 u 表的，那么自然想到查詢應(yīng)該從 u 表開始搜索，這可以使用左/右聯(lián)接，看個(gè)人愛好。 看一下最終語句的執(zhí)行結(jié)果：

可看到笛卡爾積變成了： 3*1*1=3 ，相對(duì)前者多了個(gè) ref 引用，雖然已經(jīng)很“完美”了，但不可避免有個(gè)表 type=all 意味著將根據(jù)條件進(jìn)行全表掃描。再看看優(yōu)化器的執(zhí)行方式：

很顯然，它按我們優(yōu)化方向執(zhí)行，先從 u 開始再聯(lián)接 s / r (這兩次序無所謂)查詢。就這個(gè)示例，我們也只能優(yōu)化到這里，看看笛卡積，效果還是相當(dāng)不錯(cuò)的，少了一個(gè)數(shù)量級(jí)的掃描。根據(jù) SHOW WARNINGS 最終 SQL 為( ON 條件中也可以不用括號(hào))：

    SELECT u.subId, r.phoneNum, s.serviceId FROM subscribe as u LEFT JOIN service as s
?ON (s.servId=u.servId) LEFT JOIN register as r ON (r.regId=u.regId)
?WHERE u.subcribeStatus<>=? AND u.expireTime <= now() LIMIT ?,?
  

?

通過上面示例，可以知道 MySQL 提供的一些工具非常實(shí)用，下面介紹一下剛才用過的：

1. GRANT 語句盡量簡單，以降低不必要的許可檢查開銷;
2. 如果問題與具體 MYSQ L表達(dá)式或函數(shù)有關(guān)，可使用 BENCHMARK() 函數(shù)執(zhí)行定時(shí)測(cè)試，語法： BENCHMARK(loop_count,expression) ;
3. EXPLAIN 可作為 DESCRIBE 的同義詞，它將解釋 MySQL 如何處理 SELECT 語句，提供有關(guān)表如何聯(lián)接和聯(lián)接的次序信息，這對(duì)優(yōu)化 SQL 語句特別是級(jí)聯(lián)查詢時(shí)特別有用。

4. SHOW WARNINGS 可以瀏覽 EXTENDED 產(chǎn)生的附加信息，輸出優(yōu)化器重寫并優(yōu)化后的SELECT語句，可能還包括優(yōu)化過程的其它注解。
EXPLAIN 語法及等價(jià) SQL 語句如下：
EXPLAIN tbl_name / EXPLAIN [EXTENDED] SELECT select_options
EXPLAIN tbl_name = DESCRIBE tbl_name = SHOW COLUMNS FROM tbl_name

????????? 何時(shí)須為表加入索引以得到更快的 SELECT
???????? /
主要用途:
???????? \
????????? 知道優(yōu)化器是否以一個(gè)最佳次序聯(lián)接表

?

?

WHERE子句優(yōu)化

1. MySQL 能更高效地在聲明具有相同類型和尺寸的列上使用索引,所以在類型相同時(shí)盡量保持相同尺寸(如對(duì)于固定大小的使用char類型),如果數(shù)據(jù)經(jīng)常修改的話 CHAR 要優(yōu)于 VARCHAR 。因?yàn)槎ㄩL的行并不會(huì)有存儲(chǔ)殘片。對(duì)于非常短的列， CHAR 要比 VARCHAR 高效。

?

2.去除不必要的括號(hào)

e.g. ((a AND b) AND c OR (((a AND b) AND (c AND d))))
?? -> (a AND b AND c) OR (a AND b AND c AND d)

?

3. 常量重疊

e.g. (a < b AND b = c) AND a = 5
?? -> b > 5 AND b = c AND a = 5

?

4. 去除常量條件(由于常量重疊需要 )：

?e.g. (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
?? -> B=5 OR B=6

?

5. 如果不使用GROUP BY或分組函數(shù)(COUNT()、MIN()……)，HAVING與WHERE合并。

?

6. 對(duì)于聯(lián)接內(nèi)的每個(gè)表，構(gòu)造一個(gè)更簡單的WHERE以便更快地對(duì)表進(jìn)行WHERE計(jì)算并且也盡快跳過記錄。

?

7. 所有常數(shù)的表在查詢中比其它表先讀出。常數(shù)表為：
?? 空表或只有1行的表。
?? 與在一個(gè)PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，這里所有的索引部分使用常數(shù)表達(dá)式并且索引部分被定義為NOT NULL。

e.g.下列的所有表用作常數(shù)表：
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id

?

?

待續(xù)。。。。

?

MySQL SQL優(yōu)化小結(jié)