C++--问题33--数据库索引

C++--问题33--数据库索引

---

从物理存储角度：

 

（1）聚集索引

        聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。

        聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

        InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（ MyISAM 可以没有），如果没有显式指定，则MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。

       辅助索引中，叶结点的 data 域存放的是对应记录的主键的 key 。

       对于建立辅助索引的表需要先根据辅助索引找到相应的主键，再根据主键在聚集索引中找到相应的记录集。

InnoDB     聚集索引

   数据和索引结合，把索引当成数据的一部分存储，叶子结点存放数据

（2）非聚集索引

      非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。

      主键索引中，叶节点的 data 域存放的是数据记录的地址，如果指定的 Key 存在，则取出其 data 域的值，然后以data 域的值为地址，读取相应数据记录。（ MYISAM 采用此种索引方式）。

MyISAM   B+树   非聚集索引

索引和数据分离开来设计，叶子结点存储数据的地址

关键字不重复Dev，可做主索引

.frm    创建表的基本信息

.myi    表对应的索引

.myd   存储数据的文件

区别：

      聚集索引表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而非聚集索引表数据写入的顺序是按写入时间顺序存储的。

      聚簇索引索引的叶节点就是数据节点；而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

 

适用情景:

 

从数据结构角度 :

b+树索引

优点：

1. 单次请求涉及的磁盘 IO 次数少（出度 d 大，且非叶子节点不包含表数据，树的高度小）；

2. 查询效率稳定（任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同）；

3. 遍历效率高（从符合条件的某个叶子节点开始遍历即可）；

缺点：

B+ 树最大的性能问题在于会产生大量的随机 IO ，主要存在以下两种情况：

1. 主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；

2. 即使主键是有序递增的，大量写请求的分布仍是随机的；

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

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像 B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO 访问，所以 Hash 索引的查询效率要远高于B-Tree 索引。

对比：

（ 1 ） Hash 索引仅仅能满足 "=", 和 "<=>" 等值查询，不能使用范围查询。

（ 2 ） Hash 索引无法被用来避免数据的排序操作。

（ 3 ） Hash 索引 不支持多列联合索引的最左匹配规则 ；

（ 4 ） Hash 索引在任何时候都不能避免表扫描。

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

从逻辑角度 :

主键索引：索引值必须唯一，不能为 NULL ，在 B+TREE 中的 InnoDB 引擎中 , 主键索引起到了至关重要的地位。

普通索引或者单列索引：最普通的索引，没有任何限制。

多列索引（复合索引）：多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

唯一索引或者非唯一索引：与普通索引的不同的是，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

组合索引：平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。

在使用查询的时候遵循“最左前缀”：

不按索引最左列开始查询不适用索引。例如对 idnex( c1 , c2 , c3 ), 使用 where c2 = “aaa” and c3 = “bbb”不能使用索引查询中某个列有范围查询，则其右边的所有列都无法使用查询。例如对idnex( c1 , c2 , c3 ) ，where c1 = “xxx” and c2 like = “aa%” and c3 = “sss”查询只会使用索引的前两列，因为 like 是范围查询不能跳过某个字段进行查询。

使用索引优点：

1. 可以通过建立唯一索引或者主键索引 , 保证数据库表中每一行数据的唯一性。

2. 建立索引可以大大提高检索的数据 , 以及减少表的检索行数。

3. 在表连接的连接条件，可以加速表与表直接的相连。

4. 在分组和排序字句进行数据检索 , 可以减少查询时间中分组和 排序时所消耗的时间 ( 数据库的记录会重新排序)。

5. 建立索引 , 在查询中使用索引，可以提高性能。

使用索引缺点：

1. 创建索引和维护索引会耗费时间 , 随着数据量的增加而增加。

2. 索引文件会占用物理空间 , 除了数据表需要占用物理空间之外 , 每一个索引还会占用一定的物理空间。

3. 当对表的数据进行 INSERT,UPDATE,DELETE 的时候 , 索引也要动态的维护 , 这样就会降低数据的维护速度,( 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快) 。