今天从慢查询发现一条语句查询时间达6秒。结果只查出一条记录。
原语句如下
SELECT biz_order_id, buyer_id, buyer_nick, gmt_create, gmt_modified, attributeCc, seller_id
FROM trade.biz_order
WHERE shop_id=20484 AND STATUS=4 AND gmt_create
>= '2017-10-30 16:34:42' AND order_type = 6
ORDER BY gmt_create DESC, biz_order_id DESC
LIMIT 0,100;
执行计划
shop_id都有索引可却走了时间gmt_create的索引,rows=861665
优化方法3种:
1:强制走shop_id索引
![mysql中order by 和limit一起使用不当会导致效率极慢的4种优化方法 mysql中order by 和limit一起使用不当会导致效率极慢的4种优化方法](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAABBsAAABCCAIAAACZ7km/AAAUOklEQVR4nO2dzW7cuhXH+VSGoCe47+BVZEOLvkF2WdZjF9DG8Lbbi9b2DApokWUW6SZAcce+3ghpe5vWTeHFzU3GHsdxwC70RfFrJA1FkZr/D4Qxw9HHOYeHFP9DaUw+AQAAAAAAAEBfyMYt/rirWIg+AAAAAAAAvtNKUdDdA4oCAAAAAACANmxQFLspJ3IgKgAAAAAAANhIN0VBCNG8nRhQFAAAAAAAAGzEsKIgagzN87MkJCRODR2NUkppGhMSJhlfDUUBAAAAAADARjooikoVaAQDqxxUrzmyJE4yStO4KRPSWDLJp1kSNuUEs1mWhKUxsl1FmH3TWNwHigIAAAAAAICN6BSFSk5odEL3NQqJoCikgUQWNCb+/Gb1IWT6gD8rdwpBqlBKISoAAAAAAADYRAdFIQoDqaLY+JrVBxwNVSGKAlEpSDdjatO4PmiWhI3dm/tKjwRFAQAAAAAAgJ62ioITBqr1h85rFPnCQrFQsUEqSOqkOkCQ*JWsQ3L5pLC5SQFEAAAAAAACgR6koNHKiJW22VzxEsY2ikNzyJL+lidtXdkYoCgAAAAAAAPS0VRTcaoN0/UGzQCFZptDd89RbUSjlRL81CogKAAAAAAAA9HRYo+AEhvQ1W8P+VaC656nXcxT8cxLFHts8R0GhKAAAAAAAANDSTVGwaxRsJbeN6gWPQlAwPwNL+J+UFX/rqdyMfUs2/YCscAr5jVEUigIAAAAAAAAtckUhfYiCqJ/AVm2j2r03yon/lmh/bxaiAgAAAAAAABVtFQWH/q4nff122Puf2RVQFAAAAAAAAKjoqSh2CigKAAAAAAAAVEBRbAaKAgAAAAAAABUSRQE5IQJRAQAAAAAAgBQoilbkMbHfPAAAAAAAADgO+aPA2LN3RxEDBQAAAAAAACB/Ov8LCgoKCgoKCgoKCgpKvzLED7wCAAAAAAAAdoWRFcXnz5/HNWBEds33CfvrpmtuWuUCiIzIBGLiqQuemq3BX4/8tRwAy0g7CxTFaOya7xP2103X3LTKBRAZkQnExFMXPDVbg78e+Ws5AJZxUVH89ttv4xowIrvm+4T9ddM1N61yAURGZAIx8dQFT83W4K9H/loOgGWknQWKYjR2zfcJ++uma25a5QKIjMgEYuKpC56arcFfj/y1HADLQFG4xa75PmF/3XTNTatcAJERmUBMPHXBU7M1+OuRv5YDYJlRFUWWhGGSCdWfPn0ydSjv6Oy76LhXoejT1p7gpmsDWrVNKjqQtNLIjG7VuAwVE4vN3TPh05gQQgiJU9MGtcPN0WMbjHnENo3ZRDI4IQFgJ5F2lmEUResJx6+//mrg4P22HHtmI/ddY5XniqJPW4tULmteWKdzU1rBTMA5cqc8VxTSyIxu1bgMFROLzd0r4dOYjNzwg/TTUTHkUdk03JhjJKO6TEhCwqI4twPDGgA2kXYWQin99u15/fg0bPn5D0Hwh+tm5fPz9/9+/F/ns8sO1WfL9scZoCh911glfjSqC2b87VrcC0KfpvQo4MZbwdX28qUreRYTW83d/2pCDufuRd7fYnKoZ5umSiQjGdVlQtLqdGMPaygoNkveWcR6Qil9+vb8sP5qslyfBMHJVf6i0vZ5DVOen7//5/a/4tmvjoPGLvVBgtm19ODB7DrfdxGVZ4vmN7Pq4+MbwUj208UsING8OkIwm58EwWEUVIcqneJP17+ofGdtruNQO15ZtTEUbhWVv2KcZV6fzA4IIYdzttUODoPg5EpSw0bjcL5mjyA7i5CWAzSlQwmmPe/hfC0JTuNtnnLbpGK15fyw3rexyw0frmsb7dXDKgsdx04ZMCaqxJD1yoFcKEcP0f76erF9Xpk121JpjK7ykbNsu3xwuJkFh3Pt+GnII6Zp2JFn3bysK5NK2tw9JyQBv+UiqiYV1ycBCWbX/GWosoG/nI3SyigopkveWcT6QRXFImJnLe07cDmzmR8czteLqHz7UM9m8oM36xuXN+VJ5Z/OD8nBon5RDBPlR0Rmhon2kEReenDBqqvjgOhCMX7CtfRXiLPUa6ZZ2Yuc/EWddVfHAX+E+WERt0aODd+UziSY7LxiuJjgiG+LAPZNxfoI+ZayXSThGry9Olu1nRlOlQFjokoMsVcO5AJhNY9g/9gD5viKohGf5lBQRuzqOIgODqP514f1Ito0fhrziGsjSZNpk0rS3L0nJE1ts64k1s0sYLQKo5wlCnlygwbKLhetonj6dv/waLJcnQTByTL/y9Y0N3v+/v3Dv2/Fs0eEEHJwWe3I9mdycFkdXFovmqE3sni7iMjB5cPjZUSiufhRMJsLp9suRCrf77mgMQMZH09NKMy2pomi8peP85XWazY+qheqKDV2X0TmAtWqKZ1JMMl5uXDxwVnw/XHLVCy2UXdwMVxW2qu7VdMpA8akzXCtH6i3dEE6nFb2mzj1EGZbKqpWKIaCx8somF09XkYHl1cnQbS4z/9q+6Mxj1QDO/tCn1QdU67FmFmX5SwghASzn+XWsl6wl7NRWhkFxXTJO4tY76KiuH94rMcslU5oox86KIr8ilhcHeUTPqPDweZp6NVJkM+wVfFkp3HOD1UdJrgarx9kA3cfRdHMsaGb8sGhBGuhKKTBafbHbVLx6iQITi5nAYkWYpSq0giXlfbqa9UUyoAxaTNcW1MU4lnGHjwdVxTLWRDNF1Fwsnz4eRYcXM4PGN0o749WFUWnpDKqKO7nB5sVhXgRH6WVUVBMF52i+Pr0bXW/NlmWx3vB8fJ+HpHgaLle3a+Xs4AEx8vmZs/P33/517/Fs0ez69X9enV/fRQER8t5REh0KT14s/7++igQtiQHFxoj2U+Xx3uE7OWnLk6xXt2vV5cHRH66rYrK99oqxoYielxNNFeHwrmi8pePs9ZreXz4mjoazSgxybM8bubYwE3pUoJpzluFqxGcS/7t3papWO57EeXRUOzSDJeF9ups1YTKgDFRJYbYK4d0YXW/lttv4tRDmG2pNNwXhoL79Wp5HAVB3rjL2UEUHRwtN/RHYx6xIzw75tTjqjapJM3dc0Iiy5B5RA4umANKL9ySi/gorYyCYrrknUWsJ5TSx69PX1YPJstytrc3+2n18OWieLxqL4qKGqY8P3//5y8fxLO/KBcJ946uiqNV64Z7s5+qg3P1zZoXFw9fVg/5+YvjCKX56dXRXnC0fODPSKKLlVAp+NK1qHxnrSqDV0ZP6q8qFI4Vlb9inCVeNz0q4sNkFF9TRyO6YKNUv57zOTZwU7qTYNrz5jVccJpv8wBuk4p1c8xfVMeU7MKEy0p7dbdqOmXAmKgSQ9IrB3WhPJEqbx2LvKXCuc8PBXnTl417EREmH1T90ZhHbOY0E6YeVzVJJW3uXhMSZswkhARHy/mLcoLx5SLirlzckfnL2UhphoJituSdRawfRlF0sWm0wZQr9XBp4zLjlu/Dl1bXez9L26Z0I8G8CTgbrlHay65V45YJxMTTEdVTsyfpkb+Wo6BYLlpF8fj0+cu9/fL8/PyPf/7LytkvXxCO4Pc/Nbb58wvy4qJ8+9Nsb2/2t4n47kRR+Tt0nEd0jSuOJJj1gG/uepvDNUZ7WbZq3GIuJj2be1wXPI28m8Vfj/y1HAXFcsk7i1hPKKXrx6+/fV7ZL9+en//+j1/GOvu4Zdd8n7C/brrmplUuFERmkjHx1AVPzZ6kR/5ajoJiueSdRawnlNKfAQAAAAAAAKAXhFL6bTyenp5GPPu47JrvE/bXTdfctMoFEBmRCcTEUxc8NVuDvx75azkAlpF2FkJH5dOnT+MaMCK75vuE/XXTNTetcgFERmQCMfHUBU/N1uCvR/5aDoBlpJ0FimI0ds33CfvrpmtuWuUCiIzIBGLiqQuemq3BX4/8tRwAy0BRuMWu+T5hf910zU2rXACREZlATDx1wVOzNfjrkb+WA2AZKAq32DXfJ+yvm665aZULIDIiE4iJpy54arYGfz3y13IALANF4RYa38OzJBv03HfJ4KcQ2NTWt4u3+/vLd2ZO9vF0//XLxcrMwTayVRrfxOQVIa9IfGPOIEppJ6t658NwiTRkiiIyIhMYij11wVOzNfjrkYuW279YjzE9MI+PXrCTAeftH1lRxCEVo6PrwHdJ+Irk8Q3fZMzbOM1fn6fVtuk5IWdJxuxCXsUpFVqlsQEhr8LkzqiTXeigKIznlnuK4vb9y/3lO7pavHy9//L9bVG7Wrx8u7jN/1abairZI3483edqBmOL61AaD5aENhLMVCKJxxlJUexsZLQ5nMbsoOoqw04HB0tIC7PYmFCSl5ivkTRoRsPy08phfnvZNhWW5+UzQn8g9AdC/6r99IdYWVPRdmSQJkOPDGmzy1iKojqv+GKbw6reaiqNnM4UA3pRTga4+G+J3YHLhqLIEkoIJR0VBTPTSuNcPDQyO47Pyg3ukvAs5NvgJiZiq7gk+7BGwfDuNF9SWC1evj09fVsuL2yjKOjt4u3+6UezTsjpfwW9S8LBZmn4Jl4FIiOiiUlyVi6g5YOq6VObAopCTkqrISYJaZLRJCxrUsl1Od+G/TSNi+2zpNAk4jYsNhXFX2M6Syml9EMiUQg0LT6llP4Y0h8zWQ3DVopiIEZfoxhoaju0XyMqit5HZicDUBQ6Mhp2VhTCTKupldM3Yfgmo5Sm5yR+I6q6NM5XM7xUFHF8xqyiFGZnyVm9wCK/SYZdyaHFjsk587Zan6ki+Yo/Ub29LX/r9YRcGHw8LW5/2kpR0Nv3L+vljiHRX4dk8c/FcPXVLxliorb56sjnQxrn64HFR/LFk+xNWO9SJwyTk5IkrPJZnbdykwYBkRFpNxSnkmHZGdots5R3FMjHybohGq3JjL1FGlgy2zAxoWn5l61hqdVCedWO2aWJkGaybVhsejSrliYy+ruQfmizpbqmq6IQu/ymjt/svJ12kV0+1INS54TnzyjMOsLzWBiL9EdrmqSYjJmJIWVn5FlyVsz91AeRTXj4CFv0gp0MNOIvsao5NOlnfbYHLncVRZq3BHNrk7D6xmgGYZ0oexPK1yi4ZjPnYld0gxebOqIXym8K05i9HJ6nub/lTKX+tBFAytQ0tjeMxt966l8Kg3fL/dOP2yoKWimTgdE2JRv/ZrSpMDxZs6qcJTfzoRqUs+RMcS8W8z1Kel7cf9j8AluahOXRbmK5WK0TUjBpABAZkXbLxWr3HaDVMgulG8bJanGbbU3q8xoFpZTSJKzvWYqZW5VqbcAQN+9okiqQ2I27nlhVICqEnB9D/p4osSanm6IQuzx3DRU6vnw+2moXxeVDPihlnRNePKN4kRLGIvXRWs/Ft49hGZnsTRifx/ENpTSNlQfRTHhUEbbjhRBn3SSNHZr0sz6sUXDkj6pI7npKslJ11E+xSGW9l2sUpZF59rBmq78ipfxmwo7ca05fiUs6RumkKBqyobeikFYOQOumbEabjjpvVuRG/v2H+vuM/KsUYeSlwnWIypJQ9dyIdF9ERrWvXUXRTF0v1yiUVwHlONlsTWr7wjwQhZZgnoIQH6Wo7nFKwuIeJ1FRiNuwuKYoqk+5e5zEmo4pJOvyGzu+Yo1iwy7Sy4dqUNJMe1QJr6mRz3S1R5NGUG7V1jGkND0Pk7tyKp9PqfO/nSY8ighb80IeZ4lV2guNxRtz/FQUlNbaUZri7EeaRpK+HZVeioL77qFJD0WhD5dRuikKSm/fvzx97/8ahX6odW/enGv4TSuk5aDm27w5B5ERaTUUuzR+ihhVFDnMxXsSiqJSAgXidblZk69g8Hc9ybZhcfOup+qJC01Nr+coml1ezCX2noJtFIU0/aSDkk+KYusY5qsTN2l8lmT5/U43Mf9bSW0mPC07+GBeKBWF3CrFhQaKQrVHWN3vpMpsduuJKop6Gessyaq3SmTrX7IpS3EzFbsGlzPSNI5/jqKofXf6en//7eK2em6bUsr+KpQnz1Gwww2vBkecN0vygZbqXf0LVHdJXFy9suQsTG7EEVmbhKob9up9RZPMg8iItJgFZsmZ+ZtxDbLVXU/C5KPRmnfMt1cWzTZCljR+solNnSSkYcK/jkmpOjIaNlckqiezxW1YRn4yO6O/K2XGh6RehchXJMQalnb3/tVpI+nykkkqM2i0VBRtLtaUqgelFnc9bTyj6nvbVkejsutdOkgMKaV3SXxWPFWbvYnj85h5+pQ7iGbCo4qwPS9kyk1ic3No0s/6bA9cFn/riVBCaNz6KwHmKeRqQFffnKNRFNwiV2MJydVfj5Xfu8U+u6NektPcVFP+2rHsESsieWDDKK1/64kRBqzSeL2/nxex5vXpO+4tpY781pPQt7V35Vm2is8HZvBS3dbPJGHxm87itURMwvZLyWKKDgAiI6LrntWpHZYTtPWNW6oHVcXJB+d1fpOtf09mp8L1t6yp5ARlxYbvvx7LKAqalj8US8rlCLGGoe29f8ykme/y0o5f9iDNk9mqXZQXa/2g1DnhhTMy2xSZr30yWzbWyX5cx3gMi4MwD6SJ11bJCCyb8Egv0Na8kCsKebs3hib9rM/uwIX/cDcau+Z7q/9HYRA//h/FgLhilUsLgzmIjIgrMdkCT11wwuxU8jhEb5zwqBeDW34Ta+8ymBDDeWozhtPwYhigKNxia9+bPzc29pLLRvA/sy1jwioTOSaZN4+cuoiMiJs53AlPXXDB7Czhbx/YBhc86sdAlqfnVXd294cNzJI/LW30gCPEcBpeDAQUhVvsmu8T9tdN19y0ygUQGZEJxMRTFzw1W4O/HvlrOQCWgaJwi13zfcL+uumam1a5ACIjMoGYeOqCp2Zr8Ncjfy0HwDJKRfEJAAAAAAAAAFogVxQAAAAAAAAA0A8oCgAAAAAAAEB/oCgAAAAAAAAA/YGiAAAAAAAAAPQHigIAAAAAAADQHygKAAAAAAAAQH/+D9JZFppZY7/YAAAAAElFTkSuQmCCAA==)
SELECT biz_order_id, buyer_id, buyer_nick, gmt_create, gmt_modified, attributeCc, seller_id
FROM trade.biz_order force index(idx_shop_id)
WHERE shop_id=20484 AND STATUS=4 AND gmt_create
>= '2017-10-30 16:34:42' AND order_type = 6
ORDER BY gmt_create DESC, biz_order_id DESC
LIMIT 0,100;
2:用子查询:
select * from ( SELECT biz_order_id, buyer_id, buyer_nick, gmt_create, gmt_modified, attributeCc, seller_id
FROM trade.biz_order
WHERE shop_id=20484 AND STATUS=4 AND gmt_create >= '2017-10-30 16:34:42' AND order_type = 6
ORDER BY gmt_create DESC, biz_order_id DESC) t
LIMIT 0,100;执行计划如上一样
FROM trade.biz_order
WHERE shop_id=20484 AND STATUS=4 AND gmt_create >= '2017-10-30 16:34:42' AND order_type = 6
ORDER BY gmt_create DESC, biz_order_id DESC) t
LIMIT 0,100;执行计划如上一样
3:调换order by中的两个条件顺序
ORDER BY biz_order_id DESC ,gmt_create DESC limit 0,100;换成这样。我发现这样执行计划的rows=1.9万效果更好。
4:还有一种方法删除gmt_create列的索引,原理和方法3差不多。
总结:mysql中的order ,limit一起使用时的顺序是这样的和oracle不一样
order -->limit-->where条件
而常规一般是where-->order-->limit。