当前位置:首页 > 开发 > IT生活 > 正文

MySQL 优化实例---添加索引

发表于: 2013-06-28   作者:coollyj   来源:转载   浏览次数:
摘要: http://chlotte.blog.51cto.com/318402/361132 近来webgame在维护后起动的速度越来越慢,竞然超过了1个小时30分钟,以前一直以为是数据量大的缘故,清理了无效的数据之后,速度没有任何改变,执行show full processlist发现异常: mysql> show full processlist\G; *************
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 |   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)





查看表结构发现,只有主键索引:
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)
.

给userId字段添加索引:
mysql> create index Tongtian_userId on TongTianRecord(userId);
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)


见识了索引的威力,现在只需: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)


经过猫兄的帮助,查时间只需要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

优化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)

MySQL 优化实例---添加索引

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下
索引的类型 : Ø 普通索引  这是最基本的索引类型,没唯一性之类的限制。 Ø 唯一性索引 和普通索
写 在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下
写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下
写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下
写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html 写在前面:索引对查询的速度有着
写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下
写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下
理解MySQL——索引与优化 写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号