Mysql索引

1、什么是索引

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据

2、索引采用的哪种数据结构

常见的MySQL主要有两种结构：Hash索引和B+ Tree索引

3、Hash索引和B+ Tree索引有什么区别

  不同的引擎对于索引有不同的支持：Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。
  Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;
  B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;
  为什么不都用Hash索引而使用B+树索引？
1、范围区别
  hash索引因为底层是哈希表，数据存储在哈希表中顺序是没有关联的，经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样，所以他不适合范围查找，如果要范围查找就需要全表扫描，他只适合全值扫描；简单的来说就是hash索引适合等值查找，不适合范围查找
  B+ tree树索引，底层是多路查询平衡树，节点是天然有序的（左节点小于服节点，右节点大于父节点），所以对于范围查找的时候不需要做全表扫描；
2、联合索引
  Hash索引不能利用部分索引键查询，对于联合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用；
  Btree索引支持联合索引，遵循最左匹配原则：
mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

• 如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
• 如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

3、排序
  哈希索引没办法利用索引完成排序
4、重复键值
  在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

4、聚簇索引和非聚簇索引的区别

  在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引
  对于聚集索引，表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。叶子结点即存储了真实的数据行，不再有另外单独的数据页，所以查询速度比较快。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
  对于非聚集索引，表数据存储顺序与索引顺序无关。叶子结点包含索引字段值及指向数据页数据行的逻辑指针，所以需要查询多次（回表）？，其行数量与数据表行数据量一致。

5、非聚簇索引并不一定会查询多次→覆盖索引

  覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
  是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。
  当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。

  当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。
  不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引
  当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息

6、Index Condition Pushdown（索引下推）

  MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下：

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;
  如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。
  如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

7、查询优化器

  有时会发生明明创建了索引，但是执行的时候并没有通过索引呢？
  一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。
  在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。
  这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

1. 根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个