MySQL索引详解

一、索引简介

       关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
　　索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。例如这样一个查询 ：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。
　　如果把表比作书，那么主键就是页码，而索引就像一本书的目录，能够加快查询速度。

二、索引的分类与创建

2.1、普通索引

       普通索引是最基本的索引，他没有任何限制。他的创建与删除方式如下：

• 直接创建索引

# 其中长度length是可选项， 一般列名是文本的时候使用
CREATE INDEX 索引名 ON 表名(列名(length))

• 修改表结构的方式添加索引

ALTER TABLE 表名 ADD INDEX 索引名 ON (列名(length))

• 创建表的时候同时创建索引

# 在创建表时，为title字段添加索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`),
    INDEX 索引名 (title(length))   
)

• 删除索引

DROP INDEX 索引名 ON 表名

2.2、唯一索引

       与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。 创建方式如下： （关键词 UNIQUE ）

• 直接创建

CREATE UNIQUE INDEX 索引名 ON 表名(列名(length))

• 修改表结构的方式添加索引

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 ON (列名(length))

• 创建表时直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`),
    UNIQUE 索引名 (id (length))   
)

2.3、主键索引

       是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。主键索引一般是在创建表的时候就直接指定，该列必须符合主键的性质，创建方式如下：

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

2.4、组合索引

       mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。创建方式如下：

ALTER TABLE 表名 ADD INDEX  索引名 (列名a, 列名b, 列名c); 

       使用组合索引时遵循最左前缀集合，即：只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。以上语句相当于创建了三个索引：（列名a，列名b， 列名c）、（列名a， 列名b）、（列名a， 列名c）

2.5、全文索引

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

1. 直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名)

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

ALTER TABLE 表名ADD FULLTEXT 索引名(列名)

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`),
    FULLTEXT (content)
);

三、索引的弊端与注意事项

3.1、弊端

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。

2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

3.2、注意事项

1. 应尽量避免在 where 子句中使用 NOT IN或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

3. 尽量使用短索引
          对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序
          Mysql在查询的时候只会使用一个索引，因此如果where子句已经使用了索引的话，那么order by中的列是不会使用索引的，所以order by尽量不要包含多个列的排序，如果非要多列排序，最好使用组合索引。

5. like语句操作
           一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6. 不要在列上进行运算
       这将导致索引失效而进行全表扫描，例如：

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

四、什么情况需要创建索引

4.1、需要创建索引的时候

1. 在经常需要搜索的列上，可以加快搜索的速度；

2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

3. 在经常用于连接两张表的列上，这些列主要是一些外键，可以加快连接的速度；

4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

4.2、什么情况不需要创建索引

1. .查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求。

2. 很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率。

3. 定义为text和image和bit数据类型的列不应该增加索引。

4. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系