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