mysql 索引优化一例

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

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

查询如下：

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;
    +---------+
    | amount |
    +---------+
    | 1271240 |
    +---------+
    1 row in set (1.94 sec)

EXPLAIN信息如下：

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;
+----+-------------+---------+-------+---------------+-----------------+---------+------+---------+--------------------------+
| id | select_type | table   | type  | possible_keys | key             | key_len | ref  | rows    | Extra                    |
+----+-------------+---------+-------+---------------+-----------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | smw_ids | index | NULL          | idx_smw_sortkey | 257     | NULL | 1274034 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

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

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;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | smw_ids | index | NULL          | PRIMARY | 8       | NULL | 1274034 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

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

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;
+---------+
| amount  |
+---------+
| 1271208 |
+---------+
1 row in set (0.77 sec)

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