首页 > > 网络编程 > Mysql >

MySQL->索引的维护[20180504]

2018-06-18 01:24:22来源：未知阅读 ()

学习MySQL数据库中表的索引维护(新增和删除)

索引的好处：

提高查询的效率

可限定特定的资料（如唯一）

索引的不足：

如果过多的索引，会影响Insert，Update的速率，因为资料插入和更新同时也会维护索引。

消耗更多的存储空间

索引按列可分为，单列索引和组合索引。单列只包含表中的一列，组合索引可包含当前表中多列栏位。

创建普通索引

CREATE INDEX <index_name> ON <tab_name>(<col_name>(length));

如果列的类型是CHAR，VARCHAR可以不指定length的值。

如果列的类型是BLOB，TEXT则需指定length的值

创建表时建立索引

CREATE TABLE <tab_name>(

<col_name> TYPE,....

[INDEX_TYPE] <index_name>(<col_name>(length))

engine=InnoDB default charset=utf8;

mysql> create table index_tab03(
    ->     id int,
    ->    col01 varchar(20),
    ->     index index_01(col01)
    ->      )engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
 
mysql> desc index_tab03;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| col01 | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> show index from index_tab03\G
*************************** 1. row ***************************
       Table: index_tab03
  Non_unique: 1
    Key_name: index_01
Seq_in_index: 1
Column_name: col01
   Collation: A
Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)
 m

ALTER语句维护索引

ALTER TABLE <tab_name> ADD PRIMARY KEY(<column_list>);#增加一个主键，唯一和非空

ALTER TABLE <tab_name> ADD UNIQUE <index_name>(<column_list>);#增加一个唯一索引，列中可以为NULL并且可以有多次

ALTER TABLE <tab_name> ADD INDEX <index_name>(<column_list>);#增加一个普通索引

ALTER TABLE <tab_name> ADD FULLTEXT <index_name>(<column_list>);增加一个全文索引，注意只有MyISAM引擎支持

mysql> alter table index_tab01 add primary key(id,col01);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table index_tab01 add index indx_01(col01);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table index_tab01 add fulltext indx_02(col01,col02(300));
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
 
mysql> alter table index_tab01 engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table index_tab01 add fulltext index_02(col01,col02(30));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from index_tab01\G
*************************** 1. row ***************************
       Table: index_tab01
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
   Collation: A
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: index_tab01
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 2
Column_name: col01
   Collation: A
Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: index_tab01
  Non_unique: 0
    Key_name: index_un
Seq_in_index: 1
Column_name: col01
   Collation: A
Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 4. row ***************************
       Table: index_tab01
  Non_unique: 1
    Key_name: indx_01
Seq_in_index: 1
Column_name: col01
   Collation: A
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 5. row ***************************
       Table: index_tab01
  Non_unique: 1
    Key_name: index_02
Seq_in_index: 1
Column_name: col01
   Collation: NULL
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: FULLTEXT
     Comment:
*************************** 6. row ***************************
       Table: index_tab01
  Non_unique: 1
    Key_name: index_02
Seq_in_index: 2
Column_name: col02
   Collation: NULL
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment:
6 rows in set (0.00 sec)

索引删除

DROP INDEX <index_name> ON <tab_name>;

ALTER TABLE <tab_name> DROP PRIMARY KEY;

ALTER TABLE <tab_name> DROP INDEX <index_name>;

mysql> alter table index_tab01 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 
mysql> alter table index_tab01 drop index index_02;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> drop index index_01 on index_tab01;

查看索引结构

SHOW INDEX FROM <tab_name>\G

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有