Mysql索引

什么是索引

• 索引是一种能帮助 MySQL 提高查询效率的数据结构。
• 聚集索引-叶节点包含了完整的数据记录（InnoDB ）
• MyISAM索引文件和数据文件是分离的（非聚集）

索引分别有哪些有点和缺点?

优点如下：

• 快速访问数据表中的特定信息，提高检索速度
• 创建唯一性索引，保证数据表中每一行数据的唯一性
• 加速表与表之间的连接
• 使用分组和排序进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点如下：

• 虽然提高了查询速度，但却降低了更新表的速度，比如 update，insert，因为更新数据时，MySQL 不仅要更新数据，还要更新索引文件，
• 建立索引会占用磁盘文件的索引文件。

使用索引注意事项：

• 使用短索引，短索引不仅可以提高查询速度，更能节省磁盘空间和 I/O操作
• 索引列排序，MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下，不要进行排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引;
• like 语句操作，一般情况下不鼓励使用 like操作，如果非使用不可，注意 like "%ming%"不会使用索引，而like% 可以使用索引。
• 不要在列上进行运算
• 不适用 NOT IN 和 < > 操作

索引的常见存储算法有哪些：

• 哈希存储法：以 key , value 方式存储，把值存入数组中使用哈希值确认数据的位置，如果发生哈希冲突，使用链表存储数据
• 有序数组存储法：按顺序存储，优点是可以使用二分法快速找到数据，缺点是更新效率，适合静态数据存储
• 搜索树：以树的方式进行存储，查询性能好，更新速度快。

InnoDB 为什么要使用 B+ 树，而不是 B树，hash，红黑树或者二叉树？

因为 B 树，Hash，红黑树或二叉树存在以下问题：

• B树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的i情况下要保存大量数据，只能增加树的高度，导致 I/O 操作变多，查询性能变低。
• Hash：虽然可以快速定位，但是没有顺序，I/O 复杂度高
• 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高
• 红黑树：树的高度随着数据量增加而增加，IO代价高，

为什么 InnDB 要使用 B+ 树来存储索引：

B+Tree 中的 B 是 Balance，是平衡的意思，它在经典 B Tree 的基础上进行了优化，增加了顺序访问指针，在 B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree，这样就提高了区间访问性能：如果要查询 key 为从 18 到 49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提高了区间查询效率（无需返回上层父节点重复遍历查找减少I/O操作）

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上，这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以索引的结果组织要尽量减少查找过程中磁盘 IO 的存储次数，从而提升索引效率。综合所述，InnoDB 只有采用 B+ 树的数据结构存储索引，才能提供数据库整体的操作性能。