数据库索引练习数据库索引教程

转载

mob6454cc692b0f 2024-03-18 14:46:34

文章标签 数据库索引练习 big data 数据结构数据库数据 文章分类 数据仓库大数据

一、索引

1、定义

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

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

2、分类

单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

3、创建索引

#查看索引，主键会自动创建索引

show index from dept;

#创建普通索引

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

# 创建唯一索引

#创建唯一索引--索引列的值必须唯一

CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
    CREATE UNIQUE INDEX bindex ON dept(loc)

# 创建复合索引

#如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX 索引名 ON 表名 (字段1, 字段2)
    CREATE INDEX PIndex ON Persons (LastName, FirstName)

# 删除索引

alter table dept drop index fuhe_index

索引扫描类型

type：

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

最左特性

explain
     
    select * from dept where loc='二区' #使用了loc索引
     
    explain
     
    select * from dept where dname='研发部'#使用了dname索引
     
    explain
     
    select * from dept where dname='研发部' and loc='二区' #使用了dname索引

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

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

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

注：排序，tree结构，类似二分查找
索引表小