반응형
마리아답:내 쿼리에서 인덱스를 사용하지 않는 이유는 무엇입니까?
이 쿼리가 인덱스를 사용하지 않는 이유를 파악하려고 합니다(그래서 매우 깁니다).
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 | | |
+---------------------------------+------------+------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
내가 뭘 잘못하고 있지?쿼리 또는 데이터베이스 구조를 최적화하려면 어떻게 해야 합니까?
정말 감사해요.
쿼리가 반환되는 행 수에 따라 mysql은 인덱스를 사용하는 대신 전체 스캔을 사용하는 것이 더 효율적이라고 추정할 수 있습니다.
원인을 좁히려면 where 구를 지정하지 않고 쿼리를 실행하여 인덱스가 사용되고 있는지 확인합니다.그런 다음 where 절을 하나씩 추가하여 어느 절이 인덱스를 사용하지 않는지 확인합니다.Where 절이 너무 일반적일 수 있으므로 인덱스를 사용하는 대신 전체 테이블 검색을 수행하는 것이 더 효율적입니다.
연령으로 인해 인덱스가 사용되지 않는 경우 contact_id + age에 인덱스를 생성할 수 있습니다.
에 참가하고 있습니다.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)
언급URL : https://stackoverflow.com/questions/48719673/mariadb-why-my-queries-is-not-using-the-indexes
반응형
'programing' 카테고리의 다른 글
| Java Mail을 사용하여 첨부 파일 다운로드 (0) | 2022.10.21 |
|---|---|
| Matplotlib에서 그림 제목 및 축 레이블 글꼴 크기를 설정하려면 어떻게 해야 합니까? (0) | 2022.10.21 |
| PHP - 개체 배열에서 속성 추출 (0) | 2022.10.21 |
| Java 동기 스태틱메서드: 객체 또는 클래스에 대한 잠금 (0) | 2022.10.21 |
| Javascript를 사용하여 통화 문자열을 더블로 변환하는 방법은 무엇입니까? (0) | 2022.10.21 |