数据库索引相关笔记
##数据库索引
数据库索引是什么
数据库的索引是存储了数据库表中某一个列的值的数据结构,最常见的是B-Tree。所以说,数据库的本质是一个数据结构。
(索引是一种快速查询表中内容的机制,类似于新华字典的目录
运用在表中某些字段上,但存储时,独立于表之外)
数据库索引的作用是什么
在查找次数很频繁的数据列上使用索引可以有效减少查找的时间,使数据查找更加有效率。
数据库索引的特点是什么
(查找操作花费时间比较短,删除和插入操作花费时间比较多)
1.索引一旦建立,数据库管理系统会对其进行自动维护,而且由数据库管理系统决定何时使用索引
2.用户不用在查询语句中指定使用哪个索引
3.在定义primary key或unique约束后系统自动在相应的列上创建索引
4.用户也能按照自己的需求,对指定单个字段或多个字段,添加索引
数据库索引是怎样提升性能的
在没有索引的情况下,如果查找表中数据,那么就需要一条数据一条数据地查找,比对与所查数据是否一致,极端情况下需要查到数据表最后一条,如果数据表中的数据不存在所查的数据条目的时候,甚至不能查到。因此使用索引,因为索引中只记录特定列的数据,并且通常的B-Tree是有序且分层的,查找到对应的数据的列的值会快很多。查找到特定列的值的数据之后,这条索引上会记录有该条数据所对应的表中的地址,直接读取即可获得这一条全部数据。
数据库怎么知道什么时候使用索引
当某一条查询语句(比如select * from Tabel_A where A=a )运行的时候,数据库会检查所查询的列上是否有索引。假如该列(本例中的A)上确实有索引,数据库会接着检查这个索引做查询是否合理(因为某系情况下,使用索引会比全表扫描效率更低),当使用索引的时间预期更低的时候才会使用索引。
什么时候使用索引比不使用索引效率还低
例如,当有100万条数据,每个数据块8k,只能存10条数据,那么一共需要10万个数据块,而要查询的数据很分散,每个数据块都保存有一行。此时,全表扫描比索引扫描会快,因为此时全表扫描和索引扫描都需要10万个物理读,而索引扫描要先去索引中招到所有的rowid,然后发现还是要加载10万个数据块,结果比全表扫描多了IO。
另一个例子:一个表有100万条数据,一个数据块能保存1000条数据,那么需要1000个数据块。查询10万条数据,如果用索引扫描,那么需要从索引中找到10万行数据的rowid,然后再去执行10万次TABLE ACCESS BU ROWID,需要处理10万次数据块,虽然会缓存避免物理读,但是全表扫描只需要处理1000个数据块,显然此时全表扫描的效率会高一些。
以上只是两个比较极端的例子,用以说明并不是任何时候使用索引都会提高查询效率。
使用数据库索引有什么代价(缺点)
1.表越大,索引占用的空间越大。
2.性能损失(主要是值更新操作),当你在表中添加、删除或更新行数据的时候,在索引中也会有相同的操作(建立在某列或某几列的索引需要保存该列上最新的数据)
因此,使用索引的基本原则是,如果表中某列在查询过程中使用非常频繁,那么就在该列上创建索引。
什么时候【要】创建索引
1.表经常进行select操作
2.表很大(记录很多),记录内容分布范围很广
3.列名经常在where子句或连接条件中出现
什么时候【不要】创建索引
1.表经常进行insert/delete/update操作
2.表很小(记录很少)
3.列名不经常作为连接条件或出现在where子句中
索引类型
· 唯一索引: 唯一索引不允许两行具有相同的索引值
· 主键索引: 为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的,并且不能为空
· 聚集索引(Clustered): 表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只能有一个
· 非聚集索引(Non-clustered): 非聚集索引指定表的逻辑顺序。数据存储在一个位置,索引存储在另一个位置,索引中包含指向数据存储位置的指针。可以有多个,小于249个
创建索引的语句
单个索引:
1 | CREATE INDEX name_index ON TABEL_A (A) |
联合索引:
1 | CREATE INDEX name_index ON TABEL_A (A, B) |