MySQL:索引

转载 2015年11月18日 12:15:25
【例9.1】在book表中的year_publication字段上建立普通索引,SQL语句如下:
CREATE TABLE book
(
bookid                INT NOT NULL,
bookname              VARCHAR(255) NOT NULL,
authors                VARCHAR(255) NOT NULL,
info                   VARCHAR(255) NULL,
comment               VARCHAR(255) NULL,
year_publication       YEAR NOT NULL,
INDEX(year_publication)
);
该语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:
 SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
CREATE Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由结果可以看到,book1表的year_publication字段上成功建立索引,其索引名称year_publication为MySQL自动添加。使用EXPLAIN语句查看索引是否正在使用:
 explain select * from book where year_publication=1990 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
        Extra:
1 row in set (0.05 sec)
【例9.2】创建一个表t1,在表中的id字段上使用UNIQUE关键字创建唯一索引。
CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
该语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:
 SHOW CREATE table t1 \G
*************************** 1. row ***************************
       Table: t1
CREATE Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
由结果可以看到,id字段上已经成功建立了一个名为UniqIdx的唯一索引。
【例9.3】创建一个表t2,在表中的name字段上创建单列索引。
表结构如下:
CREATE TABLE t2
(
id   INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);
该语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:
 SHOW CREATE table t2 \G
*************************** 1. row ***************************
       Table: t2
CREATE Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由结果可以看到,id字段上已经成功建立了一个名为SingleIdx的单列索引,索引长度为20。
【例9.4】创建表t3,在表中的id、name和age字段上建立组合索引,SQL语句如下:
CREATE TABLE t3
(
id    INT NOT NULL,
name CHAR(30)  NOT NULL,
age  INT NOT  NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, age(100))
);
该语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:
 SHOW CREATE table t3 \G
*************************** 1. row ***************************
       Table: t3
CREATE Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由结果可以看到,id、name和age字段上已经成功建立了一个名为MultiIdx的组合索引。
组合索引可起几个索引的作用,但是使用时并不是随便查询哪个字段都可以使用索引,而是遵从“最左前缀”:利用索引中最左边的列集来匹配行,这样的列集称为最左前缀。例如这里由id、name和age 3个字段构成的索引,索引行中按id/name/age的顺序存放,索引可以搜索下面字段组合:(id, name, age)、(id, name)或者id。如果列不构成索引最左面的前缀,MySQL不能使用局部索引,如(age)或者(name,age)组合则不能使用索引查询。
在t3表中,查询id和name字段,使用EXPLAIN语句查看索引的使用情况:
 explain select * from t3 where id=1 AND name='joe' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 94
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)
可以看到,查询id和name字段时,使用了名称MultiIdx的索引,如果查询(name,age)组合或者单独查询name和age字段,结果如下:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
此时,possible_keys和key值为NULL,并没有使用在t3表中创建的索引进行查询。
5.创建全文索引
FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。
【例9.5】创建表t4,在表中的info字段上建立全文索引,SQL语句如下:
CREATE TABLE t4
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
age  INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;
因为MySQL5.6中默认存储引擎为InnoDB,在这里创建表时需要修改表的存储引擎为MyISAM,不然创建索引会出错。
语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:
 SHOW CREATE table t4 \G
*************************** 1. row ***************************
       Table: t4
CREATE Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `FullTxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
由结果可以看到,info字段上已经成功建立了一个名为FullTxtIdx的FULLTEXT索引。全文索引非常适合于大型数据集,对于小的数据集,它的用处可能比较小。
6.创建空间索引
空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。
【例9.6】创建表t5,在空间类型为GEOMETRY的字段上创建空间索引,SQL语句如下:
CREATE TABLE t5
( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;
该语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:
 SHOW CREATE table t5 \G
【例9.7】在book表中的bookname字段上建立名为BkNameIdx的普通索引,SQL语句如下:
ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );
添加索引之前,使用SHOW INDEX语句查看指定表中创建的索引:
 SHOW INDEX FROM book \G

下面使用ALTER TABLE 在bookname字段上添加索引,SQL语句如下:
ALTER TABLE book ADD INDEX BkNameIdx( bookname(30) );
使用SHOW INDEX语句查看表中的索引:
 SHOW INDEX FROM book \G

【例9.8】在book表的bookId字段上建立名称为UniqidIdx 的唯一索引,SQL语句如下:
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );
使用SHOW INDEX语句查看表中的索引:
 SHOW INDEX FROM book \G

【例9.9】在book表的comment字段上建立单列索引,SQL语句如下:
ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );
使用SHOW INDEX语句查看表中的索引:

【例9.10】在book表的authors和info字段上建立组合索引,SQL语句如下:
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );
使用SHOW INDEX语句查看表中的索引:

【例9.11】创建表t6,在t6表上使用ALTER TABLE创建全文索引,SQL语句如下:
首先创建表t6,语句如下:
CREATE TABLE t6
(
id    INT NOT NULL,
info  CHAR(255)
) ENGINE=MyISAM;
注意修改ENGINE参数为MyISAM,MySQL默认引擎InnoDB不支持全文索引。
使用ALTER TABLE语句在info字段上创建全文索引:
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );
使用SHOW INDEX语句查看索引:
 SHOW index from t6 \G

【例9.12】创建表t7,在t7的空间数据类型字段g上创建名称为spatIdx的空间索引,SQL语句如下:
CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;
使用ALTER TABLE在表t7的g字段建立空间索引:
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);
使用SHOW INDEX语句查看索引:
 SHOW index from t7 \G

2.使用CREATE INDEX创建索引
CREATE INDEX语句可以在已经存在的表上添加索引,MySQL中CREATE INDEX被映射到一个ALTER TABLE语句上,基本语法结构为:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (col_name[length],…) [ASC | DESC]
可以看到CREATE INDEX语句和ALTER INDEX语句的语法基本一样,只是关键字不同。
在这里,使用相同的表book,假设该表中没有任何索引值,创建book表语句如下:
CREATE TABLE book
(
bookid              INT NOT NULL,
bookname            VARCHAR(255) NOT NULL,
authors               VARCHAR(255) NOT NULL,
info                  VARCHAR(255) NULL,
comment              VARCHAR(255) NULL,
year_publication       YEAR NOT NULL
);
提示:读者可以将该数据库中的book表删除,按上面的语句重新建立,然后进行下面的操作。
【例9.13】在book表中的bookname字段上建立名为BkNameIdx的普通索引,SQL语句如下:
CREATE INDEX BkNameIdx ON book(bookname);
语句执行完毕之后,将在book表中创建名称为BkNameIdx的普通索引。读者可以使用SHOW INDEX或者SHOW CREATE TABLE语句查看book表中的索引,其索引内容与前面介绍相同。
【例9.14】在book表的bookId字段上建立名称为UniqidIdx 的唯一索引,SQL语句如下:
CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );
语句执行完毕之后,将在book表中创建名称为UniqidIdx 的唯一索引。
【例9.15】在book表的comment字段上建立单列索引,SQL语句如下:
CREATE INDEX BkcmtIdx ON book(comment(50) );
语句执行完毕之后,将在book表的comment字段上建立一个名为BkcmtIdx的单列索引,长度为50。
【例9.16】在book表的authors和info字段上建立组合索引,SQL语句如下:
CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );
语句执行完毕之后,将在book表的authors和info字段上建立了一个名为BkAuAndInfoIdx的组合索引,authors的索引序号为1,长度为20,info的索引序号为2,长度为50。
【例9.17】删除表t6,重新建立表t6,在t6表中使用CREATE INDEX语句,在CHAR类型的info字段上创建全文索引,SQL语句如下:
首先删除表t6,并重新建立该表,分别输入下面语句:
 drop table t6;
Query OK, 0 rows affected (0.00 sec)

 CREATE TABLE t6
     (
     id    INT NOT NULL,
     info  CHAR(255)
     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
使用CREATE INDEX在t6表的info字段上创建名称为infoFTIdx的全文索引:
CREATE FULLTEXT INDEX ON t6(info);
语句执行完毕之后,将在t6表中创建名称为infoFTIdx的索引,该索引在info字段上创建,类型为FULLTEXT,允许空值。
【例9.18】删除表t7,重新创建表t7,在t7表中使用CREATE INDEX语句,在空间数据类型字段g上创建名称为spatIdx的空间索引,SQL语句如下:
首先删除表t7,并重新建立该表,分别输入下面语句:
 drop table t7;
Query OK, 0 rows affected (0.00 sec)

 CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
使用CREATE INDEX语句在表t7的g字段建立空间索引,
CREATE SPATIAL INDEX spatIdx ON t7 (g);
语句执行完毕之后,将在t7表中创建名称为spatIdx的空间索引,该索引在g字段上创建。
9.3  删除索引
MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句,两者可实现相同的功能,DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。
1.使用ALTER TABLE删除索引
ALTER TABLE删除索引的基本语法格式如下:
ALTER TABLE table_name DROP INDEX index_name;
【例9.19】删除book表中的名称为UniqidIdx的唯一索引,SQL语
句如下:
首先查看book表中是否有名称为UniqidIdx的索引,输入SHOW语句如下:
 SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
CREATE Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  UNIQUE KEY `UniqidIdx` (`bookid`),
  KEY `BkNameIdx` (`bookname`),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
查询结果可以看到,book表中有名称为UniqidIdx的唯一索引,该索引在bookid字段上创建,下面删除该索引,输入删除语句如下:
 ALTER TABLE book DROP INDEX UniqidIdx;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
语句执行完毕,使用SHOW语句查看索引是否被删除:
 SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
CREATE Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameIdx` (`bookname`),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由结果可以看到,book表中已经没有名称为uniqidIdx的唯一索引,删除索引成功。
添加AUTO_INCREMENT约束字段的唯一索引不能被删除。
2.使用DROP INDEX语句删除索引
DROP INDEX删除索引的基本语法格式如下:
DROP INDEX index_name ON table_name;
【例9.20】删除book表中名称为BkAuAndInfoIdx的组合索引,SQL语句如下:
 DROP INDEX BkAuAndInfoIdx ON book;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
语句执行完毕,使用SHOW语句查看索引是否被删除:
 SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
CREATE Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameIdx` (`bookname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,book表中已经没有名称为BkAuAndInfoIdx的组合索引,删除索引成功。
删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。


综合案例

  登录MySQL数据库。
打开Windows命令行,输入登录用户名和密码:
C:\>mysql –h localhost -u root -p
Enter password: **
  创建数据库index_test。
创建数据库index_test的语句如下:
 CREATE database index_test;
Query OK, 1 row affected (0.00 sec)
结果显示创建成功,在index_test数据库中创建表,必须先选择该数据库,输入语句如下:
 USE index_test;
Database changed
结果显示选择数据库成功。
  创建表test_table1。
CREATE TABLE test_table1
(
id          INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,
name       CHAR(100) NOT NULL,
address     CHAR(100) NOT NULL,
description  CHAR(100) NOT NULL,
UNIQUE INDEX UniqIdx(id),
INDEX MultiColIdx(name(20), address(30)),
INDEX ComIdx( description(30) )
);
使用SHOW语句查看索引信息:
 SHOW CREATE table test_table1 \G
*************************** 1. row ***************************
       Table: test_table1
CREATE Table: CREATE TABLE `test_table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(100) NOT NULL,
  `address` char(100) NOT NULL,
  `description` char(100) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`),
  KEY `MultiColIdx` (`name`(20),`address`(30)),
  KEY `ComIdx` (`description`(30))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由结果可以看到,test_table1表中成功创建了3个索引,分别是:在id字段上名称为UniqIdx的唯一索引;在name和address字段上的组合索引,两个索引列的长度分别为20个字符和30个字符;在description字段上的长度为30的普通索引。
  创建表test_table2,存储引擎为MyISAM。
CREATE TABLE test_table2
(
id         INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,
firstname   CHAR(100) NOT NULL,
middlename CHAR(100) NOT NULL,
lastname   CHAR(100) NOT NULL,
birth      DATE NOT NULL,
title       CHAR(100) NULL
) ENGINE=MyISAM;
  使用ALTER TABLE语句在表test_table2的birth字段上,建立名称为ComDateIdx的普通索引。
 ALTER TABLE test_table2 ADD INDEX ComDateIdx(birth);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
  使用ALTER TABLE语句在表test_table2的id字段上,添加名称为UniqIdx2的唯一索引,并以降序排列。
 ALTER TABLE test_table2 ADD UNIQUE INDEX UniqIdx2 (id DESC) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
  使用CREATE INDEX在firstname、middlename和lastname3个字段上建立名称为MultiColIdx2的组合索引。
 CREATE INDEX MultiColIdx2 ON test_table2(firstname, middlename, lastname);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
  使用CREATE INDEX在title字段上建立名称为FTIdx的全文索引。
 CREATE FULLTEXT INDEX FTIdx ON test_table2(title);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  使用ALTER TABLE语句删除表test_table1中名称为UniqIdx的唯一索引。
  ALTER TABLE test_table1 DROP INDEX UniqIdx;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  使用DROP INDEX语句删除表test_table2中名称为MultiColIdx2的组合索引。
 DROP INDEX MultiColIdx2 ON test_table2;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql深入之视图和索引

注:求职季,巩固下mysql知识! 1.视图 视图又叫虚表。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查...
  • basycia
  • basycia
  • 2016年07月28日 11:36
  • 5513

数据库的视图和索引的概念和区别

一、视图 1、什么是视图: 视图是从一个或多个表中导出来的表,是一种不是一种真正存在的概念。 视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据。 这样,用户可以不用看到整个数据库...

MySQL之高效覆盖索引

  • 2016年03月15日 10:47
  • 13KB
  • 下载

MySQL查询索引的正确使用

索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用,但是一般来说引起最大性能差异的都是索引的正确使用。在MySQL邮件列表中,人们经常询问那些让查询运行得更快的方法。在大多数情况下,我们...
  • pcyph
  • pcyph
  • 2015年05月17日 14:32
  • 23123

mysql存储与索引技术

  • 2011年12月23日 08:38
  • 141KB
  • 下载

MySQL索引缺点&使用详细注意事项

本文转自:点击打开链接 以下主要介绍的是MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项是值得我们大家注意的,我们大家可能不知道过多的对索引进行使用将会造成滥用,有兴趣的...

MySQL 索引最佳实践

  • 2013年03月14日 17:05
  • 640KB
  • 下载

mysql-常见问题,索引优化

  • 2017年11月17日 13:51
  • 388KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL:索引
举报原因:
原因补充:

(最多只允许输入30个字)