???????????
????ALTER TABLE `happy_for_ni_label_links` DROP INDEX `idx_status_happy_for_ni_id_with_id`;
????ALTER TABLE `happy_for_ni_label_links` DROP INDEX `idx_status_checked_happy_for_ni_id_with_id`;
???????????
????ALTER TABLE `happy_for_ni_label_links`  ADD INDEX `idx_status_happy_for_ni_id_with_id` (happy_for_ni_id?? status?? id);
????Query OK?? 0 rows affected (3.52 sec)
????ALTER TABLE `happy_for_ni_label_links`  ADD INDEX `idx_status_checked_happy_for_ni_id_with_id` ( checked_happy_for_ni_id?? status?? id);
????Query OK?? 0 rows affected (3.57 sec)
???????????￡??????????????????????????
mysql> explain SELECT `happy_for_ni_labels`.`id`
->  FROM `happy_for_ni_labels`
->  INNER JOIN `happy_for_ni_label_links`
->   ON `happy_for_ni_labels`.`id` = `happy_for_ni_label_links`.`label_id` WHERE `happy_for_ni_label_links`.`happy_for_ni_id` = 3369231G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_label_links
type: ref
possible_keys: idx_label_id_with_id??idx_status_happy_for_ni_id_with_id
key: idx_status_happy_for_ni_id_with_id
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_labels
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: my_local_test.happy_for_ni_label_links.label_id
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
????????? key?? ref?? rows ??????????????????????????Ч??
???????????
?????????Щ????????????????????DDL????????????? 3.52 + 3.57 = 7.09 ????????????ALTER????????????????????????????????????????????????????????SELECT?????????????????ɡ????????????????????????????Щ??????
???????????????????
???????????????????????????????
???????????????????????????Cardinality?????????? idx_status_happy_for_ni_id_with_id
*************************** 2. row ***************************
Table: happy_for_ni_label_links
Non_unique: 1
Key_name: idx_status_happy_for_ni_id_with_id
Seq_in_index: 1
Column_name: status
Collation: A
Cardinality: 18
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: happy_for_ni_label_links
Non_unique: 1
Key_name: idx_status_happy_for_ni_id_with_id
Seq_in_index: 2
Column_name: happy_for_ni_id
Collation: A
Cardinality: 996079
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: happy_for_ni_label_links
Non_unique: 1
Key_name: idx_status_happy_for_ni_id_with_id
Seq_in_index: 3
Column_name: id
Collation: A
Cardinality: 996079
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
?????????????????ó???status ??????????? 18?? happy_for_ni_id ??????????? 996079?? id ??????????? 996079
??????????????????????????????????????檔
????????????????????????????????????????????????????????????????????????????????????Ч??????????????У?????????????????????????????????Σ??????????????CPU??????????????????Щ??
??????????????????status ??????????????????????????????
????????????????????????????μ?????????????? status ????????????檔??????????????????????????á?
????????
????KEY `idx_status_happy_for_ni_id_with_id` (`status`??`happy_for_ni_id`??`id`)
????????????????????????????
????1??WHERE happy_for_ni_label_links.status = xxx
????2??WHERE happy_for_ni_label_links.status = xxx AND  happy_for_ni_label_links.happy_for_ni_id = xxx
????3??WHERE happy_for_ni_label_links.status = xxx AND  happy_for_ni_label_links.happy_for_ni_id = xxx AND  happy_for_ni_label_links.id = xxx
??????????????SQL?????д??
mysql> explain select `happy_for_ni_labels`.`id` from `happy_for_ni_labels` inner join `happy_for_ni_label_links` on `happy_for_ni_labels`.`id` = `happy_for_ni_label_links`.`label_id` WHERE `happy_for_ni_label_links`.status = 0 AND `happy_for_ni_label_links`.`happy_for_ni_id` = 3369231G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_label_links
type: ref
possible_keys: idx_status_happy_for_ni_id_with_id??idx_status_checked_happy_for_ni_id_with_id??idx_label_id_with_id
key: idx_status_happy_for_ni_id_with_id
key_len: 5
ref: const??const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: happy_for_ni_labels
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: my_local_test.happy_for_ni_label_links.label_id
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
ERROR:
No query specified
????key ??  idx_xx_tag_id_with_id ???  idx_status_happy_for_ni_id_with_id??
????ref????NULL????????????????????const?? ????Ч???????????????
????????????????? 461??1872 ??????????  1??1 ???????????????á?
??????????????????