前言

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。进入正题：

有些硬啃的干货还是得了解的，下面先了解索引的基本知识

索引分类

单列索引
- 主键索引
- 唯一索引
- 普通索引

组合索引

用到的表

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='获奖信息表';

单列索引

普通索引

这个是最基本的索引

创建语法：

其sql格式是：
   第一种方式 :
     CREATE INDEX IndexName ON `TableName`(`字段名`(length))
   第二种方式 :
	 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

创建例子：

第一种方式 :
  CREATE INDEX account_Index ON `award`(`account`);
第二种方式:
  ALTER TABLE award ADD INDEX account_Index(`account`)

唯一索引

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

创建语法：

其sql格式是：
   第一种方式 :
    CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
   第二种方式 :
	ALTER TABLE TableName ADD UNIQUE (column_list)

创建例子：

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

主键索引

他与唯一索引的不同在于不允许有空值(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

创建语法：

其sql格式是：
   第一种方式 :
    CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
   第二种方式 :
	ALTER TABLE TableName ADD UNIQUE (column_list)

创建例子：

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

单列索引的总结

mysql>SELECT ｀uid｀ FROM people WHERE lname｀='Liu'  AND ｀fname｀='Zhiqun' AND ｀age｀=26
因为我们不想扫描整表，故考虑用索引。
单列索引：
ALTER TABLE people ADD INDEX lname (lname);
将lname列建索引，这样就把范围限制在lname='Liu'的结果集1上，之后扫描结果集1，产生满足fname='Zhiqun'的结果集2，再扫描结果集2，找到 age=26的结果集3，即最终结果。
由 于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引，再创建fname或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。
> 所以就需要组合索引

组合索引

一个表中含有多个单列索引不代表是组合索引,通俗一点讲组合索引是:包含多个字段但是只有索引名称

创建语法：
其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)

组合索引的最左前缀

上面的例子中给nickname,account,created_time 这三个字段建立索引他会去创建三个索引，但是在执行查询的时候只会用其中一个索引去查询，mysql会选择一个最严格(获得结果集记录数最少)的索引，所以where子句中使用最频繁的一列放在最左边。所谓最左前缀原则就是先要看第一列，在第一列满足的条件下再看左边第二列

全文索引

文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.
如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效
.这个时候全文索引就祈祷了作用了
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。
ELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

总结

使用索引的优点

可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
建立索引可以大大提高检索的数据,以及减少表的检索行数
在表连接的连接条件可以加速表与表直接的相连
在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
建立索引,在查询中使用索引可以提高性能

使用索引的缺点

在创建索引和维护索引会耗费时间,随着数据量的增加而增加
索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

使用索引需要注意的地方

在经常需要搜索的列上,可以加快索引的速度
主键列上可以确保列的唯一性
在表与表的而连接条件上加上索引,可以加快连接查询的速度
在经常需要排序(order by),分组(group by)和的distinct 列上加索引可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)
在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
like语句的如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’ 那么将可以用到索引
索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ‘ ‘字符串
使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.
不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中占用的空间很少,处理起来更快

什么情况下不建立索引

查询中很少使用到的列不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
很少数据的列也不应该建立索引,比如一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
定义为text和image和bit数据类型的列不应该增加索引
当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

好的文章

转：SQL优化
 转：MySQL索引原理及慢查询优化