mysql优化1
1. Mysql架构与sql执行流程【上】
讲师：青山
时长：1h5min
计划：2020/1/19 11：30 – 12：00
1.1. MySQL的发展历史
2000年MySQL开源，就出现很多分支。
MariaDB【oracle收购后，】，
Percona Server---XtraDB Engine
1.1.1.MYSQL的拼读
MySQL-----【My Ess Que Ell 】----注意：写法-----大小写【简历---体现专业性】
1.1.2.Mysql的官网
https://dev.mysql.com/doc/5.7/en/
1.2.SQL执行流程
一条查询语句是如何执行的？
Select * from user_innodb where name = ‘青山’;
1.2.1.客户端连接服务端
1.2.1.1.通信类型
同步/异步
1.2.1.2.连接方式
长连接/短连接
MySQL支持两种方式：
短连接：当一个sql执行完毕，会立即close这个连接。
长连接：保持连接多次使用，不需要频繁地关闭、打开连接。一个连接可被多个客户端复用。
一般在数据库连接池中使用。它对服务端的影响【消耗内存】
1.2.1.3.通信协议
》unix Socket
>TCP/IP
连接时，-h参数来体现
》Named Pipes
>Share Memory
后面两种，需要在安装勾选才会有效
1.2.1.4.通信方式
MySQL里面使用的半双工通信方式：
》要么客户端------向服务端发送数据
》要么服务端------向客户端响应数据
1.2.2.MySQL服务端参数
1.2.1.1.连接参数
一个连接保持多长时间没有使用，会关闭它？
(1).超时时间【8个小时】
wait_timeout【等待超时时间】，如：jdbc程序
interactive_timeout【交互式超时时间】如：数据库连接工具
show GLOBAL VARIABLES like 'wait_timeout';
show GLOBAL VARIABLES like 'interactive_timeout';
(2).连接数量
show global status like 'Thread%';
说明：
当客户端建立一个连接后，服务端是通过线程Thread来进行处理的。
Show processlist;
(3).最大连接数
show VARIABLES like 'max_connections';
Mysql5.5官方默认值为100
Mysql5.7官方默认值为151，如下所示：
说明：
它的最大值可以设置为2^14 = 16384。
并不是连接数设置越大，性能越好。
系统参数有两种级别：session和global，当不写明时，默认session
(4).一次发送数据的最大长度
当超过这个最大值时，可能会报错。所以，避免不带limit的查询操作
show VARIABLES like 'max_allowed_packet';
MySQL5.7官方默认值为4MB：
MySQL5.5默认值为1MB，如下所示：
(5).MySQL查询缓存开关
show VARIABLES like 'query_cache%';
而MySQL5.5版本是打开的，MySQL5.5是关闭的。
为什么会关闭它呢？说明MySQL官方不推荐使用，为什么呢？
因为查询缓存的生效条件要求很严格，很多情况会导致缓存失效：
》sql语句必须完全一样【甚至空格也不能多或少】
》一张表只要有一条数据发生变化【更新】，所有数据缓存会失效
所以，在mysql8.0.0版本中查询缓存模块已经被移除掉了。
(6)查询优化器监控开关
show VARIABLES like 'optimizer_trace%';
修改值：
Set optimizer_trace = ‘enabled=on’ //开启监控
注意：
这个查询优化器是MySQL5.7以后才有的，5.5版本是没有的。
(7）查看数据库下所有表的存储引擎
show table status from dsg_dmsdb;
(8) 服务端数据的存储目录查看
1.2.2.2.参数设置
A.永久设置
通过修改配置文件。
Vi /etc/my.cnf
B.动态修改
使用set命令
1.2.3.MySQL的sql执行流程
1.客户端和服务端建立连接
2.服务端缓存数据
MySQL默认查询缓存的开关是关闭的。
3.解析器模块-----校验sql语句
当我们输入一条不符合sql语句的sql后，点击执行，会报错
》词法解析
把sql拆分成一个个的关键字，select ,*,from…
》语法解析
经过解析后，解析过程构成一个解析树,如下所示：
服务端sql的执行有很多的执行路径，执行方式，这些执行方式是怎么得到的呢？我们又是如何了解的呢？
它们由预处理器来完成。
当得到这些执行路径后，又应该选择哪一条执行？如何选择【判断标准是什么？】
这部分功能，由优化器Optimizer来处理。最后得到一个执行计划Execution Plan
优化：sql开销小，就选择哪一种。
如何查询一条sql语句的开销情况：
show status like 'Last_query_cost';
关于优化器的书籍推荐：
《数据库查询优化器的艺术-原理解析》
查询优化器监控情况：
Select * from information_schema.optimizer_trace\G
说明：
执行前提，必须要开启监控开关.
返回json。分析如下：
4.数据存储
经过优化后，生成执行计划，plan是在哪里执行？由谁来执行？
逻辑上，数据是存储在Table表结构中的，可以理解为excel表格。
在存储数据时，还需要组织数据的存储结构。这个结构是由什么决定的呢？
它是由存储引擎来决定的。
Mysql中有多种存储引擎，它们是可以相互替换。
表新建完成后，存储引擎是可以修改的。
在服务端数据的存储目录查看：
1. MySQL架构与sql执行流程【下】
讲师：青山
时长：48min
计划：1/20/2020 10:20 –11:20---15：38
2.1.MySQL模块详解
这四层自顶向下分别是网络连接层，服务层（核心层），存储引擎层，系统文件层。我们自顶向下开始讲解。
2.1.1.网络接入层
作用
主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是MySQL所特有的技术。
为什么要设计成线程池？
在服务器内部，每个client都要有自己的线程。这个连接的查询都在一个单独的线程中执行。想象现实场景中数据库访问连接实在是太多了，如果每次连接都要创建一个线程，同时还要负责该线程的销毁。对于系统来说是多么大的消耗。由于线程是操作系统宝贵的资源。这时候线程池的出现就显得自然了，服务器缓存了线程，因此不需要为每个Client连接创建和销毁线程。
2.1.2.服务层
作用
第二层服务层是MySQL的核心，MySQL的核心服务层都在这一层，查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。通过下图来观察服务层的内部结构：
下面来简单分析SQL语句在服务层中具体的流程：
查询缓存
在解析查询之前，服务器会检查查询缓存，如果能找到对应的查询，服务器不必进行查询解析、优化和执行的过程，直接返回缓存中的结果集。
解析器与预处理器
MySQL会解析查询，并创建了一个内部数据结构（解析树）。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理会根据MySQL的规则进一步检查解析树是否合法。比如要查询的数据表和数据列是否存在等。
查询优化器
优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。优化器并不关心使用的什么存储引擎，但是存储引擎对优化查询是有影响的。优化器要求存储引擎提供容量或某个具体操作的开销信息来评估执行时间。
查询引擎
在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令调用存储引擎的接口得出结果。
2.1.3.存储引擎层
作用
负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。例如阿里巴巴的X-Engine，为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。
特点：
存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。
下面大致介绍一下MySQL中常见的的存储引擎
InnoDB
特点：支持事务，适合OLTP应用，假设没有什么特殊的需求，一般都采用InnoDB作为存储引擎。支持行级锁，从MySQL5.5.8开始,InnoDB存储引擎是默认的存储引擎。
MyISAM
特点
不支持事务，表锁设计，支持全文索引，主要应用于OLAP应用
场景
在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表就是MyISAM类型报表，数据仓库
Memory
特点
数据都存放在内存中，数据库重启或崩溃，表中的数据都将消失，但是标的结构还是会保存下来。默认使用Hash索引。
场景
适合存储OLTP应用的临时数据或中间表。
用于查找或是映射表，例如邮编和地区的对应表。
除此之外还有CSV,Federated、Archive等等。
2.1.4.系统文件层
作用
该层主要是将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。
2.2.再讲sql执行流程----更新sql
一条更新语句是如何执行的？【包括修改，删除和插入】
Update user_innodb set name = ‘penyuyan’ where id = 1;
2.2.1.buffer pool
更新操作时，会从缓冲池buffer pool中读取数据----来自于磁盘中page页
修改时，先在内存中修改buffer pool中数据，当数据与磁盘中数据不一致时，再进行同步。
查看buffer pool的状态：
show status like '%innodb_buffer_pool%';
查询buffer pool默认大小：
show VARIABLES like '%innodb_buffer_pool%';
说明：
这里由于开关关闭，无法查询到默认大小innodb_buffer_pool_chunk_size
我们把buffer pool放到内存当中，如果内存占满了怎么办呢？使用LRU内存淘汰算法进行清理不常用数据。
2.2.1.1.Change buffer区域
当我们要更新page页，加载到buffer pool中时，直接同步到磁盘，即可完成更新。
但是，如果要更新的数据在磁盘中，需要先加载到buffer【至少进行一次磁盘io】,再进行更新。那么，buffer Pool有没有好的优化操作呢？
这个优化，由Change buffer来完成，它的优化原理：
如果更新记录，不是一种唯一索引的情况，就直接把数据写入change buffer，直接进行更新。不需要比较是否重复。适应于写多读少的场景。
查看change buffer的参数：
show VARIABLES like '%change_buffer%';
innodb_change_buffer_max_size表示change buffer占buffer pool的百分比。
2.2.1.2.log buffer
如果内存中dirty页的数据还未刷入磁盘，就挂掉了，在向磁盘写入文件时，可能会存在只写入一部分就挂掉的情况。为了防止这种情况，写入磁盘时，会把写入操作以日志的方式记录到log buffer中，当重新启动时，可以从log buffer中加载恢复数据。
Log buffer是用来保存，即将写入磁盘的数据。
查看log buffer的大小：
show VARIABLES like '%log_buffer%';
innodb_log_buffer_size表示log buffer的大小，默认为16MB.这里可能有修改。
2.2.1.3.redo log
文件系统中存储redo log模块，它有WAL【Write Ahead Logging】先写日志，后写文件的机制。
写入redo log能提升IO性能，写入效率会提高。为什么同样是写入磁盘，而写入redo log会更快呢？
需要了解两个概念：
》随机IO
》顺序IO
磁盘的结构如下：
磁盘的最小单元，称为扇区。【是一个扇形区域】，通常是512b
操作系统与内存的交互，最小单位，称为page页。
操作系统与磁盘的交互，最小单位，称为块。
如果我们操作的数据是随机分布在磁盘上不同的page页，不同的扇区的话，这个数据该如何找到它呢？
它是通过磁臂arm旋转，旋转到一个指定的page页之后，盘键找到这个扇区，才能找到需要的数据。一直到在不同的扇区里面把所有的数据拿到之后，IO操作才算完成。
这种方式称为随机IO.可以知道这种方式，读取数据是比较慢的。
而顺序IO，只需要一次寻址找到数据，然后依次顺序读取相临的数据，就会快速很多。
写入redo log日志文件，是以顺序IO的方式进行，读取性能较快。
Redo log日志文件，主要是用来做灾难恢复的。
Redo log的特点：
》它是基于innodb
》它是基于物理page
2.2.1.4.log buffer的刷盘时机
Log buffer数据是如何写入redo log文件的，它与事务相关。
它的决定参数：
show VARIABLES like '%log_at_trx_commit%';
这个参数有0，1，2三种值，默认值为1，只要提交事务，就会写入数据到磁盘文件中。
2.2.1.5.binlog日志
它是用于记录DDL DML，是逻辑日志
作用：
主从
数据恢复
2.2.2.Innodb架构
查看官网：
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
2.2.3.更新sql执行流程总结
1.从内存或磁盘读取数据
2.执行器 执行sql，写入数据
3.先写入日志文件undo log,redo log
4.写入buffer pool
注意：在内存和磁盘文件之间，有很多工作线程[后台线程]
2.2.3.1.加上binlog日志文件后，sql的执行流程
3.MySQL的索引原理及使用原则【上】
时长：58min
计划：2020/1/20 15：40 –16：40
目标：
》索引的本质
》索引底层数据结构
》不同存储引擎的索引实现方式
》索引的创建及使用原则
3.1.MySQL常见优化手段
思考：
》表的索引越全越好，因为不管什么情况下都能用到索引，对吗？
》为什么不要在性别字段【值重复较高】上建索引？
》为什么不建议使用身份证【随机】作为主键？
》模糊匹配like abc%，like %2673%,like %888都用不到索引，对吗？
》不要使用select * ,写明具体查询字段，为什么？
3.2.索引的本质
假设有一张表user_innodb，有500百万条数据，测试查询性能？
Select * from user_innodb where name = '青山';
如果没有添加索引，大约需要11s
然后创建索引,再进行查询，
3.2.1.索引到底是什么？
【维基百科】：是数据库管理系统(DBMS)中一个排好序的数据结构，以协助快速查询、更新数据库表中数据。
索引建立后，查询数据就相当于查字典。
理解：
建立一个存储地址，与存储数据的映射关系。当建立索引后，可以根据地址，快速定位到要查询的数据。
普通的数据查询：需要进行全表扫描
3.2.2.创建索引
有两种方式：新建表时创建，和建表后修改表结构添加
1.修改表结构添加索引
Alter table user_innodb add INDEX idx_name(name);
说明：
Idx_name：索引名称
（name）针对name字段创建索引。
当执行创建索引的sql时，速度较慢，500万条数据【大约需要1min30s】
2.使用navcat可视化工具创建索引
MySQL中提供3种索引类型：
Normal:普通---------Index/key
Unique:唯一,主键索引是特殊的唯一索引，还要求字段值Not null.
Full Text:全文索引,主要针对文本数据，如：varchar,text
在文本中匹配一个字符串，常使用模糊匹配【like ‘%abc%’】,但是它会导致索引失效。
为了解决这种大文本中数据查询的性能问题，需要使用Full text全文索引。
3.2.2.1.全文索引创建
》新建表时，创建
CREATE TABLE `user_innodb` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`gender` tinyint(4) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
Fulltext index(name) //创建全文索引
或 fulltext key `context_fulltext`(`content`) //创建全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
全文索引，匹配字符串，使用match语句。
Select * from fulltext_test where match(content) against('test' IN NATURAL LANGUAGE MODE);
3.3.索引底层数据结构推导
3.3.1.引出
玩游戏-----猜数字，只有5次机会，女朋友说：猜猜我双十一买了多少钱的东西？
我猜想：
第一次-------1万----------低了
第二次-------3万----------高了
第三次-------你会猜多少呢？会猜2万------使用2分查找
二分查找，是查找有序数组效率较高的查询算法，所以，索引的底层数据结构猜想----也可能会拿手二分查找。
3.3.1.1.索引的底层数据结构数据模型
(1)有序数组：
分析：
等值查询【=】
比较查询【>】
根据index下标，进行查询，性能很高
但是，更新值时，性能较差了。
因为数据增加或减少了，会导致数组大小发生变化，数组下标就需要相应移动。
所以，有序数组不适合数据变更的场景。
(2)单链表：
为了优化，数据库更新操作，sql执行性能问题。可以考虑使用单链表。
特点：
更新快，查询慢【因为需要从第一个节点开始遍历】
再分析，那么有没有一种使用二分查询算法，链表呢？--------BST【二叉查找树】
(3)二叉查找树(Binary Search Tree)
特点：
左子树的节点 <父节点
右子树的节点 >父节点
树的节点数据，投映到一个平面，就是一个天然有序的链表。
这种结构的问题：
当插入数据刚好是一个有序的列表时，构成一个单链表，也称为斜树。
这种情况，就需要从头开始查找，时间复杂度为O(n).查询性能不能得到很好提升。
问题分析：
这种树的特点，全部元素都集中到右子树。左子树没有元素。我们称它为不平衡二叉树。
因此，有人提出平衡二叉树。
(4)平衡二叉树（AVL,Tree-Balanced BST）
特点：左右子树深度差绝对值不能超过1
下面来模拟，插入1，2，3，4，5，6的情况
》插入1，2 情况如下：
》再插入3
分析：
当插入3时，右边子树深度为2，而左边子树深度为0，违背高度差。
所以，发生了左旋操作，第二个节点2提升为父节点。
》再插入，4，5，6,如下图所示：
假设使用AVL存储数据，物理实现逻辑如下所示：
(5)多路平衡查找树 Balanced Tree【B树】
查询逻辑：
如需要查询id = 15的数据，查询顺序如下
15 《 17----查询左子树
15 》12 查询右子树，找到磁盘块7，
再根据磁盘地址，直接找到id = 15的数据
B树的实现原理：
B树是通过分裂和合并保持树的平衡的。
(6)B+树
也称加强版多路平衡查找树
B+树存储数据逻辑实现
特点：
关键字= 度=树的高度
只有叶子节点才存储数据
叶子节点增加指针---指向下一节点，形成有序链表结构
对于范围查询，只需要在叶子节点上进行查找即可
特点总结：
》B+ Tree能解决b tree解决的问题
》扫库，扫表能力更强----叶子节点指针
》磁盘读写能力更强---根节点存储地址，叶子节点存数据，3次IO，可存储2千万
》排序能力更强
》效率更加稳定
数据存储大小：
假设一条记录，内存大小为1Kb,一个叶子节点可放16条记录。
假设id为bigint,8节点 + 指针 = 14b
16k = 16384 /14 = 1170个分叉-------》1170
1170 * 1170 * 16 = 21902400，2千万数据
树的深度为2，只需3次IO
3.3.2.InnoDB逻辑存储结构
3.3.2.1.文件系统中的页
3.3.2.2.页存放数据
1.页分裂
3.3.2.3.行格式
表的行格式，决定一个表的行数据是如何物理存储的，也影响着数据查询性能和数据库操作。
该InnoDB存储引擎支持四名的格式：REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。
Antelope(羚羊)是InnoDB内置的文件格式，有两种行格式：
》REDUNDANT
》COMPACT(5.6默认)
Barracuda(梭子鱼)是InnoDB Plugin支持的文件格式，新增两种行格式：
》DYNAMIC（5.7默认）
》和COMPRESSED
3.3.3.AVL Tree数据存储
当数据存储引擎为InnoDB，使用AVL Tree存储索引。一个树的节点设置为一个页的大小，即为16KB.
一个节点需要存储3个数据：
》键值
》数据的磁盘地址
》子节点的引用
以int型字段为例，上面的3个数据占用内存只有几十byte.一个页的大小为16KB，造成内存浪费。
显然这种数据结构，也是不可行
4.MySQL的索引原理及使用原则【下】
4.1.索引底层数据结构为什么不使用红黑树？
红树的定义：
1.节点分为红色或黑色
2.根节点必须是黑色
3.叶子节点都是黑色的NULL节点
4.红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）
5.从任意节点出发，到其每个叶子节点的路径中包含相同数量的黑色节点
达到效果：
从根节点到叶子节点的最长路径不大于最短路径的2倍----保持树的左右基本平衡
4.2.MyIsam存储引擎
索引的数据是存储到磁盘文件上的，myIsam型数据表在磁盘上会生成3个文件：
》*.frm
》*.MYD 存储数据文件
》*.MYI 存储索引值
4.2.1.MyISAM-主键索引
4.3.MyISAM-辅助索引
4.4.InnoDB主键索引
InnoDB型数据表，在磁盘上存储文件为：
》*.frm
》*.ibd 存放数据及索引信息
特点：
是以主键为索引来组织数据的存储
4.5.聚集【簇】索引
它是索引的键值的逻辑顺序。
如：id为有序1，2，3，4…,表数据行的物理存储顺序，和它是一致的。就称为聚集索引
只有主键索引，是聚集索引。
4.6.InnoDB的辅助索引
它和主键索引是不一样的，表示图解如下：
主键索引：存储索引和数据
辅助索引：存储索引和主键值
如果使用辅助索引（如：name）来查询数据，需要经过两次：
》先在辅助索引的B+树上找到索引值对应的id
》再把id拿到主键索引的B+树上进行查找，最终在叶子节点上找到完整的数据
辅助索引的查询，为什么要这样设计？为什么不在叶子节点上存储地址？
原因：地址可能占用空间比较大，浪费存储空间。
此外，我们知道管理磁盘最小的单位是页，如果发生页的分裂，行数据的地址是会改变的。
所以，不存储地址，而存储主键值【不会改变】
没有主键怎么办呢？
一张表有没有可能没有聚集索引？
是不可能的，如果表中没有定义主键，它会选择表中第一个不包含null的unique key,来作为聚集索引来组织数据存储。
如果表中没有unique的key,它会选用内置的6byte长的rowId来作为聚集索引。
4.7.索引的使用原则
4.7.1.误区1.并不会对所有字段建立索引
结论：
当字段重复值很多时，没有必要建立索引
4.7.1.1.相关概念
【1】列的离散度
离散度公式：
count(distinct(column_name))/count(*)
比值越大，离散度越高。
gender和name相比，哪一个离散度更高？
gender一般只有0或1两个值，离散高较低。
【2】联合索引最左匹配原则
当对多个字段，创建联合索引时，如下语句：
alter table user_innodb add INDEX `comidx_name_phone`(`name`,`phone`);
name和phone建立联合索引，顺序性非常重要：
sql
sql使用：
where name = ‘青山’and phone = ‘136’
如果前面一个字段name不在where子句中，就会导致索引失效，会按全表扫描。
【3】覆盖索引
什么是回表？
当使用辅助索引时，需要先辅助索引的B+树，再扫描主键索引的B+树，这种动作称为回表。
什么是覆盖索引？
查询字段只含索引字段，如：user表中name,phone两个建立联合索引。
select name from user where name = ‘青山’
这种查询，称为覆盖索引
特点：
这种查询，不需要再经过主键索引的B树，能提升查询性能。
能够避免回表。
验证：
可使用Explain工具，查看Extra的值，如果为using index，说明sql语句使用到覆盖索引。
【4】索引条件下推
【4】索引条件下推ICP
创建一个employee表：
drop table employees;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
//增加索引
alter table employees add index idx_lastname_firstname(last_name,first_name);
//插入数据
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1, NULL, '698', 'liu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2, NULL, 'd99', 'zheng', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3, NULL, 'e08', 'huang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4, NULL, '59d', 'lu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5, NULL, '0dc', 'yu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6, NULL, '989', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7, NULL, 'e38', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8, NULL, '0zi', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9, NULL, 'dc9', 'xie', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10, NULL, '5ba', 'zhou', 'F', NULL);
show variables like 'optimizer_switch'; //查询优化器
index_merge=on,index_merge_union=on,
index_merge_sort_union=on,index_merge_intersection=on,
engine_condition_pushdown=on
index_condition_pushdown索引条件下推开关，默认on
奇怪：我的mysql中没有查询到这个开关？
set optimizer_switch = 'engine_condition_pushdown=off';
//set默认当前session级别，只有加上global才会影响其他的会话。
当开关关闭后，来测试一个sql查询：
SELECT * from employees WHERE last_name = 'wang' and first_name LIKE '%zi';
这条sql有两种执行方式：
1.先找出姓wang的二级索引数据，然后回表，到主键索引里面查询所有符合条件的数据
2. 先找出姓wang的二级索引数据，在从二级索引中筛选出，以zi结尾的索引【剩下一个索引】，然后再到主键索引中查询符合条件的数据。
显然，第二种方式，查询性能更高。
注意：
索引的过滤是在存储引擎进行的。数据的过滤是在mysql的server层进行的。
当开关设置为off时，explain工具查询到sql使用二级索引方式，extra的值为Using where
当开发为on时，extra 的值为Using index_condition,支持索引下推。
4.7.1.2.创建索引的原则
1.在用于where 判断order排序和join的on字段上创建索引
2.索引的个数不要太多【索引要消耗存储空间，过多可能影响insert数据】
3.区分度低【重复度高】的字段，例如：性别，不要创建索引
4.频繁更新的值，不要作为主键或索引【会发生数据调整，产生页分裂】
5.组合索引把散列性高【区分度高】的值放在前面
6.创建复合索引，而不是修改单列索引
作业：
7.过长的字段，怎么创建索引？
可以使用前缀索引，它的语法如下：
key `address`(`address(12)`) //指定匹配位数
然后通过离散度公式，来测试匹配位数
8.为什么不建议用无序的值（如：身份证，uuid）作为索引？
如果使用随机值作为主键，可能会导致数据插入时发生页分裂
4.7.1.3.什么情况导致索引失效？
1.索引列上使用函数（replace,substring,concat,sum,count,avg），表达式
2．字符串不加引号，出现隐式转换
3.like条件中左边带%【最左前缀匹配】
4.负向查询能用到索引吗？
如：<>,!=,not in…等关键字，not like一定不能有效
不等，可能会使用主键索引。
4.7.2.explain工具
查询一条sql语句，预计要扫描多少行数据？
使用：
explain select * from table where name = “qingshan”
如果name创建索引，并且值不会重复，结果rows =1,只需要扫描一行
4.7.3.查看表的索引
show indexes from table;
5.Mysql事务和锁【上】
时长：54min
计划：2020/3/18 23：27
目标：
》事务特性，与事务并发造成的问题
》事务读一致性问题的解决方案
》MVCC的原理
》锁的分类，行锁的原理，行锁的算法
5.1.什么是数据库事务？
5.1.1.事务的典型场景
技术层面，只有与数据库更新有关方法
业务层面，多系统共同存储，金融行业---转帐
5.1.2.什么是事务【定义】
事务是数据库管理系统【DBMS】执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。
逻辑单位：理解为最小单位，不可再分割。
操作序列：dml语句，ddl语句。
5.1.3.哪些存储引擎支持事务
只有InnoDB，ndb【集群】
5.1.4.事务四大特性
原一隔持ACID
ACID
原子性[Atomicity]
最小单位，不可再分。
对数据库的操作，要么全部成功，要么全部失败。
对于已经的提交的sql，如何撤销呢？需要通过回滚来实现。
回滚是如何实现的呢？undoLog记录是数据修改之前的値。 一旦发生异常，通过undo log来实现回滚。
一致性【Consistent】
是指数据库的完整性约束没有被破坏。事务执行前后，数据都是一个合法的状态。
如：主键必须唯一，字段长度必须符合要求。
还有指用户自定义数据的完整性。如：转帐业务，转入与转出必须保持一致。
隔离性【Isolation】
可能出现多个事务，操作同一张表或操作同一张表的同一行数据，同时去操作，就会出现一些如并发等干扰性操作。
隔离性要求，不同事务之间相互独立，互不干扰。
持久性【Durable】
对数据库的操作【增删改】，只要事务提交成功，它的执行结果就应该是永久性的。不会因为系统宕机或机器重启，而导致数据恢复到提交之前。
思考：
数据库的事务应该如何实现？或者数据库崩溃应该如何恢复？
事务的崩溃恢复是能redo log来实现，磁盘在操作数据时，会先把数据写到内存的buffer pool缓冲池里面。如果在刷盘时出现异常，那么，就可以在系统重启之后读取redo log里面的数据，写入到磁盘，保证数据一致性。
如果磁盘页上的数据本身已经损坏，redo log是无法恢复的。有一个double write双写缓冲，为数据页创建一个副本，保证数据页的完整性。
5.1.5.数据库什么时候会出现事务
最终发送一个指令到数据库执行，才会开启一个事务。
1.查询数据库版本：
select VERSION();
2.查询存储引擎
show variables like '%engine%';
默认InnoDB
3.查看事务隔离级别：
show GLOBAL variables like 'tx_isolation';
REPEATABLE-READ 可重复读
》事务并发会带来什么问题
4.创建一张student表
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`sname` varchar(255) NOT NULL,
`sno` varchar(20) NOT NULL,
`company` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;
插入3条数据：
INSERT INTO `student` (`id`, `sno`, `sname`, `company`)
VALUES (1, 'GP16666', '猫老公', 'Tencent');
INSERT INTO `student` (`id`, `sno`, `sname`, `company`)
VALUES (2, 'GP17777', '个人的精彩', 'Baidu');
INSERT INTO `student` (`id`, `sno`, `sname`, `company`)
VALUES (3, 'GP18888', '菜鸟', 'Alibaba');
//执行语句，查看是否有事务？
UPDATE student set sname = '猫老公111' where id = 1;
是有事务，自动开启并提交事务
什么参数来控制事务的开启与提交？
show global variables like 'autocommit';
autocommit默认值为on.
设置autocommit的值为off
set session autocommit = 'off';
需要手动开启事务：
begin;或start transaction;
begin;
UPDATE student set sname = '猫老公222' where id = 1;
ROLLBACK;
先依次执行前两个sql,但未提交事务，所以值不会修改。
结束事务：
commit;或rollback;
注意：
事务所持有的锁，在事务结束之后，就会自动释放掉。
当我们使用客户端连接数据库服务端，关闭会话【一个窗口】，连接断开，就会结束事务，也会释放锁。
5.1.6. 事务并发会带来什么问题
有3大问题：
5.1.6.1.脏读
前提：两个事务[Transation A 和 B]操作同一张表同一行数据
说明：
事务A通过select查询到一条记录，事务B执行update更新语句【但未提交】，修改age=18.
事务A再执行select查询，获取不同的结果。
结论：
同一事务A，执行同样的select语句,得到不同的结果。是因为其他事务【B】修改了数据【未提交】，即读取到其他事务未提交的数据。
其他事务未提交的数据，可能回滚，所以读取到的是脏数据。这种情况称为脏读。
5.1.6.2.不可重复读
说明：
事务A通过select查询到一条记录，事务B执行update更新语句【并完成提交】，修改age=18.
事务A再执行select查询，获取不同的结果。
结论：
同一事务A，执行同样的select语句,得到不同的结果。是因为其他事务【B】修改了数据【并提交】，即读取到其他事务已提交的数据。
其他事务已提交的数据，不能回滚，所以不可能重复读取原来数据。这种情况称为不可重复读。
5.1.6.3.幻读
前提：两个事务[Transation A 和 B]操作同一张表
说明：
事务A通过select范围查询，查询到一条记录，事务B执行insert语句【并且提交】
事务A再执行select范围查询，获取两条记录，得到不同的结果。
结论：
同一事务A，两次执行同样的select语句,得到不同的结果。是因为其他事务【B】插入了新数据【并提交】，即读取到比原来更多的数据。
这种读取更多的数据，可理解为是原来数据的幻影，称为幻读。
5.1.6.4.问题解决
事务并发带来3大问题其实都是数据库读一致性问题。必须由数据库提供一定的事务隔离机制来解决。
打个比方，我们到餐厅去吃饭，基本的桌椅应该由餐厅来提供，而不是我们自带。
所以，就由很多的数据库专家联合制定标准。产生数据库的4大隔离级别。
5.1.7.数据库四大隔离级别
SQL92 ANSI/ISO标准：
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
搜索_iso:
3种问题：
1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-
transaction T2 then reads that row before T1 performs a COMMIT.
If T1 then performs a ROLLBACK, T2 will have read a row that was
never committed and that may thus be considered to have never
existed.
2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-
transaction T2 then modifies or deletes that row and performs
a COMMIT. If T1 then attempts to reread the row, it may receive
the modified value or discover that the row has been deleted.
3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N
that satisfy some <search condition>. SQL-transaction T2 then
executes SQL-statements that generate one or more rows that
satisfy the <search condition> used by SQL-transaction T1. If
SQL-transaction T1 then repeats the initial read with the same
<search condition>, it obtains a different collection of rows.
不同的数据库厂商有不同的数据库隔离级别：
Oracle只支持RC【默认】,串行化
Mysql有4种：默认是可重复读
思考：
如果要解决读一致性的问题，保证一个事务中前后两次读取数据结果一致，实现事务隔离，应该怎么做？
5.1.8.事务隔离级别的解决方案
解决方案：
第一种：
在读取数据前，对其加锁，阻止其他事务对数据进行修改【LBCC】lock Based Concurrency Control【基于锁的并发控制】
第二种：
生成一个数据请求时间点的一致性数据快照(Snapshot)，并用这个快照来提供一定的级别（语句级或事务级）的一致性读取【MVCC】Multi Version Concurrency Control
5.8.1.1.MVCC
该方案是如何实现的？
DB_TRX_ID,6字节：插入或更新行的最后一个事务的事务ID,事务编号是自动递增的(创建版本)
DB_ROLL_PTR,7字节：回滚指针（删除版本）
上面是mysql数据库InnoDB中实现的两个隐藏字段，都理解为当前事务的编号。
它的核心思想：
一旦我们开启一个事务，在当前事务里面已经开始查询数据了，在我后面插入的数据是查询不到的。即使数据在后面被修改或删除，依然可读取到数据。
思考：
这个快照是什么时候创建的？读取数据时，怎么能保证读取到的是快照的数据，而不是最新的数据或已经被其他事务修改的数据？这个应该如何来实现？
理解过程如下：
https://www.processon.com/view/link/5d29999ee4b07917e2e09298
第一个事务，初始化数据（检查初始数据），插入两条数据[insert]
Transaction
1
begin;
insert into mvcctest values(NULL,'qingshan') ;
insert into mvcctest values(NULL,'jack') ;
commit;
此时的数据，创建版本是当前事务 ID，删除版本为空：
注意：
navcat客户端如何表示不同事务？
其实一个会话窗口【创建一个查询器】，就表示一个事务。
第二个事务，执行第 1 次查询，读取到两条原始数据，这个时候事务 ID 是 2：
第三个事务，插入数据：
此时的数据，多了一条 tom，它的创建版本号是当前事务编号，3：
第二个事务，执行第 2 次查询：
MVCC 的查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。
也就是不能查到在我的事务开始之后插入的数据，tom 的创建
ID 大于 2，所以还是只能查到两条数据。
第四个事务，删除数据，删除了 id=2 jack 这条记录：
|
Transaction 4 |
|
begin; |
此时的数据，jack 的删除版本被记录为当前事务 ID，4，其他数据不变：
在第二个事务中，执行第 3 次查询：
查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。
也就是，在我事务开始之后删除的数据，所以 jack 依然可以查出来。所以还是这两条数据。
第五个事务，执行更新操作，这个事务事务 ID 是 5：
此时的数据，更新数据的时候，旧数据的删除版本被记录为当前事务 ID 5（undo），产生了一条新数据，创建 ID 为当前事务 ID 5：
第二个事务，执行第 4 次查询：
Transaction 2
select * from mvcctest ; (4) 第四次查询
查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。
因为更新后的数据 penyuyan 创建版本大于 2，代表是在事务之后增加的，查不出来。
而旧数据
qingshan 的删除版本大于 2，代表是在事务之后删除的，可以查出来。
通过以上演示我们能看到，通过版本号的控制，无论其他事务是插入、修改、删除，第一个事务查询到的数据都没有变化。
在 InnoDB 中，MVCC 是通过 Undo log 实现的。
Oracle、Postgres 等等其他数据库都有 MVCC 的实现。
需要注意，在 InnoDB 中，MVCC 和锁是协同使用的，这两种方案并不是互斥的。
第一大类解决方案是锁，锁又是怎么实现读一致性的呢？
6.Mysql事务和锁【下】
时长：1h7min
计划：2020/3/21 23:30 -0:00
MySQL InnoDB锁的基本类型
查看官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.htm
官网把锁分成了 8 类。所以我们把前面的两个行级别的锁（Shared and Exclusive Locks），和两个表级别的锁（Intention Locks）称为锁的基本模式。
后面三个 Record Locks、Gap Locks、Next-Key Locks，我们把它们叫做锁的算法，也就是分别在什么情况下锁定什么范围。
6.1.锁的粒度
6.1.1.表锁与行锁的区别
锁定粒度：表锁 > 行锁
加锁效率：表锁 > 行锁
冲突概率：表锁 > 行锁
并发性能：表锁 < 行锁
思考：
MyISAM和InnoDB 分别支持什么粒度的锁？
InnoDB 里面我们知道它既支持表锁又支持行锁。MyISAM只支持表锁，使用lock tables xxx read/write;来加表读锁或写锁
使用unlock tables;释放表锁。
问题二： InnoDB 已经支持行锁了，那么它也可以通过把表里面的每一行都锁住来实现表锁，为什么还要提供表锁呢？
要搞清楚这个问题，我们就要来了解一下 InnoDB 里面的基本的锁的模式（lock
mode），这里面有两个行锁和两个表锁。
6.1.1.1.相关概念
【表锁】
锁住一张表。
【行锁】
就是锁住表里面的一行数据。
显然，表锁锁住所有行，而行锁只锁定一行。
【加锁效率】
表锁，只需要锁住整张表。而行锁，需要先到表中查询到该行数据，再加锁。所以，行锁加锁更困难，效率更低。
【锁冲突】
是指多个事务之间，持有锁的事务对其他事务的干扰性。
表锁，当一个事务锁定全表时，其他事务都不能操作该表。
而行锁，只是锁住表中一行数据，表中其他未被锁定的数据行，还可以由其他事务操作。
所以，表锁冲突更大，并发性能就越低。
6.2.InnoDB的锁类型
共享锁【行锁】：Shared Locks
排它锁【行锁】：Exclusive Locks
意向共享锁【表锁】：Intention Shared Locks
意向排它锁【表锁】：Intention Exclusive Locks
6.2.1.共享锁
又称读锁，简称S锁，指多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
加锁/释放方式：
Select * from student where id = 1 LOCK IN SHARE MODE;
Commmit/rollback;
6.2.2.行锁-排它锁
又称写锁，简称X锁，排他锁不能与其他锁并存。如一个事务A获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁，排他锁）
只有该获取了排他锁的事务是可以对数据行进行读取和修改。
加锁、释放锁方式：
自动：delete/update/insert 默认加上X锁。
手动：select * from student where id =1 FOR UPDATE;
Commit/rollback;
6.2.3.意向共享锁[IS]、意向排他锁【IX】
意向锁：
是由数据引擎自己维护的，用户无法手动操作意向锁。
意向共享锁【Intention Shared Lock,IS】
表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁【Intention Exclusive Lock,IX】：
表示事务准备给数据行加入排他锁，也就是说一个数据行加共享锁前必须先取得该表的IX锁。
思考：
为什么需要（表级别的）意向锁？
给一张表，加上表锁，前提是这张表的任意一行数据都没有被其他事务锁定。
如何保证这个前提呢？----需要进行全表扫描，如果数据千万级，效率会很低。所以，数据库提供意向锁，来减轻全表扫描的消耗。
意向锁，理解为一种加锁前提保证标记，提高表扫描效率。
问题：
锁的作用是什么？
锁到底锁住了什么？是一行数据(Row)吗？还是一个字段（column）吗？
Java中同步锁是用来干嘛的呢？----用来控制并发，解决并发带来线程安全问题的。说到底，就是为了解决资源竞争问题。
Java里面的资源是一个个的对象。
而数据库的资源是一个个数据表，数据行，数据库的锁是用来解决事务并发访问问题的。
6.3.数据库锁的原理
锁到底锁的是什么东西，才能实现控制并发访问的？
下面进行sql验证:
6.3.1.无索引表验证
前提：t1表中有4条记录，t1表中id和name不存在索引，即没有主键。
有两个会话：
Session 1:
Begin;
Select * from t1 where id = 1 for update;
第一个事务，执行sql,锁住id=1的一行记录。
Session 2:
//自动开启事务
Select * from t1 where id = 3 for update;
第二个事务，执行sql,锁住id=3的一行记录。
根据排它锁的定义，理论上应该是可以执行成功的。但验证结果，是session2中加锁失败。
说明两个加锁是相互冲突的。现在是对已存在的记录进行加锁失败，那么，新插入一条数据，看是否能成功。
Insert into `t1` (`id`,`name`) values(5,’5’);
结果说明，也是执行失败。
原因是：加锁是锁定整张表
6.3.2.主键索引表验证
前提：t2表中有4条记录，t2表中id设置为主键。Id=1,4,7,10
建表语句如下：
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`name` varchar(255) DEFAULT NULL,
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
有两个会话：
Session 1:
Begin;
Select * from t2 where id = 1 for update;
Session 2:
//自动开启事务
Select * from t2 where id = 1 for update; //两个session对同一行数据查询，锁冲突，不能加锁成功。
Select * from t2 where id = 4 for update; //两个session对不同行数据查询，加锁成功。
结论：
主键表，看起来是对行锁定。
6.3.2.索引表列字段锁定验证
前提：
存在表t3，表结构如下：
CREATE TABLE `t3` (
`name` varchar(255) DEFAULT NULL,
`id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
有两个会话：
Session 1:
//通过唯一索引锁定
Begin;
Select * from t3 where name= ‘4’ for update; //锁定name
Session 2:
//自动开启事务
Select * from t3 where name= ‘4’ for update; //阻塞
Select * from t3 where id = 4 for update; //阻塞
Select * from t3 where id = 1 for update;
原理探究：
1.不使用索引 t1表------锁定全表
2.使用主键索引 t2----锁定主键索引行
3.唯一索引 t3----锁定唯一索引行
所以，数据库锁锁定的是索引。
一张表有没有可能没有索引呢？
不可能的。主键索引一定有。
》primary key
>unique key not null【第一个】作为主键索引
》隐藏ROWID
所以，当表中中没有索引时，只能进行全表扫描，就会把该表默认的[隐藏]ROWID全部锁住。就造成了锁表的现象。
在t3表中即存在主键索引id，也有唯一索引name【也是辅助索引】
所以，当对辅助索引name加锁时，由于InnoDB辅助索引检索数据特性，会把主键索引id也给锁住。
6.4.行锁的算法
即锁住了什么范围?
T2表中插入4行数据：
Insert into
6.4.1.区间的划分
6.4.1.1.记录Record
指主键的键值。
间隙Gap:
主键不存在的区间。
如果有n个record,就会有n+1个gap
临键区间：
主键不存在的区间和临界主键组成的区间。
思考：
字符可以排序吗？
使用ascII码排序。
6.4.2.记录锁（Record Lock）:锁定记录
唯一性索引（唯一/主键）等值查询，精确匹配
Select * from t2 where id = 4 for update; //锁住id = 4
6.4.3.间隙锁Gap Lock:锁定范围
锁住的是数据不存在的区间。
Select * from t2 where id > 4 and id < 7 for update; //锁住（4，7）
Select * from t2 where id = 6 for update; //锁住（4，7）
Select * from t2 where id >20 for update; //锁住（10，+∞）
注意：
间隙锁之间不冲突，它会阻塞插入。
如：
存在id【1，4，7，10】的记录表中，4和7之间5和6不存在，
当查询Select * from t2 where id = 6 for update;执行完成，间隙锁生效。
再使用另一事务执行
Insert into t2 (id,name) values(5,”5”);
Insert into t2 (id,name) values(6,”6”);
两条记录插入操作，会执行失败。阻塞插入。
而同样执行，Select * from t2 where id = 6 for update可以成功。
注意：
当执行Select * from t2 where id >20 for update;时，实际的锁定区间为（10，+∞），所以当执行Insert into t2 (id,name) values(11,”11”);失败，受到阻塞。
6.4.4.临键锁Next-Key Lock
它是InnoDB引擎默认的行锁算法，条件：范围查询，包含记录和区间
Select * from t2 where id >5 and id < 9 for update;//锁住(4,7]，（7，10】
Next-Key Lock = Gap Lock + Record Lock
InnoDB类型表，可重复读【Repeated Read】底层就已经解决了幻读问题，是什么原理？
解决方案：就是阻塞其他事务插入数据，就是使用了临键锁。【锁住数据不存在的区间】
6.4.5.事务隔离级别的实现
Read Uncommited读未提交
不加锁-----会出现严重事务干扰
Serializable
所有的select语句都会被隐式的转换为select …in share mode,会和update,insert
6.4.6.事务隔离级别的实现
|
|
RR可重复读 |
RC 读已提交 |
|
普通的select |
MVCC |
MVCC |
|
加锁的select和更新 Select …in share mode Select …for update Insert Update delete |
Record Lock Gap Lock Next-key Lock |
Record Lock |
RC的额外好处：
Update或delete操作时，如果没有命名索引，只会锁住行。
6.4.7.事务隔离级别如何选择
1.RR的间隙锁会导致锁定范围的扩大。
2.条件列未使用到索引，RR锁表，RC锁行
3.RC的“半一致性”(semi-consistent)读可以增加update操作的并发性。
6.4.8.死锁的发生
1）互斥
2）不可剥夺
3）形成等待环路
show VARIABLES like 'innodb_lock_wait_timeout';
当一个事务获取到锁，另一个事务尝试获取锁，但会被阻塞，这个事务会一直等待吗？
显然不会，会设置一个阻塞超时时间，如果超过这个时间，就会放弃获取锁。
默认超时时间为50s.
7.Mysql性能优化总结【上】
时长：1h6min
计划：2020/4/2 22：28 –23：28
课程目标
掌握 MySQL 数据库优化的层次和思路
掌握 MySQL 数据库优化的工具。
内容定位
适合学习了 MySQL 架构、MySQL 索引、MySQL 事务的同学。
集中答疑链接：https://gper.club/articles/7e7e7f7ff7g55gc9g6b
7.1 优化思路
作为架构师或者开发人员，说到数据库性能优化，你的思路是什么样的？
或者具体一点，如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库，
你会怎么回答？
我们在第一节课开始的时候讲了，这四节课的目标是为了让大家建立数据库的知识
体系，和正确的调优的思路。
我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的
动作又是由很多个环节组成的，每个环节都会消耗时间，我们在第一节课讲 SQL 语句的
执行流程的时候已经分析过了。
我们要减少查询所消耗的时间，就要从每一个环节入手。
7.2 连接——配置优化
第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题？
有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error
1040: Too many connections 的错误。
我们可以从两个方面来解决连接数不够的问题：
1、从服务端来说，我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：
（1）修改配置参数增加可用连接数，修改 max_connections 的大小：
show variables like 'max_connections'; -- 修改最大连接数， 当有多个应用连接的时候
（2）或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时
间都是 28800 秒，8 小时，我们可以把这个值调小。
show global variables like 'wait_timeout'; --及时释放不活动的连接， 注意不要释放连接池还在使用的连接
2、从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行
SQL 都创建一个新的连接，应该怎么做？
这个时候我们可以引入连接池，实现连接的重用。
我们可以在哪些层面使用连接池？
ORM 层面（MyBatis 自带了一个连接池）；
或者使用专用的连接池工具（阿里的 Druid、Spring Boot 2.x 版本默认的连接池 Hikari、老牌的 DBCP 和 C3P0）。
当客户端改成从连接池获取连接之后，连接池的大小应该怎么设置呢？大家可能会有一个误解，觉得连接池的最大连接数越大越好，这样在高并发的情况下客户端可以获取的连接数更多，不需要排队。
实际情况并不是这样。连接池并不是越大越好，只要维护一定数量大小的连接池，
其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。
Druid 的默认最大连接池大小是 8。Hikari 的默认最大连接池大小是 10。
为什么默认值都是这么小呢？
在 Hikari 的 github 文档中，给出了一个 PostgreSQL 数据库建议的设置连接池大小
的公式：
https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
它的建议是机器核数乘以 2 加 1。也就是说，4 核的机器，连接池维护 9 个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。这里面还有一个减少连接池大小实现提升并发度和吞吐量的案例。
为什么有的情况下，减少连接数反而会提升吞吐量呢？为什么建议设置的连接池大小要跟 CPU 的核数相关呢？
每一个连接，服务端都需要创建一个线程去处理它。连接数越多，服务端创建的线程数就会越多。
问题：CPU 是怎么同时执行远远超过它的核数大小的任务的？
时间片。上下文切换。
而 CPU 的核数是有限的，频繁的上下文切换会造成比较大的性能开销。
我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括 CPU、内存、磁盘、网络。
在不同的硬件环境下，操作系统和 MySQL 的参数的配置是不同的，没有标准的配置。
在我们这几天的课程里面也接触了很多的 MySQL 和 InnoDB 的配置参数，包括各种开关和数值的配置，大多数参数都提供了一个默认值，比如默认的 buffer_pool_size，默认的页大小，InnoDB 并发线程数等等。
这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义的情况下再去修改它。修改配置的工作一般由专业的 DBA 完成。
至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的 CPU 型号这
些，更不是我们开发人员关注的重点，这个我们就不做过多的介绍了。
如果想要了解一些特定的参数的含义，官网有一份系统的参数列表可以参考：
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
除了合理设置服务端的连接数和客户端的连接池大小之外，我们还有哪些减少客户端跟数据库服务端的连接数的方案呢？
我们可以引入缓存。
7.3 缓存——架构优化
7.3.1 缓存
在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。
我们可以用第三方的缓存服务来解决这个问题，例如 Redis。
运行独立的缓存服务，属于架构层面的优化。
为了减少单台数据库服务器的读写压力，在架构层面我们还可以做其他哪些优化措施？
7.3.2 主从复制
如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。
集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？
这个时候我们需要用到复制技术（replication），被复制的节点称为 master，复制
的节点称为 slave。slave 本身也可以作为其他节点的数据来源，这个叫做级联复制。
主从复制是怎么实现的呢？
更新语句会记录 binlog，它是一种逻辑日志。
有了这个 binlog，从服务器会获取主服务器的 binlog 文件，然后解析里面的 SQL
语句，在从服务器上面执行一遍，保持主从的数据一致。
这里面涉及到三个线程，连接到 master 获取 binlog，并且解析 binlog 写入中继日
志，这个线程叫做 I/O 线程。
Master 节点上有一个 log dump 线程，是用来发送 binlog 给 slave 的。
从库的 SQL 线程，是用来读取 relay log，把数据写入到数据库的。
做了主从复制的方案之后，我们只把数据写入 master 节点，而读的请求可以分担到slave 节点。我们把这种方案叫做读写分离。
读写分离可以一定程度减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。如果我们在 master 写入了，马上到 slave 查询，而这个时候 slave 的数据还没有同步过来，怎么办？
所以，基于主从复制的原理，我们需要弄明白，主从复制到底慢在哪里？
7.3.2.1 单线程
在早期的 MySQL 中，slave 的 SQL线程是单线程。master 可以支持 SQL 语句的并行执行，配置了多少的最大连接数就是最多同时多少个 SQL 并行执行。
而 slave 的 SQL 却只能单线程排队执行，在主库并发量很大的情况下，同步数据肯
定会出现延迟。
为什么从库上的 SQL Thread 不能并行执行呢？举个例子，主库执行了多条 SQL 语句，首先用户发表了一条评论，然后修改了内容，最后把这条评论删除了。这三条语句
在从库上的执行顺序肯定是不能颠倒的。
insert into user_comments (10000009,'nice');
update user_comments set content ='very good' where id =10000009;
delete from user_comments where id =10000009;
怎么解决这个问题呢？怎么减少主从复制的延迟？
7.3.2.2 异步与全同步
首先我们需要知道，在主从复制的过程中，MySQL 默认是异步复制的。也就是说，对于主节点来说，写入 binlog，事务结束，就返回给客户端了。
对于 slave 来说，接收到 binlog，就完事儿了，master 不关心 slave 的数据有没有写入成功。
如果要减少延迟，是不是可以等待全部从库的事务执行完毕，才返回给客户端呢？
这样的方式叫做全同步复制。从库写完数据，主库才返会给客户端这种方式虽然可以保证在读之前，数据已经同步成功了，但是带来的副作用大家应该能想到，事务执行的时间会变长，它会导致 master 节点性能下降。
有没有更好的办法呢？既减少 slave 写入的延迟，又不会明显增加 master 返回给客
户端的时间？
7.3.2.3 半同步复制
介于异步复制和全同步复制之间，还有一种半同步复制的方式。
半同步复制是什么样的呢？
主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到 binlog 并写到 relay log 中才返回给客户端。
master 不会等待很长的时间，但是返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取 relay log，写入从库。
如果我们要在数据库里面用半同步复制，必须安装一个插件，这个是谷歌的一位工程师贡献的。这个插件在 mysql 的插件目录下已经有提供：
cd /usr/lib64/mysql/plugin/
主库和从库是不同的插件，安装之后需要启用：
-- 主库执行
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_enabled=1;
show variables like '%semi_sync%';
-- 从库执行
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;
show global variables like '%semi%';
相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，它需要等待一个 slave 写入中继日志，这里多了一个网络交互的过程，所以，半同步复制最好在低延时的网络中使用。
这个是从主库和从库连接的角度，来保证 slave 数据的写入。
另一个思路，如果要减少主从同步的延迟，减少 SQL 执行造成的等待的时间，那有
没有办法在从库上，让多个 SQL 语句可以并行执行，而不是排队执行呢？
7.3.2.4 多库并行复制
怎么实现并行复制呢？设想一下，如果 3 条语句是在三个数据库执行，操作各自的
数据库，是不是肯定不会产生并发的问题呢？执行的顺序也没有要求。当然是，所以如
果是操作三个数据库，这三个数据库的从库的 SQL 线程可以并发执行。这是 MySQL 5.6
版本里面支持的多库并行复制。
但是在大部分的情况下，我们都是单库多表的情况，在一个数据库里面怎么实现并行复制呢？或者说，我们知道，数据库本身就是支持多个事务同时操作的；为什么这些事务在主库上面可以并行执行，却不会出现问题呢？
因为他们本身就是互相不干扰的，比如这些事务是操作不同的表，或者操作不同的
行，不存在资源的竞争和数据的干扰。那在主库上并行执行的事务，在从库上肯定也是
可以并行执行，是不是？比如在 master 上有三个事务同时分别操作三张表，这三个事务
是不是在 slave 上面也可以并行执行呢？
7.3.2.5 异步复制之 GTID 复制
https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html
所以，我们可以把那些在主库上并行执行的事务，分为一个组，并且给他们编号，这一个组的事务在从库上面也可以并行执行。这个编号，我们把它叫做 GTID（Global Transaction Identifiers），这种主从复制的方式，我们把它叫做基于 GTID 的复制。
如果我们要使用 GTID 复制，我们可以通过修改配置参数打开它，默认是关闭的：
show global variables like 'gtid_mode';
无论是优化 master 和 slave 的连接方式，还是让从库可以并行执行 SQL，都是从数
据库的层面去解决主从复制延迟的问题。
除了数据库本身的层面之外，在应用层面，我们也有一些减少主从同步延迟的方法。
我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过大的时候，比如一张表有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据分型拆分，这个就是分库分表。
7.3.3 分库分表
垂直分库，减少并发压力。水平分表，解决存储瓶颈。
垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：
水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。
通过主从或者分库分表可以减少单个数据库节点的访问压力和存储压力，达到提升
数据库性能的目的，但是如果 master 节点挂了，怎么办？
所以，高可用（High Available）也是高性能的基础。
7.3.4 高可用方案
https://dev.mysql.com/doc/mysql-ha-scalability/en/ha-overview.html
7.3.4.1 主从复制
传统的 HAProxy + keepalived 的方案，基于主从复制。
7.3.4.2 NDB Cluster
https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-overview.html
基于 NDB 集群存储引擎的 MySQL Cluster。
7.3.4.3 Galera
一种多主同步复制的集群方案。
7.3.4.4 MHA/MMM
https://tech.meituan.com/2017/06/29/database-availability-architecture.html
MMM（Master-Master replication manager for MySQL），一种多主的高可用
架构，是一个日本人开发的，像美团这样的公司早期也有大量使用 MMM。
MHA（MySQL Master High Available）。
MMM 和 MHA 都是对外提供一个虚拟 IP，并且监控主节点和从节点，当主节点发生故障的时候，需要把一个从节点提升为主节点，并且把从节点里面比主节点缺少的数据补上，把 VIP 指向新的主节点。
7.3.4.5 MGR
https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html
MySQL 5.7.17 版本推出的 InnoDB Cluster，也叫 MySQL Group Replicatioin
（MGR），这个套件里面包括了 mysql shell 和 mysql-route。
总结一下：
高可用 HA 方案需要解决的问题都是当一个 master 节点宕机的时候，如何提升一个数据最新的 slave 成为 master。如果同时运行多个 master，又必须要解决 master 之间数据复制，以及对于客户端来说连接路由的问题。
不同的方案，实施难度不一样，运维管理的成本也不一样。
以上是架构层面的优化，可以用缓存，主从，分库分表。
第三个环节：
解析器，词法和语法分析，主要保证语句的正确性，语句不出错就没问题。由 Sever
自己处理，跳过。
第四步：优化器
7.4 优化器——SQL 语句分析与优化
优化器就是对我们的 SQL 语句进行分析，生成执行计划。
问题：在我们做项目的时候，有时会收到 DBA 的邮件，里面列出了我们项目上几个
耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？
我们的服务层每天执行了这么多 SQL 语句，它怎么知道哪些 SQL 语句比较慢呢？
第一步，我们要把 SQL 执行情况记录下来。
7.4.1 慢查询日志 slow query log
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
7.4.1.1 打开慢日志开关
因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：
show variables like 'slow_query%';
除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。
show variables like '%slow_query%';
可以直接动态修改参数（重启后失效）。
set @@global.slow_query_log=1; -- 1 开启， 0 关闭， 重启后失效
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒， 另开一个窗口后才会查到最新值
show variables like '%long_query%';
show variables like '%slow_query%';
或者修改配置文件 my.cnf。
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。
slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log
模拟慢查询：
select sleep(10);
查询 user_innodb 表的 500 万数据（检查是不是没有索引）。
SELECT * FROM `user_innodb` where phone = '136';
7.4.1.2 慢日志分析
1、日志内容
show global status like 'slow_queries'; -- 查看有多少慢查询
show variables like '%slow_query%'; -- 获取慢日志目录
cat /var/lib/mysql/ localhost-slow.log
有了慢查询日志，怎么去分析统计呢？比如 SQL 语句的出现的慢查询次数最多，平
均每次执行了多久？
2、mysqldumpslow
https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。
mysqldumpslow --help
例如：查询用时最多的 20 条慢 SQL：
mysqldumpslow -s t -t 20
-g 'select' /var/lib/mysql/localhost-slow.log
Count 代表这个 SQL 执行了多少次；
Time 代表执行的时间，括号里面是累计时间；
Lock 表示锁定的时间，括号是累计；
Rows 表示返回的记录数，括号是累计。
除了慢查询日志之外，还有一个 SHOW PROFILE 工具可以使用。
7.4.2 SHOW PROFILE
https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
SHOW PROFILE 是谷歌高级架构师 Jeremy Cole 贡献给 MySQL 社区的，可以查看
SQL 语句执行的时候使用的资源，比如
CPU、IO 的消耗情况。
在 SQL 中输入 help profile 可以得到详细的帮助信息。
7.4.2.1 查看是否开启
select @@profiling;
set @@profiling=1;
7.4.2.2 查看 profile 统计
（命令最后带一个 s）
show profiles;
查看最后一个 SQL 的执行详细信息，从中找出耗时较多的环节（没有 s）。
show profile;
6.2E-5，小数点左移 5 位，代表 0.000062 秒。
也可以根据 ID 查看执行详细信息，在后面带上 for query + ID。
show profile for query 1;
除了慢日志和 show profile，如果要分析出当前数据库中执行的慢的 SQL，还可以
通过查看运行线程状态和服务器运行信息、存储引擎信息来分析。
7.4.2.3 其他系统命令
show processlist 运行线程
https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html
show processlist;
这是很重要的一个命令，用于显示用户运行线程。可以根据 id 号 kill 线程。
也可以查表，效果一样：
select * from information_schema.processlist;
|
列 |
含义 |
|
Id |
线程的唯一标志， 可以根据它 kill 线程 |
|
User |
启动这个线程的用户， 普通用户只能看到自己的线程 |
|
Host |
哪个 IP 端口发起的连接 |
|
db |
操作的数据库 |
|
Command |
线程的命令 https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html |
|
Time |
操作持续时间， 单位秒 |
|
State |
线程状态， 比如查询可能有 copying to tmp table， Sorting result， Sending data https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html |
|
Info |
SQL 语句的前 100 个字符， 如果要查看完整的 SQL 语句， 用 SHOW FULL PROCESSLIST |
show status 服务器运行状态
https://dev.mysql.com/doc/refman/5.7/en/show-status.html
SHOW STATUS 用于查看 MySQL 服务器运行状态（重启后会清空），有 session和 global 两种作用域，格式：参数-值。
可以用 like 带通配符过滤。
SHOW GLOBAL STATUS LIKE 'com_select'; -- 查看 select 次数
show engine 存储引擎运行信息
https://dev.mysql.com/doc/refman/5.7/en/show-engine.html
show engine 用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；
事务的锁等待情况；线程信号量等待；文件 IO 请求；buffer
pool 统计信息。
例如：
show engine innodb status;
如果需要将监控信息输出到错误信息
error log 中（15 秒钟一次），可以开启输出。
show variables like 'innodb_status_output%';
-- 开启输出：
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
我们现在已经知道了这么多分析服务器状态、存储引擎状态、线程运行信息的命令，如果让你去写一个数据库监控系统，你会怎么做？
其实很多开源的慢查询日志监控工具，他们的原理其实也都是读取的系统的变量和状态。
现在我们已经知道哪些 SQL 慢了，为什么慢呢？慢在哪里？
MySQL 提供了一个执行计划的工具（在架构中我们有讲到，优化器最终生成的就是一个执行计划），其他数据库，例如 Oracle 也有类似的功能。
通过 EXPLAIN 我们可以模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。
explain 可以分析 update、delete、insert 么？
MySQL 5.6.3以前只能分析 SELECT; MySQL5.6.3以后就可以分析update、delete、insert 了。
7.4.3 EXPLAIN 执行计划
官方链接：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。
DROP TABLE IF EXISTS course;
CREATE TABLE `course` (
`cid` int(3)
DEFAULT NULL,
`cname` varchar(20)
DEFAULT NULL,
`tid` int(3)
DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS teacher;
CREATE TABLE `teacher` (
`tid` int(3)
DEFAULT NULL,
`tname` varchar(20)
DEFAULT NULL,
`tcid` int(3)
DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS teacher_contact;
CREATE TABLE `teacher_contact` (
`tcid` int(3)
DEFAULT NULL,
`phone` varchar(200)
DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `course` VALUES ('1', 'mysql', '1');
INSERT INTO `course` VALUES ('2', 'jvm', '1');
INSERT INTO `course` VALUES ('3', 'juc', '2');
INSERT INTO `course` VALUES ('4', 'spring', '3');
INSERT INTO `teacher` VALUES ('1', 'qingshan', '1');
INSERT INTO `teacher` VALUES ('2', 'jack', '2');
INSERT INTO `teacher` VALUES ('3', 'mic', '3');
INSERT INTO `teacher_contact` VALUES ('1', '13688888888');
INSERT INTO `teacher_contact` VALUES ('2', '18166669999');
INSERT INTO `teacher_contact` VALUES ('3', '17722225555');
explain 的结果有很多的字段，我们详细地分析一下。
先确认一下环境：
select version();
show variables like '%engine%';
7.4.3.1 id
id 是查询序列编号。
id 值不同
id 值不同的时候，先查询 id 值大的（先大后小）。
-- 查询 mysql 课程的老师手机号
EXPLAIN SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (
SELECT tcid
FROM teacher t
WHERE t.tid = (
SELECT c.tid
FROM course c
WHERE c.cname = 'mysql'
));
查询顺序：course c——teacher t——teacher_contact tc。
先查课程表，再查老师表，最后查老师联系方式表。子查询只能以这种方式进行，只有拿到内层的结果之后才能进行外层的查询。
id 值相同
-- 查询课程 ID 为 2， 或者联系表 ID 为 3 的老师
EXPLAIN
SELECT t.tname,c.cname,tc.phone
FROM teacher t, course c, teacher_contact
tc
WHERE t.tid = c.tid
AND t.tcid
= tc.tcid
AND (c.cid
= 2
OR tc.tcid
= 3);
id 值相同时，表的查询顺序是从上往下顺序执行。例如这次查询的 id 都是 1，查询
的顺序是 teacher t（3 条）——course c（4 条）——teacher_contact tc（3 条）。
teacher 表插入 3 条数据后：
INSERT INTO `teacher` VALUES (4, 'james', 4);
INSERT INTO `teacher` VALUES (5, 'tom', 5);
INSERT INTO `teacher` VALUES (6, 'seven', 6);
COMMIT;
-- （ 备份） 恢复语句
DELETE FROM teacher where tid in (4,5,6);
COMMIT;
id 也都是 1，但是从上往下查询顺序变成了：teacher_contact tc（3 条）——teacher
t（6 条）——course c（4 条）。
为什么数据量不同的时候顺序会发生变化呢？这个是由笛卡尔积决定的。
举例：假如有 a、b、c 三张表，分别有 2、3、4 条数据，如果做三张表的联合查询，
当查询顺序是 a→b→c 的时候，它的笛卡尔积是：2*3*4=6*4=24。如果查询顺序是 c
→b→a，它的笛卡尔积是 4*3*2=12*2=24。
因为 MySQL 要把查询的结果，包括中间结果和最终结果都保存到内存，所以 MySQL
会优先选择中间结果数据量比较小的顺序进行查询。所以最终联表查询的顺序是 a→b→
c。这个就是为什么 teacher 表插入数据以后查询顺序会发生变化。
（小标驱动大表的思想）
既有相同也有不同
如果 ID 有相同也有不同，就是 ID 不同的先大后小，ID 相同的从上往下。
7.4.3.2 select type 查询类型
这里并没有列举全部（其它：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。
下面列举了一些常见的查询类型：
SIMPLE
简单查询，不包含子查询，不包含关联查询 union.
EXPLAIN SELECT * FROM teacher;
再看一个包含子查询的案例：
-- 查询 mysql 课程的老师手机号
EXPLAIN SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (
SELECT tcid
FROM teacher t
WHERE t.tid = (
SELECT c.tid
FROM course c
WHERE c.cname = 'mysql'
);
PRIMARY
子查询 SQL 语句中的主查询，也就是最外面的那层查询。
SUBQUERY
子查询中所有的内层查询都是 SUBQUERY 类型的。
DERIVED
衍生查询，表示在得到最终查询结果之前会用到临时表。例如：
-- 查询 ID 为 1
或 2 的老师教授的课程
EXPLAIN SELECT cr.cname
FROM (
SELECT * FROM course WHERE
tid = 1
UNION
SELECT * FROM course
WHERE tid = 2
) cr;
对于关联查询，先执行右边的 table（UNION），再执行左边的 table，类型是DERIVED。
UNION
用到了 UNION 查询。同上例。
UNION RESULT
主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询
存在 UNION。同上例。
7.4.3.3 type 连接类型
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-type
所有的连接类型中，上面的最好，越往下越差。
在常用的连接类型中：system > const > eq_ref > ref > range > index > all
这 里 并 没 有 列 举 全 部 （ 其 他 ： fulltext 、 ref_or_null 、 index_merger 、
unique_subquery、index_subquery）。
以上访问类型除了 all，都能用到索引。
const
主键索引或者唯一索引，只能查到一条数据的 SQL。
DROP TABLE IF EXISTS single_data;
CREATE TABLE single_data(
id int(3) PRIMARY KEY,
content varchar(20)
);
insert into single_data values(1,'a');
EXPLAIN SELECT * FROM single_data a where id = 1;
system
system 是 const 的一种特例，只有一行满足条件。例如：只有一条数据的系统表。
EXPLAIN SELECT * FROM mysql.proxies_priv;
eq_ref
通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的
一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。
eq_ref 是除 const 之外最好的访问类型。
先删除 teacher 表中多余的数据，teacher_contact 有 3 条数据，teacher 表有 3条数据。
DELETE FROM teacher where tid in (4,5,6);
commit;
-- 备份
INSERT INTO `teacher` VALUES (4, 'james', 4);
INSERT INTO `teacher` VALUES (5, 'tom', 5);
INSERT INTO `teacher` VALUES (6, 'seven', 6);
commit;
为 teacher_contact 表的 tcid（第一个字段）创建主键索引。
-- ALTER TABLE teacher_contact DROP PRIMARY KEY;
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
为 teacher 表的 tcid（第三个字段）创建普通索引。
-- ALTER TABLE teacher DROP INDEX idx_tcid;
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
执行以下 SQL 语句：
select t.tcid from teacher t,teacher_contact tc where
t.tcid =
tc.tcid;
此时的执行计划（teacher_contact 表是 eq_ref）：
小结：
以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个
状态。
ref
查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
例如：使用 tcid 上的普通索引查询：
explain SELECT * FROM teacher where tcid = 3;
range
索引范围扫描。
如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些，type 类型就为 range。
不走索引一定是全表扫描（ALL），所以先加上普通索引。
-- ALTER TABLE teacher DROP INDEX idx_tid;
ALTER TABLE teacher ADD INDEX idx_tid (tid);
执行范围查询（字段上有普通索引）：
EXPLAIN SELECT * FROM teacher t WHERE
t.tid <3;
-- 或
EXPLAIN SELECT * FROM teacher t WHERE
tid BETWEEN 1
AND 2;
IN 查询也是 range（字段有主键索引）
EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);
index
Full Index Scan，查询全部索引中的数据（比不走索引要快）。
EXPLAIN SELECT tid FROM teacher;
all
Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。
NULL
不用访问表或者索引就能得到结果，例如：
EXPLAIN select 1 from dual where 1=1;
小结：
一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。
ALL（全表扫描）和 index（查询全部索引）都是需要优化的。
7.4.3.4 possible_key、 key
可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。
possible_key 可以有一个或者多个，可能用到索引不代表一定用到索引。
反过来，possible_key 为空，key 可能有值吗？
表上创建联合索引：
ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone
(name,phone);
执行计划（改成 select name 也能用到索引）：
explain select phone from user_innodb where
phone='126';
结论：是有可能的（这里是覆盖索引的情况）。
如果通过分析发现没有用到索引，就要检查 SQL 或者创建索引。
7.4.3.5 key_len
索引的长度（使用的字节数）。跟索引字段的类型、长度有关。
7.4.3.6 rows
MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越
好。
7.4.3.7 filtered
这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数
量的比例，它是一个百分比。
7.4.3.8 ref
使用哪个列或者常数和索引一起从表中筛选数据。
7.4.3.9 Extra
执行计划给出的额外的信息说明。
using index
用到了覆盖索引，不需要回表。
EXPLAIN SELECT tid FROM teacher ;
using where
使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要
在 server 层进行过滤（跟是否使用索引没有关系）。
EXPLAIN select * from user_innodb where phone ='13866667777';
Using index condition（ 索引条件下推）
索引下推，在第二节课中已经讲解过了。
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
using filesort
不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。
（复合索引的前提）
ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone
(name,phone);
EXPLAIN select * from user_innodb where
name ='青山' order by id;
using temporary
用到了临时表。例如（以下不是全部的情况）：
1、distinct 非索引列
EXPLAIN select DISTINCT(tid) from teacher t;
2、group
by 非索引列
EXPLAIN select tname from teacher group
by tname;
3、使用 join
的时候，group
任意列
EXPLAIN select t.tid from teacher
t join course c on
t.tid =
c.tid group by t.tid;
需要优化，例如创建复合索引。
总结一下：
模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。
通过这种方式我们可以分析语句或者表的性能瓶颈。
分析出问题之后，就是对 SQL 语句的具体优化。
比如怎么用到索引，怎么减少锁的阻塞等待，在前面两次课已经讲过。
7.4.4 SQL 与索引优化
当我们的 SQL 语句比较复杂，有多个关联和子查询的时候，就要分析 SQL 语句有没
有改写的方法。
举个简单的例子，一模一样的数据：
-- 大偏移量的 limit
select * from user_innodb limit
900000,10;
-- 改成先过滤 ID， 再 limit
SELECT * FROM user_innodb WHERE
id >= 900000 LIMIT 10;
对于具体的 SQL 语句的优化，MySQL 官网也提供了很多建议，这个是我们在分析
具体的 SQL 语句的时候需要注意的，也是大家在以后的工作里面要去慢慢地积累的（这
里我们就不一一地分析了）。
https://dev.mysql.com/doc/refman/5.7/en/optimization.html
7.5 存储引擎
7.5.1 存储引擎的选择
为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用 MyISAM。
临时数据用 Memeroy。常规的并发大更新多的表用 InnoDB。
7.5.2 分区或者分表
分区不推荐。
交易历史表：在年底为下一年度建立 12 个分区，每个月一个分区。
渠道交易表：分成当日表；当月表；历史表，历史表再做分区。
7.5.3 字段定义
原则：使用可以正确存储数据的最小数据类型。
为每一列选择合适的字段类型：
7.5.3.1 整数类型
INT 有 8 种类型，不同的类型的最大存储范围是不一样的。
性别？用 TINYINT，因为 ENUM 也是整型存储。
7.5.3.2 字符类型
变长情况下，varchar 更节省空间，但是对于 varchar 字段，需要一个字节来记录长
度。
固定长度的用 char，不要用 varchar。
7.5.3.3 非空
非空字段尽量定义成 NOT NULL，提供默认值，或者使用特殊值、空串代替 null。
NULL 类型的存储、优化、使用都会存在问题。
7.5.3.4 不要用外键、 触发器、 视图
降低了可读性；
影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储；
数据的完整性应该在程序中检查。
7.5.3.5 大文件存储
不要用数据库存储图片（比如 base64 编码）或者大文件；
把文件放在 NAS 上，数据库只需要存储 URI（相对路径），在应用中配置 NAS 服
务器地址。
7.5.3.6 表拆分
将不常用的字段拆分出去，避免列数过多和数据量过大。
比如在业务系统中，要记录所有接收和发送的消息，这个消息是 XML 格式的，用
blob 或者 text 存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。
7.6 总结： 优化体系
除了对于代码、SQL 语句、表定义、架构、配置优化之外，业务层面的优化也不能
忽视。举几个例子：
1）在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动（充 300
送 50）？
因为使用余额或者余额宝付款是记录本地或者内部数据库，而使用银行卡付款，需
要调用接口，操作内部数据库肯定更快。
2）在去年的双十一，为什么在凌晨禁止查询今天之外的账单？
这是一种降级措施，用来保证当前最核心的业务。
3）最近几年的双十一，为什么提前一个多星期就已经有双十一当天的价格了？
预售分流。
在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比
如限流，或者引入 MQ 削峰，等等等等。
为什么同样用 MySQL，有的公司可以扛住百万千万级别的并发，而有的公司几百个
并发都扛不住，关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候
还要往上层去优化。
当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据
的方案了，并不是所有的数据都要放到关系型数据库存储。
集中答疑链接：https://gper.club/articles/7e7e7f7ff7g55gc9g6b
附录：
数据结构可视化模拟工具：
https://www.cs.usfca.edu/~galles/visualization/BST.html
