为什么要用B+ 树，而不用普通二叉树？ 为什么要用 B+ 树，而不用普通二叉树？可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数。为什么不用普通二叉树？普通二叉树存在退化的情况，如果它退化成链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。为什么不用平衡二叉树呢？读取数据的时候，是从磁盘读到内存。如果树这种数据结构作为索引，那每查找一次数据就需要从磁盘中读取一个节点，也就是一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B+ 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快。为什么用 B+ 树而不用 B 树呢？B+相比较B树，有这些优势：它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题：每个节点存储更多关键字；路数更多扫库、扫表能力更强如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据。B+Tree 的磁盘读写能力相对于 B Tree 来说更强，IO次数更少根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多，IO次数更少。排序能力更强因为叶子节点上有下一个数据区的指针，数据形成了链表。效率更加稳定B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的。Hash 索引和 B+ 树索引区别是什么？B+ 树可以进行范围查询，Hash 索引不能。B+ 树支持联合索引的最左侧原则，Hash 索引不支持。B+ 树支持 order by 排序，Hash 索引不支持。Hash 索引在等值查询上比 B+ 树效率更高。B+ 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

Docker搭建Replication分布式集群 什么是Replication集群Replication集群是MySQL自带的数据同步机制原理：MySQL通过读取、执行另外一个MySQL的bin_log日志，实现数据同步。流程：首先要开启MySQL的二进制文件 （bin_log）我们所使用MySQL的命令操作都会被记录到bin_log日志里。另外一个MySQL通过读取bin_log文件，就知道第一个MySQL节点执行了哪些操作。第二个MySQL操作还会在本地也执行一边，因为执行的操作都是相同的，所以2个MySQL的内容完全相同。这就是replication的数据同步原理。总结：Rep集群异步传输的，数据只要在一个节点写入，就算成功了，至于能不能同步到其他节点Replication不做任何保证。Rep集群数据同步是单向的，只能从主节点数据同步到从节点，不能从从节点同步到主节点。如果业务系统是读多写少非常大。我们可以给一个Master设置多个Slave。所有的读操作发送给Slave从节点执行。写操作 发送给Master主节点执行，就可实现数据读写分离的功能注意： 读写分离功能需要配置MyCat才能实现的实例演示这里先实例演示Replication集群的MyCat路由转发因为本地环境，首先先准备2台服务器，分了4个端口，做为4个虚拟主机，我这边使用Vragnt生成了2台Linux服务器。192.168.205.10 （作为主节点）192.168.205.11 （从节点）一、搭建服务器环境Vgrant搭配2台服务相关配置，不熟悉的同学可以查看下Vgrant官网文档，或者用其他虚拟机搭建都可。创建Vagrantfile 文件# -*- mode: ruby -*- # vi: set ft=ruby : Vagrant.require_version ">= 1.6.0" boxes = [ { :name => "swarm-manager", :eth1 => "192.168.205.10", :mem => "1024", :cpu => "1" }, { :name => "swarm-worker1", :eth1 => "192.168.205.11", :mem => "1024", :cpu => "1" } ] Vagrant.configure(2) do |config| config.vm.box = "centos/7" boxes.each do |opts| config.vm.define opts[:name] do |config| config.vm.hostname = opts[:name] config.vm.provider "vmware_fusion" do |v| v.vmx["memsize"] = opts[:mem] v.vmx["numvcpus"] = opts[:cpu] end config.vm.provider "virtualbox" do |v| v.customize ["modifyvm", :id, "--memory", opts[:mem]] v.customize ["modifyvm", :id, "--cpus", opts[:cpu]] end config.vm.network :private_network, ip: opts[:eth1] end end #config.vm.synced_folder "./labs", "/home/vagrant/labs" # config.vm.provision "shell", privileged: true, path: "./setup.sh" end :::然后在当前Vgrantfilefile同目录下，运行启动vagrant up启动完成，输入ssh xxx进入虚拟主机里vagrant ssh swarm-manager进入主机后，配置下SSH，在外部的ssh终端也可远程连接。（进入虚拟主机是Vgrant角色，我们需要用root角色）切换到root账号 （root默认密码是vagrant） ，进入ssh目录，配置sshd_config ，修改root也可远程登录PermitRootLogin yes #root可登录 PubkeyAuthentication yes PasswordAuthentication yes以上这几个注释解开，并且重启sshd服务systemctl restart sshd这样就可以用xhsell 或iTimer2 、Termius等工具去远程连接了。配置Docker环境下载并安装dockeryum install -y docker因为仓库在国外，有些镜像下载比较慢，我们还需要配置镜像加速curl -sSL https://get.daocloud.io/daotools/set-mirror.sh | sh -s http://f1361db2.m.daocloud.io编辑/ect/docker/daemon.json文件，把结尾的逗号去掉（不然启动会报错）集群MySQL需要分布式部署环境，Docker Swarm技术可以自动把异地的Dcoker虚拟机组成一个局域网我们只需要在主节点里，输入docker swarm init --advertise-addr=192.168.205.10如果我们之前有连接相关的集群，需要强制解散swarm集群（包括从节点也要输入 ），输入：docker swarm leave -f 创建个共享网络，作为MySQL的集群网络docker network create -d overlay --attachable swarm_mysql下载Replication镜像 （Oracle没有提供官网镜像，用第三方封装的镜像）docker pull mishamx/mysql docker tag mishamx/mysql rep docker rmi mishamx/mysql搭建Replication集群环境开放防火墙端口，并且重启docker服务firewall-cmd --zone=public --add-port=9003/tcp --permanent firewall-cmd --zone=public --add-port=9004/tcp --permanent firewall-cmd --reload创建主节点容器docker run -d -p 9003:3306 --name rn1 -e MYSQL_MASTER_PORT=3306 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_REPLICATION_USER=backup -e MYSQL_REPLICATION_PASSWORD=backup123456 -v rnv1:/var/lib/mysql --privileged --net=swarm_mysql rep 创建主节点容器，去客户端尝试连接是否成功？创建从节点容器docker run -d -p 9003:3306 --name rn2 -e MYSQL_MASTER_HOST=rn1 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_REPLICATION_USER=backup -e MYSQL_REPLICATION_PASSWORD=backup123456 -v rn2:/var/lib/mysql --privileged --net=swarm_mysql rep 注意事项： 从节点需要与主节点同步，没有主节点不能创建从节点也去客户端数据库工具尝试是否连接成功。验证主从节点是否同步先去主节点rn1下，创建一个逻辑库，并且创建一个user表，写入数据， 从节点是否有同步过来？数据果真同步过来了。从节点写入数据，主节点会不会同步数据 ? 答案不会，因为rep集群是单向同步的，数据同步必须是由主节点同步给从节点搭建MyCat环境（读写分离）去官网进行下载最新版或者稳定版的Mycat http://dl.mycat.org.cn/2.0/ ，得到一个mycat.tar.gz包安装JavaJDK镜像，因为安装MyCat需要运行在java环境docker pull adoptopenjdk/openjdk8 docker tag adoptopenjdk/openjdk8 openjdk8 docker rm adoptopenjdk/openjdk8创建java容器docker run -d -it --name mycat1 -v mycat1:/root/server --privileged --net=host openjdk8把Mycat包，解压到java目录下/root/server/开放mycat需要用到的端口firewall-cmd --zone=public --add-port=9066/tcp --permanent #数据处理 （传入sql） firewall-cmd --zone=public --add-port=8066/tcp --permanent #获取mycay运行信息 firewall-cmd --reloadMyCat的主要配置目录查看Mycat目录下的文件，MyCat主要的配置文件，一共有3个重要的文件，分别： server.xml 、schema.xml 、rule.xmlserver.xml ： 配置MyCat端口、全局主键生成方式，虚拟逻辑库和账户Schema.xml ： 配置数据库连接，以及数据表用什么切分规则 （最重要）rule.xml ： 用户定制规则切分MyCat配置 Replication集群，需要把写操作发送给主节点执行，读操作发送给从节点执行。配置server.xml可以配置端口号、账户信息、全局主键方式等等<user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">test</property> </user>需要多个逻辑库就可以设置为<user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">test,shop,db1,db2</property> </user>MyCat不会保存到真实的库里，只会记录MyCat自己的配置文件。只需要配置<user>段，把多余的user删除，要不然启动报错user name ： 启动的角色property password ： 启动登录的密码property schemas ： 需要用到的库 （根据你实际业务看，我这用test库）配置schema.xml在这个文件里，要对每个MySQL节点的连接信息做设置，MyCat管理那么多个节点，首先要对这个节点做好工作上的划分。把读写任务分别发送给不同的节点执行，可以降低单一节点的负载PXC集群不需要设置读写分离，做好负载均衡即可Replication集群主从节点功能明确，需要做读写分离配置虚拟机逻辑库和关系表<schema>标签可以设置虚拟逻辑库，<table>标签可以设置虚拟关系表<schema name="test"> <table name="admin1" dataNode="dn1,dn2" type="global"></table> <table name="user" dataNode="dn1,dn2" type="mod-long"></table> </schema>user表配置了rule主键求模的切分数据。配置dataHost标签这个标签可以配置数据库集群的。 <dataHost name="rep1" maxCon="1000" minCon="10" balance="3" balanceType="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="r1w1" url="192.168.168.205.10:9003" user="root" password="123456"> <readHost host="r1r1" url="192.168.205.11:9003" password="123456" user="root" /> </writeHost> </dataHost>name ： 取别名maxCon 跟 minCon ： 代表最大连接数和最小连接数balance : 设置3，代表写节点和读节点功能分开，写节点负责写入请求，读节点负责读取请求。writeType : 读写请求是否发送可用的写节点 （1-是），MyCat自带心跳检测功能会想mysql定期发送sql语句，如果某一个节点没有响应了，那么会认为这个节点宕机了，就不会向宕机节点发送请求。dbType ： 代表什么数据库的集群dbDriver ： 使用mysql自带的native驱动SwitchType ： 会根据自身带有的心跳检测结果去判断哪一个mysql节点宕机了？slaveThreshold ： 跟rep集群有关，主从复制同步的，设置100代表100秒，如果从库超过主库的100秒就剔除从节点，不会从这个从节点读取数据。Heartbeat ： 监察心跳，连接是否正常的SQL，有返回响应就代表没宕机。writeHost ： 配置mysql写节点的 （有多少个节点就配置多个标签），图上有2个节点。<readHost> ： writeHost嵌套一个3个<readHost> ，代表3个读节点host： 读节点名称url : 读节点mysql 地址user : 读节点用户Password：读节点密码host ： 是节点的别名url : 节点请求mysql url地址user : 登录用户名Password ： 用户密码注意事项： 读节点一定要放入写节点里，如果有多个读节点，那么第一个读节点不能是闭合< />状态。3、配置多个集群分片配置多个集群分片就复制多个<dataHost> ，相当于多个集群。4、配置虚拟库和关系表因为Mycat并不存储数据，只是SQL语句的路由器，并不默认接管MySQL所有的数据表，所有必须要配置可以使用的虚拟逻辑库和关系表。<dataNode name="dn1" dataHost="rep1" database="test"> <dataNode name="dn2" dataHost="rep2" database="test" />意思就是要使用rep1节点的test数据库，并且起名为dn1。意思就是要使用rep2节点的test数据库，并且起名为dn2。我这用到2个分片，一个作为同步，一个作为切分转发配置rule.xmlMyCat默认的mod-long是按照3个分片切分数据，所以我们要求该这个默认值。设置成2个。<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property> </function>全局预览图由于我是用2台服务，用端口作为4个不同的虚拟主机节点。注意查看，我这有2个 dataHost 表示部署2个集群（2个分片），rep1=作为同步的， rep2=作为数据切分的rep1集群：<writeHost>写节点是192.168.205.10:9003 是之前容器生成的主节点。从节点是访问192.168.205.11:9003rep2集群192.168.205.10:9004是主启动MycatMyCat日志文件主要有console.log和mycat.log ，存放在logs目录 （不存在就手动创建）。为MyCat/bin 目录中所有的sh命令设置最高权限。chmod -R 777 ./*.sh启动./startup_nowrap.sh查看是否启动成功ps -a 因为mycat是基于是java， 如果包含java就表示启动成功

利用PerconaTookit工具在线修改表结构 在线修改表结构在线修改表结构必须慎重 ，在业务系统运行的过程中随意修改、删改字段，会造成重大事故，而且在修改之前，必须对表做备份。常规的做法是业务停机，维护表结构。（比如半夜做系统维护更新，12306、淘宝等..）但是不影响正常业务的表结构是运行在线修改的。（比如INT不够用换成BIGINT，或者某个字段有唯一性约束，现在去掉唯一性约束，但是也多多少少也会影响性能 ）ALTER TABLE修改表结构的弊病这些修改表是属于DDL语句由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作锁住表禁止一切的读写，如果给1千万数据表中添加一个字段，这个时间会稍微有点长 ，期间不能做任何读写操作，对线上业务影响非常大，数据越多锁表时间越长。如果修改表结构失败，必须还原表结构，所以耗时更长大数据表记录多，修改表结构锁表时间很久PerconaTookit工具利器Percona Toolkit简称pt工具—PT-Tools，是Percona公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等。pt-archive ----MySQL的在线归档，无影响生产pt-kill -----自定义查杀。确保慢查询及恶性攻击对生产无影响pt-online-schema-change ----在线DDL操作，对上亿的大表加索引加字段且对生产无影响pt-query-digest -----慢查询Log的分析。pt-slave-delay ---就是指定从库比主库延迟多长时间pt-table-checksum & pt-table-sync-----检查主从是否一致性-----检查主从不一致之后用这个工具进行处理 这两个一搬是搭配使用pt-find ---找出几天之前建立的表pt-slave-restart -----主从报错，跳过报错pt-summary ---整个系统的的概述pt-mysql-summary ---MySQL的表述，包括配置文件的描述pt-duplicate-key-checker ---检查数据库重复索引今天我们就用 pt-online-schema-change 进行在线修改表结构。1、安装PerconaTookit依赖包安装第三方依赖包yum install -y perl-DBI yum install -y perl-DBD-mysql yum install -y perl-IO-Socket-SSL yum install -y perl-Digest-MD5 yum install -y perl-TermReadKey2、安装PerconaTookit工具在线下载地址： https://www.percona.com/downloads/percona-toolkit/LATEST/wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm rpm -ivh *.rpm3、PT工具在线修改结构些参数pt-online-schema-change OPTIONS DSN参数实际含义--hostIP地址--user用户名--password密码--port端口号--alert修改语句--execute执行修改--dry-run测试执行--print打印过程t数据表D逻辑库sql举例1： my_shop库中的品牌表custom_address中的name字段varchar(200)改成varchar(20)pt-online-schema-change --host=192.168.10.101 --port=3306 --user=root --password=123456 --alter "MODIFY `name` VARCHAR(20) NOT NULL COMMENT '名称'" D=my_shop,t=t_brand --print --execute最后一个参数execute是真正修改执行，而--dry-run只是模拟执行的意思。4、实例操作修改库表mysql8.0以下无需执行，在执行之前还要执行以下2个sql语句：ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by '123456'为什么要执行呢？因为在pt工具中连接mysql数据库用的是老的连接方式，mysql8.0引入了新的密码认证方式，PT工具是暂时不支持的，所以只能在数据库端做手脚，把数据库密码验证方式改成旧的认证方式。再执行：pt-online-schema-change --host=192.168.10.101 --port=3306 --user=root --password=123456 --alter "MODIFY name VARCHAR（20） NOT NULL COMMENT '收货人'" D=my_shop,t=t_customer_address --print --execute出现Successsfully就代表成功了 。再看数据库发现修改成功，过程中都没有锁表。更多PT修改字段操作#添加字段 --alter "add age varchar(10) NOT NULL DEFAULT '' COMMENT '年龄'" #修改默认值 -alter status set DEFAULT '0' #删除列 drop age 反正跟mysql的DDL操作语句一样在线修改表结构的原理怎么在不锁表正常修改表结构的呢？比如我现在想修改订单表的结构。PT工具先拷贝订单表结构，创建一个新的数据表。然后再新表上做结构修改再之后PT程序会在原来的表上创建触发器，（只要原来表有数据修改、添加、删除）动作就会同步执行到新的订单表还会把原表的数据拷贝到新表上这样双管齐下，数据同步后并且原订单没有写入的时候，把原表删除，把新名改回原表。全称没有锁表，没有耽误数据的读写操作。

熟悉mysql的共享锁、排它锁、悲观锁、乐观锁以及使用场景 sql前文数据库的增删改(update/insert/delete) 操作默认都会加排他锁，而查询(select)不会加任何锁。表级锁（锁定整个表）页级锁（锁定一页）行级锁（锁定一行）共享锁（S锁，MyISAM 叫做读锁）排他锁（X锁，MyISAM 叫做写锁）悲观锁（抽象性，不真实存在这个锁）乐观锁（抽象性，不真实存在这个锁）*共享（S)锁：多个事务可封锁一个共享页；任何事务都不能修改该页； 通常是该页被读取完毕，S锁立即被释放。排它（X)锁：仅允许一个事务封锁此页；其他任何事务必须等到X锁被释放才能对该页进行访问；X锁一直到事务结束才能被释放。更新（U)锁：用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的页将要被更新时，则升级为X锁；U锁一直到事务结束时才能被释放。先了解InnoDB与MyISAMMysql 在5.5之前默认使用 MyISAM 存储引擎，之后使用 InnoDB 。查看当前存储引擎：show variables like '%storage_engine%';MyISAM 操作数据都是使用的表锁，你更新一条记录就要锁整个表，导致性能较低，并发不高。当然同时它也不会存在死锁问题。而 InnoDB 与 MyISAM 的最大不同有两点：一是 InnoDB 支持事务；二是 InnoDB 采用了行级锁。也就是你需要修改哪行，就可以只锁定哪行。在 Mysql 中，行级锁并不是直接锁记录，行锁，由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql 语句操作了主键索引，Mysql 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。InnoDB 行锁是通过给索引项加锁实现的，如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。因为没有了索引，找到某一条记录就得扫描全表，要扫描全表，就得锁定表。共享锁(Share Lock)共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。白话文：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后用法SELECT ... LOCK IN SHARE MODE;在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。排他锁（EXclusive Lock）排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。白话文：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作用法SELECT ... FOR UPDATE;在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。共享锁跟排它锁的场景例子这里用T1代表一个数据库执行请求，T2代表另一个请求，也可以理解为T1为一个线程，T2 为另一个线程。例子1：共享锁、排他锁并执行T1： （假设查询会花很长时间，下面的例子也都这么假设）select * from table lock in share modeT2：update table set column1='hello'过程：T1运行（并加共享锁)T2运行If T1还没执行完T2等......else 锁被释放T2执行end ifT2 之所以要等，是因为 T2 在执行 update 前，试图对 table 表加一个排他锁，而数据库规定同一资源上不能同时共存共享锁和排他锁。所以 T2 必须等 T1 执行完，释放了共享锁，才能加上排他锁，然后才能开始执行 update 语句。例子2：2条共享锁并执行T1:select * from table lock in share modeT2:select * from table lock in share mode这里T2不用等待T1执行完，而是可以马上执行。分析：T1运行，则 table 被加锁，比如叫lockA，T2运行，再对 table 加一个共享锁，比如叫lockB，两个锁是可以同时存在于同一资源上的（比如同一个表上）。这被称为共享锁与共享锁兼容。这意味着共享锁不阻止其它人同时读资源，但阻止其它人修改资源。例子3： 2条共享锁跟排他锁并执行T1：select * from table lock in share modeT2:select * from table lock in share modeT3:update table set column1='hello'T2 不用等 T1 运行完就能运行，T2跟T1都是共享锁兼容可以一并执行，T3 却要等 T1 和 T2 都运行完才能运行。因为 T3 必须等 T1 和 T2 的共享锁全部释放才能进行加排他锁然后执行 update 操作。（T3是update语句是加排他锁）例子4： 死锁产生T1：begin tran select * from table lock in share mode update table set column1='hello'T2:begin tran select * from table lock in share mode update table set column1='world'假设 T1 和 T2 同时达到 select，T1 对 table 加共享锁，T2 也对 table 加共享锁 。当 T1 的 select 执行完，准备执行 update 时,根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update。在升级排他锁前，必须等 table 上的其它共享锁（T2）释放，同理，T2 也在等 T1 的共享锁释放。于是死锁产生了。T1等T2共享锁释放，T2等T1的共享锁释放，互相等待..导致死锁，互相占用资源。解决方案：就是其中一个释放资源或者KILL掉，排查下哪里的SQL互相占用资源例子5：2条排他锁执行并发T1：begin tran update table set column1='hello' where id=10T2:begin tran update table set column1='world' where id=20这种语句虽然最为常见，很多人觉得它有机会产生死锁，但实际上要看情况。答案：不会产生死锁，因为InnoDB是行级锁，只能对主键索引进行锁住，2个操作都不是同一行。如果id是主键（默认有主键索引），那么T1会一下子找到该条记录(id=10的记录），然后对该条记录加排他锁。T2，同样，一下子通过索引定位到记录，然后对id=20的记录加排他锁，这样T1和T2各更新各的，互不影响。T2也不需要等。如果id是普通的一列，没有索引。那么当T1对id=10这一行加排他锁后，T2为了找到id=20，需要对全表扫描。但因为T1已经为一条记录加了排他锁，导致T2的全表扫描进行不下去（其实是因为T1加了排他锁，数据库默认会为该表加意向锁，T2要扫描全表，就得等该意向锁释放，也就是T1执行完成），就导致T2等待。例子6：死锁解决T1：begin tran select * from table for update update table set column1='hello'T2:begin tran select * from table for update update table set column1='world'这样，当 T1 的 select 执行时，加上for update 直接对表加上了排他锁，T2 在执行 select 时，就需要等 T1 事物完全执行完才能执行。排除了死锁发生。但当第三个 user 过来想执行一个查询(select)语句时，也因为排他锁的存在而不得不等待，第四个、第五个 user 也会因此而等待。在大并发情况下，让大家等待显得性能就太友好了。所以，有些数据库这里引入了更新锁（如Mssql，注意：Mysql不存在更新锁）。更新锁（Update Lock）T1：begin tran select * from table [加更新锁操作] update table set column1='hello'T2:begin tran select * from table [加更新锁操作] update table set column1='world'更新锁其实就可以看成排他锁的一种变形，只是它也允许其他人读（并且还允许加共享锁）。但不允许其他操作，除非我释放了更新锁。T1 执行 select，加更新锁。T2 运行，准备加更新锁，但发现已经有一个更新锁在那儿了，只好等。当后来有 user3、user4...需要查询 table 表中的数据时，并不会因为 T1 的 select 在执行就被阻塞，照样能查询，相比起例6，这提高了效率。后面还有意向锁和计划锁：计划锁，和程序员关系不大，就没去了解。意向锁（innodb特有）分意向共享锁和意向排他锁。意向共享锁：表示事务获取行共享锁时，必须先得获取该表的意向共享锁；意向排他锁：表示事务获取行排他锁时，必须先得获取该表的意向排他锁；我们知道，如果要对整个表加锁，需保证该表内目前不存在任何锁。因此，如果需要对整个表加锁，那么就可以根据：检查意向锁是否被占用，来知道表内目前是否存在共享锁或排他锁了。而不需要再一行行地去检查每一行是否被加锁。乐观锁和悲观锁首先说明，乐观锁和悲观锁都是针对读（select）来说的。概念乐观锁：乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。悲观锁：与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。案例场景：某商品，用户购买后库存数应-1，而某两个或多个用户同时购买，此时三个执行程序均同时读得库存为“n”，之后进行了一些操作，最后将均执行update table set 库存数=n-1，那么，很显然这是错误的。解决：使用悲观锁（其实说白了也就是排他锁）程序A在查询库存数时使用排他锁 select * from table where id=10 for update然后进行后续的操作，包括更新库存数，最后提交事务。程序B在查询库存数时，如果A还未释放排他锁，它将等待……程序C同B……（也等待中..）使用乐观锁（靠表设计和代码来实现）一般是在该商品表添加version版本字段或者timestamp时间戳字段程序A查询后，执行更新变成了：update table set num=num-1 where id=10 and version=23这样，保证了修改的数据是和它查询出来的数据是一致的（其他执行程序肯定未进行修改）。当然，如果更新失败，表示在更新操作之前，有其他执行程序已经更新了该库存数，那么就可以尝试重试来保证更新成功。为了尽可能避免更新失败，可以合理调整重试次数（阿里巴巴开发手册规定重试次数不低于三次）。区别总结：对于以上，可以看得出来乐观锁和悲观锁的区别：悲观锁：悲观锁实际使用了排他锁来实现（select **** for update）。文章开头说到，innodb加行锁的前提是：必须是通过索引条件来检索数据，否则会切换为表锁。因此，悲观锁在未通过索引条件检索数据时，会锁定整张表。导致其他程序不允许“加锁的查询操作”，影响吞吐。故如果在查询居多的情况下，推荐使用乐观锁。“加锁的查询操作” ：加过排他锁的数据行在其他事务中是不能修改的，也不能通过for update或lock in share mode的加锁方式查询，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。乐观锁：乐观锁更新有可能会失败，甚至是更新几次都失败，这是有风险的。所以如果写入居多，对吞吐要求不高，可使用悲观锁。也就是一句话：读用乐观锁，写用悲观锁。

MySQL如何优化超大的分页查询？ 前文在你开发程序的时候，使用 LIMIT 子句做分页查询可能是非常频繁的，这是一个非常常见的业务场景。那你在使用 limit 子句的时候有没有遇到过什么问题呢，比如说性能不好？曾经遇到过不少由于分页查询性能差，需要优化的案例。那常见的原因基本上也都是分页过多。过大的分页查询为什么会慢？下面我们先构造一个例子：/**生成3百万行的测试数据**/ CREATE TABLE `limit_optimize_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account` varchar(50) NOT NULL, `order_id` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2000002 DEFAULT CHARSET=utf8 create index idx_account on limit_optimize_tbl(account); create index idx_order_id on limit_optimize_tbl(order_id); DELIMITER CREATE PROCEDURE limit_optimize_test() BEGIN DECLARE i INT; SET i=1000000; WHILE i<=3000000 DO INSERT INTO limit_optimize_tbl(account,order_id) VALUES('test_123',concat('order', i)); SET i=i+1; END WHILE; END DELIMITER ; call limit_optimize_test();下面的这行 SQL 是一个常见的分页查询的语句：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;这种写法是最简单的，但同时也是最容易出问题的。曾经有人做过调查，用户在浏览前端页面时，假如这个页面是分页浏览的（例如淘宝），用户只会浏览前面几页，一般翻页超过 10 页很多人就开始表现的不耐烦了。在翻页比较少的情况下，LIMIT 子句并不会表现出性能问题。但是假如用户要直接跳到最后一页呢？通常情况下，由于要保证所有的页面都可以正常跳转，因此可能不会使用如下这种语句：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;而是继续采用正序顺序做分页查询：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;采用这种 SQL 查询的话，此时从 MySQL 中取出这 10 行数据的代价是非常大的，需要先排序出前面 1000010 条记录，然后抛弃前面的 1000000 条。查询数据和排序的代价非常高。我们再来看一下上面这个 SQL 语句的执行计划：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 1000000,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec)从执行计划中你可以看到，在大分页的时候，MySQL 并没有走索引扫描，而是使用了全表扫描的方式。那这又是为什么呢？MySQL 数据库采用了基于代价的查询优化器，而查询代价的估算是基于 CPU 代价和 IO 代价。由于现在机械硬盘还没有被完全淘汰掉，因此在类似这种局部扫描的动作中，随机 IO 的代价，仍然被MySQL 的查询优化器认为是非常高的。对于局部扫描，MySQL 会根据数据量的情况和数据获取的条件，去做代价估算，决定是采用顺序扫描还是随机读取存储系统。如果 MySQL 在查询代价估算中，认为采取顺序扫描方式比局部随机扫描的效率更高的话，就会放弃索引，转向顺序扫描的方式。这就是为什么在大分页中 MySQL 数据库走了全表扫描的原因。下面我们还是使用刚刚的 SQL 语句，再来实验一下：mysql> explain select * from limit_optimize_tbl order by order_id limit 5660,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 5670 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 5661,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec) 从上面的实验你可以看到2条SQL，在这个临界点上，MySQL 分别采用了索引扫描和全表扫描的查询优化方式。你可以自行运行下这两个 SQL 语句，比较下执行时间。由于 MySQL 的查询优化器的算法核心我们是无法人工干预的，因此我们的优化思路应该着眼于如何让分页维持在最佳的效率区间。下面我们就来看下如何优化。优化可以从两个角度进行分析：SQL 改写优化业务角度优化。SQL改写优化方案1：索引覆盖由于索引是有序的，因此这种优化方式的思路是直接在索引上完成排序和分页的操作。先来说说什么是覆盖索引。如果一个 SQL 语句，通过索引即可直接获取查询结果，而不再需要回表查询，就称这个索引覆盖了这条SQL 语句。也就是平时所说的不需要回表操作。在 MySQL 数据库中使用执行计划查看，如果 extra 这一列中显示 Using index ，就表示这条 SQL 语句使用了覆盖索引。下面我们看下刚刚的那条 SQL 语句：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select order_id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> explain select id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)从这个实验中可以看到，除了 select * 的那条 SQL 语句，其他两个都使用了覆盖索引。你也可以对比一下使用了覆盖索引的大分页和和没有使用覆盖索引的性能差异。在我的环境中执行实验中的 “LIMIT 1000000,10” 的分页查询，没有使用覆盖索引的 SQL 语句执行时间是 2.51s使用了覆盖索引的 SQL 语句执行时间是 0.16s优化效果还是非常明显的。方案2：子查询优化由于在 SELECT 语句中我们很少会只查询某一两个列，因此上述覆盖索引的适用范围就比较有限。可以通过将分页的 SQL 语句改写成子查询的方法获得性能的提升。示例如下：mysql> select * from limit_optimize_tbl where id >= (select id from limit_optimize_tbl order by order_id limit 1000000,1) limit 10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.16 sec)执行时间和上一节的使用了覆盖索引的 SQL 语句基本一致。不知道你有没有观察到，这种优化方法也有其局限性：首先，分页的数据必须是连续的其次，WHERE 子句里面不能再添加别的条件方案3：延迟关联和上述子查询的做法类似，我们也可以使用 JOIN 的语法，先在索引上完成分页的操作，然后再回表获取需要的数据列。示例如下：mysql> select a.* from limit_optimize_tbl a inner join (select id from limit_optimize_tbl order by order_id limit 1000000,10) b on a.id=b.id; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.15 sec)你可以和上一小节的子查询优化的方式做个对比，在采用了 JOIN 语法改写之后，上面的两个限制都解除了，并且 SQL 的执行效率没有损失方案4： 记录书签和上述使用覆盖索引的思路不同，记录书签的优化思路是使用书签记录上一页数据的位置，下次分页时直接从这个书签的位置开始扫描，从而避免 MySQL 扫描大量的数据行再丢弃的操作。示例如下：mysql> select * from limit_optimize_tbl where id>=1000001 limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.00 sec)从上面的 SQL 语句你可以看到，由于使用了主键索引来做分页的操作，SQL 语句的性能是极佳的。使用其他列做书签也是可以的：mysql> select * from limit_optimize_tbl where order_id>='order2000000' limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.01 sec)这里要给你提个醒，如果没有使用主键索引或唯一索引做这个书签，排序的字段有大量重复值的情况下，输出的结果不一定是准确的，不适合使用这种写法。方案5： 反向查找反向查找即我们在本文的开头提到的例子：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;这种优化的思路来自于二分查找，也就是说，当偏移量超过记录数的一半时，就可以使用这种写法来获得性能的提升不过这种方法需要在分页前知道符合条件的总的记录条数，但是在 InnoDB 存储引擎中，COUNT (*) 的开销其实也不小。因此建议你仅在一些特殊情况下选用，例如直接跳到尾页。业务角度优化下面我们一起来看看从业务角度如何优化大分页查询。其实这个优化思路要看具体的业务内容，业务是千变万化的，因此本文中提到的这几个案例，不一定就适合你们公司的业务类型，只是起个抛砖引玉的作用。翻页限制不允许翻过多的页 , 一言以蔽之，就是不给你查了。把 LIMIT 分页的偏移量做一个限制，超过某个阈值就停止。我们以淘宝网为例，使用比较热门的 “男鞋” 的关键词进行搜索，网站仅仅提供了 100 个数据页。很多大型互联网公司由于数据量巨大，都有使用这种方法。流式分页这种分页方式比较适用于移动端，即只能一页一页的向前或向后加载，不提供跳转的功能。可以在上一级入口中提供业务列表给用户选择，从而减少分页。这种分页方式在电商和新闻类 APP 上应用的非常广泛，你也可以试试。总结总的来说，大分页的优化思路就是让分页的SQL语句尽量在最佳的性能区间内执行，不要触发全表顺序扫描，也不要扫描太多的数据行。SQL 的优化有两个方向，一个是 SQL 语义的优化，另一个是从业务角度对 SQL 语句进行优化。SQL 语义的优化所能发挥的功力有限，在 SQL 优化的工作中大概只有3成的SQL 能通过 SQL 的改写完成优化；而从业务角度的调优占了有 7 成。因此在对 SQL 语句进行优化时，不妨多从业务角度入手，想想看有没有好的解决方案。参考资料《数据库查询优化器的艺术》，李海翔著《高性能 MySQL》第三版慕课专栏-MySQL 开发高频面试题精选

InnoDB的几种事务隔离级别 前文首先理解事务的ACID的隔离性 并发带来的问题，脏读 、不可重复读 、幻读 的问题。脏读： 一个事务读取了另一个事务未提交的数据，导致数据不一致。【事务2未提交】不可重复读： 一个事务前后两次读取的同一数据不一致 【事务2已提交】幻读： 一个事务两次查询的结果集记录数不一致，返回的记录行数有差异不可重复读跟脏读的区别不可重读就是第二个事务提交了对数据的修改，脏读是在第二个事务未提交回滚了对数据的修改。虽然在不可重复读前后数据不一样，由于事务2最终提交了修改，在事务1第二次读到数据，实际上是有效的。而不像脏读 事务2最终是回滚对数据修改，导致事务1第二次读取数据是无效的数据。所以说不可重复读是还可以接受的。InnoDB的几种事务隔离级别支持4中隔离级别，一个事务对另一个事务并发进行修改时，可以相互影响的程度。隔离级别脏读不可重复读幻读隔离性并发性串行化（SERIALIZABLE）NNN最高最低可重复读（PEREATABLE READ）NNY/N 读以提交（READ COMMITTED）NYY 读未提交（READ UNCOMMITTED）YYY最低最高4.1 串行化（顺序读）对事务强制的排序，使各个事务顺序执行，这样就避免了各个之间的相互冲突，从而解决脏读、不可重复读、幻读的问题。由于所有的事务是顺序执行，虽然在这隔离性最高，反而并发性是最差的 ， 所以的事务都是顺序执行并不存在并发。4.2 可重复读可以 保证在同一个事务中，多次读取同一数据时返回的结果是相同的，可以避免脏读、不可重复读的问题，但是不能避免幻读的问题。如果在Innodb中利用了Next-key引用下一个键索机制，锁定一个范围，并且锁定记录本身,避免幻读的现象。在这个级别不断可以做到顺序读隔离性，同时事务的并发性做到很好的保证。（INNODB默认使用级别）4.3 读以提交保证事务只能看到已经被提交的事务的关联数据的修改，只能避免脏读 ，无法避免不可重复读、幻读的产生。隔离性比较差，但是并发性很高，由于查到都是其他事务修改后的数据，有很多人喜欢这种隔离级别，在并发要求性比较高的场景中。sqlserver、PostGreSQL 默认使用的一种隔离级别4.4 读未提交如名字一样， 一个事务可以读到另一个事务未提交的数据，因此无法避免脏读、不可重复读、幻读的产生。隔离性最差，同时是并发性最高的的隔离级别。小结对事务来说，在不同的隔离级别下所执行的结果也不一样，隔离级别越高，数据的完整性一致性越好，同时并发性也越差。session：当前会话，也就是当前连接立即生效。global：全局，不包含当前连接，之后新获取的连接都会生效。修改（当前会话）事务隔离级别set session TRANSACTION ISOLATION LEVEL [SERIALIZABLE | PEREATABLE READ | READ COMMITTED | READ UNCOMMITTED ] #修改隔离级别查看事务里级别show variables like "%iso%" #查看事务隔离级别

Innodb支持索引类型以及Btree的特点 Innodb支持索引类型Btree索引 ： （没有指定，大部分都是Btree索引，比较常用）自适应HASH索引 ： （Innodb为了优化查询性能而自动建立的，并不需要手动管理）全文索引 ： （5.7+支持 用于搜索引擎、字符串数据列上，对中文支持不太友好，用第三方搜索引擎类的服务代替 ）空间索引 ： （5.7+支持 点切面空间之类的空间数据类型，在项目中并没涉及到这类的数据）Btree索引的特点首先了解什么是B+树？从图下可以看到B+树是一个平衡的二叉树，每一列的节点到根的距离都是相同的，并且所有记录节点都是按键值的大小顺序放在同一层叶子节点上，并且每个叶子节点之间通过指针来进行连接。方便快速进行查找，这就是典型的B+树的存储结构。但是对于不同的存储引擎具体实现又有不同。比如MyISAM的B+树索引，叶子节点所指向是数据行的物理地址。Innodb叶子节点是指向数据行的主键的位置。特点1： 以B+树的结构存储索引数据在B+树每一列的节点中，都包含了指向下一列叶子节点的指针。目的 方便叶子节点的遍历。特点2：Btree索引适用于全值匹配的查询比如查询课程信息是mysql课程class_name='mysql'， 这样的过滤条件，只有完全匹配的课程才被查询出来。对于class_name in ('mysql','SQL') in的做法也是全值匹配的查询。使用IN子句也可以进行查询索引，只是在in列表过多时，mysql优化器才可能被认为使用全表扫描的方式特点3： Btree索引适合处理范围查找指在数据进行范围的过滤的情况下select * from table where cnt between 100 and 200 #或者 select * from table where cnt > 300 #大于或者小于特点4： Btree索引从索引的最左侧开始匹配查找列#课程表 增加复合索引(title / study_cnt ) create index idx_title_study_cnt on imc_course(title,study_cnt)下面哪种是用到了索引呢？A: selct * from imc_course where study_cnt > 3000 #不走索引 B: selct * from imc_course where study_cnt > 3000 and title ='mysql' #走索引 C: selct * from imc_course where title ='mysql' #走索引选项A 筛选了人数大于3000，但是复合索引的列是要求从最左侧开始匹配 ，所以A 无法用到title+study_cnt的复合索引。选项B 同时对study_cnt跟title进行查找过滤，虽然说过滤条件的顺序反过来过滤，仍然是可以使用到title+sutyd_cnt的索引。mysql的优化器会自动跳转过滤条件的顺序，所以顺序乱的也是符合使用到索引。选项C 只有title的过滤条件，但是我们索引是符合最左侧列， 也是title列，符合使用到索引。

一文带你了解SQL的执行计划(explain) 一. 什么是SQL执行计划为什么关注sql的执行计划，因为一个sql的执行计划可以告诉我们很多关于如何优化sql的信息 。通过一个sql计划，如何访问中的数据 （是使用全表扫描还是索引查找？）一个表中可能存在多个表中不同的索引，表中的类型是什么是否子查询、关联查询等..sql学习来自于sqlercn讲师了解SQL如何访问表中的数据了解SQL如何使用表中的索引了解SQL所使用的查询类型二. 获取SQL执行计划在sql前加上explain 关键词就可以了！在MySQL8.0中是支持对select/delete/inster/replace/update语句来分析执行计划而MySQL5.6前只支持对select语句分析执行计划。提示： replace语句是跟inster语句非常类似，只是插入的数据和表中存在的数据（存在主键或者唯一索引）冲突的时候，replace语句会把原来的数据替换新插入的数据，表中不存在唯一的索引或主键，则直接插入新的数据除了静态分析，MySQL8.0还可以使用for connection 来直接分析正在执行的sql。（connection_id 就是processlist表中的数据id）三. 分析执行计划执行计划内容分析例子1：课程人数大于3000 EXPLAIN select course_id,title,study_cnt from imc_course where study_cnt > 3000;访问得出：这些字段代表什么含义呢？四. 分析执行计划（ID列）ID标识查询执行的顺序ID相同时，由上到下分析执行ID不同时，由大到下分析执行ID列中的值只能有2中情况，要么是1组数字，要么是NULL如果ID为数字序列，则说明查询的SQL语句对数据对象的操作顺序如果是NULL，则代表数据由另外的2个查询union操作后所产生的结果集。例子2： 【ID一致】学习人数大于3000人的课程id、课程分类、难度、课程标题、课程学习人数explain select course_id,class_name,level_name,title,study_cnt from imc_course a join imc_class b on b.class_id=a.class_id join imc_level c on c.level_id =a.level_id where study_cnt > 3000返回3行结果，并且ID值是一样的由上往下读取sql的执行计划，第一行是table c表作为驱动表 ，等于是以C表为基础来进行循环嵌套的一个关联查询。 （4 *100*1 =400 总共扫描400行等到数据） SQL优化器根据表的统计信息来进行数据的调整，实际上是由C表作为主表进行嵌套查询，sql优化动态的来调整表的关联顺序。例子3： 【ID不一致】查询课程表中不存在课程章节的数据explain select a,course_id,a.title from imc_course a where a.course_id not in ( select course_id from imc_chapter b ) 五. 分析执行计划（select_type列）值含义SIMPLE不包含子查询或者UNION操作的查询（简单查询）PRIMARY查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARYSUBQUERYselect列表中的子查询DEPENDENT SUBQUERY依赖外部结果的子查询UNIONunion操作的第二个或之后的查询只为unionDEPENDENT UNION当union作为子查询时，第二或是第二个后的查询的select_type值UNION RESULTunion产生的结果集DERIVED出现在from子句中的子查询 （派生表）例子1 : 查询学习人数大于3000 合并 课程是MySQL的记录EXPLAIN SELECT course_id,class_name,level_name,title,study_cnt FROM imc_course a join imc_class b on b.class_id =a.class_id join imc_level c on c.level_id = a.level_id WHERE study_cnt > 3000 union SELECT course_id,class_name,level_name,title,study_cnt FROM imc_course a join imc_class b on b.class_id = a.class_id join imc_level c on c.level_id = a.level_id WHERE class_name ='MySQL'先看id等于2id=2 则是查询mysql课程的sql信息，分别是b,a,c 3个表，是union操作，selecttype为是UNIONid=1 为是查询学习人数3000人的sql信息，是primary操作的结果集，分别是c,a,b3个表，select_type为PRIMARY最后一行是NULL, select_type是union RESULT 代表是2个sql 组合的结果集。六. 分析执行计划（table列）指明是从那个表中获取数据<table name> 就展示数据库表名 （如果表取了别名就显示别名）<unionM,N> 由ID为M,N 查询union产生的结果集<derived N>/<subquery N> 由ID为N的查询产生的结果（通常也是一个子查询的临时表）七. 分析执行计划（partition列）对于查询分区表，显示查询的分区ID对于非分区表，显示NULL八. 分析执行计划（type列）【重要】注意的: 是在mysql中不一定是使用JOIN才算是关联查询，实际上mysql会认为每一个查询都是连接查询就算是查询一个表，对mysql来说也是关联查询。type的取值是体现了mysql访问数据的一种方式。type列的值按照性能高到低排列 system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL值含义system这是const连接类型的特例，当查询的表只有一行时使用const表中有且只有一个匹配的行时使用，如对主键或唯一索引的查询，这是效率最高的链接方式eq_ref唯一索引或主键查询，对应每个索引建，表中只有一条记录与之匹配 【A表扫描每一行B表只有一行匹配满足】ref非唯一索引查找，返回匹配某个单独值的所有行ref_or_null类似于ref类型的查询，但是附加了对NULL值列的查询index_merge该链接类型表示使用了索引合并优化方法range索引范围扫描，常见于between、>、< 这样的查询条件indexFULL index Scan全索引扫描，同ALL的区别是，遍历的是索引树ALLFULL TABLE Scan全表扫描 ，这是效率最差的链接方式举例拿以上的sql语句分析这里先分析ID不同，由大到小，ID相同由上到下。「ID=1 union , select_type= PRIMARY 」C表 就是imc_level 作为驱动主表，a表level_id没加索引，他是ALL是全表扫描的，没有一个可用的索引。「ID=1 , select_type= PRIMARY 」 ,a表 就是imc_course，a表class_id加了索引，但是没where去查询，并且数量量小也会走全表扫描， 也是ALL全表扫描，没有一个可用的索引。`「 ID=1 , select_type= PRIMARY 」 , b表 就是imc_class , a表class_id加了索引跟class表的主键对应， 是eq_ref 对应每个索引建，表中只有一条记录与之匹配，嵌套循环查找，B表通过A表的class_id关联查找。 【一条记录匹配】「 ID=2 , select_type= union 」，b表就是imc_class ，是const ，因为where加了条件class_name=MySQL ，而class_name加了唯一索引，并且只匹配到一个。 【只有一行】「 ID=2 , select_type= union 」 a表就是imc_course表，也是用到索引。匹配某个单独值的所有行union是结果集，没有索引也是通过全表扫描。如果where like "MySQL%" ，会是什么type类型？虽然class_name 加了索引 ，但是使用where的like% 右配 所以会走索引范围扫描。如果where like "%MySQL%" , 会是什么type类型 ？虽然class_name 加了索引 ，但是使用where的%like% 左右统配 所以会走全索引扫描，如果不加索引的话，左右统配会走全表扫描。九. 分析执行计划（possible_keys/key/key_len/ ref 列）possible_keys说明表可能用到了哪些索引，而key是指实际上使用到的索引。基于查询列和过滤条件进行判断。查询出来都会被列出来，但是不一定会是使用到。指出查询中可能会用到的索引指出查询时实际用到的索引实际使用索引的最大长度possible_keys / key可能用到的索引如果在表中没有可用的索引，那么key列 展示，possible_keys是NULL，这说明查询到覆盖索引。key_len实际用到的索引key_len是索引使用的字节数ken_len注意的是，在联合索引中，如果有3列，那么总字节是长度是100个字节的话，那么key_len值数据可能少于100字节，比如30个字节，这就说明了查询中并没有使用联合索引的所有列。而只是利用到某一些列或者2列。key_len的长度是由表中的定义的字段长度来计算的，并不是存储的实际长度，所以满足数据最短的实际字段存储，因为会直接影响到生成执行计划的生成 。ref指出那些列或常量被用于索引查找执行 select * from imc_user where user_id=1从头到尾看下首先他的ID列是1 ，代表就是数据库操作对象，并不是union产生的结果集select_type列是SIMPLE 是代表简单的查询查询所涉及的表table列是imc_user表，由于这不是一个分区表所以partitions列是等于NULL 。type列是const是个常量查询，固定值是1 ，只匹配一个并且用到的索引是主键索引。possible_keys列 可能用到的索引也是主键索引，key列 实际索引也是主键索引。因为user_id是主键key_len列 长度是4个字节，user_id是int类型，大小也是4个字节ref列 是const 也就是一个固定值，固定的常量值来进行索引过滤的。十. 分析执行计划（rows / filtered 列）rows列（ 有2个含义）1.、根据统计信息预估的扫描行数，2、另一方面是关联查询内嵌的次数，每获取匹配一个值都要对目标表查询，所以循环次数越多性能越差。因为扫描行数的值是预估的，所以并不准确执行 select * from imc_user where user_id=1拿以上举例，因为查询是一条记录，所以row是一条数据。filtered列表示返回结果的行数占需读取行数的百分比filtered列跟rows列是有关联的，是返回预估符合条件的数据集，再去取的行的百分比。也是预估的值。数值越高查询性能越好十一. 分析执行计划（Extra列）包括了不适合在其他列中所显示的额外信息值含义Distinct优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作Not exists使用not exists来优化查询Using filesort使用文件来进行排序，通常会出现在order by 或 group by 查询中Using index使用了覆盖索引进行查询【意思是查询所需要的信息用索引来获取，不需要对表进行访问】Using temporaryMySQL需要使用临时表来处理，常见于排序、子查询、和分组查询Using where需要在MySQL服务器层使用WHERE条件来过滤数据select tables optimized away直接通过索引来获取数据，不用访问表例子1：查询课程表没有章节的信息EXPLAIN select a.course_id,title from imc_course a left join imc_chapter b on b.course_id = a.course_id where b.course_id is null可以看到a表的key列是有值的，而possible_keys是NULL，代表是采用了覆盖索引。印证了Extra列是Using index 覆盖索引【优化】 a表这个索引key是title，如果加了where条件可以优化a表的type级别。比如where tite ='xxx' or where title like "Mysql%"。而b表是type列是ref级别，匹配某个单独值的所有行，并且加了where条件。所以再Extra列是有多个的。以上就完成分析计划所以列的说明了...