MySQL索引（index）专题

1 MySQL索引简介

    索引（Index）是帮助MySQL高效获取数据的数据结构，它的存在形式是文件。索引能够帮助我们快速定位数据，它可以让mysql高效运行，大大提高mysql的查询（包括排序，分组）效率。
    Mysql目前主要有以下几种索引类型（按存储结构划分）：FULLTEXT，HASH，BTREE，RTREE。
    对应存储引擎支持如下：

MyISAM	BTREE,FULLTEXT,RTREE
Innodb	BTREE,RTREE
Memory	HASH,BTREE
NDB	BTREE,HASH,RTREE

    注：前面索引为存储引擎默认索引。
    MySQL索引在使用上一般有如下几种种类（按使用功能划分）：
    普通索引（INDEX），唯一索引（UNIQUE INDEX），主键索引（PRIMARY KEY），全文索引（FULLTEXT INDEX），外键索引（FOREIGN KEY），组合索引。

2 索引的优缺点

    2.1 索引的优点

    大大加快数据的检索速度，这也是创建索引的最主要的原因。
    创建唯一性索引，保证数据库表中每一行数据的唯一性。
    加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
    在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
    通过使用索引，可以在查询的过程中使用优化隐藏器。

    2.2 索引的缺点

    创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
    当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
    建立索引会占用磁盘空间的索引文件，尤其大表上创建了多种组合索引，索引文件的会膨胀很快。

3 索引创建

    3.1 建表时创建索引

    3.1.1 创建普通索引

CREATE TABLE index_normal (
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL,
    INDEX normal (c3)
) engine=innodb;

    查看表结构

mysql> show create table index_normal \G
*************************** 1. row ***************************
       Table: index_normal
Create Table: CREATE TABLE `index_normal` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `c3` date DEFAULT NULL,
  KEY `normal` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

     注：若不显示指定normal为关键字，key会指定c3为关键字。

    插入语句并查看执行计划

mysql> insert into index_normal values (1,'testing partitions','1995-07-17');
mysql> explain select * from index_normal where c3 = 1995-07-17 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index_normal
         type: ref
possible_keys: normal
          key: normal
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where

    3.1.2 创建唯一索引