mysql索引类型修改命令_mysql索引种类和操作

普通索引

最基本的索引，它没有任何限制，索引类型为Normal,索引方法为BTREE。

直接创建普通索引

CREATE INDEX index_name ON test_index(name);

修改表结构的方式添加普通索引

ALTER TABLE test_index ADD INDEX index_name(name);

创建表的时候同时创建普通索引

CREATE TABLE `table_name` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`title` char(255) NOT NULL ,

PRIMARY KEY (`id`),

INDEX index_name (title)

);

删除索引

DROP INDEX index_name ON table_name;

唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值(注意和主键不同)。

如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似,只是添加关键字UNIQUE。

索引类型为Unique,索引方法为BTREE。

直接创建唯一索引

CREATE UNIQUE INDEX index_name ON test_index(name);

修改表结构的方式添加唯一索引

ALTER TABLE test_index ADD UNIQUE INDEX index_name(name);

创建表的时候同时创建唯一索引

CREATE TABLE `table_name` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`title` char(255) NOT NULL ,

PRIMARY KEY (`id`),

UNIQUE INDEX index_name (title)

);

删除索引

DROP INDEX index_name ON table_name;

全文索引(FULLTEXT)

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；

他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。

不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

索引类型为Full Text,索引方法为空。

直接创建全文索引

CREATE FULLTEXT INDEX index_name ON table_name_full(title);

修改表结构的方式添加全文索引

ALTER TABLE table_name_full ADD FULLTEXT INDEX index_name(title);

创建表的时候同时创建全文索引

CREATE TABLE `table_name_full` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`title` char(255) NOT NULL ,

PRIMARY KEY (`id`),

FULLTEXT INDEX index_name (title)

);

删除索引

DROP INDEX index_name ON table_name;

单列索引、多列索引

多个单列索引与单个多列索引的查询效果不同

因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

组合索引(最左前缀)

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。

现在对表格test_index删除所有索引，并且添加组合索引

ALTER TABLE test_index ADD INDEX index_name_desp(name,desp);

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

name,desp

name

为什么没有desp这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引。

示例如下：

mysql语句：

select * from test_index where name = "cuiyf1465";

分析结果：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE test_index ref index_name_desp index_name_desp 768 const 1 100

其中，key值为index_name_desp 表示使用到了索引

mysql语句：

select * from test_index where desp = "handsome092";

分析结果：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE test_index ALL 96903 10 Using where

其中，key值为空 表示未使用到索引

mysql语句：

select * from test_index where name = "cuiyf034" and desp = "handsome092";

分析结果：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE test_index const index_name_desp index_name_desp 1536 const,const 1 100

其中，key值为index_name_desp 表示使用到了索引