mysql优化-建立索引技巧

索引覆盖

索引覆盖是指，如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据。这种查询速度非常快，称为“索引覆盖”。

理想的索引

1. 查询频繁2. 区分度高3. 长度小4. 尽量能覆盖常用的查询字段索引的长度直接影响索引文件的大小，影响增删查改的速度，并间接影响查询速度（占用内存多）。针对列中的值，从左往右截取部分，来建索引一. 截的越短，重复度越高，区分度越小，索引效果越不好二. 截的越长，重复度越低，区分度越高，索引效果越好，但增删查改慢，并间接影响查询速度。所以要在区分度+长度上，取得一个平衡。惯用手法：截取不同长度，并测试其区分度。

伪hash索引

对于左前缀不易区分的列，如url列，http://www.baidu.com,http://www.qq.com。列的前11个字符都是一样的，不易区分。可以用如下的办法来解决：1. 伪hash索引    利用mysql函数crc32(exp),把url转换成一个数值型存放，对转换后的列做索引2. 把列的内容倒过来存储，并建立索引    moc.qq.www//:ptth    moc.udiab.www//:http

索引与排序

排序可能发生2种情况1. 对于覆盖索引，直接在索引上查询时，就是有顺序，using index2. 先取出数据，形成临时表做filesort(文件排序，可能在内存，可能在磁盘)我们争取目标，取出来的数据本身就是有序的。利用索引来排序。

重复索引

重复索引是指在同一个列，或顺序相同的几个列，建立了多个索引，称为重复索引。重复索引没有任何帮助，只会增大索引文件，拖慢更新速度。

冗余索引

冗余索引是指2个索引所覆盖的列有重叠，称为冗余索引。比如x，y列，加索引index x（x）、index xy(x,y)。两者的x列重叠了，这种情况称为冗余索引。

索引碎片与维护

在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，行成碎片。我们可以通过一个nop操作(不产生对数据实质影响的操作)来修改表。比如，表的引擎为innodb，可以alter table XXX engine innodb。optimize table 表名，也可以修复。注意：修复表的数据及索引碎片，就会把所用的数据文件重新整理一遍，使之对其。如果表的行数比较大，分成耗费资源操作。所以，不能频繁的修复。