mysql - 这条sql可以怎么优化，求帮助

Question

select count(*) from trade where shippingtype <> "free" and status in ("TRADE_FINISHED","WAIT_SELLER_SEND_GOODS") and tosellerreachgoods = 0 and consigntime <1470110400000 and ( endtime >=1469980800000 or endtime is null ) and created >=1469980800000 and created <=1470067200000 and user = "xxxxxxxxxxxxxxxx" ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	trade	range	trade__user,trade_user_created,trade_user_buyernick,trade__status_created,trade_user_status	trade_user_created	108	NULL	588	Using index condition; Using where

Answer 1

Although there is nothing special about this query from the explain results, the following points can be considered.
1, shippingtype and status appear to be enumeration values ​​and can be replaced by tinyint (numeric query is faster than string query);
2, endtime are set to NOT NULL , replace the original null value with a special value (-1 or 0). The NULL value will affect the efficiency of the index;
3. If possible, use user_id instead of user;

Optimization of personal YY:

SELECT count(*) FROM trade 
WHERE status IN ("TRADE_FINISHED", "WAIT_SELLER_SEND_GOODS") 
AND shippingtype <> "free"
AND tosellerreachgoods = 0 
AND user = "xxxxxxxxxxxxxxxx"
AND consigntime < 1470110400000 
AND created BETWEEN 1469980800000 AND 1470067200000
AND ( endtime >= 1469980800000 OR endtime IS NULL );

There is nothing much to say about the first four conditions. The main ones are the last three conditions, which are consigntime, created and endtime. They are arranged according to the filtering range from small to large (specifically, it depends on the three conditions in your table. range of fields).

Some suggestions are based on the fact that you can modify the table structure. If you don't have permission, just ignore it.

Reference: http://tech.meituan.com/mysql...

Answer 2

Strong

Answer 3

That does not mean affecting performance

Answer 4

From the explanation, there is no problem