未加星标

MySQL:索引

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏
1、索引简介

(1)索引的含义

索引是一个单独的、存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针。

(2)索引的分类

a、普通索引和唯一索引 b、单列索引和组合索引 c、全文索引 d、空间索引
(3)索引的设计原则
a、索引并非越多越好。 b、避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。 c、数据量小的表最好不要使用索引。 d、在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。 e、当唯一性是某种数据本身的特征时,指定唯一索引。 f、在频繁进行排序或分组(即进行groupby或orderby操作)的列上建立索引
2、创建索引

(1)创建表的时候创建索引

创建表时创建索引的基本语法格式如下: CREATE TABLE table_name
[col_namedata_type]
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]
[index_name] (col_name [length])
[ASC | DESC]

例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)
例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的唯一索引。
例3:单列索引
创建一个表t2,在表中的name字段上创建单列索引
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);
此时id字段上已经成功建立了一个名为SingleIdx的单列索引,索引长度为20。

例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))
);
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列。索引总是对整个列进行,不支持局部(前缀)索引。

创建表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,不然创建索引会出错。

全文索引非常适合于大型数据集,对于小的数据集,它的用处可能比较小。

例6:空间索引

空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。
创建表t5,在空间类型为GEOMETRY的字段上创建空间索引,SQL语句如下:
CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
)ENGINE=MyISAM;

(2)在已经存在的表上创建索引

a、使用ALTERTABLE语句创建索引
基本语法:
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
例1:在book表中的bookname字段上建立名为BkNameIdx的普通索引,SQL语句如下:
ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );
添加索引之前,使用SHOW INDEX语句查看指定表中创建的索引:
SHOW INDEX FROM book \G
例2:在book表的bookId字段上建立名称为UniqidIdx 的唯一索引,SQL语句如下:
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );
例3:在book表的comment字段上建立单列索引,SQL语句如下:
ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );
例4:在book表的authors和info字段上建立组合索引,SQL语句如下:
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );
例5:创建表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
例6:创建表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
b、使用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语句的语法基本一样,只是关键字不同。
例1:在book表中的bookname字段上建立名为BkNameIdx的普通索引,SQL语句如下:
CREATE INDEX BkNameIdx ON book(bookname);
例2:在book表的bookId字段上建立名称为UniqidIdx 的唯一索引,SQL语句如下:
CREATE UNIQUE INDEX UniqidIdx ON book ( bookId );
例3:在book表的comment字段上建立单列索引,SQL语句如下:
CREATE INDEX BkcmtIdx ON book(comment(50) );
语句执行完毕之后,将在book表的comment字段上建立一个名为BkcmtIdx的单列索引,长度为50。
例4:在book表的authors和info字段上建立组合索引,SQL语句如下:
CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );
语句执行完毕之后,将在book表的authors和info字段上建立了一个名为BkAuAndInfoIdx的组合索引,authors的索引序号为1,长度为20,info的索引序号为2,长度为50。
例5:删除表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,允许空值。
3、删除索引
(1)使用ALTERTABLE删除索引 ALTER TABLE table_name;
例1:删除book表中的名称为UniqidIdx的唯一索引,SQL语句如下:
ALTER TABLE book DROP INDEX UniqidIdx;
使用 SHOW CREATE table book \G语句查看索引是否被删除
(2)使用DROPINDEX语句删除索引 DROP INDEX index_name ON table_name;
例1:删除book表中名称为BkAuAndInfoIdx的组合索引,SQL语句如下:
DROP INDEX BkAuAndInfoIdx ON book;
删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLInnoDBFU数据删除TIAURY数据库
分页:12
转载请注明
本文标题:MySQL:索引
本站链接:http://www.codesec.net/view/480182.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(43)