15.MySQL(三) 索引类型

索引类型

先创建表

mysql> CREATE TABLE test(
    -> id INT,
    -> username VARCHAR(16),
    -> city VARCHAR(16),
    -> age INT
    -> );

1.普通索引

是最基本的索引,它没有任何的限制。有以下几种创建方式

(1)直接创建索引:

CREATE INDEX index_name ON table(column(length))

 实例:

CREATE INDEX test_username ON test (username(10));          -->单列索引

indexName为索引名,mytable表名,username和city为列名,10为前缀长度,即索引在该列从最左字符开始存储的信息长度,单位字节

如果是CHAR,VARCHAR类型,前缀长度可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 前缀长度,下同。

(2)修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

  ALTER TABLE test ADD INDEX test_username(username(10));

此处 indexName 索引名可不写,系统自动赋名 username ,username_2 ,username_3,...

(3)创建表的时候同时创建索引

mysql> CREATE TABLE test(
    -> id INT,
    -> username VARCHAR(16),
    -> city VARCHAR(16),
    -> age INT,
    -> INDEX test_username (username(10))
    -> );

2.唯一索引

与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式: (1)创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

  实例:

CREATE UNIQUE INDEX test_city ON test(city(10));

(2)修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

(3)创建表的时候直接指定

mysql> CREATE TABLE test(
    -> id INT,
    -> username VARCHAR(16),
    -> city VARCHAR(16),
    -> age INT,
    -> UNIQUE test_username (username(10))
    -> );

3.主键索引

是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:

主键索引无需命名,一个表只能有一个主键。主键索引同时可是唯一索引或者全文索引,但唯一索引或全文索引不能共存在同一索引

(1)修改表结构创建

ALTER TABLE test ADD PRIMARY KEY (id);

(2)创建表的时候直接指定

mysql> CREATE TABLE test(
    -> id INT,
    -> username VARCHAR(16),
    -> city VARCHAR(16),
    -> age INT,
    -> PRIMARY KEY(id)
    -> );

4.组合索引

指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合

 ALTER TABLE test ADD INDEX test_username_city (username,city);

5.全文索引

主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

(1)直接创建

CREATE FULLTEXT INDEX full_username ON test (username);

(2)修改表结构添加全文索引

ALTER TABLE test ADD FULLTEXT INDEX full_city (city);

(3)创建表的时候直接指定

mysql> CREATE TABLE test(
    -> id INT,
    -> username VARCHAR(16),
    -> city VARCHAR(16),
    -> age INT,
    -> FULLTEXT indexName (username(10))
    -> );

6.查看索引

第一种:show create table test;
第二种:SHOW INDEX FROM test \G;

7.删除索引

DROP INDEX index_name ON table;
ALTER TABLE test DROP PRIMARY KEY;  -->删除主键

实例:

DROP INDEX test_city ON test;

DROP INDEX full_username ON test;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java达人

left join 过滤条件写在on后面和写在where 后面的区别

create table t1(id int, feild int); insert into t1 values(1 , 1); insert into t1...

291100
来自专栏性能与架构

MySQL中索引的限制

下面列出了目前 MySQL中与索引使用相关的限制 (1)MyISAM存储引擎索引键长度的总和不能超过1000字节 (2)BLOB和TEXT类型的列只能创建前缀索...

28250
来自专栏编程心路

写给新手的Mysql入门指南(一)

关键字最好大写,这样便于阅读。可以用windows的cmd运行工具对数据库操作,前提是mysql的安装目录的子目录bin的路径添加导论系统变量PATH中,mys...

17430
来自专栏散尽浮华

mysql操作命令梳理(1)-索引

1、创建索引 索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。以下命令语句分别展...

22360
来自专栏Java成神之路

常用的Hql语句

8820
来自专栏Java后端生活

MySQL(十三)DDL之库和表的管理

19040
来自专栏乐沙弥的世界

mysql explain type连接类型示例

10310
来自专栏静默虚空的博客

sql 语言

sql 语言 DDL DDL 全称 Data Definition Language,即数据定义语言。 DATABASE 创建数据库 CREATE DATABA...

21670
来自专栏WebDeveloper

Mysql数据库应用--索引(二)

CREATE TABLE table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KE...

12630
来自专栏用户2442861的专栏

mysql 创建 主键索引 唯一索引 全文索引 多列索引 添加索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

72810

扫码关注云+社区

领取腾讯云代金券