当前位置: 永利皇宫463手机版 > 数据库 > 正文

作者的MYSQL学习心得

时间:2019-12-07 04:06来源: 数据库
自家的MYSQL学习心得(九) 索引 自己的MYSQL学习体会(风流罗曼蒂克)轻巧语法 自个儿的MYSQL学习体会(二)数据类型宽度 本身的MYSQL学习心得(三)查看字段长度 作者的MYSQL学习体会

自家的MYSQL学习心得(九) 索引

自己的MYSQL学习体会(风流罗曼蒂克) 轻巧语法

自个儿的MYSQL学习体会(二) 数据类型宽度

本身的MYSQL学习心得(三) 查看字段长度

作者的MYSQL学习体会(四) 数据类型

自己的MYSQL学习体会(五) 运算符

自己的MYSQL学习心得(六) 函数

自身的MYSQL学习心得(七) 查询

自己的MYSQL学习心得(八) 插入 更新 删除

作者的MYSQL学习心得(十) 自定义存款和储蓄进程和函数

自个儿的MYSQL学习体会(十意气风发) 视图

本身的MYSQL学习心得(十八) 触发器

本身的MYSQL学习体会(十八) 权限管理

本人的MYSQL学习体会(十三) 备份和复苏

小编的MYSQL学习心得(十八) 日志

自个儿的MYSQL学习体会(十四) 优化

自己的MYSQL学习心得(十四) 复制

 

那大器晚成篇《我的MYSQL学习体会(九)》将会讲课MYSQL的目录

 

目录是在蕴藏引擎中完结的,因而每一个存款和储蓄引擎的目录都不料定完全相符,而且每种存款和储蓄引擎也不必然帮助全部索引类型。

基于存款和储蓄引擎定义各种表的最大索引数和最大索引长度。全数存款和储蓄引擎协助每种表起码15个目录,总索引长度起码为256字节。

绝大许多储存引擎有越来越高的界定。MYSQL中索引的蕴藏类型有三种:BTREE和HASH,具体和表的储存引擎相关;

MYISAM和InnoDB存款和储蓄引擎只辅助BTREE索引;MEMORAV4Y和HEAP存款和储蓄引擎能够扶植HASH和BTREE索引

 

 

SQL学习指南

mysql将引得充任表的可选零件,所以mysql5.1事情发生以前只可以接收alter table add xx来增多索引,mysql5.1带有5.1过后将create index命令映射到alter table add index

 

目录的长处:

1、通过创设独一索引,保险数据库表每行数据的唯后生可畏性

2、大大加速数据查询速度

3、在接纳分组和排序实行多少查询时,能够显然减少查询中分组和排序的光阴

 

目录的败笔:

1、维护索引要求开支数据库能源

2、索引需求占用磁盘空间,索引文件恐怕比数据文件更加快到达最大文件尺寸

永利皇宫463手机版,3、当对表的数额开展增加和删除改的时候,因为要保险索引,速度会直面震慑

 

目录的归类

1、普通索引和独一索引

主键索引是大器晚成种新鲜的独一索引,不容许有空值

2、单列索引和复合索引

单列索引只含有单个列

复合索引指八个字段上成立的目录,独有在询问条件中使用了创办索引时的率先个字段,索引才会被接纳。使用复合索引时固守最左前缀会集

3、全文索引

全文索引类型为FULLTEXT,在定义索引的列上支持值的全文字笔迹核查索,允许在那么些索引列中插入重复值和空值。全文索引能够在

CHA奥迪PB18、VARCHA帕杰罗、TEXT类型列上创制。MYSQL只有MYISAM存款和储蓄引擎协助全文索引

4、空间引得

空中引得是对空间数据类型的字段建构的目录,MYSQL中的空间数据类型有4种,

分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字张开扩充,使得能够用于创建正规索引类型的语法创制空间引得。创立空间引得的列,必需

将其声称为NOT NULL,空间引得只可以在存款和储蓄引擎为MYISAM的表中创制

 

以上的目录在SQLSERVER里都扶持

 

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

unique|fulltext|spatial为可选参数,分别代表独一索引、全文索引和空中引得;

index和key为同义词,两个效能相似,用来钦点创造索引

col_name为索要成立索引的字段列,该列必需从数据表中该定义的多少个列中选择;

index_name钦命索引的称谓,为可选参数,借使不点名,MYSQL暗中认可col_name为索引值;

length为可选参数,表示索引的长短,独有字符串类型的字段手艺钦定索引长度;

asc或desc钦点升序或降序的索引值存款和储蓄


万般索引

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查看表构造

CREATE TABLE `book` (
  `bookid` INT(11) NOT NULL,
  `bookname` VARCHAR(255) NOT NULL,
  `authors` VARCHAR(255) NOT NULL,
  `info` VARCHAR(255) DEFAULT NULL,
  `comment` VARCHAR(255) DEFAULT NULL,
  `year_publication` YEAR(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1

能够窥见,book表的year_publication字段成功建构了索引其索引名字为year_publication

倘使不加索引名,那么MySQL会以索引的率先个字段的名字来命名

CREATE TABLE customer5(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
dd DATETIME NOT NULL ,
KEY (NAME,dd),
CONSTRAINT idx_pri PRIMARY KEY (id))

永利皇宫463手机版 1

 而风华正茂旦三个表下有几个目录的第一个字段都以千篇风华正茂律的,那么索引名会在字段名后加序数

CREATE TABLE customer6(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
dd DATETIME NOT NULL ,
KEY (NAME,dd),
KEY (NAME),
CONSTRAINT idx_pri PRIMARY KEY (id))

永利皇宫463手机版 2

 

 

大家向表插入一条数据,然后使用EXPLAIN语句查看索引是不是有在利用

INSERT INTO BOOK VALUES(12,'NIHAO','NIHAO','文学','henhao',1990)


EXPLAIN SELECT * FROM book WHERE year_publication=1990 

 

因为言语比较容易,系统推断有异常的大大概会用到目录或许全文扫描

永利皇宫463手机版 3

EXPLAIN语句输出结果的次第行的分解如下:

select_type: 表示查询中各类select子句的类型(轻易 O中华V复杂)

type:代表MySQL在表中找到所需行的格局,又称“访谈类型”,数见不鲜类型如下:(从上至下,效果依次变好)

possible_keys :提出MySQL能动用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不自然被询问利用

key: 呈现MySQL在查询中实际上行使的目录,若未有动用索引,显示为NULL

key_len :表示索引中应用的字节数,可透过该列总结查询中利用的目录的长短

ref :表示上述表的接连相配原则,即什么列或常量被用来查找索引列上的值

rows :表示MySQL依据表总计音信及索引选拔景况,测度的找到所需的记录所需求读取的行数

Extra :满含不切合在其余列中展现但特别器重的额外新闻 如using where,using index

 

参考:MySQL学习系列2--MySQL实践安顿深入分析EXPLAIN


独一索引

独一索引列的值必得唯后生可畏,但允许有空值。倘若是复合索引则列值的结缘必得唯生机勃勃

建表

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

SHOW CREATE TABLE t1 查看表布局

SHOW CREATE TABLE t1 

 CREATE TABLE `t1` (                                                                                                                        
          `id` int(11) NOT NULL,                                                                                                                   
          `name` char(30) NOT NULL,                                                                                                                
          UNIQUE KEY `UniqIdx` (`id`)                                                                                                              
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8     

能够看来id字段上业已成功创立了多个名叫UniqIdx的有一无二索引

 

开创复合索引

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

SHOW CREATE TABLE t3

CREATE TABLE `t3` (                                                                                                                                                                                             
          `id` int(11) NOT NULL,                                                                                                                                                                                        
          `NAME` char(30) NOT NULL,                                                                                                                                                                                     
          `age` int(11) NOT NULL,                                                                                                                                                                                       
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
          KEY `MultiIdx` (`id`,`NAME`,`age`)                                                                                                                                                                            
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8        

由结果能够看来id,name,age字段上早就打响创设了一个名叫MultiIdx的复合索引

 

我们向表插入两条数据

INSERT INTO t3(id ,NAME,age,info) VALUES(1,'小明',12,'nihao'),(2,'小芳',16,'nihao')

使用EXPLAIN语句查看索引使用情形

EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME='小芳'

能够看看  possible_keyskey 为MultiIdx表明使用了复合索引

    id  select_type  table   type    possible_keys  key       key_len  ref            rows  Extra      
------  -----------  ------  ------  -------------  --------  -------  -----------  ------  -----------
     1  SIMPLE       t3      ref     MultiIdx       MultiIdx  94       const,const       1  Using where

举例大家只钦定name而不钦点id

EXPLAIN SELECT * FROM t3 WHERE  NAME='小芳'

    id  select_type  table   type    possible_keys  key     key_len  ref       rows  Extra      
------  -----------  ------  ------  -------------  ------  -------  ------  ------  -----------
     1  SIMPLE       t3      ALL     (NULL)         (NULL)  (NULL)   (NULL)       2  Using where

结果跟SQLSE帕杰罗VE讴歌RDX相像,也是不走索引, possible_keyskey都为NULL

 


全文索引

FULLTEXT索引能够用来全文字笔迹核实索。独有MYISAM存款和储蓄引擎帮忙FULLTEXT索引,而且只扶助CHA锐界、VARCHAHighlander和TEXT类型

全文索引不协理过滤索引。

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 

出于MYSQL5.6暗许存款和储蓄引擎为InnoDB,这里创立表的时候要改过表的存款和储蓄引擎为MYISAM,不然创制索引会出错

SHOW CREATE TABLE t4 

Table   Create Table                                                                                                                                                                                                    
------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
t4      CREATE TABLE `t4` (                                                                                                                                                                                             
          `id` int(11) NOT NULL,                                                                                                                                                                                        
          `name` char(30) NOT NULL,                                                                                                                                                                                     
          `age` int(11) NOT NULL,                                                                                                                                                                                       
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
          FULLTEXT KEY `FulltxtIdx` (`info`)                                                                                                                                                                            
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8   

由结果可以看出,info字段上业已成功创设名字为FulltxtIdx的FULLTEXT索引。

全文索引极度符合大型数据集结

 

在SQLSE奥迪Q5VE中华V里使用全文索引比MYSQL还要复杂

详尽能够参照上面两篇小说:

有关SQLSE奔驰G级VEKoleos的全文目录跟全文索引的界别

[SQLSERVER]SQL中的全文字笔迹查证索(转邹建卡塔尔


空中引得

空中引得必需在 MYISAM类型的表中创设,何况空间类型的字段必得为非空

建表t5

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

SHOW CREATE TABLE t5

TABLE   CREATE TABLE                                                                                                   
------  ---------------------------------------------------------------------------------------------------------------
t5      CREATE TABLE `t5` (                                                                                            
          `g` GEOMETRY NOT NULL,                                                                                       
          SPATIAL KEY `spatIdx` (`g`)                                                                                  
        ) ENGINE=MYISAM DEFAULT CHARSET=utf8    

能够看看,t5表的g字段上开创了名叫spatIdx的空间引得。注意创立时钦命空间类型字段值的非空节制

何况表的囤积引擎为MYISAM


后生可畏度存在的表上创造索引

在已经存在的表中成立索引,能够选择ALTECRUISER TABLE也许CREATE INDEX语句

 

1、使用ALTELAND TABLE语句创立索引,语法如下

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]

[index_name](col_name[length],...)[ASC|DESC]

 

与创立表时开创索引的语法不一样,在那处运用了ALTER TABLE和ADD关键字,ADD表示向表中增添索引

在t1表中的name字段上树立NameIdx普通索引

ALTER TABLE t1 ADD INDEX NameIdx(NAME)

增多索引之后,使用SHOW INDEX语句查看内定表中创立的目录

SHOW INDEX FROM t1

TABLE   Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT  Index_comment
------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t1               0  UniqIdx              1  id           A                    0    (NULL)  (NULL)          BTREE                             
t1               1  NameIdx              1  NAME         A               (NULL)    (NULL)  (NULL)          BTREE         

依次参数的意义

1、TABLE:要开创索引的表

2、Non_unique:索引非唯大器晚成,1意味是非独一索引,0代表独一索引

3、Key_name:索引的称号

4、Seq_in_index:该字段在目录中的地点,单列索引该值为1,复合索引为每一种字段在目录定义中的顺序

5、Column_name:定义索引的列字段

6、Sub_part:索引的长度

7、NULL:该字段是还是不是能为空值

8、Index_type:索引类型

 

可以看出,t1表已经存在了贰个独一索引

 

在t3表的age和info字段上成立复合索引

ALTER TABLE t3 ADD INDEX t3AgeAndInfo(age,info)

利用SHOW INDEX查看表中的目录

SHOW INDEX FROM t3

Table   Non_unique  Key_name      Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  ------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t3               1  MultiIdx                 1  id           A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  MultiIdx                 2  NAME         A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  MultiIdx                 3  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  t3AgeAndInfo             1  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  t3AgeAndInfo             2  info         A               (NULL)    (NULL)  (NULL)  YES     BTREE             

可以看来表中的字段的各种,第二个岗位是age,首个职分是info,info字段是可空字段

永利皇宫463手机版 4

 永利皇宫463手机版 5

 

创造表t6,在t6表上创办全文索引

CREATE TABLE t6
(
  id INT NOT NULL,
  info CHAR(255)
)ENGINE= MYISAM;

瞩目纠正ENGINE参数为MYISAM,MYSQL私下认可引擎InnoDB不扶植全文索引

使用ALTELAND TABLE语句在info字段上制造全文索引

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info)

采纳SHOW INDEX查看索引意况

SHOW INDEX FROM t6

Table   Non_unique  Key_name   Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  ---------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t6               1  infoFTIdx             1  info         (NULL)          (NULL)    (NULL)  (NULL)  YES     FULLTEXT                          

 

始建表t7,并在空间数据类型字段g上创办名称叫spatIdx的空中引得

CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;

应用ALTE福特Explorer TABLE在表t7的g字段创立空间引得

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g)

运用SHOW INDEX查看索引景况

SHOW INDEX FROM t7

Table   Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t7               1  spatIdx              1  g            A               (NULL)        32  (NULL)          SPATIAL                           

 

 

2、使用CREATE INDEX语句创造索引,语法如下

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name

ON table_name(col_name[length],...)  [ASC|DESC]

 

可以看看CREATE INDEX语句和ALTEPRADOINDEX语句的主干语法相像,只是关键字差别。

大家树立三个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
)

 

确立平日索引

CREATE INDEX BkNameIdx ON book(bookname)

 

成立独一索引

CREATE UNIQUE INDEX UniqidIdx ON book(bookId)

 

确立复合索引

CREATE INDEX BkAuAndInfoIdx ON book(AUTHORS(20),info(50))

 

创造全文索引,大家drop掉t6表,重新树立t6表

DROP TABLE IF EXISTS t6

CREATE TABLE t6
(
  id INT NOT NULL,
  info CHAR(255)
)ENGINE= MYISAM;

CREATE FULLTEXT INDEX infoFTIdx ON t6(info);

 

成立空间引得,大家drop掉t7表,重新树立t7表

DROP TABLE IF EXISTS t7

CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;

CREATE SPATIAL INDEX spatIdx  ON t7(g)

删去索引

MYSQL中动用ALTE大切诺基 TABLE或许DROP INDEX语句来删除索引,两个达成均等效果

1、使用ALTE卡宴 TABLE删除索引

 语法

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE book DROP INDEX UniqidIdx

SHOW CREATE TABLE book

Table   Create Table                                                                                                                                                                                                                                                                                                                                                      
------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                                                                             
          `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                                                                      
          `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                               
          `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                                
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                               
          `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                            
          `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                                                                            
          KEY `BkNameIdx` (`bookname`),                                                                                                                                                                                                                                                                                                                                   
          KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))                                                                                                                                                                                                                                                                                                                 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8       

能够看来,book表中后生可畏度未有名字为UniqidIdx的头一无二索引,删除索引成功

 

注意:AUTO_INCREMENT约束字段的独一索引不可能被删去!!

 

2、使用DROP INDEX 语句删除索引

DROP INDEX index_name ON table_name

DROP INDEX BkAuAndInfoIdx ON book

SHOW CREATE TABLE book;

Table   Create Table                                                                                                                                                                                                                                                                                                   
------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                          
          `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                   
          `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                            
          `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                             
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                            
          `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                         
          `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                         
          KEY `BkNameIdx` (`bookname`)                                                                                                                                                                                                                                                                                 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8  

能够看到,复合索引BkAuAndInfoIdx已经被剔除了

 

提醒:删除表中的某列时,固然要删减的列为索引的组成都部队分,则该列也会从索引中删去。

倘若索引中的全部列都被剔除,则整个索引将被去除!!


总结

那豆蔻梢头节介绍了MYSQL中的索引,索引语句的创制和删除和意气风发部分简洁明了用法,希望对我们有扶植

 

如有不对的地点,接待我们拍砖o(∩_∩)o 

正文版权归作者全部,未经我同意不得转发。

自小编的MYSQL学习心得(九)

 

本人的MYSQL学习心得(少年老成)

自个儿的MYSQL学习心得(二)

自身的MYSQL学习心得(三)

本人的MYSQL学习心得(四)

自身的MYSQL学习心得(五)

笔者的MYSQL学习心得(六)

自家的MYSQL学习心得(七)

本人的MYSQL学习体会(八)

 

那风度翩翩篇《我的MYSQL学习体会(九)》将会讲课MYSQL的目录

 

目录是在积攒引擎中贯彻的,由此各类存款和储蓄引擎的目录都不自然完全相近,并且每一个存款和储蓄引擎也不鲜明辅助所有索引类型。

基于存款和储蓄引擎定义种种表的最大索引数和最大索引长度。全部存款和储蓄引擎扶持每一种表最少十七个目录,总索引长度起码为256字节。

大部囤积引擎有更加高的限定。MYSQL中索引的仓库储存类型有二种:BTREE和HASH,具体和表的蕴藏引擎相关;

MYISAM和InnoDB存款和储蓄引擎只帮衬BTREE索引;MEMORubiconY和HEAP存款和储蓄引擎能够辅助HASH和BTREE索引

 

目录的帮助和益处:

1、通过创办独一索引,有限支撑数据库表每行数据的唯黄金年代性

2、大大加快数据查询速度

3、在运用分组和排序举行多少查询时,能够一清二楚滑坡查询中分组和排序的年华

 

目录的弱项:

1、维护索引需求消耗数据库财富

2、索引须求占用磁盘空间,索引文件可能比数据文件更加快达到最大文件尺寸

3、当对表的数目举办增加和删除改的时候,因为要保证索引,速度会遭逢震慑

 

目录的分类

1、普通索引和独一索引

主键索引是生机勃勃种独特的独步一时索引,不容许有空值

2、单列索引和复合索引

单列索引只包括单个列

复合索引指七个字段上创建的目录,独有在询问条件中央银行使了创办索引时的第叁个字段,索引才会被利用。使用复合索引时据守最左前缀会集

3、全文索引

全文索引类型为FULLTEXT,在定义索引的列上接济值的全文字笔迹考验索,允许在这里些索引列中插入重复值和空值。全文索引能够在

CHATiggo、VARCHAPRADO、TEXT类型列上创制。MYSQL独有MYISAM存款和储蓄引擎辅助全文索引

4、空间引得

空中引得是对空间数据类型的字段建构的目录,MYSQL中的空间数据类型有4种,

分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字打开扩大,使得能够用于创立正规索引类型的语法成立空间引得。创造空间引得的列,必得

将其宣称为NOT NULL,空间引得只好在积攒引擎为MYISAM的表中创建

 

如上的目录在SQLSERVER里都扶助

 

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

unique|fulltext|spatial为可选参数,分别代表独一索引、全文索引和空中引得;

index和key为同义词,两个效用同样,用来钦命创造索引

col_name为索要创建索引的字段列,该列必得从数据表中该定义的四个列中选取;

index_name钦点索引的称号,为可选参数,借使不点名,MYSQL私下认可col_name为索引值;

length为可选参数,表示索引的长度,只有字符串类型的字段工夫内定索引长度;

asc或desc钦定升序或降序的索引值存款和储蓄


日常索引

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查看表构造

CREATE TABLE `book` (
  `bookid` INT(11) NOT NULL,
  `bookname` VARCHAR(255) NOT NULL,
  `authors` VARCHAR(255) NOT NULL,
  `info` VARCHAR(255) DEFAULT NULL,
  `comment` VARCHAR(255) DEFAULT NULL,
  `year_publication` YEAR(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1

能够窥见,book表的year_publication字段成功营造了索引其索引名为year_publication

 

我们向表插入一条数据,然后使用EXPLAIN语句查看索引是还是不是有在动用

INSERT INTO BOOK VALUES(12,'NIHAO','NIHAO','文学','henhao',1990)


EXPLAIN SELECT * FROM book WHERE year_publication=1990 

 

因为言语比较轻易,系统决断有异常的大恐怕会用到目录大概全文扫描

永利皇宫463手机版 6

EXPLAIN语句输出结果的大器晚成大器晚成行的演说如下:

select_type: 表示查询中种种select子句的品种(轻松 O福特Explorer复杂)

type:代表MySQL在表中找到所需行的主意,又称“访谈类型”,管见所及类型如下:(从上至下,效果依次变好)

possible_keys :提议MySQL能应用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不自然被询问利用

key: 显示MySQL在查询中实际上行使的目录,若未有应用索引,彰显为NULL

key_len :表示索引中央银行使的字节数,可透过该列计算查询中动用的目录的长短

ref :表示上述表的连天匹配原则,即什么列或常量被用来查找索引列上的值

rows :表示MySQL依据表总结新闻及索引采用景况,推测的找到所需的记录所须要读取的行数

Extra :包涵不相符在其余列中彰显但极其入眼的额外消息 如using where,using index

 

参谋:MySQL学习系列2--MySQL实行陈设深入分析EXPLAIN


独一索引

独一索引列的值必得唯生机勃勃,但允许有空值。借使是复合索引则列值的重新整合必得唯意气风发

建表

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

SHOW CREATE TABLE t1 查看表布局

SHOW CREATE TABLE t1 

 CREATE TABLE `t1` (                                                                                                                        
          `id` int(11) NOT NULL,                                                                                                                   
          `name` char(30) NOT NULL,                                                                                                                
          UNIQUE KEY `UniqIdx` (`id`)                                                                                                              
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8     

能够观察id字段上曾经成功建设构造了三个名称为UniqIdx的必须要经过的路索引

 

创立复合索引

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

SHOW CREATE TABLE t3

CREATE TABLE `t3` (                                                                                                                                                                                             
          `id` int(11) NOT NULL,                                                                                                                                                                                        
          `NAME` char(30) NOT NULL,                                                                                                                                                                                     
          `age` int(11) NOT NULL,                                                                                                                                                                                       
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
          KEY `MultiIdx` (`id`,`NAME`,`age`)                                                                                                                                                                            
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8        

由结果能够观望id,name,age字段上早就打响建设构造了一个名字为MultiIdx的复合索引

 

作者们向表插入两条数据

INSERT INTO t3(id ,NAME,age,info) VALUES(1,'小明',12,'nihao'),(2,'小芳',16,'nihao')

使用EXPLAIN语句查看索引使用状态

EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME='小芳'

能够看见  possible_keyskey 为MultiIdx注脚使用了复合索引

    id  select_type  table   type    possible_keys  key       key_len  ref            rows  Extra      
------  -----------  ------  ------  -------------  --------  -------  -----------  ------  -----------
     1  SIMPLE       t3      ref     MultiIdx       MultiIdx  94       const,const       1  Using where

一旦大家只钦命name而不点名id

EXPLAIN SELECT * FROM t3 WHERE  NAME='小芳'

    id  select_type  table   type    possible_keys  key     key_len  ref       rows  Extra      
------  -----------  ------  ------  -------------  ------  -------  ------  ------  -----------
     1  SIMPLE       t3      ALL     (NULL)         (NULL)  (NULL)   (NULL)       2  Using where

结果跟SQLSE途锐VESportage相符,也是不走索引, possible_keyskey都为NULL

 


全文索引

FULLTEXT索引能够用来全文字笔迹查验索。只有MYISAM存储引擎扶助FULLTEXT索引,况且只帮忙CHA本田CR-V、VARCHA奥德赛和TEXT类型

全文索引不援助过滤索引。

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 

出于MYSQL5.6私下认可存款和储蓄引擎为InnoDB,这里创建表的时候要纠正表的存放引擎为MYISAM,不然创设索引会出错

SHOW CREATE TABLE t4 

Table   Create Table                                                                                                                                                                                                    
------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
t4      CREATE TABLE `t4` (                                                                                                                                                                                             
          `id` int(11) NOT NULL,                                                                                                                                                                                        
          `name` char(30) NOT NULL,                                                                                                                                                                                     
          `age` int(11) NOT NULL,                                                                                                                                                                                       
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
          FULLTEXT KEY `FulltxtIdx` (`info`)                                                                                                                                                                            
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8   

由结果能够看看,info字段上曾经成功创立名称叫FulltxtIdx的FULLTEXT索引。

全文索引特别符合大型数据集结

 

在SQLSEGL450VER里使用全文索引比MYSQL还要复杂

详尽能够参照上边两篇随笔:

有关SQLSESportageVEENVISION的全文目录跟全文索引的界别

[SQLSERVER]SQL中的全文字笔迹考验索(转邹建卡塔尔


空中引得

空中引得必得在 MYISAM类型的表中创立,並且空间类型的字段必得为非空

建表t5

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

SHOW CREATE TABLE t5

TABLE   CREATE TABLE                                                                                                   
------  ---------------------------------------------------------------------------------------------------------------
t5      CREATE TABLE `t5` (                                                                                            
          `g` GEOMETRY NOT NULL,                                                                                       
          SPATIAL KEY `spatIdx` (`g`)                                                                                  
        ) ENGINE=MYISAM DEFAULT CHARSET=utf8    

可以观察,t5表的g字段上开创了名字为spatIdx的空中引得。注意创立时钦定空间类型字段值的非空限定

还要表的蕴藏引擎为MYISAM


曾经存在的表上创造索引

在早已存在的表中创制索引,能够动用ALTEENVISION TABLE大概CREATE INDEX语句

 

1、使用ALTE途乐 TABLE语句创制索引,语法如下

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]

[index_name](col_name[length],...)[ASC|DESC]

 

与成立表时创立索引的语法不相同,在那地运用了ALTER TABLE和ADD关键字,ADD表示向表中增添索引

在t1表中的name字段上创造NameIdx普通索引

ALTER TABLE t1 ADD INDEX NameIdx(NAME)

增添索引之后,使用SHOW INDEX语句查看钦命表中创立的目录

SHOW INDEX FROM t1

TABLE   Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT  Index_comment
------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t1               0  UniqIdx              1  id           A                    0    (NULL)  (NULL)          BTREE                             
t1               1  NameIdx              1  NAME         A               (NULL)    (NULL)  (NULL)          BTREE         

梯次参数的含义

1、TABLE:要创设索引的表

2、Non_unique:索引非唯生龙活虎,1代表是非独一索引,0代表唯一索引

3、Key_name:索引的名号

4、Seq_in_index:该字段在目录中的地点,单列索引该值为1,复合索引为每种字段在目录定义中的顺序

5、Column_name:定义索引的列字段

6、Sub_part:索引的长度

7、NULL:该字段是或不是能为空值

8、Index_type:索引类型

 

可以看看,t1表已经存在了叁个独一索引

 

在t3表的age和info字段上制造复合索引

ALTER TABLE t3 ADD INDEX t3AgeAndInfo(age,info)

动用SHOW INDEX查看表中的目录

SHOW INDEX FROM t3

Table   Non_unique  Key_name      Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  ------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t3               1  MultiIdx                 1  id           A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  MultiIdx                 2  NAME         A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  MultiIdx                 3  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  t3AgeAndInfo             1  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  t3AgeAndInfo             2  info         A               (NULL)    (NULL)  (NULL)  YES     BTREE             

能够观察表中的字段的顺序,第二个岗位是age,第4个任务是info,info字段是可空字段

永利皇宫463手机版 7

 永利皇宫463手机版 8

 

创立表t6,在t6表上创办全文索引

CREATE TABLE t6
(
  id INT NOT NULL,
  info CHAR(255)
)ENGINE= MYISAM;

注意改过ENGINE参数为MYISAM,MYSQL私下认可引擎InnoDB不扶植全文索引

运用ALTEEvoque TABLE语句在info字段上创办全文索引

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info)

动用SHOW INDEX查看索引情形

SHOW INDEX FROM t6

Table   Non_unique  Key_name   Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  ---------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t6               1  infoFTIdx             1  info         (NULL)          (NULL)    (NULL)  (NULL)  YES     FULLTEXT                          

 

创制表t7,并在空间数据类型字段g上创办名称叫spatIdx的长空引得

CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;

行使ALTE冠道 TABLE在表t7的g字段建构空间引得

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g)

应用SHOW INDEX查看索引处境

SHOW INDEX FROM t7

Table   Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t7               1  spatIdx              1  g            A               (NULL)        32  (NULL)          SPATIAL                           

 

 

2、使用CREATE INDEX语句创设索引,语法如下

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name

ON table_name(col_name[length],...)  [ASC|DESC]

 

能够看到CREATE INDEX语句和ALTE科雷傲INDEX语句的基本语法相通,只是关键字不相同。

笔者们创设二个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
)

 

建构普通索引

CREATE INDEX BkNameIdx ON book(bookname)

 

树立独一索引

CREATE UNIQUE INDEX UniqidIdx ON book(bookId)

 

建设构造复合索引

CREATE INDEX BkAuAndInfoIdx ON book(AUTHORS(20),info(50))

 

树立全文索引,大家drop掉t6表,重新确立t6表

DROP TABLE IF EXISTS t6

CREATE TABLE t6
(
  id INT NOT NULL,
  info CHAR(255)
)ENGINE= MYISAM;

CREATE FULLTEXT INDEX infoFTIdx ON t6(info);

 

创建空间引得,大家drop掉t7表,重新确立t7表

DROP TABLE IF EXISTS t7

CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;

CREATE SPATIAL INDEX spatIdx  ON t7(g)

除去索引

MYSQL中选拔ALTE奇骏 TABLE恐怕DROP INDEX语句来删除索引,两个实现平等功用

1、使用ALTEQX56 TABLE删除索引

 语法

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE book DROP INDEX UniqidIdx

SHOW CREATE TABLE book

Table   Create Table                                                                                                                                                                                                                                                                                                                                                      
------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                                                                             
          `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                                                                      
          `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                               
          `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                                
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                               
          `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                            
          `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                                                                            
          KEY `BkNameIdx` (`bookname`),                                                                                                                                                                                                                                                                                                                                   
          KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))                                                                                                                                                                                                                                                                                                                 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8       

能够观察,book表中早已未有名叫UniqidIdx的独一索引,删除索引成功

 

注意:AUTO_INCREMENT约束字段的独一索引无法被去除!!

 

2、使用DROP INDEX 语句删除索引

DROP INDEX index_name ON table_name

DROP INDEX BkAuAndInfoIdx ON book

SHOW CREATE TABLE book;

Table   Create Table                                                                                                                                                                                                                                                                                                   
------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                          
          `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                   
          `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                            
          `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                             
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                            
          `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                         
          `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                         
          KEY `BkNameIdx` (`bookname`)                                                                                                                                                                                                                                                                                 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8  

可以看见,复合索引BkAuAndInfoIdx已经被删除了

 

提醒:删除表中的某列时,借使要删减的列为索引的组成部分,则该列也会从索引中除去。

假若索引中的全部列都被删除,则整个索引将被剔除!!


总结

那风姿浪漫节介绍了MYSQL中的索引,索引语句的创始和删除和有个别简短用法,希望对大家有救助

 

如有不对的地点,款待我们拍砖o(∩_∩)o 

笔者的MYSQL学习心得(风流浪漫) 小编的MYSQL学习感受(二) 笔者的MYSQL学习心得(三) 作者的MYSQL学习心得(四) 作者的MYSQL学习...

编辑: 数据库 本文来源:作者的MYSQL学习心得

关键词: