文章目錄

• 索引
• 什么是索引(index）
• • 索引有什么作用
  • 索引的分類
  • 創(chuàng)建索引有什么原則
  • 什么是外鍵
• 創(chuàng)建索引的語法
• • 如何查看索引
  • 刪除索引的方法 drop
  • 創(chuàng)建普通索引
  • 創(chuàng)建主鍵索引
  • 創(chuàng)建唯一索引
  • 創(chuàng)建全文索引

索引

什么是索引(index）

數(shù)據(jù)庫中的索引與書籍中的目錄類似

在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)

需要額外的磁盤空間

索引有什么作用

• 設(shè)置了合適的索引之后，數(shù)據(jù)庫利用各種快速的定位技術(shù)，能夠大大加快查詢速率
• 特別是當(dāng)表很大時(shí)，或者查詢涉及到多個(gè)表時(shí)，使用索引可使查詢加快成干倍
• 可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本
• 通過創(chuàng)建唯一性索引保證數(shù)據(jù)表數(shù)據(jù)的唯一性
• 可以加快表與表之間的連接
• 在使用分組和排序時(shí)，可大大減少分組和排序時(shí)間

索引的分類

• 普通索引
  • 這是最基本的索引類型，而且它沒有唯一性之類的限制
• 唯一性索引
  • 這種索引和前面的“普通索引”基本相同，但有一個(gè)區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須唯一
• 主鍵
  • 主鍵是一種唯一性索引，但它必須指定為“ PRIMARY KEY
• 全文索引
  • MySQL從32323版開始支持全文索引和全文檢索。在 MySQL中全文索引的索引類型為 FULLTEXT，全文索引可以在 ARCHAR或者TEXT類型的列上創(chuàng)建
• 單列索引與多列索引
  • 索引可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引

創(chuàng)建索引有什么原則

• 表的主鍵、外鍵必須有索引
• 數(shù)據(jù)量超過300行的表應(yīng)該有索引
• 經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引
• 唯一性太差的字段不適合建立索引
• 更新太頻繁地字段不適合創(chuàng)建索引
• 經(jīng)常出現(xiàn)在 Where子句中的字段，特別是大表的字段，應(yīng)該建立索引
• 索引應(yīng)該建在選擇性高的字段上
• 索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長字段，不要建索引

什么是外鍵

• 主表中的外鍵是令一張表的主鍵

創(chuàng)建索引的語法

• 創(chuàng)建普通索引
  最基本的索引類型，沒有唯一性之類的限制
  創(chuàng)建普通索引的方式

語法：方法一；create index <索引的名字> on 表名（列表名）；方法二：mysql> alter table （表名） add 索引名（列表名））;方法三：在建表時(shí)創(chuàng)建索引mysql> create table test(...index abc（id）)；例如：

• 創(chuàng)建唯一性索引
  與“普通索引”基本相同

與普通索引的區(qū)別是索引列的所有值只能出現(xiàn)一次，即必須唯一

創(chuàng)建唯一索引的方式

方法一： create unique index 索引名 on 表名 (列表名);方法二：alter table 表名 add unique 索引名(列表名);方法三：在建表時(shí)就創(chuàng)建索引mysql> create table test(...unqiue index abc（id）)；

• 創(chuàng)建主鍵索引
  是一種特殊的唯一索引，指定為“PRIMARY KEY”
  一個(gè)表只能有一個(gè)主鍵，不允許有空值
  創(chuàng)建主鍵索引的方式

• 創(chuàng)建全文索引

1 直接創(chuàng)建 mysql> create unique index index_name on table_name(column(length));2 修改表的方式創(chuàng)建 mysql> alter table table_name add unique index index_name(column(length));3 創(chuàng)建表的時(shí)候創(chuàng)建 mysql> create table table_name (id int(3) not null auto_increment,name varchar(10) not null,score decimal(5,2) not null,address varchar(50) default '未知',primary key(id),unique index index_name(id));

組合索引 單列索引與多列索引

create table index isis on benat (id,name);

如何查看索引

語法： show index from tablename; show keys from tablename; mysql> show index from num\G; 縱向查看索引 mysql> show index from num; 橫著查看索引

刪除索引的方法 drop

drop index 索引名 on 表名; alter table 表名 drop index 索引名; alter table 表名 drop primary key; ## 刪除主鍵索引

創(chuàng)建普通索引

最基本的索引類型，沒有唯一性之類的限制
創(chuàng)建普通索引方式（3種）

1 直接創(chuàng)建 mysql> create index index_name on table_name(column(length)); 'lengtn是可選項(xiàng)'2 修改表的方式創(chuàng)建（修改表結(jié)構(gòu)） mysql> alter table table_name add index index_name (column(length));3 創(chuàng)建表的時(shí)候創(chuàng)建 mysql> create table table_name (id int(3) auto_increment,name varchar(10) not null,score decimal(5,2) not null,address varchar(50) default '未知',primary key(id),index index_name(id(3)));

舉例子

mysql> create table infos (id int(4),name varchar(10)); 創(chuàng)建一個(gè)表 mysql> desc infos; 查看表 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> create index id_index on infos(id); 創(chuàng)建索引 mysql> desc infos; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | MUL（索引） | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show index from infos\G; 查看索引 *************************** 1. row ***************************Table: infosNon_unique: 1Key_name: id_indexSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment:

方法2

mysql> alter table info add index name_index (name) mysql> create table table_name (id int(3) not null auto_increment,name varchar(10) not null,primary key(id)); mysql> desc info; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | MUL | NULL | | | name | varchar(10) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)mysql> show index from info\G *************************** 2. row ***************************Table: infoNon_unique: 1Key_name: name_indexSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment: 2 rows in set (0.00 sec)

方法3

mysql> create table num (id int,index id_index(id)); mysql> desc num; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)mysql> show index from num\G *************************** 1. row ***************************Table: numNon_unique: 1Key_name: id_indexSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment: 1 row in set (0.00 sec)

創(chuàng)建主鍵索引

• 是一種特殊的唯一索引,指定為 “primary key”
• 一個(gè)表只能有一個(gè)主鍵,不允許有空值（唯一且非空）
• 創(chuàng)建主鍵索引的方式

創(chuàng)建唯一索引

創(chuàng)建唯一索引的方式（3種）

1 直接創(chuàng)建 mysql> create unique index index_name on table_name(column(length));2 修改表的方式創(chuàng)建 mysql> alter table table_name add unique index index_name(column(length));3 創(chuàng)建表的時(shí)候創(chuàng)建 mysql> create table table_name (id int(3) not null auto_increment,name varchar(10) not null,score decimal(5,2) not null,address varchar(50) default '未知',primary key(id),unique index index_name(id));

舉例子

mysql> create table benat (id int(4),name vaechar(10)); mysql> desc benat; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+mysql> create unique index unique_name on infos(name);mysql> show index from benat\G *************************** 1. row ***************************Table: benatNon_unique: 0Key_name: unique_nameSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment: 1 row in set (0.00 sec)

方法2

mysql> alter table benat add unique index_id(name) mysql> desc benat; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | UNI | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> show index from benat\G *************************** 2. row ***************************Table: benatNon_unique: 0Key_name: index_idSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment:

創(chuàng)建全文索引

alter table test01 add fulltext index abc(address); mysql> create table index isis on benat (id,name); info;mysql> show index from benat; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | benat | 1 | isis | 1 | id | A | 4 | NULL | NULL | | BTREE | | | | benat | 1 | isis | 2 | name | A | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 8 rows in set (0.00 sec)

創(chuàng)建索引的原則依據(jù)

• 表的主鍵、外鍵必須有索引
• 記錄數(shù)超過300行的表應(yīng)該有索引
• 經(jīng)常與其他表進(jìn)行連接的表,在連接字段上應(yīng)該建立索引
• 唯一性太差的字段不適應(yīng)建立索引
• 更新太頻繁地字段不適合創(chuàng)建索引
• 經(jīng)常出現(xiàn)呢在where子句中的字段,特別是大表的字段,應(yīng)該建立索引
• 索引應(yīng)該建在選擇性高的字段上
• 索引應(yīng)該建在小字段上,對(duì)于大的文本字段甚至超長字段,不要建索引

總結(jié)

以上是生活随笔為你收集整理的msyql索引用法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。