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
的时候
,
索引也要动态的维护
,
这样就会降低数据的维护速度,(
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快)
。