mysql 字首索引 的一些介紹

markzy5201190發表於2013-01-29
MySql索引
mysql 字首索引 的一些介紹

大家都知道在CHAR和VARCHAR列上，可以使用列的字首進行索引，如：INDEX (name(10),address(20)) 但是到底第幾位是optimal length 呢
http://www.shinguz.ch/MySQL/mysql_hints.html的一部分介紹如下：
For CHAR, VARCHAR, BINARY, and VARBINARY columns, 
indexes (called prefixed indexes) can be created that use only part of a column, 
using col_name(length) syntax to specify an index prefix length.
These indexes are shorter and thus safe space (on disk and in memory) 
and can be faster than non prefixed indexes.
But shortening indexes can reduce cardinality(=(select count (DISTINCT 
my_column )FROM my_table)計算方法) of an index and is thus worse.
With this statement you can find out the optimal length of an prefixed index. 
Optimal means close than or equal cardinality to the full index.
SELECT COUNT(DISTINCT LEFT(my_column, )) card FROM my_table;

Let's assume that we have an index on my_column VARCHAR(32) 
with a cardinality of 1142 we can say after some trials (increasing n from 1 to ...),
that a prefixed index with more than 6 characters length does NOT make sense 
with the present data (except when we retrieve the data from the 
index only (index look up)).
例如：
+---+-------+
| n | card  |
+---+-------+
| 4 |  258  |
| 5 |  741  |
| 6 | 1142  |
+---+-------+

（索引佔用空間數的計算：
Let's assume, that we have approx. 
1 Mio rows in this table with an utf8 character set (3 bytes per character) 
then the original index has a size of 97 Mio bytes (1 Mio x (1 + 3 x 32)). 
But our prefixed index has a size of only 19 Mio bytes (1 Mio x (1 + 3 x 6)). 
This is a gain of approx 80% of space (and also some performance)!）

通過上面的介紹，寫一儲存來求optimal length

/* 簡單判斷 字首索引取的是列的前幾位 使用方法：call prefixindex('表名','列名')；select @leng； */
DELIMITER $$
CREATE  PROCEDURE prefixindex(IN name1 varchar(20),IN name2 varchar(20)) 
BEGIN
DECLARE i int default 1;
select name1 into @tablesname;
select name2 into @columnname;
set @stmt=CONCAT('select  count(DISTINCT ',@columnname,') into @cardinality from ',  @tablesname);
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
label_1: while i>0
do
set @leng=i;
set @stmt=CONCAT('select count(DISTINCT LEFT(',@columnname,',',@leng,')) into @cardinality1 from ',  @tablesname);
prepare s2 from @stmt;
execute s2;
deallocate prepare s2;
if @leng=1 then
select abs(@cardinality1-@cardinality) into @diffvalue1;
else 
select abs(@cardinality1-@cardinality) into @diffvalue2;
if @diffvalue1>@diffvalue2 or @diffvalue1>3 /*自己確定的精度，可以讓更接近cardinality*/
then 
set @diffvalue1=@diffvalue2;
else 
leave label_1;
end if;
end if;
set i=i+1;
end while;
set @stmt='';
END$$
DELIMITER ;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26855487/viewspace-753526/，如需轉載，請註明出處，否則將追究法律責任。

相關文章