mysql 索引優(yōu)化一例

909413335

在實(shí)際工作中遇到的問題。smw_namespace沒有索引，但是對(duì)它做了表分區(qū)。
索引信息如下：

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)

查詢?nèi)缦拢?br>

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)

莫名其妙，為什么會(huì)用到idx_smw_sortkey這個(gè)索引？？忽略掉這個(gè)索引看看。

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)

這時(shí)候用到了主索引。因?yàn)閷?duì)smw_namespace做了分區(qū)，所以它包含在主索引中。
查詢效率提高不少

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 自己的優(yōu)化器某些時(shí)候還是不那么“聰明的”，優(yōu)化的時(shí)候要多思考。