MySQL的id聯系關系和索引應用的現實優化案例。本站提示廣大學習愛好者：（MySQL的id聯系關系和索引應用的現實優化案例）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL的id聯系關系和索引應用的現實優化案例正文
昨晚收到客服MM德律風，一用戶反應數據庫呼應異常慢，手機收到load異常報警，登上主機後發明年夜量sql履行異常慢，有的履行時光跨越了10s
優化點一：
SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;
表構造為：
CREATE TABLE `game_shares_buy_list` ( `tran_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `………..' PRIMARY KEY (`tran_id`), KEY `ind_username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8;
履行籌劃：
[email protected] : sitevipdb 09:10:22> explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10; +—-+————-+———————-+——-+—————+———+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———————-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | game_shares_buy_list | index | NULL | PRIMARY | 4 | NULL | 10 | Using where | +—-+————-+———————-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec)
剖析該sql的履行籌劃，因為tran_id是表的主鍵，所以查詢依據主鍵降序次序掃描，如許便可以不消排序，
然後在過濾前提price>2.00的記載，看上去這個履行籌劃貌似異常好，假如查詢掃描到了知足前提的10筆記錄，就會停滯掃描；
然則這裡有個成績，假如表中有年夜量的記載是不相符2.00的，意味查詢就須要掃描異常多的記載，能力找到相符前提的10條：
[email protected] : sitevipdb 09:17:23> select price,count(*) as cnt from `game_shares_buy_list` group by price order by cnt desc limit 10; +——-+——-+ | price | cnt | +——-+——-+ | 1.75 | 39101 | | 1.68 | 38477 | | 1.71 | 34869 | | 1.66 | 34849 | | 1.72 | 34718 | | 1.70 | 33996 | | 1.76 | 32527 | | 1.69 | 27189 | | 1.61 | 25694 | | 1.25 | 25450 |
可以看到表中有年夜量的記載不是2.00的，所以這個時刻不克不及在依據主鍵次序掃描，在過濾記載；
那末能否須要在price樹立一個索引：
[email protected] : sitevipdb 09:09:01> select count(*) from `game_shares_buy_list` where price>'2′; +———-+ | count(*) | +———-+ | 4087 | +———-+ [email protected] : sitevipdb 09:17:31> select count(*) from `game_shares_buy_list` ; +———-+ | count(*) | +———-+ | 1572100 |
從下面price的數據散布可以看出，price的散布絕對照樣比擬集中的，假如在price樹立索引，mysql也有能夠以為因為須要回表的記載過量，
同時須要額定的排序，而不選擇在price上的索引：
[email protected] : sitevipdb 09:24:53> alter table game_shares_buy_list add index ind_game_shares_buy_list_price(price); Query OK, 0 rows affected (5.79 sec)
可以看到優化器固然留意到了我們新加的索引，然則終究照樣選擇了primary來掃描；
所以這個時刻我們加上去的索引沒有發生後果，數據庫負載仍然很高，假如強迫走price上的索引，後果會如許：
[email protected] : sitevipdb 09:35:38> SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10; 。。。。。 10 rows in set (7.06 sec) [email protected] : 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; 。。。。 10 rows in set (1.01 sec)
可以看到假如強迫走索引，時光曾經顯著降低了，然則照樣有些慢，能不克不及在快一點？其實我們須要掃描的記載只要10條，但查詢在獲得這10筆記錄的時刻須要掃描年夜量有效的記載
怎樣下降這個數據：其實只需改寫一下sql便可以，我們先從索引中獲得知足前提的10個id，在回表停止聯系關系：
[email protected] : sitevipdb 09:44:45> select * from game_shares_buy_list t1, -> ( SELECT tran_id FROM sitevipdb.game_shares_buy_list WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10) t2 -> where t1.tran_id=t2.tran_id; 10 rows in set (0.00 sec)
可以看到履行時光曾經不在秒級別了，和客戶德律風溝通後，很情願如許改寫sql。
—這裡看到是order by tran_id是要額定排序的，索引也能夠如許來樹立清除排序(tran_id,price)如許可以清除排序，同時可以應用order by desc/asc +limit M,N的優化。
優化點二：
CREATE TABLE `game_session` ( `session_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT , `session_expires` int(10) unsigned NOT NULL DEFAULT '0′, `client_ip` varchar(16) DEFAULT NULL, `session_data` text, ……………………. PRIMARY KEY (`session_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
查詢為select `session_data`, `session_expires` from `game_session` where session_id='xxx'湧現年夜量期待情形
同時該表的insert，也有期待的景象；
可以看到這個表構造設計是有些成績的，征詢了客戶後，可以改成上面構造：
CREATE TABLE `game_session` ( id int auto_increment, `session_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT , `session_expires` int(10) unsigned NOT NULL DEFAULT '0′, `client_ip` varchar(16) DEFAULT NULL, `session_data` varchar(200), PRIMARY KEY (id), key ind_session_id(session_id,session_data, session_expires) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
小結：
