索引优化

1、避免聚簇索引页分裂

聚簇索引优势:根据主键查询条目比较少时,不用回行(数据就在主键节点下)
聚簇索引劣势:如果碰到不规则数据插入时,造成频繁的页分裂
原则:
聚簇索引的主键值,应尽量是连续增长的整型值,而不是要是随机值
比如:不要用随机字符串或UUID,否则会造成大量的页分裂与页移动
例如:int(10) unsigned NOT NULL AUTO_INCREMENT

2、不能在where条件常用的列上都加上索引

例:where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
误:cat_id上,和, price上都加上索引。
错:只能用上cat_id或Price索引。
原因:因为是两个针对整表的独立索引,所以同时只能用上1个。

3、多列索引-左前缀

建立多列的联合索引,必须满足左前缀原则才能生效,中间不能断,断线后面部分不能使用索引。
以 index(a,b,c) 为例:

语句 索引是否发挥作用
Where a=3 是,只使用了a列
Where a=3 and b=5 是,使用了a,b列
Where a=3 and b=5 and c=4 是,使用了abc
Where b=3 / where c=4
Where a=3 and c=4 a列能发挥索引,c不能
Where a=3 and b>10 and c=7 a能利用,b能利用, c不能利用
Where a=3 and b like ‘xxxx%’ and c=7 a能用,b能用,c不能用

(1)理解说明:
索引是按照索引定义的顺序来进行使用,也就是右边的索引使用的前提是左边的索引查询必须使用等号(能唯一确定一个值),如果是>,<或者like ‘xxx’的话找到的是一个区间,所以后面的索引无法使用。
(2)经典案例
假设某个表有一个联合索引(c1,c2,c3,c4),判断索引使用情况:

A     where c1=x and c2=x and c4>x and c3=x
B     where c1=x and c2=x and c4=x order by c3
C     where c1=x and c4= x group by c3,c2
D     where c1=x and c5=x order by c2,c3
E     where c1=x and c2=x and c5=? order by c2,c3

测试解析如下:

// 创建一个表
create table t4 (
    c1 tinyint(1) not null default 0,
    c2 tinyint(1) not null default 0,
    c3 tinyint(1) not null default 0,
    c4 tinyint(1) not null default 0,
    c5 tinyint(1) not null default 0,
    index c1234(c1,c2,c3,c4)
);
// 插入数据
insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);

// 解析如下:
// 对于A:c1,c2,c3,c4四列都可以用上
c1=x and c2=x and c4>x and c3=x  
等价于 c1=x and c2=x and c3=x and c4>x
explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3;

 // 对于B:c1,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序,而c4没发挥作用
explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3;

 // 对于C:只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
explain select * from t4 where c1=1 and c4=2 group by c3,c2;
// 分组统计首先是按分组字段有序排列,首先按c2,c3排序,由于c1使用索引,且c2,c3有序,因此不会使用临时表,也不会使用文件排序。
explain select * from t4 where c1=1 and c4=2 group by c2,c3;

 // 对于D:c1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3的索引发挥的排序的作用,没用到filesort。总的来说c1使用了索引
explain select * from t4 where c1=1 and c5=2 order by c2,c3;

 // 对于E:因为c2的值既是固定的,参与排序时并不考虑,c1,c2,c3用上了,c1,c2用于基础,c3用于排序。等价于
explain select * from t4 where c1=1 and c2=3 and c5=2 order by c3;;

4、对于左前缀不易区分的列,另辟蹊径

比如:url列http://www.baidu.com 列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决。
(1)把列内容倒过来存储,并建立索引
moc.udiab.www//:ptth 这样左前缀区分度大
(2)采用伪hash
同时存储url和crc32(url)列,然后给crc列建立索引
原因:rc的结果是32位int无符号数,因此当数据超过40亿,也会有重复,但这是值得的。(索引长度为int 4个字节)

5、索引覆盖

索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.,这种查询速度非常快,称为”索引覆盖”。
优点:
( 1)索引条目通常比数据行小,只需要读取索引,所以会访问更少的数据。
(2)索引是按照列值的大小顺序存储的,对于随机访问记录,需要更少的I/O。
(3)大多数据引擎能更好的缓存索引,比如MyISAM只缓存索引
(4)索引覆盖对于InnoDB表尤其有用,因为InnoDB使用聚集索引组织数据,二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。
注意:
(1)索引覆盖也并不适用于任意的索引类型,索引必须存储列的值
(2)Hash 和full-text索引不存储值,因此MySQL只能使用B-TREE
(3)不同的存储引擎实现覆盖索引都是不同的
(4)并不是所有的存储引擎都支持它们
(5)如果要使用覆盖索引,一定要注意SELECT 列表值取出需要的列,不可以是SELECT *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降,不能为了利用覆盖索引而这么做。

6、优化索引与排序

排序可能发生2种情况:
(1)对于覆盖索引,直接在索引上查询时,就是有顺序的,using index
在innodb引擎中,沿着索引字段排序,也是自然有序的。
对于myisam引擎,如果按某索引字段排序,如id,但取出的字段中,有未索引字段,如goods_name,myisam的做法,不是索引->回行,而是先取出所有行,再进行排序。
(2)先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)。
理想目标
取出来的数据本身就是有序的,利用索引来排序,尽量不出现using filesort。

7、避免重复索引与适当冗余索引

重复索引
重复索引是指在同1个列(如age),或者顺序相同的几个列(age,school), 建立了多个索引称为重复索引。
重复索引没有任何帮助,只会增大索引文件,拖慢更新速度,必须去掉。
冗余索引
冗余索引是指2个索引所覆盖的列有重叠,,称为冗余索引。
比如 x,m,列, 加索引 index x(x), index xm(x,m),x,xm索引,两者的x列重叠了, 这种情况,称为冗余索引。
甚至可以把 index mx(m,x) 索引也建立mx, xm 也不是重复的,因为列的顺序不一样。

8、理想索引

(1)查询频繁
(2)区分度高
(3)长度小
(4)尽量能覆盖常用查询字段
区分度与长度
由于索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。所以针对列中的值,从左往右截取部分,来建索引。
(1)截的越短, 重复度越高,区分度越小,索引效果越不好
(2)截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大(增删改变慢,并间影响查询速度)
所以,我们要在 区分度 + 长度 两者上,取得一个平衡。
惯用手法:
截取不同长度,并测试其区分度
对于一般的系统应用:区别度能达到0.1,索引的性能就可以接受

select count(distinct left(word,6))/count(*) from dict;

9、定期索引碎片与维护

在长期的数据更改过程中,索引文件和数据文件,都将产生空洞,形成碎片。
通过以下两种方式来修复损坏的表:

alter table xxx engine innodb

optimize table 表名

注意:
(1)修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐。这个过程,如果表的行数比较大,也是非常耗费资源的操作。所以,不能频繁的修复。
(2)如果表的Update操作很频率,可以按周/月,来修复。如果不频繁,可以更长的周期来做修复,比如半年或年。