MySQL：索引的常规SQL操作

MySQL的索引分为单列索引(主键索引、唯一索引、普通索引)和组合索引。
（1）单列索引：一个索引只包含一个列，一个表可以有多个单列索引。
（2）组合索引：一个索引包含两个或两个以上的列。

示例Table：
CREATE TABLE `award` (
   `id`          int(11) NOT NULL AUTO_INCREMENT   COMMENT '用户id',
   
   `aty_id`       varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `nickname`     varchar(12)  NOT NULL DEFAULT '' COMMENT '用户昵称',
   `is_awarded`   tinyint(1)   NOT NULL DEFAULT 0  COMMENT '用户是否领奖',
   `award_time`   int(11)      NOT NULL DEFAULT 0  COMMENT '领奖时间',
   `account`      varchar(12)  NOT NULL DEFAULT '' COMMENT '帐号',
   `password`     char(32)     NOT NULL DEFAULT '' COMMENT '密码',
   `message`      varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
   `created_time` int(11)      NOT NULL DEFAULT 0  COMMENT '创建时间',
   `updated_time` int(11)      NOT NULL DEFAULT 0  COMMENT '更新时间',
   
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

一、索引的查看

SHOW INDEX FROM `TableName`;

示例：show index from system_operation;

二、索引的创建

1、单列索引

1.1 普通索引

普通索引是最基本的索引,其sql格式是

CREATE INDEX IndexName ON `TableName`(`字段名` (length)) 

示例：CREATE INDEX account_Index ON `award`(`account`);

或者

ALTER TABLE TableName ADD INDEX IndexName(`字段名` (length))

示例：ALTER TABLE award ADD INDEX account_Index(`account`)

如果是CHAR、VARCHAR类型，length可以不填或者小于字段的实际长度。如果是BLOB和TEXT类型就必须指定长度。

1.2 唯一索引

唯一索引，与普通索引类似，但是不同的是唯一索引要求所有的类的值是唯一的，这一点和主键索引一样。但是他允许有空值,其sql格式是

CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 

示例：CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

或者

ALTER TABLE TableName ADD UNIQUE (column_list)  

示例：ALTER TABLE award  ADD UNIQUE(`account`);

1.3 主键索引

主键索引，不允许有空值(在B+Tree中的InnoDB引擎中，主键索引起到了至关重要的地位)。

主键索引建立的规则是 int优于varchar，一般在建表的时候创建，最好是与表的其他字段不相关的列或者是业务不相关的列。一般会设为 int 或bigint，而且是 AUTO_INCREMENT自增类型的。

2、组合索引

一个表中含有多个单列索引不代表是组合索引，通俗一点讲组合索引是包含多个字段但是只有一个索引名称。其sql格式是：

CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

示例： 
CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);

如果你建立了组合索引（nickname_account_createdTime_Index) 那么他实际包含的是3个索引 (nickname)、 (nickname, account)、(nickname, account, created_time)。

在使用查询的时候遵循mysql组合索引的"最左前缀"原则。 例如index(‘c1’, ‘c2’, ‘c3’) ：
（1）不按索引最左列开始查询（多列索引）不会使用索引。
where ‘c2’ = ‘aaa’、where c2 = aaa and c3=sss 不会使用索引。
（2）查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）。
Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ ，该查询只会使用索引中的前两列，因为like是范围查询。
（3）不能跳过某个字段来进行查询，这样利用不到索引。
select * from award where nickname > ‘rSUQFzpkDz3R’ and account = ‘DYxJoqZq2rd7’ and created_time = 1449567822; 那么这时候他使用不到其组合索引。因为我的索引是 (nickname, account, created_time),如果第一个字段出现 范围符号的查找,那么将不会用到索引,如果我是第二个或者第三个字段使用范围符号的查找,那么他会利用索引，利用的索引是(nickname),因为上面说了建立组合索引(nickname, account, created_time), 会出现三个索引。

3、全文索引

文本字段上(text)如果建立的是普通索引，那么只有对文本的字段内容前面的字符进行索引，其字符大小根据索引建立索引时申明的大小来规定。
如果文本中出现多个一样的字符，而且需要查找的话，那么其条件只能是 where column lick ‘%xxxx%’ 这样做会让索引失效。这个时候全文索引就起到了作用。

ALTER TABLE `TableName` ADD FULLTEXT(column1, column2)

有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

SELECT * FROM `TableName` WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)

这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

三、索引的删除

删除索引的mysql格式 :

DORP INDEX IndexName ON `TableName`

四、索引的设计原则和索引失效的分析