優化是一個綜合復雜的問題，涉及到硬軟件平臺。這里僅就 MySQL5.0 SQL 查詢語句方面作個小結。先舉個例子：

現在表register(PRIMARY KEY regId),service(PRIMARY KEY servId, index serviceId )和subscribe(PRIMARY KEY subId,FOREIGN KEY regId/servId)，且前兩表跟后張表均為一對多關聯，假設有如下表格結構：

    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;
  

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

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

可以看到優化器將使用內連接來執行這個語句，這將是一個考慮的優化點，再看看這有許多 WHERE 條件，這可不可以做反應優化呢？

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

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

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

    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 提供的一些工具非常實用，下面介紹一下剛才用過的：

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

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

????????? 何時須為表加入索引以得到更快的 SELECT
???????? /
主要用途:
???????? \
????????? 知道優化器是否以一個最佳次序聯接表

?

?

WHERE子句優化

1. MySQL 能更高效地在聲明具有相同類型和尺寸的列上使用索引,所以在類型相同時盡量保持相同尺寸(如對于固定大小的使用char類型),如果數據經常修改的話 CHAR 要優于 VARCHAR 。因為定長的行并不會有存儲殘片。對于非常短的列， CHAR 要比 VARCHAR 高效。

?

2.去除不必要的括號

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或分組函數(COUNT()、MIN()……)，HAVING與WHERE合并。

?

6. 對于聯接內的每個表，構造一個更簡單的WHERE以便更快地對表進行WHERE計算并且也盡快跳過記錄。

?

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

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

?

?

待續。。。。

?

MySQL SQL優化小結