删除索引
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` = 3369231\G; *************************** 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` = 3369231\G; *************************** 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 说明优化确实起到了作用。
建议指数：五颗星
