昨晚收到客服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` ;
| 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，也有等待的現象；
可以看到這個表結構設計是有些問題的，咨詢了客戶後，可以改為下面結構：
id int auto_increment,
`session_id` varchar(30) character set utf8 collate utf8_bin not null default ,
`session_data` varchar(200),
primary key (id),
key ind_session_id(session_id,session_data, session_expires)
-新增自增主鍵id作為表的主鍵，這樣對插入的性能提升是很好的，同時也降低了表主鍵的大小；
-将session_data由text改為了varchar(200),咨詢了客戶後，這個字段可以不用大字段存儲，同時有text改為了varchar，就可以備援到索引中；
由于查詢可以使用覆寫索引來完成，是以将查詢的3個字段備援到索引中，查詢通過索引完成，不用回表；