1399元价位的小米8青春版、荣耀8x
928 2023-08-04 18:22:19
数据库索引的种类:
1、按照索引列值的唯一性,索引可分为唯一索引和非唯一索引
非唯一索引:B树索引
create index 索引名 on 表名(列名) tablespace 表空间名;
唯一索引:建立主键或者唯一约束时会自动在对应的列上建立唯一索引
2、索引列的个数:单列索引和复合索引
3、按照索引列的物理组织方式
B树索引
create index 索引名 on 表名(列名) tablespace 表空间名;
位图索引
create bitmap index 索引名 on 表名(列名) tablespace 表空间名;
反向键索引
create index 索引名 on 表名(列名) reverse tablespace 表空间名;
函数索引
create index 索引名 on 表名(函数名(列名)) tablespace 表空间名;
删除索引
drop index 索引名
重建索引
alter index 索引名 rebuild
索引的创建格式:
CREATE UNIUQE | BITMAP INDEX 《schema》.《index_name》
ON 《schema》.《table_name》
(《column_name》 | 《expression》 ASC | DESC,
《column_name》 | 《expression》 ASC | DESC,...)
TABLESPACE 《tablespace_name》
STORAGE 《storage_settings》
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS《nn》
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION《partition_setting》
UNIQUE | BITMAP:指定UNIQUE为唯一值索引,BITMAP为位图索引,省略为B-Tree索引。
《column_name》 | 《expression》 ASC | DESC:可以对多列进行联合索引,当为expression时即“基于函数的索引”
TABLESPACE:指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
STORAGE:可进一步设置表空间的存储参数
LOGGING | NOLOGGING:是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
COMPUTE STATISTICS:创建新索引时收集统计信息
NOCOMPRESS | COMPRESS《nn》:是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
NOSORT | REVERSE:NOSORT表示与表中相同的顺序创建索引,REVERSE表示相反顺序存储索引值
PARTITION | NOPARTITION:可以在分区表和未分区表上对创建的索引进行分区
使用USER_IND_COLUMNS查询某个TABLE中的相应字段索引建立情况
使用DBA_INDEXES/USER_INDEXES查询所有索引的具体设置情况。
在Oracle中的索引可以分为:B树索引、位图索引、反向键索引、基于函数的索引、簇索引、全局索引、局部索引等,下面逐一讲解:
一、B树索引:
最常用的索引,各叶子节点中包括的数据有索引列的值和数据表中对应行的ROWID,简单的说,在B树索引中,是通过在索引中保存排过续的索引列值与相对应记录的ROWID来实现快速查询的目的。其逻辑结构如图:
可以保证无论用户要搜索哪个分支的叶子结点,都需要经过相同的索引层次,即都需要相同的I/O次数。
B树索引的创建示例:
create index ind_t on t1(id) ;
注1:索引的针对字段创建的,相同字段不能创建一个以上的索引;
注2:默认的索引是不唯一的,但是也可以加上unique,表示该索引的字段上没有重复值(定义unique约束时会自动创建);
注3:创建主键时,默认在主键上创建了B树索引,因此不能再在主键上创建索引。
二、位图索引:
有些字段中使用B树索引的效率仍然不高,例如性别的字段中,只有“男、女”两个值,则即便使用了B树索引,在进行检索时也将返回接近一半的记录。
所以当字段的基数很低时,需要使用位图索引。(“低”的标准是取值数量 《 行数*1%)
位图索引的逻辑结构如上图所示:索引中不再记录rowid和键值,而是将每个值作为一列,用0和1表示该行是否等于该键值(0表示否;1表示是)。其中位图索引的行顺序与原表的行顺序一致,可以在查询数据的过程中对应计算出行的原始物理位置。
位图索引的创建示例:
create bitmap index ind_t on t1(type);
注:位图索引不可能是唯一索引,也不能进行键值压缩。
三、反向键索引:
考虑这个情况:某一字段的值是1-1000顺序排列,建立B树索引后依旧递增,到后来该B数索引不断在后面增加分支,会形成如下如的不对称树:
反向键索引是一种特殊的B树索引,在存储构造中与B树索引完全相同,但是针对数值时,反向键索引会先反向每个键值的字节,然后对反向后的新数据进行索引。例如输入2008则转换为8002,这样当数值一次增加时,其反向键在大小中的分布仍然是比较平均的。
反向键索引的创建示例:
create index ind_t on t1(id) reverse;
注:键的反转由系统自行完成。对于用户是透明的。
四、基于函数的索引:
有的时候,需要进行如下查询:select * from t1 where to_char(date,’yyyy’)》’2007’;
但是即便在date字段上建立了索引,还是不得不进行全表扫描。在这种情况下,可以使用基于函数的索引。其创建语法如下:
create index ind_t on t1(to_char(date,’yyyy’));
注:简单来说,基于函数的索引,就是将查询要用到的表达式作为索引项。
五、全局索引和局部索引:
这个索引貌似很复杂,其实很简单。总得来说一句话,就是无论怎么分区,都是为了方便管理。
具体索引和表的关系有三种:
1、局部分区索引:分区索引和分区表1对1
2、全局分区索引:分区索引和分区表N对N
3、全局非分区索引:非分区索引和分区表1对N
创建示例:
首先创建一个分区表
create table student
(
stuno number(5),
sname vrvhar2(10),
deptno number(5)
)
partition by hash (deptno)
(
partition part_01 tablespace A1,
partition part_02 tablespace A2
);
创建局部分区索引(1v1):
create index ind_t on student(stuno)
local(
partition part_01 tablespace A2,
partition part_02 tablespace A1
); --local后面可以不加
创建全局分区索引(NvN):
create index ind_t on student(stuno)
global partition by range(stuno)
(
partition p1 values less than(1000) tablespace A1,
partition p2 values less than(maxvalue) tablespace A2
); --只可以进行range分区
创建全局非分区索引(1vN)
create index ind_t on student(stuno) GLOBAL;
1.按照索引列值的唯一性,索引可分为唯一索引和非唯一索引; 非唯一索引: create index 索引名 on 表名(列名) tablespace 表空间名; 唯一索引: 建立主键或者唯一约束时会自动在对应的列...
2.索引列的个数:单列索引和复合索引;
3.按照索引列的物理组织方式。 索引的创建格式: CREATE UNIUQE | BITMAP INDEX 《schema》.《index_name》 ON 《schema...
数据库索引的作用相当于书的目录,就是提高数据的查询速度,不同的数据库索引类型不用,比如SQL SERVER就有聚集索引和非聚集索引,聚集索引提高数据的查询速度,非聚集索引对提高查询速度不利,但可以提高数据的删除或插入速度,因为它的逻辑页面和物理页面不一致
种类:
1、按照索引列值的唯一性,索引可分为唯一索引和非唯一索引;
非唯一索引:
create index 索引名 on 表名(列名) tablespace 表空间名;
唯一索引:
建立主键或者唯一约束时会自动在对应的列上建立唯一索引;
2、索引列的个数:单列索引和复合索引;
3、按照索引列的物理组织方式。
索引的创建格式:
CREATE UNIUQE | BITMAP INDEX 《schema》.《index_name》 ON 《schema》.《table_name》 (《column_name》 | 《expression》 ASC | DESC, 《column_name》 | 《expression》 ASC | DESC,...) TABLESPACE 《tablespace_name》 STORAGE 《storage_settings》 LOGGING | NOLOGGING COMPUTE STATISTICS NOCOMPRESS | COMPRESS《nn》 NOSORT | REVERSE PARTITION | GLOBAL PARTITION《partition_setting》
使用USER_IND_COLUMNS查询某个TABLE中的相应字段索引建立情况
使用DBA_INDEXES/USER_INDEXES查询所有索引的具体设置情况。
在Oracle中的索引可以分为:B树索引、位图索引、反向键索引、基于函数的索引、簇索引、全局索引、局部索引等,下面逐一讲解:
一、B树索引:
最常用的索引,各叶子节点中包括的数据有索引列的值和数据表中对应行的ROWID,简单的说,在B树索引中,是通过在索引中保存排过续的索引列值与相对应记录的ROWID来实现快速查询的目的。其逻辑结构如图:
可以保证无论用户要搜索哪个分支的叶子结点,都需要经过相同的索引层次,即都需要相同的I/O次数。
B树索引的创建示例:
create index ind_t on t1(id) ;
注1:索引的针对字段创建的,相同字段不能创建一个以上的索引;
注2:默认的索引是不唯一的,但是也可以加上unique,表示该索引的字段上没有重复值(定义unique约束时会自动创建);
注3:创建主键时,默认在主键上创建了B树索引,因此不能再在主键上创建索引。
二、位图索引:
有些字段中使用B树索引的效率仍然不高,例如性别的字段中,只有“男、女”两个值,则即便使用了B树索引,在进行检索时也将返回接近一半的记录。
所以当字段的基数很低时,需要使用位图索引。(“低”的标准是取值数量 《 行数*1%)
位图索引的逻辑结构如上图所示:索引中不再记录rowid和键值,而是将每个值作为一列,用0和1表示该行是否等于该键值(0表示否;1表示是)。其中位图索引的行顺序与原表的行顺序一致,可以在查询数据的过程中对应计算出行的原始物理位置。
位图索引的创建示例:
create bitmap index ind_t on t1(type);
注:位图索引不可能是唯一索引,也不能进行键值压缩。
三、反向键索引:
考虑这个情况:某一字段的值是1-1000顺序排列,建立B树索引后依旧递增,到后来该B数索引不断在后面增加分支,会形成如下如的不对称树:
反向键索引是一种特殊的B树索引,在存储构造中与B树索引完全相同,但是针对数值时,反向键索引会先反向每个键值的字节,然后对反向后的新数据进行索引。例如输入2008则转换为8002,这样当数值一次增加时,其反向键在大小中的分布仍然是比较平均的。
反向键索引的创建示例:
create index ind_t on t1(id) reverse;
注:键的反转由系统自行完成。对于用户是透明的。
四、基于函数的索引:
有的时候,需要进行如下查询:select * from t1 where to_char(date,’yyyy’)》’2007’;
但是即便在date字段上建立了索引,还是不得不进行全表扫描。在这种情况下,可以使用基于函数的索引。其创建语法如下:
create index ind_t on t1(to_char(date,’yyyy’));
注:简单来说,基于函数的索引,就是将查询要用到的表达式作为索引项。
五、全局索引和局部索引:
这个索引貌似很复杂,其实很简单。总得来说一句话,就是无论怎么分区,都是为了方便管理。
具体索引和表的关系有三种:
1、局部分区索引:分区索引和分区表1对1
2、全局分区索引:分区索引和分区表N对N
3、全局非分区索引:非分区索引和分区表1对N
创建示例:
首先创建一个分区表
create table student
(
stuno number(5),
sname vrvhar2(10),
deptno number(5)
)
partition by hash (deptno)
(
partition part_01 tablespace A1,
partition part_02 tablespace A2
);
创建局部分区索引(1v1):
create index ind_t on student(stuno)
local(
partition part_01 tablespace A2,
partition part_02 tablespace A1
); --local后面可以不加
创建全局分区索引(NvN):
create index ind_t on student(stuno)
global partition by range(stuno)
(
partition p1 values less than(1000) tablespace A1,
partition p2 values less than(maxvalue) tablespace A2
); --只可以进行range分区
创建全局非分区索引(1vN)
create index ind_t on student(stuno) GLOBAL;
根据具体问题类型,进行步骤拆解/原因原理分析/内容拓展等。
具体步骤如下:/导致这种情况的原因主要是……
三种索引类型分别是:
1、主键索引:不允许具有索引值相同的行,从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
2、聚集索引:指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况。
3、非聚集索引:索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。非聚集索引的叶层不包含数据页。 相反,叶节点包含索引行。
扩展资料
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。
例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。
频繁更改的列 这将导致整行移动,因为 SQL Server 必须按物理顺序保留行中的数据值。这一点要特别注意,因为在大数据量事务处理系统中数据是易失的。来自聚集索引的键值由所有非聚集索引作为查找键使用,因此存储在每个非聚集索引的叶条目内。
参考资料来源:百度百科-非聚集索引
参考资料来源:百度百科-聚集索引
参考资料来源:百度百科-唯一索引
在满足语句需求的情况下,尽量少的访问资源是数据库设计的重要原则,这和执行的 SQL 有直接的关系,索引问题又是 SQL 问题中出现频率最高的,常见的索引问题包括:无索引(失效)、隐式转换。
1. SQL 执行流程看一个问题,在下面这个表 T 中,如果我要执行 select * from T where k between 3 and 5; 需要执行几次树的搜索操作,会扫描多少行?mysql》 create table T ( -》 ID int primary key, -》 k int NOT NULL DEFAULT 0, -》 s varchar(16) NOT NULL DEFAULT ’’, -》 index k(k)) -》 engine=InnoDB;mysql》 insert into T values(100,1, ’aa’),(200,2,’bb’),\ (300,3,’cc’),(500,5,’ee’),(600,6,’ff’),(700,7,’gg’);
这分别是 ID 字段索引树、k 字段索引树。
这条 SQL 语句的执行流程:
1. 在 k 索引树上找到 k=3,获得 ID=3002. 回表到 ID 索引树查找 ID=300 的记录,对应 R33. 在 k 索引树找到下一个值 k=5,ID=5004. 再回到 ID 索引树找到对应 ID=500 的 R4
5. 在 k 索引树去下一个值 k=6,不符合条件,循环结束
这个过程读取了 k 索引树的三条记录,回表了两次。因为查询结果所需要的数据只在主键索引上有,所以必须得回表。所以,我们该如何通过优化索引,来避免回表呢?
2. 常见索引优化2.1 覆盖索引覆盖索引,换言之就是索引要覆盖我们的查询请求,无需回表。
如果执行的语句是 select ID from T wherek between 3 and 5;,这样的话因为 ID 的值在 k 索引树上,就不需要回表了。
覆盖索引可以减少树的搜索次数,显著提升查询性能,是常用的性能优化手段。
但是,维护索引是有代价的,所以在建立冗余索引来支持覆盖索引时要权衡利弊。
2.2 最左前缀原则
B+ 树的数据项是复合的数据结构,比如 (name,sex,age) 的时候,B+ 树是按照从左到右的顺序来建立搜索树的,当 (张三,F,26) 这样的数据来检索的时候,B+ 树会优先比较 name 来确定下一步的检索方向,如果 name 相同再依次比较 sex 和 age,最后得到检索的数据。
# 有这样一个表 P
mysql》 create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;
mysql》 insert into P values(1,’张三’,’F’,26),(2,’张三’,’M’,27),(3,’李四’,’F’,28),(4,’乌兹’,’F’,22),(5,’张三’,’M’,21),(6,’王五’,’M’,28);
# 下面的语句结果相同
mysql》 select * from P where name=’张三’ and sex=’F’; ## A1
mysql》 select * from P where sex=’F’ and age=26; ## A2
# explain 看一下
mysql》 explain select * from P where name=’张三’ and sex=’F’;
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | P | NULL | ref | tl | tl | 38 | const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
mysql》 explain select * from P where sex=’F’ and age=26;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | P | NULL | index | NULL | tl | 43 | NULL | 6 | 16.67 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
2.3 索引下推
2.4 隐式类型转化
修改应用,将应用中传入的字符类型改为与表结构相同类型。
3.2 扫描行数
在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:
on 表示统计信息会持久化存储。默认 N = 20,M = 10。
off 表示统计信息只存储在内存中。默认 N = 8,M = 16。
可以用 analyze table 来重新统计索引信息,进行修正。
不同数据库中提供了不同的索引类型,SQLServer中的索引有两种:聚集索引(CLUSTERED)和非聚集索引(NONCLUSTERED)。
索引区别:
聚集索引:
聚集索引基于数据行的键值,在表内排序和存储这些数据行。每个表只能有一个聚集索引,应为数据行本分只能按一个顺序存储。
在聚集索引中,表中各行的物理顺序与索引键值的逻辑(索引)顺序相同。聚集索引通常可加快UPDATE和DELETE操作的速度,因为这两个操作需要读取大量的数据。创建或修改聚集索引可能要花很长时间,因为执行这两个操作时要在磁盘上对表的行进行重组。
非聚集索引:
因为一个表中只能有一个聚集索引,如果需要在表中建立多个索引,则可以创建为非聚集索引。表中的数据并不按照非聚集索引列的顺序存储,但非聚集索引的索引行中保存了非聚集键值和行定位器,可以快捷地根据非聚集键的值来定位记录的存储位置。
延伸阅读:
无论是聚集索引,还是非聚集索引,都可以是唯一索引(UNIQUE)
。在SQL Server中,当唯一性是数据本身的特点时,可创建唯一索引,但索引列的组合不同于表的主键。例如,如果要频繁查询表a(该表主键为列a_id)的列a_name,而且要保证姓名是唯一的,则在列a_name上创建唯一索引。如果用户为多个员工输入了相同的姓名,则数据库显示错误,并且不能保存该表。