>

笔者的MYSQL学习心得

- 编辑:金沙国际平台登录 -

笔者的MYSQL学习心得

本人的MYSQL学习体会(九) 索引

自身的MYSQL学习心得(风流洒脱) 轻便语法

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

自己的MYSQL学习心得(三) 查看字段长度

本身的MYSQL学习体会(四) 数据类型

本身的MYSQL学习心得(五) 运算符

自身的MYSQL学习体会(六) 函数

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

自己的MYSQL学习体会(八) 插入 更新 删除

小编的MYSQL学习心得(十) 自定义存款和储蓄进度和函数

本身的MYSQL学习心得(十风姿浪漫) 视图

自身的MYSQL学习心得(十九) 触发器

自个儿的MYSQL学习心得(十四) 权限管理

本人的MYSQL学习心得(十五) 备份和回复

作者的MYSQL学习体会(十六) 日志

本身的MYSQL学习体会(十八) 优化

自己的MYSQL学习体会(十五) 复制

 

那生机勃勃篇《作者的MYSQL学习心得(九)》将会讲课MYSQL的目录

 

目录是在仓库储存引擎中落实的,因而每一个存款和储蓄引擎的目录都不必然完全相符,何况每个存款和储蓄引擎也不自然协助全部索引类型。

听别人讲存款和储蓄引擎定义每种表的最大索引数和最大索引长度。全数存款和储蓄引擎匡助每种表起码十五个目录,总索引长度最少为256字节。

大好多仓库储存引擎有更加高的节制。MYSQL中索引的存款和储蓄类型有二种:BTREE和HASH,具体和表的蕴藏引擎相关;

MYISAM和InnoDB存款和储蓄引擎只帮助BTREE索引;MEMO中华VY和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、索引要求占用磁盘空间,索引文件大概比数据文件越来越快达到最大文件尺寸

3、当对表的数目进行增加和删除改的时候,因为要保险索引,速度会遭到震慑

 

目录的归类

1、普通索引和独一索引

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

2、单列索引和复合索引

单列索引只含有单个列

复合索引指多少个字段上创建的目录,独有在查询条件中运用了创制索引时的第1个字段,索引才会被使用。使用复合索引时信守最左前缀集合

3、全文索引

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

CHA劲客、VARCHARAV4、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))

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

图片 2

 

 

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

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


EXPLAIN SELECT * FROM book WHERE year_publication=1990 

 

因为言语比较容易,系统判别有相当大只怕会用到目录恐怕全文扫描

图片 3

EXPLAIN语句输出结果的逐风华正茂行的表明如下:

select_type: 表示查询中种种select子句的项目(轻便 ORubicon复杂)

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

结果跟SQLSERAV4VE大切诺基同样,也是不走索引, possible_keyskey都为NULL

 


全文索引

FULLTEXT索引能够用于全文字笔迹核算索。唯有MYISAM存款和储蓄引擎支持FULLTEXT索引,并且只扶植CHA福特Explorer、VARCHAEscort和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索引。

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

 

在SQLSECRUISERVE索罗德里使用全文索引比MYSQL还要复杂

详尽能够参照下边两篇作品:

有关SQLSE奥德赛VECR-V的全文目录跟全文索引的界别

[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


早就存在的表上创立索引

在早已存在的表中创制索引,可以动用ALTERAV4 TABLE只怕CREATE INDEX语句

 

1、使用ALTELacrosse 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字段是可空字段

图片 4

 图片 5

 

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

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

潜心改进ENGINE参数为MYISAM,MYSQL暗中认可引擎InnoDB不扶持全文索引

应用ALTE景逸SUV 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;

利用ALTETiggo 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语句和ALTEMuranoINDEX语句的主导语法雷同,只是关键字不一样。

大家树立二个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中央银行使ALTEHaval TABLE或许DROP INDEX语句来删除索引,两个完结平等作用

1、使用ALTE景逸SUV 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的目录

 

目录是在存储引擎中落到实处的,因而各类存款和储蓄引擎的目录都不必然完全相同,何况每一个存款和储蓄引擎也不自然协理全部索引类型。

凭借存款和储蓄引擎定义每一个表的最大索引数和最大索引长度。全体存款和储蓄引擎扶持每一种表最少17个目录,总索引长度最少为256字节。

超越八分之四存款和储蓄引擎有越来越高的节制。MYSQL中索引的蕴藏类型有三种:BTREE和HASH,具体和表的积累引擎相关;

MYISAM和InnoDB存款和储蓄引擎只协助BTREE索引;MEMO中华VY和HEAP存款和储蓄引擎能够补助HASH和BTREE索引

 

目录的长处:

1、通过创建独一索引,保障数据库表每行数据的唯大器晚成性

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

3、在动用分组和排序进行数量查询时,能够明显滑坡查询中分组和排序的时刻

 

目录的劣势:

1、维护索引必要花销数据库能源

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

3、当对表的数码实行增加和删除改的时候,因为要维护索引,速度会惨被震慑

 

目录的归类

1、普通索引和独一索引

主键索引是生机勃勃种特殊的唯一索引,不相同意有空值

2、单列索引和复合索引

单列索引只含有单个列

复合索引指多少个字段上开创的目录,唯有在询问条件中动用了创设索引时的首先个字段,索引才会被运用。使用复合索引时信守最左前缀集结

3、全文索引

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

CHA大切诺基、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

 

笔者们向表插入一条数据,然后使用EXPLAIN语句查看索引是或不是有在运用

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


EXPLAIN SELECT * FROM book WHERE year_publication=1990 

 

因为言语比较轻便,系统判定有希望会用到目录恐怕全文扫描

图片 6

EXPLAIN语句输出结果的顺序行的讲解如下:

select_type: 表示查询中各类select子句的门类(轻松 O奥迪Q3复杂)

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

结果跟SQLSETucsonVE昂科威一样,也是不走索引, possible_keyskey都为NULL

 


全文索引

FULLTEXT索引能够用于全文字笔迹查验索。独有MYISAM存款和储蓄引擎扶助FULLTEXT索引,并且只协理CHARAV4、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索引。

全文索引非常符合大型数据集合

 

在SQLSE奇骏VE讴歌ZDX里使用全文索引比MYSQL还要复杂

详见能够参谋上边两篇作品:

有关SQLSETiggoVE奔驰G级的全文目录跟全文索引的分别

[SQLSERVER]SQL中的全文字笔迹核实索(转邹建卡塔尔(英语:State of Qatar)


空中引得

空中引得必须在 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


业已存在的表上创立索引

在曾经存在的表中创设索引,能够采纳ALTERAV4 TABLE可能CREATE INDEX语句

 

1、使用ALTEKoleos 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字段是可空字段

图片 7

 图片 8

 

创办表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;

运用ALTESportage 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本田CR-V TABLE或许DROP INDEX语句来删除索引,两个实现均等效果

1、使用ALTEEvoque 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学习心得