mysql索引简单介绍

索引从本质上来说也是一种表,这样的表存储被列为索引的列项值和指向真正完整记录的指针。索引对用户透明。仅仅被数据库引擎用来加速检索真实记录。有索引的表。insert和update操作会耗费很多其它时间而select则会变快。由于insert和update操作同一时候也要insert和update索引值。但这些操作对用户也透明。

索引通常运用在where、join、order by语句中[1]。

在mysql中,index和key是同义词,两者能够互换[2]。一张表最多能有16个索引。每一个索引最多由15个列组成。mysql中有4种类型索引。索引根据表类型(也既是存储引擎)有不同存储方式[3]。

  • primary key 能够指定一列或多列组合成索引,用来唯一确定一条记录,每一个指定为primary key的项必须唯一,且不能为空。一张表至多有一个primary key。设置了 primary key 的表自己主动为 primary key

    建立索引。
  • unique key 与 primary key 相似,能够指定一列或多列组合成索引,用来唯一确定一条记录。

    每一个指定为 unique key 的项必须唯一,同意有NULL值。一张表能够有多个 unique key。

  • fulltext类型索引仅仅能用于MyISAM表,也仅仅能用于char。varchar,text列项。
  • 普通 index。没有唯一性限制,也没有非空限制。
存储引擎 可能的索引类型
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若为索引项类型,必须指定索引前缀长度[5]。

比如:

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列(与定义索引顺序一致),能够使用该索引[6]。比如:

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';

综合解说索引能够參见链接[7]

參考链接:

[1]http://www.tutorialspoint.com/mysql/mysql-indexes.htm

[2]http://*.com/questions/3844899/difference-between-key-primary-key-unique-key-and-index-in-mysql

[3]https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

[4]https://dev.mysql.com/doc/refman/5.0/en/alter-table.html

[5]https://dev.mysql.com/doc/refman/5.0/en/create-table.html

[6]https://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

[7]http://blog.csdn.net/tian*st/article/details/7930482

上一篇:基于 USB 传输的针式打印机驱动程序开发


下一篇:【墙裂推荐】大学生如何学习WEB开发