8、mysql索引
8、mysql索引
一、索引的数据结构
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
MyISAMB+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也使用B+Tree作为索引结构
第一个重大区别是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的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
下一章将具体讨论这些与索引有关的优化策略。
二、什么是覆盖索引
覆盖索引是一种避免回表查询的优化策略: 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
具体的实现方式:
- 将被查询的字段建立普通索引或者联合索引，这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。
EXPLAIN SELECT user_name,user_age,user_level FROM users
WHERE user_name = 'tom' AND user_age = 17;
覆盖索引的定义与注意事项:
如果一个索引包含了 所有需要查询的字段的值 (不需要回表)，这个索引就是覆盖索引。
MySQL只能使用B+Tree索引做覆盖索引 (因为只有B+树能存储索引列值)
在explain的Extra列, 如果出现 **
Using index表示 使用到了覆盖索引 , 所取的数据完全在索引中就能拿到
三、索引哪些情况下会失效情况
查询条件包含 or，会导致索引失效。
隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们 where age = “1”，这样就会触发隐式类型转换
like 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
对索引字段进行函数运算。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
索引字段上使用 is null， is not null，可能导致索引失效。
相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
mysql 估计使用全表扫描要比使用索引快，则不使用索引。
四、索引查询的机制
4.1、聚簇索引的查询
将数据和索引放在一起，查到索引就可以查到数据
4.2、非聚簇索引
非聚簇索引放的是索引值和主键值，查到索引值然后根据索引对应的主键值再次查询id键值，这个叫做回表操作，会慢一点
五、索引的一些特点
索引下推
当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录； 在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。 如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；自适应索引
1、自适应即我们不需要自己处理，当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点，就会给其建立一个hash索引； 2、hash索引底层的数据结构是散列表（Hash表），其数据特点就是比较适合在内存中使用，自适应Hash索引存在于InnoDB架构中的缓存中（不存在于磁盘架构中），见下面的InnoDB架构图。 3、自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'，而对于其他查找类型，如范围查找，是不能使用的；
思考
- 根据索引的数据结构思考为什么是最左前缀原则，和%会使索引失效
- 聚簇索引和非聚簇索引