MySQL的加入和排序慢 [英] MySQL Slow on join and order by

问题描述

我有一个缓慢的查询，该查询连接了4个表并按顺序排列.需要30秒.

我在GuruList.GuruName，GuruList.id，GuruName，Stocks.TickerName，Stocks.exchange，triple_insider.symbol，triple_insider.exchange，triple_insider.date上都有索引

这是查询:

  SELECT DISTINCT stock_list.symbol作为t，stock_list.exchange，REPLACE(stock_list.company，''，'')as c，REPLACE(triple_insider.position，''，'')作为p，Triple_insider.date为d，Triple_insider.name为n，Triple_insider.type为y，Triple_insider.trans_share为r，Triple_insider.cost为cs，Triple_insider.price为z，stock_list.price为x，ROUND(100 *(stock_list.price-triple_insider.price)/triple_insider.price，1)作为h来自Triple_insider左加入stock_list在Triple_insider.symbol = stock_list.symbol上AND Triple_insider.exchange = stock_list.exchange左加入股票ON Triple_insider.symbol = Stocks.TickerNameAND Triple_insider.exchange =股票.交易所左联接GuruList在Stocks.GuruName = GuruList.GuruName上在哪里stock_list.price>0AND stock_list.mktcap> = 100AND stock_list.rank_balancesheet/10> = 5AND stock_list.volume！= 0AND stock_list.volume> = 200000AND stock_list.price> = 2AND stock_list.price< = 10AND stock_list.shares！= 0AND stock_list.shares< = 500AND stock_list.p_pct_change！= 0AND stock_list.p_pct_change> = 2AND stock_list.cash2debt> 0AND stock_list.cash2debt> = 0.1AND stock_list.equity2asset> 0AND stock_list.equity2asset> = 0.1AND stock_list.fscore！= 0AND stock_list.fscore> = 1AND stock_list.zscore！= 0AND stock_list.zscore> = 0AND stock_list.medpsvalue>0AND stock_list.p2medpsvalue< = 0.7AND stock_list.p2iv_dcf_share< = 0.5AND GuruList.id IN(0,155,88,54,11,47,112,84,3,20,22,114,67,40,102,164,50,64,108,163)AND stock_list.exchange IN('NAS'，'NYSE'，'OTCPK'，''，'OTCBB'，'AMEX')ORDER BY Triple_insider.date DESC极限5001 

---

这是说明:

  ----------------------------------------------- +|id |select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|+ ---- + ------------- + ---------------- + -------- + ------------------------------------------------------------------------------------ + ---------- + ---------+ --------------------------------------------------------------------------- + ------ + ---------------------------------------------- +|1 |简单GuruList |范围|PRIMARY，GuruList_GuruName，GuruList_id，GuruName |主要|4 |NULL |20 |在哪里使用；使用临时的；使用文件排序||1 |简单库存参考|GuruName，TickerName，exchange，exchange_2 |GuruName |43 |g_main.GuruList.GuruName |1490 |在哪里使用|1 |简单Triple_insider |参考|Triple_insider_symbol_index，交换|交换|22 |g_main.Stocks.exchange，g_main.Stocks.TickerName |56 |在哪里使用|1 |简单stock_list |eq_ref |主要，股票清单价格，股票清单价格，股票fscore，股票fzcore，符号，交易所|主要|22 |g_main.triple_insider.symbol，g_main.triple_insider.exchange |1 |在哪里使用+ ---- + ------------- + ---------------- + -------- + ------------------------------------------------------------------------------------ + ---------- + ---------+ --------------------------------------------------------------------------- + ------ + ---------------------------------------------- +设置4行(0.00秒) 

---

解决方案

在 triple_insider.date 上创建索引.

您可能还希望摆脱 LEFT JOIN 或将除 triple_insider 之外的所有内容的条件移至相应的 ON 子句./p>

DISTINCT ，尽管其本身是合法的构造，但似乎是解决您在帖子中未描述的问题的错误方法.

I have a slow query which join 4 tables and with order by. It will take +30 Seconds.

I have index on GuruList.GuruName, GuruList.id, GuruName , Stocks.TickerName , Stocks.exchange, triple_insider.symbol, triple_insider.exchange, triple_insider.date

This is the query :

SELECT DISTINCT stock_list.symbol as t, 
                stock_list.exchange,
                REPLACE(stock_list.company, '    ', ' ') as c,
                REPLACE(triple_insider.position, '    ', ' ') as p,
                triple_insider.date as d,
                triple_insider.name as n,
                triple_insider.type as y,
                triple_insider.trans_share as r,
                triple_insider.cost as cs,
                triple_insider.price as z,
                stock_list.price as x,
                ROUND(100*(stock_list.price-triple_insider.price)/triple_insider.price, 1) as h
    FROM  triple_insider 
        LEFT JOIN stock_list 
            ON triple_insider.symbol=stock_list.symbol 
                AND triple_insider.exchange=stock_list.exchange  
        LEFT JOIN Stocks 
            ON triple_insider.symbol=Stocks.TickerName 
                AND triple_insider.exchange=Stocks.exchange  
        LEFT JOIN GuruList 
            ON Stocks.GuruName=GuruList.GuruName    
    WHERE stock_list.price > 0  
        AND stock_list.mktcap >= 100 
        AND stock_list.rank_balancesheet/10 >= 5 
        AND stock_list.volume != 0 
        AND stock_list.volume >= 200000 
        AND stock_list.price >= 2 
        AND stock_list.price <= 10 
        AND stock_list.shares != 0 
        AND stock_list.shares <= 500 
        AND stock_list.p_pct_change != 0 
        AND stock_list.p_pct_change >= 2 
        AND stock_list.cash2debt >0 
        AND stock_list.cash2debt >= 0.1 
        AND stock_list.equity2asset >0 
        AND stock_list.equity2asset >= 0.1 
        AND stock_list.fscore != 0 
        AND stock_list.fscore >= 1 
        AND stock_list.zscore != 0 
        AND stock_list.zscore >= 0 
        AND stock_list.medpsvalue > 0 
        AND stock_list.p2medpsvalue <= 0.7 
        AND stock_list.p2iv_dcf_share <= 0.5 
        AND  GuruList.id IN( 0,155 ,88 ,54 ,11 ,47 ,112 ,84 ,3 ,20 ,22 ,114 ,67 ,40 ,102 ,164 ,50 ,64 ,108 ,163)  
        AND stock_list.exchange IN ('NAS','NYSE','OTCPK','','OTCBB','AMEX')   
    ORDER BY triple_insider.date DESC
    LIMIT 5001

---

This is the Explain:

-------------------------------------------+
| id | select_type | table          | type   | possible_keys                                                                      | key      | key_len | ref                                                                       | rows | Extra                                        |
+----+-------------+----------------+--------+------------------------------------------------------------------------------------+----------+---------+---------------------------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | GuruList       | range  | PRIMARY,GuruList_GuruName,GuruList_id,GuruName                                     | PRIMARY  | 4       | NULL                                                                      |   20 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Stocks         | ref    | GuruName,TickerName,exchange,exchange_2                                            | GuruName | 43      | g_main.GuruList.GuruName                                                  | 1490 | Using where                                  |
|  1 | SIMPLE      | triple_insider | ref    | triple_insider_symbol_index,exchange                                               | exchange | 22      | g_main.Stocks.exchange,g_main.Stocks.TickerName                           |   56 | Using where                                  |
|  1 | SIMPLE      | stock_list     | eq_ref | PRIMARY,stock_list_mktcap,stock_list_price,stockfscore,stockfzcore,symbol,exchange | PRIMARY  | 22      | g_main.triple_insider.symbol,g_main.triple_insider.exchange               |    1 | Using where                                  |
+----+-------------+----------------+--------+------------------------------------------------------------------------------------+----------+---------+---------------------------------------------------------------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

---

解决方案

Create an index on triple_insider.date.

You might also want to either get rid of LEFT JOIN or move the conditions on everything but triple_insider to the appropriate ON clauses.

Also DISTINCT, though a legit construct by itself, seems to be a wrong way to solve a problem you're not describing in your post.

这篇关于MySQL的加入和排序慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！