SQL语句优化

txwdhs · 发表于 2011-11-10 15:29

这个表的OWNUNO有70W左右的数据，按理说量不至于大到查询那么慢吧？而且我还是count（1）查的，只是记数，问题语句是这样的：SELECT COUNT(1) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = '361a47ca-666c-4374-bb37-733fb822aa7f' and REMOVESTATUS='n';
多了一个REMOVESTATUS的条件，里面只有Y和N，我觉得建不建索引貌似意义也不是太大

philip_zhong · 发表于 2011-11-10 15:35

你试下这个sql执行的时间是多少
SELECT COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = '361a47ca-666c-4374-bb37-733fb822aa7f' ，把执行计划也贴下

txwdhs · 发表于 2011-11-10 15:41

philip_zhong 发表于 2011-11-10 15:35
你试下这个sql执行的时间是多少
SELECT COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = '361 ...

mysql> explain SELECT COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = '361a47ca-666c-4374-bb37-733fb822aa7f';
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+--------------------------+
| id | select_type | table                | type | possible_keys       | key                   | key_len | ref | rows | Extra                   |
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE    | TIMELINE_ITEM_HOME_49 | ref  | IDX_TLI_HOME_49_UNIQUE | IDX_TLI_HOME_49_UNIQUE | 110    | const | 368071 | Using where; Using index |
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

下面是profile的结果

mysql> SHOW PROFILE for query 6;
+--------------------------------+----------+
| Status                      | Duration |
+--------------------------------+----------+
| starting                      | 0.000048 |
| checking query cache for query | 0.000044 |
| Opening tables                | 0.000015 |
| System lock                   | 0.000005 |
| Table lock                   | 0.000023 |
| init                         | 0.000020 |
| optimizing                   | 0.000020 |
| statistics                   | 0.000059 |
| preparing                   | 0.000014 |
| executing                   | 0.000007 |
| Sending data                | 0.297292 |
| end                         | 0.000021 |
| query end                   | 0.000005 |
| freeing items                | 0.000063 |
| storing result in query cache  | 0.000008 |
| logging slow query          | 0.000004 |
| cleaning up                   | 0.000006 |
+--------------------------------+----------+
17 rows in set (0.00 sec)

速度倒是不慢，可和之前的缓存是不是有关系呢？

philip_zhong · 发表于 2011-11-10 15:44

SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = '361a47ca-666c-4374-bb37-733fb822aa7f';
加上SQL_NO_CACHE你试下

txwdhs · 发表于 2011-11-10 15:49

philip_zhong 发表于 2011-11-10 15:44
SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = '361a47ca-666c-4374-bb ...

txwdhs · 发表于 2011-11-10 15:56

philip_zhong 发表于 2011-11-10 15:44
SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = '361a47ca-666c-4374-bb ...

换了一个新的表一个新的UNO，这个里面有60多W，下面是结果：

mysql> explain SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_57 WHERE OWNUNO = 'cbcac16b-9c97-4a8c-9a4e-c7aa50725df2' AND REMOVESTATUS='n';
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+-------------+
| id | select_type | table                | type | possible_keys       | key                   | key_len | ref | rows | Extra    |
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+-------------+
|  1 | SIMPLE    | TIMELINE_ITEM_HOME_57 | ref  | IDX_TLI_HOME_57_UNIQUE | IDX_TLI_HOME_57_UNIQUE | 110    | const | 547693 | Using where |
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+-------------+
1 row in set (0.51 sec)

mysql> explain SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_57 WHERE OWNUNO = 'cbcac16b-9c97-4a8c-9a4e-c7aa50725df2';
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+--------------------------+
| id | select_type | table                | type | possible_keys       | key                   | key_len | ref | rows | Extra                   |
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE    | TIMELINE_ITEM_HOME_57 | ref  | IDX_TLI_HOME_57_UNIQUE | IDX_TLI_HOME_57_UNIQUE | 110    | const | 547693 | Using where; Using index |
+----+-------------+-----------------------+------+------------------------+------------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_57 WHERE OWNUNO = 'cbcac16b-9c97-4a8c-9a4e-c7aa50725df2' AND REMOVESTATUS='n';
+-----------------+
| COUNT(DIRECTID) |
+-----------------+
|       644389 |
+-----------------+
1 row in set (1 min 57.07 sec)

下面是profile的结果：

mysql> SHOW PROFILE for query 33;
+--------------------+------------+
| Status          | Duration |
+--------------------+------------+
| starting          | 0.000079 |
| Opening tables    | 0.000022 |
| System lock       | 0.000006 |
| Table lock       | 0.000009 |
| init             | 0.000021 |
| optimizing       | 0.000013 |
| statistics       | 0.000069 |
| preparing       | 0.000014 |
| executing       | 0.000006 |
| Sending data    | 117.093829 |
| end             | 0.000020 |
| query end       | 0.000006 |
| freeing items    | 0.000064 |
| logging slow query | 0.000005 |
| logging slow query | 0.000037 |
| cleaning up       | 0.000099 |
+--------------------+------------+
16 rows in set (0.00 sec)

这次更慢，愁死我了。。。

philip_zhong · 发表于 2011-11-10 16:03

本帖最后由 philip_zhong 于 2011-11-10 16:04 编辑

看了下你第一次的SQL，两次的执行计划是一致的，因此count的里面的列是没有关系的，但是你真实的SQL执行是加上and REMOVESTATUS='n'，如果你不加索引区别就很大了
区别在于：
1.需要获取REMOVESTATUS的数据，那么意味着要访问PK索引获取数据，增加了PK索引树和数据的访问。
2.然后根据获取的数据进行filter。

建议重新建一个新的索引是OWNUNO+REMOVESTATUS,可以把测试的结果再贴下。

txwdhs · 发表于 2011-11-10 16:19

philip_zhong 发表于 2011-11-10 16:03
看了下你第一次的SQL，两次的执行计划是一致的，因此count的里面的列是没有关系的，但是你真实的SQL执行是加 ...

效果貌似好多了，结果如下：

mysql> explain SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_74 WHERE OWNUNO = '62305ea1-6ba7-4fd1-98cb-36e2984c34f9' AND REMOVESTATUS='n';
+----+-------------+-----------------------+------+--------------------------------------+---------------+---------+-------+--------+-------------+
| id | select_type | table                | type | possible_keys                      | key          | key_len | ref | rows | Extra    |
+----+-------------+-----------------------+------+--------------------------------------+---------------+---------+-------+--------+-------------+
|  1 | SIMPLE    | TIMELINE_ITEM_HOME_74 | ref  | IDX_TLI_HOME_74_UNIQUE,IDX_74_OWNUNO | IDX_74_OWNUNO | 110    | const | 170496 | Using where |
+----+-------------+-----------------------+------+--------------------------------------+---------------+---------+-------+--------+-------------+
1 row in set (0.10 sec)

mysql> SELECT SQL_NO_CACHE COUNT(DIRECTID) FROM TIMELINE_ITEM_HOME_74 WHERE OWNUNO = '62305ea1-6ba7-4fd1-98cb-36e2984c34f9' AND REMOVESTATUS='n';
+-----------------+
| COUNT(DIRECTID) |
+-----------------+
|       668564 |
+-----------------+
1 row in set (1.94 sec)

profile的结果：

mysql> SHOW PROFILE for query 40;
+--------------------+----------+
| Status          | Duration |
+--------------------+----------+
| starting          | 0.000098 |
| Opening tables    | 0.000086 |
| System lock       | 0.000007 |
| Table lock       | 0.000010 |
| init             | 0.000021 |
| optimizing       | 0.000014 |
| statistics       | 0.000081 |
| preparing       | 0.000015 |
| executing       | 0.000007 |
| Sending data    | 1.940094 |
| end             | 0.000022 |
| query end       | 0.000006 |
| freeing items    | 0.000142 |
| logging slow query | 0.000006 |
| logging slow query | 0.000042 |
| cleaning up       | 0.000007 |
+--------------------+----------+
16 rows in set (0.00 sec)

不知道66W级别的数据，1.9秒的速度算正常吗？是不是还是有些慢呢？

justlooks · 发表于 2011-11-10 16:21

本帖最后由 justlooks 于 2011-11-10 16:23 编辑

你这个查询只是用到了原来索引的一部分，那么另外的数据需要从cluster index中找，而因为OWNUNO列的聚集度比较低，那么对应的cluser index的叶节点需要加载比较多，所以会比较慢把where后的条件都包括在secondary index里，不用2次加载cluster index速度会快很多

txwdhs · 发表于 2011-11-10 16:26

justlooks 发表于 2011-11-10 16:21
你这个查询只是用到了原来索引的一部分，那么另外的数据需要从cluster index中找，而因为OWNUNO列的聚集度比 ...

可是那个REMOVESTATUS里只有Y和N，聚集度比较高，加这样一个索引按理说不会太有效吧？