MySQL索引（index）专题

1 MySQL索引简介

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

MyISAMBTREE,FULLTEXT,RTREEInnodbBTREE,RTREEMemoryHASH,BTREENDBBTREE,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_normalCreate 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_bin1 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: refpossible_keys: normal          key: normal      key_len: 4          ref: const         rows: 1        Extra: Using where

    3.1.2 创建唯一索引

    一个表中可以有多个唯一性索引,唯一性索引列允许空值。

CREATE TABLE index_unique (    c1 int default NULL,    c2 varchar(30) default NULL,    c3 date default NULL,    UNIQUE INDEX uniqueIDX (c1)) engine=innodb;

    查看表结构

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

    3.1.3 创建主键索引

    一个表中只能有一个主键索引,主键索引列不允许空值。因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。

CREATE TABLE index_primary (    c1 int NOT NULL auto_increment,    c2 varchar(30) default NULL,    c3 date default NULL,    primary key (c1)) engine=innodb auto_increment=10;

    可以设定主键自增长auto_increment，及自增长开始点auto_increment=10
    查看表结构

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

    3.1.4 创建全文索引

    MySQL只有MyISAM存储引擎支持FULLTEXT索引，并且类类型为CHAR、TEXT、VARCHAR。

CREATE TABLE index_fulltext (    c1 int default NULL,    c2 varchar(300) default NULL,    c3 date default NULL,    FULLTEXT INDEX fulltextIDX (c2)) engine=myisam;

    查看表结构

mysql> show CREATE TABLE index_fulltext \G*************************** 1. row ***************************       Table: index_fulltextCreate Table: CREATE TABLE `index_fulltext` (  `c1` int(11) DEFAULT NULL,  `c2` varchar(300) COLLATE utf8_bin DEFAULT NULL,  `c3` date DEFAULT NULL,  FULLTEXT KEY `fulltextIDX` (`c2`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

     3.1.5 创建外键索引

    保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！

    外键语法规则

[CONSTRAINT 外键名] FOREIGN KEY [id] (index_col_name, ...)    REFERENCES tbl_name (index_col_name, ...)    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

    该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
    ON DELETE、ON UPDATE表示事件触发限制，可设参数：
    ① RESTRICT（限制外表中的外键改动，默认值）
    ② CASCADE（跟随外键改动）
    ③ SET NULL（设空值）
    ④ SET DEFAULT（设默认值）
    ⑤ NO ACTION（无动作，默认的）

    创建外键实例

CREATE TABLE index_foreign(id int(11),name varchar(20),CONSTRAINT foreignKEY foreign key(id) references index_primary(c1) on delete cascade on update cascade) engine=innodb;<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"></span>

    查看表结构

mysql> show create table index_foreign \G*************************** 1. row ***************************       Table: index_foreignCreate Table: CREATE TABLE `index_foreign` (  `id` int(11) DEFAULT NULL,  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,  KEY `foreignKEY` (`id`),  CONSTRAINT `index_foreign_ibfk_1` FOREIGN KEY (`id`) REFERENCES `index_primary` (`c1`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

    注：有外键约束的表必须是innodb型，因为在MySQL中只有InnoDB类型的表才支持外键。

            不能删除有外键约束的父表，否则会报错：ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

            MySQL只允许向当前表中加入外键表（参照表）中已有的数据列。

            在外键关系中，字段的数据类型必须相似。

            外键表中数据删除，当前表中相关数据也会被级联删除，反之则不会。

    3.2  已存在的表上通过ALTER TABLE创建索引

    3.2.1 基本语法

ALTER TABLE table_name ADD INDEX index_name (column_name);ALTER TABLE table_name ADD UNIQUE index_name (column_name);ALTER TABLE table_name ADD PRIMARY KEY (column_name);ALTER TABLE table_name ADD FULLTEXT index_name (column_name);ALTER TABLE table_name ADD CONSTRAINT foreignKEY FOREIGN KEY (index_column_name) REFERENCES foreign_table_name (index_column_name) ON DELETE CASCADE ON UPDATE CASCADE ;

    3.2.2 创建测试表

CREATE TABLE index_alter_normal (    c1 int default NULL,    c2 varchar(30) default NULL,    c3 date default NULL) engine=innodb;CREATE TABLE index_alter_unique (    c1 int default NULL,    c2 varchar(30) default NULL,    c3 date default NULL) engine=innodb;CREATE TABLE index_alter_primary (    c1 int NOT NULL,    c2 varchar(30) default NULL,    c3 date default NULL) engine=innodb;CREATE TABLE index_alter_fulltext (    c1 int default NULL,    c2 varchar(300) default NULL,    c3 date default NULL) engine=myisam;CREATE TABLE index_foreign(id int(11),name varchar(20) )engine=innodb;

    3.2.3 添加索引

ALTER TABLE index_alter_normal ADD INDEX normal (c3);ALTER TABLE index_alter_unique ADD UNIQUE uniqueIDX (c1);ALTER TABLE index_alter_primary ADD PRIMARY KEY (c1);ALTER TABLE index_alter_fulltext ADD FULLTEXT fulltextIDX (c2);ALTER TABLE index_foreign ADD CONSTRAINT foreignKEY FOREIGN KEY (id) REFERENCES index_primary (c1) ON DELETE CASCADE ON UPDATE CASCADE ;

    注：在已存在的表上添加主键只能通过alter table的方法，不能直接create primary key

    若添加主键为自增主键，先增加主键，在添加自增长，最后添加增长起始点

    添加主键为自增长，并设置从10开始自增：

mysql> Alter table index_alter_primary change c1 c1 int(11) not null auto_increment;mysql> Alter table index_alter_primary  auto_increment=10;

    3.3  已存在的表上通过CREATE INDEX创建索引

    主键及外键的添加不能通过此方法，只能通过上面alter table的方法。

    3.3.1 基本语法

CREATE INDEX index_name ON table_name (column_name);CREATE UNIQUE INDEX index_name ON table_name (column_name);CREATE FULLTEXT INDEX index_name ON table_name (column_name);

    3.3.2 创建测试表

CREATE TABLE index_create_normal (    c1 int default NULL,    c2 varchar(30) default NULL,    c3 date default NULL) engine=innodb;CREATE TABLE index_create_unique (    c1 int default NULL,    c2 varchar(30) default NULL,    c3 date default NULL) engine=innodb;CREATE TABLE index_create_fulltext (    c1 int default NULL,    c2 varchar(300) default NULL,    c3 date default NULL) engine=myisam;

    3.3.3 添加索引

CREATE INDEX normal ON index_create_normal (c3);CREATE UNIQUE INDEX uniqueIDX ON index_create_unique (c1);CREATE FULLTEXT INDEX fulltextIDX ON index_create_fulltext (c2);

4 索引查看

mysql> show index from table_name;mysql> show key from table_name;

    例如：

mysql> show index from index_normal \G*************************** 1. row ***************************        Table: index_normal   Non_unique: 1     Key_name: normal Seq_in_index: 1  Column_name: c3    Collation: A  Cardinality: 2     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: BTREE      Comment: Index_comment: 1 row in set (0.00 sec)mysql> show index from index_fulltext \G*************************** 1. row ***************************        Table: index_fulltext   Non_unique: 1     Key_name: fulltextIDX Seq_in_index: 1  Column_name: c2    Collation: NULL  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: FULLTEXT      Comment: Index_comment: 1 row in set (0.00 sec)

    注：其中普通索引，唯一索引，主键索引类型为btree，全文索引类型为fulltext
5 删除索引

    删除语法：

drop index index_name on table_name ;alter table table_name drop index index_name ;alter table table_name drop primary key ;

    注：在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果主键为自增长，删除主键前需要先改变自增长为普通字段。

    例如：

mysql> drop index uniqueIDX on index_unique;mysql> alter table index_normal drop index ;mysql> alter table index_fulltext drop index fulltextIDX;mysql> alter table index_primary change c1 c1 int(11);//删除自增长mysql> alter table index_primary drop PRIMARY KEY;//删除主键mysql> alter table index_foreign drop FOREIGN KEY index_foreign_ibfk_1;//删除外键索引

    注：删除外键语法如下：
    ALTER TABLE table-name DROP FOREIGN KEY key-id;
    其中：key-id可通过show create table查看，紧跟在CONSTRAINT之后的那个参数就是。

6 几种索引对比

    6.1 索引类型对比（按存储结构划分）

    6.1.1 B-TREE索引

    B-Tree索引加速了数据访问。从B-Tree根开始，借助中间节点页的上界和下界值，可以快速搜寻到叶子页层，最终找到含有需要找的值的叶子页(或者确定无法找到需要的数据)，找到对应的叶子页后可以通过相应的指针直接找到数据表中对应的数据行。这样存储引擎不会扫描整个表得到需要的数据。同时B-Tree索引通常意味着索引中数据保存时有序的，可以利用B-Tree索引来加速排序。

    6.1.2 HASH索引

    哈希索引建立在哈希表的基础上，它只对使用了索引的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值(可能对具有相同索引值的不同行计算出的哈希值不同)。索引中包含哈希码和对应指向数据行的指针。 

    hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree。hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。

    6.1.3 FULLTEXT索引

    FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。
    它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的。

    6.1.4 R-TREE索引

    RTREE在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
    相对于BTREE，RTREE的优势在于范围查找。

    6.2 索引类型对比（按使用功能划分）

    6.2.1 普通索引

    这是最基本的索引类型，而且它没有唯一性之类的限制。

    6.2.2 唯一索引

    索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

    6.2.3 主键索引

    主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”，且不允许有空值。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）。

    6.2.4 全文索引

    MySQL只有MyISAM存储引擎支持FULLTEXT索引，并且类类型为CHAR、TEXT、VARCHAR。详细参考同上。

    6.2.5 外键索引

    如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。

**************************************************************************************************
*  转载请注明出处：http://blog.csdn.net/jesseyoung/article/details/38037543  *
**************************************************************************************************