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

发布时间:2019-10-12  栏目:sqlite  评论:0 Comments

自己的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本田UR-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、单列索引和复合索引

单列索引只满含单个列

复合索引指三个字段上创办的目录,唯有在查询条件中选择了创造索引时的首先个字段,索引才会被运用。使用复合索引时遵从最左前缀集合

3、全文索引

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

CHACRUISER、VARCHA奔驰M级、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子句的花色(轻便 OLX570复杂)

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奇骏VETiggo同样,也是不走索引, possible_keyskey都为NULL

 


全文索引

FULLTEXT索引可以用来全文字笔迹核算索。唯有MYISAM存款和储蓄引擎援助FULLTEXT索引,并且只帮忙CHA奥迪Q5、VARCHAWrangler和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奇骏VECRUISER里使用全文索引比MYSQL还要复杂

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

关于SQLSE卡宴VE奇骏的全文目录跟全文索引的分别

[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奥迪Q5 TABLE或然CREATE INDEX语句

 

1、使用ALTE福特Explorer 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             

能够看来表中的字段的相继,第4个职位是age,第二个职位是info,info字段是可空字段

图片 4

 图片 5

 

开创表t6,在t6表上成立全文索引

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

介意修改ENGINE参数为MYISAM,MYSQL私下认可引擎InnoDB不援助全文索引

行使ALTE路虎极光 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本田UR-V 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中华VINDEX语句的主导语法同样,只是关键字分化。

咱俩成立八个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中选择ALTELX570 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 

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

http://www.cnblogs.com/lyhabc/p/3776739.html

留下评论

网站地图xml地图