SQL 如何建立索引来加快数据库的查询 |
您所在的位置:网站首页 › 创建索引的sql语句 › SQL 如何建立索引来加快数据库的查询 |
文章目录
1. 什么是索引2. 索引的特点3. 索引的分类4. 索引的设计原则5. 创建索引5.1. 自动创建索引5.2. 手动创建索引5.2.1. 创建表时创建索引5.2.2. 在已经存在的表上创建索引5.2.2.1. alter语句创建索引5.2.2.2. create语句创建索引
6. 查看索引6.1. explain关键字查看索引6.2. show index关键字查看索引
7. 删除索引7.1. alter关键字删除索引7.2. drop关键字删除索引
1. 什么是索引
索引是一个单独存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针 在数据库中,索引用来加速对表的查询,索引技术通过使用快速路径访问的方法快速定位数据,减少了磁盘的I/O 可以把索引比作书的目录,每一张表都要有索引,就像每一本书都应该有目录一样。有了索引,数据库的检索速度可以大大提高 索引是作用于表查询的一项技术,因此创建索引时,需要确保该索引是应用在SQL查询语句的条件,一般作为where子句的条件。 在表现形式上,索引表现为一张表,该表保存了主键与索引字段,并指向实体表的记录。索引独立于数据表存放,但不能独立存在,必须属于某个表。索引由数据库自动维护,数据表被删除时,该表上的索引自动被删除。 不过需要注意的是,虽然索引极大地提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete的速度会被降低。 因为更新表时,数据库不仅需要更新表数据,还要更新索 引文件,此外由于索引是独立于表存在的“表”,因此建立索引会占用一定的磁盘空间。 3. 索引的分类 普通索引 最基本的索引,它没有任何限制,用于加速查询唯一索引(unique) 索引列的值必须唯一,但允许有空值。如果是组合索列值的组合必须唯一主键索引(primary key) 是一种特殊的唯 一索引,一个表只能有一个主键,不允许有空值,主键索引一般是在表建立的同时自动创建单列索引 指单个字段上创建索引组合索引 指条个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合的规则全文索引(fulltext) 该索引不直接与索引中的值相比较,而是查找文本中的关键字空间索引(spatial) MySQL 5.7.4实验室版本中,InnoDB存储引擎新增了对于几何数据空间索引的支持 4. 索引的设计原则设计索引应遵循如下原则: 只建立适量的索引。 索引并非越多越好,过多的索引会降低表的修改速度以及占用额外的存储空间。只在需要大量查询的表中建立大量索引。 应该避免对经常更新的表进行过多的索引,并且索引中的列要尽可能少。不在数据量小的表建立索引。 数据量小的表没有必要使用索引不在不同值少的列上建立索引。 如果表的某一列中大部分数据都是重复的,则没有必要建立索引指定唯一索引。 当表的某一列数据被指定为unique是,可以建立唯一索引在频繁操作的列上建立索引。 若想要建立索引,则应选择频繁排序(order by)或分组(group by)的列建立索引 5. 创建索引 5.1. 自动创建索引当在表上定义primary key或者unique约束条件时,数据库会自动创建一个对应的索引,也就是创建主键索引和唯一索引。 5.2. 手动创建索引 5.2.1. 创建表时创建索引在建表的同时创建索引的语法为: create table [表名]( [属性名1] [数据类型1] [约束1], ... [属性名n] [数据类型n] [约束n], [unique | fulltext | spatial] index|key [索引名]( [属性名1] [(长度)] [asc | desc], ... [属性名n] [(长度)] [asc | desc] ) );例如 建立普通索引 create table normal_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在id列建立普通索引 index normal_index(id) );建立唯一索引 create table unique_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在email列建立唯一索引,并升序排列 unique index uniquel_index(email asc) );建立全文索引 create table fulltext_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在username列建立全文索引 fulltext index fulltext_index(username) );建立单列索引 create table single_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在username列建立索引,索引长度为9 index single_index(username(9)) );建立多列索引 create table multi_table( id int(20) primary key, username varchar(20), email varchar(20), -- 在username列和email列建立索引,索引长度分别为9和10 index multi_index( username(9), email(10) ) ); 5.2.2. 在已经存在的表上创建索引 5.2.2.1. alter语句创建索引语法: alter table [表名] add [unique|fulltext|spatial] [index|key] [索引名]( [属性名1] [(长度)] [asc | desc], ... [属性名n] [(长度)] [asc | desc] );例如: 建立普通索引 -- 在normal_table表的id列建立名为normal_index的普通索引 alter table normal_table add index normal_index(id);建立唯一索引 -- 在unique_table表的username列建立名为unique_index的唯一索引 alter table unique_table add unique index unique_index(username);建立多列索引 -- 在multi_table表的username和email列建立名为multi_index的多列索引 alter table multi_table add index multi_index(username, email);其他类型的索引同理 5.2.2.2. create语句创建索引语法: create unique [unique|fulltext|spatial] index [索引名] ON [表名]( [属性名1] [(长度)] [asc | desc], ... [属性名n] [(长度)] [asc | desc] );例如 建立普通索引 -- 在normal_table表的id列建立名为normal_index的普通索引 create index normal_index on normal_table(id);建立唯一索引 -- 在unique_table表的username列建立名为unique_index的唯一索引 create unique index unique_index on unique_table(username);建立多列索引 -- 在multi_table表的username和email列建立名为multi_index的多列索引 create index multi_table on multi_table(username, email);其他类型的索引同理 6. 查看索引 6.1. explain关键字查看索引explain关键字用于查看和分析SQL索引使用情况 语法: explain select * from [表名];使用里explain语句后,会返回一张表: idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra其中对表中字段的说明可以参考这篇博客:mysql中explain查看sql语句索引使用情况 例如: -- 查看multi_table表中的所有使用情况 explain select * from multi_table;结果为: idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEmulti_tableindexmulti_table21661100.00Using index 6.2. show index关键字查看索引show index关键字用于查看某一表中的所有索引 语法: show index from [表名];使用里show index语句后,会返回一张表: TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression其中对表中字段的说明可以参考这篇博客:MySQL SHOW INDEX 的语法解析 例如: -- 查看multi_table表中的全部索引 show index from multi_table;结果为: TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpressionmulti_table0PRIMARY1idA0BTREEYESmulti_table1multi_index1usernameA09YESBTREEYESmulti_table1multi_index2emailA010YESBTREEYESmulti_table1multi_table21usernameA0YESBTREEYESmulti_table1multi_table22emailA0YESBTREEYES 7. 删除索引 7.1. alter关键字删除索引语法: alter table [表名] drop index [索引名];例如 -- 从multi_table表中删除multi_index索引 alter table multi_table drop index multi_index; 7.2. drop关键字删除索引语法: drop index [索引名] on [表名];例如 -- 从multi_table表中删除multi_index索引 drop index multi_index on multi_table; |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |