|
确定从语义上查询条件可以直接下推后，重写如下：
- SELECT target,
- Count(*)
- FROM operation
- WHERE target = 'rm-xxxx'
- GROUP BY target
执行计划变为：
- +----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
- | 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |
- +----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
关于 MySQL 外部条件不能下推的详细解释说明请参考以前文章：MySQL · 性能优化 · 条件下推到物化表 http://mysql.taobao.org/monthly/2016/07/08
7、提前缩小范围**
先上初始 SQL 语句：
- SELECT *
- FROM my_order o
- LEFT JOIN my_userinfo u
- ON o.uid = u.uid
- LEFT JOIN my_productinfo p
- ON o.pid = p.pid
- WHERE ( o.display = 0 )
- AND ( o.ostaus = 1 )
- ORDER BY o.selltime DESC
- LIMIT 0, 15
该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。
- +----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
- | 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort |
- | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
- | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
- +----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
（编辑：拼字网 - 核心网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
