mysql 5.7从零开始学_第九章 |索引

第九章 |索引

9.1索引简介

索引用于快速找出在某列中有一特定值的行
不使用索引，mysql必须从第一条记录开始读完整个表，表越大，查询数据花费的时间越多，如果表中查询的列有一个索引，mysql能快速到达某个位置去搜寻数据文件，而不必查看所有数据

索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度

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

索引的优点
通过创建唯一索引，可以保证数据表中每一行数据的唯一性
可以大大加快数据的查询速度
加快表与表之间的连接
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间

索引的缺点
创建索引和维护索引耗费时间，随着数据量的增加，耗费的时间也会增加
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，索引文件可能比数据文件更快达到最大文件尺寸
当对表中数据进行增加、删除、修改的时候，索引也要动态的维护，降低了数据库的维护速度

索引的分类

普通索引和唯一索引

普通索引是mysql中的基本索引类型，允许在定义索引的列中插入重复值和空值

唯一索引：索引列的值必须唯一，单允许有空值，如果是组合索引，则列值的组合必须唯一，主键索引是一特殊的唯一索引，不允许有空值

单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

全文索引
全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找，允许在这些索引列中插入空值和重复值，全文索引可以在CHAR VARCHAR或者TEXT类型的列上创建，mysql中只有MyIsam存储引擎支持全文索引

空间索引
空间索引是对空间数据类型的字段建立的索引，mysql中空间数据类型有4种，GEOMETRY POINT LINESTRING 和 POLYGON，使用SPATIAL关键字进行扩展，空间索引只能在存储引擎为MyIsam的表中创建

索引的设计原则
高效的索引对于获得良好的性能非常重要，索引设计不合理或缺少索引都会对数据库和应用程序的性能造成障碍。

索引并非越多越好（因为表中数据更改时，索引也会进行调整和更新）。

避免对经常更新的表进行过多的索引，并且索引中的列尽可能少（对经常用于查询的字段应该建立索引）。

数据量小的表最好不要使用索引（数据量少，查询花费的时间可能比遍历索引时间还短，索引可能不会产生优化效果）。

在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引（比如 男 女）。

当唯一性是某种数据本身的特征时，指定唯一索引（使用唯一索引需能确保定义的列的数据完整性，以提高查询速度）。

在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引（如果待排序的列有多个，可以在这些列上建立组合索引）

9.2 创建索引

9.2.1 创建表的时候创建索引
【例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)
);

【例9.2】创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引。

CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

【例9.3】创建一个表t2，在表中的name字段上创建单列索引。

CREATE TABLE t2
(
id   INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(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))
);

【例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;

【例9.6】创建表t5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

CREATE TABLE t5
( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;

9.2.2 在已经存在的表上创建索引

1．使用ALTER TABLE创建索引
【例9.7】在book表中的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：

ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );

【例9.8】在book表的bookId字段上建立名称为UniqidIdx 的唯一索引，SQL语句如下：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

【例9.9】在book表的comment字段上建立单列索引，SQL语句如下：

ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );

【例9.10】在book表的authors和info字段上建立组合索引，SQL语句如下：

ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );

【例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 );

例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);

2．使用CREATE INDEX创建索引
【例9.13】在book表中的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：

CREATE INDEX BkNameIdx ON book(bookname);

【例9.14】在book表的bookId字段上建立名称为UniqidIdx 的唯一索引，SQL语句如下：

CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );

【例9.15】在book表的comment字段上建立单列索引，SQL语句如下：

CREATE INDEX BkcmtIdx ON book(comment(50) );

【例9.16】在book表的authors和info字段上建立组合索引，SQL语句如下：

CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(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);

【例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);

9.3 删除索引

MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。

【例9.19】删除book表中的名称为UniqidIdx的唯一索引，SQL语句如下：

ALTER TABLE book DROP INDEX UniqidIdx;

2．使用DROP INDEX语句删除索引
DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

【例9.20】删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：

 DROP INDEX BkAuAndInfoIdx ON book;

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

添加AUTO_INCREMENT约束字段的唯一索引不能被删除