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

本身的MYSQL学习心得(九) 索引

本身的MYSQL学习心得(一)
简单语法

本身的MYSQL学习心得(二)
数据类型宽度

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

自身的MYSQL学习心得(四)
数据类型

自己的MYSQL学习心得(五)
运算符

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

本人的MYSQL学习心得(七)
查询

本身的MYSQL学习心得(八) 插入 更新
删除

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

本人的MYSQL学习心得(十一)
视图

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

本身的MYSQL学习心得(十三)
权限管理

本身的MYSQL学习心得(十四)
备份和死灰复燃

本人的MYSQL学习心得(十五)
日志

自家的MYSQL学习心得(十六)
优化

本人的MYSQL学习心得(十七)
复制

 

这一篇《我的MYSQL学习心得(九)》将会讲课MYSQL的目录

 

目录是在储存引擎中贯彻的,由此每一个存款和储蓄引擎的目录都不肯定完全相同,并且各个存款和储蓄引擎也不必然协理全体索引类型。

据说存款和储蓄引擎定义每一个表的最大索引数和最大索引长度。全数存款和储蓄引擎帮助每种表至少16个目录,总索引长度至少为256字节。

大多数仓库储存引擎有更高的限定。MYSQL中索引的仓库储存类型有二种:BTREE和HASH,具体和表的存款和储蓄引擎相关;

MYISAM和InnoDB存款和储蓄引擎只辅助BTREE索引;MEMOCRUISERY和HEAP存款和储蓄引擎能够支撑HASH和BTREE索引

 

 

SQL学习指南

mysql将引伏贴作表的可选部件,所以mysql5.1事先只可以采纳alter table add
xx来添加索引,mysql5.1包含5.1从此将create index命令映射到alter table add
index

 

目录的长处:

壹 、通过创造唯一索引,保障数据库表每行数据的唯一性

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

三 、在应用分组和排序进行数据查询时,能够显然收缩查询中分组和排序的时刻

 

目录的毛病:

① 、维护索引须求消耗数据库财富

贰 、索引须求占用磁盘空间,索引文件或许比数据文件更快达到最大文件尺寸

叁 、当对表的数量开始展览增加和删除改的时候,因为要爱戴索引,速度会受到震慑

 

目录的归类

壹 、普通索引和唯一索引

主键索引是一种奇特的绝无仅有索引,差别意有空值

贰 、单列索引和复合索引

单列索引只含有单个列

复合索引指多少个字段上创建的目录,唯有在询问条件中动用了创办索引时的率先个字段,索引才会被选拔。使用复合索引时服从最左前缀集合

叁 、全文索引

全文索引类型为FULLTEXT,在定义索引的列上帮忙值的全文字笔迹检验索,允许在这几个索引列中插入重复值和空值。全文索引可以在

CHAR、VARubiconCHA奥迪Q7、TEXT类型列上成立。MYSQL惟有MYISAM存款和储蓄引擎援助全文索引

四 、空间引得

空间引得是对空间数据类型的字段建立的目录,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子句的项目(不难 O汉兰达复杂)

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揽胜极光一样,也是不走索引, possible_keyskey都为NULL

 


全文索引

FULLTEXT索引能够用于全文字笔迹检验索。只有MYISAM存款和储蓄引擎协理FULLTEXT索引,并且只帮助CHA本田CR-V、VATucsonCHA汉兰达和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索引。

全文索引至极适合大型数据集合

 

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

详尽能够参照下边两篇小说:

关于SQLSE福睿斯VECRUISER的全文目录跟全文索引的分别

[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


一度存在的表上成立索引

在曾经存在的表中创造索引,能够应用ALTE卡宴 TABLE可能CREATE INDEX语句

 

壹 、使用ALTEHaval TABLE语句创制索引,语法如下

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

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

 

与创建表时开立索引的语法差异,在此地运用了ALTEPRADOTABLE和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         

各类参数的意义

一 、TABLE:要创设索引的表

2、Non_unique:索引非唯一,1表示是非唯一索引,0代表唯一索引

3、Key_name:索引的称呼

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

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

6、Sub_part:索引的长度

柒 、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             

能够看到表中的字段的一一,第3个地点是age,第②个岗位是info,info字段是可空字段

图片 4

 图片 5

 

始建表t6,在t6表上创立全文索引

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

留神修改ENGINE参数为MYISAM,MYSQL暗中认可引擎InnoDB不协助全文索引

选拔ALTEHaval 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宝马7系 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                           

 

 

贰 、使用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中应用ALTECRUISER TABLE恐怕DROP INDEX语句来删除索引,两者达成均等效果

一 、使用ALTE奥迪Q5 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约束字段的唯一索引无法被删去!!

 

② 、使用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 

本文版权归小编全部,未经小编同意不得转发。

相关文章