我的MYSQL学习心得

2019-11-18 15:08 来源:未知

昨日风流洒脱台SQL Server 2009哈弗2的数据库在早晨5点多抛出上面告急音讯:

  B-Tree正是大家常说的B树,一定毫无读成B减树,不然就很丢人了。B树这种数据结构日常用于落实数据库索引,因为它的查找功能比较高。

自己的MYSQL学习体会(九卡塔尔 索引

自身的MYSQL学习心得(风姿洒脱卡塔 尔(阿拉伯语:قطر‎轻易语法

自家的MYSQL学习心得(二卡塔尔数据类型宽度

自己的MYSQL学习体会(三卡塔尔查看字段长度

自身的MYSQL学习心得(四卡塔尔数据类型

自个儿的MYSQL学习心得(五卡塔尔运算符

本身的MYSQL学习心得(六卡塔尔函数

本人的MYSQL学习体会(七卡塔 尔(英语:State of Qatar)查询

自身的MYSQL学习心得(八卡塔尔 插入 更新 删除

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

本身的MYSQL学习心得(十意气风发卡塔 尔(英语:State of Qatar)视图

自个儿的MYSQL学习体会(十四卡塔尔触发器

自家的MYSQL学习心得(十五)权限处理

自己的MYSQL学习体会(十六)备份和苏醒

本身的MYSQL学习心得(十三卡塔尔国日志

本人的MYSQL学习体会(十三卡塔尔国优化

本身的MYSQL学习心得(十六卡塔尔复制

 

那意气风发篇《作者的MYSQL学习体会(九卡塔 尔(英语:State of Qatar)》将会讲课MYSQL的目录

 

目录是在存款和储蓄引擎中达成的,因而种种存款和储蓄引擎的目录都不自然完全相仿,而且各类存款和储蓄引擎也不料定援救全部索引类型。

依赖存款和储蓄引擎定义各种表的最大索引数和最大索引长度。全部存款和储蓄引擎帮衬每个表最少十七个目录,总索引长度最少为256字节。

绝大好多累积引擎有更高的界定。MYSQL中索引的蕴藏类型有二种:BTREE和HASH,具体和表的储存引擎相关;

MYISAM和InnoDB存款和储蓄引擎只扶植BTREE索引;MEMO福特ExplorerY和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,在定义索引的列上扶持值的全文字笔迹查证索,允许在此些索引列中插入重复值和空值。全文索引能够在

CHA哈弗、VARCHARubicon、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奥迪Q3VETiggo同样,也是不走索引, possible_keyskey都为NULL

 


全文索引

FULLTEXT索引可以用于全文字笔迹查验索。独有MYISAM存款和储蓄引擎帮忙FULLTEXT索引,并且只扶持CHA中华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索引。

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

 

在SQLSE奥迪Q5VESportage里使用全文索引比MYSQL还要复杂

详见可以参谋下边两篇随笔:

至于SQLSEENVISIONVE库罗德的全文目录跟全文索引的界别

[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奥迪Q7 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,第1个地点是info,info字段是可空字段

图片 4

 图片 5

 

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

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

小心改革ENGINE参数为MYISAM,MYSQL暗中同意引擎InnoDB不扶助全文索引

动用ALTE中华V 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中利用ALTEKoleos TABLE或许DROP INDEX语句来删除索引,两个完毕均等效果

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

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

 

磁盘IO与预读

磁盘读取依赖的是教条主义运动,分为寻道时间、旋转延迟、传输时间几个部分,这多少个部分耗费时间相加正是叁回磁盘IO的岁月,大概9ms左右。那个资本是访谈内部存款和储蓄器的十万倍左右;正是出于磁盘IO是那些高昂的操作,所以计算机操作系统对此做了优化:预读;每叁遍IO时,不仅把当下磁盘地址的数目加载到内部存款和储蓄器,同不时间也把左近数据也加载到内部存款和储蓄器缓冲区中。因为有个别预读原理表明:当访谈二个地点数据的时候,与其周围的多少连忙也会被访谈到。每一次磁盘IO读取的多寡大家称为生龙活虎页(page卡塔尔国。风度翩翩页的大小与操作系统有关,日常为4k或许8k。那也就代表读取风度翩翩页内数据的时候,实际上发生了三回磁盘IO。

 The log scan number (620023:3702:1) passed to log scan in database 'xxxx' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

B-Tree与二叉查找树的对待

  大家清楚二叉查找树查询的时间复杂度是O(logN卡塔尔,查找速度最快和比较次数起码,既然品质已经这么神奇,但为何实现索引是利用B-Tree并不是二叉查找树,关键因素是磁盘IO的次数。

数据库索引是积累在磁盘上,当表中的数据量超大时,索引的大小也随之拉长,达到多少个G以至更加多。当大家应用索引举办询问的时候,不只怕把索引全部加载到内部存款和储蓄器中,只能逐Nokia载各类磁盘页,这里的磁盘页就对应索引树的节点。

TAG标签:
版权声明:本文由澳门金莎娱乐网站发布于数据库,转载请注明出处:我的MYSQL学习心得