Mysql系列之索引(三)

一、常见的索引类型

在使用mysql面对大数据量查询的时候，往往查询速度比较慢，这时索引的作用就很明显了，可以显著提高查询速度，让我们来看看常见的mysql有哪些索引。

1.普通索引 最基本的索引，用来加速查询
 1.1CREATE INDEX  (自定义)索引名  ON  数据表(字段);

2.唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
2.1.　建表的时候一起创建
CREATE TABLE tableName ( `name` VARCHAR(32) , UNIQUE index_unique_mytable_name (`name`) );
2.2. 建表后，直接创建索引
CREATE UNIQUE INDEX index_mytable_name ON tableName(name);
2.3. 修改表结构增加索引
ALTER TABLE tableName ADD UNIQUE INDEX index_mytable_name (name);
注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

3.主键索引
  是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
3.1.　建表的时候一起创建
CREATE TABLE tableName ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(32) , PRIMARY KEY (`id`) );
3.2. 修改表结构
ALTER tableName test.t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);
注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。（最左原则）
创建方法：
4.1.　建表的时候一起创建
CREATE TABLE tableName ( `id` int(11) , `name` VARCHAR(32) , INDEX index_mytable_id_name (`id`,`name`) );
4.2. 建表后，直接创建索引
CREATE INDEX index_mytable_id_name ON tableName(id,name);
c4.3 修改表结构
ALTER TABLE tableName ADD INDEX index_mytable_id_name　(id,name);

5.全文索引
   主要用于文本检索类字段fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。
5.1. 建表的时候一起创建
CREATE tableName`article` ( `id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(250) NOT NULL , `contents` text NULL , `create_at` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (contents) );
5.2. 建表后，直接创建索引
CREATE FULLTEXT INDEX index_article_contents ON tableName(contents);
5.3. 修改表结构
ALTER tableNamearticle ADD FULLTEXT INDEX index_article_contents　(contents);

二、索引失效的情况

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、组合索引，不是使用第一列索引，索引失效。

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5.在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。