MySQL的索引问题

1、关于索引

1.1 首先来看一下的关于对索引的理解

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

1.2 关于使用索引的优缺点

优点： 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点： 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?大多数情况下，索引查询都是比全表扫描要快的。
但是 如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升

1.3 如何判断数据库的索引有没有生效？

可以使用EXPLAIN语句查看索引是否正在使用。

举例，假设已经创建了book表，并已经在其year_publication字段上建立了普通索引。执行如下语句：

EXPLAIN SELECT * FROM book WHERE year_publication=1990;
EXPLAIN语句将为我们输出详细的SQL执行信息，其中：

possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。

key行是MySQL实际选用的索引。

如果possible_keys行和key行都包含year_publication字段，则说明在查询时使用了该索引。

1.4 如何评估一个索引创建的是否合理？

建议按照如下的原则来设计索引：

• 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

• 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

• 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。

• 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

• 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

1.5 所有的字段都适合创建索引吗？

并不是。

下列几种情况，是不适合创建索引的：

• 频繁更新的字段不适合建立索引；
• where条件中用不到的字段不适合建立索引；
• 数据比较少的表不需要建索引；
• 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值；
• 参与列计算的列不适合建索引。

2. MySQL的Hash索引和B树索引有什么区别？

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。它们有以下的不同：

hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

hash索引不支持使用索引进行排序，原理同上。

hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。

hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

3. 索引的数据结构 B+Tree

下图是B-Tree数据结构的示意图，分为上下两部分，下部分数据页，上部分是索引
页。数据页中存储的是表中的记录，索引页存储的是索引数据。

如上图是一颗B-Tree数据结构，其中包含了数据页和索引页，数据页和索引页中虚线框存储的是数据项，实线框中的P1、P2、P3是指针。可以看到每个数据页或索引页包含多个数据项和指针，如“索引页1”包含数据项17和35，包含指针P1、P2、P3。P1指向小于17的索引页，P2指向在17和35之间的索引页，P3指向大于35的索引页。真实的数据存在于叶子节点（数据页），即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
如果要查找数据项29，那么首先会把索引页1由磁盘加载到内存，此时发生一次IO（Input/Output，即读写磁盘）操作，在内存中用二分查找确定29在17和35之间，锁定“索引页1”的P2指针，通过索引页1的P2指针把索引页3由磁盘加载到内存，发生第二次IO操作，29在26和30之间，锁定索引页3的P2指针，通过指针加载数据页8到内存，发生第三次IO操作，同时内存中做二分查找找到29，结束查询，总计三次读取硬盘。真实的情况是，3层的B-Tree结构可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高是巨大的。如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常高。
对于任何DBMS（DataBase Manager System数据库管理系统），索引都是进行查询优化的最主要的因素。