Mysql高级个人文章

首先就是systemctlstartmysqld出现问题，怎么定位问题呢？

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

优势

1）类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。

2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

1）实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

2）虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree索引，统称为索引。

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1]<=n<=m-1。所以2<=n<=4。当n>4时，中间节点分裂到父节点，两边节点分裂。

插入CNGAHEKQMFWLTZDPRXYS数据为例。

演变过程如下：

1).插入前4个字母CNGA

2).插入H，n>4，中间元素G字母向上分裂到新的节点

3).插入E，K，Q不需要分裂

4).插入M，中间元素M字母向上分裂到父节点G

5).插入F，W，L，T不需要分裂

6).插入Z，中间元素T向上分裂到父节点中

7).插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂

8).最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了，BTREE树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1).n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

2).B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

3).所有的非叶子节点都可以看作是key的索引部分。

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的B+Tree索引结构示意图:

1）单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

2）唯一索引：索引列的值必须唯一，但允许有空值

3）复合索引：即一个索引包含多个列

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

准备环境:

createdatabasedemo_01defaultcharset=utf8mb4;usedemo_01;CREATETABLE`city`(`city_id`int(11)NOTNULLAUTO_INCREMENT,`city_name`varchar(50)NOTNULL,`country_id`int(11)NOTNULL,PRIMARYKEY(`city_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;CREATETABLE`country`(`country_id`int(11)NOTNULLAUTO_INCREMENT,`country_name`varchar(100)NOTNULL,PRIMARYKEY(`country_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;insertinto`city`(`city_id`,`city_name`,`country_id`)values(1,'西安',1);insertinto`city`(`city_id`,`city_name`,`country_id`)values(2,'NewYork',2);insertinto`city`(`city_id`,`city_name`,`country_id`)values(3,'北京',1);insertinto`city`(`city_id`,`city_name`,`country_id`)values(4,'上海',1);insertinto`country`(`country_id`,`country_name`)values(1,'China');insertinto`country`(`country_id`,`country_name`)values(2,'America');insertinto`country`(`country_id`,`country_name`)values(3,'Japan');insertinto`country`(`country_id`,`country_name`)values(4,'UK');2.5.1创建索引语法：

CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name[USINGindex_type]ONtbl_name(index_col_name,...)index_col_name:column_name[(length)][ASC|DESC]示例：为city表中的city_name字段创建索引；

语法：

showindexfromtable_name;示例：查看city表中的索引信息；

DROPINDEXindex_nameONtbl_name;示例：想要删除city表上的索引idx_city_name，可以操作如下：

1).altertabletb_nameaddprimarykey(column_list);该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL2).altertabletb_nameadduniqueindex_name(column_list);这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）3).altertabletb_nameaddindexindex_name(column_list);添加普通索引，索引值可以出现多次。4).altertabletb_nameaddfulltextindex_name(column_list);该语句指定了索引为FULLTEXT，用于全文索引2.6索引设计原则索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

创建视图的语法为：

CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]修改视图的语法为：

ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]选项:WITH[CASCADED|LOCAL]CHECKOPTION决定了是否允许更新数据使记录不再满足视图的条件。LOCAL：只要满足本视图的条件就可以更新。CASCADED：必须满足所有针对该视图的所有视图的条件才可以更新。默认值.示例,创建city_country_view视图,执行如下SQL:

createorreplaceviewcity_country_viewasselectt.*,c.country_namefromcountryc,citytwherec.country_id=t.country_id;查询视图:

从MySQL5.1版本开始，使用SHOWTABLES命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的SHOWVIEWS命令。

同样，在使用SHOWTABLESTATUS命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

如果需要查询某个视图的定义，可以使用SHOWCREATEVIEW命令进行查看：

语法:

DROPVIEW[IFEXISTS]view_name[,view_name]...[RESTRICT|CASCADE]示例,删除视图city_country_view:

DROPVIEWcity_country_view;4.存储过程和函数4.1存储过程和函数概述存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

函数：是一个有返回值的过程；

过程：是一个没有返回值的函数；

CREATEPROCEDUREprocedure_name([proc_parameter[,...]])begin--SQL语句end;示例：

delimiter$createprocedurepro_test1()beginselect'HelloMysql';end$delimiter;知识小贴士

DELIMITER

callprocedure_name();4.4查看存储过程--查询db_name数据库中的所有的存储过程selectnamefrommysql.procwheredb='db_name';--查询存储过程的状态信息showprocedurestatus;--查询某个存储过程的定义showcreateproceduretest.pro_test1\G;4.5删除存储过程DROPPROCEDURE[IFEXISTS]sp_name；4.6语法存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN…END块中。

DECLAREvar_name[,...]type[DEFAULTvalue]示例:

delimiter$createprocedurepro_test2()begindeclarenumintdefault5;selectnum+10;end$delimiter;直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下：

SETvar_name=expr[,var_name=expr]...示例:

DELIMITER$CREATEPROCEDUREpro_test3()BEGINDECLARENAMEVARCHAR(20);SETNAME='MYSQL';SELECTNAME;END$DELIMITER;也可以通过select...into方式进行赋值操作:

DELIMITER$CREATEPROCEDUREpro_test5()BEGINdeclarecountnumint;selectcount(*)intocountnumfromcity;selectcountnum;END$DELIMITER;4.6.2if条件判断语法结构:

ifsearch_conditionthenstatement_list[elseifsearch_conditionthenstatement_list]...[elsestatement_list]endif;需求：

根据定义的身高变量，判定当前身高的所属的身材类型180及以上---------->身材高挑170-180--------->标准身材170以下---------->一般身材示例:

delimiter$createprocedurepro_test6()begindeclareheightintdefault175;declaredescriptionvarchar(50);ifheight>=180thensetdescription='身材高挑';elseifheight>=170andheight<180thensetdescription='标准身材';elsesetdescription='一般身材';endif;selectdescription;end$delimiter;调用结果为:

语法格式:

createprocedureprocedure_name([in/out/inout]参数名参数类型)...IN:该参数可以作为输入，也就是需要调用方传入值,默认OUT:该参数作为输出，也就是该参数可以作为返回值INOUT:既可以作为输入参数，也可以作为输出参数IN-输入

需求:

根据定义的身高变量，判定当前身高的所属的身材类型示例:

delimiter$createprocedurepro_test5(inheightint)begindeclaredescriptionvarchar(50)default'';ifheight>=180thensetdescription='身材高挑';elseifheight>=170andheight<180thensetdescription='标准身材';elsesetdescription='一般身材';endif;selectconcat('身高',height,'对应的身材类型为:',description);end$delimiter;OUT-输出

根据传入的身高变量，获取当前身高的所属的身材类型示例:

createprocedurepro_test5(inheightint,outdescriptionvarchar(100))beginifheight>=180thensetdescription='身材高挑';elseifheight>=170andheight<180thensetdescription='标准身材';elsesetdescription='一般身材';endif;end$调用:

callpro_test5(168,@description)$select@description$小知识

@description:这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@global.sort_buffer_size:这种在变量前加上"@@"符号,叫做系统变量

语法结构:

方式一:CASEcase_valueWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE;方式二:CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE;需求:

给定一个月份,然后计算出所在的季度示例:

delimiter$createprocedurepro_test9(monthint)begindeclareresultvarchar(20);casewhenmonth>=1andmonth<=3thensetresult='第一季度';whenmonth>=4andmonth<=6thensetresult='第二季度';whenmonth>=7andmonth<=9thensetresult='第三季度';whenmonth>=10andmonth<=12thensetresult='第四季度';endcase;selectconcat('您输入的月份为:',month,',该月份为:',result)ascontent;end$delimiter;4.6.5while循环语法结构:

whilesearch_conditiondostatement_listendwhile;需求:

计算从1加到n的值示例:

delimiter$createprocedurepro_test8(nint)begindeclaretotalintdefault0;declarenumintdefault1;whilenum<=ndosettotal=total+num;setnum=num+1;endwhile;selecttotal;end$delimiter;4.6.6repeat结构有条件的循环控制语句,当满足条件的时候退出循环。while是满足条件才执行，repeat是满足条件就退出循环。

注意：UNTILsearch_condition结束条件没有分号

REPEATstatement_listUNTILsearch_conditionENDREPEAT;需求:

delimiter$createprocedurepro_test10(nint)begindeclaretotalintdefault0;repeatsettotal=total+n;setn=n-1;untiln=0//注意：结束条件没有分号endrepeat;selecttotal;end$delimiter;4.6.7loop语句LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现，具体语法如下：

[begin_label:]LOOP//给LOOP循环起别名statement_listENDLOOP[end_label]如果不在statement_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环。

用来从标注的流程构造中退出，通常和BEGIN...END或者循环一起使用。下面是一个使用LOOP和LEAVE的简单例子,退出循环：

OPENcursor_name;//打开游标，才可以进行迭代FETCH光标：

FETCHcursor_nameINTOvar_name[,var_name]...//迭代游标，调用一次，读取游标中一行数据CLOSE光标：

CLOSEcursor_name;//关闭游标示例:

初始化脚本:

CREATEFUNCTIONfunction_name([paramtype...])RETURNStypeBEGIN...END;案例:

定义一个存储函数,请求满足条件的总记录数;

delimiter$createfunctioncount_city(countryIdint)returnsintbegindeclarecnumint;selectcount(*)intocnumfromcitywherecountry_id=countryId;returncnum;end$delimiter;调用:

selectcount_city(1);selectcount_city(2);5.触发器5.1介绍触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。（Oracle既有行级触发器，又有语句级触发器）

createtriggertrigger_namebefore/afterinsert/update/deleteontbl_name[foreachrow]--行级触发器begintrigger_stmt;end;示例

需求

通过触发器记录emp表的数据变更日志,包含增加,修改,删除;首先创建一张日志表:

DELIMITER$createtriggeremp_logs_insert_triggerafterinsertonempforeachrowbegininsertintoemp_logs(id,operation,operate_time,operate_id,operate_params)values(null,'insert',now(),new.id,concat('插入后(id:',new.id,',name:',new.name,',age:',new.age,',salary:',new.salary,')'));end$DELIMITER;创建update型触发器，完成更新数据时的日志记录:

DELIMITER$createtriggeremp_logs_update_triggerafterupdateonempforeachrowbegininsertintoemp_logs(id,operation,operate_time,operate_id,operate_params)values(null,'update',now(),new.id,concat('修改前(id:',old.id,',name:',old.name,',age:',old.age,',salary:',old.salary,'),修改后(id',new.id,'name:',new.name,',age:',new.age,',salary:',new.salary,')'));end$DELIMITER;创建delete行的触发器,完成删除数据时的日志记录:

DELIMITER$createtriggeremp_logs_delete_triggerafterdeleteonempforeachrowbegininsertintoemp_logs(id,operation,operate_time,operate_id,operate_params)values(null,'delete',now(),old.id,concat('删除前(id:',old.id,',name:',old.name,',age:',old.age,',salary:',old.salary,')'));end$DELIMITER;测试：

insertintoemp(id,name,age,salary)values(null,'光明左使',30,3500);insertintoemp(id,name,age,salary)values(null,'光明右使',33,3200);updateempsetage=39whereid=3;deletefromempwhereid=5;5.3删除触发器语法结构:

droptrigger[schema_name.]trigger_name如果没有指定schema_name，默认为当前数据库。

可以通过执行SHOWTRIGGERS命令查看触发器的状态、语法等信息。

语法结构：

showtriggers;Mysql高级-day021.Mysql的体系结构概览

整个MySQLServer由以下组成

1）连接层

2）服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3）引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4）存储层

数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

和大多数的数据库不同,MySQL中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

可以通过指定showengines，来查询当前数据库支持的存储引擎：

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

查看Mysql数据库默认的存储引擎，指令：

showvariableslike'%storage_engine%'；

下面重点介绍几种常用的存储引擎，并对比各个存储引擎之间的区别，如下表所示：

下面我们将重点介绍最长使用的两种存储引擎：InnoDB、MyISAM，另外两种MEMORY、MERGE，了解即可。

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点：

事务控制

createtablegoods_innodb(idintNOTNULLAUTO_INCREMENT,namevarchar(20)NOTNULL,primarykey(id))ENGINE=innodbDEFAULTCHARSET=utf8;starttransaction;insertintogoods_innodb(id,name)values(null,'Meta20');commit;

测试，发现在InnoDB中是存在事务的；

外键约束

MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

下面两张表中，country_innodb是父表，country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id。

createtablecountry_innodb(country_idintNOTNULLAUTO_INCREMENT,country_namevarchar(100)NOTNULL,primarykey(country_id))ENGINE=InnoDBDEFAULTCHARSET=utf8;createtablecity_innodb(city_idintNOTNULLAUTO_INCREMENT,city_namevarchar(50)NOTNULL,country_idintNOTNULL,primarykey(city_id),keyidx_fk_country_id(country_id),CONSTRAINT`fk_city_country`FOREIGNKEY(country_id)REFERENCEScountry_innodb(country_id)ONDELETERESTRICTONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8;--ONDELETERESTRICT:删除主表，如果有关联记录，不删除--ONUPDATECASCADE:更新主表，如果子表有关联记录，更新子表记录insertintocountry_innodbvalues(null,'China'),(null,'America'),(null,'Japan');insertintocity_innodbvalues(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、CASCADE、SETNULL和NOACTION。

RESTRICT和NOACTION相同，是指限制在子表有关联记录的情况下，父表不能更新；

CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；

SETNULL则表示父表在更新或者删除的时候，子表的对应字段被SETNULL。

针对上面创建的两个表，子表的外键指定是ONDELETERESTRICTONUPDATECASCADE方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，主表在更新记录的时候，如果子表有对应记录，则子表对应更新。

表中数据如下图所示：

外键信息可以使用如下两种方式查看：

showcreatetablecity_innodb;

删除country_id为1的country数据：

deletefromcountry_innodbwherecountry_id=1;

更新主表country表的字段country_id:

updatecountry_innodbsetcountry_id=100wherecountry_id=1;

更新后，子表的数据信息为：

存储方式

InnoDB存储表和索引有以下两种方式：

①.使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。

②.使用多表空间存储，这种方式创建的表的表结构仍然存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。

不支持事务

createtablegoods_myisam(idintNOTNULLAUTO_INCREMENT,namevarchar(20)NOTNULL,primarykey(id))ENGINE=myisamDEFAULTCHARSET=utf8;

通过测试，我们发现，在MyISAM存储引擎中，是没有事务控制的；

文件存储方式

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：

.frm(存储表定义)；

.MYD(MYData,存储数据)；

.MYI(MYIndex,存储索引)；

对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST或LAST值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

下面是一个创建和使用MERGE表的示例：

1）.创建3个测试表order_1990,order_1991,order_all,其中order_all是前两个表的MERGE表：

createtableorder_1990(order_idint,order_moneydouble(10,2),order_addressvarchar(50),primarykey(order_id))engine=myisamdefaultcharset=utf8;createtableorder_1991(order_idint,order_moneydouble(10,2),order_addressvarchar(50),primarykey(order_id))engine=myisamdefaultcharset=utf8;createtableorder_all(order_idint,order_moneydouble(10,2),order_addressvarchar(50),primarykey(order_id))engine=mergeunion=(order_1990,order_1991)INSERT_METHOD=LASTdefaultcharset=utf8;2）.分别向两张表中插入记录

insertintoorder_1990values(1,100.0,'北京');insertintoorder_1990values(2,100.0,'上海');insertintoorder_1991values(10,200.0,'北京');insertintoorder_1991values(11,200.0,'上海');3）.查询3张表中的数据。

order_1990中的数据：

order_1991中的数据：

order_all中的数据：

4）.往order_all中插入一条记录，由于在MERGE表定义时，INSERT_METHOD选择的是LAST，那么插入的数据会想最后一张表中插入。

insertintoorder_allvalues(100,10000.0,'西安')；

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

在应用的开发过程中，由于初期数据量小，开发人员写SQL语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，本章将详细介绍在MySQL中优化SQL语句的方法。

当面对一个有SQL性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题SQL并尽快解决问题。

MySQL客户端连接成功后，通过show[session|global]status命令可以提供服务器状态信息。show[session|global]status可以根据需要加上参数“session”或者“global”来显示session级（当前连接）的统计结果和global级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前session中所有统计参数的值：

showstatuslike'Com_______';

showstatuslike'Innodb_rows_%';

Com_xxx表示每个xxx语句执行的次数，我们通常比较关心的是以下几个统计参数。

Com_*:这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_*:这几个参数只是针对InnoDB存储引擎的，累加的算法也略有不同。

可以通过以下两种方式定位执行效率较低的SQL语句。

explainselect*fromtb_itemwhereid=1;

explainselect*fromtb_itemwheretitle='阿尔卡特(OT-979)冰川白联通3G手机3';

explainselect*fromt_roler,t_useru,user_roleurwherer.id=ur.role_idandu.id=ur.user_id;

2）id不同id值越大，优先级越高，越先被执行。

EXPLAINSELECT*FROMt_roleWHEREid=(SELECTrole_idFROMuser_roleWHEREuser_id=(SELECTidFROMt_userWHEREusername='stu1'))

3）id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

EXPLAINSELECT*FROMt_roler,(SELECT*FROMuser_roleurWHEREur.`user_id`='2')aWHEREr.id=a.role_id;

表示SELECT的类型，常见的取值，如下表所示：从上往下效率越来越低

表中derived2表示derived中id为2的表。

展示这一行的数据是关于哪一张表的

type显示的是访问类型，是较为重要的一个指标，可取值为：

结果值从最好到最坏以此是：

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALLsystem>const>eq_ref>ref>range>index>ALL一般来说，我们需要保证查询至少达到range级别，最好达到ref。

possible_keys:显示可能应用在这张表的索引，一个或多个。key：实际使用的索引，如果为NULL，则没有使用索引。key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。3.3.7explain之rows扫描行的数量。

其他的额外的执行计划信息，在该列展示。需要优化的前面两个，保持的是后面usingindex

通过have_profiling参数，能够看到当前MySQL是否支持profile：

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

setprofiling=1;//开启profiling开关；通过profile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下图所示：

showdatabases;usedb01;showtables;select*fromtb_itemwhereid<5;selectcount(*)fromtb_item;执行完上述命令之后，再执行showprofiles指令，来查看SQL语句执行的耗时：

MySQL5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A计划,而不是选择B计划。

打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SEToptimizer_trace="enabled=on",end_markers_in_json=on;setoptimizer_trace_max_mem_size=1000000;执行SQL语句：

select*fromtb_itemwhereid<4;最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：

在我们准备的表结构tb_item中，一共存储了300万记录；

A.根据ID查询

select*fromtb_itemwhereid=1999\G;

查询速度很快，接近0s，主要的原因是因为id为主键，有索引；

2).根据title进行精确查询

select*fromtb_itemwheretitle='iphoneX移动3G32G941'\G;

查看SQL语句的执行计划：

处理方案，针对title字段，创建索引：

createindexidx_item_titleontb_item(title);

索引创建完成之后，再次进行查询：

通过explain，查看执行计划，执行SQL时使用了刚才创建的索引

explainselect*fromtb_sellerwherename='小米科技'andstatus='1'andaddress='北京市'\G;

2).最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。(想象爬楼梯)

匹配最左前缀法则，走索引：

违法最左前缀法则，索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3).范围查询右边的列，不能使用索引。范围查询条件之后的字段，索引失效

根据前面的两个字段name，status查询是走索引的，但是最后一个条件address没有用到索引。

4).不要在索引列上进行运算操作，索引将失效。

5).字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6).尽量使用覆盖索引，避免select*

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select*。

如果查询列，超出索引列，也会降低性能。(索引列没有password，所以需要回表查询)

TIP:usingindex：使用覆盖索引的时候就会出现usingwhere：在查找使用索引的情况下，需要回表去查询所需的数据usingindexcondition：查找使用了索引，但是需要回表查询数据usingindex;usingwhere：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据7).用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：

explainselect*fromtb_sellerwherename='黑马程序员'orcreatetime='2088-01-0112:00:00'\G;

8).以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案：通过覆盖索引来解决

9).如果MySQL评估使用索引比全表更慢，则不使用索引。

10).isNULL，isNOTNULL有时索引失效。

11).in走索引，notin索引失效。

12).单列索引和复合索引。

尽量使用复合索引，而少使用单列索引。

创建复合索引

createindexidx_name_sta_addressontb_seller(name,status,address);就相当于创建了三个索引：namename+statusname+status+address创建单列索引

createindexidx_seller_nameontb_seller(name);createindexidx_seller_statusontb_seller(status);createindexidx_seller_addressontb_seller(address);使用单列索引的时候，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。4.3查看索引使用情况showstatuslike'Handler_read%';--查看当前会话索引使用情况showglobalstatuslike'Handler_read%';--查看全局索引使用情况

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。Handler_read_next：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDERBY...DESC。Handler_read_rnd：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。5.SQL优化5.1大批量插入数据环境准备：

CREATETABLE`tb_user_2`(`id`int(11)NOTNULLAUTO_INCREMENT,`username`varchar(45)NOTNULL,`password`varchar(96)NOTNULL,`name`varchar(45)NOTNULL,`birthday`datetimeDEFAULTNULL,`sex`char(1)DEFAULTNULL,`email`varchar(45)DEFAULTNULL,`phone`varchar(45)DEFAULTNULL,`qq`varchar(32)DEFAULTNULL,`status`varchar(32)NOTNULLCOMMENT'用户状态',`create_time`datetimeNOTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`unique_user_username`(`username`)--唯一性约束)ENGINE=InnoDBDEFAULTCHARSET=utf8;当使用load命令导入数据的时候，适当的设置可以提高导入的效率。

对于InnoDB类型的表，有以下几种方式可以提高导入的效率：

1）主键顺序插入

脚本文件介绍:sql1.log---->主键有序sql2.log---->主键无序插入ID顺序排列数据：

插入ID无序排列数据：

注意出现：【LOADDATA语法上传数据】

2）关闭唯一性校验

3）手动提交事务

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

2).第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为usingindex，不需要额外排序，操作效率高。

多字段排序

通过创建合适的索引，能够减少Filesort的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快Filesort的排序操作。对于Filesort，MySQL有两种排序算法：

1）两次扫描算法：MySQL4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sortbuffer中排序，如果sortbuffer不够，则在临时表temporarytable中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sortbuffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。

由于GROUPBY实际上也同样会进行排序操作，而且与ORDERBY相比，GROUPBY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUPBY的实现过程中，与ORDERBY一样也可以利用到索引。

如果查询包含groupby但是用户想要避免排序结果的消耗，则可以执行orderbynull禁止排序。如下：

dropindexidx_emp_age_salaryonemp;explainselectage,count(*)fromempgroupbyage;

优化后

explainselectage,count(*)fromempgroupbyageorderbynull;

创建索引：

createindexidx_emp_age_salaryonemp(age,salary)；

示例，查找有角色的所有的用户信息:

explainselect*fromt_userwhereidin(selectuser_idfromuser_role);执行计划为:

优化后:

explainselect*fromt_useru,user_roleurwhereu.id=ur.user_id;

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

获取emp表中的所有的索引：

示例：

explainselect*fromempwhereid=1orage=30;

建议使用union替换or：

我们来比较下重要指标，发现主要差别是type和ref这两项

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALLUNION语句的type值为ref，OR语句的type值为range，可以看到这是一个很明显的差距

UNION语句的ref值为const，OR语句的type值为null，const表示是常量值引用，非常快

这两项的差距就说明了UNION要优于OR。

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。(局限性：主键不能断层)

在查询语句中表名的后面，添加useindex来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

createindexidx_seller_nameontb_seller(name);

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用ignoreindex作为hint。

explainselect*fromtb_sellerignoreindex(idx_seller_name)wherename='小米科技';

为强制MySQL使用一个特定的索引，可在查询中使用forceindex作为hint。

createindexidx_seller_addressontb_seller(address);

前面章节，我们介绍了很多数据库的优化措施。但是在实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化，来降低数据库的访问压力。

对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有必要建立数据库连接池，以提高访问的性能。

在编写应用代码时，需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求。

比如，需要获取书籍的id和name字段，则查询如下：

selectid,namefromtb_book;之后，在业务逻辑中有需要获取到书籍状态信息，则查询如下：

selectid,statusfromtb_book;这样，就需要向数据库提交两次请求，数据库就要做两次查询操作。其实完全可以用一条SQL语句得到想要的结果。

selectid,name,statusfromtb_book;1.2.2增加cache层在应用中，我们可以在应用中增加缓存层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。

因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储，或者使用框架(Mybatis,Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据。

负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来降低单台服务器的负载，达到优化的效果。

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。

MySQL的查询缓存默认是关闭的，需要手动配置参数query_cache_type，来开启查询缓存。query_cache_type该参数的可取值有三个：

在/usr/my.cnf配置中，增加以下配置：

配置完毕之后，重启服务既可生效；

SQL_CACHE:如果查询结果是可缓存的，并且query_cache_type系统变量的值为ON或DEMAND，则缓存查询结果。

SQL_NO_CACHE:服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：

SELECTSQL_CACHEid,nameFROMcustomer;SELECTSQL_NO_CACHEid,nameFROMcustomer;2.6查询缓存失效的情况1）SQL语句不一致的情况，要想命中查询缓存，查询的SQL语句必须一致。

SQL1:selectcount(*)fromtb_item;SQL2:Selectcount(*)fromtb_item;2）当查询语句中有一些不确定的时，则不会缓存。如：now(),current_date(),curdate(),curtime(),rand(),uuid(),user(),database()。

SQL1:select*fromtb_itemwhereupdatetime

select'A';4）查询mysql，information_schema或performance_schema数据库中的表时，不会走查询缓存。(默认的系统数据库)

select*frominformation_schema.engines;5）在存储的函数，触发器或事件的主体内执行的查询。

6）如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变INSERT，UPDATE，DELETE，TRUNCATETABLE，ALTERTABLE，DROPTABLE，或DROPDATABASE。

1）将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。

2）MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。

3）排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。

在/usr/my.cnf中做如下配置：

key_buffer_size=512Mread_buffer_size如果需要经常顺序扫描myisam表，可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

对于需要做排序的myisam表的查询，如带有orderby子句的sql，适当增加read_rnd_buffer_size的值，可以改善此类的sql性能。但需要注意的是read_rnd_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

该变量决定了innodb存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O就越少，性能也就越高。

innodb_buffer_pool_size=512Minnodb_log_buffer_size决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M4.Mysql并发参数调整从实现上来说，MySQLServer是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括max_connections、back_log、thread_cache_size、table_open_cahce。

采用max_connections控制允许连接到MySQL数据库的最大数量，默认值是151。如果状态变量connection_errors_max_connections不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections的值。

back_log参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6版本之前默认值为50，之后的版本默认为50+（max_connections/5），但最大不超过900。

该参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开1个表缓存。该参数的值应该根据设置的最大连接数max_connections以及每个连接执行关联查询中涉及的表的最大数量来设定：

max_connectionsxN；

为了加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制MySQL缓存客户服务线程的数量。(在MySQLServer端设置了线程池的大小)

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

从对数据操作的粒度分：

1）表锁：操作时，会锁定整个表。

2）行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2）写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

MySQL这3种锁的特性可大致归纳如下：

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

显示加表锁语法：

加读锁：locktabletable_nameread;加写锁：locktabletable_namewrite；5.4.2读锁案例准备环境

createdatabasedemo_03defaultcharset=utf8mb4;usedemo_03;CREATETABLE`tb_book`(`id`INT(11)auto_increment,`name`VARCHAR(50)DEFAULTNULL,`publish_time`DATEDEFAULTNULL,`status`CHAR(1)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=myisamDEFAULTCHARSET=utf8;INSERTINTOtb_book(id,name,publish_time,status)VALUES(NULL,'java编程思想','2088-08-01','1');INSERTINTOtb_book(id,name,publish_time,status)VALUES(NULL,'solr编程思想','2088-08-08','0');CREATETABLE`tb_user`(`id`INT(11)auto_increment,`name`VARCHAR(50)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=myisamDEFAULTCHARSET=utf8;INSERTINTOtb_user(id,name)VALUES(NULL,'令狐冲');INSERTINTOtb_user(id,name)VALUES(NULL,'田伯光');客户端一：

1）获得tb_book表的读锁

locktabletb_bookread;客户端一：

2）执行查询操作

select*fromtb_book;

可以正常执行，查询出数据。

客户端二：

3）执行查询操作

也可以正常执行，查询出数据。

客户端一：

4）查询未锁定的表

selectnamefromtb_seller;

客户端一查询未锁定的表失败。因为持有了一张tb_book的读锁，并未释放锁。

5）查询未锁定的表

可以正常查询出未锁定的表；

6）执行插入操作

insertintotb_bookvalues(null,'Mysql高级','2088-01-01','1');

执行插入，直接报错，由于当前tb_book获得的是读锁，不能执行更新操作。只能读

7）执行插入操作

当在客户端一中释放锁指令unlocktables后，客户端二中的inesrt语句，立即执行；

客户端一:

1）获得tb_book表的写锁

locktabletb_bookwrite;客户端一:

查询操作执行成功；加了写锁可以读。

3）执行更新操作

updatetb_booksetname='java编程思想（第二版）'whereid=1;

更新操作执行成功；（加了写锁当然可以写）

客户端二:

4）执行查询操作

当在客户端一中释放锁指令unlocktables后，客户端二中的select语句，立即执行；（因为客户端一线程加的是写锁，写锁是排他锁，会阻断其他线程的读和写操作）

锁模式的相互兼容性如表中所示：

由上表可见：

1）对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

2）对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

showopentables；

In_user:表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

showstatuslike'Table_locks%';

Table_locks_immediate：指的是能够立即获得表级锁的次数，每立即获取锁，值加1。

Table_locks_waited：指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

并发事务处理带来的问题

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。

数据库的隔离级别有4个，由低到高依次为Readuncommitted、Readcommitted、Repeatableread、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

备注：√代表可能出现，×代表不会出现。

Mysql的数据库的默认隔离级别为Repeatableread，查看方式：

showvariableslike'tx_isolation';

InnoDB实现了以下两种类型的行锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT*FROMtable_nameWHERE...LOCKINSHAREMODE排他锁（X)：SELECT*FROMtable_nameWHERE...FORUPDATE（悲观锁）悲观锁和乐观锁

悲观锁：事务必须排队执行。数据锁住了，不允许并发。（行级锁：select后面添加forupdate）

乐观锁：支持并发，事务也不需要排队，只不过需要一个版本号。

怎样避免间隙锁呢？

在更新的时候，或者对数据行进行加锁的时候，尽量去缩小条件，使得间隙数据尽量的少，最大程度避免间隙锁的存在。

showstatuslike'innodb_row_lock%';

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

编写顺序

SELECTDISTINCTFROMJOINONWHEREGROUPBYHAVINGORDERBYLIMIT执行顺序

FROMONJOINWHEREGROUPBYHAVINGSELECTDISTINCTORDERBYLIMIT6.2正则表达式使用正则表达式（RegularExpression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。

select*fromempwherenameregexp'^T';select*fromempwherenameregexp'2$';select*fromempwherenameregexp'[uvw]';6.3MySQL常用函数数字函数

字符串函数

日期函数

聚合函数

该mysql不是指mysql服务，而是指mysql的客户端工具。

mysql[options][database]1.1.1连接选项参数：-u,--user=name指定用户名-p,--password[=name]指定密码-h,--host=name指定服务器IP或域名-P,--port=#指定连接端口示例：mysql-h127.0.0.1-P3306-uroot-pmysql-h127.0.0.1-P3306-uroot-p2143//都不加空格也可以1.1.2执行选项-e,--execute=name执行SQL语句并退出此选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。

示例：mysql-uroot-p2143db01-e"select*fromtb_book";

mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

可以通过：mysqladmin--help指令查看帮助文档

示例：mysqladmin-uroot-p2143create'test01';mysqladmin-uroot-p2143drop'test01';mysqladmin-uroot-p2143version;1.3mysqlbinlog由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具。

mysqldump[options]db_name[tables]mysqldump[options]--database/-Bdb1[db2db3...]mysqldump[options]--all-databases/-A1.4.1连接选项参数：-u,--user=name指定用户名-p,--password[=name]指定密码-h,--host=name指定服务器IP或域名-P,--port=#指定连接端口1.4.2输出内容选项参数：--add-drop-database在每个数据库创建语句前加上Dropdatabase语句--add-drop-table在每个表创建语句前加上Droptable语句,默认开启;不开启(--skip-add-drop-table)-n,--no-create-db不包含数据库的创建语句-t,--no-create-info不包含数据表的创建语句-d--no-data不包含数据-T,--tab=name自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件，相当于selectintooutfile示例：mysqldump-uroot-p2143db01tb_book--add-drop-database--add-drop-table>amysqldump-uroot-p2143-T/tmptestcity--将test数据库中city表输出到tem目录下，生成两个文件，一个.sql文件一个.txt文件

mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件。

mysqlimport[options]db_nametextfile1[textfile2...]示例：

mysqlimport-uroot-p2143test/tmp/city.txt如果需要导入sql文件,可以使用mysql中的source指令:

source/root/tb_book.sql1.6mysqlshowmysqlshow客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

mysqlshow[options][db_name[table_name[col_name]]]参数：

--count显示数据库及表的统计信息（数据库，表均可以不指定）-i显示指定数据库或者指定表的状态信息示例：

#查询每个数据库的表的数量及表中记录的数量mysqlshow-uroot-p2143--count#查询test库中每个表中的字段书，及行数mysqlshow-uroot-p2143test--count#查询test库中book表的详细情况mysqlshow-uroot-p2143testbook--count#如果将以上count换成i，则显示详细信息2.Mysql日志在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL也不例外，在MySQL中，有4种不同的日志，分别是错误日志、二进制日志（BINLOG日志）、查询日志和慢查询日志，这些日志记录着数据库在不同方面的踪迹。

该日志是默认开启的，默认存放目录为mysql的数据目录（var/lib/mysql）,默认的日志文件名为hostname.err（hostname是主机名）。

查看日志位置指令：

showvariableslike'log_error%';

查看日志内容：

tail-f/var/lib/mysql/xaxh-server.err

二进制日志，默认情况下是没有开启的，需要到MySQL的配置文件中开启，并配置MySQL日志的格式。

配置文件位置:/usr/my.cnf

日志存放位置:配置时，给定了文件名但是没有指定路径，日志默认写入Mysql的数据目录。

#配置开启binlog日志，日志的文件前缀为mysqlbin----->生成的文件名如:mysqlbin.000001,mysqlbin.000002log_bin=mysqlbin#配置二进制日志的格式binlog_format=STATEMENT2.2.2日志格式STATEMENT

该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

ROW

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句：updatetb_booksetstatus='1',如果是STATEMENT日志格式，在日志中会记录一行SQL文件；如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中会记录每一行的数据变更。

MIXED

这是目前MySQL默认的日志格式，即混合了STATEMENT和ROW两种格式。默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED格式能尽量利用两种模式的优点，而避开他们的缺点。

由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog工具来查看，语法如下：

mysqlbinloglog-file；查看STATEMENT格式日志

执行插入语句：

insertintotb_bookvalues(null,'Lucene','2088-05-01','0');查看日志文件：

mysqlbin.index:该文件是日志索引文件，记录日志的文件名；

mysqlbing.000001：日志文件

mysqlbinlogmysqlbing.000001；

查看ROW格式日志

配置:

#配置开启binlog日志，日志的文件前缀为mysqlbin----->生成的文件名如:mysqlbin.000001,mysqlbin.000002log_bin=mysqlbin#配置二进制日志的格式binlog_format=ROW插入数据:

insertintotb_bookvalues(null,'SpringCloud实战','2088-05-05','0');如果日志格式是ROW,直接查看数据,是查看不懂的;可以在mysqlbinlog后面加上参数-vv

mysqlbinlog-vvmysqlbin.000002

方式一

通过ResetMaster指令删除全部binlog日志，删除之后，日志编号，将从xxxx.000001重新开始。

查询之前，先查询下日志文件：

执行删除日志指令：

ResetMaster执行之后，查看日志文件：

方式二

执行指令purgemasterlogsto'mysqlbin.******'，该命令将删除******编号之前的所有日志。

方式三

执行指令purgemasterlogsbefore'yyyy-mm-ddhh24:mi:ss'，该命令将删除日志为"yyyy-mm-ddhh24:mi:ss"之前产生的所有日志。

方式四

设置参数--expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样将有利于减少DBA管理日志的工作量。

配置如下：

默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：

#该选项用来开启查询日志，可选值：0或者1；0代表关闭，1代表开启general_log=1#设置日志的文件名，如果没有指定，默认的文件名为host_name.loggeneral_log_file=file_name在mysql的配置文件/usr/my.cnf中配置如下内容：

配置完毕之后，在数据库执行以下操作：

select*fromtb_book;select*fromtb_bookwhereid=1;updatetb_booksetname='lucene入门指南'whereid=5;select*fromtb_bookwhereid<8;执行完毕之后，再次来查询日志文件：

慢查询日志默认是关闭的。可以通过两个参数来控制慢查询日志：

1）查询long_query_time的值。

selectid,title,price,num,statusfromtb_itemwhereid=1;

select*fromtb_itemwheretitlelike'%阿尔卡特(OT-927)炭黑联通3G手机双卡双待165454%';

该SQL语句，执行时长为26.77s，超过10s，所以会记录在慢查询日志文件中。

3）查看慢查询日志文件

直接通过cat指令查询该日志文件：

如果慢查询日志内容很多，直接查看文件，比较麻烦，这个时候可以借助于mysql自带的mysqldumpslow工具，来对慢查询日志进行分类汇总。

复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL的主从复制原理如下。

从上层来看，复制分成三步：

MySQL复制的有点主要包含以下三个方面：

1）在master的配置文件（/usr/my.cnf）中，配置如下内容：

#mysql服务ID,保证整个集群环境中唯一server-id=1#mysqlbinlog日志的存储路径和文件名log-bin=/var/lib/mysql/mysqlbin#错误日志,默认已经开启#log-err#mysql的安装目录#basedir#mysql的临时目录#tmpdir#mysql的数据存放目录#datadir#是否只读,1代表只读,0代表读写read-only=0#忽略的数据,指不需要同步的数据库binlog-ignore-db=mysql#指定同步的数据库#binlog-do-db=db012）执行完毕之后，需要重启Mysql：

grantreplicationslaveon*.*to'itcast'@'192.168.192.131'identifiedby'itcast';flushprivileges;--主服务器的ip是192.168.192.130--从服务器的ip是192.168.192.1314）查看master状态：

showmasterstatus;

字段含义：

File:从哪个日志文件开始推送日志文件Position：从哪个位置开始推送日志Binlog_Ignore_DB:指定不需要同步的数据库3.4.2slave1）在slave端配置文件中，配置如下内容：

#mysql服务端ID,唯一server-id=2#指定binlog日志log-bin=/var/lib/mysql/mysqlbin2）执行完毕之后，需要重启Mysql：

servicemysqlrestart；3）执行如下指令：

changemastertomaster_host='192.168.192.130',master_user='itcast',master_password='itcast',master_log_file='mysqlbin.000001',master_log_pos=413;--指定主节点的ip，账户，密码，二进制日志文件名称，以及从哪一行开始同步指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。

4）开启同步操作

startslave;showslavestatus\G;

5）停止同步操作

stopslave;3.4.3验证同步操作1）在主库中创建数据库，创建表，并插入数据：

createdatabasedb01;userdb01;createtableuser(idint(11)notnullauto_increment,namevarchar(50)notnull,sexvarchar(1),primarykey(id))engine=innodbdefaultcharset=utf8;insertintouser(id,name,sex)values(null,'Tom','1');insertintouser(id,name,sex)values(null,'Trigger','0');insertintouser(id,name,sex)values(null,'Dawn','1');2）在从库中查询数据，进行验证：

在从库中，可以查看到刚才创建的数据库：

在该数据库中，查询user表中的数据：

记录系统中的日志信息，可以通过Spring框架的AOP来实现。具体的请求处理流程，如下：

通过自定义注解，来标示方法需不需要进行记录日志，如果该方法在访问时需要记录日志，则在该方法上标示该注解既可。