MySQL索引

1、索引是什么？

索引是一种特殊的文件（Innodb数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。
索引可以大大提高MySQL的检索速度。

2、索引有哪些优缺点？

优点：可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
	通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统的性能。
缺点：时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和				修改时，索引也要动态维护，会降低执行增删改的执行效率。
	空间方面：索引需要占物理空间。

3、MySQL有哪几种索引类型？

1、从存储结果上划分：BTree索引，Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式,
2、从应用层次来分：普通索引，唯一索引，复合索引。
	普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

	唯一索引：索引列的值必须唯一，但允许有空值

	复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

	聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取	决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术	上来说是B+Tree)和数据行。

	非聚簇索引：不是聚簇索引，就是非聚簇索引
3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引	

4、说一说索引结构默认使用B+Tree,而不是B-Tree,Hash,二叉树，红黑树？

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：虽然可以快速定位，但是没有顺序，IO复杂度高；如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

5、说一下索引的底层实现？

Hash索引:基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

B+Tree索引
是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。
B+tree性质：
	n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
	所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结		点本身依关键字的大小自小而大顺序链接。
	所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。
B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

6、讲一讲聚簇索引与非聚簇索引？

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。(一般要进行回表操作)

7、非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么不需要进行回表查询。

8、联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

9、讲一讲MySQL的最左前缀原则？

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

10、讲一讲前缀索引？

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。

11、了解索引下推吗？

MySQL 5.6引入了索引下推优化。默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

有了索引下推优化，可以在减少回表次数

在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：
在 people_table中有一个二级索引(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%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。

12、怎么查看MySQL语句有没有用到索引？

通过explain

type：type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引) 。

通常来说, 不同的 type 类型的性能关系如下:ALL < index < range ~ index_merge < ref < eq_ref < const < systemALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的. 而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.

13、为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

14、如何创建索引？

创建索引有三种方式
1、 在执行CREATE TABLE时创建索引
	CREATE TABLE user_index2 (
 	...
 	KEY name (first_name, last_name),
 	...
	);
2、 使用ALTER TABLE命令去增加索引。
	ALTER TABLE table_name ADD INDEX index_name (column_list);
3、 使用CREATE INDEX命令创建。
	CREATE INDEX index_name ON table_name (column_list);

15、创建索引时需要注意什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

16、建索引的原则有哪些？

1、最左前缀匹配原则
2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
3、尽量选择区分度高的列作为索引
4、索引列不能参与计算，保持列“干净”
5、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

17、使用索引查询一定能提高查询的性能吗？

通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的I* NSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
	基于一个范围的检索，一般查询返回结果集小于表中记录数的30%。
	基于非唯一性索引的检索。

18、什么情况下不走索引（索引失效）？

1、使用!= 或者 <> 导致索引失效
2、类型不一致导致的索引失效
3、函数导致的索引失效
4、运算符导致的索引失效
5、OR引起的索引失效
6、模糊搜索导致的索引失效
7、NOT IN、NOT EXISTS导致索引失效