摘要：現(xiàn)在的排序是根據(jù)數(shù)據(jù)表中的主鍵序號(hào)進(jìn)行的排序，沒有達(dá)到想要的效果。

答：

在日常的業(yè)務(wù)開發(fā)中，常見使用到索引的地方大概有兩類：
（1）做業(yè)務(wù)約束需求，比如需要保證表中每行的單個(gè)字段或者某幾個(gè)組合字段是唯一的，則可以在表中創(chuàng)建唯一索引
（2）提高SQL語句執(zhí)行速度，可以根據(jù)SQL語句的查詢條件在表中創(chuàng)建合適的索引，以此來提升SQL語句的執(zhí)行速度

1、在圖書數(shù)據(jù)庫數(shù)據(jù)表的書名字段里，按圖書書名進(jìn)行關(guān)鍵字搜索，如何快速搜索相關(guān)的圖書？ 現(xiàn)在由于數(shù)據(jù)不多，直接用的like模糊查找驗(yàn)證功能而已；
2、如何按匹配的關(guān)鍵度進(jìn)行快速排序？比如搜索“算法”，有一本書是《算法》，另一本書是《算法設(shè)計(jì)》，要求前者排在更前面。現(xiàn)在的排序是根據(jù)數(shù)據(jù)表中的主鍵序號(hào)id進(jìn)行的排序，沒有達(dá)到想要的效果。

答：

1、如果數(shù)據(jù)量不大，是可以在數(shù)據(jù)庫中完成搜索的，可以在搜索字段上創(chuàng)建索引，然后進(jìn)行搜索查詢：
CREATE TABLE `book` (  
  `book_id` int(11) NOT NULL AUTO_INCREMENT,  
  `book_name` varchar(100) NOT NULL,  
  .............................  
  PRIMARY KEY (`book_id`),  
  KEY `ind_name` (`book_name`)  
) ENGINE=InnoDB  
select book.*  from book , (select book_id from book where book_name like "%算法%")  book_search_id  where book.book_id=book_search_id.book_id;
但是當(dāng)數(shù)據(jù)量變得很大后，就不在適合了，可以采用一些其他的第三方搜索技術(shù)比如sphinx。

2、select book_id,book_name from book_search where book_name like "%算%" order by book_name;  
+---------+--------------+  
| book_id | book_name    |  
+---------+--------------+  
|       2 | 算法       |  
|       1 | 算法設(shè)計(jì) | 

答：

模糊查詢分為半模糊和全模糊，也就是：

select * from book where name like "xxx%";(半模糊)  
select * from book where name like "%xxx%";(全模糊)  

半模糊可以可以使用到索引，全模糊在上面場(chǎng)景是不能使用到索引的，但可以進(jìn)行一些改進(jìn)，比如：

select book.*  from book , (select book_id from book where book_name like "%算法%")  book_search_id where book.book_id=book_search_id.book_id;   

注意這里book_id是主鍵，同時(shí)在book_name上創(chuàng)建了索引
上面的sql語句可以利用全索引掃描來完成優(yōu)化，但是性能不會(huì)太好；特別在數(shù)據(jù)量大，請(qǐng)求頻繁的業(yè)務(wù)場(chǎng)景下不要在數(shù)據(jù)庫進(jìn)行模糊查詢；非得使用數(shù)據(jù)庫的話 ，建議不要在生產(chǎn)庫進(jìn)行查詢，可以在只讀節(jié)點(diǎn)進(jìn)行查詢，避免查詢?cè)斐芍鳂I(yè)務(wù)數(shù)據(jù)庫的資源消耗完，導(dǎo)致故障. 可以使用一些開源的搜索引擎技術(shù)，比如sphinx.

答：

SQL優(yōu)化需要了解優(yōu)化器原理，索引的原理，表的存儲(chǔ)結(jié)構(gòu)，執(zhí)行計(jì)劃等，可以買一本書來系統(tǒng)的進(jìn)行學(xué)習(xí)，多多實(shí)驗(yàn)；不同的數(shù)據(jù)庫優(yōu)化器的模型不一樣，比如oracle支持NL，HJ，SMJ，但是mysql只支持NL，不通的連接方式適用于不同的應(yīng)用場(chǎng)景；
NL：對(duì)于被連接的數(shù)據(jù)子集較小的情況，嵌套循環(huán)連接是個(gè)較好的選擇
HJ：對(duì)于列連接是做大數(shù)據(jù)集連接時(shí)的常用方式
SMJ：通常情況下散列連接的效果都比排序合并連接要好，然而如果行源已經(jīng)被排過序，在執(zhí)行排序合并連接時(shí)不需要再排序了，這時(shí)排序合并連接的性能會(huì)優(yōu)于散列連接。

CREATE TABLE TQueCategory  
(  
ID INT IDENTITY(1,1) PRIMARY KEY,        --問題分類ID  
NAME VARCHAR(20)        --問題分類名稱  
)  
CREATE TABLE TQuestion  
(  
ID INT IDENTITY(1,1) PRIMARY KEY,        --問題ID  
CateID INT NOT NULL,        --問題分類ID  
TITLE VARCHAR(50),        --問題標(biāo)題  
CONTENT VARCHAR(500)        --問題內(nèi)容  
)  

當(dāng)前要統(tǒng)計(jì)某個(gè)分類下的問題數(shù)，有兩種方式：
1.每次統(tǒng)計(jì)，在TQuestion通過CateID進(jìn)行分組統(tǒng)計(jì)
SELECT CateID,COUNT(1) AS QueNum FROM TQuestion GROUP BY CateID WHERE 1=1
2.在TQueCategory表增加字段QueNum，用于標(biāo)識(shí)該分類下的問題數(shù)量
ALTER TABLE TQueCategory ADD QueNum INT
SELECT CateID,QueNum FROM TQueCategory
問：在哪種業(yè)務(wù)應(yīng)用場(chǎng)景下采用上面哪種方式性能比較好，為什么？

答：

方案 一 需要對(duì) TQuestion 的 CateID字段 進(jìn)行分組 ，可以在CateID上創(chuàng)建一個(gè)索引，這樣就可以索引掃描來完成查詢；
方案 二 需要對(duì) TQueCategory 進(jìn)行掃描就可以得出結(jié)果，但是必須在問題表有插入，刪除的時(shí)候維護(hù)quenum數(shù)量；
分析：
單單從SQL的性能來看，分類表的數(shù)量應(yīng)該是遠(yuǎn)遠(yuǎn)小于問題表的數(shù)量的，所以方案二的性能會(huì)比較好； 但是如果TQuestion 的插入非常頻繁的話，會(huì)帶來對(duì)TQueCategory的頻繁更新，一次TQuestion 的insert或deleted就會(huì)帶來一次TQueCategory 的update，這個(gè)代價(jià)其實(shí)是蠻高的； 如果這個(gè)分類統(tǒng)計(jì)的查詢不是非常頻繁的話，建議還是使用方案一； 同時(shí)還可能還會(huì)其他的業(yè)務(wù)邏輯統(tǒng)計(jì)需求（例如：CateID +時(shí)間），這個(gè)時(shí)候在把邏輯放到TQueCategory就不合適了。

答：

普通寫法：
select * from t where sellerid=100 limit 100000，20
普通limit M，N的翻頁寫法，往往在越往后翻頁的過程中速度越慢，原因
mysql會(huì)讀取表中的前M+N條數(shù)據(jù)，M越大，性能就越差。

優(yōu)化寫法：

select t1.* from  t t1,(select id from t where sellerid=100 limit 100000，20) t2 where t1.id=t2.id;  

優(yōu)化后的翻頁寫法，先查詢翻頁中需要的N條數(shù)據(jù)的主鍵id，在根據(jù)主鍵id回表查詢所需要的N條數(shù)據(jù)，此過程中查詢N條數(shù)據(jù)的主鍵ID在索引中完成
注意：需要在t表的sellerid字段上創(chuàng)建索引

create index ind_sellerid on t(sellerid);

案例：
慢查詢：

select id, ... from t_buyer where sellerId = 765922982 and  
gmt_modified >= "1970-01-01 08:00:00" and gmt_modified <= "2013-06-05 17:11:31"   
limit 255000, 5000;
5000 rows in set (90 sec)

優(yōu)化后：

selectt2.* from 
(select id from t_buyer where sellerId = 765922982 and
andgmt_modified >= "1970-01-01 08:00:00" and
andgmt_modified <= "2013-06-05 17:11:31"   
limit 255000, 5000）t1,t_buyer t2 where t1.id=t2.id  
index：seller_id,gmt_modified
5000 rows in set (4.25 sec)

答：

這里的不建議使用外鍵，主要考慮到 ：
第一.維護(hù)成本上，把一些業(yè)務(wù)邏輯交由數(shù)據(jù)庫來保證，當(dāng)業(yè)務(wù)需求發(fā)生改動(dòng)的時(shí)候，需要同時(shí)考慮應(yīng)用程序和數(shù)據(jù)庫，有時(shí)候一些數(shù)據(jù)庫變更或者bug，可能會(huì)導(dǎo)致外鍵的失效；同時(shí)也給數(shù)據(jù)庫的管理人員帶來維護(hù)的麻煩，不便于管理。
第二.性能上考慮，當(dāng)大量數(shù)據(jù)寫入的時(shí)候，外鍵肯定會(huì)帶來一定的性能損耗，當(dāng)出現(xiàn)這樣的問題時(shí)候，再來改造去除外鍵，真的就不值得了；
最后，不在數(shù)據(jù)庫中參與業(yè)務(wù)的計(jì)算（存儲(chǔ)過程，函數(shù)，觸發(fā)器，外鍵），是保證數(shù)據(jù)庫運(yùn)行穩(wěn)定的一個(gè)好的最佳實(shí)踐。

答：

1.可以將mysql的慢日志打開，就可以記錄執(zhí)行時(shí)間超過指定閥值的慢SQL到本地文件或者數(shù)據(jù)庫的slow_log表中;在RDS中默認(rèn)是打開了慢日志功能的：long_query_time=1，表示會(huì)記錄執(zhí)行時(shí)間>=1秒的慢sql;

2.如何快速找到mysql瓶頸:
簡(jiǎn)單一點(diǎn)的方法，可以通過監(jiān)控mysql所在主機(jī)的性能（CPU，IO，load等）,以及mysql本身的一些狀態(tài)值（connections，thread running，qps，命中率等;
RDS提供了完善的數(shù)據(jù)庫監(jiān)控體系，包括了CPU，IOPS，Disk，Connections，QPS，可以重點(diǎn)關(guān)注cpu，IO，connections，disk 4個(gè) 指標(biāo); cpu，io，connections主要體現(xiàn)在了性能瓶頸，disk主要體現(xiàn)了空間瓶頸；
有時(shí)候一條慢sql語句的頻繁調(diào)用，也可能導(dǎo)致整個(gè)實(shí)例的cpu，io，connections達(dá)到100%;也有可能一條排序的sql語句，消耗大量的臨時(shí)空間，導(dǎo)致實(shí)例的空間消耗完。