MySQL 優化例項
近來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 ***************************
*************************** 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)
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 | 182014 | 120 |
| 125729 | 2010-01-19 22:25:11 | 2245 | 120 |
| 354352 | 2010-02-13 20:39:18 | 28959 | 120 |
| 126128 | 2010-03-21 18:00:46 | 2585 | 120 |
| 423356 | 2010-05-02 15:26:07 | 3718 | 120 |
| 408878 | 2010-05-22 11:43:47 | 3896 | 120 |
| 276298 | 2010-06-05 17:38:37 | 15662 | 120 |
| 406735 | 2010-06-14 00:36:41 | 9331 | 114 |
| 398012 | 2010-01-08 15:56:49 | 3703 | 103 |
| 238642 | 2010-01-12 18:36:37 | 18024 | 103 |
| 391747 | 2010-03-05 09:54:07 | 31989 | 103 |
| 393066 | 2010-03-10 17:37:59 | 11688 | 103 |
| 188250 | 2010-04-09 22:28:46 | 11168 | 103 |
| 421844 | 2010-05-13 09:13:20 | 32757 | 103 |
| 188049 | 2010-05-18 19:37:45 | 12703 | 103 |
| 403485 | 2010-05-23 16:20:20 | 8884 | 103 |
| 405829 | 2010-06-04 17:21:31 | 9288 | 103 |
| 432198 | 2010-06-07 06:26:58 | 21719 | 103 |
| 386609 | 2010-06-20 20:40:16 | 4943 | 103 |
| 447321 | 2010-07-04 08:18:14 | 56203 | 103 |
| 250245 | 2010-01-14 07:36:51 | 27718 | 102 |
| 255427 | 2010-02-01 17:25:28 | 15714 | 102 |
| 249995 | 2010-02-01 17:26:40 | 14328 | 102 |
+--------+---------------------+----------+-------+
50 rows in set (42 min 38.90 sec)
-> ;
+--------+---------------------+----------+-------+
| 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 | 182014 | 120 |
| 125729 | 2010-01-19 22:25:11 | 2245 | 120 |
| 354352 | 2010-02-13 20:39:18 | 28959 | 120 |
| 126128 | 2010-03-21 18:00:46 | 2585 | 120 |
| 423356 | 2010-05-02 15:26:07 | 3718 | 120 |
| 408878 | 2010-05-22 11:43:47 | 3896 | 120 |
| 276298 | 2010-06-05 17:38:37 | 15662 | 120 |
| 406735 | 2010-06-14 00:36:41 | 9331 | 114 |
| 398012 | 2010-01-08 15:56:49 | 3703 | 103 |
| 238642 | 2010-01-12 18:36:37 | 18024 | 103 |
| 391747 | 2010-03-05 09:54:07 | 31989 | 103 |
| 393066 | 2010-03-10 17:37:59 | 11688 | 103 |
| 188250 | 2010-04-09 22:28:46 | 11168 | 103 |
| 421844 | 2010-05-13 09:13:20 | 32757 | 103 |
| 188049 | 2010-05-18 19:37:45 | 12703 | 103 |
| 403485 | 2010-05-23 16:20:20 | 8884 | 103 |
| 405829 | 2010-06-04 17:21:31 | 9288 | 103 |
| 432198 | 2010-06-07 06:26:58 | 21719 | 103 |
| 386609 | 2010-06-20 20:40:16 | 4943 | 103 |
| 447321 | 2010-07-04 08:18:14 | 56203 | 103 |
| 250245 | 2010-01-14 07:36:51 | 27718 | 102 |
| 255427 | 2010-02-01 17:25:28 | 15714 | 102 |
| 249995 | 2010-02-01 17:26:40 | 14328 | 102 |
+--------+---------------------+----------+-------+
50 rows in set (42 min 38.90 sec)
執行explain發現，都是全表掃描：
mysql> explain 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
-> ;
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 99420 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | b | ALL | NULL | NULL | NULL | NULL | 99420 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
-> ;
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 99420 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | b | ALL | NULL | NULL | NULL | NULL | 99420 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
檢視錶結構發現，只有主鍵索引：
mysql> show index from TongTianRecord;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TongTianRecord | 0 | PRIMARY | 1 | id | A | 99420 | NULL | NULL | | BTREE | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TongTianRecord | 0 | PRIMARY | 1 | id | A | 99420 | NULL | NULL | | BTREE | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
.
給userId欄位新增索引：
mysql> create index Tongtian_userId on TongTianRecord(userId);
Query OK, 8408 rows affected (0.17 sec)
Records: 8408 Duplicates: 0 Warnings: 0
Query OK, 8408 rows affected (0.17 sec)
Records: 8408 Duplicates: 0 Warnings: 0
再次執行explain：
mysql> explain 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
-> ;
+----+--------------------+-------+-------+-----------------+-----------------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+-----------------+-----------------+---------+-------------+------+----------------------------------------------+
| 1 | PRIMARY | a | index | NULL | Tongtian_userId | 4 | NULL | 1900 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | b | ref | Tongtian_userId | Tongtian_userId | 4 | xx.a.userId | 38 | |
+----+--------------------+-------+-------+-----------------+-----------------+---------+-------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
-> ;
+----+--------------------+-------+-------+-----------------+-----------------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+-----------------+-----------------+---------+-------------+------+----------------------------------------------+
| 1 | PRIMARY | a | index | NULL | Tongtian_userId | 4 | NULL | 1900 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | b | ref | Tongtian_userId | Tongtian_userId | 4 | xx.a.userId | 38 | |
+----+--------------------+-------+-------+-----------------+-----------------+---------+-------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
見識了索引的威力，現在只需：35秒，速度比原來提高了70多倍
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 | 182014 | 120 |
| 125729 | 2010-01-19 22:25:11 | 2245 | 120 |
| 354352 | 2010-02-13 20:39:18 | 28959 | 120 |
| 126128 | 2010-03-21 18:00:46 | 2585 | 120 |
| 423356 | 2010-05-02 15:26:07 | 3718 | 120 |
| 408878 | 2010-05-22 11:43:47 | 3896 | 120 |
| 276298 | 2010-06-05 17:38:37 | 15662 | 120 |
| 406735 | 2010-06-14 00:36:41 | 9331 | 114 |
| 398012 | 2010-01-08 15:56:49 | 3703 | 103 |
| 238642 | 2010-01-12 18:36:37 | 18024 | 103 |
| 391747 | 2010-03-05 09:54:07 | 31989 | 103 |
| 393066 | 2010-03-10 17:37:59 | 11688 | 103 |
| 188250 | 2010-04-09 22:28:46 | 11168 | 103 |
| 421844 | 2010-05-13 09:13:20 | 32757 | 103 |
| 188049 | 2010-05-18 19:37:45 | 12703 | 103 |
| 403485 | 2010-05-23 16:20:20 | 8884 | 103 |
| 405829 | 2010-06-04 17:21:31 | 9288 | 103 |
| 432198 | 2010-06-07 06:26:58 | 21719 | 103 |
| 386609 | 2010-06-20 20:40:16 | 4943 | 103 |
| 447321 | 2010-07-04 08:18:14 | 56203 | 103 |
| 250245 | 2010-01-14 07:36:51 | 27718 | 102 |
| 255427 | 2010-02-01 17:25:28 | 15714 | 102 |
| 249995 | 2010-02-01 17:26:40 | 14328 | 102 |
+--------+---------------------+----------+-------+
50 rows in set (34.97 sec)
-> ;
+--------+---------------------+----------+-------+
| 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 | 182014 | 120 |
| 125729 | 2010-01-19 22:25:11 | 2245 | 120 |
| 354352 | 2010-02-13 20:39:18 | 28959 | 120 |
| 126128 | 2010-03-21 18:00:46 | 2585 | 120 |
| 423356 | 2010-05-02 15:26:07 | 3718 | 120 |
| 408878 | 2010-05-22 11:43:47 | 3896 | 120 |
| 276298 | 2010-06-05 17:38:37 | 15662 | 120 |
| 406735 | 2010-06-14 00:36:41 | 9331 | 114 |
| 398012 | 2010-01-08 15:56:49 | 3703 | 103 |
| 238642 | 2010-01-12 18:36:37 | 18024 | 103 |
| 391747 | 2010-03-05 09:54:07 | 31989 | 103 |
| 393066 | 2010-03-10 17:37:59 | 11688 | 103 |
| 188250 | 2010-04-09 22:28:46 | 11168 | 103 |
| 421844 | 2010-05-13 09:13:20 | 32757 | 103 |
| 188049 | 2010-05-18 19:37:45 | 12703 | 103 |
| 403485 | 2010-05-23 16:20:20 | 8884 | 103 |
| 405829 | 2010-06-04 17:21:31 | 9288 | 103 |
| 432198 | 2010-06-07 06:26:58 | 21719 | 103 |
| 386609 | 2010-06-20 20:40:16 | 4943 | 103 |
| 447321 | 2010-07-04 08:18:14 | 56203 | 103 |
| 250245 | 2010-01-14 07:36:51 | 27718 | 102 |
| 255427 | 2010-02-01 17:25:28 | 15714 | 102 |
| 249995 | 2010-02-01 17:26:40 | 14328 | 102 |
+--------+---------------------+----------+-------+
50 rows in set (34.97 sec)
經過貓兄的幫助，查時間只需要0.05秒，步驟如下:
加索引：
mysql> create index Tongtian_userId on TongTianRecord(userId,raid);
Query OK, 98997 rows affected (1.08 sec)
Records: 98997 Duplicates: 0 Warnings: 0
Query OK, 98997 rows affected (1.08 sec)
Records: 98997 Duplicates: 0 Warnings: 0
優化SQL：
mysql> select a.userId,a.leaveDttm, a.costTime, a.raid as layer from TongTianRecord a inner join (select userId,max(raid)as level from TongTianRecord where raid>0 group by userId) b on a.raid=b.level and 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 | 182014 | 120 |
| 125729 | 2010-01-19 22:25:11 | 2245 | 120 |
| 354352 | 2010-02-13 20:39:18 | 28959 | 120 |
| 126128 | 2010-03-21 18:00:46 | 2585 | 120 |
| 423356 | 2010-05-02 15:26:07 | 3718 | 120 |
| 408878 | 2010-05-22 11:43:47 | 3896 | 120 |
| 276298 | 2010-06-05 17:38:37 | 15662 | 120 |
| 406735 | 2010-06-14 00:36:41 | 9331 | 114 |
| 398012 | 2010-01-08 15:56:49 | 3703 | 103 |
| 238642 | 2010-01-12 18:36:37 | 18024 | 103 |
| 391747 | 2010-03-05 09:54:07 | 31989 | 103 |
| 393066 | 2010-03-10 17:37:59 | 11688 | 103 |
| 188250 | 2010-04-09 22:28:46 | 11168 | 103 |
| 421844 | 2010-05-13 09:13:20 | 32757 | 103 |
| 188049 | 2010-05-18 19:37:45 | 12703 | 103 |
| 403485 | 2010-05-23 16:20:20 | 8884 | 103 |
| 405829 | 2010-06-04 17:21:31 | 9288 | 103 |
| 432198 | 2010-06-07 06:26:58 | 21719 | 103 |
| 386609 | 2010-06-20 20:40:16 | 4943 | 103 |
| 447321 | 2010-07-04 08:18:14 | 56203 | 103 |
| 250245 | 2010-01-14 07:36:51 | 27718 | 102 |
| 255427 | 2010-02-01 17:25:28 | 15714 | 102 |
| 249995 | 2010-02-01 17:26:40 | 14328 | 102 |
+--------+---------------------+----------+-------+
50 rows in set (0.05 sec)
+--------+---------------------+----------+-------+
| 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 | 182014 | 120 |
| 125729 | 2010-01-19 22:25:11 | 2245 | 120 |
| 354352 | 2010-02-13 20:39:18 | 28959 | 120 |
| 126128 | 2010-03-21 18:00:46 | 2585 | 120 |
| 423356 | 2010-05-02 15:26:07 | 3718 | 120 |
| 408878 | 2010-05-22 11:43:47 | 3896 | 120 |
| 276298 | 2010-06-05 17:38:37 | 15662 | 120 |
| 406735 | 2010-06-14 00:36:41 | 9331 | 114 |
| 398012 | 2010-01-08 15:56:49 | 3703 | 103 |
| 238642 | 2010-01-12 18:36:37 | 18024 | 103 |
| 391747 | 2010-03-05 09:54:07 | 31989 | 103 |
| 393066 | 2010-03-10 17:37:59 | 11688 | 103 |
| 188250 | 2010-04-09 22:28:46 | 11168 | 103 |
| 421844 | 2010-05-13 09:13:20 | 32757 | 103 |
| 188049 | 2010-05-18 19:37:45 | 12703 | 103 |
| 403485 | 2010-05-23 16:20:20 | 8884 | 103 |
| 405829 | 2010-06-04 17:21:31 | 9288 | 103 |
| 432198 | 2010-06-07 06:26:58 | 21719 | 103 |
| 386609 | 2010-06-20 20:40:16 | 4943 | 103 |
| 447321 | 2010-07-04 08:18:14 | 56203 | 103 |
| 250245 | 2010-01-14 07:36:51 | 27718 | 102 |
| 255427 | 2010-02-01 17:25:28 | 15714 | 102 |
| 249995 | 2010-02-01 17:26:40 | 14328 | 102 |
+--------+---------------------+----------+-------+
50 rows in set (0.05 sec)
本文轉自 trt2008 51CTO部落格，原文連結：http://blog.51cto.com/chlotte/361132，如需轉載請自行聯絡原作者
相關文章
- (轉)MySQL優化例項MySql優化
- (轉)例項分析：MySQL優化經驗MySql優化
- SQL優化例項SQL優化
- DeviceMotionEvent程式碼優化例項dev優化
- SQL優化例項-思路分析SQL優化
- SQL開發例項和優化SQL優化
- HP UNIX系統優化例項優化
- 分享一個SQLite 效能優化例項SQLite優化
- C# Winform程式介面優化例項C#ORM優化
- java多型-優化上個例項Java多型優化
- 優化 WebView 的載入速度例項優化WebView
- 無線頁面動畫優化例項動畫優化
- css優化文字顯示效果程式碼例項CSS優化
- 【前端構建】WebPack例項與前端效能優化前端Web優化
- php例項化物件的例項方法PHP物件
- JavaScript 前端效能優化小竅門例項彙總JavaScript前端優化
- 效能優化---（.net）規範例項（canonical instance）優化
- 如何讀懂火焰圖？+ 例項講解程式效能優化優化
- Laravel kernel例項化Laravel
- Java--例項化Java
- mysql優化MySql優化
- Mysql 優化MySql優化
- (mysql優化-3) 系統優化MySql優化
- MySQL 效能優化之索引優化MySql優化索引
- MySQL優化-安裝配置優化MySql優化
- MySQL 效能優化之SQL優化MySql優化
- oracle優化一例之sql優化Oracle優化SQL
- Django+MySQL 例項入門DjangoMySql
- mysql 5.7單例項安裝MySql單例
- c++ 連線mysql例項C++MySql
- Grails國際化例項AI
- oracle例項最佳化Oracle
- MySQL(二) MySql常用優化MySql優化
- 【MySQL】淺談MySQL優化MySql優化
- mysql效能優化MySql優化
- MySQL優化面試MySql優化面試
- mysql優化（一）MySql優化
- mysql order by 優化MySql優化