MYSQL or 导致索引失效

Mysql版本 5.6.50

新建表：

CREATE TABLE ttt (
id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
a mediumint(8) unsigned NOT NULL DEFAULT ‘0’,
b mediumint(8) unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id),
KEY idx_a (a) USING BTREE,
KEY idx_b (b) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0

查询语句：

explain format = json select id from ttt where a = 173 or b = 173;

虽然a和b上都有索引，但是：

{ “query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “1.60”
},
“table”: {
“table_name”: “ttt”,
“access_type”: “ALL”,
“possible_keys”: [
“idx_a”,
“idx_b”
],
“rows_examined_per_scan”: 3,
“rows_produced_per_join”: 1,
“filtered”: “55.56”,
“cost_info”: {
“read_cost”: “1.27”,
“eval_cost”: “0.33”,
“prefix_cost”: “1.60”,
“data_read_per_join”: “26”
},
“used_columns”: [
“id”,
“a”,
“b”
],
“attached_condition”: “((test1.ttt.a = 173) or (test1.ttt.b = 173))”
} } }

可以看到没有走索引

三、调整索引如下：

增加联合索引： KEY idx_a_b (a,b) USING BTREE

结果：

{
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “1.60”
},
“table”: {
“table_name”: “ttt”,
“access_type”: “index”,
“possible_keys”: [
“idx_a”,
“idx_b”,
“idx_a_b”
],
“key”: “idx_a_b”,
“used_key_parts”: [
“a”,
“b”
],
“key_length”: “6”,
“rows_examined_per_scan”: 3,
“rows_produced_per_join”: 1,
“filtered”: “55.56”,
“using_index”: true,
“cost_info”: {
“read_cost”: “1.27”,
“eval_cost”: “0.33”,
“prefix_cost”: “1.60”,
“data_read_per_join”: “26”
},
“used_columns”: [
“id”,
“a”,
“b”
],
“attached_condition”: “((test1.ttt.a = 173) or (test1.ttt.b = 173))”
}
}
}

使用了索引！