MySQL索引基本原理 聚簇和非聚簇区别 数据结构 设计原则

目录

一、索引的基本原理

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

三、mysql索引的数据结构，各自优劣  

四、索引设计的原则

---

一、索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理：就是把无序的数据变成有序的查询。

1. 把创建了索引的列的内容进行排序。

2. 对排序结果生成倒排表。

3. 在倒排表内容上拼上数据地址链。

4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

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

都是 B+树 的数据结构

聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数 据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是 相邻地存放在磁盘上的。

只有一个文件，包含索引和数据。 一个 InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多 个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操 作系统文件大小的限制；

非聚簇索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置 再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个 目录里面找，找到对应的页码后再去对应的页码看文章。 

一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件；

 优势：

1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。

2 、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的。

3 、聚簇索引适合用在排序的场合，非聚簇索引不适合。

劣势：

1 、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页 (page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过 OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片。

2 、表因为使用 UUId （随机 ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用 int的auto_increment 作为主键。 

3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间。

 InnoDB中一定有主键，主键一定是聚簇索引，不手动设置、则会使用unique索引，没有unique索引， 则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引， 辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引， 辅助索引叶子节点存储的不再是行的物理位置，而是主键值。 

 MyISM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据。对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

如果涉及到大数据量的排序、全表扫描、 count 之类的操作的话，还是 MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。使用单独的字段记录了count值。

三、mysql索引的数据结构，各自优劣  

索引的数据结构和具体存储引擎的实现有关，在 MySQL 中使用较多的索引有 Hash索引， B+树索引等， InnoDB存储引擎的默认索引实现为：B+树索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择 BTree索引。   

B+树 ：

B+ 树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过 1，而且同层级的节点间有指针 相互链接。在 B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且 基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此 B+树索引被广泛应用于数据库、文件系统等场景。

哈希 ：

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+树那样从根节点到 叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

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

如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后， 有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

哈希索引也没办法利用索引完成排序，以及 like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

哈希索引也不支持多列联合索引的最左匹配规则；

B+ 树索引的关键字检索效率比较平均，不像 B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

四、索引设计的原则

查询更快、占用空间更小

1. 适合索引的列是出现在 where 子句中的列，或者连接子句中指定的列。

2. 基数较小的表，索引效果较差，没有必要在此列建立索引。

3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间， 如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。

4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进 行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

5. 定义有外键的数据列一定要建立索引。

6. 更新频繁字段不适合创建索引。

7. 若是不能有效区分数据的列不适合做索引列 ( 如性别，男女未知，最多也就三种，区分度实在太低 )。

8. 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b)的索引，那么只需要修改原来的索引即可，即使用联合索引。

9. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

10. 对于定义为text、image和bit的数据类型的列不要建立索引，使用全文索引。