数据库各类索引比较

目录

一.结论

二.各类索引说明

三.各类主键比较区别

四.补充


一.结论

索引的一个主要目的就是加快检索表中数据
索引是用来定位的

注:所有的索引都可以有多个字段,如联合索引,普通索引,主键,聚集索引

二.各类索引说明

1.唯一索引:(可以用来防止数据重复问题,用insert ignore  into插入不报错,affect:0)
注:不能重复,可以有多个,可以有一个null值

  • a.防止添加将在表中创建重复键值的新数据
  • b.加快查询速度

2.主键索引:(主键和主键索引可以想象成是一个东西)
注:不能重复,只能有一个主键,但一个主键可以包含多个字段,不可以有null值,定义主键primarykey时也定义了主键索引
定义主键将自动创建主键索引,主键索引是唯一索引的特定类型

3.聚集索引(从某种程度上,聚集索引即数据)
 注:数据库索引分为聚集索引和非聚集索引,非聚集索引分为普通索引,唯一索引,全文索引等

  • a.一个表只能包含一个聚集索引
  • b.聚集索引和非聚集索引只多了个唯一约束,其他没什么区别
  • c.MySQL-innodb里主键就是聚集索引
  • d.聚集索引的约束是唯一性,是否要求字段也是唯一的呢?不要求唯一!
  • e.聚集索引可以创建在任何一列你想创建的字段上,这是从理论上讲
  • f.聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。

三.各类主键比较区别

1.聚集索引和非聚集索引的主要区别:
(1.)数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同,一个表中就一个;索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以有多个
(2.)聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。
第三:是不是聚集索引就一定要比非聚集索引性能优呢?


例:如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?
答:否。既然只输出两列,我们可以在学分以及学生姓名上创建联合非聚集索引,此时的索引就形成了覆盖索引,即索引所存储的内容就是最终输出的数据,这种索引在比以学分为聚集索引做查询性能更好。
除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。
在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。

2.主键索引和主键的关系:创建主键的同时创建了主键索引
3.主键索引和唯一索引的区别:主键索引使用primarykey创建主键时自动创建,主键索引根据主键要求字段不重复不为空,唯一索    引要求字段不同,但可以有一个null值
4.唯一索引和普通索引的区别:唯一索引unique index,要求值唯一,普通索引index
5.主键和聚集索引关系:主键可分为聚集的主键和非聚集的主键。在创建主键时,如果不指定类型,则默认创建的是聚集的主键,mysql-innodb创建主键同时将主键变为聚集索引

四.补充

1.哪些字段应该加索引哪些不应该加?
注:这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。
应该加:where查询字段,order by子句,join字段
不应加:查询中很少使用或者参考的列不应该创建索引,小值字段如性别,text, image和bit数据类型,当修改性能远远大于检索性能时(常修改的字段)

2.多列索引和多个单列索引
  多列索引即是一个索引包含多个字段的情况;

  多个单列索引即是每个索引只包含一个字段,但是有多个索引。

  考虑两种不同的建立索引方式:
  case 1:对c1,c2,c3三列按此顺序添加一个多列索引;
  case 2: 对c1,c2,c3分别建立三个单列索引;

  问题1:按c1搜索时,哪种索引效率快?
  答:case2

  问题2:按C2搜索时,哪种索引效率快?
  答:case2,并且,case1的索引无效

  问题3:按C1,C2搜索时哪种效率快?
  答:不知道

  问题4:按C1,C2,C3搜索哪种效率快?
  答:case1

  问题5:按C2,C3,C1搜索时哪种效率快?
  答:case2,因为没有按多列索引的顺序搜索,case1的索引没有使用到。

 

参考文章:

### 数据库索引类型 数据库索引可以根据其用途和实现方式进行分类。以下是几种常见的数据库索引类型及其特点: #### 1. 主键索引 主键索引是一种特殊的唯一性索引,用于标识表中的每一行数据。它的特点是不允许重复值,并且不能为空。在 InnoDB 存储引擎中,主键索引也被称为聚簇索引,因为数据行按主键顺序存储在叶节点上[^3]。 #### 2. 唯一索引 唯一索引确保某一列或多列的组合值在整个表中具有唯一性。与主键索引不同的是,唯一索引允许 NULL 值的存在。如果某一行的唯一索引字段为空,则不会违反唯一性的约束条件[^3]。 #### 3. 普通索引 普通索引是最基本的索引形式,没有任何特殊限制。它可以提高查询效率,同时允许多个相同的值存在于同一列中。这种类型索引广泛应用于各种场景下的快速查找需求[^2]。 #### 4. 复合索引(联合索引) 复合索引是指在一个表中定义多个字段共同构成一个索引的情况。当涉及到多条件过滤时,使用复合索引能够有效减少 I/O 开销并提升性能。需要注意的是,“最左前缀原则”,即查询条件应尽量从左边开始匹配才能利用到整个复合索引的效果最佳[^2]。 #### 5. 全文索引 全文索引专门针对大段文字内容设计,适合执行复杂的文本搜索任务。它支持模糊匹配以及关键词权重排序等功能。值得注意的是,只有特定的数据类型如 CHAR、VARCHAR 或 TEXT 才能创建此类索引,并且必须搭配 MATCH AGAINST 函数一起使用,而不是传统的 WHERE LIKE 表达式[^4]。 #### 6. 空间索引 空间索引主要用于地理信息系统(Geographic Information System, GIS)领域内的对象定位服务。例如地图应用中的位置标记点或者区域边界描述等都需要依靠这类高级别的索引来完成精确的空间运算操作[^2]。 --- ### 示例代码:创建不同类型索引 下面展示了一些常见情况下如何为 MySQL 表添加相应类型索引的例子: ```sql -- 创建主键索引 ALTER TABLE table_name ADD PRIMARY KEY (column_name); -- 添加唯一索引 CREATE UNIQUE INDEX index_name ON table_name(column_name); -- 构建普通索引 CREATE INDEX index_name ON table_name(column_name); -- 定义复合索引 CREATE INDEX composite_index_name ON table_name(col1,col2,...coln); -- 设置全文索引 CREATE FULLTEXT INDEX fulltext_index_name ON table_name(text_column); ``` 以上命令分别演示了怎样给定表格设置各类有用的索引方法论。 --- ### 总结 综上所述,数据库提供了丰富的索引选项供开发人员选择适合自己应用场景的最佳方案。理解每种索引的功能特性和适用场合可以帮助我们更好地优化系统的整体表现水平。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值