Mariadb:为什么我的查询没有使用索引？
Mariadb:为什么我的查询没有使用索引？
提问于 2018-02-10 18:23:08
我试图弄清楚为什么这个查询没有使用索引(所以它很长)。
explain select count(*) as aggregate
from `megabase_sms_data_1`
left join `megabase_sms_thematic_repulse_1`
on `megabase_sms_data_1`.`contact_id` = `megabase_sms_thematic_repulse_1`.`megabase_data_id`
where not (megabase_sms_thematic_repulse_1.thematic_id <=> 6)
and `age` in ('2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '-1');
+------+-------------+---------------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------------------+----------+-------------+
| 1 | SIMPLE | megabase_sms_data_1 | ALL | tmp_megabase_sms_data_1_age_index,tmp_megabase_sms_data_1_age_incomes_index,tmp_megabase_sms_data_1_age_housing_zipcode_sex_index,tmp_megabase_sms_data_1_age_zipcode_type_sex_index,tmp_megabase_sms_data_1_age_family_zipcode_sex_index,tmp_megabase_sms_data_1_age_couple_zipcode_sex_index,tmp_megabase_sms_data_1_age_department_sex_housing_index,tmp_megabase_sms_data_1_age_type_department_sex_index,tmp_megabase_sms_data_1_age_family_department_sex_index,tmp_megabase_sms_data_1_age_sex_couple_department_index | NULL | NULL | NULL | 20508191 | Using where |
| 1 | SIMPLE | megabase_sms_thematic_repulse_1 | ref | tmp_megabase_sms_thematic_repulse_1_megabase_data_id_index | tmp_megabase_sms_thematic_repulse_1_megabase_data_id_index | 8 | adsconsole.megabase_sms_data_1.contact_id | 1 | Using where |我试图将索引添加到contact_id上的表中，但它没有使用它。可能是我误解了一些概念。
我有这两张表：
DESCRIBE megabase_sms_data_1;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| contact_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sms_md5 | varchar(255) | NO | MUL | NULL | |
| sms | varchar(255) | NO | MUL | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
| sex | tinyint(4) | NO | MUL | NULL | |
| couple | tinyint(4) | NO | MUL | NULL | |
| family | tinyint(4) | NO | MUL | NULL | |
| type | tinyint(4) | NO | MUL | NULL | |
| housing | tinyint(4) | NO | MUL | NULL | |
| gather_date | date | NO | | NULL | |
| freshness_date | date | NO | | NULL | |
| zipcode | char(5) | NO | MUL | NULL | |
| department | char(2) | NO | MUL | NULL | |
| address | varchar(255) | NO | | NULL | |
| city | varchar(255) | NO | | NULL | |
| latitude | double | NO | | NULL | |
| longitude | double | NO | | NULL | |
| incomes | tinyint(4) | NO | MUL | NULL | |
| csp | tinyint(4) | NO | | NULL | |
| adr_iris | varchar(255) | NO | | NULL | |
| adr_inse | varchar(255) | NO | | NULL | |
+----------------+------------------+------+-----+---------+----------------+
21 rows in set (0.00 sec)
SHOW INDEX FROM megabase_sms_data_1;
+---------------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ---------------+
| megabase_sms_data_1 | 0 | PRIMARY | 1 | contact_id | A | 20508191 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 0 | tmp_megabase_sms_data_1_contact_id | 1 | contact_id | A | 20508191 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sms_md5_index | 1 | sms_md5 | A | 20508191 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sex_index | 1 | sex | A | 3 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_couple_index | 1 | couple | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_family_index | 1 | family | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_type_index | 1 | type | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_housing_index | 1 | housing | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_incomes_index | 1 | incomes | A | 10 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_incomes_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_incomes_index | 2 | incomes | A | 131 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_type_age_index | 1 | type | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_type_age_index | 2 | age | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_couple_age_index | 1 | couple | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_couple_age_index | 2 | age | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_family_age_index | 1 | family | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_family_age_index | 2 | age | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sex_age_index | 1 | sex | A | 3 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sex_age_index | 2 | age | A | 52 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_incomes_age_index | 1 | incomes | A | 10 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_incomes_age_index | 2 | age | A | 131 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_sex_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_sex_index | 2 | sex | A | 43266 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_age_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_age_index | 2 | age | A | 110259 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_couple_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_couple_index | 2 | couple | A | 37085 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_family_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_family_index | 2 | family | A | 38261 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_type_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_type_index | 2 | type | A | 38549 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_housing_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_housing_index | 2 | housing | A | 38841 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_sex_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_sex_index | 2 | sex | A | 396 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_age_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_age_index | 2 | age | A | 1393 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_couple_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_couple_index | 2 | couple | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_family_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_family_index | 2 | family | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_type_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_type_index | 2 | type | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_housing_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_housing_index | 2 | housing | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 2 | housing | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 3 | zipcode | A | 253187 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 4 | sex | A | 539689 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 2 | zipcode | A | 110259 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 3 | type | A | 256352 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 4 | sex | A | 554275 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 2 | family | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 3 | zipcode | A | 256352 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 4 | sex | A | 554275 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 2 | couple | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 3 | zipcode | A | 250099 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 4 | sex | A | 525851 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 2 | department | A | 1393 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 3 | sex | A | 5067 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 4 | housing | A | 12192 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 2 | type | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 3 | department | A | 3913 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 4 | sex | A | 12207 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 2 | family | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 3 | department | A | 3931 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 4 | sex | A | 11759 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 2 | sex | A | 52 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 3 | couple | A | 132 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 4 | department | A | 10868 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sms_index | 1 | sms | A | 6836063 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | contact_id | 1 | contact_id | A | 20508191 | NULL | NULL | | BTREE | | |第二个是：
describe megabase_sms_thematic_repulse_1;
+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| megabase_data_id | bigint(20) | NO | MUL | NULL | |
| thematic_id | int(11) | NO | MUL | NULL | |
+------------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
SHOW INDEX FROM megabase_sms_thematic_repulse_1;
+---------------------------------+------------+------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------------------+------------+------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| megabase_sms_thematic_repulse_1 | 1 | tmp_megabase_sms_thematic_repulse_1_megabase_data_id_index | 1 | megabase_data_id | A | 3 | NULL | NULL | | BTREE | | |
| megabase_sms_thematic_repulse_1 | 1 | tmp_megabase_sms_thematic_repulse_1_thematic_id_index | 1 | thematic_id | A | 1 | NULL | NULL | | BTREE | | |
+---------------------------------+------------+------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+我做错了什么？我能做些什么来优化查询或数据库结构？
非常感谢
回答 2
Stack Overflow用户
发布于 2018-02-10 18:32:24
根据查询返回的行数，mysql可能会估计使用完全扫描比使用索引更有效。
要缩小原因范围，请运行不带where子句的查询，并查看是否正在使用索引。然后逐个添加where子句，查看它们中的哪一个导致索引未被使用。where子句可能过于通用，这使得执行全表扫描比使用索引更有效。
如果年龄导致索引不被使用，您可以在contact_id + age上创建索引。
Stack Overflow用户
发布于 2018-02-10 18:38:40
您正在对megabase_sms_data_1.contact_id进行联接，并对megabase_sms_data_1.age进行筛选，因此您应该创建一个包含这两个列的索引，并且每个表只能使用一个索引。您当前可以支持联接或筛选器，但不能同时支持两者。数据库查询优化器仍然可以决定改用全表扫描。
要支持您的查询，请尝试：
alter table megabase_sms_data_1 add index test(contact_id, age)或
alter table megabase_sms_data_1 add index test(age, contact_id)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48719673
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号