首頁 > 資料庫 > mysql教程 > mysql索引简介_MySQL

mysql索引简介_MySQL

WBOY
發布: 2016-06-01 13:00:06
原創
929 人瀏覽過

索引从本质上来说也是一种表,这种表存储被列为索引的列项值和指向真正完整记录的指针。索引对用户透明,只被数据库引擎用来加速检索真实记录。有索引的表,insert和update操作会耗费更多时间而select则会变快,因为insert和update操作同时也要insert和update索引值,但这些操作对用户也透明。索引通常运用在where、join、order by语句中。

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

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

索引可以在创建表的时候创建:

<code class="hljs r">create table table_name (create_column_definition [, ...] );</code>
登入後複製

其中 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]<br /> 其中 column_definetion 可以替换成: data_type [not null | null] [default default_value] [auto_increment] [unique [key] | [primary] key] [comment &#39;string&#39;] [reference_definition]

例如:

<code class="hljs r"><code class="hljs sql">create table test(
`id` int unsigned not null auto_increment,
`data0` varchar(20),
`data1` varchar(20),
primary key (`id`),
);</code></code>
登入後複製
<code class="hljs r"><code class="hljs sql"><code class="hljs sql">create table test(
id int unsigned not null auto_increment primary key,
`data0` varchar(20),
`data1` varchar(20)
);</code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql">表创建之后也可以添加索引:<br /> 1)使用alter命令:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r">alter table table_name
[alter_specification [, alter_specification] ... ];</code></code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql"><code class="hljs r">其中 alter_sepcification 可以替换成任意一种:

<code class="hljs sql"><code class="hljs sql"><code class="hljs r">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]

<code class="hljs sql"><code class="hljs sql"><code class="hljs r">其中 index_cloumn_name 可以替换成:column_name [(length) [asc|desc]]<br /> 其中 index_type 可以替换成:using {btree|hash}<br /> 例如:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql">alter table test add unique key `index_data0` (`data0` (10));</code></code></code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql">2)使用create命令:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r">create [unique|fulltext|spatial] index index_name
on table_name (index_cloumn_name, ... ) [index_type];</code></code></code></code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r">其中 index_cloumn_name 可以替换成:column_name [(length) [asc|desc]]<br /> 其中 index_type 可以替换成:using {btree|hash}<br /> 需要注意的几点:

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r">create命令不能用于创建primary key 能够为char,varchar,binary,varbinary设置索引前缀长度,这意味着可以只索引这些字段的前面某部分。 blob和text若为索引项类型,必须指定索引前缀长度。

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r">例如:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql">create index `index_data1` on test (`data1` (10));</code></code></code></code></code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql">删除索引:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs sql">alter table table_name drop primary key;
alter table table_name drop {index | key} index_name;</code></code></code></code></code></code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs sql">当创建多列索引之后,查询全部索引或索引的前n列(与定义索引顺序一致),能够使用该索引。例如:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">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)
);</code></code></code></code></code></code></code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">以下这些查询能用到索引 name:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">select * from test where last=&#39;xyb&#39;;
select * from test where last=&#39;xyb&#39; and first_name=&#39;love&#39;;
select * from test where last=&#39;xyb&#39; and (first_name=&#39;love&#39; or first_name=&#39;Charlotte&#39;);
select * from test where last=&#39;xyb&#39; and first_name >= &#39;l&#39; and first_name <= &#39;n&#39;;</code></code></code></code></code></code></code></code></code></code>
登入後複製

<code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">以下这些chauncey不能用到索引 name:

<code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs r"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">select * from test where first_name=&#39;Charlotte&#39;;
select * from test where last=&#39;xyb&#39; or first_name=&#39;Charlotte&#39;;</code></code></code></code></code></code></code></code></code></code></code>
登入後複製
相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
最新問題
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板