Wednesday, September 8, 2010

MySQL性能优化7 - 索引选择性

索引的 “ 选择性 ” 是指在该索引列里存储不同值的数目和记录数的比。比如某个表的记录数是 1000 条,而该表的索引列的值只有 900 个不同的值(有 100 个是相同或是空)。这样索引的可选择性为 900/1000 为 0.9 。这样当然效果就不好,最好的索引可选择性(如主键索引)是 1.0 。索引的可选择性是衡量索引的利用率的方法,比如在极端的情况下,一个表记录数是 1000 ,而索引列的值只有 10 个不同的值,则索引的可选择性很差(只有 0.01 )。这样的情形使用全表扫描要比采用索引还好。

简单的举例如下:

[cc lang="mysql"]mysql> desc select count(*) from t_user where Fuser_id =100 \G;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using index
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> desc select Fstatus from t_user where Fstatus=1 \G;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_user
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6553179
Extra: Using where

1 row in set (0.00 sec)

mysql> select count(distinct Fstatus) from t_user;
+-------------------------+
| count(distinct Fstatus) |
+-------------------------+
| 6 |
+-------------------------+

1 row in set (11.85 sec)[/cc]

通过上面的实例可以看出,主键索引性能最佳,只扫描 1 行,而对于同表的 Fstatus 字段,目前只有 6 个值,所以 Fstatus 字段不适合创建索引,因为它的索引选择性很低,创建索引后根据 Fstatus 字段的某个值进行查询,其执行计划也将是全表扫描。

创建索引前最简单也是最有必要做的一件事,就是检查索引的选择性,通常多数用户喜欢直接创建索引而忽略索引的选择性检查,这样的后果是创建索引后性能未有明显改善,反而增加了插入和修改数据时索引的维护成本。

No comments:

Post a Comment