使用USE INDEX优化Mysql数据库

USE INDEX (indexname) 来强制使用一个索引

优化前SQL执行时间,如下图:

优化表结构,增加联合索引,如下图:

优化后SQL执行时间,如下图:

优化后,执行SQL:

EXPLAIN  SELECT  DISTINCT (
shop_id
)
FROM goods_items
USE  INDEX ( package_2 )
WHERE package =  'false'
AND channel_id =  '1'
AND marketable =  'true'
AND shop_open =  'true'
AND areaflag =  'suzhou'
AND price_sc >  '0.00'
AND cat_id
IN (
'403',  '410',  '414',  '404',  '405',  '406',  '407',  '408',  '409',  '806',  '411',  '412',  '413',  '393'
)
ORDER  BY buy_count DESC
LIMIT 0 , 10;

如下图所示:

而优化前则,如下图所示:

总结一下,加了USE INDEX之后效率提升非常明显。经过测试,应该和SQL语句中带有ORDER BY也有很大的关系,如下图所示:

发表评论