mysql 索引优化一例

909413335

在实际工作中遇到的问题。smw_namespace没有索引，但是对它做了表分区。
索引信息如下：

1. mysql> show index from smw_ids;
   
2. +---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
   
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
   
4. +---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
   
5. | smw_ids | 0 | PRIMARY | 1 | smw_id | A | 1284866 | NULL | NULL | | BTREE | |
   
6. | smw_ids | 0 | PRIMARY | 2 | smw_namespace | A | 1284866 | NULL | NULL | | BTREE | |
   
7. | smw_ids | 1 | smw_title | 1 | smw_title | A | 1284866 | 50 | NULL | | BTREE | |
   
8. | smw_ids | 1 | idx_smw_sortkey | 1 | smw_sortkey | A | 1284866 | NULL | NULL | | BTREE | |
   
9. +---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
   
10. 4 rows in set (0.57 sec)

查询如下：

1. mysql> select /* twGetTermTotalCount 127.0.0.1 */ count(*) AS amount from smw_ids where smw_namespace between 1000 and 2000 and smw_namespace mod 2=0;
   
2.     +---------+
   
3.     | amount |
   
4.     +---------+
   
5.     | 1271240 |
   
6.     +---------+
   
7.     1 row in set (1.94 sec)

EXPLAIN信息如下：

1. mysql> explain select /* twGetTermTotalCount 127.0.0.1 */ count(*) AS amount from smw_ids where smw_namespace between 1000 and 2000 and smw_namespace mod 2=0;
   
2. +----+-------------+---------+-------+---------------+-----------------+---------+------+---------+--------------------------+
   
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
   
4. +----+-------------+---------+-------+---------------+-----------------+---------+------+---------+--------------------------+
   
5. | 1 | SIMPLE | smw_ids | index | NULL | idx_smw_sortkey | 257 | NULL | 1274034 | Using where; Using index |
   
6. +----+-------------+---------+-------+---------------+-----------------+---------+------+---------+--------------------------+
   
7. 1 row in set (0.00 sec)

莫名其妙，为什么会用到idx_smw_sortkey这个索引？？忽略掉这个索引看看。

1. mysql> explain select /* twGetTermTotalCount 127.0.0.1 */ count(*) AS amount from smw_ids IGNORE INDEX(idx_smw_sortkey) where smw_namespace between 1000 and 2000 and smw_namespace mod 2=0;
   
2. +----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
   
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
   
4. +----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
   
5. | 1 | SIMPLE | smw_ids | index | NULL | PRIMARY | 8 | NULL | 1274034 | Using where; Using index |
   
6. +----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
   
7. 1 row in set (0.00 sec)

这时候用到了主索引。因为对smw_namespace做了分区，所以它包含在主索引中。
查询效率提高不少

1. mysql> select /* twGetTermTotalCount 127.0.0.1 */ count(*) AS amount from smw_ids IGNORE INDEX(idx_smw_sortkey) where smw_namespace between 1000 and 2000 and smw_namespace mod 2=0;
   
2. +---------+
   
3. | amount |
   
4. +---------+
   
5. | 1271208 |
   
6. +---------+
   
7. 1 row in set (0.77 sec)

mysql 自己的优化器某些时候还是不那么“聪明的”，优化的时候要多思考。