9.1 索引简介
索引是一个单独的存在磁盘上的数据结构,他们包含着对数据表列的所有记录的引用的指针,使用索引可以快速的查找出某个或多个列中有特定值的行
索引是在存储引擎上实现的,每种存储引擎的索引不一定完全相同,包括索引的类型及长度
MySql:MyISAM和InnoDB支持BTREE索引,MEMORY和HEAP支持HASH和BTREE索引
索引优点:
1通过创建唯一索引可以包装表中的每一行数据的唯一性
2可以大大提高查询效率
3在实现数据的参考的完整性方面,可以加速表与表之间的连接
4使用分组和排序子句进行查询时可以查询中分组和排序的时间
索引缺点:
1创建和维护索引需要耗费时间,并且数据两越大,耗费时间越长
2索引需要占用磁盘空间,如果有大量的索引,可能索引文件比数据文件先达到最大文件尺寸
3当对表中的数据进行增删改时,索引需要动态维护,降低了数据的维护速度
索引分类:
1普通索引和唯一索引
普通索引:允许有空值,重复
唯一索引:索引列的值(组合索引组合的值)唯一,可空值,但主键索引不允许有空值
2单列索引和组合索引
3全文索引
FULLTEXT,在定义索引的列上支持全文查找,可空值可重复,可用于char varchartext类型的列,mysql中 只有MyiSAM存储引擎支持全文索引
4空间索引
创建空间索引的列必须指明not null, mysql中 只有MyiSAM存储引擎支持空间索引
索引设计原则:
1并非索引越多越好,因为增删改时需要动态维护,且占磁盘空间
2避免多经常更新的列添加索引
3数据量小时不要创建索引
4条件表达式中经常用到的不同值较多的列上创建索引
5当唯一性是某种数据本身特征时创建唯一索引
6在频繁进行排序和分组的列上创建索引
9.2 创建索引
9.2.1创建表的时候创建索引
基本语法格式:
create table table_name [col_name data_type]
[unique | fulltext | spatial] [index | key][index_name] (col_name[length]) [asc | desc]
1 创建普通索引
CREATE TABLEbook
(
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication)
);
查看索引:
SHOW INDEX FROM book
2 创建唯一索引
CREATE TABLEbook2
(
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
UNIQUE INDEX uniqindextext(book_id)
);
3 创建单列索引
CREATE TABLEbook3
(
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
INDEX singleIdx(book_name(20))
);
4 创建组合索引
CREATE TABLEbook4
(
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
INDEXmultiIdxTest(book_id,book_name(20),year_publication)
);
5 创建全文索引
只可以为char varchar text列创建全文索引,且要修改存储引擎,mysql引擎可以修改为MyISAM
CREATE TABLEbook5
(
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
FULLTEXT INDEX fullTextIndex(book_name)
)ENGINE=MYISAM;
6 创建空间索引
只可以为非空列创建空间索引,且要修改存储引擎,mysql引擎可以修改为MyISAM
注意:A SPATIAL index may only contain a geometrical type column
CREATE TABLEbook6
(
book_id INT NOT NULL,
book_name GEOMETRYNOT NULL,
year_publication YEAR NOT NULL,
SPATIAL INDEX spatialIndexTest(book_name)
)ENGINE=MYISAM;
9.2.2在已经存在的表上创建索引
1使用alter table语句创建
基本语法:
alter table table_name add [unique |fulltext | spatial] [index | key] [index_name] (col_name[length,..]) [asc |desc]
1.1添加普通索引
#创建表:
CREATE TABLEbook7
(
book_id INT NOT NULL,
book_name VARCHAR(200) NOT NULL,
year_publication YEAR NOT NULL
);
#然后执行下面创建索引语句:
ALTER TABLE book7 ADD INDEXbknameInd(book_name(30));
1.2添加唯一索引
#创建表:
CREATE TABLEbook8
(
book_id INT NOT NULL,
book_name VARCHAR(200) NOT NULL,
year_publication YEAR NOT NULL
);
#然后执行下面创建语句:
ALTER TABLE book8 ADD UNIQUE INDEXbknameInd(book_name(30));
1.3添加组合索引
#创建表:
CREATE TABLEbook9
(
book_id INT NOT NULL,
book_name VARCHAR(200) NOT NULL,
year_publication YEAR NOT NULL
);
#然后执行下面创建语句:
ALTER TABLE book9 ADD INDEXbknameAndPubInd(book_name(30),year_publication);
1.4添加全文索引
#创建表:
CREATE TABLEbook10
(
book_id INT NOT NULL,
book_name VARCHAR(200) NOT NULL,
year_publication YEAR NOT NULL
)ENGINE=MYISAM;
#然后执行下面创建语句:
ALTER TABLE book10 ADD FULLTEXT INDEXnameFTIdx(book_name);
1.5添加空间索引
#创建表:
CREATE TABLEbook11
(
book_id INT NOT NULL,
book_name GEOMETRY NOT NULL,
year_publication YEAR NOT NULL
)ENGINE=MYISAM;
#然后执行下面创建语句:
ALTER TABLE book11 ADD SPATIAL INDEXnameSPIdx(book_name);
2使用create index 创建索引
基本语法结构:
create [unique | fulltext | spatial] indexindex_name on table_name (col_name[length…]) [asc | desc]
2.1添加普通,单列,组合,唯一索引
#创建表:
CREATE TABLEbook13
(
book_id INT NOT NULL,
book_name VARCHAR(200) NOT NULL,
book_authors VARCHAR(200) NOT NULL,
book_info VARCHAR(200) NOT NULL,
book_coment VARCHAR(200) NOT NULL,
year_publication YEAR NOT NULL
);
#添加普通索引
CREATE INDEX bkName ON book13(book_name);
#添加唯一索引
CREATE UNIQUE INDEX uniqueInd ONbook13(book_id);
#添加多列索引
CREATE INDEX infoAndComIndex ONbook13(book_info(20),book_coment(50));
2.2添加全文索引
#创建表:
CREATE TABLEbook14
(
book_id INT NOT NULL,
book_name GEOMETRY NOT NULL,
book_authors VARCHAR(200) NOT NULL,
book_info VARCHAR(200) NOT NULL,
book_coment VARCHAR(200) NOT NULL,
year_publication YEAR NOT NULL
)ENGINE=MYISAM;
#添加全文索引
CREATE FULLTEXT INDEX fullTextIndex ONbook14(book_info);
#添加空间索引
CREATE SPATIALINDEX spatIndex ON book14(book_name);
9.3 删除索引
1使用altertable语句删除索引
格式:
alter table table_name drop indexindex_name;
2 使用dropindex语句删除索引
drop index index_name on table_name;