Mysql前缀索引
来源:互联网 发布:域名绑定时出现未解析 编辑:程序博客网 时间:2024/06/03 02:38
应用场景:
数据库里有个地址(address)字段,类型为varchar(100),业务决定了要经常根据address来进行查询。
确定选择性:
Sql代码
SELECT count(DISTINCT(address))/count(*) AS Selectivity FROM info;
+-------------+
| Selectivity |
+-------------+
| 0.8745 |
+-------------+
<address>选择性很好,但是长度为100,对整个字段建立索引显然不合适,可以考虑建立前缀索引,例如<left(address,5)>,看看其选择性:
Sql代码
SELECT count(DISTINCT(left(address,5)))/count(*) AS Selectivity FROM info;
+-------------+
| Selectivity |
+-------------+
| 0.5981 |
+-------------+
选择性还不错,但和0.8745相比还是太低,所以我们可以将前缀长度增加到10,再看看选择性:
Sql代码
SELECT count(DISTINCT(left(address,10)))/count(*) AS Selectivity FROM info;
+-------------+
| Selectivity |
+-------------+
| 0.8239 |
+-------------+
大家都知道在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 on
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, <n>)) 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 da
例如:
+---+-------+
| 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 on
通过上面的介绍,写一存储来求optimal length
/* 简单判断 前缀索引取的是列的前几位 使用方法:call prefixindex('表名','列名');select @leng; */
DELIMITER $$
DROP PROCEDURE IF EXISTS `prefixindex`$$
CREATE DEFINER=`root`@`localhost` 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 ;
- mysql 前缀索引
- 认识mysql前缀索引
- MySQL 前缀索引
- Mysql前缀索引
- mysql前缀索引
- mysql前缀索引
- MYSQL前缀索引
- mysql前缀索引
- Mysql前缀索引
- mysql前缀索引及其选择
- MySQL前缀索引长度方法
- MySQL前缀索引和索引选择性
- mysql前缀索引的索引选择性
- MySQL前缀索引和索引选择性
- MySQL索引之前缀索引和索引选择性
- MySQL索引之前缀索引和索引选择性
- Mysql中的联合索引、前缀索引、覆盖索引
- MySQL索引之前缀索引和索引选择性
- boost optional
- 工程实践中最常用的数据结构与算法
- Stack
- Java ZXing 生成二维码学习
- Ubuntu中安装vsftp服务
- Mysql前缀索引
- Linux文件夹文件创建、删除
- 024
- 心念在烟雨中飘逸
- 黑马视频学习笔记-FOUNDATION-结构体
- iframe输入红色字体,不影响原来内容
- Ubuntu中安装tftp服务
- Java程序员面试体会,还没找到工作的“猿猿们”看过来!
- LeetCode 034 Search for a Range