MySQL 索引失效的一个示例

最新推荐文章于 2024-03-07 17:02:48 发布

blackcat王文俊

最新推荐文章于 2024-03-07 17:02:48 发布

阅读量217

点赞数

分类专栏：程序代码文章标签： sql mysql

本文链接：https://blog.csdn.net/blackcat123/article/details/126811252

版权

程序代码专栏收录该内容

17 篇文章 0 订阅

订阅专栏

本文分析了针对特定SQL查询，使用不同索引(osx_introducer_relation_ir_uid_IDX, ir_create_at_IDX, ir_uid_IDX, ir_uid_IDX w/o ir_create_at)所带来的查询优化效果。重点讲解了索引选择对查询性能的影响及适用场景。

摘要由CSDN通过智能技术生成

查询语句：

explain format=json
select
	count(*) as total_count
from
	osx_introducer_relation as o_i_r
where
	ir_create_at >= '1637915380'
	and ir_create_at<'1637915399'
	and ir_uid = '1229'
	and ir_del_at = 0 ;

情形一：

索引 KEY `osx_introducer_relation_ir_uid_IDX` (`ir_uid`,`ir_create_at`) USING BTREE

输出结果

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "o_i_r",
      "access_type": "range",
      "possible_keys": [
        "osx_introducer_relation_ir_uid_IDX"
      ],
      "key": "osx_introducer_relation_ir_uid_IDX",
      "used_key_parts": [
        "ir_uid",
        "ir_create_at"
      ],
      "key_length": "51",
      "rows": 1,
      "filtered": 100,
      "index_condition": "((`osx1`.`o_i_r`.`ir_create_at` >= 1637915380) and (`osx1`.`o_i_r`.`ir_create_at` < 1637915399) and (`osx1`.`o_i_r`.`ir_uid` = '1229'))",
      "attached_condition": "(`osx1`.`o_i_r`.`ir_del_at` = 0)"
    }
  }
}

情形二：

索引 KEY `osx_introducer_relation_ir_create_at_IDX` (`ir_create_at`,`ir_uid`) USING BTREE

输出结果

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "o_i_r",
      "access_type": "range",
      "possible_keys": [
        "osx_introducer_relation_ir_create_at_IDX"
      ],
      "key": "osx_introducer_relation_ir_create_at_IDX",
      "used_key_parts": [
        "ir_create_at"
      ],
      "key_length": "51",
      "rows": 1,
      "filtered": 100,
      "index_condition": "((`osx1`.`o_i_r`.`ir_create_at` >= 1637915380) and (`osx1`.`o_i_r`.`ir_create_at` < 1637915399) and (`osx1`.`o_i_r`.`ir_uid` = '1229'))",
      "attached_condition": "(`osx1`.`o_i_r`.`ir_del_at` = 0)"
    }
  }
}

情形三：

索引 KEY `osx_introducer_relation_ir_uid_IDX` (`ir_uid`,`ir_create_at`,`ir_del_at`) USING BTREE

输出结果

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "o_i_r",
      "access_type": "range",
      "possible_keys": [
        "osx_introducer_relation_ir_uid_IDX"
      ],
      "key": "osx_introducer_relation_ir_uid_IDX",
      "used_key_parts": [
        "ir_uid",
        "ir_create_at"
      ],
      "key_length": "59",
      "rows": 1,
      "filtered": 100,
      "using_index": true,
      "attached_condition": "((`osx1`.`o_i_r`.`ir_del_at` = 0) and (`osx1`.`o_i_r`.`ir_create_at` >= 1637915380) and (`osx1`.`o_i_r`.`ir_create_at` < 1637915399) and (`osx1`.`o_i_r`.`ir_uid` = '1229'))"
    }
  }
}

情形四：

索引 KEY `osx_introducer_relation_ir_uid_IDX` (`ir_uid`) USING BTREE

输出结果

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "o_i_r",
      "access_type": "ref",
      "possible_keys": [
        "osx_introducer_relation_ir_uid_IDX"
      ],
      "key": "osx_introducer_relation_ir_uid_IDX",
      "used_key_parts": [
        "ir_uid"
      ],
      "key_length": "43",
      "ref": [
        "const"
      ],
      "rows": 3,
      "filtered": 100,
      "index_condition": "(`osx1`.`o_i_r`.`ir_uid` = '1229')",
      "attached_condition": "((`osx1`.`o_i_r`.`ir_del_at` = 0) and (`osx1`.`o_i_r`.`ir_create_at` >= 1637915380) and (`osx1`.`o_i_r`.`ir_create_at` < 1637915399))"
    }
  }
}