不用索引怎么优化百亿数据？ | MySQL性能优化篇

数据库调优

一、数据库调优原理

1.1 为什么要进行MySQL数据库调优？

提升网站整体通吐量，优化用户体验数据库是关键之一

• 流畅页面的访问速度
• 良好的网站功能体验
• 避免网站页面出现访问错误
  由于数据库连接timeout产生页面5xx错误
  由于慢查询造成页面无法加载
  由于阻塞造成数据无法提交

增加数据库的稳定性

• 很多数据库问题都是由于低效的查询引起的

1.2 什么影响数据库性能？

• 服务器： OS、CPU、memory、network
• MySQL ：
  数据库表结构【对性能影响最大】
  低下效率的SQL语句
  超大的表
  大事务
  数据库配置
  数据库整体架构
  …

1.3 数据库调优到底调什么？

优化SQL语句调优：根据需求创建结构良好的SQL语句【实现同一个需求，SQL语句写法很多】

数据库表结构调优：
MySQL配置调优：最大连接数，连接超时，线程缓存，查询缓存，排序缓存，连接查询缓存…
OS底层优化：tcp连接数，打开文件数，线程栈大小…
服务器硬件优化：多核CPU、更大内存

二、数据库压力测试

2.1 什么是压测？

压力测试是给软件不断加压，强制其在极限的情况下运行，观察它可以运行到何种程度，从而发现性能缺陷，是通过搭建与实际环境相似的测试环境，通过测试程序在同一时间内或某一段时间内，向系统发送预期数量的请求、测试系统在不同压力情况下的效率状况，以及系统可以承受的压力情况。然后做针对性的测试与分析，找到影响系统性能的瓶颈，评估系统在实际使用环境下的效率情况，评价系统性能以及判断是否需要对应用系统进行优化处理或结构调整。并对系统资源进行优化。压力测试是对系统不断施加压力，来获得系统最大服务能力的测试。
为什么对系统压测呢？有没有必要。压不压测要看场景！
一般而言，只有在系统基础功能测试验证完成、系统趋于稳定的情况下，才会进行压力测试。

目的是什么？

1. 当负载逐渐增加时，观察系统各项性能指标的变化情况是否有异常
2. 发现系统的性能短板，进行针对性的性能优化
3. 判断系统在高并发情况下是否会报错，进程是否会挂掉
4. 测试在系统某个方面达到瓶颈时，系统可以支持的最大负载
5. …

压测性能指标有哪些？
以上主要的四种性能指标【响应时间、并发用户数、吞吐量、资源使用率】它们之间存在一定的相关性，共同反映出性能的不同方面。
响应时间越短、同时承受的并发数越多、吞吐量会越大、占用的资源越少，则表明系统性能越好，反之性能则越差！

常用压测工具

1. Apache JMeter : 可视化的测试工具
2. Apache的ab压力测试
3. Nginter 韩国研发
4. PAS 阿里测试工具
5. MeterSphere ：国内持续测试的开源平台

JMeter压测环境架构图：
压测的目标总的来说有4条：1.负载上升各项指标是否正常、2.发现性能短板、3.高并发下系统是否稳定、4.系统最大负载
基于上述压测目标，同时由于测试容易受到网络抖动干扰 所影响。因此，为保证测试结果准确可靠，压力测试应在内网进行；

2.2 JMeter简介

https://jmeter.apache.org/
http://www.jmeter.com.cn/2747.html
Apache JMeter是Apache组织开发的基于Java的压力测试工具。用于对软件做压力测试，它最初被设计用于Web应用测试，但后来扩展到其他测试领域。 它可以用于测试静态和动态资源，例如静态文件、Java 小服务程序、CGI 脚本、Java 对象、数据库、FTP 服务器， 等等。JMeter 可以用于对服务器、网络或对象模拟巨大的负载，来自不同压力类别下测试它们的强度和分析整体性能。另外，JMeter能够对应用程序做功能/回归测试，通过创建带有断言的脚本来验证你的程序返回了你期望的结果。为了最大限度的灵活性，JMeter允许使用正则表达式创建断言。

基本使用参考压测入门

2.3 驱动下载

在测试计划中我们要及时的添加JDBC驱动链接。这里我用的mysql数据库是5.7版本，那么我相对应的JDBC驱动选择了5.x版本。JDBC驱动可以在mysql的官网下载，具体地址是：https://dev.mysql.com/downloads/file/?id=477058
下载驱动界面，不需要登录，直接下载即可：

2.4 测试过程

1. 配置数据库驱动

下载后解压文件夹，把文件夹中的mysql-connector-java-8.0.17.jar copy到jmeter安装目录的bin文件下（其实不用放在bin目录下，只需要使用jmeter浏览jar所在位置即可）

2. 配置线程组

3. 配置JDBC 连接池

添加JDBC Connection Configuration（JDBC连接池也有人叫连接组）
需要设置jdbc线程池名称，这个变量在JDBC Request中要使用的；还有要设置Database URL，格式为：
jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC&characterEncoding=utf-8
注意：
？后面的serverTimezone=UTC&characterEncoding=utf-8不能缺少，否则会报时区错误。在配置的时候，jmeter如果报1045-Access denied for user ‘root’@‘localhost’（using password: YES）这类错误，请重置访问用户的密码，以及给与该用户权限。
重要配置说明
Variable Name ：数据库连接池的名称
JDBC Connection Configuration 算是一个数据库连接池配置
Variable Name：连接池唯一标识，后面JDBC Request需要用到。
Max Number of Connection： 池中允许的最大连接数，可以设置为20，也可以将其设置为零（0），这意味着没有线程池。
Max Wait：参数表示从连接池获取连接的超时等待时间，单位毫秒
Database URL 数据库连接 URL
JDBC Driver class 数据库驱动
Username 数据库登录用户名
Password 数据库登录密码
注意：
一个测试计划可以有多个JDBC Connection Configuration配置，只要名称不重复即可。JDBCConnection Configuration其实就是连接池配置。

思考：

1. 是不是连接数越多服务性能越强呢？
2. 从连接池获取连接的等待时间越短效率越高呢？

其他基本保持默认就行，也可根据需要进行修改 ，如下是所有参数详解：

1. 连接池参数配置：

字段	含义
Max Number ofConnections	最大连接数；做性能测试时，可以填 0。在开发的项目中按实际代码填写，默认是20。
Max Wait(ms)	在连接池中取回连接最大等待时间，单位毫秒
Time Between Eviction Runs(ms)	运行清除空闲connection的销毁线程间隔时间
Auto Commit	自动提交sql语句，如：修改数据库时，自动 commit
Transaction isolation	事务隔离级别
Preinit Pool	立即初始化连接池如果为 False，则第一个 JDBC 请求的响应时间会较长，因为包含了连接池建立的时间

• Transaction Isolation： 事务间隔级别设置，主要有如下几个选项：（对JMX加解密）
  TRANSACTION_NODE 事务节点
  TRANSACTION_READ_UNCOMMITTED 事务未提交读
  TRANSACTION_READ_COMMITTED 事务已提交读
  TRANSACTION_SERIALIZABLE 事务序列化
  DEFAULT 默认
  TRANSACTION_REPEATABLE_READ 事务重复读、

2. 校验连接池

字段	含义
Test While Idle	空闲时测试
Soft Min Evictable Idle Time(ms)	最小可收回空闲时间(ms)
Validation Query	一个简单的查询，用于确定数据库是否仍在响应，默认为jdbc驱动程序的 isValid() 方法，适用于许多数据库

3. 配置数据库连接

字段	含义
Database URL	数据库连接 URL
JDBC Driver class	数据库驱动
Username	数据库登录用户名
Password	数据库登录密码
Connection Properties	建立连接时要设置的连接属性

4. 常见数据库的连接 URL和驱动：

数据库	驱动	URL
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://host:port/{dbname}
PostgreSQL	org.postgresql.Driver	jdbc:postgresql:{dbname}
Oracle	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:user/pass@//host:port/service
sqlServer	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:sqlserver://host:port;databaseName=databaseName

4. 添加JDBC 请求
   右键点击“连接mysql”，再添加一个采样器：JDBC request，在jmeter中request可以编辑select和insert等不同的采样器类别。即通过不同的类别添加配置我们需要的对mysql不同的操作。比如

select id from tb_seckill_goods where id=1; 

参数讲解：

1. Variable Name：数据库连接池的名字，需要与JDBC Connection Configuration的Variable NameBound Pool名字保持一致
2. Query Type：此处支持方式多样，可以用于添加或者筛选数据，根据需要和Query配合使用；

• select statemen 查询
• update statement 更新
• prepared select statement 预处理参数查询
• prepared update statement 预处理参数更新

3. Query：填写的sql语句未尾可以不加“;”
4. Parameter valus：参数值，顺序替代Query中的?；
   此处对应Query中的”?”，有几个”?”则此处要填写几个值，以”,”分隔；
5. Parameter types：参数类型

• 可参考：Javadoc for java.sql.Types
• Parameter types则必须和Parameter values一一对应，且类型必须正确；

6. Variable names：保存sql语句返回结果的变量名 ，用于作为参数供调用
7. Result variable name：创建一个对象变量，保存所有返回的结果 ，供调用；
8. Query timeout：查询超时时间
9. Handle result set：定义如何处理由callable statements语句返回的结果。

5. 添加结果监听器

• 聚合报告
• 查看结果树
• 活动线程数Active Threads Over Time
• 每秒事务数TPS
• 平均响应时间RT
• 服务端：内存、网络、CPU、磁盘io、网络io【单位mb】

6) 查看测试结果

测试结论：连接数为0，数据库1.5W+的TPS

三、连接池

使用druid作为数据源： 连接池相关参数配置：

# 连接池配置
# 初始化连接数
spring.datasource.druid.initial-size=1
# 最小空闲连接数，一般设置和initial-size一致
spring.datasource.druid.min-idle=1
# 最大活动连接数
spring.datasource.druid.max-active=20
# 从连接池获取连接超时时间
spring.datasource.druid.max-wait=60000
# 配置间隔多久启动一次销毁线程，对连接池内的空闲的connection进行检测，单位是毫秒。
# 1.如果连接空闲并且超过minIdle以外的连接，如果空闲时间超过
minEvictableIdleTimeMillis设置的连接物理关闭。
# 2.在minIdle以内的不处理。
spring.datasource.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中连接最小可清理的空闲时间，单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
# 打开后，增强timeBetweenEvictionRunsMillis的周期性连接检查，minIdle内的空闲连接
# 设置从连接池获取连接时是否检查连接有效性，true时，每次都检查;false时，不检查
spring.datasource.druid.test-on-borrow=false
# 设置往连接池归还连接时是否检查连接有效性，true时，每次都检查;false时，不检查
spring.datasource.druid.test-on-return=false
# 设置从连接池获取连接时是否检查连接有效性
# 为true时，如果连接空闲时间超过minEvictableIdleTimeMillis进行检查，否则不检查
# 为false时，不检查
spring.datasource.druid.test-while-idle=true
# 检验连接是否有效的查询语句
# 如果数据库Driver支持ping()方法，则优先使用ping()方法进行检查，否则使用
validationQuery查询进行检查
spring.datasource.druid.validation-query=select 1 from dual
# 每次检查强制验证连接有效性
spring.datasource.druid.keep-alive=true

3.1 压力测试连接池参数设置

3.1.1 MaxWait

参数表示从连接池获取连接的超时等待时间，单位毫秒。
注意：这个参数只管理获取连接的超时。获取连接等待的直接原因池里没有可用连接，具体包括如下四种情况：

• 连接池未初始化
• 连接长久未使用已被释放，
• 连接使用中需要新建连接
• 连接池已耗尽需等待连接用完后归还
  这里有一个很关键的点是 maxWait 未配置或者配置为 0 时，表示不设等待超时时间。也就是无限制等待
  如果不配置maxWait，后果会怎么样呢？可能有些应用就这么干，来做个案例：

maxWait=0, 
maxActive=5, 

正常流量下业务没有发现任何问题，但突发大流量涌入时，造成连接池耗尽，所有新增的DB请求处于等待获取连接的状态中。由于 maxWait=0 表示无限等待，在请求速度大于处理速度的情况下等待队列会越排越长，最终业务上的表现就是业务接口大量超时，流量越大造成实际吞吐量反而越低。

并发5：

并发10：

并发20:
结论：配置建议，如果内网状态良好，获取连接等待时间800，网络状况不佳，推荐设置为1200。原因是TCP重连的时间一般是1秒。

3.1.2 MaxActive

线程数：20
ramp-up：1
循环次数：5000

最大连接池数量，允许的最大同时使用中的连接数。
最大连接：10
最大连接数：20
最大连接数：30

注意：配置 maxActive 千万不要好大喜多。
虽然配置大了看起来业务流量飙升后还能处理更多的请求，但切换到DB视角会发现其实连接数的增多在很多场景下反而会减低吞吐量。
我举个栗子：缓存刷新，在更新热点数据时DB 查询耗时如果很高，这时再让更多的连接操作DB，就有点像假日往高速上涌入的车辆，只会给DB添堵。因为DB处理能力有限！开辟更多的连接并不能提升DB处理的效率！

结论&推荐配置：大多数场景下，20连接足够使用了，当然这个参数的配置还需要结合业务场景的特点给与配置。一般标准是配置成为正常使用连接数的3-4倍即可！
为什么最大连接数设置的过多并不是一件好事？

# 查看数据库中的最大连接数
SHOW VARIABLES LIKE 'max_connections';

• 首先，20个连接可以产生足够的吞吐量，只要SQL执行不耗时，20个连接足以产生1w以上TPS
• 其次，如果设置过大，多个服务连接数据库超过数据库最大的连接数，会出现资源争抢踩踏，导致服务器报错。反而会造成服务器性能下降。
• 大多数业务场景及应用中，设置为10、20、30均为合适的值，判断标准主要是应用的数量，及数
  据库最大连接数的值。如果只有一个数据库配置一个应用，可以设置为0。

3.3 连接属性设置

之前在配置JDBC的连接池的时候讲过两个参数：serverTimezone=UTC&characterEncoding=utf-8。

接下来再说两个参数在网络方面有很大作用；主要应对在网络异常模式下，数据库无法释放连接的问题；

1. connectTimeout：表示等待和MySQL数据库建立socket链接的超时时间如果与服务器(这里指数据库)请求建立连接的时间超过ConnectionTimeOut，就会抛连接超时异常，即服务器连接超时。
2. socketTimeout：表示客户端和MySQL数据库建立socket后，读写socket时的等待的超时时间
   • 如果与服务器连接成功，就开始数据传输了。如果服务器处理数据用时过长，超过了SocketTimeOut，就会抛出SocketTimeOutExceptin，即服务器响应超时，服务器没有在规定的时间内返回给客户端数据。

jdbc:mysql://172.26.233.200:3306/jingnan_all?
serverTimezone=UTC&characterEncoding=utf-
8&connectionTimeout=3000&socketTimeout=1200

小结：

• 一个请求包含完整的三个阶段：1.建立连接，2.数据传输，3.断开连接

• ConnectionTime默认值是0，表示不会连接超时。配置的单位是毫秒

• socketTimeout可以不设置，默认值是30分钟，在Linux中配置，单位毫秒

• 推荐配置：connectionTimeout3000，socketTimeout1200

• 主要解决的问题：在网络异常情况下，网络连接被耗尽，缺无法及时失效，从而导致后续请求不能正常进入。

四、SQL语句优化【开发人员】

4.1 查看SQL执行计划【EXPLAIN】

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。使用explain这个命令来查看一个这些SQL语句的执行计划，查看
该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:
在MySQL中可以使用EXPLAIN查看SQL执行计划，用法：
sql EXPLAIN SELECT * FROM tb_seckill_goods

• id：SELECT识别符，这是SELECT查询序列号。
• select_type：表示单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询
• 等复杂查询。
• table：表示查询的表。
• partitions：使用的哪些分区（对于非分区表值为null）。
• type（重要）表示表的连接类型。
• possible_keys：此次查询中可能选用的索引，一个或多个
• key：查询真正使用到的索引
• key_len：显示MySQL决定使用的索引长度。
• ref：哪个字段或常数与 key 一起被使用
• rows：显示此查询一共扫描了多少行，这个是一个估计值。不精确值
• filtered: 表示此查询条件所过滤的数据的百分比
• Extra：额外信息

4.2 关键结果说明

-- 01 simple： 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。
explain select * from  tb_seckill_goods;
-- 02 union： union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
-- 语句1
explain select * from tb_seckill_goods a  union select *  from tb_seckill_goods b;-- UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：-- 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED-- UNION RESULT 从UNION表获取结果的SELECT
-- 语句2
explain select * from ( select * from tb_seckill_goods a union select * from tb_seckill_goods b) c;
-- DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
-- dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
explain select * from tb_seckill_goods a where a.id in (select id from tb_seckill_goods b union select id from tb_seckill_goods c);
-- 03 subquery 除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
explain select (select id from tb_seckill_goods where price=5346.94) fromtb_spu;
-- dependent subquery 与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
explain select (select id from tb_seckill_goods a where a.id=b.id) from tb_seckill_goods b;

4.2.1 select_type

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。
有以下几种值：

1. simple： 普通查询，表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，
   外层的查询为simple。
2. primary： 查询的主要部分，一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。
3. union：连接查询
   • derived在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
   • union若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
   • union result 从UNION表获取结果的SELECT，
4. dependent union： 依赖连接查询，与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
5. subquery： 子查询，除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
6. dependent subquery： 依赖子查询，与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
7. derived：派生表， from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

01-simple简单查询

-- simple： 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询
为simple。
explain select * from tb_seckill_goods;

02-union连接查询

-- 语句1
explain select * from tb_seckill_goods a union select * from tb_seckill_goods b;-- UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：-- 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
-- 语句2
explain select * from ( select * from tb_seckill_goods a union select * fromtb_seckill_goods b) c;
-- union： union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
-- DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

-- dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
explain select * from tb_seckill_goods a where a.id in (select id from tb_seckill_goods b union select id from tb_seckill_goods c);
-- UNION RESULT 从UNION表获取结果的SELECT

03-subquery子查询

-- subquery 除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
explain select (select id from tb_seckill_goods where price=5346.94) from tb_spu;

-- dependent subquery 与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
explain select (select id from tb_seckill_goods a where a.id=b.id) from tb_seckill_goods b;

4.2.2 type

-- const 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，
-- 通常type是const。其他数据库也叫做唯一索引扫描。
explain select * from tb_seckill_goods where id=1;
-- eq-ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。
-- 常见于主键或唯一索引扫描
explain select * from tb_seckill_goods a left join tb_seckill_goods b on a.id=b.id;
-- ref 非唯一性索引扫描，返回匹配某个单独值的所有行
-- 本质上也是一种索引访问，它返回所有匹配某个单独值的行，
-- 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
-- 组合索引
explain select * from tb_seckill_goods where title="华为5G手机";
explain select * from tb_seckill_goods a left join tb_seckill_goods b on a.title=b.title;
-- 非唯一索引
explain select * from tb_seckill_goods where price=5346.94;
-- range 索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
explain select * from tb_seckill_goods where title like '华为%';
-- index 查询结果列中使用到了索引，type会显示为index。
-- 全部索引扫描，把索引从头到尾扫一遍，
-- 常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
explain select title from tb_seckill_goods;
-- all 这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
explain select * from tb_seckill_goods;

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：

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

• system：表中只有一行数据或者是空表。等于系统表，这是const类型的特列，平时不会出现，可以忽略不计
• const（重要）：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。
• eq_ref（重要）：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
• ref（重要）：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。组合索引非唯一索引
• fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
  ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
• unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
• index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
• range（重要）：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
• index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所有索引，性能可能大部分时间都不如range
• index（重要）：select结果列中使用到了索引，type会显示为index。全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
• all（重要）：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

以下是Type详解：

01-const：唯一索引或主键
使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

explain select * from tb_seckill_goods where id=1; 

02-eq_ref：唯一性索引
唯一性索引查询，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引查询

explain select * from tb_seckill_goods a left join tb_seckill_goods b on a.id=b.id;

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

-- ref 组合索引
explain select * from tb_seckill_goods where title="华为5G手机";
explain select * from tb_seckill_goods a left join tb_seckill_goods b ona.title=b.title;

非唯一索引

-- ref 非唯一索引
explain select * from tb_seckill_goods where price=5346.94;

05-index：查询结果列中使用索引
select结果列中使用到了索引，type会显示为index。全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

explain select title from tb_seckill_goods; 

06-all：全表扫描
这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

explain select * from tb_seckill_goods; 

注意事项：
除了all之外，其他的type都可以使用到索引最少要使用到range级别

4.2.3 Extra
这个列包含不适合在其他列中显示的，但十分重要的额外的信息，这个列可以显示的信息非常多，有几十种。解释几个经常遇到的

01-Using filesort

• 使用了文件排序，说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。这种操作需要优化sql。

-- 无索引，按照文件排序
explain select price from tb_seckill_goods where price >100 order by cost_price;
-- 有索引，不按照文件排序
explain select price from tb_seckill_goods where price >100 order by price;

02-using index
表示相应的SELECT查询中使用到了索引，避免访问表的数据行，这种查询的效率很高！

• 如果同时出现Using Where ，索引在where之后，用作查询条件
• 如果没有同时出现Using Where ，索引在where之前，用作查询结果读取

-- 使用where，索引在where之后，用作查询条件
explain select id,title,price from tb_seckill_goods where price>100;
-- 没有使用where，索引在where之前，用作查询结果读取
explain select id,title,price from tb_seckill_goods where title="华为5G手机";

03-using where
表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；

-- 只有where
explain select * from tb_seckill_goods where cost_price>100;

04-Using join buffer

• 表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

4.3 索引优化

• 为搜索字段（where中的条件）、排序字段、select查询列，创建索引
  • 不过要考虑数据的业务场景：查询多还是增删多
  • 也要考虑字段内容是否适合建立索引？如何判断，请关注索引篇。
• 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。
• 尽量使用覆盖索引，SELECT语句中尽量不要使用*。
• order by、group by语句要尽量使用到索引
• 索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引
• 索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。
• order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

4.3.1 使用索引查询需要注意

索引可以提高查询的速度，但并不是使用了带有索引的字段查询都会生效，有些情况下是不生效的，需要注意！
设置索引要付出代价的：

• 一是增加了数据库的存储空间
• 二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

索引使用口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上不计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有OR，索引失效要少用。

我举几个栗子：

4.3.2 使用LIKE关键字

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效。

4.3.3 使用联合索引的查询

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

4.3.4 使用OR关键字的查询

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

id是含有索引的
cid 是有索引的
price 是没有索引的

4.4 LIMIT优化

如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。

SELECT * FROM user WHERE username=’it雄’; -- username没有建立唯一索引
SELECT * FROM user WHERE username=’it雄’ LIMIT 1;

处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE；

LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。

解决方案：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows

select * from (select * from tuser2 where id > 1000000 and id < 1000500 ORDER BY id) t limit 0, 20

4.5 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然很灵活，但是执行效率并不高。那么问题又来了啊？ 什么
叫子查询？为什么它效率不高？
（把内层查询结果当作外层查询的比较条件的）

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以子查询的速度会受到一定的影响。这多了一个创建临时表和销毁表的过程。
优化方式：可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

4.6 其他查询优化

• 小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。
• 避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
• 避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）
• JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。

SELECT * FROM orders o LEFT JOIN user u on o.user_id = u.id orders表中的user_id和user表中的id，类型要一致

• WHERE条件中尽量不要使用not in语句（建议使用not exists）
• 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

4.7 SQL语句性能分析

4.7.1 什么是Profile？

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。
通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MySQL 5.0.37以及以上版本中才有实现。
默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。

4.7.2 开启Profile功能

Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。
查看是否开启了Profile功能:

select @@profiling;
-- 或者
show variables like '%profil%';

开启profile功能

set profiling=1; --1是开启、0是关闭 

4.7.3 基本使用

• show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.
• show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由
  变量:profiling_history_size 控制,默认15条
  
• show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列
  
• show profile 还可根据 show profiles 列表中的 Query_ID ,选择显示某条记录的性能分析信息
  type是可选的，取值范围可以如下：
  • ALL 显示所有性能信息
  • BLOCK IO 显示块IO操作的次数
  • CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动
  • CPU 显示用户CPU时间、系统CPU时间
  • IPC 显示发送和接收的消息数量
  • MEMORY [暂未实现]
  • PAGE FAULTS 显示页错误数量
  • SOURCE 显示源码中的函数名称与位置
  • SWAPS 显示SWAP的次数

4.7.4 分析案例

查看是否打开了性能分析功能

select @@profiling; 

打开 profiling 功能

set profiling=1; 

执行sql语句
执行 show profiles 查看分析列表
查询第二条语句的执行情况

show profile for query 2; 

可指定资源类型查询

show profile cpu,swaps for query 2; 

4.7.5 小结

• converting HEAP to MyISAM 查询结果太大，内存不够用往磁盘上搬；
• Creating tmp table 创建临时表：拷贝数据到临时表，用完再删除；
• Copying to tmp table on disk 把内存临时表复制到磁盘，危险。

五、数据库优化

如何发现复杂的SQL有问题？一个个去explain吗？你有没有这样的困惑，开发代码运行顺畅丝滑，上生产了却卡的一逼？

# 举个栗子：查询20秒
explain select tk.id,ts.* from tb_seckill_goods ts LEFT JOIN tb_sku tk ON tk.id=ts.id where ts.id>100 order by ts.price;
# 举个栗子：查询20秒
select sleep(2);

5.1 慢查询日志

数据库性能问题，根据经验来说，80%以上都是由于慢SQL造成的。
数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的SQL。
MySQL数据库“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到症结所在，以便对症下药。至于查询时间的多少才算慢，每个项目、业务都有不同的要求。MySQL的慢查询日志功能默认是关闭的，需要手动开启。

5.1.1 开启慢查询日志

查看是否开启慢查询功能

# 查看是否开启慢查询日志
show variables like '%slow_query%';
show variables like 'long_query_time%';

• 【slow_query_log】 ：是否开启慢查询日志，1为开启，0为关闭。
• 【log-slow-queries】 ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
• 【slow-query-log-file】：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
• 【long_query_time】 ：慢查询阈值，当查询时间多于设定的阈值时，记录日志，【单位为秒】。

开启慢查询功能
注意：打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。

# 开启慢查询日志
set global slow_query_log=on;
# 大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉
set global long_query_time=1;
# 记录没有索引的查询。
set global log_queries_not_using_indexes=on;

5.1.2 慢查询日志格式

# Time: 2021-07-27T08:32:44.023309Z
# User@Host: root[root] @ [172.26.233.201] Id: 1243
# Query_time: 218.295526 Lock_time: 0.000126 Rows_sent: 10959
Rows_examined: 10929597
use jingnan_all;
SET timestamp=1627374764;
# 慢查询SQL语句
select tk.id,ts.* from tb_seckill_goods ts LEFT JOIN tb_sku tk ON
tk.id=ts.id where ts.id>100 order by ts.price;

日志解析：

• 第一行,SQL查询执行的具体时间
• 第二行,执行SQL查询的连接信息，用户和连接IP
• 第三行,记录了一些我们比较有用的信息，如下解析

Query_time,这条SQL执行的时间,越长则越慢
Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
Rows_sent,查询返回的行数
Rows_examined,查询检查的行数，越长就当然越费时间

• 第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。
• 第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

5.1.3 分析慢查询日志工具

使用mysqldumpslow工具，mysqldumpslow是MySQL自带的慢查询日志工具。可以使用mysqldumpslow工具搜索慢查询日志中的SQL语句。
得到按照时间排序的前10条里面含有左连接的查询语句：

[root@localhost mysql]# mysqldumpslow -s t -t 10 -g “left join”
/var/lib/mysql/slow.log

常用参数说明：

-s：是表示按照何种方式排序
al 平均锁定时间
ar 平均返回记录时间
at 平均查询时间（默认）
c 计数
l 锁定时间
r 返回记录
t 查询时间

-t：是top n的意思，即为返回前面多少条的数据
-g：后边可以写一个正则匹配模式，大小写不敏感的

[root@mysql132 mysql]# mysqldumpslow -s t
/var/lib/mysql/mysql132-slow.log

---

Reading mysql slow query log from /var/lib/mysql/mysql132-slow.log
Count: 1 Time=143.16s (143s) Lock=0.00s (0s) Rows=27907961.0 (27907961),
root[root]@localhost
select * from t_slow a left join t_slow b on a.name=b.name

---

Count: 5 Time=5.80s (28s) Lock=0.00s (0s) Rows=0.0 (0),
root[root]@localhost
insert into t_slow(name,address) select name,address from t_slow

---

Count: 1 Time=3.01s (3s) Lock=0.00s (0s) Rows=1.0 (1),
root[root]@localhost
select sleep(N)

5.2 连接数max_connections

同时连接客户端的最大数量，默认值 151，最小值1.
连接数导致问题：ERROR 1040，TooManyConnections原因如下

• 第一：访问确实太高，MySQL有点扛不住了，考虑扩容
• 第二：MySQL的max_connection配置少了

# 查看 max_connections
show global variables like 'max_connections'
# 设置 max_connections（立即生效重启后失效）
set global max_connections = 800;
# 这台MySQL服务器最大连接数是256，然后查询一下服务器使用过的最大连接数：
show global status like 'Max_used_connections';
# MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是：Max_used_connections / max_connections * 100% ≈ 85%  最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高
了。

Max_connection可以无限大吗？
https://dev.mysql.com/doc/refman/5.7/en/connection-interfaces.html
MySQL支持的最大连接数取决于如下几个主要因素

• 可使用内存
• 每个连接占用的内存
• 连接响应时间
• …

一般情况下，Linux操作系统支持最大连接数范围500-1000之间，最大链接数上限10w。如果想设置为最大，要么你得有足够的资源，要么就是你可以接收很长的响应时间。
建议设置：最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。可以下调

MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该不会出现1040错误
比较理想的设置是：Max_used_connections / max_connections * 100% ≈ 85%

5.3 线程使用情况

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。

根据测试发现，以上服务器线程缓存thread_cache_size没有进行设置，或者设置过小，MySQL服务器一直在创建线程销毁线程。增加这个值可以改善系统性能。通过比较 Connections 和 Threads_created
状态的变量，可以看到这个变量的作用。

Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size配置：

# 查询线程使用情况
show global status like 'Thread%';
# 查询线程缓存
show variables like 'thread_cache_size';
# 增加thread_cache_size的值
set global thread_cache_size = 64;

根据物理内存建议设置规则如下：

1G —> 8
2G —> 16
3G —> 32
大于3G —> 64

效果不明显

5.4 数据库优化-结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。这句话是什么意思呢？就是说我们的数据库优化不仅仅要局限于查询优化，要从这块跳出来做好最开始的设计优化，如果你这个主要设计是不合理的这些个查询优化效果也只是杯水车薪。
需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

5.4.1. 将字段很多的表分解成多个表（分表）

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
项目实战的时候会将一个完全信息的表里面的数据拆分出来 形成多个新表 每个新表负责那一块的数据查询 然后这个拆分是定时的

5.4.2. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

通常都是在统计当中有使用啊，每次统计报表的时候都是离线统计啊，后台有有一个线程对你这统计结果查询号放入一个中间表，然后你对这个中间表查询就行了。

举个栗子：比如我们需要五张表联查，left join每次要查询5张表，如果我们做了一个中间表，把这五张表的查询结果放在这里面，直接查询这个表，是不是就变成了单表查询呀？秒不秒

5.4.3. 增加冗余字段

设计数据表时应尽量遵循关系数据库范式的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是合理的加入冗余字段可以提高查询速度。
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

七、服务器层面优化

1、缓冲区优化

将数据保存在内存中，保证从内存读取数据

• 设置足够大的 innodb_buffer_pool_size ，将数据读取到内存中。

建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5.

• 怎样确定 innodb_buffer_pool_size 足够大。数据是从内存读取而不是硬盘？
  

2、降低磁盘写入次数

• 对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志
• 使用足够大的写入缓存 innodb_log_file_size

推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

• 设置合适的innodb_flush_log_at_trx_commit，和日志落盘有关系。

3、MySQL数据库配置优化

• 表示缓冲池字节大小。
  推荐值为物理内存的50%~80%。

innodb_buffer_pool_size 

用来控制redo log刷新到磁盘的策略。

innodb_flush_log_at_trx_commit=1 

每提交1次事务同步写到磁盘中，可以设置为n。

sync_binlog=1 

脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。

innodb_max_dirty_pages_pct=30

后台进程最大IO性能指标。
默认200，如果SSD，调整为5000~20000

innodb_io_capacity=200 

在MySQL5.1.X版本中，由于代码写死，因此最多只会刷新100个脏页到磁盘、合并20个插入缓冲，即使磁盘有能力处理更多的请求，也只会处理这么多，这样在更新量较大（比如大批量INSERT）的时候，脏页刷新可能就会跟不上，导致性能下降。
而在MySQL5.5.X版本里，innodb_io_capacity参数可以动态调整刷新脏页的数量，这在一定程度上解决了这一问题。
innodb_io_capacity参数默认是200，单位是页。该参数设置的大小取决于硬盘的IOPS，即每秒的输入输出量（或读写次数）。
至于什么样的磁盘配置应该设置innodb_io_capacity参数的值是多少，大家可参考下表。

指定innodb共享表空间文件的大小。

innodb_data_file_path

慢查询日志的阈值设置，单位秒。

long_qurey_time=0.3

mysql复制的形式，row为MySQL8.0的默认形式。

binlog_format=row

调高该参数则应降低interactive_timeout、wait_timeout的值。

max_connections=200

过大，实例恢复时间长；过小，造成日志切换频繁。

innodb_log_file_size

全量日志建议关闭。默认关闭。

general_log=0

MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：

# 01-缓冲区，将数据保存在内存中，保证从内存读取数据。建议innodb_buffer_pool_size设置为
总内存大小的3/4或者4/5.
innodb_buffer_pool_size=
# 02-降低磁盘写入次数。推荐 innodb_log_file_size 设置为 0.25 *
innodb_buffer_pool_size
innodb_log_file_size=
# 03-表示缓冲池字节大小。推荐值为物理内存的50%~80%。
innodb_buffer_pool_size=
# 04-用来控制redo log刷新到磁盘的策略。
innodb_flush_log_at_trx_commit=1
# 05-每提交1次事务同步写到磁盘中，可以设置为n。
sync_binlog=1
# 06-脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。  推荐值为25%~50%。
innodb_max_dirty_pages_pct=30
# 07-后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200
# 08-指定innodb共享表空间文件的大小。
innodb_data_file_path
# 09-慢查询日志的阈值设置，单位秒。
long_qurey_time=3
# 10-mysql复制的形式，row为MySQL8.0的默认形式。
binlog_format=row
# 11-调高该参数则应降低interactive_timeout、wait_timeout的值。
max_connections=200
# 12-过大，实例恢复时间长；过小，造成日志切换频繁。
innodb_log_file_size
# 13-全量日志建议关闭。默认关闭。
general_log=0

4、服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。

CPU的选择:

• 对于数据库并发比较高的场景，CPU的数量比频率重要。
• 对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好。