1 SQL查询优化1. 获取有性能问题SQL的方法2.慢查询日志介绍3. 实时获取3.SQL的解析预处理及生成执行计划4 对特定SQL的查询优化

SQL语句优化

  • 对查询进行优化,要尽量避免全表扫描。在 where 或 order by 的列上加索引。
  • 尽量避免在 where 子语句中有 where num is null,这样不用索引,要全表扫描,可用 0 代替 null
  • 避免在 where 中用<>or!=,因为要全表扫描
  • 尽量避免在 where 中用 or,因为若一个字段有索引,一个没有,则要全表扫描
  • like”%abc%”,全表扫描
  • 避免在 where 子语句中对字段进行函数操作,因为要全表扫描
  • 使用复合索引时,必须用到该索引的第一个字段,否则索引不被使用。
  • 尽量避免在 where 子句使用 != 或 <> 操作符 引擎将放弃使用索引而进行全表扫描
  • 应尽量避免在 where 子句中对字段进行 null 值判断 否则将导致引擎放弃使用索引而进行全表扫描 如:select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0
  • 很多时候用 exists 代替 in 是一个好的选择
  • 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

1. 获取有性能问题SQL的方法

  • 通过用户反馈(自然是不建议滴)
  • 通过慢查询日志(时间可能较长)
  • 实时获取

2.慢查询日志介绍

2.1 性能

开销较低,主要来自于磁盘I/O和存储日志所需磁盘空间,对于现代磁盘,主要问题就只在于所需的大量存储空间了

2.2 位置控制

  • slow_query_log 启动/停止记录慢查询日志(默认为off,手动配置文件on才能开启)

在运行的MySQL中,可通过set global启动

也可通过脚本定时控制

  • slow_query_log_file 指定慢查询日志的存储路径及文件(默认在数据目录) 当然最好将日志/数据存储分开啦
  • long_query_time 指定记录慢查询日志SQL执行时间的阈值(默认单位s,可精确至ms)

默认值10s,通常改为0.001s即1ms较合适

  • log_queries_not_using_indexes 是否记录未使用索引的SQL

2.3常用日志分析工具

2.3.1 mysqldumpslow(MySQL官方自带)

实例查询

2.3.2 pt-query-digest(推荐使用)

生成日志服务器及日志文件名

生成到slow.rep

执行vi slow.rep

提供信息远多于mysqldumpslow生成的

3. 实时获取

通过此表

实例

3.SQL的解析预处理及生成执行计划

3.1 查询速度为什么会慢

MySQL服务器处理查询请求的整个过程

3.2 查询缓存对SQL性能的影响

Hash查找只能进行全值匹配

命中缓存,在返回结果前,MySQL会检查用户权限,查询无需被解析,看出缓存直接返回结果其实很不容易 如果缓存中结果正确的,每次缓存牵涉到表被更新,都要对缓存也进行刷新,如此即使是同一个sql语句即使对同一个表查询中不同不涉及的字段被更新,下次查询这个sql同样无法命中 此外每次在对缓存进行检查SQL是否命中时,都要对缓存加锁

不建议使用查询缓存

  • query_cache_type 设置查询缓存是否可用(ON,OFF,DEMAND)
  • query_cache_size 设置查询缓存的内存大小
  • query_cache_limit 设置查询缓存可用存储的最大值 如果预先知道哦结果不会被缓存加上SQL_NO_CACHE可以提高效率
  • query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据(默认关闭)
  • query_cache_min_res_unit 设置查询缓存分配的内存块最小单位 当

3.3 执行计划

3.3.1 可能造成MySQL生成错误的执行计划的原因

3.3.2 MySQL优化器可优化的SQL类型

  • 重定义表的关联顺序
  • 将外连接转化为内连接 如当有where条件和库表结构等会重写优化
  • 对一些过滤规则进行等价变换
  • 优化count(),min(),max()等聚合函数 优化器会使用B+索引和列是否为null来优化 所以直接选最左或者最右的记录即得min,max 由此会在查询计划中看到如下信息
  • 将一个表达式转化为常数表达式
  • 等价变换规则
  • 子查询优化 可能转为关联查询,减少表的查询次数
  • 提前终止查询 发现已经满足查询条件时立即终止,特例如limit子句 发现不成立条件,立即返回null

film table

由于id定义为无符号类型,所以直接终止了查询,并无读取任何数据

  • 对in()条件进行优化 对in列表的元素先进行排序,再通过二分查找确定

3.4 确定查询处理各个阶段所消耗的时间

3.4.1使用profile

set profile = 1; 启动profiel,这是一个会话级别的配置

  • 执行查询
  • show profiles;查看每一个查询所消耗的总时间的信息
  • show profile for query N;查询每个阶段所消耗的时间N为queryId

当执行 `show profile for query N;`后 若想查看CPU信息执行以下 show profile cpu for query 1;

但是已经不被推荐使用

3.4.2使用performance_schema(MySQL5.6后默认开启)

启动监控项

上述执行结果

4 对特定SQL的查询优化

一个存储过程实例

4.1如何修改大表的结构

主从方式

减少主从延迟,操作有工具加减单

数据示例表

alt语句

执行过程

验证表确实被修改

4.2 如何优化not in和<>查询

下面为优化sql

4.3 使用汇总表优化查询

汇总表

显示每个商品评论数

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏搜云库

Mycat 读写分离 数据库分库分表 中间件 安装部署,及简单使用

MyCat是一个开源的分布式数据库系统,是一个实现了MySQL协议的服务器,前端用户可以把它看作是一个数据库代理,用MySQL客户端工具和命令行访问,而其后端可...

45710
来自专栏维C果糖

史上最简单的 MySQL 教程(三十四)「视图(下)」

视图数据操作:虽然我们说视图可以称之为select语句的别名,但实际上,它和别名并不一样,因为视图是可以进行数据写操作的,只不过有很多限制而已。

40212
来自专栏乐沙弥的世界

父游标、子游标及共享游标

        游标是数据库领域较为复杂的一个概念,因为游标包含了shared cursor和session cursor。两者有其不同的概念,也有不同的表现形...

713
来自专栏GreenLeaves

select for update和select for update wait和select for update nowait的区别

CREATE TABLE "TEST6" ( "ID" VARCHAR2(30), "NAME" VARCHAR2(30), "...

22010
来自专栏乐沙弥的世界

MySQL 通用查询日志(General Query Log)

    同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日...

4723
来自专栏Java架构师历程

MYSQL 浅谈MyISAM 存储引擎

       mysql中用的最多存储引擎就是innodb和myisam。做为Mysql的默认存储引擎,myisam值得我们学习一下,以下是我对《高性能MYSQ...

5952
来自专栏开发与安全

Mysql数据库学习(一):数据库基本概念、关系型数据库、Mysql数据库安装配置、DDL/DCL/DML语句

一、数据库基本概念 数据库(DB) 按照数据结构来组织、存储数据的仓库 数据库管理系统(DBMS) 数据库管理系统(Database Manag...

2180
来自专栏「3306 Pai」社区

关于MySQL 8.0的几个重点,都在这里

在MySQL8.0中重新设计了redo log,主要改进fsync,使得效率更高,减少锁,优化flush机制,不会频繁flush。同时,支持更高用户并发请求。

1160
来自专栏Python爬虫实战

MySQL从零开始:03 基本入门语句

在上一小节中介绍了 MySQL 数据库的安装,接下来终于可以动手操作数据库了。本节内容介绍 MySQL 数据库的一些基本操作当做开胃菜。

791
来自专栏全华班

数据库事务理解

事物 本章讲述了三种常见的数据操作语句Insert /Update / Delete的基本语法,也提到了Oracle9i新的数据操作语句 – Merge的作用和...

3095

扫码关注云+社区

领取腾讯云代金券