MYSQL 索引失效的 情况

1.索引无法存储null值

　　a.单列索引无法储null值，复合索引无法储全为null的值。

　　b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。

　　 为什么索引列无法存储Null值？

　　a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值无法比较，无法确定null出现在索引树的叶子节点位置。）　

　　b.如果需要把空值存入索引，方法有二：其一，把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找。其二，建立一个复合索引。例如　

　　create index ind_a on table(col1,1);  通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。　

　　

2.不适合键值较少的列（重复数据较多的列）

　　如果mysql估计使用全表扫描要比使用索引快,则不使用索引

       假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。

　　再加上访问索引块，一共要访问大于200个的数据块。

　　如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块

　　少一些，肯定就不会利用索引了。

3.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

　　假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件，由于前面是

　　模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫

　　描。如果是这样的条件where code like 'A % '，就可以查找CODE中A开头的CODE的位置，当碰到B开头的

　　数据时，就可以停止查找了，因为后面的数据一定不满足要求。这样就可以利用索引了。

4.使用关键字 or

　　如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

5.不遵从复合索引 最左原则

　　对于多列索引，不是使用的第一部分，则不会使用索引

       比如：Index_SoftWareDetail索引包含（a，b，c） 三列，但是查询条件里面，没有a，只有b c 列，那么 Index_SoftWareDetail索引也不起作用。

起作用：a   ab  abc  

半作用：ac  (确定到a, 中间有 b 字段的断层，c 条件不起作用)

不起作用： b   c    bc

 

 

6.隐性数据转换

　　如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

7.对索引列计算

　　如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

     （1）判断索引列是否不等于某个值时。‘!=’操作符。比如：select * from SoftWareDetailInfo where SoftUseLine != 0

     （2）这里运算包括+-*/等运算。。比如：

　　select * from SoftWareDetailInfo where SoftUseLine +0= 0

　　此时索引不起作用。

       （3）使用函数 如：

　　            select * from SoftWareDetailInfo where count(SoftUseLine) = 0

　　  综合：也就是说如果不是直接判断索引字段列，而是判断运算或其它函数处理后的索引列索引均不起作用。

         

8. mysql in 关键字内 子查询不起作用 

IN子查询无法使用所以，场景是MySQL，截止的版本是5.7.18

测试表如下  ：

1 2 3 4 5 6 7

create table test_table2 (   id int auto_increment primary key,   pay_id int,   pay_time datetime,   other_col varchar(100) )

建一个存储过程插入测试数据，测试数据的特点是pay_id可重复，这里在存储过程处理成，循环插入300W条数据的过程中，每隔100条数据插入一条重复的pay_id，时间字段在一定范围内随机

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)   LANGUAGE SQL   NOT DETERMINISTIC   CONTAINS SQL   SQL SECURITY DEFINER   COMMENT '' BEGIN   declare cnt int;   set cnt = 0;   while cnt< loopcount do     insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand())day),uuid());     if (cnt mod 100 = 0) then       insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand())day),uuid());     end if;     set cnt = cnt + 1;    end while; END

　　执行 call test_insert(3000000); 插入303000行数据

两种子查询的写法

查询大概的意思是查询某个时间段之内的业务Id大于1的数据，于是就出现两种写法。

第一种写法如下：IN子查询中是某段时间内业务统计大于1的业务Id，外层按照IN子查询的结果进行查询，业务Id的列pay_id上有索引，逻辑也比较简单，这种写法，在数据量大的时候确实效率比较低，用不到索引

1 2 3 4 5 6 7 8

select * from test_table2 force index(idx_pay_id) where pay_id in ( 　　select pay_id from test_table2 　　where pay_time>="2016-06-01 00:00:00" 　　　　AND pay_time<="2017-07-03 12:59:59" 　　group by pay_id 　　having count(pay_id) > 1 );

执行结果：2.23秒

 

第二种写法，与子查询进行join关联，这种写法相当于上面的IN子查询写法，下面测试发现，效率确实有不少的提高

1 2 3 4 5 6 7 8 9 10

select tpp1.* from test_table2 tpp1, (    select pay_id    from test_table2    WHERE pay_time>="2016-07-01 00:00:00"    AND pay_time<="2017-07-03 12:59:59"    group by pay_id    having count(pay_id) > 1 ) tpp2 where tpp1.pay_id=tpp2.pay_id

　　执行结果：0.48秒

第一种写法：In子查询的执行计划，发现外层查询是一个全表扫描的方式，没有用到pay_id上的索引

第二种写法：join自查的执行计划，外层（tpp1别名的查询）是用到pay_id上的索引的。

        后面想对第一种查询方式使用强制索引，虽然是不报错的，但是发现根本没用，注意：如果子查询是直接的值，则是可以正常使用索引的。另外：加一个使用临时表的情况，虽然比不少join方式查询的，但是也比直接使用IN子查询效率要高，这种情况下，也是可以使用到索引的，不过这种简单的情况，是没有必要使用临时表的。

　　下面是类似案例在sqlserver 2014中的测试，几万完全一样的测试表结构和数量，可见这种情况下，两种写法，在SQL Server中可以认为是完全一样的（执行计划+效率），这一点SQL Server要比MySQL强不少

 　下面是sqlserver中的测试环境脚本。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

create table test_table2 (   id int identity(1,1) primary key,   pay_id int,   pay_time datetime,   other_col varchar(100) ) begin tran declare @i int = 0 while @i<300000 begin   insert into test_table2 values (@i,getdate()-rand()*300,newid());   if(@i%1000=0)   begin     insert into test_table2 values (@i,getdate()-rand()*300,newid());   end   set @i = @i + 1 end COMMIT GO create index idx_pay_id on test_table2(pay_id); create index idx_time on test_table2(pay_time); GO select * from test_table2 where pay_id in (           select pay_id from test_table2           where pay_time>='2017-01-21 00:00:00'           AND pay_time<='2017-07-03 12:59:59'           group by pay_id           having count(pay_id) > 1         ); select tpp1.* from test_table2 tpp1, (    select pay_id    from test_table2    WHERE pay_time>='2017-01-21 00:00:00'    AND pay_time<='2017-07-30 12:59:59'    group by pay_id having    count(pay_id) > 1 ) tpp2 where tpp1.pay_id=tpp2.pay_id

总结：在MySQL数据中，截止5.7.18版本，对IN子查询，仍要慎用

 

5.MySQL主要提供2种方式的索引：B-Tree索引，Hash索引

　　B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。

　　哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。

　　显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。

　　如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

 

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

 

 

2. B-Tree索引 

      B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree 索引的存储结构在数据库的数据检 索中有非常优异的表现。 
      一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree ，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个 
Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。 
      在 Innodb 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（ Primary Key ），另外一种则是和其他存储引擎（如 MyISAM 存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 Innodb 存储引擎中被称为 Secondary Index 。下面我们通过图示来针对这两种索引的存放 
形式做一个比较。 

     

      图示中左边为 Clustered 形式存放的 Primary Key ，右侧则为普通的 B-Tree 索引。两种 Root Node 和 Branch Nodes 方面都还是完全一样的。而 Leaf Nodes 就出现差异了。在 Prim中， Leaf Nodes 存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据据以主键值有序的排列。而 Secondary Index 则和其他普通的 B-Tree 索引没有太大的差异，Leaf Nodes 出了存放索引键 的相关信息外，还存放了 Innodb 的主键值。 

      所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过 Secondary Index 来访问数据的话， Innodb 首先通过 Secondary Index 的相关信息，通过相应的索引键检索到 Leaf Node之后，需要再通过 Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空 的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在 Leaf Nodes 上面出了存放索引键信息之外，再存放能直接定位到 MyISAM 数据文件中相应的数据行的信息（如 Row Number ），但并不会存放主键的键值信息