28-创建索引

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

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

• 其中UNIQUE,FULLTEXT,SPATAIAL为可选参数,分别表示唯一索引,全文索引和空间索引
• INDEX与KEY为同义词,两者作用相同,用来指定创建索引
• col_name为需要创建索引的字段列
• index_name指定索引的名称,可选参数,如果不指定,MySQL默认col_name为索引值
• length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
• ASC或DESC可选,表示指定升序或降序的索引值存储

1.创建普通索引

最基本的索引类型.没有唯一性之类的限制,作用只是加快对数据的访问速度

使用SHOW CREATE TABLE查看表结构

使用EXPLAIN语句查看索引是否正在使用

其中

2.创建唯一索引

创建唯一索引的主要原因时减少查询索引列操作的执行时间;与普通索引类似,但是唯一索引的列值必须唯一,可以为空

3.创建单列索引

4.创建组合索引

组合索引可起几个索引的总用,但是使用时并不是随便查询哪个字段都可以使用索引的,而是遵循"最左前缀":利用索引中最左边的列集来匹配行,这样的列集被称为最左前缀;

例如:上图中由id,name和age3个字段构成的索引,索引行中按id/name/age的顺序存放,索引可以搜索下面字段组合:(id,name,age),(id,name)或者id;如果列不构成索引最左边的前缀,MySQL不能使用局部索引,如(age),(name,age)组合则不能使用索引查询;如:

如果查询(name,age)组合或单独查询name和age字段,结果如下

此时,possible_keys和key值为NULL,并没有在t3表中创建的索引进行查询

5.创建全文索引

FULLTEXT(全文索引)可以用于全文搜索;只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR,VARCHAR和TEXT列创建索引;索引总是对整个列进行,不支持局部(前缀)索引

因为MySQL5.7默认的存储引擎为InnoDB,在这里创建表时需要修改表的存储引擎为MyISAM,不然创建索引会出错

全文索引非常适合大型数据集,对于小的数据集,它的用处比较小

6.创建空间索引

空间索引必须在MyISAM类型的表中创建.且空间类型的字段必须为非空

2.在已存在的表上创建索引

1.使用ALTER TABLE语句创建索引

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

添加索引之前,使用SHOW INDEX语句查看指定表中创建的索引:

其中各个参数的含义为"

下面使用ALTER TABLE 在bookname字段上添加索引,SQL语句如下:

ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

2.使用CREATE INDEX创建索引

MySQL中CREATE INDEX被映射到一个ALTER TABLE语句上,基本语法为

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

转载于:https://my.oschina.net/majorx/blog/3065389