索引大小对语句执行速度的影响

先看下面这条语句:
mysql> select count(*) from search_hash;
+----------+
| count(*) |
+----------+
| 1665476 |
+----------+
1 row in set (0.53 sec)

 

哇,执行的速度非常快,100多万行只用了不到0.5秒。explian分析一下执行计划。
mysql> explain select count(*) from search_hash;
+----+-------------+-------------+-------+---------------+-----------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+-----------+---------+------+---------+-------------+
| 1 | SIMPLE | search_hash | index | NULL | info_hash | 123 | NULL | 1670417 | Using index |
+----+-------------+-------------+-------+---------------+-----------+---------+------+---------+-------------+

 

这个表里有3条索引,我发现这条语句使用的是info_hash这条索引,为什么不用其他的索引呢?
mysql> show index from search_hash\G;
*************************** 1. row ***************************
Table: search_hash
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1670119
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: search_hash
Non_unique: 0
Key_name: info_hash
Seq_in_index: 1
Column_name: info_hash
Collation: A
Cardinality: 1670119
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: search_hash
Non_unique: 1
Key_name: ix_search_hash_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 1670119
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
3 rows in set (0.02 sec)

 

然后我试一下使用主键索引的效果。
mysql> select count(0) from search_hash force index (primary);
+----------+
| count(0) |
+----------+
| 1665495 |
+----------+
1 row in set (4.63 sec)

 

。。。。速度马上下降了10倍!是什么原因呢?先看一下执行计划:
mysql> explain select count(*) from search_hash force index (primary);
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | search_hash | index | NULL | PRIMARY | 4 | NULL | 1670477 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+

 

跟第一条语句没有什么不同,除了使用的索引不一样。那这两索引有什么不同呢?
mysql> desc search_hash;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| info_hash | varchar(40) | YES | UNI | NULL | |
| name | varchar(200) | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+----------------+

 

是不是索引越大,就越慢呢?为此我又用name列上的索引测试一下。
mysql> select count(*) from search_hash force index (ix_search_hash_name);
+----------+
| count(*) |
+----------+
| 1665690 |
+----------+
1 row in set (0.94 sec)


name列上的索引比infor_hash大,执行的时间也相对长一些。那有人可能会说id列长度明明小于info_hash列啊,为什么执行时间最长?
id列上的索引是主键索引,同时也是聚族索引,它包含了所有的列,所以三个索引中主键索引的体积是最大的,执行的时间也就最长。

 

总结:
1.同等情况下,索引的体积越大,可能执行的时间也就越长。
2.mysql优化器挺智能的嘛!

posted @ 2018-06-28 16:46  AYard  阅读(515)  评论(0编辑  收藏