Mysql索引失效原因详解（1）

一、联合索引为什么要遵循“最左前缀匹配原则”？

ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);

1.1 建立联合索引（col1,col2,col3），相当于建立了三个索引：
（col1）
（col1,col2）
（col1,col2,col3）

1）创建索引的时候，先按第一列排序，第一列相同再按第二列排序，第二列相同再按第三列排序（排序：建立b+树的过程，B+树是有序结构）；

2）如果没有前一列的参考，则后一列无法继续搜索，所以要“最左前缀匹配”；

1.2 字段都可以乱序，MySQL的查询优化器可以优化成索引识别的形式：

-- 只命中col1
SELECT * FROM `table_name` WHERE `col1`='XX';
-- 命中col1，col2 两列的顺序可以颠倒
SELECT * FROM `table_name` WHERE `clo1`='XX' and `clo2`='XXX'; 
SELECT * FROM `table_name` WHERE `clo2`='XXX' and `clo1`='XX';  
-- 命中col1,col2,col3 三列的顺可以颠倒
SELECT * FROM `table_name` WHERE `col1`='X' and `col2`='XX' and `col3`='XXX';
SELECT * FROM `table_name` WHERE `col1`='X' and `col3`='XX' and `col2`='XXX';
SELECT * FROM `table_name` WHERE `col2`='X' and `col3`='XX' and `col1`='XXX';

思考：

SELECT * FROM `table_name` WHERE `clo2`='XXX'

SELECT * FROM `table_name` WHERE `col1`='X' and `col3`='XXX';

这样的搜索会怎样？可用Explain优化器查看。

二、索引为什么不能用于%开头的模糊查询？

like有四种情况，分别为无%、 %% 、左%、右%

2.1 like 字段名

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j'; 

输出为：

图1

可以看出：
type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。

2.2 like %字段名%

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%'; 

输出为：

图2

可以看出：
type的值为ALL也就是全表扫描，而且key的值为NULL，也就是说没用到任何索引。

2.3 like %字段名

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j'; 

输出为：

图3

可以看出：
type的值为ALL，key的值为NULL，同样没用到索引。

2.4 like 字段名%

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%'; 

输出为：

图4

可以看出：
type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。

2.5 对于%开头的like查询，解决办法：

LOCATE（'substr',str,pos）
FIND_IN_SET(str1,str2)

三、索引为什么不能用于聚合函数？