玻璃钢格栅无锡网站建设,企业门户网站网站怎么获得,wordpress伪静态nginx,社保网站上20号做的新增一.索引简介
索引用于快速找出在某个列中有一特定值的行。
不使用索引#xff0c;MySQL必须从第1条记录开始读完整个表#xff0c;直到找出相关的行。表越大#xff0c;查询数据所花费的时间越多。
如果表中查询的列有一个索引#xff0c;MySQL能快速到达某个位置去搜寻…一.索引简介
索引用于快速找出在某个列中有一特定值的行。
不使用索引MySQL必须从第1条记录开始读完整个表直到找出相关的行。表越大查询数据所花费的时间越多。
如果表中查询的列有一个索引MySQL能快速到达某个位置去搜寻数据文件而不必查看所有数据。
1、索引的含义与特点
索引是一个单独的、存储在磁盘上的数据库结构它们包含着对数据表里所有记录的引用指针。
使用索引用于快速找出在某个或多个列中有一特定值的行所有MySQL列类型都可以被索引
对相关列使用索引是提高查询操作速度的最佳途径。 例如数据库中有2万条记录现在要执行这样一个查询
SELECT * FROM table where num10000.
如果没有索引必须遍历整个表直到um等于10000的这一行被找到为止
如果在num列上创建索引MySQL不需要任何扫描直接在索引里面找10000,
就可以得知这一行的位置。可见索引的建立可以提高数据库的查询速度。 索引是在存储引擎中实现的因此每种存储引擎的索引都不一定完全相同并且每种存储引擎
也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度所有存储引擎
支持每个表至少16个索引总索引长度至少为256字节。大多数存储引警有更高的限制。
MySQL中索引的存储类型有两种BTREE和HASH,具体和表的存储引擎相关MyISAM和InnoDB
存储引警只支持BTREE索引MEMORY/HEAP存储引警可以支持HASH和BTREE索引。 索引的优点主要有以下几条
(1)通过创建唯一索引可以保证数据库表中每一行数据的唯一性
(2)可以大大加快数据的查询速度这也是创建索引的最主要的原因
(3)在实现数据的参考完整性方面可以加速表和表之间的连接。
(4)在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。 增加索引也有许多不利主要表现在如下几个方面
(1)创建索引和维护索引要耗费时间并且随着数据量的增加所耗费的时间也会增加
(2)索引需要占磁盘空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸。
(3)当对表中的数据进行增加、删除和修改的时候索引也要动态地维护这样就降低了数据的维护速度。 2、MySQL的索引可以分为以下几类
1.普通索引和唯一索引
普通索引是MySQL中的基本索引类型允许在定义索引的列中插入重复值和空值
唯一索引索引列的值必须唯一工但允许有空值。
如果是组合索引则列值的组合必须唯主键索引是一种特殊的唯一索引不允许有空值。
2.单列索引和组合索引
单列索引即一个索引只包含单个列一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建的索引只有在查询条件中使用了这些字段的左边字段时索引才会被使用。
使用组合索引时遵循最左前缀集合。
3.全文索引
全文索引类型为FULL TEXT,在定义索引的列上支持值的全文查找允许在这些索引列中
插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
MySQL中只有MyISAM存储引擎支持全文索引。
4.空间索引
空间索引是对空间数据类型的字段建立的索引MySQL中的空间数据类型有4种分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySOL使用SPATIAL关键字进行扩展使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须将其声明为NOT NULL非空,空间索引只能在存储引擎为MYISAM的表中创建。 3、索引设计原则
索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时应该考虑以下准则
(1)索引并非越多越好一个表中如有大量的索引不仅占用磁盘空间,而且会影响INSERT(增)、DELETE删、UPDATE改等语句的性能因为当表中的数据更改的同时索引也会进行调整和更新。
(2)避免对经常更新的表进行过多的索引并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引但要避免添加不必要的字段。
(3)数据量小单表的列的数量小占用磁盘空间小的表最好不要使用索引由于数据较少查询花费的时间可能比遍历索引的时间还要短索引可能不会产生优化效果。
(4)在条件表达式中经常用到的不同值较多的列上建立索引在不同值很少的列上不要建立索引。
比如在学生表的“性别字段上只有”男”与”女两个不同值因此就无须建立索引。
如果建立索引不但不会提高查询效率反而会严重降低数据更新速度。
(5)当唯一性是某种数据本身的特征时指定唯一索引。使用唯一索引需能确保定义的列的数据完整性以提高查询速度。
(6)在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引如果待排序的列有多个可以在这些列上建立组合索引。 二.创建索引
MySQL支持多种方法在单个或多个列上创建索引:
在创建表的定义语句 CREATE TABLE中指定索引列使用ALTER TABLE语在存在的表上创
建索引或者使用CREATE INDEX 语句在已存在的表上添加索引。本节将详细介绍这3种方法:
1、创建表的时候创建索引
使用CREATE TABLE创建表时除了可以定义列的数据类型还可以定义主键约束、外
键约束
或者唯一性约束而不论创建那种约束在定义约束的同时相当于在指定列上创建了
一个索引。
创建表时创建索引的基本语法格式如下: CREATE TABIE table_name [col_name datatype] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC] UNIOUE、FULLTEXT和SPATIAL为可选参数分别表示唯一索引、全文索引和空间索引:
INDEX与KEY为同义词两者作用相同用来指定创建索引;
col_name为需要创建索引的字段列该列必须从数据表中定义的多个列中选择;
index name 指定索引的名称为可选参数如果不指定MySQL默认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; 由结果可以看到book1表的year_publication字段上成功建立索引
其索引名称year_publication为MySQL自动添加。使用EXPLAIN语句查看索引是否正在
使用
explain select * from book where year_publication1990 \G; EXPLAIN语句输出结果的各个行解释如下: (1)select type行指定所使用的SELECT查询类型这里值为SIMPLE,表示简单的SELECT不使用UNION或子查询。其他可能的取值有:PRIMARY、UNION、SUBQUERY等 (2)table 行指定数据库读取的数据表的名字它们按被读取的先后顺序排列。 (3)type 行指定了本数据表与其他数据表之间的关联关系可能的取值有systemconsteq_ref、ref、range、index 和 All。 (4)possible keys行给出了MySQL在搜索数据记录时可选用的各个索引。 (5)key 行是MySQL实际选用的索引。 (6)key len行给出索引按字节计算的长度key len数值越小表示越快 (7)ref 行给出了关联关系中另一个数据表里的数据列的名字。 (8)rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数 (9)extra 行提供了与关联操作有关的信息。 可以看到possible keys和 key的值都为 year_publication查询时使用了索引。
2.创建唯一索引
要加unique
创建唯一索引的主要原因是减少查询索引列操作的执行时间尤其是对比较庞大的数据表。
它与前面的普通索引类似不同的就是:索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。
举例
创建一个表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 由结果可以看到id字段上已经成功建立了一个名为UniqIdx的唯一索引。 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 由结果可以看到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)
); 该语句执行完毕之后使用SHOW CREATE TABLE查看表结构
show create table t3 \G 由结果可以看到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 id1 and namejoe \G ; 可以看到查询id和name字段时使用了名称MultiIdx的索引
如果查询name,age组合或者单独查询name和age字段结果如下
此时possible_keys和key值为NULL并没有使用在t3表中创建的索引进行查询。 5.创建全文索引
用fulltext
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)
) ENGINEMyISAM; 因为MySQL5.6中默认存储引擎为InnoDB在这里创建表时需要修改表的存储引擎为 MyISAM 不然创建索引会出错。
语句执行完毕之后使用SHOW CREATE TABLE查看表结构
show create table t4 \G 由结果可以看到info字段上已经成功建立了一个名为FullTxtIdx的FULLTEXT索引。
全文索引非常适合于大型数据集对于小的数据集它的用处可能比较小。 6、创建空间索引
要加spatial
空间索引必须在MyISAM类型的表中创建且空间类型的字段必须为非空(not null)。
举例创建表t5在空间类型为GEOMETRY的字段上创建空间索引SQL语句如下
create table t5
(g GEOMETRY not null,
spatial index spatIdx(g)
)ENGINEMyISAM; 该语句执行完毕之后使用SHOW CREATE TABLE查看表结构
show create table t5 \G 可以看到t5表的g字段上创建了名称为 spatldx的空间索引。
注意创建时指定空间类型字段值的非空约束并且表的存储引擎为MyISAM。 2、在已经存在的表上创建索引
在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句
本节将介绍如何使用ALTER TABLE和CREATE INDEX语在已知表字段上创建索引。
1、使用ALTER TABLE语创建索引的基本语法如下 ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index name] (col_name[length],...) [ASC | DESC] 与创建表时创建索引的语法不同的是
在这里使用了ALTER TABLE和ADD关键字,ADD表示向表中添加索引。
普通索引
举例在book表中的bookname字段上建立名为BkNameIdx的普通索引
SQL语句如下
alter table book add index BkNameIdx (bookname(30)); 使用SHOW INDEX语句查看表中的索引:
show index from book \G; 其中各个主要参数的含义为: (1)Table表示创建索引的表。 (2)Non unique表示索引非唯一1代表是非唯一索引0代表唯一索引。 (3)Key_name表示索引的名称。 (4)Seq in index表示该字段在索引中的位置单列索引该值为1 组合索引为每个字段在索引定义中的顺序。 (5)Columnname表示定义索引的列字段。 (6)Sub part表示索引的长度。 (7)Nul表示该字段是否能为空值。 (8)Indextype表示索引类型。 可以看到现在表中已经有了两个索引另一个为通过ALTER TABLE语句添加的名称 为BkNameIdx的索引该索引为非唯一索引长度为30。
唯一索引
要加unique
举例在book表的bookId字段上建立名称为UniqidIdx 的唯一索引SQL语句如下
alter table book add unique index UniqidIdx(bookId); 使用SHOW INDEX语句查看表中的索引
show index from book \G; 可以看到Non unique属性值为0表示称为UnigidIdx的索引为唯一索引创建唯一索引成功。
举例在book表的comment字段上建立单列索引SQL语句如下
alter table book add index BkcmtIdx(comment(50)); 使用SHOW INDEX语句查看表中的索引
show index from book \G; 可以看到语句执行之后在book 表的comment字段上建立了名称为 BkcmgIdx的索引长度为50在查询时只需要检索前50个字符。
组合索引
举例在book表的authors和info字段上建立组合索引SQL语句如下
alter table book add index BkAuAndInfoIdx ( authors(20),info(50) ); 使用SHOW INDEX语句查看表中的索引
show index from book \G; 可以看到名称为BkAuAndInfoldx 的索引由两个字段组成
authors 字段长度为20在组合索引中的序号为1该字段不允许空值 NULL;
info 字段长度为50在组合索引中的序号为2该字段可以为空值NULL。 全文索引
举例创建表t6在t6表上使用ALTER TABLE创建全文索引SQL语句如下
create table t6
(
id int not null,
info CHAR(255)
) ENGINEMyISAM; 注意修改ENGINE参数为MyISAMMySQL默认引擎InnoDB不支持全文索引。
我们现在使用的5.7 版本可以支持 innodb 引擎以前的5.6 不支持。
使用ALTER TABLE语句在info字段上创建全文索引
alter table t6 add FULLTEXT index infoFTIdx (info); 使用SHOW INDEX语句查看索引
show index from t6 \G; 可以看到t6表中已经创建了名称为infoFTIdx的索引该索引在info字段上创建类型为FULLTEXT允许空值。 空间索引
要加spatial
创建表t7在t7的空间数据类型字段g上创建名称为spatIdx的空间索引SQL语句如下
create table t7
(
g GEOMETRY not null
)ENGINEMyISAM; 使用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被映射到一个
ALTERTABLE语上基本语法结构为: CREATE [UNIOUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name [length],...) [ASC | DESC] 可以看到CREATE INDEX语和ALTER TABLE语的语法基本一样只是关键字不同在这
里使用相同的表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表删除按上面的语句重新建立然后进行下
面的操作。
普通索引
举例在book表中的bookname字段上建立名为BkNameIdx的普通索引SQL语句如下
create index BkNameIdx on book(bookname); 语句执行完毕之后将在book表中创建名称为BkNameIdx的普通索引。
可以使用SHOW INDEX或者SHOW CREATE TABLE语句查看book表中的索引 其索引内容与前面介绍相同。
show index from book \G; 唯一索引
在book表的bookId字段上建立名称为UniqidIdx 的唯一索引SQL语句如下
create unique index UniqidIdx on book ( bookId );
语句执行完毕之后将在book表中创建名称为UniqidIdx 的唯一索引。 单列索引
在book表的comment字段上建立单列索引SQL语句如下
create index BkcmtIdx on book(comment(50) );
语句执行之后将在book表的comment字段上建立一个名为BkcmtIdx的单列索引长度为50。 组合索引
在book表的authors和info字段上建立组合索引SQL语句如下
create index BkAuAndInfoIdx on book ( authors(20),info(50) );
语句执行完毕之后将在book表的authors和info字段上建立了一个名为BkAuAndInfoIdx的组合索引authors的索引序号为1长度为20info的索引序号为2长度为50。 全文索引
删除表t6重新建立表t6在t6表中使用CREATE INDEX语句在CHAR类型的info字段上
创建全文索引SQL语句如下
首先删除表t6并重新建立该表分别输入下面语句
drop table t6; create table t6 ( id int not null, info char(255) ) ENGINEMyISAM; 全文索引
使用CREATE INDEX在t6表的info字段上创建名称为infoFTIdx的全文索引
create fulltext index infoFTIdx on t6(info); 语句执行完毕之后将在t6表中创建名称为infoFTIdx的索引该索引在info字段上创建类型为FULLTEXT允许空值。 空间索引
删除表t7重新创建表t7在t7表中使用CREATE INDEX语句在空间数据类型字段g上
创建名称为spatIdx的空间索引SQL语句如下
首先删除表t7并重新建立该表分别输入下面语句
drop table t7; create table t7
(
g GEOMETRY not null
)ENGINEMyISAM; 使用CREATE INDEX语句在表t7的g字段建立空间索引
create spatial index spatIdx on t7 (g); 语句执行完毕之后将在t7表中创建名称为spatIdx的空间索引该索引在g字段上创建。
show index from t7 \G; 创建索引的两种方法 三.删除索引
MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句两者可实现相同的功能DROP INDEX语在内部被映射到一个ALTER TABLE语中
1、使用ALTER TABLE 删除索引
ALTERT ABLE删除索引的基本语法格式如下: ALTER TABLE table_name DROP INDEX index_name; 举例删除book表中的名称为UniqidIdx的唯一索引SQL语句如下
首先查看book表中是否有名称为UniqidIdx的索引输入SHOW语句如下
show create table book \G; 查询结果可以看到book表中有名称为UniqidIdx的唯一索引该索引在bookid字段上创建
下面删除该索引输入删除语句如下
alter table book drop index UniqidIdx; 语句执行完毕使用SHOW语句查看索引是否被删除
show create table book \G; 由结果可以看到book表中已经没有名称为UniqidIdx的唯一索引删除索引成功。
注意添加AUTO_ICREMENT约束字段的唯一索引不能被删除。
2、使用DROP INDEX语删除索引
DROP INDEX删除索引的基本语法格式如下: DROP INDEX index_name ON table_name ; 举例
删除book表中名称为BkAuAndInfoIdx的组合索引SQL语句如下
drop index BkAuAndInfoIdx on book; 语句执行完毕使用SHOW语句查看索引是否被删除
show create table book \G; 可以看到book表中已经没有名称为 BkAuAndInfoldx的组合索引删除索引成功。
删除表中的列时如果要删除的列为索引的组成部分则该列也会从索引中删除。
如果组成索引的所有列都被删除则整个索引将被删除。
10个索引失效的坑
https://zhuanlan.zhihu.com/p/582525271?utm_id0
删除索引的两种方法 四.练习题
注意事项 疑问1:索引对数据库性能如此重要应该如何使用它? 为数据库选择正确的索引是一项复杂的任务。如果索引列较少则需要的磁盘空间和维护开销都较少。如果在一个大表上创建了多种组合索引索引文件也会膨胀很快。而另一方面,索引较多可覆盖更多的查询。可能需要试验若干不同的设计才能找到最有效的索引。可以添加、修改和删除索引而不影响数据库架构或应用程序设计。因此应尝试多个不同的索引从而建立最优的索引。 疑问2:尽量使用短索引 对字符串类型的字段进行索引如果可能应该指定一个前缀长度。例如如果有一个CHAR(255)的列如果在前 10个或30个字符内多数值是唯一的则不需要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间、减少IO操作。 练习题
在test数据库中创建数据表 writerswriters表结构如表9.3所示按要求进行操作。 (1)在数据库test中创建表writers存储引擎为MyISAM创建表的同时在w_id字段上
添加名称为UniqIdx的唯一索引。
(2)使用ALTER TABLE语在w_name字段上建立名称为nameldx的普通索引。
(3)使用CREATE INDEX语在w_address和w_age字段上建立名称为Multildx的组合索
引。
(4)使用CREATE INDEX语在w_note字段上建立名称为FTIdx的全文索引。
(5)删除名称为FTIdx的全文索引。
