MySQL 刪除數據時何時會走聯合索引?

MySQL 刪除數據時何時會走聯合索引?

mysql 刪除數據時走索引的條件

在一個用戶表中有字段 id、name、age、sex、work 和 city,聯合索引為 (sex, city)。現在要刪除 sex=男、city=北京 的數據,會不會使用聯合索引?

回答:

mysql 中,當涉及的數據量超過 20% 時,不會使用索引。因此,如果符合條件的數據量超過表總數據量的 20%,則不會走索引。

實踐驗證:

  • 表總數據量:1602 條
  • 符合條件(sex, city)條數:

    • 女,廣州:604 條
    • 女,惠州:6 條

執行刪除語句的 explain 結果:

  • 女,廣州:符合條件數據量超過 20%,不會使用索引。

    explain delete from  test_del_idx where sex="女" and city = "廣州";

    返回結果:

+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | extra       | +----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+ |  1 | delete      | test_del_idx | null       | all  | idx_sex_city  | null | null    | null | 1602 |   100.00 | using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • 女,惠州:符合條件數據量少于 20%,會使用索引。

    explain delete from  test_del_idx where sex="女" and city = "惠州";

返回結果:

+----+-------------+--------------+------------+-------+---------------+--------------+---------+-------------+------+----------+-------------+ | id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref         | rows | filtered | Extra       | +----+-------------+--------------+------------+-------+---------------+--------------+---------+-------------+------+----------+-------------+ |  1 | DELETE      | test_del_idx | NULL       | range | idx_sex_city  | idx_sex_city | 773     | const,const |    6 |   100.00 | Using where | +----+-------------+--------------+------------+-------+---------------+--------------+---------+-------------+------+----------+-------------+

? 版權聲明
THE END
喜歡就支持一下吧
點贊10 分享