前段时间一位同事收到某开发部门一位同事求助,希望帮忙优化一条Mysql的sql语句,大体如下:
select n.id ,nc.content
from news n force index (category1_status,category2_status,category3_status),news_content nc
where n.id=nc.id
and n.status=2 and (n.category_id_1 in (5003107,5003108)
or n.category_id_2 in (5003107,5003108)
or n.category_id_3 in (5003107,5003108)
调试的时候发现怎么都不能走index_merge的执行计划(我们所期望的),后来临时给他们一个union的解决方案。后来下班吃完晚饭后一起找问题,发现即使只有单个表,也没办法走到index_merge的执行计划,不管是加提示还是不加提示,调试过程如下:
mysql> explain select n.id ,nc.content
-> from news n force index (category1_status,category2_status,category3_status),news_content nc
-> where n.id=nc.id
-> and n.status=2 and (n.category_id_1 in (5003107,5003108)
-> or n.category_id_2 in (5003107,5003108)
-> or n.category_id_3 in (5003107,5003108)
-> ) ;
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
| 1 | SIMPLE | n | ALL | category1_status,category2_status,category3_status | NULL | NULL | NULL | 552535 | Using where |
| 1 | SIMPLE | nc | eq_ref | PRIMARY | PRIMARY | 4 | biznews.n.id | 1 | |
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
2 rows in set (0.00 sec)
从上面可以看出,Mysql优化器已经识别到有三个索引可以用,但是没有选任何一个,然后去掉其中一个参与join的表,同时去掉提示,还是不行:
mysql> explain select *
-> from news n
-> where
-> ( n.status=2 and n.category_id_1 = 5003107)
-> or ( n.status=2 and n.category_id_2 = 5003107)
-> or (n.category_id_3 =5003107 and n.status=2);
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
| 1 | SIMPLE | n | ALL | news_ind_status,category1_status,category2_status,category3_status | NULL | NULL | NULL | 552535 | Using where |
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
1 row in set (0.00 sec)
单表,加提示效果:
mysql> explain select *
-> from news n force index (category1_status,category2_status,category3_status)
-> where
-> ( n.status=2 and n.category_id_1 = 5003107)
-> or ( n.status=2 and n.category_id_2 = 5003107)
-> or (n.category_id_3 =5003107 and n.status=2);
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
| 1 | SIMPLE | n | ALL | category1_status,category2_status,category3_status | NULL | NULL | NULL | 552535 | Using where |
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
1 row in set (0.00 sec)
后来,尝试了一下去掉一个or,,发现正常了,执行计划和我们预期的一样了:
mysql> explain select n.id ,nc.content
-> from news n force index (category1_status,category2_status),news_content nc
-> where n.id=nc.id
-> and ((n.status=2 and n.category_id_1 in (5003107,5003108))
-> or (n.status=2 and n.category_id_2 in (5003107,5003108))
-> );
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| 1 | SIMPLE | n | index_merge | category1_status,category2_status | category1_status,category2_status | 6,6 | NULL | 54238 | Using sort_union(category1_status,category2_status); Using where |
| 1 | SIMPLE | nc | eq_ref | PRIMARY | PRIMARY | 4 | biznews.n.id | 1 | |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
2 rows in set (0.01 sec)
ok,再将之前拿掉的参与join的表加入进来,也正常:
mysql> explain select n.id ,nc.content
-> from news n force index (category1_status,category2_status),news_content nc
-> where n.id=nc.id
-> and ((n.status=2 and n.category_id_1 in (5003107,5003108))
-> or (n.status=2 and n.category_id_2 in (5003107,5003108))
-> );
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| 1 | SIMPLE | n | index_merge | category1_status,category2_status | category1_status,category2_status | 6,6 | NULL | 54244 | Using sort_union(category1_status,category2_status); Using where |
| 1 | SIMPLE | nc | eq_ref | PRIMARY | PRIMARY | 4 | biznews.n.id | 1 | |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
2 rows in set (0.00 sec)
莫非就是因为有三个or条件,而且三个条件都是不同的column上面,并对应上三个不同的索引所造成的?继续测试:
mysql> select * from t;
+——+————+———-+
| id | name | descs |
+——+————+———-+
| 1 | abc | x |
| 2 | abcd | xx |
| 3 | abcde | xxx |
| 4 | abcdef | xxxx |
| 5 | abcdefg | xxxxx |
| 6 | abcdefgh | xxxxxx |
| 7 | abcdefghi | xxxxxx |
| 8 | abcdefghij | xxxxxxx |
| 8 | a | xxxxxxx |
| 9 | ab | xxxxxxxx |
+——+————+———-+
mysql> explain select * from t where id = 3;
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
| 1 | SIMPLE | t | ref | t_id_ind | t_id_ind | 5 | const | 1 | Using where |
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
1 row in set (0.00 sec)
mysql> explain select * from t where name = ‘abc’;
+—-+————-+——-+——+—————+————+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+————+———+——-+——+————-+
| 1 | SIMPLE | t | ref | t_name_ind | t_name_ind | 23 | const | 1 | Using where |
+—-+————-+——-+——+—————+————+———+——-+——+————-+
1 row in set (0.00 sec)
mysql> explain select * from t where name = ‘abc’ or id = 3;
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
| 1 | SIMPLE | t | index_merge | t_id_ind,t_name_ind | t_name_ind,t_id_ind | 23,5 | NULL | 2 | Using union(t_name_ind,t_id_ind); Using where |
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
mysql> explain select * from t where id = 3 or descs = ‘xxx’;
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
| 1 | SIMPLE | t | index_merge | t_id_ind,t_descs_ind | t_id_ind,t_descs_ind | 5,23 | NULL | 2 | Using union(t_id_ind,t_descs_ind); Using where |
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
1 row in set (0.00 sec)
mysql> explain select * from t where name = ‘abc’ or descs = ‘xxx’;
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
| 1 | SIMPLE | t | index_merge | t_name_ind,t_descs_ind | t_name_ind,t_descs_ind | 23,23 | NULL | 2 | Using union(t_name_ind,t_descs_ind); Using where |
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
1 row in set (0.00 sec)
mysql> explain select * from t where name = ‘abc’ or id = 3 or descs = ‘xxx’;
+—-+————-+——-+——+———————————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+———————————+——+———+——+——+————-+
| 1 | SIMPLE | t | ALL | t_id_ind,t_name_ind,t_descs_ind | NULL | NULL | NULL | 10 | Using where |
+—-+————-+——-+——+———————————+——+———+——+——+————-+
1 row in set (0.00 sec)
mysql> explain select * from t force index(t_id_ind,t_name_ind) where name = ‘abc’ or id = 3 or descs = ‘xxx’;
+—-+————-+——-+——+———————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+———————+——+———+——+——+————-+
| 1 | SIMPLE | t | ALL | t_id_ind,t_name_ind | NULL | NULL | NULL | 10 | Using where |
+—-+————-+——-+——+———————+——+———+——+——+————-+
1 row in set (0.00 sec)
基本验证了上面的想法,只要是两个索引,都可以走index_merge,换成三个马上就不行了,即使是强行指定用某两个索引也不行,索引都能够认到,但 优化器就是不使用任何一个。想一下,如果按照提示,使用了两个索引,那么会有剩下一个条件不会走索引,那么对于该条件的过滤还是要通过表查询,这样,对于 mysql来说就相当于要两个索引的index_mereg后再读表,而且仍然要做一次全表扫描,那还不如就作一次表扫描,Mysql最终还是选择一次表 扫描是可以理解的。在Mysql文档上面也说了,在提示了mysql用某一个索引后,也就相当于告诉了mysql不要用其他的相关的一些索引。估计 Mysql也并没有去实现三个索引的index_merge,实际上想想就算是实现了,通过读三个索引然后做merge再去取表的记录,其消耗可能也并不 会太小,对于Mysql的这个选择也无可厚非。