mysql数据库索引优化策略

• 索引分类
• 索引常规策略
• 索引技巧
• 索引查看

索引分类

1.myisam,innodb表用的都是B-tree索引,都是"排好序的快速查找结构".
2.memory表用的是hash索引
3.优缺点:
(1)hash索引的查询理论的时间复杂度是0,但是无法对范围查询进行优化,无法对排序进行优化,必须回行取数据,无法i用前缀索引
(2)B-tree可以利用做前缀进行优化

B-tree索引细分之聚簇索引与非聚簇索引
1.myisam(非聚簇索引)
(1)myisam主索引和次索引都指向数据的物理行(数据在磁盘上的位置)
(2)特点:

• 可以根据索引一次回行获得所有磁盘上的数据
• 插入数据时除了更新磁盘上的数据还得更新索引的指向

2.innodb(聚簇索引)
(1)innodb主键索引上直接存放该行数据,成为聚簇索引,次索引指向对主索引的引用
(2)如果没有主键则会用unique key做主键,如果连unique key也没有系统会生成一个rowid来做主键
(3)innodb的主键尽量用整型而且是递增型的整型
(4)特点:

• 索引顺序与表中记录的物理顺序一致
• 表中只能建立一个聚簇索引但这个索引可以建立多个列
• 对经常要对范围值查找的特别有效找到第一个值后便可确保后续索引值的行在物理相邻
• 对经常排序的字段聚簇也是有好处的
• 当主键是无规律的数据时会造成频繁的页分裂和页移动影响插入速度
• 插入数据时根据索引找到数据页,然后挪动已有数据或拆分数据页为新数据腾出空间插入数据

索引常规策略

1.根据where条件建立多列的联合索引,根据左前缀匹配规则
2.查询的时候尽量写要查的列这样查询的列如果正好是索引的一部分就会用到索引覆盖
3.理想的索引要考虑(1)查询频繁(2)区分度高(3)长度小(4)尽量能覆盖常用查询字段

索引技巧

1.索引和排序
索引覆盖的情况下直接再索引上查询,就是有序的,利用索引来排序
2.重复索引和冗余索引
重复索引:在一个列上或者顺序相同的几个列上建立多个索引,重复索引是没有好处的还会拖慢跟新速度要去掉
冗余索引:两个或多个索引所覆盖的列有重复,冗余索引有利于查询
3.索引碎片与维护
修复表的数据和索引文件
(1) alter table XXX engine innodb/myisam
(2)optimize table 表名
注:修复表的数据及索引碎片,就会吧所有的数据文件整理一遍,使之对齐这个过程中如果表的行数比较大也是很耗资源的,所以不能频繁的修复
4.左前缀不容易区分的列建立索引
1.对于做前缀不容易区分的列建立索引的时候可以把内容倒过来存储如:url http://baidu.com
2.建立伪哈希索引,存储url_hash列
5.多列索引联合索引
1.列的查询频率和区分度的考虑(索引长度越长,所占用空间越多,区分度越高,两者要考虑一个均衡)
2.满足做前缀匹配
6.延迟关联
利用inner join 内层的查询利用id的做索引覆盖先查出id在关联查询,这样通过id来查找行的的过程呗延后了,叫做延迟关联—大数据分页原理
7.索引查看
用explain来查看执行计划
重要参数说明:
table:所查询的表名或者标的别名如果是null 表示通过计算就可以获得不用查表如myisam表查询select count(*) from xxx 时候
key:最终用的索引
key_len:使用索引的最大长度
type:查询方式
(1)all 从第一行开始逐行做全表扫描(2)index 扫面所有的索引(3)range 根据索引做范围的扫描(4)ref根据索引列可以直接获得某些数据行(5)eq_ref根据索引列可以直接引用到某一行数据(6)const,system,null值优化到了常量级别甚至不需要查询时间,一般根据主键来查询一行容易出现const,system直接根据表达式不经过表时容易出现null如myisam表查询用max,min,count查询所有行时并且不加where条件时
rows:查询估计要扫描多少行
extra:
(1)index查询用到了覆盖索引,效率非常高(2)using where 光靠索引定位不了还得where判断一下(3)using temporary 查询用到了临时表(4)using filesort 用到了文件排序