亚洲av成人无遮挡网站在线观看,少妇性bbb搡bbb爽爽爽,亚洲av日韩精品久久久久久,兔费看少妇性l交大片免费,无码少妇一区二区三区

Chinaunix

標(biāo)題: mysql 索引優(yōu)化一例 [打印本頁(yè)]
作者: 909413335    時(shí)間: 2011-12-21 08:42
標(biāo)題: mysql 索引優(yōu)化一例
在實(shí)際工作中遇到的問(wèn)題。smw_namespace沒(méi)有索引，但是對(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í)候要多思考。












歡迎光臨 Chinaunix (http://www.72891.cn/) Powered by Discuz! X3.2