MySQL 如何添加索引

表中添加索引的三种方式

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

隐式创建：使用CREATE TABLE创建表时，在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引。

#CREATE TABLE时隐式创建索引

CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

可以使用 SHOW INDEX FROM 表名，来查看索引，如下：

显示创建：

• 创建普通索引

  #创建普通索引

  CREATE TABLE book(
  book_id INT,
  book_name VARCHAR(100),
  authors VARCHAR(100),
  info VARCHAR(100),
  comment VARCHAR(100),
  year_publication YEAR,
  #声明普通索引
  INDEX idx_bname(book_name)
  );

• 创建唯一索引

声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加NULL值。

#创建唯一索引

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
UNIQUE INDEX uk_idx_cmt(`comment`)
);
1
2
3
4
5
6
7
8
9
10
11

• 创建主键索引

通过定义主键约束的方式定义主键索引，如上所示。不能类比以PRIMARY KEY INDEX方式创建。

• 创建单列索引

这个没必要再赘述，以上作用于单个字段索引的都可称之为单列索引。

• 创建联合索引

如下表，联合索引会以book_id, book_name, info的顺序进行排序，声明时顺序颠倒排序也会变，即遵循最左前缀原则。所以要把最常检索的字段放在最前面！

#创建联合索引

CREATE TABLE book2(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
#声明普通索引
INDEX mulidx_bid_bname_info(book_id, book_name, info)
);
1
2
3
4
5
6
7
8
9
10
11
12

• 创建全文索引

值得说明的是，全文索引比LIKE + %的方式快N倍！但要注意版本支不支持！此外，如果需要全文索引的是大量数据，建议先添加数据，后创建索引。

#创建全文索引

CREATE TABLE book3(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
FULLTEXT INDEX futxt_idx_info(info(50))
);
1
2
3
4
5
6
7
8
9
10
11

在表已经创建之后再添加索引

• 通过 ALTER TABLE 表名 ADD 索引 这种方式来添加索引

  CREATE TABLE book4(
  book_id INT,
  book_name VARCHAR(100),
  authors VARCHAR(100),
  info VARCHAR(100),
  comment VARCHAR(100),
  year_publication YEAR,
  );

  ALTER TABLE book4 ADD INDEX idx_cmt(comment);

其他的索引也以类似的方法创建，不再赘述。

• 通过 CREATE INDEX 索引 ON 表(列)

  CREATE INDEX idx_cmt ON book4(comment);

最后

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

小编已加密：aHR0cHM6Ly9kb2NzLnFxLmNvbS9kb2MvRFVrVm9aSGxQZUVsTlkwUnc==出于安全原因，我们把网站通过base64编码了，大家可以通过base64解码把网址获取下来。