這篇文章主要介紹了MySQL的id關(guān)聯(lián)實際優(yōu)化案例,關(guān)聯(lián)和索引一直是MySQL常見的可優(yōu)化大塊兒,需要的朋友可以參考下

昨晚收到客服MM電話，一用戶反饋數(shù)據(jù)庫響應(yīng)非常慢，手機收到load異常報警，登上主機后發(fā)現(xiàn)大量sql執(zhí)行非常慢，有的執(zhí)行時間超過了10s

優(yōu)化點一：

1. SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10; 

表結(jié)構(gòu)為：

1. CREATE TABLE `game_shares_buy_list` ( 
2. `tran_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
3. `………..' 
4. PRIMARY KEY (`tran_id`), 
5. KEY `ind_username` (`username`) 
6. ) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8; 

執(zhí)行計劃：

1. root@127.0.0.1 : sitevipdb 09:10:22> explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10; 
2. +—-+————-+———————-+——-+—————+———+———+——+——+————-+ 
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
4. +—-+————-+———————-+——-+—————+———+———+——+——+————-+ 
5. | 1 | SIMPLE | game_shares_buy_list | index | NULL | PRIMARY | 4 | NULL | 10 | Using where | 
6. +—-+————-+———————-+——-+—————+———+———+——+——+————-+ 
7. 1 row in set (0.00 sec) 

分析該sql的執(zhí)行計劃，由于tran_id是表的主鍵，所以查詢根據(jù)主鍵降序順序掃描，這樣就可以不用排序，

然后在過濾條件price>2.00的記錄，看上去這個執(zhí)行計劃貌似非常好，如果查詢掃描到了滿足條件的10條記錄，就會停止掃描;

但是這里有個問題，如果表中有大量的記錄是不符合2.00的，意味查詢就需要掃描非常多的記錄，才能找到符合條件的10條：

1. root@127.0.0.1 : sitevipdb 09:17:23> select price,count(*) as cnt from `game_shares_buy_list` group by price order by cnt desc limit 10; 
2. +——-+——-+ 
3. | price | cnt | 
4. +——-+——-+ 
5. | 1.75 | 39101 | 
6. | 1.68 | 38477 | 
7. | 1.71 | 34869 | 
8. | 1.66 | 34849 | 
9. | 1.72 | 34718 | 
10. | 1.70 | 33996 | 
11. | 1.76 | 32527 | 
12. | 1.69 | 27189 | 
13. | 1.61 | 25694 | 
14. | 1.25 | 25450 | 

可以看到表中有大量的記錄不是2.00的，所以這個時候不能在根據(jù)主鍵順序掃描，在過濾記錄;

那么是否需要在price建立一個索引：

1. root@127.0.0.1 : sitevipdb 09:09:01> select count(*) from `game_shares_buy_list` where price>'2′; 
2. +———-+ 
3. | count(*) | 
4. +———-+ 
5. | 4087 | 
6. +———-+ 
7. root@127.0.0.1 : sitevipdb 09:17:31> select count(*) from `game_shares_buy_list` ; 
8. +———-+ 
9. | count(*) | 
10. +———-+ 
11. | 1572100 | 

從上面price的數(shù)據(jù)分布可以看出，price的分布相對還是比較集中的，如果在price建立索引，mysql也有可能認為由于需要回表的記錄過多，

同時需要額外的排序，而不選擇在price上的索引：

1. root@127.0.0.1 : sitevipdb 09:24:53> alter table game_shares_buy_list add index ind_game_shares_buy_list_price(price); 
2. Query OK, 0 rows affected (5.79 sec) 

可以看到優(yōu)化器雖然注意到了我們新加的索引，但是最終還是選擇了primary來掃描;

所以這個時候我們加上去的索引沒有產(chǎn)生效果，數(shù)據(jù)庫負載依然很高，如果強制走price上的索引，效果會這樣：

1. root@127.0.0.1 : sitevipdb 09:35:38> SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10; 
2. 。。。。。 
3. 10 rows in set (7.06 sec) 
4.  
5. root@127.0.0.1 : sitevipdb 09:36:00> SELECT * FROM `sitevipdb`.`game_shares_buy_list` force index(ind_game_shares_buy_list_price) WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10; 
6. 。。。。 
7. 10 rows in set (1.01 sec) 

可以看到如果強制走索引，時間已經(jīng)明顯下降了，但是還是有些慢，能不能在快一點?其實我們需要掃描的記錄只有10條，但查詢在取得這10條記錄的時候需要掃描大量無效的記錄

怎么降低這個數(shù)據(jù)：其實只要改寫一下sql就可以，我們先從索引中得到滿足條件的10個id，在回表進行關(guān)聯(lián)：

1. root@127.0.0.1 : sitevipdb 09:44:45> select * from game_shares_buy_list t1, 
2. -> ( SELECT tran_id FROM sitevipdb.game_shares_buy_list WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10) t2 
3. -> where t1.tran_id=t2.tran_id; 
4. 10 rows in set (0.00 sec) 

可以看到執(zhí)行時間已經(jīng)不在秒級別了，和客戶電話溝通后，很愿意這樣改寫sql。

—這里看到是order by tran_id是要額外排序的，索引也可以這樣來建立消除排序(tran_id,price)這樣可以消除排序，同時可以利用order by desc/asc +limit M,N的優(yōu)化。

優(yōu)化點二：

1. CREATE TABLE `game_session` ( 
2. `session_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT , 
3. `session_expires` int(10) unsigned NOT NULL DEFAULT '0′, 
4. `client_ip` varchar(16) DEFAULT NULL, 
5. `session_data` text, 
6. ……………………. 
7. PRIMARY KEY (`session_id`) 
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

查詢?yōu)閟elect `session_data`, `session_expires` from `game_session` where session_id='xxx'出現(xiàn)大量等待情況

同時該表的insert，也有等待的現(xiàn)象;

可以看到這個表結(jié)構(gòu)設(shè)計是有些問題的，咨詢了客戶后，可以改為下面結(jié)構(gòu)：

1. CREATE TABLE `game_session` ( 
2. id int auto_increment, 
3. `session_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT , 
4. `session_expires` int(10) unsigned NOT NULL DEFAULT '0′, 
5. `client_ip` varchar(16) DEFAULT NULL, 
6. `session_data` varchar(200), 
7. PRIMARY KEY (id), 
8. key ind_session_id(session_id,session_data, session_expires) 
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

小結(jié)：

新增自增主鍵id作為表的主鍵，這樣對插入的性能提升是很好的，同時也降低了表主鍵的大小;

將session_data由text改為了varchar(200),咨詢了客戶后，這個字段可以不用大字段存儲，同時有text改為了varchar，就可以冗余到索引中;

由于查詢可以使用覆蓋索引來完成，所以將查詢的3個字段冗余到索引中，查詢通過索引完成，不用回表