位置: 编程技术 - 正文

sql语句优化的一般步骤详解(sql语句优化的13种方法)

编辑:rootadmin

推荐整理分享sql语句优化的一般步骤详解(sql语句优化的13种方法),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:sql语句的优化方式,sql语句的优化方式,sql语句优化的一般步骤是,sql优化的一般步骤,sql优化有哪些,sql语句优化的一般步骤是,sql语句优化的几种方法,sql语句优化的一般步骤是,内容如对您有帮助,希望把文章链接给更多的朋友!

前言

本文主要给大家分享了关于sql语句优化的一般步骤,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。

一、通过 show status 命令了解各种 sql 的执行频率

mysql 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息,也可以在操作系统上使用 mysqladmin extend-status 命令获取这些消息。

show status 命令中间可以加入选项 session(默认) 或 global:

session (当前连接) global (自数据上次启动至今)

我们通常比较关心的是以下几个统计参数:

Com_select : 执行 select 操作的次数,一次查询只累加 1。 Com_insert : 执行 insert 操作的次数,对于批量插入的 insert 操作,只累加一次。 Com_update : 执行 update 操作的次数。 Com_delete : 执行 delete 操作的次数。

上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对 innodb 的,累加的算法也略有不同:

Innodb_rows_read : select 查询返回的行数。 Innodb_rows_inserted : 执行 insert 操作插入的行数。 Innodb_rows_updated : 执行 update 操作更新的行数。 Innodb_rows_deleted : 执行 delete 操作删除的行数。

通过以上几个参数,可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主,以及各种类型的 sql 大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累加。

对于事务型的应用,通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。

此外,以下几个参数便于用户了解数据库的基本情况:

Connections : 试图连接 mysql 服务器的次数。 Uptime : 服务器工作时间。 Slow_queries : 慢查询次数。

二、定义执行效率较低的 sql 语句

1. 通过慢查询日志定位那些执行效率较低的 sql 语句,用 --log-slow-queries[=file_name] 选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 sql 语句的日志文件。

2. 慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 mysql 在进行的线程,包括线程的状态、是否锁表等,可以实时的查看 sql 的执行情况,同时对一些锁表操作进行优化。

三、通过 explain 分析低效 sql 的执行计划

测试数据库地址: email 为租赁电影拷贝所支付的总金额,需要关联客户表 customer 和 付款表 payment , 并且对付款金额 amount 字段做求和(sum) 操作,相应的执行计划如下:

select_type: 表示 select 类型,常见的取值有: simple:简单表,及不使用表连接或者子查询 primary:主查询,即外层的查询 union:union 中的第二个或后面的查询语句 subquery: 子查询中的第一个 select table : 输出结果集的表 type : 表示 mysql 在表中找到所需行的方式,或者叫访问类型,常见类型性能由差到最好依次是:all、index、range、ref、eq_ref、const,system、null:

1.type=ALL,全表扫描,mysql 遍历全表来找到匹配的行:

2.type=index, 索引全扫描,mysql 遍历整个索引来查询匹配的行

sql语句优化的一般步骤详解(sql语句优化的13种方法)

3.type=range,索引范围扫描,常见于<、<=、>、>=、between等操作:

4.type=ref, 使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行,例如:

索引 idx_fk_customer_id 是非唯一索引,查询条件为等值查询条件 customer_id = , 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中:

5.type=eq_ref,类似 ref,区别就在使用的索引时唯一索引,对于每个索引的键值,表中只要一条记录匹配;简单的说,就是多表连接中使用 primary key 或者 unique index 作为关联条件。

6.type=const/system,单表中最多有一个匹配行,查起来非常迅速,所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理,例如,根据主键 primary key 或者唯一索引 unique index 进行查询。

7.type=null, mysql 不用访问表或者索引,直接就能够得到结果:

  类型 type 还有其他值,如 ref_or_null (与 ref 类似,区别在于条件中包含对 null 的查询)、index_merge(索引合并优化)、unique_subquery (in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似,区别在于 in 的后面是查询非唯一索引字段的子查询)等。

possible_keys : 表示查询时可能使用的索引。 key :表示实际使用索引 key-len : 使用到索引字段的长度。 rows : 扫描行的数量 extra:执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

show warnings 命令

执行explain 后再执行 show warnings,可以看到sql 真正被执行之前优化器做了哪些 sql 改写:

从 warning 的 message 字段中能够看到优化器自动去除了 1=1 恒成立的条件,也就是说优化器在改写 sql 时会自动去掉恒成立的条件。

explain 命令也有对分区的支持.

可以看到 sql 访问的分区是 p2。

四、通过 performance_schema 分析 sql 性能

旧版本的 mysql 可以使用 profiles 分析 sql 性能,我用的是5.7.的版本,已经不允许使用 profiles 了,推荐用performance_schema 分析sql。

五、通过 trace 分析优化器如何选择执行计划。

mysql5.6 提供了对 sql 的跟踪 trace,可以进一步了解为什么优化器选择 A 执行计划而不是 B 执行计划,帮助我们更好的理解优化器的行为。

使用方式:首先打开 trace ,设置格式为 json,设置 trace 最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整显示。

接下来执行想做 trace 的 sql 语句,例如像了解租赁表 rental 中库存编号 inventory_id 为 的电影拷贝在出租日期 rental_date 为 -- 4:: ~ 5:: 之间出租的记录:

六、 确定问题并采取相应的优化措施

经过以上步骤,基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施,进行优化以提高执行的效率。

总结

标签: sql语句优化的13种方法

本文链接地址:https://www.jiuchutong.com/biancheng/348632.html 转载请保留说明!

上一篇:Mysql启动报ERROR:2002的分析与解决(启动mysql1067错误)

下一篇:mysql数据库无法被其他ip访问的解决方法(mysql数据库无法连接到服务器怎么办)

  • 契税计税依据及税率
  • 增值税附加税是几个点
  • 软件使用权收入怎么计算
  • 上期金额和本期金额是什么意思
  • 企业缴纳的社保可以税前扣除吗
  • 金税系统怎么录入专票
  • 直接支付和授权支付方式的区别与联系
  • 研发技术服务属于什么费用
  • 预收款项增值税
  • 应付票据的处理
  • 股权转让企业所得税纳税地点
  • 质量扣款可以放到费用吗
  • 劳务派遣公司是干嘛的
  • 房贷利息在哪里能看到
  • 积分抵扣所开的发票,也能在税前扣除吗?
  • 把公司的钱借给别人责任
  • 白酒与外购产品的区别
  • 实收资本不是股东打来的怎么调帐
  • 分支机构的季度所得税务报表是填合并报表吗
  • 出口退税附加税计税依据
  • 电信猫光信号灯不亮
  • deepin解锁文件
  • 买一个金蝶软件年费多少
  • 丢失空白发票怎么处罚
  • 员工没有工资卡可以转到一个人的账户上发工资不
  • 个人投资额是什么意思
  • 外购的福利费要转出吗
  • 公司认缴出资怎么交税
  • 让绿萝疯长的妙招
  • 多对账工作的主要内容
  • 有趣的css代码
  • 贵的u盘和便宜的有什么区别
  • php md5加盐
  • 外商投资企业需要备案吗
  • thinkphp addons
  • 计算机视觉需要学什么
  • PHP+Apache+Mysql环境搭建教程
  • 上月未记账,本月不能记账
  • 工伤保险赔偿范围和企业承担范围
  • 私房出租税收
  • 帝国cms模板文件放在哪里
  • python怎么打开mac本地文件
  • 小规模纳税人应交税费科目设置
  • 以货换货账务怎么处理
  • 预付账款购进原材料怎么做账
  • 企业欠缴非税收据怎么写
  • 业务招待费的所得税扣除
  • 新建厂房环评费会计科目
  • 房屋出租简易计税进项税额需要转出么
  • 计提的工会经费年底有余额咋处理
  • 银行承兑到期之前怎么办
  • 工会年末结余会计分录
  • 公司账户可以转个人支付宝吗
  • 质量问题扣款怎么开票
  • ppp项目政府可以不出资
  • 账户信息变更说明
  • 投标保证金的计算
  • 管理费用结转有余额吗
  • sql server 还原数据库后显示为备用只读
  • mysql5.7.32免安装教程
  • sqlserver模糊查询不用like
  • ubuntu18 ssh
  • Linux Container(LXC容器)的基本命令使用简介
  • windowsupdate.diagcab
  • 电脑硬件用途
  • 五种修改方法
  • fdreader.exe是什么程序
  • linux 文件夹权限 777
  • 如何升级win10专业版
  • windows资源管理器在哪个文件夹
  • opengl纹理错误变成条纹
  • 进入cmd命令
  • 批处理禁用计算机
  • python多进程编程
  • python 压缩gz
  • javascript在客户端执行
  • Android的事件处理机制是一种______机制
  • 四川国家税务局官网
  • 火车票抵扣申报表怎么填
  • 何为金税三期
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设