5、mysql优化--索引使用情况、索引的结构

避免索引失效

　　1). 全值匹配 ，对索引中所有列都指定具体值。

　　2). 最左前缀法则

　　　　如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

 　　3). 范围查询右边的列，不能使用索引 。

　　 4). 不要在索引列上进行运算操作， 索引将失效。
　　　　

 

 

 　　5). 字符串不加单引号，造成索引失效。

　　　　　　由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

 　　6). 尽量使用覆盖索引，避免select * ,尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *  

　　　　　　如果查询列，超出索引列，也会降低性能。

　　7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

 

　　8). 以%开头的Like模糊查询，索引失效。

　　　　如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

　　

 

　　　　解决方案 ：

　　　　　　通过覆盖索引来解决

 

 

　　9). 如果MySQL评估使用索引比全表更慢，则不使用索引。

 

　　10). is NULL ， is NOT NULL 有时索引失效。 （根据表的数据数决定，为null的比较多 is null不走索引，is NOT NULL走索引）

　　11). in 走索引， not in 索引失效。

　　12). 单列索引和复合索引。尽量使用复合索引，而少使用单列索引 。、

　　　　数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

　　 

TIP : 
    
    using index ：使用覆盖索引的时候就会出现

    using where：在查找使用索引的情况下，需要回表去查询所需的数据

    using index condition：查找使用了索引，但是需要回表查询数据

    using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

 

　　

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

 

 

　　这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。

　　可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

 

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

 

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

　　　　第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

　　　　而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

 

 

图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

 

 

 

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

　　了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

 

总结：

　　1、MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

　　2、InnoDB主键索引，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。辅助索引叶节点是保存主键数据。

　　3、因为辅助索引叶节点是保存主键数据，辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。