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

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

--创建索引
mysql> use test;
Database changed
mysql> 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: 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:
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: 0

mysql>

唯一索引

MySQL 有两种方式

--方式1
mysql> alter table department add unique dept_nme_idx (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index dept_nme_idx on department;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

--方式2
mysql> 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>