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 `ttt_a_IDX` (`a`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
查询语句:
explain format = json select id from ttt where a = 173 or b = 173;
结果:
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "ttt",
"access_type": "ALL",
"possible_keys": [
"ttt_a_IDX"
],
"rows": 1,
"filtered": 100,
"attached_condition": "((`test`.`ttt`.`a` = 173) or (`test`.`ttt`.`b` = 173))"
}
}
}
可以看到没有走索引
二、变更索引如下:
KEY `ttt_a_IDX` (`a`) USING BTREE,
KEY `ttt_b_IDX` (`b`) USING BTREE
结果:
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "ttt",
"access_type": "ALL",
"possible_keys": [
"ttt_a_IDX",
"ttt_b_IDX"
],
"rows": 1,
"filtered": 100,
"attached_condition": "((`test`.`ttt`.`a` = 173) or (`test`.`ttt`.`b` = 173))"
}
}
}
可以看到,依然没有走索引
三、调整索引如下:
KEY `ttt_a_IDX` (`a`,`b`) USING BTREE
结果:
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "ttt",
"access_type": "index",
"possible_keys": [
"ttt_a_IDX"
],
"key": "ttt_a_IDX",
"used_key_parts": [
"a",
"b"
],
"key_length": "6",
"rows": 1,
"filtered": 100,
"using_index": true,
"attached_condition": "((`test`.`ttt`.`a` = 173) or (`test`.`ttt`.`b` = 173))"
}
}
}
使用了索引