mysql 索引

1索引的意义：

优点：索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时必须从第一个记录开始扫描表中所有记录，表里面的记录数量越多，这个操作的代价就越高。

     缺点：索引要占用磁盘空间；且任何写操作涉及的索引个数多的话会引起降速，因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。

以下就对索引：Index Test (col1,col2,col3) 进行分析，如有不准确之处，欢迎大家拍砖。

2 从左至右的正向索引匹配（第一行是索引可用场景，第二行是索引不可用场景）

右边的适用

where col1=$para1;

where col1=$para1and col2=$para2;

where col1=$para1 and col2=$para2 and col3=$para3;

右边的不适用

where col2=$para2;

where col1=$para1and col3=$para3;

where col2=$para2 and col3=$para3;

 

 

 

3 like与索引有效性的关系（第一行是索引可用场景，第二行是索引不可用场景）

右边的适用

where col1 like ‘aaa%’;

where col1 like ‘aaa%’ and col2 like ‘bbb%’ ;

where col1 like ‘aa%a%’ and col2 like ‘bb%b%’ ;

右边的不适用

where col1 like ‘%aaa’;  (通配符在前，无确定范围)

where col1 like ‘%aaa’ and col2 like ‘%bbb’ ;

where col1 like ‘%aa%a’ and col2 like ‘%bb%b’ ;

 

 

 

4 or、and与索引有效性的关系（第一行是索引可用场景，第二行是索引不可用场景）

右边的适用

where col1=$para1and col2=$para2;

where col1=$para1or col1=$para11; (同一column)

where col1=$para1 or col1=$para11 and col2=$para2;

右边的不适用

where col1=$para1or col2=$para2;  (无确定范围)

where col1 like ‘%aaa’ or col2 like ‘%bbb’ ;

where col1=$para1 or col2=$para2 and col3=$para3;

 

 

 

5 其他mysql内置函数与索引有效性的关系(以col1举例) （第二列是可用场景，第三列是不可用场景）

函数名

可用场景

不可用场景

替代办法

SUBSTRING

--

SUBSTRING（col1,1,2）

不可用，拆分了sql字段

拆离成新的sql字段存储，或考虑用like

LENGTH

--

LENGTH(col1)

不可用，重构为新的sql字段

用“<”、“<=”、“>=”、“>”、BETWEEN AND

DATE_FORMAT

--

DATE_FORMAT(col1,’%Y%m%d’);

不可用，拆分了sql字段

直接对datetime字段进行比较，

比如到天： col1 >  ‘20090619235959’  

比如到小时： col1 >  ‘20090619010000’ and  col1 <  ‘20090619020000’

某一小时内，可以用like：col1 like ‘2009-06-19 01%’，能命中索引

FROM_UNIXTIME

--

FROM_UNIXTIME (col1);

不可用，重构为新的sql字段

舍弃 from_unixtime，直接对col1索引

IN

存在索引则可用，col1 in ($para1,$para11)相当于 col1=$para1 or  col1=$para11

--

--

GROUP BY

存在索引则可用,如

where col1=$para1and col2=$para2 group by col1;

--

--

ORDER BY

存在索引则可用, 可与and组合，

如 where col1=$para1and col2=$para2 order by col3;

但实际此时col3有没有加到索引里影响不大

--

--

“<”、“<=”、“ = ”、“>=”、“>”、BETWEEN AND

存在索引则可用，col1 BETWEEN $para1AND $para11等同于col1 >= $para1 AND col1<= $para11

--

--

 

ps:

效率顺序可以看type字段结果，效率排序如下（红色是我们比较常用的连接类型）：

system< const < eq_ref < ref < unique_subquery <  index_subquery < range < index < all

 

找到一篇还不错的关于explain用法的帖，可以看一下

http://hi.baidu.com/suthagol/blog/item/93cc76587b6a2a84810a184d.html