Java架构——未完成

单体>集群>分布式>微服务>容器化
高可用、高并发、高性能

第一阶段：单体项目开发与上线

第1周 万丈高楼，地基首要

架构师需要具备的能力
1、技术全面，有广度
2、关注前沿技术
3、全局观、预判
4、把控团队，忙而不乱
5、系统分解与模块拆分
6、指导与培训
7、沟通与协调能力
8、抽象、举例、画图
9、软技能(举行活动、谈判能力)

1、技术选型

后端

用springMVC还是用Springboot
1、用springboot。配置：xml配置比yml配置复杂
2、用springboot。SpringBoot集成了多样化中间件[*-stater]
3、用springboot。从外置tomcat变为内置tomcat

Struts十年前比较火(老，金融项目,有安全漏洞，请求变量共享，Filter拦截，非spring官方自产自销)

前端

MVVM

Jquery、VUE.JS

html、css

技术选型

1、切合业务
2、社区活跃度
3、团队技术水平
4、版本更新迭代周期
5、试错精神
6、安全性
7、成功案例
8、开源精神

2、前后端分离开发模式

为什么不用jsp
用户量多了，所有的界面在后端生成，服务器压力大

3、项目拆分与聚合

2-4

第2周 分类，推荐，搜索，评价，购物车开发

第3周 地址，订单，支付，定时任务开发

第4周 用户中心 ,订单

第5周 云服务器部署上线

第二阶段：从单体到高可用集群演进

第6周 LVS+Keepalived+Nginx实现高可用集群
第7周 主从复制高可用Redis集群
第8周 Redis缓存雪崩，穿透

第三阶段：逐个击破分布式核心问题

第9周 分布式会话与单点登录SSO
第10周 分布式搜索引擎-Elasticsearch
第11周 分布式文件系统-FastDFS+阿里OSS

第12周 分布式消息队列-RabbitMQ(兔子)

分布式消息队列(MQ)认知提升

业界主流的分布式队列(MQ)与技术选型

应用场景
1、服务解耦(拆分和隔离，然后通信)
(1) 强依赖： 服务A调用服务B，B服务出现故障时A服务也不可用
Dubbo、http、Tcp
(2) 弱依赖： 服务A调用服务B，B服务出现故障时A服务仍然可用
消息中间件

2、削峰填谷(需要把请求突刺均摊到一段时间内,让系统负载保持在请求处理水位之内,同时 尽可能地处理更多请求,从而起到“削峰填谷”的效果。)
秒杀，大促(缓存到一个地方，慢慢进行消费)
3、异步化缓冲
异步，最终一致性

分布式消息队列(MQ)应用思考点
1、生产端可靠性投递(与数据库的原子性)。
2、消费端幂(幂等性的验证，不能消费多次)。
3、高可用、低延迟、可靠性(rebbitcat)、堆积能力，可扩展性，等等…。
主流的分布式信息队列MQ
1、Active
2、Rabbit
3、Rocket
4、Kafka
如何进行技术选型？
1、各个MQ的性能、优缺点、响应的业务场景。
2、集群架构模式，分布式、可扩展、高可用、可维护性。
3、综合成本问题，集群规模，人员成本。
4、未来的方向、规划、思考。

四种集群架构
1、主备模式
热备份，master，sleep
warren(兔子窝)，一个主/备方案(主节点如果挂了，节点提供服务，和ActiveMQ利用Zookeeper做主/备一样)

2、远程模式
异地容灾，单个集群无法，发到下游。

3、镜像模式

4、多活模式

ActiveMQ特性原理与集群架构

RabbitMQ特性原理与集群架构解析

Kafka特性原理与集群架构解析

RabbitMQ实战

AMQP核心概念

Springboot整合实战-生产端和消费端详解

RabbitMQ可靠性投递基础组件封装

第13周 分布式消息队列-Kafka-1

Kafka应用实战

Kafka高吞吐量日志收集实战

架构思考：分布式日志、跟踪、告警、分析平台

第14周 分布式锁-1

第15周 读写分离、分库分表

MyCat读写分离(默认端口:8099)

背景
1、单机（单库）性能瓶颈，并且扩展起来非常的困难。
2、当前的这些数据库厂商，包括开源的数据库MySQL在内，提供这些服务都是需要收费的(mysql厂商分库分表是收费的)。
3、转向第三方软件，使用这些软件做数据的切分，将原本在一台数据库上的数据，分散到多台数据库当中，降低每一个单体数据库的负载。

数据切分
通过某种条件，将我们之前存储在一台数据库上的数据，分散到多台数据库中，从而达到降低单台数据库负载的效果。

垂直切分
按照不同的表或者Schema切分到不同的数据库中，

比如：在我们的课程中，订单表（order）和商品表（product）在同一个数据库中，而我们现在要对其切分，使得订单表（order）和商品表（product）分别落到不同的物理机中的不同的数据库中，使其完全隔离，从而达到降低数据库负载的效果。

原则：一个架构设计较好的应用系统，其总体功能肯定是有多个不同的功能模块组成的。每一个功能模块对应着数据库里的一系列表。

建议：
1、在架构设计中，各个功能模块之间的交互越统一、越少越好(系统模块之间的耦合度会很低，各个系统模块的可扩展性、可维护性也会大大提高)。
2、在实际的系统架构设计中，有一些表很难做到完全的独立，往往存在跨库join的现象。
3、现在垂直切分成了两个数据库，跨库连表查询是十分影响性能的，也不推荐这样用，只能通过接口去调取服务，这样系统的复杂度又升高了。

垂直切分的优点
1、拆分后业务清晰，拆分规则明确；
2、系统之间容易扩展和整合；
3、数据维护简单

垂直切分的缺点
1、部分业务表无法join，只能通过接口调用，提升了系统的复杂度；
2、跨库事务难以处理；
3、垂直切分后，某些业务数据过于庞大，仍然存在单体性能瓶颈；
4、正如缺点中的最后一条所说，当某一个业务模块的数据暴增时，仍然存在着单机性能缺陷。还是之前的例子，如果出现了一个爆款商品，订单量急剧上升，达到了单机性能瓶颈，那么你所有和订单相关的业务都要受到影响。这时我们就要用到水平切分。

水平切分
反例：订单尾号为奇数的订单放在了订单数据库1中，而订单尾号为偶数的订单放在了订单数据库2中。
那么这个订单列表页要去怎么查，要根据我的用户id分别取订单1库和订单2库去查询出订单，然后再合并成一个列表，是不是很麻烦。

几种水平拆分的典型的分片规则
1、用户id求模，我们前面已经提到过；
2、按照日期去拆分数据；
3、按照其他字段求模，去拆分数据；

水平拆分的优点
1、解决了单库大数据、高并发的性能瓶颈；
2、拆分规则封装好，对应用端几乎透明，开发人员无需关心拆分细节；
3、提高了系统的稳定性和负载能力；

水平拆分的缺点
1、拆分规则很难抽象；
2、分片事务一致性难以解决；
3、二次扩展时，数据迁移、维护难度大。比如：开始我们按照用户id对2求模，但是随着业务的增长，2台数据库难以支撑，还是继续拆分成4个数据库，那么这时就需要做数据迁移了。

共同缺点
1、分布式的事务问题；
2、跨库join问题；
3、多数据源的管理问题

多数据源的管理问题
1、客户端模式，在每个应用模块内，配置自己需要的数据源，直接访问数据库，在各模块内完成数据的整合；
2、中间代理模式，中间代理统一管理所有的数据源，数据库层对开发人员完全透明，开发人员无需关注拆分的细节。

这两种模式的代表作：
中间代理模式：MyCat
客户端模式：sharding-jdbc

如何正确使用数据库读写分离
我们都会采用简单的架构，随着业务不断的发展，访问量不断升高，我们再对系统进行架构方面的优化。

什么是MyCat
它是一个开源的分布式数据库系统。
前端的用户可以把它看成一个数据库代理，用MySql客户端和命令行工具都可以访问。
后端则是用MySql原生的协议与多个MySql服务之间进行通信。

MyCat的核心功能是分库分表，即将一个大表水平切分成N个小表，然后存放在后端的MySql数据当中。

MyCat发展到目前的版本：已经不是一个单纯的MySql代理了，它的后端支持MySql，Oracle，SqlServer，DB2等主流的数据库，也支持MongoDB这种NoSql数据库。

Mycat(存储引擎)：MyCat就是MySql，而MyCat后面连接的MySql，可以理解为MySql中的存储引擎，比如：MyISAM、InnoDB等。所以，MyCat本身不存储数据，数据都是存储在MyCat后面连接的MySql上，数据的可靠性和事务都是MySql保证的。

MyCat是一个强大的数据库中间件，不仅仅可以用作读写分离、分库分表，还可以用于容灾备份，云平台建设等，让你的架构具备很强的适应性和灵活性。

MyCat的应用场景
MyCat发展到现在，使用的场景很丰富
1、单纯的读写分离，此时配置最为简单，支持读写分离，主从切换。
2、分库分表，对于超过1000w的表进行分片，最大支持1000亿的数据。
3、多租户应用，每个应用一个数据库，应用只连接MyCat，程序本身不需要改造。
4、代替HBase，分析大数据。

MyCat中的基本概念
MyCat是一个数据库的中间件，介于应用与数据库之间，是进行数据处理和交互的中间服务。

逻辑库(Schema)
在实际的开发中，开发人员不需要知道数据库中间件的存在，开发人员只需要有数据库的概念就可以了。
所以数据库中间件可以被看做是一个或者多个数据库集群构成的逻辑库。

逻辑表(table)
就是逻辑上一个库里的一个表。
实际：这个表已经水平切分为几个表了。
分片表：凡是我们做的数据水平切分的表。
全局表：
比如：查询订单时，需要把订单状态关联查出，如果订单表做了分片，分布在不同的数据库中，而订单状态表由于数据量小，没有做分片，那么我们查询的时候就要跨库关联查询订单状态，增加了不必要的麻烦，不如我们干脆把订单状态表冗余到所有的订单分片库中，这样关联查询就不需要跨库了。我们把这种通过数据冗余方式复制到所有的分片库中的表。

分片节点（dataNode）
数据被切分后，一张大表被分到不同的分片数据库上面，每个分片表所在的数据库就叫做分片节点。

节点主机（dataHost）
数据切分后，每一个分片节点不一定都会占用一个真正的物理主机，会存在多个分片节点在同一个物理主机上的情况，这些分片节点所在的主机就叫做节点主机。为了避免单节点并发数的限制，尽量将读写压力高的分片节点放在不同的节点主机上。

分片规则（rule）
一个大表被拆分成多个分片表，就需要一定的规则，按照某种业务逻辑，将数据分到一个确定的分片当中，这个规则就叫做分片规则。数据切分选择合适的分片规则非常重要，这将影响到后的数据处理难度，结合业务，选择合适的分片规则，是对架构师的一个重大考验。对于架构师来说，选择分片规则是一个艰难的，难以抉择的过程。

全局序列号（sequence）
大家有没有想过，数据切分以后，数据库表的中的id怎么办？原来在一张表的时候，我们采用id自增，但是数据分布到多个库怎么办？比如：向用户表插入数据，第一条记录插入了用户库1，它的id为1；第二条记录插入了用户库2，如果是自增，它的id也为1。这样id就混乱了，我们也无法确定一条数据的唯一标识了。这时，我们需要借助外部的机制保证数据的唯一标识，这种保证数据唯一标识的机制，我们叫做全局序列号。

垂直切分和水平切分
垂直切分选择
1、按照业务切分。
2、每种业务一个数据库。
3、不同业务之间，禁止跨库join联表。

垂直切分——优点
1、拆分后业务清晰，拆分规则明确;
2、系统之间容易扩展和整合;
3、数据维护简单。

垂直切分——缺点
1、部分业务表无法join，只能通过接口调用，提升了系统的复杂度。
2、跨库事务难以处理。
3、崔志切分后，某些业务数据过于庞大，仍然存在单体性能瓶颈。

水平切分
1、将一张表的数据按照某种规则分到不同的数据库中。
2、需要确定分片规则。
3、使用分片字段查询时，可确定实体库，其他字段查询，查询所有表。

水平切分——优点
1、解决了单库大数据、高并发的性能瓶颈;
2、拆分规则封装好，对应用端几乎透明，开发人员无需关心拆分细节。
3、提高了系统的稳定性和负载能力。

水平切分——缺点
1、拆分规则很难抽象;
2、分片事务一致性难以解决;
3、二次扩展时，数据迁移、维护难度大。

MyCat快速体验
采用VMware做虚拟机，虚拟3台机器
操作系统Linux CentOS 7
采用yum方式，在其中两台安装mysql

1、检查两台mysql是否安装正确
2、下载MyCat软件包
3、在第三台机器上安装MyCat，并修改配置文件。

如何将文件传入虚拟机
rz 命令
安装rz命令：
yum search rz
yum -y install lrzsz
rz
选择文件

启动mysql，查看进程
service mysqld start
ps -ef|grep mysql

mycat实践(安装都放在/opt)
1、准备好两台虚拟机安装好mysql
2、创建用户。
create user ‘imooc’@‘%’ identified with mysql_native_password by ‘Imooc@123456’;

grant all on * . * to ‘imooc’@‘%’;

flush privileges;//刷新权限
netstat -ntIp | grep mysql //查看端口
systemctl stop firewalld;//关闭防火墙

3、第三台虚拟机安装mycat
(1) 下载压缩包
(2) 解压
(3) 修改配置文件
mycat/conf
vim server.xml

<user name="root" defaultAccount="true">
	<property name='password'>123456</property>
	<property name='schemas'>user</property>
</user>

<user name="user">
	<property name='password'>user</property>
	<property name='schemas'>TESTDB</property>
	<property name='readOnly'>true</property>
</user>

vim schema.xml

<!-- 读写库的配置 -->
<dataHost name ="db131">
	<writeHost host='hostM1' url='地址'>
		<readHost />
	</writeHost>
</dataHost>
<dataHost name ="db132">
	<writeHost host='hostM1' url='地址'>
		<readHost />
	</writeHost>
</dataHost>

<!-- 数据结点 -->
<dataNode/>

<!-- schema -->
<schema>
	<table name ='user'></table>
</schema>

(4) 在数据库里面新建库(库名和dataNode->database一致)
新建表(表名和schema->table->name一致)
(5)
在mycat目录目录下
./bin/mycat console 启动 打印到控制台，没办法其他操作
./bin/mycat start 后台启动
报错一：
vim schema.xml(schema ->table) ->
rule.xml ->rule标签 ->function标签 ->找到指定文件->修改为两个即可
报错二：
vim server.xml (user ->schema里面的name) 要和schema.xml(schema 里面的name一致)
(6) 插入到mycat数据实际存储在mysql。
因为设置规则是按照id，所以插入id为1，id为6000000分别在不同的库。

server.xml配置
1、配置Mycat的用戶名、密碼、权限、Schema等。
2、如同给MySql新建用户一样。
3、客户端连接MyCat与连接MySql无异。

schema.xml配置
1、配置dataHost(节点主机)，包括读host、写host。
2、配置dataNode(数据节点)，指定到具体的数据库。
3、配置schema，表名、数据节点、分片规则等。

dataHost标签属性

属性名	说明
name
maxCon
minCon
balance	负载均衡类型：0不开启读写分离;1和2读写均衡分配；3读落在readHost上。
writeType	0代表所有写请求会落到第一个写库上，1随机
dbType
dbDriver
switchType
slaveThreshold

hearbeat标签

writeHost标签

属性名	说明
host
url
user
password

dataNode标签

属性名	说明
name
dataHost
database

schema标签

属性名	说明
name
checkSQLschema	是否去掉SQL中的Schema
sqlMaxLimit	select默认的limit值，仅对分片表有效。

table标签

属性名	说明
name	定义逻辑表的表名
dataNode	定义逻辑表的数据结点
rule属性	定义分片表的规则，必须与rule.xml中的tableRule对应。
ruleRequired属性，是否绑定分片规则，如果为true，没有绑定分片规则，程序报错。

改变配置文件让文件生效
mycat的管理
1、ip：mycat的ip
端口：9066
账号密码相同

2、右击连接成功的，选择命令行界面
3、reload @@config;//让配置生效
4、show @@help;//帮助文档
5、show @@datanode;
6、reload @@config_all;//
7、systemctl stop mysqlld;//关闭mysql

Mysql主从搭建
1、主配置log-bin，指定文件的名字。
2、主配置server-id，默认为1。
主mysql的配置：

vim /etc/my.cnf
log-bin=imcooc_mysql
server-id=1

service mysqld restart //重启

3、从配置server-id，与主不能重复。
从mysql配置：
vim /etc/my.cnf
log-bin=imcooc_mysql
server-id=2

4、创建用户（主创建备份账户并授权REPLICATION CLAVE）
create user ‘repl’@‘%’ identified by ‘Imooc@123456’;
grant relpication slave on * . * to ‘账号名’
flush privileges;

5、主进行锁表flush tables with read lock;

6、主 找到log-bin的位置 show master status;

7、主备份数据
mysqldump --all-databases --master-data > dbdump.db
//执行失败，不能在mysql中执行，要在linux系统中执行
show master status;
复制会话
mysqldump --all-databases --master-data > dbdump.db
没权限
mysqldump --all-databases --master-data > dbdump.db -uroot -p

8、将备份文件复制到从数据库
scp root@192.168.73.131:~/dbdump.db .
mysql <dbdump.db -uroot -p

9、取消锁表
unlock tables;

10、在从上设置主的配置
change master to
master_host=‘master_host_name’,
master_user=‘replication_user_name’,
master_password=‘replication_password’,
master_LOG_FILE=‘recorded_log_file_name’,
master_LOG_POS=recorded_log_position;

注意：这里的名称是上面查出来的
master_LOG_FILE、master_LOG_POS
show master status;//分别对应前两列

11、刷新从库 start slave;

schema.xml配置
rule属性：定义分片表的分片规则，必须与rule.xml中tableRule对应。
schema ->rule

Mycat常用的分片规则
1、分片枚举：有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则(和某个值相等存储，但是如果都不匹配，需要设置默认的存储路径)。

2、取模(即根据id进行十进制求模预算，相比固定分片hash，此种在批量插入时可能存在批量插入单事务插入多数据分片，增大事务一致性难度)

3、global全局表
type属性：global为全局表，不指定则为分片表

子表的分片
childTable标签，定义分片子表
name属性，子表名称
joinKey属性，标志子表中的列，用于与父表做关联
parentKey标签，标志父表中的列，与joinKey对应。
primaryKey属性，子表主键，同table标签
needAddLimit属性，同table标签。

如果主表不存在这个id，子表分到哪？

mycat的原理
避免MyCat成为系统中的单点。

Mycat的HA
1、互联网时代，对系统可靠性要求高。
2、避免单点故障。
3、系统应用、数据库、缓存都是双节点。

Haproxy(端口是5000)
//复制mycat
1、scp -r root@192.168.73.130:/opt/mycat .
//安装Haproxy
2、yum search haproxy
yum -y install haproxy.x86_64
//配置haproxy配置文件
vim /etc/haproxy/haproxy.cfg
defaults ->mode http 改成 tcp
backend app
配置两台
3、查询haproxy进程
ps -ef|grep haproxy
4、启动
haproxy -f /etc/haproxy/haproxy.cfg

配置第二台Haproxy
defaults里面的
mode tcp
option tcplog
option http-server-close
option forwardfor except 127.0.0.0/8

未完

Sharding-Jdbc读写分离，分库分表

1、是一个开源的分布式的关系型数据库的中间件。
2、目前已经进入了Apache孵化器。
3、客户端代理模式。
4、定位为轻量级的java框架，以jar包提供服务。
5、可以理解为增强版的Jdbc驱动。
6、兼容各种ORM框架

提供4种配置方式
JavaAPI、Yaml、SpringBoot、Spring命名空间
1、Mycat是服务器代理Sharding-Jdbc是客户端代理(运维能力比较强，用Mycat)
2、Mycat不支持同一库内的水平切分、Sharding-Jdbc支持。

shardingspache
1、是一款开源分布式数据库生态项目
2、由 JDBC、Proxy 和 Sidecar（规划中） 3 款产品组成。
3、其核心采用可插拔架构，通过组件扩展功能。对上以数据库协议及 SQL 方式提供诸多增强功能，包括数据分片、访问路由、数据安全等；
4、对下原生支持 MySQL、PostgreSQL、SQL Server、Oracle 等多种数据存储引擎。
5、Apache ShardingSphere 项目理念，是提供数据库增强计算服务平台，进而围绕其上构建生态。充分利用现有数据库的计算与存储能力，通过插件化方式增强其核心能力，为企业解决在数字化转型中面临的诸多使用难点，为加速数字化应用赋能。

** Sharding-Jdbc 基本 概念 **

Sharding-Jdbc在spring环境搭建
1、新建项目，导入mysql驱动，jpa，mybatis。
2、引入mybatis的生成器。
3、mybatis的生成文件，generatorConfig.xml。
4、maven -> 项目名 -> plugins ->mybatis-generator ->mybatis-generator:generate。
5、建立springconfig，sharding-jdbc.xml，到官网复制命名空间的内容。
6、引入jar包。
7、sharding-jdbc.xml添加内容。

<bean id ="ds0"
class="com.zaxxer.hikari.HikariDataSource" destroy-method='close'>

<property name ="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>

<property name ="username" value="">
</property>

<property name ="password" value="">
</property>

<property name ="jdbcUrl" value="">
</property>

</bean>

<bean id ="ds1" class="com.zaxxer.hikari.HikariDataSource" destroy-method='close'>
<property name ="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name ="username" value="">
</property>
<property name ="password" value="">
</property>
<property name ="jdbcUrl" value="">
</property>
</bean>

<sharding:data-source id="sharding-data-source">
	<sharding:sharding-rule data-source-names="ds0,ds1">
	<sharding:table-rules>
		<sharding:table-rule logic-table="t_order" actual-data-nodes="ds$->{0..1}.t_order_$->{1..2}" database-strategy-ref="databaseStrategy"
		table-strategy-ref="tableStrategy"/>
	</sharding:table-rules>
</sharding:sharding-rule>
</sharding:data-source>
<sharing:inline-strategy id="databaseStrategy" sharding-column="user-id" algorithm-expression="ds$->{user_id%2}"/>
<sharing:inline-strategy id="tableStrategy" sharding-column="user-id" algorithm-expression="t_order_$->{user_id%2+1}"/>

<bean class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref= "sharding-data-source"/>
	<property name="mapperLocations" value="classpath*:/mybatis/*.xml"/>
</bean>

8、在启动类里面写
@ImportResource(“classpath*:sharding-jdbc.xml”)
启动
报错了
在启动类上加@MapperScan(“dao的地址”)

Springboot搭建Sharding-JDBC
1、引入jar。
2、启动类注释掉@ImportResource注解。
3、在application.properties配置。

<!-- 在官网复制下来 -->
<!-- 需要改，先配置库，再配置表。 -->

如何配置广播表(全局表)
springboot配置
1、哪些表设置为全局表，哪些表设置为分片表。
(1) 建一个表
(2) 修改配置文件generatorConfig.xml

<table schema="sharding_order" tableName="area" domainObjectName="Area"></table>

(3) 修改application.properties

spring.shardingsphere.sharding.broadcast-tables=area

spring配置
在sharding-jdbc.xml配置

<sharding:broadcast-table-rule table="area"/>

JDBC配置子表

sharding-JDBC的读写分离

第16周 分布式全局ID、分布式事务和数据一致性
第17周 分布式接口幂等性，分布式限流

第四阶段：SpringCloud G版微服务

第18周 微服务架构认知、服务治理-Eureka
第19周 负载均衡、服务通信与调用
第20周 服务容错-Hystrix
第21周 分布式配置中心-Config
第22周
第23周
第24周
第25周

第五阶段：Docker，K8S容器化

第26周 服务容器化-Docker
第27周 容器技术-Cloud Foundry
第28周 容器编排-Mesos+ Marathon
第29周 容器编排-K8S
第30周 容器弹性扩缩容

第六阶段：Netty与性能调优

第31周 高性能网络通信基石-Netty入门与提高

第32周 高性能网络通信基石-Netty最佳实战

第33周 基于Netty打造RPC通信框架-1

第34周 基于Netty打造RPC通信框架-2

第35周 应用监控与调优-工具篇

第36周 应用监控与调优-技巧与实战篇

第37周 JVM性能调优-理论+工具篇

第38周 JVM性能调优-实战篇

第39周 数据库监控与调优

数据库的调优

调优方式
发现问题：慢查询日志与分析
分析问题：EXPLAIN、SQL性能分析、optimizer trace…

调优理论
索引的原理
创建索引的技巧
索引失效…

特定语句的原理与优化
join、LIMIT、COUNT、GROUP BY、ORDER BY、表结构设计原则…
Percona Toolkit
foodie-dev项目慢SQL调优实战

测试数据准备与数据库操作工具

导入测试数据
foodie-dev项目 不合理
MySQL官方测试数据库-employee-data 合理
地址

# 下载项目

# 进入项目根目录

# 执行 ，导入SQL
mysql -uroot -proot123 <employees.sql

# 判断是否导入正确
mysql -t < test_employees_md5.sql # 内容：预期的导入情况，实际的导入情况，最终判断。
mysql -t < test_employees_sha.sql -- 测试分区才需要这个命令

# 在idea连接数据库
打开employees
# 看到表之间的关系
在idea右击表 -> Diagrams -> show visualisation

# 新建console，选择数据库

# 新建表，操作表

# 也可以使用SQLYog、Navicat

慢查询日志
发现慢SQL的工具SKywalking，VisualVM，JavaMelody
相关参数与默认值

参数	作用	默认值
log_output	1、日志输出到哪儿。2、设置为file、table，还可以设置为FILE,TABLE	FILE
long_query_time	执行时间超过这么久记录到慢查询日志，单位秒，可使用小数表示小于秒的时间。	10
log_queries_not_using_indexes	是否要将未使用索引的SQL记录到慢查询日志中，此配置会无视long_query_time的配置。生产环境建议关闭，开发环境建议开启	OFF
log_throttle_queries_not_using_indexes	和log_queries_not_using_indexes配合使用，如果log_queries_not_using_indexes打开，则该参数限制每分钟写入的、未使用索引的SQL数量。	0
min_examined_row_limit	扫描行数至少到这么多才记录到慢查询日志	0
log_slow_admin_statements	是否要记录管理语句，默认关闭。管理语句包括ALTER TABLE,ANALYZE TABLE,CHECK TABLE,CREATE INDEX,DROP INDEX,OPTIMIZE TABLE,AND REPAIR TABLE。	OFF
slow_query_log_file	指定慢查询日志文件路径	/var路径
log_slow_slave_statements	该参数在从库上设置，决定是否记录在复制过程中超过long_query_time的SQL。如果binlog格式是row，则该参数无效。	OFF
log_slow_extra	当log_output=FILE时，是否要记录额外信息(MYSQL8.0.14开始提供)，对log_output=TABLE的结果无影响。	OFF

使用方式
方式一：修改配置文件my.cnf,在[mysqld]段落中加入如下参数即可

[mysqld]
log_output='FILE,TABLE'
slow_query_log=ON
long_query_time=0.001
## 重启

方式二：通过全局参数设置

这种方式无需重启即可生效，一旦重启，配置会丢失
set global log_output = 'FILE,TABLE';
set global slow_query_log = 'ON'; # 打开
set global long_query_time = 0.001; # 配置好以后需要重新连接数据库
这样设置之后，就会将慢查询日志同时记录到文件以及mysql.slow_log表中。

# 查看mysql日志
select * from mysql.slow_log

# 查看文件存放位置
show variables like '%slow_query_log_file%'

分析慢查询日志工具——mysqldumpslowx
当log_output=FILE时，可使用mysqldumplow分析。
使用：mysqldumplow
参数

参数名	作用
–verbose	verbose
–debug	debug
–help	write this text to standard output
-v	展示更详细的信息
-d	debug
-s ORDER	以哪種方式排序，默认at,还有al,ar,at,c,l,r,t
al	平均锁定时间
ar	平均返回记录数
at	平均查询时间
c	访问计数
l	锁定时间
r	返回记录
t	查询时间
-r	将-s的排序倒序
-t NUM	top n的意思，展示最前面的几条
-a	不加，将会把数字展示成N，将字符展示成’S’
-n NUM	abstract numbers with at least n digits within names
-g pattern	后边可以写一个正则，只有符合正则的行会展示
-h hostname	慢查询日志以 主机名-slow.log的格式命名，-h可指定读取指定主机名的慢查询日志，默认情况下是*，读取所有的慢查询日志
-i NAME	MySQL Server的实例名称(如果使用了mysql.server startup脚本的话)
-l	不将锁定时间从总时间中减去

pt-query-digest
出mysql自带的mysqldumplslow外，还可以用pt_query_digest分析慢查询日志文件。pt-query-digest是Percona公司开发的工具，是Percona Toolkit工具套件中的工具之一。本文暂不展开，后面专门探讨Percona Tookit如何使用吧。

EXPLAIN详解

(1) 参数

字段	format=json时的名称	含义
id	select_id	该语句的唯一标识
select_type	无	查询类型
table	table_name	表名
partitions	partitions	匹配的分区
type	access_type	联接类型
possible_keys	possible_keys	可能的索引选择
key	key	实际选择的索引
key_len	key_length	索引的长度
ref	ref	索引的哪一列被引用了
rows	rows	估计要扫描的行
filtered	filtered	表示复核查询条件的数据百分比
extra	没有	附加信息

补充：
① rows x filtered = 和下一张表连接的行数
mysql低于5.7的话，需要explain extended命令
② extra
有两个关键字段
Usingfilesort：用文件排序(需要优化)。
Using temporary：使用临时表(需要优化)。
③ explain format = tree
(idea)选择语句，右击选择Expain Plan ，选择Explain Plan,选择show Visualisation…
④ mysql的workbench
Database → Connect to Database
⑤ 扩展Explain
Explain后紧跟show warning
Mysql8.0.12及更高版本，扩展信息可用于SELECT、DELETE、INSERT、REPLACE、UPDATE语句
MYSQL8.0.12之前，扩展信息只适用于SELECT语句
MySQL5.6及更低版本，需使用EXPLAIN EXTENDED xxx语句;而从MySQL5.7开始，无需添加EXTENDED关键词。

⑥ 性能评估公式
较小的表，通常可以通过在一次磁盘搜索中找到行(因为索引可能被缓存了)
更大的表，你可以使用B-tree索引进行估算
log(row_count)/log(index_block_length/32/(index_length+data_pointer_length))+1
例如：在Mysql中，index_block_length通常是1024字节，数据指针一般是4字节。比如有一个500000的表，key是三字节，那么根据计算公式需要4次四次搜索。
索引需要空间：5000007*3/2=5.2MB的存储空间(假设典型的索引缓存的填充率是2/3)，因此你可以在内存中存放更多索引，可能只要一到两个调用就可以找到想要的行了。
但是写操作，你需要4个搜索请求来查找在何处放置新的索引值，然后通常需要2次搜索更新索引并写入行。
讨论结果：当数据量到达某个值的时候，会变得很慢(由于数据量太大，无法缓存，收到磁盘搜索约束)。
解决：增加key，在MyISAM表中，key的缓存大小名字叫key_buffer_size。

(2) type的取值

取值	说明
system	该表只有一行(相当于系统表)，system是const类型的特例
const	针对主键或唯一索引的等值查询扫描，最多只返回一行数据.const查询速度非常块，因为它仅仅读取一次即可
eq_ref	当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL才会使用该类型，性能仅次于system及const
ref	当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到上量的行，性能也是不错的。
range	范围扫描
index	索引树扫描
all	全表扫描(full table scan)

SQL性能分析
三种方式
① SHOW PROFILE
② INFORMATION_SCHEMA.PROFILING
③ PERFORMANCE_SCHEMA

SHOW PROFILE
是MySQL的一个性能分析命令，可以跟踪SQL各种资源消耗

命令	说明
select @@have_profiing;	数据库是否支持
select @@profiling;	是否开启
set @@profiling=1;	开启
set profiling_history_size = 100	多记录点数据
show profiles	记录的执行的sql和概要信息
show profile for query 350;	从开始到结束的时间开销
show profile memory for query 350;	查看内存情况
show profile all for query 350;	各种信息
set @@profiling=0;	关闭

官方说明：show profile已被废弃，并建议使用Performance Schema作为代替品。
原因：性能分析在windows系统部分无法使用(性能分析是进程级的，不是线程级的)。

INFORMATION_SCHEMA.PROFILING表
用来做性能分析。

字段名	说明
QUERY_ID	语句的唯一标识
SEQ	一个序号，展示具有相同QUERY_ID值的行的显示顺序。
STATE	分析状态
DURATION	在这个状态下持续了多久(秒)
CPU_USER,CPU_SYSTEM	用户和系统CPU使用情况(秒)
CONTEXT_VOLUNTARY,CONTEXT_INVOLUNTARY	发生了多少自愿和非自愿的上下文转换
BLOCK_OPS_IN,BLOCK_OPS_OUT	块输入和输出操作的数量
MESSAGES_SENT,MESSAGES_RECEIVED	发送和接收的信息数
PAGE_FAULTS_MAJOR,PAGE_FAULTS_MINOR	主要和次要的页错误信息
SWAPS	发生了多少SWAP
SOURCE_FUNCTION,SOUCE_FILE,SOURCE_LINE	当前状态是在源码的哪里执行的

SELECT STATE,FORMAT(DURATION,6)  as DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID= 413 ORDER BY SEQ;

INFORMATION_SCHEMA.PROFILING表已被废弃，未来可使用performance schema代替。

PERFORMANCE_SCHEMA
是Mysql建议的性能分析方式，是mysql5.6引入。
使用：show variables like ‘performance_schema’;查看启用情况，MySQL5.7开始默认启用。

语句	说明
select * from performance_schema.setup_actors;	查看是否开启性能监控(默认打开的)
update performance_schema.setup_actors set ENABLED = ‘NO’,HISTORY=‘NO’ WHERE HOST=‘%’ AND USER=‘%’;	更新监控的范围
INSERT INTO performance_schema.setup_actors(HOST,USER,ROLE,ENABLED,HISTORY) VALUES(‘localhost’,‘test_user’,‘%’,‘YES’,‘YES’);	插入一条监控范围语句
update performance_schema.setup_instruments set enabled = ‘YES’,TIMED=‘YES’ WHERE NAME LIKE ‘%STATEMENT/%’;	开启指定监控项1
update performance_schema.setup_instruments set enabled = ‘YES’,TIMED=‘YES’ WHERE NAME LIKE ‘%stage/%’;	开启指定监控项2
update performance_schema.setup_instruments set enabled = ‘YES’,TIMED=‘YES’ WHERE NAME LIKE ‘%events_statements_/%’;	开启指定监控项3
update performance_schema.setup_instruments set enabled = ‘YES’,TIMED=‘YES’ WHERE NAME LIKE ‘%events_stages_/%’;	开启指定监控项4
SELECT EVENT_ID,TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration,SQL_TEXT from performance_schema.events_statements_history_long where SQL_TEXT like ‘%10001%’;	获得EVENT_ID
SELECT event_name as Stage,TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration from performance_schema.events_stages_history_long where nesting_event_id=	分析

OPTIMIZER_TRACE优化及跟踪
是Mysql5.6引入的一项跟踪功能。它可以跟踪优化器作出的各种决策(比如访问表的方法、各种开销计算、各种转换，并将跟踪过结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中，此功能默认关闭的，开启后，可分析如下语句：
SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IF、RETURN、CALL

参数

参数	说明
optimizer_trace	1、总开关，默认值：enabled=off，one_line=off。
enabled	是否开启optimizer_trace;on表示开启，off表示关闭
one_line	是否开启单行存储。on表示开启;off表示关闭，将会用标准的JSON格式化存储。设置成on将会有良好的格式，设置成off可节省一些空间。
optimizer_trace_features	控制optimizer_trace跟踪的内容，默认值：greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on,表示开启所有跟踪项。
greedy_search	是否跟踪贪心搜索
range_optimizer	是否跟踪范围优化器
dynamic_range	是否跟踪动态范围优化
repeated_subselect	是否跟踪子查询，如果设置成off，只跟踪第一条item_subselect的执行
optimizer_trace_limit	控制optimizer_trace展示多少条结果，默认是1
optimizer_trace_max_mem_size	optimizer_trace堆栈信息允许的最大内存，默认1048576
optimizer_trace_offset	第一个要展示的optimizer_trace的偏移量，默认-1
end_markers_in_json	如果JSON结构很大，则很难将右括号和左括号配对。

设置参数：

设置参数语句	说明
set OPTIMIZER_TRACE=“enabled=on”,END_MARKERS_IN_JSON=on;	打开OPTIMIZER TRACE
set GLOBAL OPTIMIZER_TRACE=“enabled=on”,END_MARKERS_IN_JSON=on;	全局开启
set optimizer_trace_offset=< OFFSET >,optimizer_trace_limit=< LIMIT >	有点类似与limit
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30	只查看近期30条

显示的字段
① trace
准备阶段(join_preparation)+优化阶段(join_optimization)+执行阶段(join_execution)
这里需要补充。。

数据库诊断命令
① show processlist = select * from INFORMATION_SCHEMA.PROCESSLIST
作用：show processlist用于查看当前正在运行的线程。如果执行此命令的用户拥有PROCESS权限，则可看到所有线程;否则只能看到自己的线程(即与当前登陆用户关联的线程)。如果不使用FULL关键字，只在Info字段中展示前100个字符。
背景：当遇到“too many connections”错误信息时，想要了解发生了什么，SHOW PROCESSLIST就非常有用。(Mysql保留了一个额外的连接，用于让拥有connection_admin[或已废弃的SUPER]权限的账户使用，从而确保管理员始终能够连接并检查系统。)
可使用Kill语句杀死线程。

字段

字段名	说明
Command	当前线程正在执行的命令。
Host	发出该语句的主机名
db	当前执行的命令是在哪个数据库
Time	线程处于当前状态的事件
State	指示现场正在执行的操作、事件或状态。
Info	当前线程正在执行的语句，如果未执行任何语句则值为NULL。

Command的取值
需要补充

-- 实用SQL
-- 按照客户端IP分组，看哪个客户端的连接数最多
select client_ip,count(client_ip) as client_num
from (select substring_index(host,':',1) as client_ip
		from 'infromation_schema'.processlist) as connect_info
group by client_ip
order by client_num desc;

-- 查看正在执行的线程，并按Time倒排序，看看有没有执行时间特别长的线程
select * from ‘information_schema’.processlist
where Command !='Sleep' order by Time desc;

-- 找出所有执行时间超过5分钟的线程，拼凑出kill语句，方便后面查杀
select conect('kill',id,';') from 'information_schema'.processlist
where Command = 'Sleep'
	and Time >300
	order by Time desc;

② SHOW STATUS
作用：查看服务器相关信息。
显示的内容

③ SHOW VARIABLES
Mysql的所有变量

④ SHOW TABLE STATUS
作用：查看表以及视图的状态

⑤ show index from employees;查看索引

⑥ show engine innodb status;查看引擎的相关信息

⑦ 展示有关master binlog文件的相关信息

⑧ show SLAVE status
作用：展示slave线程的相关信息

举例

Saas记账系统

Saas的记账系统，每增加一笔账都需要对同比和环比产生影响(原本每次需要8秒 ->改成存储过程后时间变成了5秒 ->SQL的调优+索引 (2秒) -> 同比和环比需要点击详细获得并且分页(500ms，但是详细不一定))。

架构选择应该考虑的问题
读写分离?高可用?实例个数?分库分表?用什么数据库?

索引数据结构

二叉树查找
说明：小于结点找左边，大于结点找右边。

平衡二叉搜索树(AVL树)
每个结点的左子树和右子树的高度相差不超过1
数的深度是log2n，查询时间复杂度是O(log2n)

B-Tree(Balance Tree)
结点 = 指针+关键字+数据

① 树的阶为m，那么根结点的子节点个数2<=x<=m
假设m=3,则根节点可以有2-3个孩子。
② 中间节点的子节点个数m/2<=y<=m
假设m=3,中间节点至少有2个孩子，最多3个孩子
③ 每个中间节点包含n个关键字，n=子节点个数-1，且按升序排序。
如果中间节点有3个子节点，则里面会有2个关键字，且按升序排序。
④ 指针 = 节点+1

B+Tree
B+Tree是在B-Tree基础上的一种优化
InnoDB存储引擎使用B+Tree实现其索引结构
① B+Tree有n个子节点的节点中含有n个关键字
B-Tree是n个子节点的节点有n-1个关键字
② B+Tree中，所有的叶子节点中包含了全部关键字的信息，且叶子节点按照关键字的大小自小而大顺序链接，构成一个有序链表B-Tree的叶子节点不包括全部关键字。
③ B+Tree中，非叶子节点仅用于索引，不保存数据记录，记录存放在叶子节点中。
B-Tree中，非叶子节点既能保存索引，也保存数据记录。

InnoDB存储方式(聚簇索引)
B+Tree
主键索引：叶子节点存储主键及数据
非主键索引(二级索引、辅助索引)：叶子节点存储索引以及主键。

MyISAM存储方式(非聚簇索引)
B+Tree
主键/非主键索引的叶子节点都是存储指向数据块的指针。

Hash索引
Hash索引 = keys +Buckets+entries
keys：
buckets：
entries：

hash索引支持情况
Memory引擎支持显式Hash索引
使用：创建表的时候

create table test_hash_table(
	name varchar(45) not null,
	age tinyint(4) not null,
	key using hash(name)
)engine = memory;

-- 换成InnoDB就不能成功
create table test_hash_table(
	name varchar(45) not null,
	age tinyint(4) not null,
	key using hash(name)
)engine = InnoDB;

-- 换成InnoDB就不能成功
create table test_hash_table(
	name varchar(45) not null,
	age tinyint(4) not null,
	key using hash(name)
)engine = InnoDB;
--但是InnoDB有一个开关
show variables like 'innodb_adaptive_hash_index'查看开关情况,默认打开
set global innodb_adaptive_hash_index ='OFF' # 关闭

空间索引(R-Tree索引)
存储GIS数据，基于R-Tree
MySQL5.7开始InnoDB支持空间索引
R-Tree介绍
空间索引使用

全文索引
适应全文搜索
MySQL5.7之前，全文索引不支持中文，经常搭配Sphinx。
MySQL5.7起，内置ngram，支持中文
ngram

B-Tree(B+Tree)&Hash索引特性与限制
B-Tree(B+Tree)特性
完全匹配：index(name) =>where name=‘大目’
范围匹配：index(age) =>where age>5
前缀匹配：index(name) =>where name like ‘大%’ # 左模糊不能使用索引

B-Tree(B+Tree)限制
index(name,age,sex)
where age = 5 and sex = 1 查询条件不包括最左列，无法使用索引。
where name=‘大目’ and sex=32 =>不能跳过索引，只能用name这一列
where name = ‘大目’ and age>32 and sex =1 =>某个列范围/模糊查询，则其右边所有列都无法使用索引，只能用name，age两列

Hash索引
一般性能比B-Tree(B+Tree)要好一些

Hash索引限制
(1) Hash索引并不是按照索引值排序，所以没法使用排序
不支持部分索引列匹配查找
(2) hash(a,b) =>where a=1
(3) 只支持等值查询(例如 = IN),不支持范围查询、模糊查询
(4) hash冲突越严重，性能下降越厉害

创建索引的原则
哪些场景建议创建索引？
(1) select语句，频繁作为where条件的字段(最左前缀原则)。
(2) update/delete语句的where条件。
(3) 需要分组以及排序的字段。
(4) distinct所使用的字段
(5) 字段的值有唯一性约束
(6) 对于多表查询，联接字段应创建索引，且类型务必保持一致(隐式转换：将无法使用)。
哪些场景建议创建索引？
(1) where子句里用不到的字段。
(2) 表的记录非常少。
(3) 有大量重复数据，选择性低。
(4) 索引的选择性越高，查询效率越好，因为可以在查找是过滤更多行。
(5) 频繁更新的字段，如果创建索引要考虑其索引维护开销。

索引失效与解决方案
1、索引列不独立。
独立：列不能是表达式的一部分，也不能是函数的参数。
2、使用了左模糊。(避免不了：搜索引擎)。
3、使用OR查询的部分字段没有索引(or的所有字段都要加索引)。
4、字符串条件未使用’'引起来。
5、不符合最左前缀原则的查询。
6、索引字段交易添加not null约束(字段 is null 无法使用索引)。
解决：把字段设置为not null，并设置默认值
7、隐式转换导致索引失效。
创建表的时候规范，同一字段类型一致。

索引调优技巧
1、长字段的索引调优
(1) 新建一个字段
(2) 将长字段值作为CRC32的参数，函数结果保存进新字段。
新字段的要求：
字段的长度应该比较的小，SHA1/MD5是不合适的。
应当尽量避免hash冲突，就目前来说，流行使用CRC32()或者FNV64()[注意：还需要原字段的比较条件]

前缀索引
(1) alter table employees add key (first_name(5))
最大选择性=索引选择性 = 不重复的索引值/数据表的总记录数。
数值越大，表示选择性越高，性能越好。
select count(distinct first_name)/count(*) ;
当截取字段长度为n且为最大选择性，那么去前n为前缀索引。

– 局限性：无法做order by 、group by ;无法使用覆盖索引
“后缀索引”：额外创建一个字段，比如说first_name+reverse,在存储的时候，把firstname的值翻转过来

2、使用组合索引的技巧

3、覆盖索引
对于索引X，SELECT的字段只需从索引就能获得，而无需到表数据里面获取，这样的索引就叫覆盖索引。
就是查询的=索引 或者 ∈ 索引
尽量只返回想要的字段
使用覆盖索引
减少网络传输的开销

4、排序优化

5、冗余、重复索引的优化
索引是有开销的！
重复索引：primary key 或 unique() 或 index() 修饰同一字段。
冗余索引：index(A,B) 或index(A) 共用，冗余索引。
index(A)和index(A,B) [表是拥有主键C]
index(A) => index(A,C)
index(A,B) =>index(A,B,C) order by C 无法使用。

6、未使用的索引

join语句的优化
1、join的种类？彼此的区别？
left join、right join、inner join 、left join + is null 、right join + is null、FULL OUTER JOIN、FULL OUTER JOIN + is null、cross join[笛卡尔连接]

2、join有哪些算法？
算法一：Nested-Loop Join(NLJ)：嵌套循环
算法二：Block Nested-Loop join(BNLJ)：(S*C) /join_buffer_size +1
S：缓存的t1/t2表的一行数据
C：缓存的行数
join_buffer_size:join buffer 的大小
是否用到缓存区：看explain的extra

使用join buffer的条件
(1) 连接类型是ALL、index或range
(2) 第一个nonconst table 不会分配join buffer，即使类型是ALL或者index。
(3) join buffer只会缓存需要的字段，而非整行数据。
(4) 可通过join_buffer_size变量设置join buffer大小。
show variables like ‘join_buffer_size’
set join_buffer_size = 1024102450;
set global join_buffer_size = 1024102450;
(5) 每个能被缓存的join都会分配一个join buffer，一个查询可能拥有多个join buffer
(6) join buffer在执行联接之前会分配，在查询完成后释放。

算法三：Batched Key Access Join(BKA)
MySQL5.6引入
BKA的基石：Multi Range Read(MRR)
可能会伴随大量的随机IO=> 数据按照主键排列，而不是from_date字段排列的。
MRR:将随机IO转换成顺序IO,从而提升性能。
optimizer_switch的子参数
mrr：是否开启mrr，on开启，off关闭
mrr_cost_based：表示是否要开启基于成本计算的MRR
read_rnd_buffer_size：指定mrr缓存大小

查看参数：
show variables like ‘%optimizer_switch%’
show variables like ‘%read_rnd_buffer_size%’
set optimizer_switch=‘mrr_cost_based=off’ 使用mrr

BKA流程
optimizer_switch的子参数
batched_key_access:on开启 off关闭
set optimizer_switch = ‘batched_key_access=on’';

算法四：JOIN算法4-HASH JOIN
Mysql8.0.18引入，用来替代BNLJ
join buffer缓存外部循环的hash表，内层循环遍历时到hash表匹配。
细节：https://mysqlserverteam.com/hash-join-in-mysql-8/

注意：
1、8.0.18有很多的限制，不能作用于外连接，比如left join/right join等等。
从8.0.20开始，限制少了很多，建议8.0.20或更高版本。
2、从Mysql8.0.18开始，hash join的join buffer是递增分配的，这意味着，你可以为将join_buffer_size设置得比较大。而在MySQL8.0.18中，如果你使用了外连接，外连接没法用hash join，此时join_buffer_size会按照你设置的值直接分配内存。因此join_buffer_size还是得谨慎设置。
3、从8.0.20开始，BNLJ已被删除了，用hash join替代了BNLJ。

HASH JOIN

驱动表vs被驱动表
驱动表：外层循环的表
被驱动表：内层循环的表
区分：
1、用explain执行，先执行的是驱动表
2、也可以可视化分析(idea)
选择语句 ->右击 选择Explain plan ->explain plan ->show vialazation(左边是驱动表)

join的优化
(1) 用小表驱动大表：一般无需人工考虑，关联查询优化器会自动选择最优的执行书序。
如果优化器故障，可使用STRAIGHT_JOIN。
(2) 如果有where条件，应当要能够使用索引，并尽可能地减少外层循环的数据量。
(3) join的字段尽量创建索引
执行一个sql以后，执行show warnings;//会说明索引没起作用的原因
(4) 尽量减少扫描的行数(explain-rows)
尽量控制在百万以内。
(5) 参与join的表不要太多。
阿里编程规约建议不超过三张。
要学会拆分语句。
(6) 如果被驱动表的join字段用不了索引，且内存较为充足，可以考虑把join buffer设置得大一些。

分页查询
limit
前一个参数越大，越慢。

分页查询的优化
(1) 覆盖索引(就是查询的数量<=索引，查询值∈索引集合)

(2) 覆盖索引+join
就是先查询符合条件的主键，再按照主键查询需要的值。
on h.emp_no = k.emp_no = using(emp_no)

(3) 覆盖索引+子查询
select * from employees where emp_no≥(select emp_no from employees limit 300000,1) limit 10;

(4) 范围查询 +limit语句(需要拿到上一页的最大值)
select * from employees where emp_no>10010 limit 10;

(5) 如果能得到起始和结束主键值
select * from employees where emp_no between 20000 and 20010;

(6) 禁止传入过大的页码

Count语句
1、当没有非主键索引，会使用主键索引。
2、如果存在非主键索引的话，会使用非主键索引。
3、如果存在多个非主键索引，会使用一个最小的非主键索引
innodb非主键索引
叶子节点存储的是：索引+主键
主键索引叶子节点：主键+表数据
在一个page里面，非主键索引可以存储更多的条目，对于一张表，1000000数据
使用非主键索引 扫描page100，主键索引500。

count(字段)只会针对该字段统计，使用这个字段上面的索引(如果有的话)
count(字段)会排除字段值=null的行
count()不会排除
count() =count(1)

对于MyISAM引擎，如果count()没有where条件(形如 select count() from 表名)，查询的语句select count() from 表名，查询的会非常的快。
对于Mysql8.0.13,InnoDB引擎，如果count() 没有where 条件(形如 select count(*) from 表名) ，查询也会被优化，性能有所提升

count优化
1、count( * ) 和count(1)一样
2、count( * )会选择最小的非主键索引，如果不存在任何非主键索引，则会使用主键。
3、count()不会排除为null的行，而count(字段)会排除
4、对于不带查询条件的count( * )语句，MyISAM及InnoDB(MyuSQL>=8.0.13),都做了优化
5、如果没有特殊需求，尽量用COUNT()

如何优化count语句
show create table salaries;//存储引擎
select version();
innodb版本>8.0.13,可以针对无条件的count语句去优化

1、创建一个更小的非主键索引
2、把数据库引擎换成MyISAM
3、汇总表 table[table_name,count] 记录。
好处：结果比较准确。
缺点：增加了维护的成本。
4、sql_calc_found_rows
select * from slaries limit 0,10;
select count(*) from salaries;

select sql_calc_found_rows * from slaries limit 0,10; //自动执行count
select found_rows() as salary_count;
缺点：8.0.17已经被废弃了。
注意点：需要在MYSQL终端执行，IDEA无法正常返回结果。

5、方案5：缓存select count(*) from salaries;存放到缓存
优点：性能比较高;结果比较准确，有误差但是比较小(除非在缓存更新的期间，新增或者删除了大量数据。)
缺点：引入了额外的组件，增加了架构的复杂度，

6、方案6：information_schema.tables
select * from ‘information_schema’.tables
where TABLE_SCHEMA = ‘employees’ and TABLE_NAME=‘salaries’;
好处：不操作salaries表
缺点：估算值，并不是准确值。

7、方案7
show table status where name = ’salaries‘;

8、方案8
explain 语句
rows = 行数

优点：不操作原表
缺点：估算值，并不是准确值

优化：
大于某个值计数
=总数-小于某值的计数

order by
最好的做法：利用索引避免排序
利用索引本身的有序性，让MySQL跳过排序的过程。

1、当全表排序，mysql会发现全表扫描比索引扫描更低时，会选择全表扫描。
select * from employees order by first_name,last_name ;//all
select * from employees order by first_name,last_name limit 10;//index

2、可以使用索引避免排序(符合索引原则)。
select * from employees where first_name=‘Bader’
order by last_name;//索引 [first_name ,last_name,emp_no]

3、可以使用索引避免排序
select * from employees where first_name>’Bader‘ order by first_name;

4、升降序不一致
select * from employees order by first_name desc,last_name asc limit 10;

5、前一个范围查询，后一个排序（不能避免排序）
select * from employees where first_name <‘Bader’ order by last_name;

排序模式
1、rowid排序(常规排序)
(1) 从表中获取满足where条件的记录
(2) 对于每条记录，将记录的主键及排序键(id,order_column)取放入sort buffer(sort_buffer_size控制)
(3) 如果sort buffer能存放所有满足条件的(id,order_column),则进行排序;否则sort buffer满后，排序并写到临时文件
排序算法：快速排序算法
(4) 若排序中产生了临时文件，需要利用归并排序算法，从而保证记录有序。

group by
1、松散索引扫描(Loose Index Scan)：无需扫描满足条件的所有索引键即可返回结果
selcect emp_no,min(salary) from salaries group by emp_no;
//原来是 每次取出所有员工的工资，求最小值
//优化(松散索引扫描)：得到员工的所有工资，取第一个

性能依次递减

使用松散扫描的条件
(1) Using index for group-by 使用了松散索引扫描。
(2) 查询作用在单张表上，Group指定的所有字段要符合最左前缀原则，且没有其他字段。
(3) 如果存在聚合函数，只支持MIN()/MAX()，并且如果同时使用MIN()和MAX()，则必须作用在同一个字段。聚合函数作用的字段必须在索引中，并且要紧跟Group by 所指定的字段。
(4) 如果查询中存在除GROUP BY 指定的列以外的其他部分，则必须以常量的形式出现。
select c1,c3 from t1 Group by c1,c2;//不能使用
select c1,c3 from t1 where c3 =3 group by c1,c2
(5) 索引必须索引整个字段的值，不能是前缀索引
比如有字段c1 varchar(20),但如果该字段使用的是前缀索引index(c1(10)) 而不是index(c1)，无法使用松散索引扫描。

不能使用松散索引扫描的SQL一览
– 聚合函数不是MIN()或MAX()
select c1,SUM(c2) from t1 group by c1;
– 不符合最左前缀原则
select c1,c2 from t1 group by c2,c3;
– 查询c3字段，但是c3字段上没有等值查询
select c1,c3 from t1 group by c1,c2;
改成：select c1,c3 from t1 where c3 = const group by c1,c2

能使用松散索引扫描的条件
1、AVG(DISTINCT)、SUM(DISTINCT)、COUNT(DISTINCT)，其中AVG(DISTINCT)、SUM(DISTINCT) 可接受单个参数，而COUNT(distinct)可接受多个参数。
2、查询中必须不存在GROUP BY 或DISTINCT语句
3、满足前面所有使用松散索引扫描的条件。

紧凑索引扫描(Tight Index Scan)：explain-extra没有明显的标识
1、需要扫描满足条件的所有索引键才能返回结果
性能一般比松散索引扫描差，但一般都可接受

select emp_no,sum(salary) from salaries group by emp_no;

临时表(Temporary table)：explain-extra using temporary
紧凑索引扫描也没有办法使用的话，MySQL将会读取需要的数据，并建立一个临时表，用临时表实现GROUP BY操作。

Distinct优化

表结构设计优化
第一范式(1NF)：字段具有原子性，即数据库表的每一个字段都是不可分割的原子数据项，不能是集合、数组、记录等非原子数据项。
当实体中的某个属性有多个值时，必须拆分为不同的属性。
第二范式(2NF):满足1NF的基础上，要求每一行数据具有唯一性，并且非主键字段完全依赖主键字段。
第三范式(3NF):满足2NF的基础上，不能存在传递依赖(避免冗余)。

反模式设计
1、为了提高查询性能。

表设计原则
1、字段少而精，建议20个以内，超过可以拆分。
把常用的字段放到一起，把不常用的字段独立出去，大字段(TEXT/BLOB/CLOB等等独立出去)
2、尽量用小型字段
用数字代替字符串
3、避免使用允许为NULL的字段
允许NULL字段很难查询优化
允许为NULL字段的索引需要额外空间。
4、合理平衡范式和冗余。
5、如果数据量非常大，考虑分库分表。

Percona Toolkit
本文基于Percona Toolkit3.2.0，理论支持所有版本。
Percona Toolkit是一款MySQL世界里面非常实用的工具套件。

工具列表

工具名	说明
pt-align	对齐其他工具的输出
pt-archiver	将数据归档到其他表或文件
pt-config-diff	比较配置文件和变量
pt-deadlock-logger	记录MySQL死锁
pt-diskstats	交互式IO监控工具
pt-duplicate-key-checker	找到重复的索引或外键
pt-fifo-split	模拟分割文件并输出
pt-find	查找表，并执行命令
pt-fingerprint	将查询转换成fingerprint
pt-fk-error-logger	记录外键错误信息
pt-heartbeat	监控MySQL复制延迟
pt-index-usage	通过日志分析查询，并分析查询如何使用索引
pt-ioprofile	监控进程IO并打印IO活动表
pt-kill	kill掉符合条件查询
pt-mext	并行查询SHOW GLOBAL STATUS 的样本信息
pt-mongodb-query-digest	通过汇总赖子MongoDB查询分析器(query profiler)的查询来报告查询使用情况统计信息
pt-mongodb-summary	收集有关MongoDB集群的信息，它从多个来源收集信息从而提供集群的概要信息
pt-mysql-summary	展示MySQL相关的概要信息
pt-online-schema-change	在线修改表结构。无需锁表地ALTER表结构
pt-pg-summary	收集有关PostgreSQL集群的信息
pt-pmp	针对指定程序，聚合GDB的stack traces
pt-query-digest	从日志、processlist以及tcpdump中分析MySQL查询
pt-secure-collect	收集、清理、打包、加密数据
pt-show-grants	规范化打印MySQL授权
pt-sift	浏览由pt-stalk创建的文件
pt-slave-delay	使MySQL从属服务器滞后于其Master
pt-slave-find	查询和打印MySQL slave，并在发生错误后重启
pt-stalk	发生问题时收集有关MySQL的诊断数据
pt-summary	展示系统概要信息
pt-table-checksum	验证MySQL主从复制的一致性
pt-table-sync	高效同步表数据
pt-table-usage	分析查询是如何使用表的
pt-upgrade	验证不同服务器上的查询结构是否相同

windows系统

Linux系统安装
操作系统
Deblan7(“wheezy”)
Debian8(“jessle”)
Ubuntu 14.04 LTS(Trusty Tahr)
Ubuntu 16.04 LTS(Xenial xerus)
Ubuntu 16.10(Yakkety Yak)
Ubuntu 17.04(Zesty Zapus)
Red Hat Enterprise Linux or CentOS 6(Santiago)
Red Hat Enterprise Linux or CentOS 7(Maipo)

查看自己用的是哪个Linux发行版
支持其他操作系统

macOS系统的安装

后面听。。。

pt-query-digest
show variables like ‘%slow%’;//慢查询是否打开
show variables like ‘log_output’;//输出到哪里

1、切换目录到mysql下面
2、pt-query-digest 慢查询日志文件名字。
pt-query-digest --since=12h 12小时内
pt-query-digest --since ’ ’ --until ’ ’ 多久到多久
…

结果可视化界面
Percona官方
Query Digest UI
Box Anemometer
这两款不建议用于生产。

pt-index-usage
作用：通过日志文件分析查询，并分析查询如何使用索引

原理：
1、清点数据库所有的表和索引，并将表中现有的索引和日志中的查询所使用的索引进行比较
2、对日志中的每个查询运行EXPLAIN(这一步使用单独的数据库连接清点表并执行EXPLAIN)
3、对于无用的索引，展示删除的语句

注意
此工具使用Mysql资源比较多，因此，在使用此工具时候
(1）如果有条件，尽量不能直接在生产环境执行，而应在相同表结构的数据库环境执行。
(2) 如果必须在生产环境执行，请避开高峰期，比如在凌晨低谷期执行。
(3) 这里只是慢查询中未使用到的索引，其他在其他地方使用到了。

alter table employees
alter index emploees_first_name_last_name_birth_date
invisible;

pt-variable-advisor
作用：分析Mysql变量，并对可能出现的问题提出建议。
原理：show variables，并分析变量的值设置不合理，给出建议。

pt-online-schema-change
作用：在线修改表结构，无需锁表地ALTER表结构
原理
(1) 创建一张一模一样的表，表名一般是_new后缀
(2) 在新表上执行更改字段操作
(3) 在原表上加三个触发器，分别对应与DELETE/UPDATE/INSERT操作，并将原表中要执行的语句也在新表中执行。
(4) 将原表的数据拷贝到新表中
(5) 使用原子的RENAME TABLE操作同时重命名原始表和新表，完成此操作后，删除原始表。

pt-summary

pt-mysql-summary --user=root --password=root123 --host=localhost --port=3306

pt-visual-explain

第40周 Linux调优与架构调优