mysql——索引——概念

Wesley13
• 阅读 520
一、索引



索引由数据库表中一列或者多列组合而成,其作用是提高对表中数据的查询速度。

索引是创建在表上面的,是对数据表中一列或者多列的值进行排序的一种结构。


通过索引,查询数据时可以不必读完记录的所有信息,而只是查询索引列。


索引优点:提高检索数据的速度;

索引缺点:创建和维护索引需要耗费时间,耗费时间的数量随着数据量的增加而增加;索引需要占用物理空间,每一个索引要占一定的物理空间,

          增加、删除、修改数据时,要动态的维护索引,造成数据的维护速度降低了。


注意:索引可以提高查询速度,但是会影响插入记录的速度。向有索引的表中插入记录时,数据库会按照索引进行排序,这样就降低了插入记录的速度,

       插入大量记录时的速度影响更加明显。这种情况下,最好的办法是先删除表中的索引,然后再插入数据。插入完成后,再创建索引。



索引的分类


1、普通索引


2、唯一性索引


3、全文索引


4、单列索引


5、多列索引


6、空间索引


==================================================================================================
索引的设计原则

1、选择唯一性索引

2、为经常需要排序、分组和联合操作的字段建立索引

3、为常作为查询条件的字段建立索引

4、限制索引的数目

5、尽量使用数据量少的索引

6、尽量使用前缀索引

7、删除不再使用或者很少使用的索引

===========================================================================================================

创建索引

一、创建表的时候创建索引

语法格式如下:

create table 表名 (  属性名 数据类型 [完整性约束条件],
                     属性名 数据类型 [完整性约束条件],
                     ……
                     属性名 数据类型
                     [unique | fulltext | spatial] index | key
                       [ 别名 ] ( 属性名1 [(长度)]) [ asc | desc ] )
                   );

unique是可选参数,表示索引为唯一性索引;

fulltext是可选参数,表示索引为全文索引;

spatial是可选参数,表示索引为空间索引;

index 和 key参数用来指定字段为索引的,两者选择其中之一就可以了,作用是一样的;

“别名”是可选参数,用来给创建的索引取的新的名称;

“属性1”参数指定索引对应的字段的名称,该字段必须为前面定义好的字段;

“长度”是可选参数,其指索引的长度,必须是字符串类型才可以使用;

“asc”和“desc”都是可选参数,“asc”表示升序排序,“desc”表示降序排序;


1、创建普通索引

创建一个普通索引,不需要加任何unique、fulltext、spatial参数。

示例:
create table index1 ( id int,
                      name varchar(20),
                      sex varchar(20),
                      index index1_id (id)
                     );

show create table index1;

explain select * from index1 where id = 1; /*possible_keys:index1_id;kye:index1_id ;说明索引被引用了*/



2、创建唯一性索引

创建唯一性索引时,需要使用unique参数进行约束。

示例:创建一个表名为index2的表,表中的id字段上建立名为index2_id的唯一性索引,且以升序的形式排列。

create table index2 ( id int,
                      name varchar(20),
                      sex varchar(20),
                      unique index index2_id (id asc)
                     );

show create table index2;



3、创建全文索引

全文索引,只能创建在char、varchar、text类型的字段上,而且只有myisam存储引擎支持全文索引。

create table index3 ( id  int,
                      info varchar(20),
                      fulltext index index3_info ( info )
                     )engine=myisam;

show create table index3;

4、创建单列索引

单列索引是在表的某一个字段上创建索引

subject字段的长度为20,而index4_st索引的长度只有10,这样做的目的是为了提高查询速度。

对于字符型的数据,可以不用查询全部信息,而只查询前面的若干字符信息。


create table index4 ( id  int,
                      subject varchar(30),
                      index index4_st ( subject(10) )
                     );


show create table index4;


5、创建多列索引

创建多列索引是在表的多个字段上创建一个索引

create table index5 ( id  int,
                      name varchar(30),
                      sex varchar(4),
                      index index5_ns ( name,sex )
                     );


show create table index5;

可以看出,name和sex字段上已经建立了一个名为index5_ns的单列索引。

多列索引里,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用;

如果没有使用索引中的第一个字段,那么这个多列索引就不会起作用。


6、创建空间索引


创建空间索引时,必须使用spatial参数来设置。而且,创建空间索引时,表的存储引擎必须是myisam类型。而且,索引字段必须有非空约束。


create table index6 ( id int,
                      space geometry not null,
                      spatial index index6_sp ( space )
                    )engine=myisam;

show create table index6;


注意:space字段必须是非空的,而且数据类型是geometry类型。这个类型是空间数据类型。空间数据类型包括:geometry、point、linestring、polygon类型等


==================================================================================================================================

二、在已经存在的表上创建索引


在已经存在的表中,可以直接为表上的一个或几个字段创建索引。格式如下:


create [unique | fulltext | spatial] index 索引名
                       
on 表名 ( 属性名 [(长度)]) [ asc | desc ] );



unique是可选参数,表示索引为唯一性索引;

fulltext是可选参数,表示索引为全文索引;

spatial是可选参数,表示索引为空间索引;

index 用来指定字段为索引的;

“索引名”参数是给创建的索引取的新名称;

“表名”是指需要创建索引的表的名称,该表必须是已经存在的,如果不存在,需要先创建;

“属性名”参数指定索引对应的字段的名称,该字段必须为前面定义好的字段;

“长度”是可选参数,其指索引的长度,必须是字符串类型才可以使用;

“asc”和“desc”都是可选参数,“asc”表示升序排序,“desc”表示降序排序;


1、创建普通索引


create table example0 ( id int,
                        name varchar(20),
                        sex varchar(40)
                       );

show create table example0;


create index index7_id on example0( id );


2、创建唯一性索引

create unique index index8_id on index8( course_id );


3、创建全文索引

create fulltext index index9_info on index9( info );

4、创建单列索引

create index index10_addr on index10 ( address(4) );

5、创建多列索引

create index index11_na on index11 ( name , address );

6、创建空间索引

create spatial index index12_line on index12( line );


==================================================================================================

三、用alter table语句来创建索引


在已经存在的表上,可以通过alter table语句直接为表上的一个或几个字段创建索引

语法格式如下:


alter table 表名 add [unique | fulltext | spatial] index 索引名 ( 属性名 [(长度)]) [ asc | desc ] );


1、创建普通索引

alter table example0 add index index13_name ( name );


2、创建唯一性索引

alter table index14 add unique index index14_id ( course_id );


3、创建全文索引

alter table index15 add fulltext index index15_info( info );


4、创建单列索引


alter table index16 add index index16_addr( address(4) );


5、创建多列索引

alter table index17 add index index17_na( name,address );


6、创建空间索引

alter table index18 add spatial index index18_line( line );


====================================================================================================================

删除索引

指将表中已经存在的索引删除掉。

语法格式如下:

drop index 索引名 on 表名;

========================================================================================================================
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Wesley13 Wesley13
3年前
MySQL千万级别优化·中
MySQL千万级别的查询优化手段·中单列索引(假设在v\_record表中存在id列的索引)1、WHERE条件使用​EXPLAINSELECT\FROMv\_recordWHEREid2​结论:利用索引进行回表查询2、SELECT字段使用
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
95%的人都不知道 MySQL还有索引管理与执行计划
1.1索引的介绍  索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。  索引的一个主要目的就是加快检索表中数据的方法,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。!fi
Wesley13 Wesley13
3年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
3年前
MySQL 索引(3)
什么是索引?索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。比如想从字典中查询某一个字,我们可以通过偏旁、或者拼音来快速定位到要找的页码,这种方式也可以被理解为一种索引。Mysql常用的索引类型类型说明Normal(普通)普通索引,没任何限制。Unique(唯
Stella981 Stella981
3年前
ELK学习笔记之ElasticSearch的索引详解
0x00ElasticSearch的索引和MySQL的索引方式对比Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。倒排索引很多地方都有介绍,但是其比关系型
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究