前言

想的再多，不如行动起来，大家好，我是啊Q，让我们徜徉在知识的海洋里吧。

一起“开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第11天， 点击查看活动详情 。

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引对于查询十分重要，今天我们来梳理一下各个数据库之间索引的异同。

mysql

基于 mysql 8

创建普通索引

- CREATE INDEX indexName ON tableName (columnName[(length)] [ ASC | DESC] );
- ALTER TABLE tableName ADD INDEX indexName(columnName[(length)] [ ASC | DESC] ); 不推荐。

创建唯一索引

- CREATE UNIQUE INDEX indexName ON tableName (columnName [(length)] [ ASC | DESC] );

创建普通联合索引

- create index indexName on tableName(columnName1 [(length)] [ ASC | DESC] , columnName2[(length)] [ ASC | DESC] , ... );

创建唯一联合索引

- create unique index indexName on tableName(columnName1[(length)] [ ASC | DESC] , columnName2[(length)] [ ASC | DESC] , ...);

查看索引记录脚本

- SHOW INDEX FROM tableName;

删除索引

- DROP INDEX indexName ON tableName

length ： ****索引长度，可以不是必须的 。索引长度的作用这里解释一下：索引长度过低会造成索引区分度不高，相当于无索引，而索引长度过长，又回造成数据存储空间大，内存在用多。所以适当的索引也很重要很重要。
唯一索引数据不可重复。
无修改索引的方法，想修改则需先删除在新增。
排序是可选项，默认 ASC。
小知识：mysql的索引类型有：普通索引（normal），唯一索引（unique），全文检索索引（full text），空间索引（spatial） 。

示列：

-- select version(); mysql 8.0.28
CREATE INDEX index_name ON `user` (name(1));

ALTER TABLE user ADD INDEX index_name1(name(1));

ALTER TABLE user ADD INDEX index_name2(name);

create index index_name3 on user(id, name);  

create index index_name4 on user(id(1), name(1));  

create UNIQUE index index_name5 on user(id(1), name(1));  
 
create UNIQUE index index_name6 on user(id, name); 

create UNIQUE index index_name7 on user(id desc, name asc);

创建唯一索引报错示列子：

创建联合唯一索引报错示列

Oracle

基于Oracle 11g

创建普通索引

- CREATE INDEX indexName ON tableName (columnName [ASC | DESC] );

创建唯一索引

- CREATE UNIQUE INDEX indexName ON tableName (columnName [ ASC | DESC] );

创建普通联合索引

- create index indexName on tableName(columnName1 [ASC | DESC] , columnName2 [ ASC | DESC] , ... );

创建唯一联合索引

- create unique index indexName on tableName(columnName1 [ ASC | DESC], columnName2 [ ASC | DESC], ...);

查看索引记录脚本

- select * from all_indexes WHERE table_name='table_name' AND table_owner='table_owner';

删除索引

- DROP INDEX indexName;

oracle无法设置索引的长度。 想达到如mysql那样的效果，需要使用：substr(execute_user_ids,0,1)建函数索引，并且在在查询时，条件也要带上这个函数才能走上索引。
小知识：mysql的索引类型有：普通索引（normal），唯一索引（unique），位图索引（bitmap）。
oracle还提供了反转索引。相关资料查看： oracle索引反转是什么

示列

-- select * from v$version; 
-- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

-- 创建普通索引
CREATE INDEX INDEX_TITLE ON ARCHIVES_AUDIT(TITLE desc);
CREATE INDEX INDEX_TITLE_2 ON ARCHIVES_AUDIT(TITLE); 

-- 删除索引
DROP INDEX INDEX_TITLE;

-- 2.创建唯一索引
CREATE UNIQUE INDEX INDEX_TITLE_1 ON ARCHIVES_AUDIT(TITLE); 

-- 3.创建组合索引
CREATE INDEX INDEX_TITLE_3 ON ARCHIVES_AUDIT(TITLE,FILETIME desc);

-- 4.创建唯一组合索引
CREATE UNIQUE INDEX INDEX_TITLE_4 ON ARCHIVES_AUDIT(TITLE,FILETIME desc);

-- 查看索引
--select * from user_indexes where table_name='ARCHIVES_AUDIT';
select * from all_indexes WHERE table_name='ARCHIVES_AUDIT' AND table_owner='ARC';

PostgreSQL

基于PostgreSQL 9

创建普通索引

- CREATE INDEX indexName ON tableName [USING INDEX_TYPE] (columnName [ ASC | DESC] );

创建唯一索引

- CREATE UNIQUE INDEX indexName ON tableName [USING INDEX_TYPE] (columnName [ ASC | DESC] );

创建普通联合索引

- create index indexName on tableName [USING INDEX_TYPE] (columnName1 [ ASC | DESC] , columnName2 [ ASC | DESC] , ... );

创建唯一联合索引

- create unique index indexName on tableName [USING INDEX_TYPE] (columnName1 [ ASC | DESC] , columnName2 [ ASC | DESC] , ...);

查看索引记录脚本

- select * from pg_indexes WHERE tablename='table_name' ;

删除索引

- DROP INDEX indexName;

USING INDEX_TYPE 可以不用设置。INDEX_TYPE默认使用的 btree ，除了btree pg还提供了：

Hash, GiST, GIN ，SP-GIST，BRIN 。后面我们拿一篇来专门索引的数据结构等知识。

pgsql 也没有提供索引长度的设置。

示列

-- SELECT "version"(); 
-- PostgreSQL 9.6.22 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

-- 创建普通索引
CREATE INDEX index_description ON re_dictionary (description);

-- 创建普通索引到排序
CREATE INDEX index_description1 ON re_dictionary (description desc);

-- 创建唯一索引
CREATE UNIQUE INDEX index_code ON re_dictionary (item_code);

-- 创建唯一组合索引
CREATE UNIQUE INDEX index_code1 ON re_dictionary (type_name,item_code);
CREATE  INDEX index_code2 ON re_dictionary (type_name desc,item_code asc);

-- 删除索引
drop index index_description ;

-- 查询索引
select * from pg_indexes where tablename='re_dictionary';

SQL Server

基于 SQL Server 2017

创建普通索引

- CREATE INDEX [CLUSTERED | NONCLUSTERED] indexName ON tableName (columnName [ ASC | DESC] );

创建唯一索引

- CREATE UNIQUE [CLUSTERED | NONCLUSTERED] INDEX indexName ON tableName (columnName [ ASC | DESC] );

创建普通联合索引

- create [CLUSTERED | NONCLUSTERED] index indexName on tableName (columnName1, columnName2, ... [ ASC | DESC] );

创建唯一联合索引

- create unique [CLUSTERED | NONCLUSTERED] index indexName on tableName (columnName1, columnName2, ... [ ASC | DESC] );

查看索引记录脚本

- SELECT * FROM sys.indexes where object_id = (select OBJECT_ID('tableName'));

删除索引

- DROP INDEX indexName on tableName ;

CLUSTERED ， NONCLUSTERED : CLUSTERED 聚集索引， NONCLUSTERED非聚集索引，我们一般不需要指定，默认创建的就是NONCLUSTERED 。只有当表没有主键约束时，可以创建聚集索引。

示列

-- select @@version;
-- Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) 

-- 创建普通索引
CREATE INDEX index_login_name ON LoginLog (loginName);

-- 创建倒排序普通索引
CREATE INDEX index_login_name2 ON LoginLog (loginName desc);

-- 创建唯一普通索引
CREATE UNIQUE INDEX index_login_name3 ON LoginLog (loginName desc);

-- 创建组合索引
CREATE INDEX index_login_name3 ON LoginLog (loginName desc , LoginId asc);

-- 删除索引
DROP INDEX index_login_name on LoginLog;

-- 查询索引
SELECT * FROM sys.indexes where object_id = (select OBJECT_ID('LoginLog'));

数据库索引-1

前言

mysql

Oracle

PostgreSQL

SQL Server