MySQL中的索引

1.1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

• 从 功能逻辑 按照上说，索引主要有 4 种：普通索引、唯一索引、主键索引、全文索引。
  
• 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
  
• 按照 作用字段个数 进行划分，分成单列索引和联合索引。
  

留意：不同的存储引擎支持的索引范例也不一样

• InnoDB：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  
• MyISAM：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  
• Memory：支持 B-tree、Hash 等 索引，不支持 Full-text 索引；
  
• NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
  
• Archive：不支 持 B-tree、Hash、Full-text 等索引;
  

---

1.2 创建索引

1、创建表的时候创建索引

隐式的索引创建：

1. # 1.隐式的添加索引(在添加有主键约束、唯一性约束或者外键约束的字段会自动的创建索引)
2. CREATE TABLE dept(
3.     dept_id INT PRIMARY KEY AUTO_INCREMENT,# 创建主键索引
4.     dept_name VARCHAR(20)
5. );
6. CREATE TABLE emp(
7.     emp_id INT PRIMARY KEY AUTO_INCREMENT,# 主键索引
8.     emp_name VARCHAR(20) UNIQUE,# 唯一索引
9.     dept_id INT,
10.     CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
11. ); # 外键索引

复制代码

显式的索引创建：

1. CREATE TABLE table_name [col_name data_type]
2. [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

复制代码

• UNIQUE、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引;
  
• INDEX与KEY 为同义词，两者的作用雷同，用来指定创建索引;
  
• index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名;
  
• col_name为必要创建索引的字段列，该列必须从数据表中界说的多个列中选择;
  
• length 为可选参数，表示索引的长度，只有字符串范例的字段才气指定索引长度;
  
• ASC 或 DESC 指定升序或者降序的索引值存储。
  
• 特例：主键索引利用主键束缚的方式来创建。
  

1、创建普通索引

在book表中的year_publication字段上创建普通索引，SQL语句如下:

1. # ①创建普通的索引
2. CREATE TABLE book(
3.     book_id INT ,
4.     book_name VARCHAR(100),
5.     AUTHORS VARCHAR(100),
6.     info VARCHAR(100) ,
7.     COMMENT VARCHAR(100),
8.     year_publication YEAR,
9.     # 声明索引
10.     INDEX idx_bname(book_name)
11. );

复制代码

通过命令查看索引有没有创建乐成

1. # 方式1：
2. SHOW CREATE TABLE book; # Linux下添加\G参数

复制代码

1. # 方式2：
2. SHOW INDEX FROM book;

复制代码

1. #性能分析工具：EXPLAIN，查看索引是否正在使用
2. EXPLAIN SELECT * from book where book_name = 'mysql高级';

复制代码

EXPLAIN语句输出效果的各个行我们在下一章解说，这里主要关注两个字段

• possible_keys行给出了MySQL在搜刮数据记录时可选用的各个索引
  
• key行时MySQL实际选用的索引
  

可以看到，possible_keys和key值都为idx_bname，查询时利用了索引
2、创建唯一索引       

1. # ②创建唯一索引
2. CREATE TABLE book1 (
3.   book_id INT,
4.   book_name VARCHAR (100),
5.   AUTHORS VARCHAR (100),
6.   info VARCHAR (100),
7.   COMMENT VARCHAR (100),
8.   year_publication YEAR,
9.   #声明索引
10.   UNIQUE INDEX uk_idx_cmt (COMMENT)
11. );

复制代码

1. show INDEX from book1;# 查看索引

复制代码

3、主键索引

设定为主键后数据库会自动创建索引，innodb为聚簇索引，语法:
随表一起建索引:

1. # ③主键索引
2. # 通过定义主键约束的方式定义主键索引
3. create table book2(
4.     book_id int primary key,
5.     book_name varchar(100),
6.     AUTHORS VARCHAR (100),
7.     info VARCHAR (100),
8.     COMMENT VARCHAR (100),
9.     year_publication YEAR
10. ) ;

复制代码

删除主键索引:

1. # 通过删除主键约束的方式删除主键索引
2. alter table book2
3. drop primary key;

复制代码

修改主键索引：必须先删撤除（drop）原索引，再新建（add）索引
4、创建单列索引

1. #④ 创建单列索引
2. CREATE TABLE book3(
3.     book_id INT,
4.     book_name VARCHAR(100),
5.     AUTHORS VARCHAR (100),
6.     info VARCHAR (100),
7.     COMMENT VARCHAR (100),
8.     year_publication YEAR,
9.     UNIQUE INDEX idx_bname(book_name)
10. );
12. show index from book3;

复制代码

5、创建组合索引

        举例：创建表 book4，在表中的 book_id、book_name和 info字段上创建组合索引，SQL 语句如下：

1. # ⑤ 创建联合索引
2. create table book4(
3.     book_id INT,
4.     book_name VARCHAR(100),
5.     AUTHORS VARCHAR (100),
6.     info VARCHAR (100),
7.     COMMENT VARCHAR (100),
8.     year_publication YEAR,
9.     index mul_bid_bname_info(book_id,book_name,info)       
10. )
12. SHOW INDEX FROM book4;

复制代码

留意上面三行依次是book_id,book_name,info，与我们创建索引时指定的顺序是严格对应的。在查询时会遵守最左索引原则，先进行book_id条件的比较，然后再进行book_name比较，最后才是info。因此留意把最常用的查询字段放在索引的最左边。

1. # 分析
2. explain select * from book4 where book_id = 1001 and book_name = 'mysql'; # 会使用到mul_bid_bname_info索引
4. explain select * from book4 where book_name = 'mysql';# 不会使用到mul_bid_bname_info索引

复制代码

6、 创建全文索引

FULLTEXT全文索引可以用于全文搜刮，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。
举例1：创建表test4，在表中的 info 字段上创建全文索引，SQL 语句如下:

1. CREATE TABLE test4(
2.     id INT NOT NULL,
3.     name CHAR(30) NOT NULL,
4.     age INT NOT NULL,
5.     info VARCHAR(255),
6.     FULLTEXT INDEX futxt_idx_info(info)
7. ) ENGINE=MyISAM;

复制代码

  在 MySQL 5.7 及之后版本中可以不指定最后的 ENGINE 了，因为在此版本中 InnoDB 支持全文索引。
  语句执行完毕后，用SHOW CREATE TABLE查看表结构：

1. SHOW INDEX FROM test4\G;

复制代码

由效果可以看到，info字段上已经乐成创建了一个名为futxt_idx_info的FULLTEXT索引。
举例2：

1. CREATE TABLE articles (
2.     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3.     title VARCHAR (200),
4.     body TEXT,
5.     FULLTEXT index (title, body)
6. ) ENGINE = INNODB ;

复制代码

创建了一个给 title 和 body 字段添加全文索引的表。
举例3：

1. CREATE TABLE `papers` (
2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3.   `title` varchar(200) DEFAULT NULL,
4.   `content` text,
5.   PRIMARY KEY (`id`),
6.   FULLTEXT KEY `title` (`title`,`content`)
7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

复制代码

不同于 like 方式的的查询:

1.  SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;

复制代码

全文索引用 match+against 方式查询:

1. SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

复制代码

显着的进步查询服从
   留意点
  

• 利用全文索引前，搞清晰版本支持情况；
  
• 全文索引比like + % 快 N倍，但是可能存在精度问题
  
• 如果必要全文索引的是大量数据，建议先添加数据，再创建索引。
  

  

---

2、在已经存在的表上创建索引

在已经存在的表中创建索引可以利用 ALTER TABLE 语句或者 CREATE INDEX 语句。
1. 利用 ALTER TABLE 语句创建索引

1. ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
2. [index_name] (col_name[length],...) [ASC | DESC]

复制代码

2. 利用 CREATE INDEX 创建索引

CREATE INDEX 语句可以在已经存在的表上添加索引，在 MySQL 中， CREATE INDEX 被映射到一个 ALTER TABLE 语句上，根本语法结构为：

1. CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
2. ON table_name (col_name[length],...) [ASC | DESC]

复制代码

举例1：在book表的comment字段上创建名为 的普通索引

1. create index idx_cmt on book(comment);

复制代码

举例2：在book表中的book_id字段上创建名为uk_idx_bid的唯一索引，SQL语句如下：

1. CREATE UNIQUE INDEX uk_idx_bid ON book(book_id);

复制代码

举例3：在book表的book_id、book_name、info字段上创建联合索引，SQL语句如下：

1. CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);

复制代码

---

1.3 删除索引

MySQL中删除索引利用ALTER TABLE或DROP INDEX语句，两者可实现雷同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中
1. 利用 ALTER TABLE 删除索引：

ALTER TABLE删除索引的根本语法格式如下：

1. ALTER TABLE table_name DROP INDEX index_name;

复制代码

练习：删除book表中名称为idx_bk_id的唯一索引
起首查看book表中是否名称为idx_bk_id的索引，输入SHOW语句如下：

1. SHOW INDEX FROM book\G;

复制代码

下面删除该索引，输入删除语句如下：

1. ALTER TABLE book DROP INDEX idx_bk_id;

复制代码

  提示
  添加AUTO_INCREMENT束缚字段的唯一索引不能被删除（）
  2. 利用 DROP INDEX 语句删除索引：

DROP INDEX删除索引的根本语法格式如下：

1. DROP INDEX index_name ON table_name;

复制代码

练习：删除book表中名称为idx_aut_info的组合索引，SQL语句如下：

1. DROP INDEX idx_aut_info ON book;

复制代码

语句执行完毕，利用SHOW查看索引是否删除：

1. SHOW CREATE TABLE book\G;

复制代码

可以看到，book表中已经没著名称为idx_aut_info的组合索引，删除索引乐成。
   提示 删除表中的列时，如果要删除的列为索引的构成部分，则该列也会从索引中删除。如果构成索引的所有列都被删除，则整个索引将被删除。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。