欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL中B+树索引的管理

程序员文章站 2022-05-14 21:01:33
...

目前mysql数据库存在的一个普遍的问题是,所有对于索引的添加或者删除操作,mysql数据库是先创建一张新的临时表,然后把数据导入

索引的创建和删除可以通过两种方法;一种是alter table ,另一种是create /drop index.alter table 创建索引的语法:
alter table tbl_name
|ADD {INDEX|KEY} {INDEX_NAME}
{INDEX_TYPE}(INDEX_COL_NAME,......)[INDEX_OPTION].............
|DROP{INDEX|KEY}INDEX_NAME

alter table tbl_name drop primary key;


create/drop index的语法同样很简单;
create [unique] index index_name
[index_type] on tbl_name(index_col_name...)

drop index index_name on tbl_name;

drop index index_name on tbl_name;


索引可以索引整个列的数据,也可以只索引列的开头部分的数据,如我们前面创建的表t,b列为varchar(8000),我们只索引前100个字段,如;
mysql> alter table t add key idx_b(b(100));
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>
目前mysql数据库存在的一个普遍的问题是,所有对于索引的添加或者删除操作,mysql数据库是先创建一张新的临时表,然后把数据导入临时表,删除原表,再把临时表重名为原来的表名,。因此对于一张大表,添加和删除索引需要很长的时间。

innodb存储引擎从版本innodb plugin开始,支持一种快速索引创建方法;当然这种方法只限制于辅助索引,对于主键的创建和删除还是需要重新创建一个表,对于辅助索引的创建,innodb存储引擎会对表加上一个S锁,。在创建的过程中,不需要重建表 ,因此速度极快。但是在创建过程中,由于上了S锁,,因此创建的过程中该表只能进行读操作,删除辅助索引操作就更简单,只需要在innodb内部视图进行更新,将辅助索引的空间标记为可用,并删除mysql内部视图上对于该表的索引定义即可;

查看表中索引的信息可以使用show index语句,如我们分析表t,之前加一个联合索引,,如:

mysql> alter table t add key idx_a_b(a,c);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from t\G;
*************************** 1. row ***************************
Table: t
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: t
Non_unique: 1
Key_name: idx_c
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: t
Non_unique: 1
Key_name: idx_b
Seq_in_index: 1
Column_name: b
Collation: A
Cardinality: 4
Sub_part: 100
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: t
Non_unique: 1
Key_name: idx_a_b
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: t
Non_unique: 1
Key_name: idx_a_b
Seq_in_index: 2
Column_name: c
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
5 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

MySQL中B+树索引的管理