前段时间一位同事收到某开发部门一位同事求助，希望帮忙优化一条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
前段时间一位同事收到某开发部门一位同事求助，希望帮忙优化一条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)
单表，加提示效果：
触发式加载精美特效企业网站源码使用jquery实现了很多精美的触发式加载特效，网站首页在随着访客的滚动条滚动过程中会出现很多触发式加载的特殊效果，让这个网站的风格瞬间显得非常的高大上，让你的企业品牌在访客心中留下更深的影响。当然，我们在使用jquery特效的同时也要注意程序对搜索引擎的友好型，所以这一点儿作者也有考虑到，已经尽可能的对js和css脚本进行精简和优化，尽可能的加快网站加载速度，同时也
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的这个选择也无可厚非。
原文地址：一条Mysql上的Sql优化经历, 感谢原作者分享。
