查詢忽略索引導致的全表掃描

我在 mysql 5.7 中有兩個來自 django 的表，auth_user並且account_account：

CREATE TABLE `auth_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(150) NOT NULL,
 `first_name` varchar(30) NOT NULL,
 `last_name` varchar(30) NOT NULL,
 `email` varchar(75) NOT NULL,
 `password` varchar(128) NOT NULL,
 `is_staff` tinyint(1) NOT NULL,
 `is_active` tinyint(1) NOT NULL,
 `is_superuser` tinyint(1) NOT NULL,
 `last_login` datetime DEFAULT NULL,
 `date_joined` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `username` (`username`),
 KEY `is_staff` (`is_staff`)
) ENGINE=InnoDB AUTO_INCREMENT=4438 DEFAULT CHARSET=utf8;

CREATE TABLE `account_account` (
 `user_ptr_id` int(11) NOT NULL,
 `org_form` smallint(5) unsigned DEFAULT NULL,
 `org_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `contact_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `balance` decimal(15,2) NOT NULL,
 `total_debt` decimal(15,2) NOT NULL,
 `contact_email` varchar(255) NOT NULL,
 `phone` varchar(255) NOT NULL,
 `mob_phone` varchar(255) NOT NULL,
 `other_phone` varchar(255) NOT NULL,
 `delivery_methods` smallint(5) unsigned NOT NULL,
 `delivery_address` longtext NOT NULL,
 `payment_method` smallint(5) unsigned NOT NULL,
 `person_type` smallint(5) unsigned NOT NULL,
 `client_type` smallint(5) unsigned NOT NULL,
 `city` smallint(5) unsigned NOT NULL DEFAULT '1',
 `patronymic` varchar(255) NOT NULL,
 `can_delay` tinyint(1) NOT NULL,
 `status` smallint(5) unsigned NOT NULL,
 `block_reason` varchar(255) NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `manager_id` int(11) DEFAULT NULL,
 `wholesale_level_id` int(11) DEFAULT NULL,
 `markup_type` smallint(5) unsigned NOT NULL,
 `org_markup_level_id` int(11) DEFAULT NULL,
 `use_vat` tinyint(1) NOT NULL,
 `last_order_date` datetime NOT NULL,
 `group_id` int(11) DEFAULT NULL,
 `is_partner` tinyint(1) NOT NULL,
 `partner_markup` decimal(15,2) DEFAULT NULL,
 `call_markup` decimal(15,2) DEFAULT NULL,
 `delivery_price` decimal(15,2) DEFAULT NULL,
 `city_id` int(11) NOT NULL,
 `unloading_at` time NOT NULL,
 `site` varchar(200),
 `ip_address_id` varchar(20) DEFAULT NULL,
 `ip_restriction` tinyint(1) NOT NULL,
 `visualisation_id` int(11),
 `_express_delivery_price` int(10) unsigned DEFAULT NULL,
 `is_express_delivery` tinyint(1) NOT NULL,
 `payment_type` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`user_ptr_id`),
 KEY `account_account_501a2222` (`manager_id`),
 KEY `account_account_7c811b3c` (`wholesale_level_id`),
 KEY `group_id_refs_id_35dd1116` (`group_id`),
 KEY `city_id_refs_id_96652cf` (`city_id`),
 KEY `account_account_ip_address_id_e42ae8c3_uniq` (`ip_address_id`),
 KEY `account_ac_visualisation_id_cd5729f6_fk_account_visualisation_id` (`visualisation_id`),
 CONSTRAINT `account_ac_visualisation_id_cd5729f6_fk_account_visualisation_id` FOREIGN KEY (`visualisation_id`) REFERENCES `account_visualisation` (`id`),
 CONSTRAINT `account_accou_ip_address_id_e42ae8c3_fk_account_ipadress_address` FOREIGN KEY (`ip_address_id`) REFERENCES `account_ipadress` (`address`),
 CONSTRAINT `account_account_city_id_2ab1a62f_fk_classifiers_city_id` FOREIGN KEY (`city_id`) REFERENCES `classifiers_city` (`id`),
 CONSTRAINT `group_id_refs_id_35dd1116` FOREIGN KEY (`group_id`) REFERENCES `account_clientgroup` (`id`),
 CONSTRAINT `user_ptr_id_refs_id_17b5ed9e` FOREIGN KEY (`user_ptr_id`) REFERENCES `auth_user` (`id`),
 CONSTRAINT `wholesale_level_id_refs_id_369bcb86` FOREIGN KEY (`wholesale_level_id`) REFERENCES `classifiers_wholesalelevel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在嘗試查詢：

explain     SELECT  `auth_user`.`id`, `account_account`.`contact_name`, `account_account`.`contact_email`,
           `account_account`.`phone`
       FROM  `account_account`
       INNER JOIN  `auth_user`  ON (`account_account`.`user_ptr_id` = `auth_user`.`id`)
       WHERE  (`auth_user`.`is_staff` = 0)
       LIMIT  16;

+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table           | partitions | type   | possible_keys    | key     | key_len | ref                               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+
|  1 | SIMPLE      | account_account | NULL       | ALL    | PRIMARY          | NULL    | NULL    | NULL                              | 3741 |   100.00 | NULL        |
|  1 | SIMPLE      | auth_user       | NULL       | eq_ref | PRIMARY,is_staff | PRIMARY | 4       | raros.account_account.user_ptr_id |    1 |   100.00 | Using where |
+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+

它表明即使我們不對帳戶進行任何過濾，account_account 也會進行完整掃描，僅加入。你能向我解釋兩件事嗎：

1. 為什麼mysql不使用is_staff索引進行初始過濾？我們有兩個索引auth_user：主要和is_staff
2. 為什麼即使我們不對account_account錶進行任何過濾，mysql也會對account_account進行全掃描？

提前致謝！

它可能是您擁有它的最佳方式。

首先註意ALLand 3741 RowsinEXPLAIN不一定有意義。這是因為在列出此類數據時EXPLAIN很少注意到。LIMIT

讓我們假設它確實這樣做ALL：

1. 掃描大約 3741 行的整個表。以任何順序執行此操作，因為沒有任何東西可以限制掃描（例如ORDER BY）。
2. 對於這些行中的每一行，請訪問另一個表。
3. 到達auth_user通過PRIMARY KEY是非常有效的。
4. 檢查is_staff。如果它通常是0，那麼一切都很好。如果很少0，那麼就會浪費很多精力。
5. 16 行後停止。因此，根據is_staff，這需要查看 16 到 3741 行。

添加一個ORDER BY; 那麼讓我們重新解釋一下。

引用自：https://dba.stackexchange.com/questions/287103