评论

收藏

[MySQL] 无合适where条件过滤时尽量选择order by后的字段以驱动表进行查询

数据库 数据库 发布于:2021-07-04 10:19 | 阅读数:314 | 评论:0

  后台查询语句
SELECT o.orders_id, s.orders_status_name, ot.text ,af.affiliate_id
FROM orders o
LEFT JOIN orders_total ot ON (o.orders_id = ot.orders_id)
LEFT JOIN affiliate_sales AS afs ON afs.affiliate_orders_id = o.orders_id
LEFT JOIN affiliate_affiliate AS af ON af.affiliate_id = afs.affiliate_id
LEFT JOIN  orders_status s  ON o.orders_status = s.orders_status_id
WHERE   s.language_id = '1'
 AND (ot.class = 'ot_total' OR ot.orders_total_id IS NULL)
ORDER BY o.orders_id DESC LIMIT 0, 20
  有客户反应某后台查询非常慢,通过程序找到对应的sql,如上!
  explain发现
+----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys        | key            | key_len | ref             | rows  | Extra                    |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+
|  1 | SIMPLE    | s   | ALL  | PRIMARY          | NULL             | NULL  | NULL            |  21 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE    | o   | ref  | orders_status        | orders_status        | 4     | banggood.s.orders_status_id | 31747 |                        |
|  1 | SIMPLE    | ot  | ref  | idx_orders_total_orders_id | idx_orders_total_orders_id | 4     | banggood.o.orders_id    |  19 | Using where                  |
|  1 | SIMPLE    | afs   | ref  | PRIMARY          | PRIMARY          | 4     | banggood.o.orders_id    |  11 | Using index                  |
|  1 | SIMPLE    | af  | eq_ref | PRIMARY          | PRIMARY          | 4     | banggood.afs.affiliate_id   |   1 | Using index                  |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+
  s表被作为驱动表,s表为全表扫描,o表使用了status类型的可选择性非常低的字段作为索引。
  初步一看就知道索引使用不恰当!
  我们可以看到这条语句where条件中,没有什么合适的可驱动条件;但是,在order by中,发现order by o.orders_id(orders_id为orders表的主键)。我们就可以利用这个特性!
  强制使用orders表的orders_id索引进行驱动!
  更改如下:
EXPLAIN SELECT o.orders_id, s.orders_status_name, ot.text ,af.affiliate_id
FROM orders o FORCE INDEX(PRIMARY)
LEFT JOIN orders_total ot ON (o.orders_id = ot.orders_id)
LEFT JOIN affiliate_sales AS afs ON afs.affiliate_orders_id = o.orders_id
LEFT JOIN affiliate_affiliate AS af ON af.affiliate_id = afs.affiliate_id
LEFT JOIN  orders_status s  ON o.orders_status = s.orders_status_id
WHERE   s.language_id = '1'
 AND (ot.class = 'ot_total' OR ot.orders_total_id IS NULL)
ORDER BY o.orders_id DESC LIMIT 0, 20;
+----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys        | key            | key_len | ref              | rows | Extra     |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE    | o   | index  | NULL             | PRIMARY          | 4     | NULL               |  1 |       |
|  1 | SIMPLE    | s   | eq_ref | PRIMARY          | PRIMARY          | 8     | banggood.o.orders_status,const |  1 | Using where |
|  1 | SIMPLE    | ot  | ref  | idx_orders_total_orders_id | idx_orders_total_orders_id | 4     | banggood.o.orders_id       |   19 | Using where |
|  1 | SIMPLE    | afs   | ref  | PRIMARY          | PRIMARY          | 4     | banggood.o.orders_id       |   11 | Using index |
|  1 | SIMPLE    | af  | eq_ref | PRIMARY          | PRIMARY          | 4     | banggood.afs.affiliate_id    |  1 | Using index |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+
  

  对比两次profiling;
  前者:
+--------------------------------+------------+-----------+------------+--------------+---------------+
| Status             | Duration   | CPU_user  | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+------------+-----------+------------+--------------+---------------+
| starting             |   0.000027 |  0.000000 |   0.000000 |      0 |       0 |
| Waiting for query cache lock   |   0.000006 |  0.000000 |   0.000000 |      0 |       0 |
| checking query cache for query |   0.000130 |  0.000000 |   0.000000 |      0 |       0 |
| checking permissions       |   0.000007 |  0.000000 |   0.000000 |      0 |       0 |
| checking permissions       |   0.000003 |  0.000000 |   0.000000 |      0 |       0 |
| checking permissions       |   0.000003 |  0.000000 |   0.000000 |      0 |       0 |
| checking permissions       |   0.000003 |  0.000000 |   0.000000 |      0 |       0 |
| checking permissions       |   0.000007 |  0.000000 |   0.000000 |      0 |       0 |
| Opening tables         |   0.000130 |  0.000000 |   0.000000 |      0 |       8 |
| System lock          |   0.000017 |  0.000000 |   0.000000 |      0 |       0 |
| Waiting for query cache lock   |   0.000033 |  0.000000 |   0.000000 |      0 |       0 |
| init               |   0.000057 |  0.000000 |   0.000000 |      0 |       0 |
| optimizing           |   0.000026 |  0.000000 |   0.000000 |      0 |       0 |
| statistics           |   0.000041 |  0.000000 |   0.000000 |      0 |       0 |
| preparing            |   0.000031 |  0.000000 |   0.000000 |      0 |       0 |
| Creating tmp table       |   0.000111 |  0.001000 |   0.000000 |      0 |       0 |
| executing            |   0.000007 |  0.000000 |   0.000000 |      0 |       0 |
| Copying to tmp table       |   3.541123 |  0.968852 |   2.357642 |    75800 |       0 |
| converting HEAP to MyISAM    |   0.239566 |  0.038994 |   0.198969 |      0 |    262152 |
| Copying to tmp table on disk   | 174.185144 | 13.864893 |  35.361625 |    2135152 |     2500280 |
| Sorting result         |  20.923419 |  0.127980 |   3.017541 |    2770408 |     27536 |
| Sending data           |   0.045078 |  0.000000 |   0.002999 |     1208 |       0 |
| end              |   0.000018 |  0.000000 |   0.000000 |      0 |       0 |
| removing tmp table       |   0.881884 |  0.018997 |   0.160976 |      760 |       8 |
| end              |   0.003960 |  0.000000 |   0.002000 |      448 |       0 |
| query end            |   0.000012 |  0.000000 |   0.000000 |      0 |       0 |
| closing tables         |   0.031745 |  0.000000 |   0.000999 |      936 |       0 |
| freeing items          |   0.015499 |  0.000000 |   0.003000 |      808 |       0 |
| Waiting for query cache lock   |   0.000017 |  0.000000 |   0.000000 |      0 |       0 |
| freeing items          |   0.000791 |  0.000000 |   0.000000 |      0 |       0 |
| Waiting for query cache lock   |   0.000009 |  0.000000 |   0.000000 |      0 |       0 |
| freeing items          |   0.000003 |  0.000000 |   0.000000 |      0 |       0 |
| storing result in query cache  |   0.000009 |  0.000000 |   0.000000 |      0 |       0 |
| logging slow query       |   0.000003 |  0.000000 |   0.000000 |      0 |       0 |
| logging slow query       |   0.000010 |  0.000000 |   0.000000 |      0 |       0 |
| cleaning up          |   0.000007 |  0.000000 |   0.000000 |      0 |       0 |
+--------------------------------+------------+-----------+------------+--------------+---------------+
  各种cpu,io损耗,惨不忍睹!其中最大的消耗是Copying to tmp table on disk。
  优化后的profiling
+----------------------+----------+----------+------------+--------------+---------------+
| Status         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting       | 0.000139 | 0.000000 |   0.000000 |      0 |       0 |
| checking permissions | 0.000008 | 0.000000 |   0.000000 |      0 |       0 |
| checking permissions | 0.000003 | 0.000000 |   0.000000 |      0 |       0 |
| checking permissions | 0.000003 | 0.000000 |   0.000000 |      0 |       0 |
| checking permissions | 0.000003 | 0.000000 |   0.000000 |      0 |       0 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |      0 |       0 |
| Opening tables     | 0.000125 | 0.000000 |   0.000000 |      0 |       8 |
| System lock      | 0.000018 | 0.000000 |   0.000000 |      0 |       0 |
| init         | 0.000057 | 0.000000 |   0.000000 |      0 |       0 |
| optimizing       | 0.000026 | 0.000000 |   0.000000 |      0 |       0 |
| statistics       | 0.000043 | 0.000000 |   0.000000 |      0 |       0 |
| preparing      | 0.000030 | 0.000000 |   0.000000 |      0 |       0 |
| executing      | 0.000005 | 0.000000 |   0.000000 |      0 |       0 |
| Sorting result     | 0.000006 | 0.000000 |   0.000000 |      0 |       0 |
| Sending data     | 0.000573 | 0.000000 |   0.000000 |      0 |       0 |
| end          | 0.000008 | 0.000000 |   0.000000 |      0 |       0 |
| query end      | 0.000004 | 0.000000 |   0.000000 |      0 |       0 |
| closing tables     | 0.000014 | 0.000000 |   0.000000 |      0 |       0 |
| freeing items    | 0.000062 | 0.000000 |   0.000000 |      0 |       0 |
| logging slow query   | 0.000005 | 0.000000 |   0.000000 |      0 |       0 |
| cleaning up      | 0.000007 | 0.000000 |   0.000000 |      0 |       0 |
+----------------------+----------+----------+------------+--------------+---------------+
  相对于前者来说,后者小号可以忽略不计!
  前后两者执行时间之比是196s比0.01s!
  

  总结:
  如果碰到where中没有适当条件来筛选数据的时候,看到order by中有比较好的条件,我们第一个就要想到用order by中的条件驱动查询!

  但是mysql优化器并没有使用到该条件,可以强制force index使用该条件!


  
关注下面的标签,发现更多相似文章