查询条件字段做运算优化
对字段做函数和运算完后无法使用索引。这么做的话,让优化器对字段产生歧义,不知道如何选择索引。因为这时候并没有一个concat(year_id,'-',month_id)这样的字段
- mysql> explain select * from data_market.mkt_order_by_month_userid_type where concat(year_id,'-',if(month_id<10,'0',''),month_id)='2011-09';
- +----+-------------+--------------------------------+------+---------------+------+---------+------+---------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+--------------------------------+------+---------------+------+---------+------+---------+-------------+
- | 1 | SIMPLE | mkt_order_by_month_userid_type | ALL | NULL | NULL | NULL | NULL | 3673176 | Using where |
- +----+-------------+--------------------------------+------+---------------+------+---------+------+---------+-------------+
- 1 row in set (0.00 sec)
- mysql> explain select * from data_market.mkt_order_by_month_userid_type where concat(year_id,'-',month_id)='2011-09';
- +----+-------------+--------------------------------+------+---------------+------+---------+------+---------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+--------------------------------+------+---------------+------+---------+------+---------+-------------+
- | 1 | SIMPLE | mkt_order_by_month_userid_type | ALL | NULL | NULL | NULL | NULL | 3673176 | Using where |
- +----+-------------+--------------------------------+------+---------------+------+---------+------+---------+-------------+
- 1 row in set (0.00 sec)
优化方法,对要传入的值做函数操作
- mysql> explain select * from data_market.mkt_order_by_month_userid_type where year_id=substring('2011-09',1,4) and month_id = substring('2011-09',6,2);
- +----+-------------+--------------------------------+------+---------------+---------+---------+-------------+--------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+--------------------------------+------+---------------+---------+---------+-------------+--------+-------+
- | 1 | SIMPLE | mkt_order_by_month_userid_type | ref | year_id | year_id | 8 | const,const | 377128 | |
- +----+-------------+--------------------------------+------+---------------+---------+---------+-------------+--------+-------+
转载于:https://blog.51cto.com/ace105/752042