【討論中】關于Mysql索引使用問題求助

wangjj20

有一個表，有2276411條數(shù)據(jù)。索引如下：

1. root@phpcmsv9 10:25>SHOW INDEX FROM test;
   
2. +-----------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
   
3. | Table           | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
   
4. +-----------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
   
5. | test |          0 | PRIMARY                  |            1 | id          | A         |     2266380 |     NULL | NULL   |      | BTREE      |         |
   
6. | test |          0 | PRIMARY                  |            2 | downtime    | A         |     2266380 |     NULL | NULL   |      | BTREE      |         |
   
7. | test |          1 | userid                   |            1 | userid      | A         |       90655 |     NULL | NULL   | YES  | BTREE      |         |
   
8. | test |          1 | userid                   |            2 | catid       | A         |      755460 |     NULL | NULL   | YES  | BTREE      |         |
   
9. | test |          1 | userid                   |            3 | contentid   | A         |     2266380 |     NULL | NULL   | YES  | BTREE      |         |
   
10. | test |          1 | ucenteruid               |            1 | ucenteruid  | A         |      125910 |     NULL | NULL   |      | BTREE      |         |
    
11. | test |          1 | idx_contentid_catid_time |            1 | contentid   | A         |      103017 |     NULL | NULL   | YES  | BTREE      |         |
    
12. | test |          1 | idx_contentid_catid_time |            2 | catid       | A         |      103017 |     NULL | NULL   | YES  | BTREE      |         |
    
13. | test |          1 | idx_contentid_catid_time |            3 | downtime    | A         |     2266380 |     NULL | NULL   |      | BTREE      |         |
    
14. | test |          1 | idx_downtime_contentid   |            1 | downtime    | A         |     2266380 |     NULL | NULL   |      | BTREE      |         |
    
15. | test |          1 | idx_downtime_contentid   |            2 | contentid   | A         |     2266380 |     NULL | NULL   | YES  | BTREE      |         |
    
16. +-----------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

復制代碼

執(zhí)行如下sql：

1. EXPLAIN
   
2. SELECT SQL_NO_CACHE contentid,COUNT(contentid) AS downloadnum FROM  test WHERE modelid=14  AND catid IN(248,250,251,330,331,249,252)
   
3. AND downtime<=1355814270 AND downtime>=1355209470  GROUP BY contentid   ORDER BY downloadnum DESC,downtime DESC  LIMIT  0,5;
   
4. +----+-------------+-----------------+-------+------------------------+--------------------------+---------+------+---------+----------------------------------------------+
   
5. | id | select_type | table           | type  | possible_keys          | key                      | key_len | ref  | rows    | Extra                                        |
   
6. +----+-------------+-----------------+-------+------------------------+--------------------------+---------+------+---------+----------------------------------------------+
   
7. |  1 | SIMPLE      | test | index | idx_downtime_contentid | idx_contentid_catid_time | 14      | NULL | 2266380 | Using where; Using temporary; Using filesort |
   
8. +----+-------------+-----------------+-------+------------------------+--------------------------+---------+------+---------+----------------------------------------------+
   

復制代碼

這樣效率實在太低，執(zhí)行時間需要好幾秒。索引中的idx_downtime_contentid是為了優(yōu)化這條SQL加的。但是優(yōu)化器默認選擇了 idx_contentid_catid_time 這條索引。

force index(idx_downtime_contentid)這樣效率提高很多，但是有沒有辦法在不改動索引，不添加force index的情況下，能用到idx_downtime_contentid索引。

1. EXPLAIN
   
2. SELECT SQL_NO_CACHE contentid,COUNT(contentid) AS downloadnum FROM test FORCE INDEX(idx_downtime_contentid) WHERE modelid=14  AND catid IN(248,250,251,330,331,249,252)
   
3. AND downtime<=1355814270 AND downtime>=1355209470  GROUP BY contentid   ORDER BY downloadnum DESC,downtime DESC  LIMIT  0,5;
   
4. +----+-------------+-----------------+-------+------------------------+------------------------+---------+------+--------+----------------------------------------------+
   
5. | id | select_type | table           | type  | possible_keys          | key                    | key_len | ref  | rows   | Extra                                        |
   
6. +----+-------------+-----------------+-------+------------------------+------------------------+---------+------+--------+----------------------------------------------+
   
7. |  1 | SIMPLE      | test | range | idx_downtime_contentid | idx_downtime_contentid | 4       | NULL | 186136 | Using where; Using temporary; Using filesort |
   
8. +----+-------------+-----------------+-------+------------------------+------------------------+---------+------+--------+----------------------------------------------+

復制代碼

G8bao7

好像不行，這屬于優(yōu)化器自己選擇的，除了force index，其他好像沒有辦法控制。

在優(yōu)化器看來idx_downtime_contentid 沒有 idx_contentid_catid_time 這條索引效率高，至于是什么原因產生這個判斷結果，這個就比較深了，需要了解源碼、數(shù)據(jù)分布情況等

wangjj20

先感謝樓上的兄弟回復，但是個人感覺來說，mysql這個機制如果不能做些調整就太傻了。
另外測試在20W左右的數(shù)據(jù)中，優(yōu)化器會選擇idx_downtime_contentid這個索引來走。

G8bao7

客氣了，共同學習

優(yōu)化器在進行代價比較時是用估算的方式的進行的，這樣數(shù)據(jù)分布對最終結果影響很大，比較難控制。
可以參考大牛的ppt。http://vdisk.weibo.com/s/dqBwD

感覺如果開發(fā)人員對表中數(shù)據(jù)分布很了解的話，最好能用force index，可控性比較高，也省去了優(yōu)化器分析時間。

回復 3# wangjj20


   

cenalulu

回復 3# wangjj20

可以先optimize table 更新下數(shù)據(jù)字典信息，看看會不會有幫助。
如果仍然走不了預期的索引，那么一般解決優(yōu)化器弱智問題有三種做法：
1. 改寫SQL
2. force index
3. optimizer switch （5.5及以上版本）

樓主這個情況唯一能用的就是force index了。

devilkin0312

select a.contentid,count(a.contentid) as downloadnum from (select contentid,downloadnum,downtime from FROM  test WHERE modelid=14  AND catid IN(248,250,251,330,331,249,252)
AND downtime<=1355814270 AND downtime>=1355209470 ) a group by a.contentid order by a.downloadnum,a.downtime desc limit 5;

explain 看下

wangjj20

回復 6# devilkin0312


先感謝回復，這幾天出差，剛回來。

我測試了下，sql得寫成這樣才行。

1. EXPLAIN
   
2. SELECT * FROM (SELECT SQL_NO_CACHE contentid,COUNT(contentid) AS downloadnum FROM test WHERE modelid=14  AND catid IN(248,250,251,330,331,249,252)
   
3. AND downtime<=1355814270 AND downtime>=1355209470  GROUP BY contentid   ORDER BY downloadnum DESC,downtime DESC ) a  LIMIT  0,5

復制代碼

不然數(shù)據(jù)就不對了。

這樣走的索引就對了。謝謝啊！

另外我還測試了原來的sql,在去掉了LIMIT  0,5后能按照我需要的索引跑出數(shù)據(jù)，但是加上Limit 0,5后就不行。

devilkin0312

改的原因是：
1.先用條件取其中幾列數(shù)據(jù)以達到走對索引，減少數(shù)據(jù)集
2.對子查詢結果集再做group和sort 這樣效率和高些。
我看后面這個sql沒有變化呢，只是把limit 放到外面？

wangjj20 發(fā)表于 2012-12-25 09:37
回復 6# devilkin0312

wangjj20

回復 8# devilkin0312


因為表中是沒有downloadnum這個字段的，我開始測試的時候手動把downloadnum改成了COUNT(a.contentid) AS downloadnum，這樣子查詢結果就只有一條，所以我才改成那樣的。
后來檢查了一下，把downloadnum去掉也行。

謝謝��！