Mysql 索引学习笔记之语法

1 索引概述

Mysql索引学习笔记之概述

2 索引分类

Mysql 索引学习笔记之分类

3 索引语法

3.1 创建索引

3.1.1 创建表创建索引

1. 格式：

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                  [UNIQUE | FULLTEXT | SPATIAL]    INDEX | KEY
                  [索引名](字段名1 [(长度)] [ASC | DESC])
);
参数信息
UNIQUE：可选。表示索引为唯一性索引。
FULLTEXT；可选。表示索引为全文索引。
SPATIAL：可选。表示索引为空间索引。
INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名：可选。给创建的索引取一个新名称。
字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度：可选。指索引的长度，必须是字符串类型才可以使用。
ASC：可选。表示升序排列。
DESC：可选。表示降序排列

2. 举例 ：

CREATE TABLE `city` ( 
	`city_id` int(11) NOT NULL AUTO_INCREMENT, 
	`city_name` varchar(50) NOT NULL,
	 `country_id` int(11) NOT NULL, 
	 PRIMARY KEY (`city_id`) ,
	INDEX(city_name)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

3.1.2 在已有表中加入索引

1. 使用CREATE 语句创建索引
   1. 可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键
   2. 格式

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

参数说明
<索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
<表名>：指定要创建索引的表名。
<列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
<长度>：可选项。指定使用列前的 length 个字符来创建索引。
		使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。
		在某些情况下，只能对列的前缀进行索引。
		索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。
		另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC

2. 使用ALTER TABLE语句创建索引
   1. ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。
   2. 具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项

语法格式：
	ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。

语法格式：
	ADD PRIMARY KEY [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。

语法格式：
	ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。

语法格式：
	ADD FOREIGN KEY [<索引名>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键

1). alter table tb_name add primary key(column_list); 
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL 

2). alter table tb_name add unique index_name(column_list); 
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次） 

3). alter table tb_name add index index_name(column_list); 
添加普通索引， 索引值可以出现多次。

4). alter table tb_name add fulltext index_name(column_list);
 该语句指定了索引为FULLTEXT， 用于全文索引

3. 创建复合索引

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ; 
	对name , email 创建了索引 ; 
	对name , email, status 创建了索引 ;

3.2 查看索引

1. 格式

show index from table_name;

show index from table_name\G;

使用  \G  可以使格式化，方便看

3.3 删除索引

1. 格式

DROP INDEX index_name ON tbl_name;

4 索引设计原则

索引设计原则

5 索引的使用

Mysql索引学习笔记之使用