mysql建立高效的索引实例分析

　　

本文实例讲述了mysql建立高效的索引。分享给大家供大家参考,具体如下:

　　

<>强如何建立理想的索引?

　　
    　　<李>查询频繁度 　　<李>区分度 　　<李>索引长度 　　<李>覆盖字段李 　　
　　

<强>区分度

　　

假设100年万用户,性别基本上男/女各为50 w区分度就低。

　　

<强>长度小

　　

索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。

　　

<强>区分度高,长度小

　　

问题:如果让区分度高,而长度小?

　　

答:可以针对列中的值,从左往右截取部分,来建索引

　　

(1)截的越短,重复度越高,区分度越小,索引效果越不好
　　(2)截的越长,重复度越低,区分度越高,索引效果越好,但带来的影响也越大——增删改变慢,并间影响查询速度。

　　

所以,我们要在区分度+长度两者上,取得一个平衡。惯用手法:截取不同长度,并测试其区分度。

　　

假设我们有一张表:英语4级的单词表,里面有13324条记录,我们怎么给的名字字段加索引呢?

　　

mysql建立高效的索引实例分析

　　

如果计算区分度?

　　

截取单词第1位的不重复数:

　　 　　 　　选择计数(不同的左(名字,1))从dict类型 　　之前 　　 　　

总的数量:

　　 　　 　　从dict select count (*) 　　之前 　　 　　

区分度:不重复数/总的数量,sql语句如下:

　　 　　 　　选择(选择计数(不同的左(名字,1))从dict)/(select count(*)从dict)率; 　　 　　之前 　　 　　

然后按照这样的步骤把其他长度所对应的区分度给找出来,看一个这个图表,可以知道当长度为11的时候重复度仅仅为1%,我们可以考虑建立11位长的索引

　　

mysql建立高效的索引实例分析

　　 　　 　　alter table dict添加索引名称名称(11); 　　 　　之前 　　 　　

<强>左前缀不好区分的情况

　　

对于左前缀不易区分的列,建立索引的技巧

　　

如url列 　　

　　

列的前11个字符都是一样的,不易区分,可以用如下2个办法来解决

　　

(1)把列内容倒过来存储,并建立索引

　　

　　

这样左前缀区分度大

　　

(2)伪哈希索引效果

　　

同时存url和url_hash列

　　 　　 　　#建表 　　创建表t10 ( 　　int id主键, 　　url char (60) not null默认” 　　); 　　#插入数据 　　插入t10值 　　(1,' http://www.baidu.com '), 　　(2,' http://www.sina.com '), 　　(3,' http://www.sohu.com.cn '), 　　(4,' http://www.onlinedown.net '), 　　(5,' http://www.gov.cn '); 　　#修改表结构,添加urlcrc列 　　alter table t10添加urlcrc int unsigned not null; 　　 　　之前 　　 　　

在存储的时候,将url对应的crc32码一同插入到数据库中,然后按照urlcrc字段建立索引,然后查找的时候,我们在业务层中将对应的url转换为crc32进行查找,就可以利用上索引了。

　　

因为crc的结果是32位整型无符号数,因此当数据超过40个亿,也会有重复,但这是值得的。(索引长度为int4个字节)

　　

<强>多列索引

　　

多列索引的考虑因素,列的查询频率,列的区分度,注意一定要结合实际业务场景

　　

以ecshop商城为例,产品表中的cat_id, brand_id,做多列索引,从区分度看,brand_id区分度更高,但从商城的实际业务业务看,顾客一般先选大分类→小分类→品牌,最终选择建立2个索引:

　　

(1)指数(cat_id brand_id)
　　(2)指数(cat_id shop_price)

　　

甚至可以再加(3)指数(cat_id, brand_id shop_price), 3个冗余索引

　　

但(3)中的前2列和(1)中的前2列一样,所以可以再去掉(1),建立2个索引

　　

<代码>指数(cat_id价格)和<代码>指数(cat_id, brand_id shop_price);

　　

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》,《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

　　

希望本文所述对大家MySQL数据库计有所帮助。

mysql建立高效的索引实例分析