时间:2021-07-01 10:21:17 帮助过:64人阅读
在mysql中,index和key是同义词,两者可以互换。一张表最多能有16个索引,每个索引最多由15个列组成。mysql中有4种类型索引。索引依据表类型(也既是存储引擎)有不同存储方式。
primary key 可以指定一列或多列组合成索引,用来唯一确定一条记录,每个指定为primary key的项必须唯一,且不能为空。一张表至多有一个primary key。设置了 primary key 的表自动为 primary key存储引擎 | 可能的索引类型 |
---|---|
MyISAM | btree |
InnoDB | btree |
Memory/Heap | hash,btree |
NDB | btree,hash |
索引可以在创建表的时候创建:
create table table_name (create_column_definition [, ...] );
其中 create_column_definition 可以替换成:
column_name column_definetion [constraint [symbol]] primary key (column_name, ...) [index_type] [constraint [symbol]] unique [index|key] (column_name, ...) [index_type] {index|key} [index_name] (column_name, ...) [index_type] {fulltext} [index | key] (column_name, ...) [index_type]
其中 column_definetion 可以替换成: data_type [not null | null] [default default_value] [auto_increment] [unique [key] | [primary] key] [comment 'string'] [reference_definition]
例如:
create table test(
`id` int unsigned not null auto_increment,
`data0` varchar(20),
`data1` varchar(20),
primary key (`id`),
);
create table test(
id int unsigned not null auto_increment primary key,
`data0` varchar(20),
`data1` varchar(20)
);
表创建之后也可以添加索引:
1)使用alter命令:
alter table table_name
[alter_specification [, alter_specification] ... ];
其中 alter_sepcification 可以替换成任意一种:
add [constraint [symbol]] primary key (index_cloumn_name, ... ) [index_type] add [constraint [symbol]] unique [index|key] [index_name] (index_cloumn_name, ... ) [index_type] add {index | key} [index_name] (index_cloumn_name, ... ) [index_type] add [fulltext] [index|key] [index_name] (index_cloumn_name, ... ) [index_type]
其中 index_cloumn_name 可以替换成:column_name [(length) [asc|desc]]
其中 index_type 可以替换成:using {btree|hash}
例如:
alter table test add unique key `index_data0` (`data0` (10));
2)使用create命令:
create [unique|fulltext|spatial] index index_name
on table_name (index_cloumn_name, ... ) [index_type];
其中 index_cloumn_name 可以替换成:column_name [(length) [asc|desc]]
其中 index_type 可以替换成:using {btree|hash}
需要注意的几点:
create命令不能用于创建primary key 能够为char,varchar,binary,varbinary设置索引前缀长度,这意味着可以只索引这些字段的前面某部分。 blob和text若为索引项类型,必须指定索引前缀长度。
例如:
create index `index_data1` on test (`data1` (10));
删除索引:
alter table table_name drop primary key;
alter table table_name drop {index | key} index_name;
当创建多列索引之后,查询全部索引或索引的前n列(与定义索引顺序一致),能够使用该索引。例如:
create table test(
id int not null auto_increment,
last_name char(30) not null,
first_name char(30) not null,
primary key (id),
index name (last_name, first_name)
);
以下这些查询能用到索引 name:
select * from test where last='xyb';
select * from test where last='xyb' and first_name='love';
select * from test where last='xyb' and (first_name='love' or first_name='Charlotte');
select * from test where last='xyb' and first_name >= 'l' and first_name <= 'n';
以下这些chauncey不能用到索引 name:
select * from test where first_name='Charlotte';
select * from test where last='xyb' or first_name='Charlotte';