MySQL 优化实例---添加索引
http://chlotte.blog.51cto.com/318402/361132

近来webgame在维护后起动的速度越来越慢，竞然超过了1个小时30分钟，以前一直以为是数据量大的缘故，清理了无效的数据之后，速度没有任何改变，执行show full processlist发现异常：

mysql> show full processlist\G;
*************************** 14. row ***************************
     Id: 16
   User: programs
   Host: localhost:53912
     db: sword
Command: Query
   Time: 1843
  State: Sending data
   Info: SELECT userId,leaveDttm, costTime, raid as layer FROM TongTianRecord as a  WHERE times != 0 and raid!=0 and raid = (select max(raid) from TongTianRecord as b where a.userId=b.userId ) group by userId order by layer Desc , leaveDttm asc limit 50
*************************** 15. row ***************************


查了一下，表将近10W条记录
mysql> select count(*) from TongTianRecord;
+----------+
| count(*) |
+----------+
|    99090 |
+----------+
1 row in set (0.03 sec)


手工执行了这条异常语句，速度惊人，执行这条语句花费了近43分钟，这是什么概念呀
mysql>  SELECT userId,leaveDttm, costTime, raid as layer FROM TongTianRecord as a  WHERE times != 0 and raid!=0 and raid = (select max(raid) from TongTianRecord as b where a.userId=b.userId ) group by userId order by layer Desc , leaveDttm asc limit 50
    -> ;
+--------+---------------------+----------+-------+
| userId | leaveDttm           | costTime | layer |
+--------+---------------------+----------+-------+
| 432676 | 2010-03-03 20:44:25 |    47271 |   142 |
| 437123 | 2010-03-07 19:40:43 |    38798 |   142 |
| 385063 | 2010-03-02 19:05:52 |    14772 |   140 |
| 370529 | 2010-03-11 10:00:40 |    68756 |   140 |
| 416174 | 2010-05-22 10:03:24 |    72971 |   133 |
| 385938 | 2010-06-07 13:47:52 |    28274 |   129 |
| 442027 | 2010-06-09 11:08:35 |    48048 |   128 |
| 107397 | 2010-01-09 23:58:35 |     9954 |   123 |
| 129437 | 2010-01-17 08:13:40 |    41907 |   123 |
| 227342 | 2010-02-17 12:19:09 |     6170 |   123 |
| 441531 | 2010-03-20 12:52:47 |     5901 |   123 |
| 180382 | 2010-04-07 21:53:42 |    19133 |   123 |
| 212991 | 2010-06-20 08:48:32 |    57859 |   123 |
| 252337 | 2010-07-23 00:41:53 |     4867 |   123 |
| 216937 | 2010-01-07 06:27:14 |    24580 |   122 |
|  73227 | 2010-02-05 18:09:50 |     8336 |   122 |
| 187937 | 2010-03-18 23:55:45 |     7375 |   122 |
| 220040 | 2010-03-20 13:48:14 |    45294 |   122 |
| 185100 | 2010-05-06 02:34:09 |    13080 |   122 |
| 187953 | 2010-05-11 20:54:49 |     3571 |   122 |
| 118332 | 2010-05-20 19:19:37 |     9057 |   122 |
| 303014 | 2010-06-06 15:35:23 |     9638 |   122 |
| 218924 | 2010-07-14 19:53:41 |    93286 |   122 |
| 218689 | 2010-01-03 00:00:09 |     2999 |   121 |
| 245938 | 2010-01-17 19:39:56 |    13599 |   121 |
| 425601 | 2010-05-18 17:11:56 |     5007 |   121 |
| 217315 | 2010-05-22 09:33:52 |    45245 |   121 |
| 368088 | 2010-01-12 20:10:09 |