SQL索引

database

SQL索引

遥远的将不再遥远,平凡的已不再平凡。

索引 index

索引是一种排好序的快速查找的数据结构,它帮助数据库高效的进行数据的检索。在数据之外,数据库系统还维护着满足特定查找算法的数据结构(额外的存储空间),这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大,不可能全部存储在内存中,因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

分类

单值索引:一个索引只包括一个列,一个表可以有多个列

唯一索引:索引列的值必须唯一,但允许有空值;主键会自动创建唯一索引

复合索引:一个索引同时包括多列

创建索引

  • 查看索引,主键会自动创建索引

show index from user;

  • 创建索引

create index 索引名字 on 表名(字段名); #创建索引

create index score_index on scores(score); #创建索引

  • 修改表结构,添加普通索引

alter table user add index sex_index(sex)

  • 创建唯一索引

alter table students add unique(id) #创建唯一索引,索引列的值必须唯一

  • 创建复合索引

alter table user add index cp_index(name,age);

  • 创建复合唯一索引

alter table user add unique cp_index(name,age);

alter table uesr drop index cp_index;

索引扫描类型

type:

  • ALL 全表扫描,没有优化,最慢的方式
  • index 索引全扫描,其次慢的方式
  • range 索引范围扫描,常用语<,<=,>=,between等操作
  • ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中
  • eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询
  • const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询,system是const的特殊情况
  • null MySQL不访问任何表或索引,直接返回结果

使用索引 explain

explain

select * from user where id = 1;#使用了id的索引

最左特性

当我们创建一个联合索引(复合索引)的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则,也称为最左特性。

注意:查询k2字段或者k3字段或者k2和k3字段,索引就会失效

ALTER TABLE user ADD INDEX user_index(id,name,age); #这里添加复合索引

explain

select * from user where id = 1 and name = "阿柒"; #索引生效

select * from user where name = "阿柒"; #索引失效

select * from user where id = 1 and age = 21; #索引失效

索引查询

明显查询索引表比直接查询数据表要快的多,首先,索引表是排序了,可以类似二分查找,非常有效的提高了查询的速度。

其过程如下图,先到事先排序好的索引表中检索查询,找到其主键后,就直接定位到记录所在位置,然后直接返回这条数据。

  • 排序,tree结构,类似二分查找
  • 索引表小

优点:

  • 索引是数据库优化
  • 表的主键会默认自动创建索引
  • 每个字段都可以被索引
  • 大量降低数据库的IO磁盘读写成本,极大提高了检索速度
  • 索引事先对数据进行了排序,大大提高了查询效率

缺点:

  • 索引本身也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也要占用空间
  • 索引表中的内容,在业务表中都有,数据是重复的,空间是“浪费的”
  • 虽然索引大大提高了查询的速度,但对数据的增、删、改的操作需要更新索引表信息,如果数据量非常巨大,更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
  • 随着业务的不断变化,之前建立的索引可能不能满足查询需求,需要消耗我们的时间去更新索引

以上是 SQL索引 的全部内容, 来源链接: utcz.com/z/535859.html

回到顶部