mysql - index 索引 之 分类 & 创建

一、索引分类：

MySQL目前主要有以下几种索引类型：

1. 普通索引
2. 唯一索引
3. 主键索引
4. 组合索引
5. 全文索引

二、建表语句：

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1. unique | fulltext 为可选参数，分别表示唯一索引、全文索引；
2. index 和 key 为同义词，两者作用相同，用来指定创建索引；
3. col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
4. index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值；
5. length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
6. asc 或 desc 指定升序或降序的索引值存储。

三、各类索引的创建语句：

普通索引：

1. 直接创建：

   CREATE INDEX index_name ON table(column(length));

   mysql> create index general_index on index_test(title);
   Query OK, 0 rows affected (0.02 sec)
   Records: 0 Duplicates: 0 Warnings: 0

2. 修改表结构：

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

   mysql> alter table index_test add index general_index1 (title);
   Query OK, 0 rows affected, 1 warning (0.02 sec)
   Records: 0  Duplicates: 0  Warnings: 1

3. 建表时同时创建索引：

   CREATE TABLE `table` (
       `id` int(11) NOT NULL AUTO_INCREMENT ,
       `title` char(255) CHARACTER NOT NULL ,
       `content` text CHARACTER NULL ,
       `time` int(10) NULL DEFAULT NULL ,
       PRIMARY KEY (`id`),
       INDEX index_name (title(length))
   );

4. 删除索引：

   DROP INDEX index_name ON table;

   mysql> drop index general_index on index_test;
   Query OK, 0 rows affected (0.01 sec)
   Records: 0  Duplicates: 0  Warnings: 0

    

唯一索引：

简述：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引(允许多列字段)，则列值的组合必须唯一。

1. 直接创建：

   CREATE UNIQUE INDEX index_name ON table(column(length));

    

2. 修改表结构：

   ALTER TABLE table_name ADD UNIQUE index_name (column(length));

    

3. 建表时创建：

   CREATE TABLE `table_name`(
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `title` char(255) NOT NULL,
       `content` text，
       `time` int(10) NULL DEFAULT NULL,
       UNIQUE index_name (title(length))
   ) ;

    

主键索引：

简述：又叫做聚簇索引，是一种特殊的唯一索引，一个表

1. 只能有一个主键，
2. 不允许有空值，
3. 允许包含1列或者多列字段。
4. 一般是在建表的时候同时创建主键索引；

作用：有了聚簇索引，将来插入的数据行，在同一个区内，都会按照ID值的顺序在物理磁盘上有序存储。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

组合索引（联合索引）：

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

1. 直接创建：

   CREATE INDEX index_name ON table(column_1, colunmn_2, ...);

    

2. 修改表结构：

   ALTER TABLE table_name ADD INDEX index_name (column_1, column_2, ...);

   mysql> alter table index_test add index joint_index (title, content,c_time);
   ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key length
   mysql> 
   mysql> alter table index_test add index joint_index (title, content(30),c_time);
   Query OK, 0 rows affected (0.01 sec)
   Records: 0  Duplicates: 0  Warnings: 0

    

    

3. 建表时创建：

   CREATE TABLE `table` (
       `id` int(11) NOT NULL AUTO_INCREMENT ,
       `title` char(255) CHARACTER NOT NULL ,
       `content` text CHARACTER NULL ,
       `time` int(10) NULL DEFAULT NULL ,
       PRIMARY KEY (`id`),
       INDEX index_name (title(length), time)
   );

    

全文索引：

简述：

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

1. 建表时创建：

   CREATE TABLE `table` (
       `id` int(11) NOT NULL AUTO_INCREMENT ,
       `title` char(255) CHARACTER NOT NULL ,
       `content` text CHARACTER NULL ,
       `time` int(10) NULL DEFAULT NULL ,
       PRIMARY KEY (`id`),
       FULLTEXT (content)
   );

    

2. 直接创建：

   CREATE FULLTEXT INDEX index_name ON table(content);

    

3. 修改表：

   ALTER TABLE table_name ADD FULLTEXT INDEX index_name (content);

    

显示索引信息：

mysql> show index from table_name\G;