关于SQL的学习记录（四、索引）

最新推荐文章于 2024-11-12 14:54:23 发布

木可就(～￣▽￣)～

最新推荐文章于 2024-11-12 14:54:23 发布

阅读量112

点赞数

分类专栏： SQL 文章标签： sql oracle 数据库 sqlserver

本文链接：https://blog.csdn.net/weixin_43787552/article/details/120060690

版权

SQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

索引

索引基础：
对于大部分数据库用户来说索引是一个非常陌生的概念。因为普通用户很少特意的去使用索引。只有那些管理着海量数据的DBA才会去特意的创建索引和使用索引。
①使用索引的原因：

提供唯一的码值
提高查询性能
数据表中的记录越多，使用索引可能就会得到越大的效益。反之，使用索引就没有什么价值了。

②索引的种类
在许多大型数据库系统中，按存储结构的不同将索引分为两类：聚簇索引、非聚簇索引

聚簇索引：
一个聚簇索引就是一个在物理上与表融合在一起的视图。表和视图共享相同的存储区域。聚簇索引在物理上以索引顺序重新整理了数据的行。这种体系结构中一个表只允许一个聚簇索引。
❀MySQL中不同的数据存储引擎对聚簇索引有不同的支持。MyISAM存储引擎使用的是非聚簇索引，InnoDB存储引擎使用的是聚簇索引。
❀在SQL Server中，删除和重建一个聚簇索引对于改造一个表来说是一个常用的技术。这是一种保证数据页在磁盘上邻近的方便途径。同时，也是重建表中一些空闲空间的很好的方法。
❀Oracle的聚簇索引和SQL Server的聚簇索引完全不同。Oracle聚簇索引在一个Oracle块中同时存储两个或多个表中的数据。在建立聚簇索引时，先创建一个聚簇，然后在该聚簇上创建一个索引，最后在CREATE TABLE语句中指定该表存储在这个聚簇上。
❀聚簇码通常是用来连接这两个或多个表的连接字段。也就是说，如果用户需要使用两个表中的数据，那么只需要存取这一个Oracle块就可以，而并不需要先访问一个表，然后再访问另一个表。在SQL Server中没有与Oracle相似的结构。
非聚簇索引
❀在非聚簇索引中，索引数据和表数据在物理上是分离的，表中的记录并不按照索引中的顺序存储。非聚簇索引的查询效率相对于聚簇索引来说比较低，但由于一个数据表只能创建一个聚簇索引，所以当用户需要使用多个索引时就只能创建非聚簇索引了。

索引的创建和使用：
①创建索引时应该注意的内容
实际上，使用索引会提高查询性能这句话是有前提的，就是说并不是所有情况下使用索引都能提高查询性能。所以在创建并使用索引前应当注意下面的几点内容。

对于只有少量数据记录的表或在Oracle中占有小于10个Oracle块的表来说，使用索引查询数据没有任何好处。应当省掉存取和使用索引块的开销，直接执行全表扫描的到表中的所有数据，这样会更快一些。
如果索引字段中有很多不同的数据值和空值时，使用索引会极大地提高性能。
如果执行查询后，返回的数据记录很少，则索引可以优化该查询。比较好的情况时返回记录数少于全部数据的25%（根据DBMS的不同配置，该数字有所不同）。如果返回的数据记录很多，则使用索引不会得到太多的好处。
索引可以提高查询数据的速度，但它也降低了数据的更新速度。因此，如果要进行大量的更新操作，再执行更新操作前应该删除一些不必要的索引，在更新完毕后再重新创建索引，这样会提高效率。
索引也会占用数据库空间，所以在设计数据库的可用空间时应当考虑索引所占用的空间。
在某字段上创建索引时，应当考虑是否经常使用该字段筛选记录。如果不是，则不应该创建索引，因为该索引不会起什么作用，反而在修改数据是会影响性能。
尽量不要对经常需要更新或修改的字段创建索引，更新索引的开销会降低期望获得的性能。
尽量不要将索引与表存储在同一个驱动器上，分开存储会避免访问冲突，从而能够提高性能。

②创建索引的SQL语句（其中[ ]中的为可选项）

语法格式：
```
CREATE INDEX 索引名 ON 表名(字段名1[,字段名...]);
```
（在MySQL和SQL Server中，索引的名称在表内必须唯一，但在数据库中不必唯一。而在Oracle中，索引的名称在用户内必须唯一。）

MySQL、SQL Server、Oracle对上面创建索引的语句有不同的扩展
❀MySQL

CREATE [UNIQUE|FULLTEXT|SPATIAL] 
INDEX 索引名
[USING 索引类型]
ON 表名(字段名1[,字段名...]);

❀SQL Server

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX 索引名
ON {table|view} (字段名1 [ASC|DESC][,字段名2...])
[WITH {PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB}[,字段名n...]] 
[ON filegroup];

❀Oracle

CREATE [UNIQUE]
INDEX 索引名
ON 表名(字段名1 [ASC|DESC] [,字段名...])
[INITRANS integer]
[MAXTRANS integer]
[TABLESPASE tablespase_name]
[STORAGE storage_clause]
[PCTFREE integer]
[NOSORT]
[RECOVERABLE|UNRECOVERABLE]
[PARALLEL parallel_clause];