MySQL泛泛而谈（3W字）coderxz|图书馆的官网一般都有哪些功能_在线图书馆

MySQL是一个关系型数据库管理系统，由瑞典MySQLAB公司开发，目前属于Oracle公司。

MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL是开源的，所以你不需要支付额外的费用。

MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。

MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Per、PHP、Eifel、Ruby和Tcl等。Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。

MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。

没装过：

已装过

如果报错，可以说缺包：

rpm-ivhMySQL-server-5.5.48-1.linux2.6.i386.rpm--nodeps--force解决了依赖性关系，强制安装

yum-yinstallglibc.i686yum-yinstalllibncurses.so.5yum-yinstalllibaio.so.1yum-yinstalllibz.so.1

查看MySQL安装时创建的MySQL用户和MySQL组

或者可以执行mysqladmin--version命令。

或者ps-ef|grepmysql

启动servicemysqlstart可以报错

因为这个文件的问题：mv/etc/my.cnf/etc/my.cnf.bak

mysql服务启动后，开始连接。

首次连接成功

密码其实对应着mysql中的mysql数据库中的user表：

也可以具体到IP192.168.1.%

自启动mysql服务

为什么mysql会自己读取/etc/my.cnf？

打开mysql.server你就会发现conf=/etc/my.cnf居然是写死的。

在Linux下查看安装目录ps-ef|grepmysql。

查看字符集

默认的是客户端和服务器都用了latin1，所以会乱码。

修改字符集和数据存储路径

[client]#password=your_passwordport=3306socket=/var/lib/mysql/mysql.sockdefault-character-set=utf8[mysqld]port=3306character_set_server=utf8character_set_client=utf8socket=/var/lib/mysql/mysql.sockcollation-server=utf8_general_ci#(注意linux下mysql安装完毕是默认：表名区分大小写；0：区分大小写；1：不分区大小写)lower_case_table_names=1#(设置最大连接数，默认为151，MySQL服务器允许的最大的连接数为16384)max_connections=1000[mysql]default-character-set=utf8重启数据库

重新连接后重新createdatabse并使用新建库，然后再重新建表试试

主要配置文件

二进制日志log-bin

错误日志log-error

查询日志log

数据文件

如何配置

总体概览

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

1.连接层

2.服务层

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

3.引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同这样我们可以根据自己的实际需要进行选取，后面介绍MyISAM和InnoDB。

4.存储层

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

指的是不同语言中与SQL的交互

系统管理和控制工具

管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对MySQLServer的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上MySQLServer的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责MySQLServer与客户端的通信，接受客户端的命令请求，传递Server端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的cache等。

接受用户的SQL命令，并且返回用户需要查询的结果。比如selectfrom就是调用SQLInterface

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在MySQL中我们习惯将所有Client端发送给Server端的命令都称为query，在MySQLServer里面，连接线程接收到客户端的一个Query后，会直接将该query传递给专门负责将各种Query进行分类然后转发给各个对应的处理模块。主要功能：a.将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。b.如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的query（sql语句），根据客户端请求的query语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个query语句的结果(SQL查询顺序优先级从高到低：from-where-groupby-having-select-orderby-limit)他使用的是“选取-投影-联接”策略进行查询。用一个例子就可以理解：selectuid,namefromuserwheregender=1;这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤将这两个查询条件联接起来生成最终查询结果

他的主要功能是将客户端提交给MySQL的Select类query请求的返回结果集cache到内存中，与该query的一个hash值做一个对应。该Query所取数据的基表发生任何数据的变化之后，MySQL会自动使该query的Cache失效。在读写比例非常高的应用系统中，QueryCache对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

存储引擎接口模块可以说是MySQL数据库中最有特色的一点了。目前各种数据库产品中，基本上只有MySQL可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天MySQL可插拔存储引擎的特色。从图中还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。注意：存储引擎是基于表的，而不是数据库。

查看命令：

mysql>showvariableslike'%storage_engine%';+----------------------------------+--------+|Variable_name|Value|+----------------------------------+--------+|default_storage_engine|InnoDB||default_tmp_storage_engine|InnoDB||disabled_storage_engines|||internal_tmp_disk_storage_engine|InnoDB|+----------------------------------+--------+4rowsinset,1warning(0.01sec)MyISAM和InnoDB

阿里巴巴、淘宝用哪个？

1.查询语句写的烂没建索引

2.索引失效

select*fromuserwherename='';createindexidx_user_nameonuser(name);select*fromuserwherename=''andemail='';createindexidx_user_nameonuser(name,email);3.关联查询太多join（设计缺陷或不得已的需求）

4.服务器调优及各个参数设置（缓冲、线程数等）

SELECTDISTINCTFROMJOINONWHEREGROUPBYHAVINGORDERBYLIMIT机读先从from开始读

1FROM2ON3JOIN4WHERE5GROUPBY6HAVING7SELECT8DISTINCT9ORDERBY10LIMIT总结

select*fromAinnerjoinBonA.key=B.keyinner表示需要内查询AB的公共部分

select*fromAleftjoinBonA.key=B.keyleft左表示需要左表的全部AB的公共部分+A的独有

select*fromArightjoinBonA.key=B.keyright右表示需要右表的全部AB的公共部分+B的独有

select*fromAleftjoinBonA.key=B.keywhereB.keyisNULLleft左表示需要左表的全部A所有的部分+非AB的公共部分

select*fromArightjoinBonA.key=B.keywhereB.keyisNULLright右表示需要右表的全部B所有的部分+非AB的公共部分

准备工作，创建以下表，插入新数据：

mysql>select*fromtbl_empainnerjointbl_deptbona.deptId=b.id;+----+------+--------+----+----------+--------+|id|NAME|deptId|id|deptName|locAdd|+----+------+--------+----+----------+--------+|1|z3|1|1|RD|11||2|z4|1|1|RD|11||3|z5|1|1|RD|11||4|w5|2|2|HR|12||5|w6|2|2|HR|12||6|s7|3|3|MK|13||7|s8|4|4|MIS|14|+----+------+--------+----+----------+--------+7rowsinset(0.00sec)2.leftjoin

mysql>select*fromtbl_empaleftjointbl_deptbona.deptId=b.id;+----+------+--------+------+----------+--------+|id|NAME|deptId|id|deptName|locAdd|+----+------+--------+------+----------+--------+|1|z3|1|1|RD|11||2|z4|1|1|RD|11||3|z5|1|1|RD|11||4|w5|2|2|HR|12||5|w6|2|2|HR|12||6|s7|3|3|MK|13||7|s8|4|4|MIS|14||8|s9|51|NULL|NULL|NULL|+----+------+--------+------+----------+--------+8rowsinset(0.00sec)3.rightjoin

4.leftjoin2

MySQL不支持fulljoin，不过可以换种方法表示

mysql>select*fromtbl_empaleftjointbl_deptbona.deptId=b.id->union->select*fromtbl_emparightjointbl_deptbona.deptId=b.id;+------+------+--------+------+----------+--------+|id|NAME|deptId|id|deptName|locAdd|+------+------+--------+------+----------+--------+|1|z3|1|1|RD|11||2|z4|1|1|RD|11||3|z5|1|1|RD|11||4|w5|2|2|HR|12||5|w6|2|2|HR|12||6|s7|3|3|MK|13||7|s8|4|4|MIS|14||8|s9|51|NULL|NULL|NULL||NULL|NULL|NULL|5|FD|15|+------+------+--------+------+----------+--------+9rowsinset(0.00sec)7.fulljoin2

mysql>select*fromtbl_empaleftjointbl_deptbona.deptId=b.idwhereb.idisnull->union->select*fromtbl_emparightjointbl_deptbona.deptId=b.idwherea.deptIdisnull;+------+------+--------+------+----------+--------+|id|NAME|deptId|id|deptName|locAdd|+------+------+--------+------+----------+--------+|8|s9|51|NULL|NULL|NULL||NULL|NULL|NULL|5|FD|15|+------+------+--------+------+----------+--------+2rowsinset(0.00sec)4-索引是什么MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

索引的目的在于提高查询效率，可以类比字典。

如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要逐个逐个寻找，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？

你可以简单理解为“排好序的快速查找数据结构”。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

总结来说，为什么索引查找会快呢？想想使用二叉查找树去查询肯定比顺序遍历要快啊！一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hashindex)等。

一个注意点：

一般在企业中，delete了一条数据，仅仅只是把这一条记录的active修改了false，其中调用service层的delete实际上很多都是update，只能说是逻辑上进行了删除，但是其底层数据库的记录依旧保留着。其原因之一：一个是为了数据分析；另外一个就是索引（数据被删除了索引可能不准确了，其位置不再固定了）。当数据频繁的增加，修改后，这个二叉查找树就不准确了。（干脆把索引重建一遍）。

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。

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

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

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

MySQL索引分类：

MySQL索引结构

BTree索引检索原理

初始化介绍

一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示)，如磁盘块1包含数据项17和35，包含指针P1、P2、P3,

P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。（作为参考比较值）

查找过程

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率天约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

MySQLQueryOptimizer

当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQueryOptimizer时，MySQLQueryOptimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

Explain是什么：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

能干嘛

怎么玩

mysql>select*fromtbl_emp;+----+------+--------+|id|NAME|deptId|+----+------+--------+|1|z3|1||2|z4|1||3|z5|1||4|w5|2||5|w6|2||6|s7|3||7|s8|4||8|s9|51|+----+------+--------+8rowsinset(0.01sec)mysql>explainselect*fromtbl_emp;+----+-------------+---------+------+---------------+------+---------+------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+---------+------+---------------+------+---------+------+------+-------+|1|SIMPLE|tbl_emp|ALL|NULL|NULL|NULL|NULL|8||+----+-------------+---------+------+---------------+------+---------+------+------+-------+1rowinset(0.01sec)②explain之id介绍select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：

id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在

小结

select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

select_type有哪些？

table：显示这一行的数据是关于哪张表的。

访问类型排列

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

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

详细说明

表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。（单表一条记录）

表示通过索引一次就找到了，const用于比较primarykey或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。下面的system是因为符合查询中只会返回一条记录作为临时表。

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

FullIndexScan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。

FullTableScan，将遍历全表以找到匹配的行。

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段火若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

是否使用到了索引；在多个索引存在的情况下，MySQL究竟使用到了哪一个索引。

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则索引和查询的select字段重叠

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

由key_len可知t1表的idx_col1_col2索引被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即‘ac’。

shared库的t2表的col1；

查询中与其它表关联的字段，外键关系建立索引。

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。（每张表有多少行被优化器查询过）

包含不适合在其他列中显示但十分重要的额外信息。

①Usingfilesort九死一生

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"\G表示使用key-value的显示方式进行输出

你建立的索引我只是部分使用到了，查询的时候我只是部分使用到了索引，排序我并没有用到。

对比下面两者，其中上面只用到了col1和col3，中间col2没了；下者使用到了col1、col2、col3；

把建索引比喻成修好的楼梯，col1、col2、col3修了三条路；

上面的部分相当于自己又进行了一次排序的步骤，内部的折腾多了一次；而下面的就是一步步来的，显然下面的效率要高于上面的部分。

②Usingtemporary必死

使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序orderby和分组查询groupby。

orderby和分组查询groupby经常是拖慢SQL的元凶。

上面的部分：建立的复合索引(col1,col2)，但是只用到了col2，没有一楼而直接上二楼，所以需要临时表和文件排序进行处理；

下面的部分：建立的是col1、col2，使用的也是col1、col2;

orderby和分组查询groupby最好和建立的索引顺序一致；

③Usingindex

表示相应的select操作中使用了覆盖索引（CoveringIndex），避免访问了表的数据行，效率不错！

如果同时出现usingwhere，表明索引被用来执行索引键值的查找；

如果没有同时出现usingwhere，表明索引用来读取数据而非执行查找动作。

覆盖索引（CoveringIndex）,一说为索引覆盖。

1、建是索引是复合索引（1，2，3）；

2、没有写select*；刚好查的就是select123或者select12;这就是覆盖索引；

理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为

如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

WhatisaCoveringIndex？

Acoveringindexisanindexthatcontainsallof,andpossiblymore,thecolumnsyouneedforyourquery.

Forinstance,this:

SELECT*FROMtablenameWHEREcriteria

willtypicallyuseindexestospeeduptheresolutionofwhichrowstoretrieveusingcriteria,butthenitwillgotothefulltabletoretrievetherows.

However,iftheindexcontainedthecolumnscolumn1,column2andcolumn3,thenthissql:

SELECTcolumn1,column2FROMtablenameWHEREcriteria

and,providedthatparticularindexcouldbeusedtospeeduptheresolutionofwhichrowstoretrieve,theindexalreadycontainsthevaluesofthecolumnsyou’reinterestedin,soitwon’thavetogotothetabletoretrievetherows,butcanproducetheresultsdirectlyfromtheindex.

Thiscanalsobeusedifyouseethatatypicalqueryuses1-2columnstoresolvewhichrows,andthentypicallyaddsanother1-2columns,itcouldbebeneficialtoappendthoseextracolumns(ifthey’rethesameallover)totheindex,sothatthequeryprocessorcangeteverythingfromtheindexitself.

④Usingwhere

表明使用了where过滤。

⑤Usingjoinbuffer

使用了连接缓存。

⑥impossiblewhere

where子句的值总是false，不能用来获取任何元组。

⑦selecttablesoptimizedaway(用的不多)

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

⑧distinct（用的不多）

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【selectd1.name…】

第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【selectid,namefromt1whereother_column=’’】

第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【selectidfromt3】

第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【selectname,idfromt2】

第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

建表SQL

结论：很显然，type是ALL，即最坏的情况。Extra里还出现了Usingfilesort，也是最坏的情况。优化是必须的。

开始优化

新建索引+删除索引

或者用下面sql创建索引：

ALTERTABLE'article'ADDINDEXidx_article_ccv('category_id,'comments','views');创建后的效果

Extra里还是出现了Usingfilesort，创建这索引作用不大。

如果comments>1换成comments=1，可以让Usingfilesort消失，但不符题目要求。

既然创建这索引作用不大，删了它吧。

mysql>DROPINDEXidx_article_ccvONarticle;QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0缘由

type变成了range，这是可以忍受的。但是extra里使用Usingfilesort仍是无法接受的。

但是我们已经建立了索引，为啥没用呢？

这是因为按照BTree索引的工作原理：

当comments字段在联合索引里处于中间位置时，因comments>1条件是一个范围值(所谓range)，范围以后的索引会失效，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

改进

跟上次创建索引相比，这次不为comments字段创建索引。

mysql>createindexidx_article_cvonarticle(category_id,views);QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0再次explain

可以看到，type变为了ref，Extra中的Usingfilesort也消失了，结果非常理想。

总结：

尽量不要对range类型（一般就是在你的where语句中出现了between、<、>、in等的查询）的查询字段建索引！

新建SQL

创建后的结果：

mysql>select*fromclass;+----+------+|id|card|+----+------+|1|17||2|2||3|18||4|4||5|4||6|8||7|9||8|1||9|18||10|6||11|15||12|15||13|12||14|15||15|18||16|2||17|18||18|5||19|7||20|1||21|2|+----+------+21rowsinset(0.00sec)mysql>select*frombook;+--------+------+|bookid|card|+--------+------+|1|8||2|14||3|3||4|16||5|8||6|12||7|17||8|8||9|10||10|3||11|4||12|12||13|9||14|7||15|6||16|8||17|3||18|11||19|5||20|11|+--------+------+20rowsinset(0.00sec)开始explain分析两个表有主外键，索引应该加在哪张表上？

type都是all，需要优化,且查询的记录数量为21+20！

为book.card创建索引

mysql>ALTERTABLE`book`ADDINDEXY(`card`);QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0再次explain分析

可以看到第二行的type变为了ref，rows也变少了，优化比较明显。这是由左连接特性决定的。LEFTJOIN的特性就是左表全都有；条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要在右表建立索引。

删除为book.card创建索引

mysql>dropindexyonbook;QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:0为class.card创建索引

mysql>ALTERTABLE`class`ADDINDEXY(`card`);QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0再次explain分析

可见右边是我们的关键点，要想优化需要在右表建立索引。

然后我们换用右连接RIGHTJOIN查询

换成左边是我们的关键点，要想优化需要在左表建立索引。

索引两表优化，左连接右表建索引，右连接左表建索引。

mysql>select*fromphone;+---------+------+|phoneid|card|+---------+------+|1|10||2|13||3|17||4|5||5|12||6|7||7|15||8|17||9|17||10|14||11|19||12|13||13|5||14|8||15|2||16|8||17|11||18|14||19|13||20|5|+---------+------+20rowsinset(0.00sec)复用到上一节book，class两表，移除它们原有的索引。

为phone.card和book.card创建新的索引。

mysql>altertable`phone`addindexz(`card`);QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0mysql>altertable`book`addindexy(`card`);QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0explain三表连接三表至少两次关联，索引应该建在哪张表上？

后2行的type都是ref且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

结论

Join语句的优化

尽可能减少Join语句中的NestedLoop的循环总次数(不要join过多或者嵌套)：“永远用小结果集驱动大的结果集”。

优先优化NestedLoop的内层循环，保证Join语句中被驱动表上Join条件字段已经被索引。

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer（在my.cnf文件中）的设置。

索引失效的各种原因：（应该避免）

注意下面的explain我们建立索引的顺序是：(name、age、pos)

上面两个索引失效了。如果查询字段的顺序与索引的建立的顺序不一致，需要满足最佳左前缀法则。

②最佳左前缀法则

带头大哥不能死！中间兄弟不能断！

ref只有一个const，说明上述其实只是使用到了部分索引，而且只是用到了第一个索引，name。

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

存储引擎不能使用索引中范围条件右边的列（我理解为限定复合索引某字段的范围会时索引失效，也就是>，<，between…and…谨慎用在复合索引某字段）。

由age=25变成age>25后，type从ref变成range。

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*

我们最好按需索取，少用select*

mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。

isnull,isnotnull也无法使用索引

Extra打印为ImpossibleWHERE，是因为我们在创建staffs表，设置name字段的属性为notnull。

下面额外演示Extra为ImpossibleWHERE情况。

mysql>EXPLAINSELECT*FROMstaffsWHERE1=1;mysql>EXPLAINSELECT*FROMstaffsWHERE1!=1;⑦like以通配符%开头字符串like以通配符%开头（’%abc…’），mysql索引失效会变成全表扫描的操作。

CREATETABLE`tbl_user`( `id`INT(11)NOTNULLAUTO_INCREMENT, `name`VARCHAR(20)DEFAULTNULL, `age`INT(11)DEFAULTNULL, `email`VARCHAR(20)DEFAULTNULL, PRIMARYKEY(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;INSERTINTOtbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');INSERTINTOtbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');INSERTINTOtbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');INSERTINTOtbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');mysql>select*fromtbl_user;+----+------+------+-----------+|id|name|age|email|+----+------+------+-----------+|1|1aa1|21|a@163.com||2|2bb2|23|b@163.com||3|3cc3|24|c@163.com||4|4dd4|26|d@163.com|+----+------+------+-----------+4rowsinset(0.00sec)创建索引前，先看看以下explain：

没有创建索引，都全表查找。

现在创建索引

mysql>CREATEINDEXidx_user_nameAgeONtbl_user(NAME,age);QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:0再执行上述一系列explain

看得出，用上索引（覆盖索引）

看得出，都用上索引（覆盖索引）

看得出，都没用上索引了，有email字段再，只能全表搜索。

一说为索引覆盖。类比锅和锅盖，查询的字段要小于等于创建的索引(注意：主键id天生自带索引，故而主键字段对与覆盖索引无影响)。

理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

解决like'%字符串%'时索引不被使用的方法？复合索引，然后覆盖索引。

数目字符串不加单引号索引失效。varchar类型一定要加单引号！！！

MySQL底层发送了隐式的类型转换，影响效率。

少用or，用它来连接时会索引失效。

小总结

假设创建的索引：index(a,b,c)

优化总结口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写*；

不等空值还有OR，索引影响要注意；

VAR引号不可丢，SQL优化有诀窍。

问题：我们创建了复合索引idx_test03_c1234(c1、c2、c3、c4)，根据以下SQL分析下索引使用情况？

为什么我建的是1、2、3、4，但是我查询的是4、3、2、1，依旧可以使用到索引呢？MySQL中的第二层中的Optimizer会把MySQL中的命令自动的进行调整和优化。。

限定范围

c3作用在排序而不是查找

上面两个explain的相同。上述两条说明：和c4='a4'这个没啥关系直接在三楼（c3）排序就行，不找了。

orderbyc3换成orderbyc4

mysql>explainselect*fromtest03wherec1='a1'andc2='a2'orderbyc4;用到Usingfilesort。中间的兄弟断了，但是MySQL要交出结果，只好整一次内排序Usingfilesort

mysql>explainselect*fromtest03wherec1='a1'andc5='a5'orderbyc2,c3;只用c1一个字段索引，但是c2、c3用于排序，无filesort。

将orderbyc2,c3换成orderbyc3,c2。

mysql>explainselect*fromtest03wherec1='a1'andc5='a5'orderbyc3,c2;出现了filesort，我们建的索引是1234，它没有按照顺序来，3，2颠倒了。

mysql>explainselect*fromtest03wherec1='a1'andc2='a2'orderbyc2,c3;mysql>explainselect*fromtest03wherec1='a1'andc2='a2'andc5='a5'orderbyc2,c3;用c1、c2两个字段索引，但是c2、c3用于排序，无filesort和c5='a5'没啥关系

上述第二条SQL的orderby违反了创建索引的顺序，那么为什么没有产生usingfilesort呢？

原因是：

对比说明：

mysql>explainselect*fromtest03wherec1='a1'andc2='a2'andc5='a5'orderbyc3,c2;mysql>explainselect*fromtest03wherec1='a1'andc5='a5'orderbyc3,c2;跳过c2，就用c3，就出现Usingfilesort。

groupby表面上是分组，但是分组之前必排序，所以groupby优化原则几乎和orderby相同

定值、范围还是排序，一般orderby是给个范围

groupby基本上都需要进行排序，会有临时表产生(建的索引搞不定，MySQL自己内部再次排序)

假设index(a,b,c)

通常SQL调优过程：（面试问：平时是咋样进行SQL优化的？）

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

for(inti=0;i<5;i++){for(intj=0;j<1000;j++){...}}====================for(inti=0;i<1000;i++){for(intj=0;j<5;j++){...}}类似上述两个for循环，尽量选择上面的那种。数据库最耗时的是多表之间关系的建立。第一种：相当于两张表建立了5次连接；第二种：相当于两张表建立了1000次连接。也就是说被嵌套在里面的应该是小表！（因为是里面嵌套的先执行，相当于被嵌套在里面的SQL是外层for循环）

RBO原理

当B表的数据集小于A表的数据集时，用in优于exists。

select*fromAwhereidin(selectidfromB)等价于:forselectidfromBforselect*fromAwhereA.id=B.id当B表的数据集大于A表的数据集时，用exists优于in。

select*fromAwhereexists(select1fromBwhereB.id=A.id)等价于：先：forselect*fromA后：forselect*fromBwhereB.id=A.id注意：A表与B表的ID字段应建立索引。

SELECT...FROMtableWHEREEXISTS(subquery)该语法可以理解为：先将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。

提示

示例表：

mysql>select*fromtbl_empewheree.deptIdin(selectidfromtbl_deptd);+----+------+--------+|id|NAME|deptId|+----+------+--------+|1|z3|1||2|z4|1||3|z5|1||4|w5|2||5|w6|2||6|s7|3||7|s8|4|+----+------+--------+7rowsinset(0.00sec)mysql>select*fromtbl_empewhereexists(select1fromtbl_deptdwhered.id=e.deptId);+----+------+--------+|id|NAME|deptId|+----+------+--------+|1|z3|1||2|z4|1||3|z5|1||4|w5|2||5|w6|2||6|s7|3||7|s8|4|+----+------+--------+7rowsinset(0.00sec)#'X'就是表示一个常量，是一个常量就行了，反正返回的就是true/falsemysql>select*fromtbl_empewhereexists(select'X'fromtbl_deptdwhered.id=e.deptId);+----+------+--------+|id|NAME|deptId|+----+------+--------+|1|z3|1||2|z4|1||3|z5|1||4|w5|2||5|w6|2||6|s7|3||7|s8|4|+----+------+--------+7rowsinset(0.00sec)③为排序使用索引OrderBy优化ORDERBY子句，尽量使用Index方式排序，避免使用FileSort方式排序。

createtabletblA(#idintprimarykeynotnullauto_increment,ageint,birthtimestampnotnull);insertintotblA(age,birth)values(22,now());insertintotblA(age,birth)values(23,now());insertintotblA(age,birth)values(24,now());createindexidx_A_ageBirthontblA(age,birth);查看验证：

分析SQL重点在于会不会产生UsingFileSort：

最后一种情况：尽管orderby的顺序和创建索引的顺序保持一致，但是由于orderby默认的是升序，而DESC是降序，MySQL建立索引的顺序默认的就是升序，现在需要降序，用不上了，那么MySQL只有产生一次内部排序了。

MySQL支持二种方式的排序，FileSort和lIndex，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

结论：ORDERBY满足两情况，会使用Index方式排序：

如果Orderby查询的列不在索引列上，MySQL的Filesort有两种算法：

1）双路排序

MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和OrderBy列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

2）单路排序

从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序压的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题

在sort_buffer(在my.cnf中有配置缓冲区大小)中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再取取

sort_buffer容量大小，再排……从而多次I/O。（类比于缓冲区大小不够，那只有取多次了）

本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

为什么设置sort_buffer_size、max_length_for_sort_data参数能优化排序？

提高OrderBy的速度：

为排序使用索引

创建复合索引a_b_c(a,b,c)

orderby能使用索引最左前缀

如果WHERE使用素引的最左前缀定义为常量，则orderby能使用索引（不产生FileSort）

不能使用索引进行排序

GroupBy优化

默认情况下，MySQL数据库没有开启慢查询日速，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

mysql>SHOWVARIABLESLIKE'%slow_query_log%';+---------------------+--------------------------+|Variable_name|Value|+---------------------+--------------------------+|slow_query_log|OFF||slow_query_log_file|localhost-slow.log|+---------------------+--------------------------+2rowsinset,1warning(0.00sec)mysql>setglobalslow_query_log=1;QueryOK,0rowsaffected(0.01sec)mysql>SHOWVARIABLESLIKE'%slow_query_log%';+---------------------+--------------------------+|Variable_name|Value|+---------------------+--------------------------+|slow_query_log|ON||slow_query_log_file|localhost-slow.log|+---------------------+--------------------------+2rowsinset,1warning(0.00sec)如果要永久生效，就必须修改配置文件my.cnf(其它系统变量也是如此)

修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件

slow_query_log=1slow_query_log_file=/var/lib/mycoderxz-slow.log关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOWVARIABLESLIKE'long_query_time%';

setgloballong_query_time=3;

需要重新连接或新开一个会话才能看到修改值。

模拟超时SQL：

mysql>SELECTsleep(4);+----------+|sleep(4)|+----------+|0|+----------+1rowinset(4.00sec)日志记录：

查询当前系统中有多少条慢查询记录

#[mysqld]下配置:slow_query_log=1;slow_query_log_file=/var/lib/mysql/atguigu-slow.loglong_query_time=3;log_output=FILE;②日志分析工具mysqldumpslow在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息，mysqldumpslow--help。

工作常用参考

createdatabasebigData;usebigData;CREATETABLEdept( idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT, deptnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0, dnameVARCHAR(20)NOTNULLDEFAULT"", locVARCHAR(13)NOTNULLDEFAULT"")ENGINE=INNODBDEFAULTCHARSET=utf8;CREATETABLEemp(idintunsignedprimarykeyauto_increment,empnomediumintunsignednotnulldefault0,enamevarchar(20)notnulldefault"",jobvarchar(9)notnulldefault"",mgrmediumintunsignednotnulldefault0,hiredatedatenotnull,saldecimal(7,2)notnull,commdecimal(7,2)notnull,deptnomediumintunsignednotnulldefault0)ENGINE=INNODBDEFAULTCHARSET=utf8;②设置参数log_bin_trust_function_creators创建函数，假如报错：ThisfunctionhasnoneofDETERMINISTIC…

由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。

showvariableslike'log_bin_trust_function_creators';+---------------------------------+-------+|Variable_name|Value|+---------------------------------+-------+|log_bin_trust_function_creators|OFF|+---------------------------------+-------+1rowinset(0.02sec)setgloballog_bin_trust_function_creators=1;这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：

1）随机产生字符串

delimiter$$#两个$$表示结束createfunctionrand_string(nint)returnsvarchar(255)begindeclarechars_strvarchar(100)default'abcdefghijklmnopqrstuvwxyz';declarereturn_strvarchar(255)default'';#定义返回值变量declareiintdefault0;whilei

delimiter$$createfunctionrand_num()returnsint(5)begindeclareiintdefault0;seti=floor(100+rand()*10);returni;end$$运行结果

注意：设置autocommit设置为0；

delimiter$$createprocedureinsert_emp(instartint(10),inmax_numint(10))#没有返回值begindeclareiintdefault0;setautocommit=0;#把自动提交设置为0，默认是写一次提交一次，需要批处理只提交一次repeatseti=i+1;insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());untili=max_numendrepeat;commit;#注意要commitend$$创建往dept表中插入数据的存储过程

delimiter$$createprocedureinsert_dept(instartint(10),inmax_numint(10))begindeclareiintdefault0;setautocommit=0;repeatseti=i+1;insertintodept(deptno,dname,loc)values((start+i),rand_string(10),rand_string(8));untili=max_numendrepeat;commit;end$$⑤调用存储过程往dept表中插入数据

mysql>DELIMITER;#恢复为；作为结束符号，上面改为了$$mysql>CALLinsert_dept(100,10);QueryOK,0rowsaffected(0.01sec)往emp表中插入50万数据

mysql>DELIMITER;mysql>CALLinsert_emp(100001,500000);QueryOK,0rowsaffected(27.00sec)运行结果

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

1.是否支持，看看当前的mysql版本是否支持。

2.开启功能，默认是关闭，使用前需要开启。

6.日常开发需要注意的结论（有下面四种情况就很糟糕了）

永远不要在生产环境开启这个功能。

配置文件启用

在mysql的my.cnf中，设置如下：

#开启general_log=1#记录日志文件的路径general_log_file=/path/logfile#输出格式log_output=FILE编码启用：

mysql>setglobalgeneral_log=1;QueryOK,0rowsaffected(0.01sec)mysql>setgloballog_output='TABLE';QueryOK,0rowsaffected(0.00sec)此后，你所编写的sql语句，将会记录到mysql库里的geneial_log表，可以用下面的命令查看：

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

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

类比：网上购物

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？

这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

从对数据操作的类型（读\写）分

从对数据操作的粒度分

表锁（偏读）下面使用的是MyISAM引擎。

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

createtablemylock(idintnotnullprimarykeyauto_increment,namevarchar(20)default'')enginemyisam;#注意这里使用了MyISAM引擎insertintomylock(name)values('a');insertintomylock(name)values('b');insertintomylock(name)values('c');insertintomylock(name)values('d');insertintomylock(name)values('e');select*frommylock;运行结果

mysql>select*frommylock;+----+------+|id|name|+----+------+|1|a||2|b||3|c||4|d||5|e|+----+------+5rowsinset(0.00sec)①手动增加表锁locktable表名字read(write),表名字2read(write),其他;

mysql>locktablemylockread;QueryOK,0rowsaffected(0.00sec)②查看表上加过的锁mysql>showopentables;+--------------------+------------------------------------------------------+--------+-------------+|Database|Table|In_use|Name_locked|+--------------------+------------------------------------------------------+--------+-------------+|performance_schema|events_waits_summary_by_user_by_event_name|0|0||performance_schema|events_waits_summary_global_by_event_name|0|0||performance_schema|events_transactions_summary_global_by_event_name|0|0||performance_schema|replication_connection_status|0|0||mysql|time_zone_leap_second|0|0||mysql|columns_priv|0|0||my|test03|0|0||bigdata|mylock|1|0|...In_use为1表示这个库的mylock表被加了一把锁。

mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)④加读锁——为mylock表加read锁（读阻塞写例子）多个session可模拟分布式的情况：当session1对A表进行加锁，其他的session只能对该表进行读，更新和插入都会被阻塞。

为mylock表加write锁（MylSAM存储引擎的写阻塞读例子）

session1加了写锁之后（自己爱怎么玩就怎么玩），其他的session不能读了！

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

结合上表，所以对MyISAM表进行操作，会有以下情况：

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

mysql>showopentables;可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。

mysql>showstatuslike'table_locks%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Table_locks_immediate|170||Table_locks_waited|0|#越高锁门锁竞争的越激烈+-----------------------+-------+2rowsinset(0.00sec)这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

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

由于行锁支持事务，复习老知识

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性：

并发事务处理带来的问题

更新丢失(LostUpdate)

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题――最后的更新覆盖了由其他事务所做的更新。

例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。

如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读(DirtyReads)

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。

一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。(也许我改错了，而你拿到了错误的数据去干活)

不可重复读(Non-RepeatableReads)

一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性。

幻读(PhantomReads)

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读“。

一句话:事务A读取到了事务B体提交的新增数据，不符合隔离性。

多说一句：幻读和脏读有点类似，

脏读是事务B里面修改了数据，

幻读是事务B里面新增了数据。

事务隔离级别

”脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

常看当前数据库的事务隔离级别：showvariableslike'tx_isolation';

#使用INNODB引擎CREATETABLEtest_innodb_lock(aINT(11),bVARCHAR(16))ENGINE=INNODB;INSERTINTOtest_innodb_lockVALUES(1,'b2');INSERTINTOtest_innodb_lockVALUES(3,'3');INSERTINTOtest_innodb_lockVALUES(4,'4000');INSERTINTOtest_innodb_lockVALUES(5,'5000');INSERTINTOtest_innodb_lockVALUES(6,'6000');INSERTINTOtest_innodb_lockVALUES(7,'7000');INSERTINTOtest_innodb_lockVALUES(8,'8000');INSERTINTOtest_innodb_lockVALUES(9,'9000');INSERTINTOtest_innodb_lockVALUES(1,'b1');CREATEINDEXtest_innodb_a_indONtest_innodb_lock(a);CREATEINDEXtest_innodb_lock_b_indONtest_innodb_lock(b);运行结果

mysql>select*fromtest_innodb_lock;+------+------+|a|b|+------+------+|1|b2||3|3||4|4000||5|5000||6|6000||7|7000||8|8000||9|9000||1|b1|+------+------+9rowsinset(0.00sec)mysql>showindexfromtest_innodb_lock;

行锁定基本演示（两个客户端更新同一行记录）

当两个session修改同一行数据，后来的会被阻塞；当修改的不是同一行数据时，互不干扰（大路朝天各走一边）。

session2还需要重新commit才可刷新最新的数据：原因是因为session2的commit也设置为0了，如果MySQL默认的commit=1的时候，读的时候是能够拿到最新的数据的。

无索引行锁升级为表锁

什么是间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）宁可错杀，不可放过。上述案例中没有a=2的记录，但是MySQL依旧给这一条记录加锁了。。

【危害】

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

面试：如何锁定一行？begin…commit

我希望在操作这条数据的时候，此数据不能被其他线程修改。

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MylISAM相比就会有比较明显的优势了。

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

如何分析行锁定

通过检查lnnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

mysql>showstatuslike'innodb_row_lock%';+-------------------------------+--------+|Variable_name|Value|+-------------------------------+--------+|Innodb_row_lock_current_waits|0||Innodb_row_lock_time|128380||Innodb_row_lock_time_avg|32095||Innodb_row_lock_time_max|50618||Innodb_row_lock_waits|4|+-------------------------------+--------+5rowsinset(0.01sec)对各个状态量的说明如下:

对于这5个状态变量，比较重要的主要是

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划（可以使用showProfile）。

THE END

MySQL泛泛而谈（3W字）coderxz

最新动态

国家数字图书馆新东方课程国家数字图书馆app官方版下载v6.1.7

新闻中心——驱动之家：您身边的电脑专家

安伯托·艾柯访谈巴黎评论

陕图高新馆区丨2024年了，总要来一趟陕图高新馆区，请收下这份图书馆使用攻略

MySQL泛泛而谈（3W字）coderxz

高校办公软件都用哪些?Worktile社区

市场调研的功能范文

共同危险行为疑难问题研究