mysql高级及调优

1.mysql架构

1.1.主要文件

​ 1.二进制日志logbin ： 存放了所有的操作记录 用于主从复制和备份恢复，相当于redis中的AOF 配置中默认关闭

​ 2.错误日志 logerror ：记录严重的警告和错误信息，启动和关闭时的信息，默认关闭

​ 3.慢查询日志 : 记录查询的sql语句，从而对执行时间较长的sql语句进行分析,默认关闭，因为记录日志影响整体性能.

1.2.数据文件

​ window系统和linux系统存放存在差异

​ 对myisam引擎 分为 frm文件(存放表结构) myd文件(存放表数据) myi文件(存放表索引)

​ 对innodb引擎 所有文件都存在了/usr/share/mysql/ibdata1 而frm文件存放在库同名的包下

​

1.3. 配置文件

​ 对于配置文件 windows下是my.ini linux下是my.cnf

​ 大小写问题 SHOW VARIABLES LIKE ‘%lower_case_table_names%’

​ windows系统默认大小写不敏感，但是linux系统是大小写敏感的
​ 默认为0，大小写敏感。
​ 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和DB进行查找。
​ 设置2，创建的表和DB依据语句上格式存放，凡是查找都是转换为小写进行。

​ 设置变量常采用 set lower_case_table_names = 1； 的方式，但此变量是只读权限，所以需要在配置文件中改。
​ 当想设置为大小写不敏感时，要在my.cnf这个配置文件 [mysqld] 中加入 lower_case_table_names = 1 ，然后重启服务器。
​ 但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则更改后将找不到数据库名。
​ 在进行数据库参数设置之前，需要掌握这个参数带来的影响，切不可盲目设置

sql mode

​ 在默认下是空值，就会允许一些非法的操作

​ 常用值如下

ONLY_FULL_GROUP_BY：
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE：
在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：
禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

1.4. 整体架构

​ 插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层

2.1 Management Serveices & Utilities： 系统管理和控制工具
2.2 SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解： select uid,name from user where gender= 1;
优化器来决定先投影还是先过滤。

2.5 Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
缓存是负责读，缓冲负责写。

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

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

1.5.查询流程

首先，mysql的查询流程大致是：
mysql客户端通过协议与mysql服务器建连接(tcp连接socket通信)，由连接器管理连接和权限校验(user表)，发送查询语句，先检查查询缓存，如果命中(一模一样的sql才能命中)，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。(适用于静态表即数据长时间才改动一次，这样缓存命中的几率才会高，数据经常变动，则缓存功能鸡肋。如果对表数据进行更新，则会清空缓存区中该查询语句的记录，则再次查询会重新把查询结果写入缓存。在引擎层会有buffer pool ，LRU缓存热点数据)

语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。

查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。在表里有多个索引的时候，决定使用哪个索引，或者在一个语句里有多表关联(join)的时候,决定各个表的连接顺序。

执行器调用引擎的接口，取满足条件的每一行数据，放入到结果集中。

bin-log归档

sql语句的每一次执行的逻辑记录在bin-log中，binlog是server层实现的二进制日志，记录cud操作。开启这个功能，如果误删库，则可通过这个文件进行归档

1.6.存储引擎

1 如何用命令查看
#看你的mysql现在已提供什么存储引擎:
mysql> show engines;

#看你的mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%’;

1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况

2、MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Archive引擎
Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。适合低访问量大数据等情况。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

4、Blackhole引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5、CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

6、Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis)

7、Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好,

阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。
AliSql+AliRedis

2.索引优化

查询数据过多，使用了太多的join(用A表的每一条数据扫描匹配B表的数据),没有使用到索引，或者索引失效等待原因都会导致一条慢sql的产生.

2.1 sql执行顺序

1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

2、JOIN table2 所以先是确定表，再确定关联条件

3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

4、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3

5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

6、HAVING 对分组后的记录进行聚合 产生中间表Temp5

7、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6

8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9

2.2 索引是什么

​ 索引是帮助mysql高效获取数据的排好序的数据结构

对列加索引，即将该列用一种数据结构来表示，二叉搜索树，每个节点key存索引列某一行的值，value存该行数据的磁盘地址，由于二叉搜索树的排序特性，若sql中where条件为i = 99 则查找时，先和第一个节点比较，若大于改值则向右子树查找，这样就加快了查找效率。

但是二叉搜索树有一定的局限，例如对自增列或者有序列增加索引，则该二叉搜索树将会退变成链表，则时间复杂度又变成o(n)

于是有了红黑树的自平衡特性来解决退化成链表，但是由于红黑树可能造成树的高度很深，即纵向深，则从横向进行优化，叶节点拥有相同的深度，单一节点不再只存放一个数据，而是多个数据，数据索引从左向右递增排列，即B树

由于B树的查找并不稳定（最好的情况是查询根节点，最坏查询叶子节点）。而B+树每一次查找都是稳定的。

比起B树，B+树 ①IO次数更少 ②查询性能很稳定 ③范围查询更简便

但是mysql索引底层用的是B+树，非叶子节点不存储data，只存储索引(叶子节点和非叶子节点索引冗余)，可以放更多的索引，叶子节点包含所有的索引字段，叶子节点用指针连接，提高区间访问的能力

mysql中innodb_page_size的值即为B+树节点的大小 默认为16kb 则对于一个非叶子节点来说存储的是多对 索引 和指向下一节点的指针，索引按bigint类型算，指针为6B 则一个节点可存放1170左右对索引和指针.

若该树层次为三层 第一层存1170 第二层存1170 第三层叶子节点层存索引加data data按1k算 则叶子节点可存18个 则一颗树最少可存两千万数据 所以如果使用索引能大大加速语句查询速度，如果不走索引，对于千万数据的表，全表扫描，速度很慢。

mysql会把B+树的根节点常驻内存，其它节点都要进行磁盘io读取.

聚簇索引和非聚簇索引即·叶子节点是否包含了完整的数据记录。

对于innodb则其索引就是聚簇索引 因为它的叶子节点的data部分就是该索引行的数据，而myisam其叶子节点的data是指针

对于myisam引擎 表的数据分为frm myd myi文件 即data和index分开存放，则查询时，会对两个文件进行io

对于innodb引擎 表的数据文件分为frm ibd文件 即data和index都存在一个ibd文件，查询时，只会对一个文件进行io，且生成的ibd文件默认会按主键生成索引文件，若你表中无主键，则会帮你挑选一列无重复数据列或者加隐藏的一列构成B+树 存放在ibd文件中。

hash索引

类似java中的hashmap 由于hash算法 查找是o(1)的。hash冲突是一个问题，但是根本问题在于不能范围查询

面试题 ： 1.为什么innodb必须有主键，并且推荐用整型的自增主键

innodb的文件存储分为frm文件和ibd文件 ibd文件就是索引和数据的文件 即已经按照主键索引排好了的B树 所以需要主键

推荐使用整型是 一是为了节约索引的大小 二是提高比较的速度 因为字符串比较没有整型比较快

使用自增的原因是 B+树的叶子节点的索引从左到右递增，且叶子节点之间通过指针连接，如果按自增主键进行插入数据，则新插入的值只需要追加在最后面，而如果不按自增主键，则新索引的值按大小可能插入到某一个中间节点的内部，因为B+树的节点有多个索引，而为了保证顺序，则就必须对其结构进行调整从而插入新索引，则就会导致性能损耗，所以需要使用自增的主键。

2. 为什么非主键索引结构叶子节点存储的是主键值？

一致性和节省存储空间

首先innodb的表建表需要创建主键，则生成的ibd文件里数据就按主键排序好的B+树，则叶子节点存放的就是主键索引行其它列的数据

如果没有创建主键，则会默认创建一个隐藏的列索引，形成一个B+树 存放在ibd文件中。

如果创建了一个非主键的索引，则形成的B+树中，该树的索引按照该非主键列进行排序，而data部分存放的是该非主键列所在行的主键值，再用该值，去主键索引形成的B+树进行查找。存储主键值，既因为节约空间，更为了该索引树和主键索引树的一致性。

3.联合索引(最左列原则)

联合索引和普通索引类似，先按照第一个索引列排序，再按照第二个索引列进行排序。就是说在第一索引排好序的情况下，对第二索引进行排序。 对所有叶子节点来说 第一索引是排好序的，但是对于所有叶子节点，第二索引并不是有序的，而是在第一索引相同的那些节点其第二索引才排好序。所以对于只有第二个索引的查询语句，对于所有叶子节点，单看第二索引是无序的，无序就会导致全表扫描，则索引失效。

2.3 优化原则

1.最左前缀法则

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

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

3.存储引擎不能使用索引中范围条件右边的列

范围查询后索引列失效

EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age > 22 AND position =‘mager’;

会用到两个索引，第三个索引没用上。

4.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

5.mysql在使用不等于（！=或者<>），not in ，not exists的时候无法使用索引会导致全表扫描

< 小于、 > 大于、 <=、>=这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

6.is null,is not null 一般情况下也无法使用索引

7.like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作

解决办法：1.尽量使用’abc%’ 走索引

​ 2.select里覆盖索引，则查询走索引

8.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评

估是否使用索引，详见范围查询优化

9.范围查询优化

explain select * from employees where age >=1 and age <=2000;

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是

由于单次数据量查询过大导致优化器最终选择不走索引

优化方法：可以将大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;

explain select * from employees where age >=1001 and age <=2000;

优化实战

1、联合索引第一个字段用范围不会走索引

​ 联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不

如就全表扫描

​ 可采用覆盖索引进行优化，因为此时查询走索引树，且要查询的字段的值都在树的节点里，所以不会回表。加快了查询速度

2、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

3、like KK% 一般情况都会走索引

索引下推（Index Condition Pushdown，ICP）, like KK%其实就是用到了索引下推优化

什么是索引下推了？

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%'

AND age = 22 AND position =‘manager’ 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和

position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索

引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可

以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过

滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全

行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

为什么范围查找Mysql没有用索引下推优化？

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like

KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

4, 排序与分组优化

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1. order by语句使用索引最左前列。

2. 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

2.4分页查询优化

select * from employees limit 10000,10;

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010

条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率

是非常低的。

常用优化技巧

1、根据自增且连续的主键排序的分页查询

select * from employees where id > 10000 limit 5;

通过explain该语句 发现该条语句走了索引，而传统方法并没有走索引，执行效率显著提高

但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致。所以主键不连续，无法使用该种方法优化

2、根据非主键字段排序的分页查询

select * from employees ORDER BY name limit 90000,5;

发现并没有使用 name 字段的索引（key 字段对应的值为 null），具体原因上节课讲过：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

知道不走索引的原因，那么怎么优化呢？

其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

原sql使用filesort排序 新sql使用的索引排序

2.5join关联查询优化

常见的两种关联算法:

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动

表）里取出满足条件的行，然后取出两张表的结果合集。

select * from t1 inner join t2 on t1.a= t2.a; a列有索引

从执行计划中可以看到这些信息：

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优

化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，

当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

上面sql的大致流程如下：

1. 从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；

2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；

3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；

4. 重复上面 3 步。

该过程会读取t2表的所有数据(小表，数据量小，100条)，然后遍历这100条记录，根据t2表中的a索引字段的值，扫描t1表(大表，10000行记录)的对应行，因为t1表中有索引，可以认为t1表实际只扫描了一百行，因此整个过程只扫描了两百行。

如果t1表无索引，则整个过程会扫描100x10000行，性能很低。所以对被驱动表的关联字段有索引会选择NLJ算法，没有索引的会选择BNL算法

2、 基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

select * from t1 inner join t2 on t1.b= t2.b; b列无索引

上面sql的大致流程如下：

1. 把 t2 的所有数据放入到 join_buffer 中

2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比

3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =

10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是

100 * 10000= 100 万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，

就是分段放。

比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然

后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再

次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。

很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有

索引的情况下 NLJ 算法比 BNL算法性能更高

对于关联sql的优化

关联字段加索引，让mysql做join操作时尽量选择NLJ算法

小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去

mysql优化器自己判断的时间

in和exsits优化

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

**in：**当B表的数据集小于A表的数据集时，in优于exists

1 select * from A where id in (select id from B)2

等价于：

for(select id from B){

select * from A where A.id = B.id }

**exists：**当A表的数据集小于B表的数据集时，exists优于in

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id)

等价于:

for(select * from A){

select * from B where B.id = A.id }

A表与B表的ID字段应建立索引

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会

忽略SELECT清单,因此没有区别

2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

3.事务

事务就是一组sql语句组成的逻辑处理单元，事务有ACID四个属性

原子性，一致性，隔离性，持久性

3.1 并发带来的问题

1.脏写

事务A覆盖事务B对数据的更新

2.脏读

事务A读到了事务B写未提交的数据

3.可重复读

事务A在一次事务中查询到的记录不一致

4. 幻读

事务A读取到事务B提交的新增数据，不符合隔离性

3.2 事务隔离级别

1.读未提交

事务A查询数据，事务B在A提交事务前修改数据，事务A再次查询数据，就能查询到事务B没有提交的数据，如果此时事务B rollback则事务A查询到的数据就是脏数据。

2.读已提交

事务A查询数据，事务B在A提交事务前修改数据，事务A再次查询数据，不会读到事务B没有提交的数据，事务B提交事务，则事务A会读取到事务B提交后的数据，则在事务A在一次事务中的两次查询中读取到的数据不一致，就是带来的可重复读问题

3.不可重复读

事务A查询数据，事务B在A提交事务前修改数据并提交，事务A再次查询数据，则不会查询到事务B修改后的数据。保证了一次事务中，多次查询到的数据不一致问题。但是事务B中如果新增一个数据并提交，则事务A查询不会查询到这条数据，但是底层能感知到这个数据，也就是说对新增的数据可以更新，就是幻读问题。

串行化

事务A查询数据，事务B在A提交事务前，更新数据，如果该数据和事务A查询的数据相同，则事务B更新数据会堵塞。因为该事务隔离级别下，事务A查询到的数据会给对应行加写锁，事务B只能读，无法写。而且事务A查询数据事务未提交前，事务B插入数据也会被堵塞。

串行化下会加间隙锁，例如表里有id为 1 2 3 5 8 10 20这几条记录，事务A查询语句where id > 3 and id < 10则事务B无法对3-10id之间的数据进行更新操作，同样例如插入id为6的数据也会被堵塞。 同样如果事务A查询语句where id > 7 and id < 18 则表中 (7-20]均会加锁。

3.3锁详解

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

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资

源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发

访问性能的一个重要因素。

锁分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁

从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

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

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分，分为表锁和行锁

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

一般用在整表数据迁移的场景。

手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

加读锁，当前session和其它session可以读，但是当前会话写报错，其它会话写堵塞

加写锁，当前session可以读写，其它session所有操作被堵塞

结论

1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当

读锁释放后,才会执行其它进程的写操作。

2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进

程的读写操作

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最

高。

InnoDB与MYISAM的最大不同有两点：

1. InnoDB支持事务（TRANSACTION）

2. InnoDB支持行级锁

行锁演示

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

总结：

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自

动给涉及的表加写锁。

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

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

无索引行锁会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

session1 执行：update account set balance = 800 where name = ‘lilei’;

session2 对该表任一行操作都会阻塞住

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为

表锁

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：select * from

test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定

行的session提交

结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更

高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb

的整体性能和MYISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现

不仅不能比MYISAM高，甚至可能会更差。

行锁分析

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

1 show status like ‘innodb_row_lock%’;

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数

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

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，

然后根据分析结果着手制定优化计划。

死锁

set tx_isolation=‘repeatable-read’;

Session_1执行：select * from account where id=1 for update;

Session_2执行：select * from account where id=2 for update;

Session_1执行：select * from account where id=2 for update;

Session_2执行：select * from account where id=1 for update;

查看近期死锁日志信息：show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

锁优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

2. 合理设计索引，尽量缩小锁的范围

3. 尽可能减少检索条件范围，避免间隙锁

4. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

5. 尽可能低级别事务隔离

4.MVCC机制

mysql在可重复读隔离级别下，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。

这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认

是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操

作加锁互斥来实现的。

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

undo日志版本链和readview机制

​ undo日志版本链是指一行数据被多个事务修改后，mysql会保留修改前的数据，写进undo回滚日志里，用两个隐藏字段trx_id和roll_pointer把这些历史记录版本串联起来

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束

之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事

务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应

版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

版本链比对规则：

1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若

row 的 trx_id 就是当前自己的事务是可见的）；

3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

   a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自

己的事务是可见的)；

​ b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见

注意 : begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，

事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

比对过程详解 ： 例如session A 与 B同时begin，B查询了一下表中的数据发现count = 6 则B会生成readview 会记录出当前未提交事务的最小min_id 和最大min_id和 已经生成所有事务中最大的事务mid，则会形成一个区间，小于min_id的一定是已经提交过的事务，则B已经可以看的到，大于mid的事务一定是还没开始的事务，则对于B来说，如果在版本链中发现一个记录的事务id大于该值，则对于事务B来说一定是不应该看见的。则事务B再次查询数据的时候，会从版本链（如上图所示）一个记录一个记录对比事务id，如果第一个事务id大于B的readview中形成的mid ，则该记录对于B来说不应该读到 ，则顺着链，读取下一个版本，如果这个版本的id在min_id和max_id中间，则有两种情况，一种是这个事务未提交或者已经提交，未提交的事务一定在min_id和max_id之间，已经提交的事务则在max_id和mid之间，则根据版本匹配原则第三条进行比对，选择是否应该返回该版本的数据.如果该版本仍然不符合要求，继续向下个版本递归。(过程较为复杂，语言描绘可能难以听懂。大概知道MVCC这个机制保证隔离性的大概过程就行)

总结：

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取

同一条数据在版本链上的不同版本数据

5.Innodb引擎SQL执行的BufferPool缓存机制

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差,

因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能

保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

已经可以看的到，大于mid的事务一定是还没开始的事务，则对于B来说，如果在版本链中发现一个记录的事务id大于该值，则对于事务B来说一定是不应该看见的。则事务B再次查询数据的时候，会从版本链（如上图所示）一个记录一个记录对比事务id，如果第一个事务id大于B的readview中形成的mid ，则该记录对于B来说不应该读到 ，则顺着链，读取下一个版本，如果这个版本的id在min_id和max_id中间，则有两种情况，一种是这个事务未提交或者已经提交，未提交的事务一定在min_id和max_id之间，已经提交的事务则在max_id和mid之间，则根据版本匹配原则第三条进行比对，选择是否应该返回该版本的数据.如果该版本仍然不符合要求，继续向下个版本递归。(过程较为复杂，语言描绘可能难以听懂。大概知道MVCC这个机制保证隔离性的大概过程就行)

总结：

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取

同一条数据在版本链上的不同版本数据

5.Innodb引擎SQL执行的BufferPool缓存机制

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差,

因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能

保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求