mysql索引

索引:

explain select * from TableName where ...    //分析语法

show index from TableName  \G;    //查看表中索引

Show profiles;   //查看效率(默认是关闭的,开启:setprofiling=1)

 

----------------------------------------------------------------------------------------------------------------------------

普通索引:

create index 索引名 on 表名(column(length))    //直接创建索引

create table 表名(   //创建表的时候同时创建索引

'column' ... ,

index 索引名(column(length))

)

alter table 表名add index 索引名 (column(length))  //修改表结构的方式添加索引

alter table 表名drop index 索引名 //删除索引

drop index 索引名 on 表名     //删除索引

 

全文索引:

create fulltext index 索引名 on 表名(columb)  //创建全文索引

create table 表名(    //创建表的时候同时创建索引

'column' ... ,

fulltext(column)

)

alter table 表名 add fulltext 索引名(column)   //修改表结构的方式添加索引

alter table 表名 drop index 索引名    //删除索引

 

唯一索引:

create unique index 索引名 on 表名 (column(length))    //创建唯一索引

create table 表名(     //创建表的时候同时创建索引

'column' ... ,

unique 索引名(column(length))

)

alter table 表名 add unique 索引名 (column(length))  //修改表结构的方式添加索引

alter table 表名 drop index 索引名   //删除索引

 

主键索引:

create table 表名(    //创建表的时候同时创建索引

'id' ... ,

primary key('id')

)

create table 表名(    //创建表的时候同时创建索引

'id' ...primary key

)

alter table 表名 add primary key(column)   //修改表结构的方式添加索引

alter table表名 drop primary key  //删除索引

 

 

---------------------------------------------------------------------------------------------------------------------------

索引优化策略:理想的索引

1:查询频繁  2:区分度高  3:长度小  4: 尽量能覆盖常用查询字段.

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

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

1: 截的越短, 重复度越高,区分度越小, 索引效果越不好

2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.

 

索引类型

B-tree索引

 Myisam,innodb中,默认用B-tree索引

  抽象一下---B-tree系统,可理解为”排好序的快速查找结构”. 

 

hash索引

     在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)

 

聚簇索引与非聚簇索引

Myisam与innodb引擎,索引文件的异同

myisam中, 主索引和次索引,都指向物理行(磁盘位置).

innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用

 

注意:对于 innodb来说

1: 主键索引 既存储索引值,又在叶子中存储行的数据

2: 如果没有主键, 则会Unique key做主键

3: 如果没有unique,则系统生成一个内部的rowid做主键.

4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

 

聚簇索引

优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

 

高性能索引策略

对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.

对于innodb的主键,尽量用整型,而且是递增的整型.

如果是无规律的数据,将会产生的页的分裂,影响速度.

 

索引覆盖:

索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.

这种查询速度非常快,称为”索引覆盖”

 

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

如 url列

http://www.baidu.com

http://www.zixue.it

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

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

Moc.udiab.www//:ptth

Ti.euxiz.www//://ptth

这样左前缀区分度大,

2: 伪hash索引效果

同时存 url_hash列,将url列转成整型"可以使用crc32(String)"

 

索引与排序

排序可能发生2种情况:

1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, usingindex

2: 先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)

我们的争取目标-----取出来的数据本身就是有序的! 利用索引来排序.

 

比如: goods商品表, (cat_id,shop_price)组成联合索引,

where cat_id=Norder by shop_price ,可以利用索引来排序,

select goods_id,cat_id,shop_price fromgoods order by shop_price;

// using where,按照shop_price索引取出的结果,本身就是有序的.

 

select goods_id,cat_id,shop_price fromgoods order by click_count;

// using filesort 用到了文件排序,即取出的结果再次排序

 

 

重复索引与冗余索引

重复索引: 是指在同1个列(如age), 或者顺序相同的几个列(age,school), 建立了多个索引,

称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.

 

冗余索引:

冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引

比如 x,m,列   , 加索引  index x(x), index xm(x,m)

x,xm索引, 两者的x列重叠了,  这种情况,称为冗余索引.

甚至可以把 index mx(m,x) 索引也建立, mx, xm 也不是重复的,因为列的顺序不一样.

 

 

索引碎片与维护

在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.

我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.

比如: 表的引擎为innodb , 可以 alter table 表名engine innodb

 

optimizetable 表名, 也可以修复.

 

注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.

这个过程,如果表的行数比较大,也是非常耗费资源的操作.

所以,不能频繁的修复.

 

如果表的Update操作很频率,可以按周/月,来修复.

如果不频繁,可以更长的周期来做修复.