sql优化

如何获取有性能问题的sql

  • 通过用户反馈存在性能问题的sql //用户反馈,测试人员测试
  • 通过慢查询日志获取存在性能问题的sql //主要手段
  • 实时捕获存在性能问题的sql

慢查询日志

# 查看my.cnf参数配置
mysqld --help --verbose | more

主要的开销是磁盘io和磁盘空间

启动慢查询日志

slow_query_log 启动停止慢查询日志
slow_query_log_file 指定慢查询日志存储日志及文件
long_query_time 指定慢查询日志sql执行时间的阀值
log_queries_not_using_indexes 是否记录未使用索引的sql

long_query_time=1
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow-query.log
log_queries_not_using_indexes=1

慢查询分析工具

  • mysqldumpslow mysql安装之后就自带的
    查看详细命令 mysqldumpslow --help

实时发现有性能问题的sql

利用information_schema

//可以通过脚本去执行,实时去查找执行时间超过30秒的
select id,`user`,DB,`host`,command,`time`,state,info from information_schema.processlist where time >30

查询为什么会慢

mysql查询执行的过程

  1. 客户端发送sql请求给服务器
  2. 服务器检查是否可以在查询缓存中命中该sql
  3. 服务器进行sql解析,预处理,再由优化器生成对应的执行计划
  4. 根据执行计划,调用引擎API来查询数据
  5. 将结果返回给客户端

影响的因素

查询缓存

如果查询缓存是打开的,优先检查查询缓存是否命中,使用hash查找来匹配缓存结果。如果命中查询缓存,在返回之前,会检查用户权限,如果权限符合,则返回结果。
检查缓存是否命中时,需要对缓存进行加锁,并且在数据被更新之后,缓存也就失效了。如果系统比较繁忙,则不建议开启缓存。

查询缓存的影响参数

query_cache_type 设置查询缓存是否可用 off/on
query_cache_size 设置查询缓存的内存大小 0
query_cache_limit 设置查询缓存可用存储的最大值
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

依照执行计划对存储引擎进行交互

在过程中出错则返回
解析sql,预处理,优化sql执行计划


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

使用profile

开启 set profiling = 1 这是一个session级的配置
执行查询
show profiles 查看每一个查询所消耗的总时间信息
查看 show profile for query n;

+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.01540625 | show databases    |
|        2 | 0.00012825 | SELECT DATABASE() |
|        3 | 0.00027775 | show databases    |
|        4 | 0.00042550 | show tables       |
|        5 | 0.00030150 | show tables       |
+----------+------------+-------------------+

//查看
show profile for query 1;
//结果
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.001794 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000693 |
| init                 | 0.000022 |
| System lock          | 0.000011 |
| optimizing           | 0.000005 |
| statistics           | 0.000027 |
| preparing            | 0.000017 |
| executing            | 0.012708 |
| Sending data         | 0.000046 |
| end                  | 0.000007 |
| query end            | 0.000005 |
| closing tables       | 0.000004 |
| removing tmp table   | 0.000010 |
| closing tables       | 0.000007 |
| freeing items        | 0.000019 |
| cleaning up          | 0.000018 |
+----------------------+----------+

Performance Schema

从mysql5.5 引入的
传送门


慢查询基础

简单的查询衡量指标:1响应时间,2扫描行数,3返回的行数

  • 响应时间主要是等待时间和服务时间,服务时间是服务器处理这个查询所消耗的时间。等待时间是等待资源的时间,如io、锁等待
  • 扫描行数在一定程度上能说明该查询的效率, 较短的行访问较快,内存中的行比磁盘上快。

优化数据访问

  1. 确认是否检索了大量超过需要的数据。通常是访问了太多行,有时候也可能是太多列
  2. 确认服务层是否分析了大量超过需要的数据行
  • 是否向数据库请求了不需要的数据
  • 是否扫描了额外的记录
    扫描的行数和返回的行数
    扫描的行数和访问类型

重构查询方式

  1. 将复杂查询分解成多个简单查询 //少连表 或单表查询
  2. 切分查询 //如删除多行数据,该为多次删除
  3. 分解关联查询
    子查询优化为join查询,需注意一对多情况时,是否有数据重复

mysql 查询执行路径

  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段
  3. 服务端进行SQL解析,预处理,再由优化器生成对应的执行计划。
  4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

查询优化

count

count是一种特殊函数,可以统计某个列值的数量,也可以统计行数。在统计列值时,要求列值是非空的。

count可以通过索引覆盖来实现优化,或者使用汇总表

select count(id2),count(id) from t
//结果 id2:2  id:3 count的列为null时,count并不会将其统计
利用null 优化count
select count(release_year='2008'  or null) from film

优化关联查询

  • 确保on或者using子句中的列上有索引。一般来说只需要在关联顺序中的第二个表的相应列上建立索引。 如:表A、B用c列进行关联,关联顺序为B、A,则只需要在A表上建立c列的索引。
  • 确保任何的group by 和order by 中的表达式只涉及表中的一个列,这样mysql才有可能使用索引来优化这个过程

优化子查询

子查询尽量换成关联查询。若使用5.6以上版本,则不需要进行替换。

优化group by 和distinct

mysql优化器会在内部处理时的时候相互转化这两类查询,都可以使用索引来优化,也是最有效的方法。
当无法使用索引优化时,group by 由临时表或文件排序来做分组

limit优化

可以参考mysql翻页优化
通常在where条件上加索引会由不错的性能,但是当数据量大,且翻页多时,如,limit 10010,10。此时mysql需要查询10010条数据,并且10000都被抛弃,只取最后10条。要对此类语句优化,要么限制页面中分页的数量,或者优化最大偏移量的性能。
优化最大偏移量,利用索引覆盖来加快查询。利用的是主键
limit和offset的问题,都是offset的问题。它会导致mysql扫描大量不需要的行然后在抛弃。

//第一种写法
select * from actor where actor_id >=(select actor_id from actor order by actor_id limit 100,1) limit 10;

//第二种写法
select * from actor a join (select actor_id from actor  order by actor_id limit 100,10) b on a.actor_id=b.actor_id 
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 201,468评论 5 473
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 84,620评论 2 377
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 148,427评论 0 334
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,160评论 1 272
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,197评论 5 363
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,334评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,775评论 3 393
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,444评论 0 256
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,628评论 1 295
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,459评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,508评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,210评论 3 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,767评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,850评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,076评论 1 258
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,627评论 2 348
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,196评论 2 341

推荐阅读更多精彩内容