MySQL-SQL Server、Oracle中的索引维护方式

来源：互联网发布：加强网络监督文化建设编辑：程序博客网时间：2024/05/18 03:39

在MySQL中索引作为表附属的一部分，无法对索引进行单独维护，都用alter table的方式来进行。

--创建索引mysql> use test;Database changedmysql> alter table department    -> add index dept_name_idx (name);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0--查看表的索引信息（主键自动生成索引）mysql> show index from department \G*************************** 1. row ***************************        Table: department   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: dept_id    Collation: A  Cardinality: 3     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:*************************** 2. row ***************************        Table: department   Non_unique: 1     Key_name: dept_name_idx Seq_in_index: 1  Column_name: name    Collation: A  Cardinality: 3     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:2 rows in set (0.00 sec)--删除一个索引mysql> alter table department    -> drop index dept_name_idx;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from department \G*************************** 1. row ***************************        Table: department   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: dept_id    Collation: A  Cardinality: 3     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:1 row in set (0.00 sec)mysql>

其他数据库的索引维护方式

Oracle

create index dept_name_idx on department (name);
drop index dept_name_idx;

MySQL、SQL Server

--创建索引mysql> create index dept_name_idx on department (name);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0--删除索引mysql> drop index dept_name_idx on department;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql>

唯一索引

MySQL 有两种方式

--方式1mysql> alter table department add unique dept_nme_idx (name);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> drop index dept_nme_idx on department;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0--方式2mysql> create unique index dept_name_idx    -> on department (name);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0--查看mysql> show index from department \G*************************** 1. row ***************************        Table: department   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: dept_id    Collation: A  Cardinality: 3     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:*************************** 2. row ***************************        Table: department   Non_unique: 0     Key_name: dept_name_idx Seq_in_index: 1  Column_name: name    Collation: A  Cardinality: 3     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:2 rows in set (0.00 sec)mysql>

0 0