mysql索引问题_MySQL索引问题一些总结

１、MySQL索引有Hash索引和B+ Tree索引，这两个相比有何异同？

首先要理解这两个数据结构：

B+树是为磁盘及其他存储辅助设备而设计一种平衡查找树(不是二叉树)。B+树中，所有记录的节点按大小顺序存放在同一层的叶节点中，各叶节点用指针进行连接。

哈希索引底层是哈希表，哈希表是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。多个数据在存储关系上是没有任何顺序关系的。

１)如果是等值查询，哈希索引会具有优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据。

２)哈希索引无法用于范围查询检索和多列联合索引的最左匹配原则。

３)如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

２、B+ Tree的叶子节点可以存放整行数据也可存放主键值，二者有何区别？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键值的是非主键索引(辅助索引)，也被称之为非聚簇索引。

３、聚簇索引和非聚簇索引，在查询数据的时候有何区别？

聚簇索引查询效率更高，这是因为主键索引的叶子节点直接就是数据，而非主键索引(辅助索引)的叶子节点存储的是主键值，通过该索引查询时要先找到主键值，在通过主键值查询到对应的数据(该过程叫回表)。

４、非主键索引一定会查询多次么？

不一定，如果是覆盖索引，就只可以查询一次。

覆盖索引指的是对于查询中使用的除去参与索引过滤扫描的所有字段将其加入到该查询所使用的索引尾部的索引。覆盖索引扫描的优点在于由于查询中所使用的所有字段都在同一索引的字段，因而在进行查询时只需要在索引中获取相关数据即可，而不需要回磁盘扫描相应的数据，从而避免了查询中最耗时的磁盘 I/O 读取。对于如下查询：

该查询中如果建立联合索引(a, b, c)，那么这就是使用了覆盖扫描的索引，因为对于该查询，可以使用索引的前两个字段 a 和 b 根据 where 条件进行索引片的过滤，对过滤后的索引片直接在索引中读取 a, b, c 三个字段的值即可，而无需回表扫描。

５、使用联合索引时多发个字段顺序如何选择？

将识别度最高的字段放在最前面，这样做是因为最左前缀匹配。

在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了(key1)、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

６、什么情况下会使索引失效？

１)where语句中使用”!=”(“<>”)不等于操作符，会进行全表扫描。MySQL只有对以下操作符才使用索引：，>=，BETWEEN，IN，以及不是以通配符(%或者_)开头的LIKE。

２)where语句中使用”or”来连接条件。

例如

这种情况会进行全表扫描，此时可以利用”UNION”合并查询：

３)where语句中使用参数、字段表达式以及函数也会导致全表扫描

４)联合索引违反了最左前缀原则

假设有索引 Index(A,B,C):

使用索引对结果进行排序，需要索引的顺序和 ORDER BY 子句中的顺序一致，并且所有列的升降序一致(ASC/DESC)。如果查询连接了多个表，只有在 ORDER BY 的列引用的是第一个表才可以(需要按序 JOIN)。

Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；