摘要:查询性能优化优化数据访问检查是否检索大量超过需要的数据是否访问太多行或太多列增加网络开销消耗和内存资源检查服务器层是否在分析大量超过需要的数据行重构查询的方式切分查询有时对于一个大查询我们需要分而治之切分成小查询每次只完成一部分分解关联查询
1. 查询性能优化 1.1 优化数据访问
检查是否检索大量超过需要的数据.是否访问太多行或太多列,增加网络开销,消耗cpu和内存资源
检查服务器层是否在分析大量超过需要的数据行
1.2 重构查询的方式 1.2.1 切分查询有时对于一个大查询我们需要分而治之,切分成小查询每次只完成一部分
1.2.2 分解关联查询缓存效率更高: 方便缓存单表查询结果
执行单个查询可以减少锁的竞争
在应用层做关联,更容易对数据库进行拆分,更容易做到高性能和可扩展
使用in 代替关联查询可能比随机的关联要高效
可以减少冗余记录的查询
1.3 查询执行的基础 1.3.1 查询流程先检查缓存
sql解析,预处理,优化器生成相应的执行计划
调用存储引擎的api执行查询
1.3.2 通信协议半双工,任何一时刻要么是服务器向客户端发送数据,要么是客户端向服务端发送数据
客户端从服务器获取数据时,实际是MySQL向客户端推送数据的过程
1.3.3 查询状态Sleep: 线程正在等待客户端发送新的请求
Query: 线程正在执行查询或者正在将结果发送给客户端
Locked: 服务器层,线程正在等待表锁
Analyzing and statistics: 线程正在收集存储引擎统计信息,并生成查询的执行计划
Copying to tmp table: 线程正在执行查询,并且将结果集复制到一个临时表中.常见group by或文件排序操作
Sorting result: 线程正在对结果进行排序
Sending data: 线程可能在多个状态之间传送数据或在生成结果集或向客户端返回数据
1.3.4 查询优化 1.3.4.1 语法解析器和预处理通过关键字将sql语句进行解析,生成对应的解析树
解析器使用语法规则验证和解析查询
预处理器进一步检查解析树是否合法,验证权限
1.3.4.2 查询优化器一条查询可以有多种执行方式,优化器找到其中最好的执行计划,MySQL使用基于成本的优化器
优化类型
重新定义关联表的顺序
外联结转化成内连接
使用等价变换规则
优化count, min, max函数
预估并转化为常数表达式
覆盖索引扫描
子查询优化
提前终止查询,如limit
等值传播
In优化
1.3.4.3 关联查询嵌套循环: 先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,如果最后一个联表无法找到更多的行,则返回上一层次关联表
UNION查询和子查询时都会将临时结果存放到一个临时表中
1.3.4.4 执行计划MySQL生成一棵指令树,通过存储引擎执行完成并返回结果
1.3.4.5 排序优化排序是一个成本很高的操作
MySQL排序: 如果数据量小,则在内存中进行; 数据量大则先分块再排序再合并
MySQL4.1后使用单次传输排序: 先读取查询所需要的所有列,再根据给定列排序
1.3.4.6 查询执行引擎根据执行计划的指令逐步执行
1.3.4.7 返回结果给客户端如果查询可以缓存,则缓存在这个阶段进行
返回结果的过程是一个增量逐步返回的过程,一旦开始生成第一条结果时就可以开始向客户端返回结果集
1.4 查询优化器的局限子查询相对糟糕(不是绝对),如子查询用in
联表查询与子查询根据场景不同有不同优势
MySQL无法将限制条件下推到子查询
索引合并优化
MySQL无法利用多核特性并行执行查询
MySQL并不支持哈希关联, MariaDB已经实现了真正的哈希关联
松散索引扫描,无法按照不连续的方式扫描一个索引
最大值最小值函数的优化一般
不允许同一张表上同时查询和更新, 如update set 等于 select 自己.解决方法,可以通过关联临时表
1.5 查询优化器的提示设置查询优化器参数,可以阅读官方手册
一般除非需要,修改查询优化器参数会提高维护成本
1.6 优化特定类型的查询关联查询: on的列加索引; 使用group by和order by 只使用一个表的列可以利用索引
优化LIMIT分页: 尽量使用覆盖索引
子查询: 尽量使用关联查询替换
静态查询分析: Percona Toolkit中的pt-query-advisor能解析查询日志,分析查询模式
使用用户自定义变量: 无法使用查询缓存,可能被优化器优化掉
2. MySQL高级特性 2.1 分区表 2.1.1 应用表非常大无法全部放在内存中,或者只在表的最后部分有热点数据其他均是历史数据
分区表的数据更容易维护
分区表的数据可以分布在不同的物理设备上
使用分区表避免某些瓶颈,如InnoDB单个索引的互斥访问
备份和恢复独立分区,对于大数据集效果较好
2.1.2限制一个表最多1024个分区
分区表达式必须是整数或返回整数的表达式
如果分区字段有主键或唯一索引列,那么所有主键列和唯一索引都必须包含进来
分区表中无法使用外键约束
2.1.3 原理分区表由多个相关的底层表实现,存储引擎管理它们跟管理普通表一样
select 查询: 分区层打开并锁住所有底层表,优化器判断是否过滤分区,在调用存储引擎api访问各个分区数据
insert: 分区层打开并锁住所有底层表,确定分区,写入
delete: 分区层打开并锁住所有底层表,确定数据所在分区,删除
update: 分区层打开并锁住所有底层表,确定分区,取出数据,更新,确定分区,写入
打开并锁住所有底层表: 如果存储引擎实现行级锁如InnoDB,则会在分区层释放表锁
2.1.4 分区表类型根据范围进行分区: 每个分区储存落在某个范围的记录
根据键值进行分区,减少InnoDB互斥量竞争
使用数学模函数进行分区,然后将数据轮询放入不同的分区,适用于只想保留几天的数据
2.1.5 使用问题回顾:数据量很大时,除非是索引覆盖查询,否则数据库需要根据索引扫描回表查询,产生大量的随机IO,数据库响应时间很大
全量扫描数据不要索引,根据分区定位数据位置
索引数据,分离热点. 将热点数据多带带放在一个分区
NULL值会使分区过滤无效: 分区表达式接收NULL值并将其放到第一个分区导致查询时多查分区.解决方法:创建第一个无用分区存放NULL值数据
分区列和索引列不匹配,查询无法进行分区过滤
选择分区成本高,插入大量数据时都需要扫描分区定义找到分区
打开并锁住所有底层表的成本可能很高
维护分区的成本很高,同alter一样创建临时表然后拷贝数据
所有分区都必须使用相同的存储引擎
2.1.6 查询优化在where条件带入分区列
创建分区时可以使用表达式,但是查询时只能在使用分区列本身进行比较时才能过滤分区,而不能根据表达式的值过滤分区
2.2 视图视图本身是一个虚拟表,不存放任何数据,不能对视图创建触发器
2.2.1 算法合并算法: 将存放的视图sql和用户发起的查询sql合并后执行
临时表算法: 由存放的视图sql先创建临时表后根据用户的查询sql查询返回
2.2.2 可更新视图可以通过更新视图更新相关表, 所有临时表算法实现的视图都无法更新
2.2.3 视图对性能的影响一般情况视图不能提升性能,在某些情况下可以帮助提升性能,需要做比较详细的测试
视图还可以实现基于列的权限控制不用真正创建列权限
2.2.4 视图的限制不保存视图定义的原始sql语句
查看视图创建的语句,可以通过使用视图的.frm文件的最后一行获取一些信息
2.3 外键约束InnoDB强制外键使用索引
查询需要额外访问一些表,需要额外的锁容易导致一些死锁
如果使用外键做约束,通常在应用程序里实现会更好
2.4 内部存储代码 2.4.1 优点离数据最近,节省带宽和网络延迟
帮助提升安全性,应用程序可以通过存储过程访问那些没有权限的表
服务器端可以缓存存储过程的执行计划
维护方便,便于分工
2.4.2 缺点调试困难,难以定位问题
存储代码效率相对差
增加维护复杂性,存储过程会给数据库服务器增加额外压力
存在安全隐患,没有什么选项可以控制存储程序的资源消耗,所以一个小错误可能直接把服务器拖死
2.4.3 存储过程和函数优化器无法评估存储函数的执行成本
每个连接都有独立的存储过程的执行计划缓存,多个连接调用同一个存储过程会浪费缓存空间反复缓存同样的执行计划
2.4.4 触发器每个表的每个事件只能一个
MySQL只支持基于行的触发,如果变更的数据集非常庞大的化效率会很低
触发器的问题很难排查
可能导致死锁和锁等待
实现一些约束,系统维护任务及更新反范式化数据的时候会比较有用
2.4.5 事件类似Linux的定时任务
2.5 游标MySQL在服务器端提供只读的,单向的游标
一个存储过程中可以有多个游标,也可以嵌套
2.6 绑定变量创建一个绑定变量sql时客户端向服务器发送了一个sql语句原型
服务器端解析并存储这个sql语句的部分执行计划返回客户端一个sql语句处理句柄
可以使用问号作为sql的占位,在使用sql接口执行时赋予变量值
2.7 插件存储过程插件
后台插件: 如Percona Server中包含的Handler-Socket
INFORMATION_SCHEMA插件
全文解析插件: 可以对文档进行分词处理
审计插件: 可以用作记录事件日志
认证插件: 扩展认证功能
2.8 字符集和校对字符集是指一种从二进制编码到某类字符符号的映射
校对是指一组用于某个字符集的排序规则
2.8.1 创建对象时的默认设置服务器,数据库,表都有默认的字符集和校对规则,这是一个逐层继承的默认设置
创建数据库时根据character_set_server设置来设定默认字符集
2.8.2 服务器和客户端通信设置服务端总是假设客户端按照character_set_client设置的字符来传输数据和sql语句
服务器端收到sql语句后根据character_set_connection转换成字符串
服务器端返回数据时会将其转换为character_set_result
2.8.3 选择字符集和校对规则极简原则: 先为服务器选择合理的字符集在根据实际情况让某些列选择合适的字符集
2.8.4 对查询的影响不同字符集和校对规则之间的转换会带来额外的开销
排序查询要求的字符集与服务器数据的字符集相同时才能利用索引进行排序
当两个字符集不同列关联两个表时,MySQL会尝试转换其中一个列的字符集
2.9 全文索引自然语言的全文索引: 相关度是基于匹配的关键词个数及关键词在文档中出现的次数,整个索引中出现次数越少的词语匹配的相关度越高
布尔全文索引: 只有MyISAM才能使用
平时没接触过,有兴趣者请自行google
2.10 分布式XA事务事务协调器保证所有事务参与者完成工作,通知所有事务提交
内部XA事务: 存储引擎提交的同时,需要将提交的信息写入二进制日志
外部XA事务: XA事务是一种在多个服务器之间同步数据的方法,如果由于不能使用MySQL本身的复制或者性能并不是瓶颈可以尝试使用
2.11 查询缓存查询缓存系统会跟踪查询中涉及的每个表,如果表发生变化则缓存数据失效
缓存存放在一个引用表中,通过一个哈希值引用,哈希值包括查询本身,查询数据库等信息
当sql语句和客户端发送过来的其他原始信息,任何字符上的不同都会导致缓存不命中
打开查询缓存对读和写都会带来额外的消耗
InnoDB事务修改表时,会将这个表对应的查询缓存都设置失效
查询缓存被发现是一个影响服务器扩展性的因素
如果缓存了大量的查询结果,那么失效操作可能会造成系统僵死.因为靠一个全局锁保护,所有该操作都要等锁
减少碎片, 选择合适的query_cache_min_res_unit可以减少内存浪费
对于写密集型的应用,直接禁用更好
高并发环境也不适合.只有明确缓存的好处才使用
查询缓存的替代方案: 客户端缓存
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/47319.html
摘要:基准测试应用验证系统的一些假设重现系统异常测试系统当前的运行情况模拟更高的负载规划业务增长测试应用适应可变环境的能力测试不同硬件软件和操作系统的配置策略针对整个系统的整体测试集成式单独测试单组件式测试指标吞吐量在线事务处理单位为每秒事务数响 1. 基准测试 1.1 应用 验证系统的一些假设重现系统异常测试系统当前的运行情况模拟更高的负载规划业务增长测试应用适应可变环境的能力测试不同硬件...
摘要:服务器逻辑架构连接线程处理基于的工具类似实现连接处理授权认证安全等查询缓存解析器实现查询解析分析优化缓存内置函数和跨存储引擎如存储过程触发器视图等存储引擎数据的存储和提取不会解析独立与上层服务器通过进行通信并发控制每种存储引擎有不同的锁策略 1. MySQL服务器逻辑架构 连接/线程处理: 基于C/S的工具类似,实现连接处理,授权认证,安全等.查询缓存/解析器: 实现查询解析,分析,优...
摘要:查询优化查询缓慢的原因是否向数据库请求了不必要的数据查询时是否返回了全部或者大部分数据然后再进行处理的。进行单查或者多表联查时是否返回了全部列数据。将一次处理大量数据的操作,分解为多个小操作。尽量减少在数据库中进行排序操作 查询优化 查询缓慢的原因 是否向数据库请求了不必要的数据1.查询时是否返回了全部或者大部分数据然后再进行处理的。2.进行单查或者多表联查时是否返回了全部列数据。...
摘要:高效,简单,准确,只能满足其中两个。计数器表计数器表在应用程序中使用很频繁例如统计朋友圈的点赞数某个文件的下载量空间访问人数等。 数据类型优化 1.数据类型优化 尽量使用可以正确储存数据的最小数据类型,例如状态字段时只有只需要几个数字就使用tinyint而不是int类型 尽量使用简单的方式去存储数据,可以用整形表示的时候就不要用字符串类型 例如性别,IP等、 避免NULL值的使用...
摘要:当并发性增加时,需要测量吞吐量是否下降,响应时间是否变长可扩展性可扩展性不是压力测试的指标,可扩展性指标对于容量规范非常有用,它可以提供其他测试无法提供的信息,来帮助发现应用的瓶颈归根结底,应该测试那些对用户来说最重要的指标。 ...
阅读 2447·2021-11-24 10:23
阅读 939·2021-11-17 09:33
阅读 2269·2021-09-28 09:41
阅读 1155·2021-09-22 15:55
阅读 3451·2019-08-29 16:32
阅读 1715·2019-08-29 16:25
阅读 940·2019-08-29 11:06
阅读 3292·2019-08-29 10:55