IT博客汇 | 一条Mysql上的Sql优化经历

一条Mysql上的Sql优化经历

朝阳发表于 2008-05-23 18:32:51

前段时间一位同事收到某开发部门一位同事求助，希望帮忙优化一条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的这个选择也无可厚非。