容桂品牌网站建设优惠,wordpress登陆后跳转到首页,网站规划与设计期末大作业怎么做,牡丹江百度推广文章目录数据库调优一、数据库调优原理1.1 为什么要进行MySQL数据库调优#xff1f;1.2 什么影响数据库性能#xff1f;1.3 数据库调优到底调什么#xff1f;二、数据库压力测试2.1 什么是压测#xff1f;2.2 JMeter简介2.3 驱动下载2.4 测试过程三、连接池3.1 压力测试连接…
文章目录数据库调优一、数据库调优原理1.1 为什么要进行MySQL数据库调优1.2 什么影响数据库性能1.3 数据库调优到底调什么二、数据库压力测试2.1 什么是压测2.2 JMeter简介2.3 驱动下载2.4 测试过程三、连接池3.1 压力测试连接池参数设置3.1.1 MaxWait3.1.2 MaxActive3.3 连接属性设置四、SQL语句优化【开发人员】4.1 查看SQL执行计划【EXPLAIN】4.2 关键结果说明4.2.1 select_type4.2.2 type4.3 索引优化4.3.1 使用索引查询需要注意4.3.2 使用LIKE关键字4.3.3 使用联合索引的查询4.3.4 使用OR关键字的查询4.4 LIMIT优化4.5 子查询优化4.6 其他查询优化4.7 SQL语句性能分析4.7.1 什么是Profile4.7.2 开启Profile功能4.7.3 基本使用4.7.4 分析案例4.7.5 小结五、数据库优化5.1 慢查询日志5.1.1 开启慢查询日志5.1.2 慢查询日志格式5.1.3 分析慢查询日志工具5.2 连接数max_connections5.3 线程使用情况5.4 数据库优化-结构优化5.4.1. 将字段很多的表分解成多个表分表5.4.2. 增加中间表5.4.3. 增加冗余字段七、服务器层面优化1、缓冲区优化2、降低磁盘写入次数3、MySQL数据库配置优化4、服务器硬件优化数据库调优
一、数据库调优原理
1.1 为什么要进行MySQL数据库调优
提升网站整体通吐量优化用户体验数据库是关键之一
流畅页面的访问速度良好的网站功能体验避免网站页面出现访问错误 由于数据库连接timeout产生页面5xx错误 由于慢查询造成页面无法加载 由于阻塞造成数据无法提交
增加数据库的稳定性
很多数据库问题都是由于低效的查询引起的
1.2 什么影响数据库性能
服务器 OS、CPU、memory、networkMySQL 数据库表结构【对性能影响最大】 低下效率的SQL语句 超大的表 大事务 数据库配置 数据库整体架构 …
1.3 数据库调优到底调什么
优化SQL语句调优根据需求创建结构良好的SQL语句【实现同一个需求SQL语句写法很多】
数据库表结构调优 MySQL配置调优最大连接数连接超时线程缓存查询缓存排序缓存连接查询缓存… OS底层优化tcp连接数打开文件数线程栈大小… 服务器硬件优化多核CPU、更大内存
二、数据库压力测试
2.1 什么是压测
压力测试是给软件不断加压强制其在极限的情况下运行观察它可以运行到何种程度从而发现性能缺陷是通过搭建与实际环境相似的测试环境通过测试程序在同一时间内或某一段时间内向系统发送预期数量的请求、测试系统在不同压力情况下的效率状况以及系统可以承受的压力情况。然后做针对性的测试与分析找到影响系统性能的瓶颈评估系统在实际使用环境下的效率情况评价系统性能以及判断是否需要对应用系统进行优化处理或结构调整。并对系统资源进行优化。压力测试是对系统不断施加压力来获得系统最大服务能力的测试。 为什么对系统压测呢有没有必要。压不压测要看场景 一般而言只有在系统基础功能测试验证完成、系统趋于稳定的情况下才会进行压力测试。
目的是什么
当负载逐渐增加时观察系统各项性能指标的变化情况是否有异常发现系统的性能短板进行针对性的性能优化判断系统在高并发情况下是否会报错进程是否会挂掉测试在系统某个方面达到瓶颈时系统可以支持的最大负载…
压测性能指标有哪些 以上主要的四种性能指标【响应时间、并发用户数、吞吐量、资源使用率】它们之间存在一定的相关性共同反映出性能的不同方面。 响应时间越短、同时承受的并发数越多、吞吐量会越大、占用的资源越少则表明系统性能越好反之性能则越差
常用压测工具
Apache JMeter : 可视化的测试工具Apache的ab压力测试Nginter 韩国研发PAS 阿里测试工具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/?id477058 下载驱动界面不需要登录直接下载即可
2.4 测试过程
配置数据库驱动 下载后解压文件夹把文件夹中的mysql-connector-java-8.0.17.jar copy到jmeter安装目录的bin文件下其实不用放在bin目录下只需要使用jmeter浏览jar所在位置即可 配置线程组 配置JDBC 连接池 添加JDBC Connection ConfigurationJDBC连接池也有人叫连接组 需要设置jdbc线程池名称这个变量在JDBC Request中要使用的还有要设置Database URL格式为 jdbc:mysql://localhost:3306/dbname?serverTimezoneUTCcharacterEncodingutf-8 注意 后面的serverTimezoneUTCcharacterEncodingutf-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其实就是连接池配置。 思考
是不是连接数越多服务性能越强呢从连接池获取连接的等待时间越短效率越高呢
其他基本保持默认就行也可根据需要进行修改 如下是所有参数详解
连接池参数配置
字段含义Max Number ofConnections最大连接数做性能测试时可以填 0。在开发的项目中按实际代码填写默认是20。Max Wait(ms)在连接池中取回连接最大等待时间单位毫秒Time Between Eviction Runs(ms)运行清除空闲connection的销毁线程间隔时间Auto Commit自动提交sql语句如修改数据库时自动 commitTransaction isolation事务隔离级别Preinit Pool立即初始化连接池如果为 False则第一个 JDBC 请求的响应时间会较长因为包含了连接池建立的时间
Transaction Isolation 事务间隔级别设置主要有如下几个选项对JMX加解密 TRANSACTION_NODE 事务节点 TRANSACTION_READ_UNCOMMITTED 事务未提交读 TRANSACTION_READ_COMMITTED 事务已提交读 TRANSACTION_SERIALIZABLE 事务序列化 DEFAULT 默认 TRANSACTION_REPEATABLE_READ 事务重复读、
校验连接池
字段含义Test While Idle空闲时测试Soft Min Evictable Idle Time(ms)最小可收回空闲时间(ms)Validation Query一个简单的查询用于确定数据库是否仍在响应默认为jdbc驱动程序的 isValid() 方法适用于许多数据库
配置数据库连接
字段含义Database URL数据库连接 URLJDBC Driver class数据库驱动Username数据库登录用户名Password数据库登录密码Connection Properties建立连接时要设置的连接属性
常见数据库的连接 URL和驱动
数据库驱动URLMySQLcom.mysql.jdbc.Driverjdbc:mysql://host:port/{dbname}PostgreSQLorg.postgresql.Driverjdbc:postgresql:{dbname}Oracleoracle.jdbc.driver.OracleDriverjdbc:oracle:thin:user/pass//host:port/servicesqlServercom.microsoft.sqlserver.jdbc.SQLServerDriverjdbc:sqlserver://host:port;databaseNamedatabaseName
添加JDBC 请求 右键点击“连接mysql”再添加一个采样器JDBC request在jmeter中request可以编辑select和insert等不同的采样器类别。即通过不同的类别添加配置我们需要的对mysql不同的操作。比如
select id from tb_seckill_goods where id1; 参数讲解
Variable Name数据库连接池的名字需要与JDBC Connection Configuration的Variable NameBound Pool名字保持一致Query Type此处支持方式多样可以用于添加或者筛选数据根据需要和Query配合使用
select statemen 查询update statement 更新prepared select statement 预处理参数查询prepared update statement 预处理参数更新
Query填写的sql语句未尾可以不加“;”Parameter valus参数值顺序替代Query中的? 此处对应Query中的”?”有几个”?”则此处要填写几个值以”,”分隔Parameter types参数类型
可参考Javadoc for java.sql.TypesParameter types则必须和Parameter values一一对应且类型必须正确
Variable names保存sql语句返回结果的变量名 用于作为参数供调用Result variable name创建一个对象变量保存所有返回的结果 供调用Query timeout查询超时时间Handle result set定义如何处理由callable statements语句返回的结果。
添加结果监听器
聚合报告查看结果树活动线程数Active Threads Over Time每秒事务数TPS平均响应时间RT服务端内存、网络、CPU、磁盘io、网络io【单位mb】 6) 查看测试结果 测试结论连接数为0数据库1.5W的TPS
三、连接池
使用druid作为数据源 连接池相关参数配置
# 连接池配置
# 初始化连接数
spring.datasource.druid.initial-size1
# 最小空闲连接数一般设置和initial-size一致
spring.datasource.druid.min-idle1
# 最大活动连接数
spring.datasource.druid.max-active20
# 从连接池获取连接超时时间
spring.datasource.druid.max-wait60000
# 配置间隔多久启动一次销毁线程对连接池内的空闲的connection进行检测单位是毫秒。
# 1.如果连接空闲并且超过minIdle以外的连接如果空闲时间超过
minEvictableIdleTimeMillis设置的连接物理关闭。
# 2.在minIdle以内的不处理。
spring.datasource.druid.time-between-eviction-runs-millis60000
# 配置一个连接在池中连接最小可清理的空闲时间单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis300000
# 打开后增强timeBetweenEvictionRunsMillis的周期性连接检查minIdle内的空闲连接
# 设置从连接池获取连接时是否检查连接有效性true时每次都检查;false时不检查
spring.datasource.druid.test-on-borrowfalse
# 设置往连接池归还连接时是否检查连接有效性true时每次都检查;false时不检查
spring.datasource.druid.test-on-returnfalse
# 设置从连接池获取连接时是否检查连接有效性
# 为true时如果连接空闲时间超过minEvictableIdleTimeMillis进行检查否则不检查
# 为false时不检查
spring.datasource.druid.test-while-idletrue
# 检验连接是否有效的查询语句
# 如果数据库Driver支持ping()方法则优先使用ping()方法进行检查否则使用
validationQuery查询进行检查
spring.datasource.druid.validation-queryselect 1 from dual
# 每次检查强制验证连接有效性
spring.datasource.druid.keep-alivetrue3.1 压力测试连接池参数设置
3.1.1 MaxWait
参数表示从连接池获取连接的超时等待时间单位毫秒。 注意这个参数只管理获取连接的超时。获取连接等待的直接原因池里没有可用连接具体包括如下四种情况
连接池未初始化连接长久未使用已被释放连接使用中需要新建连接连接池已耗尽需等待连接用完后归还 这里有一个很关键的点是 maxWait 未配置或者配置为 0 时表示不设等待超时时间。也就是无限制等待 如果不配置maxWait后果会怎么样呢可能有些应用就这么干来做个案例
maxWait0,
maxActive5, 正常流量下业务没有发现任何问题但突发大流量涌入时造成连接池耗尽所有新增的DB请求处于等待获取连接的状态中。由于 maxWait0 表示无限等待在请求速度大于处理速度的情况下等待队列会越排越长最终业务上的表现就是业务接口大量超时流量越大造成实际吞吐量反而越低。
并发5 并发10
并发20: 结论配置建议如果内网状态良好获取连接等待时间800网络状况不佳推荐设置为1200。原因是TCP重连的时间一般是1秒。
3.1.2 MaxActive 线程数20 ramp-up1 循环次数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的连接池的时候讲过两个参数serverTimezoneUTCcharacterEncodingutf-8。
接下来再说两个参数在网络方面有很大作用主要应对在网络异常模式下数据库无法释放连接的问题
connectTimeout表示等待和MySQL数据库建立socket链接的超时时间如果与服务器(这里指数据库)请求建立连接的时间超过ConnectionTimeOut就会抛连接超时异常即服务器连接超时。socketTimeout表示客户端和MySQL数据库建立socket后读写socket时的等待的超时时间 如果与服务器连接成功就开始数据传输了。如果服务器处理数据用时过长超过了SocketTimeOut就会抛出SocketTimeOutExceptin即服务器响应超时服务器没有在规定的时间内返回给客户端数据。 jdbc:mysql://172.26.233.200:3306/jingnan_all? serverTimezoneUTCcharacterEncodingutf- 8connectionTimeout3000socketTimeout1200 小结 一个请求包含完整的三个阶段1.建立连接2.数据传输3.断开连接 ConnectionTime默认值是0表示不会连接超时。配置的单位是毫秒 socketTimeout可以不设置默认值是30分钟在Linux中配置单位毫秒 推荐配置connectionTimeout3000socketTimeout1200 主要解决的问题在网络异常情况下网络连接被耗尽缺无法及时失效从而导致后续请求不能正常进入。
四、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
idSELECT识别符这是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 price5346.94) fromtb_spu;
-- dependent subquery 与dependent union类似表示这个subquery的查询要受到外部表查询的影响
explain select (select id from tb_seckill_goods a where a.idb.id) from tb_seckill_goods b;4.2.1 select_type
单位查询的查询类型比如普通查询、联合查询(union、union all)、子查询等复杂查询。 有以下几种值
simple 普通查询表示不需要union操作或者不包含子查询的简单select查询。有连接查询时 外层的查询为simple。primary 查询的主要部分一个需要union操作或者含有子查询的select位于最外层的单位查询的select_type即为primary。union连接查询 derived在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表中union若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVEDunion result 从UNION表获取结果的SELECT dependent union 依赖连接查询与union一样出现在union 或union all语句中但是这个查询要受到外部查询的影响subquery 子查询除了from字句中包含的子查询外其他地方出现的子查询都可能是subquerydependent subquery 依赖子查询与dependent union类似表示这个subquery的查询要受到外部表查询的影响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表获取结果的SELECT03-subquery子查询
-- subquery 除了from字句中包含的子查询外其他地方出现的子查询都可能是subquery
explain select (select id from tb_seckill_goods where price5346.94) from tb_spu;-- dependent subquery 与dependent union类似表示这个subquery的查询要受到外部表查询的影响
explain select (select id from tb_seckill_goods a where a.idb.id) from tb_seckill_goods b;4.2.2 type
-- const 使用唯一索引或者主键返回记录一定是1行记录的等值where条件时
-- 通常type是const。其他数据库也叫做唯一索引扫描。
explain select * from tb_seckill_goods where id1;
-- eq-ref 唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。
-- 常见于主键或唯一索引扫描
explain select * from tb_seckill_goods a left join tb_seckill_goods b on a.idb.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.titleb.title;
-- 非唯一索引
explain select * from tb_seckill_goods where price5346.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之后但是实际上由于要读取所有索引性能可能大部分时间都不如rangeindex重要select结果列中使用到了索引type会显示为index。全部索引扫描把索引从头到尾扫一遍常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。all重要这个就是全表扫描数据文件然后再在server层进行过滤返回符合要求的记录。
以下是Type详解
01-const唯一索引或主键 使用唯一索引或者主键返回记录一定是1行记录的等值where条件时通常type是const。其他数据库也叫做唯一索引扫描。
explain select * from tb_seckill_goods where id1; 02-eq_ref唯一性索引 唯一性索引查询对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引查询
explain select * from tb_seckill_goods a left join tb_seckill_goods b on a.idb.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.titleb.title;非唯一索引
-- ref 非唯一索引
explain select * from tb_seckill_goods where price5346.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 price100;
-- 没有使用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_price100;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, 204.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语句执行的各种资源消耗情况比如CPUIO等以及该SQL执行所耗费的时间等。不过该工具只有在MySQL 5.0.37以及以上版本中才有实现。 默认的情况下MYSQL的该功能没有打开需要自己手动启动。
4.7.2 开启Profile功能
Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。 查看是否开启了Profile功能:
select profiling;
-- 或者
show variables like %profil%;开启profile功能
set profiling1; --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 profiling1; 执行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.idts.id where ts.id100 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_logon;
# 大于1秒钟的数据记录到慢日志中如果设置为默认0则会有大量的信息存储在磁盘中磁盘很容易满掉
set global long_query_time1;
# 记录没有索引的查询。
set global log_queries_not_using_indexeson;5.1.2 慢查询日志格式
# Time: 2021-07-27T08:32:44.023309Z
# UserHost: 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 timestamp1627374764;
# 慢查询SQL语句
select tk.id,ts.* from tb_seckill_goods ts LEFT JOIN tb_sku tk ON
tk.idts.id where ts.id100 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条里面含有左连接的查询语句 [rootlocalhost 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后边可以写一个正则匹配模式大小写不敏感的 [rootmysql132 mysql]# mysqldumpslow -s t /var/lib/mysql/mysql132-slow.log Reading mysql slow query log from /var/lib/mysql/mysql132-slow.log Count: 1 Time143.16s (143s) Lock0.00s (0s) Rows27907961.0 (27907961), root[root]localhost select * from t_slow a left join t_slow b on a.nameb.name Count: 5 Time5.80s (28s) Lock0.00s (0s) Rows0.0 (0), root[root]localhost insert into t_slow(name,address) select name,address from t_slow Count: 1 Time3.01s (3s) Lock0.00s (0s) Rows1.0 (1), root[root]localhost select sleep(N) 5.2 连接数max_connections
同时连接客户端的最大数量默认值 151最小值1. 连接数导致问题ERROR 1040TooManyConnections原因如下
第一访问确实太高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_commit1 每提交1次事务同步写到磁盘中可以设置为n。
sync_binlog1 脏页占innodb_buffer_pool_size的比例时触发刷脏页到磁盘。 推荐值为25%~50%。
innodb_max_dirty_pages_pct30后台进程最大IO性能指标。 默认200如果SSD调整为5000~20000
innodb_io_capacity200 在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_time0.3mysql复制的形式row为MySQL8.0的默认形式。
binlog_formatrow调高该参数则应降低interactive_timeout、wait_timeout的值。
max_connections200过大实例恢复时间长过小造成日志切换频繁。
innodb_log_file_size全量日志建议关闭。默认关闭。
general_log0MySQL的配置参数都在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_commit1
# 05-每提交1次事务同步写到磁盘中可以设置为n。
sync_binlog1
# 06-脏页占innodb_buffer_pool_size的比例时触发刷脏页到磁盘。 推荐值为25%~50%。
innodb_max_dirty_pages_pct30
# 07-后台进程最大IO性能指标。默认200如果SSD调整为5000~20000
innodb_io_capacity200
# 08-指定innodb共享表空间文件的大小。
innodb_data_file_path
# 09-慢查询日志的阈值设置单位秒。
long_qurey_time3
# 10-mysql复制的形式row为MySQL8.0的默认形式。
binlog_formatrow
# 11-调高该参数则应降低interactive_timeout、wait_timeout的值。
max_connections200
# 12-过大实例恢复时间长过小造成日志切换频繁。
innodb_log_file_size
# 13-全量日志建议关闭。默认关闭。
general_log04、服务器硬件优化
提升硬件设备例如选择尽量高频率的内存频率不能高于主板的支持、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。
CPU的选择:
对于数据库并发比较高的场景CPU的数量比频率重要。对于CPU密集型场景和频繁执行复杂SQL的场景CPU的频率越高越好。
