设为首页 加入收藏

TOP

Mysql关于查询性能优化的方法教程
2018-03-14 09:00:27 】 浏览:178
Tags:Mysql 关于 查询 性能 优化 方法 教程

1.优化数据访问

查询常见问题:向数据库请求了不需要的数据

查询不需要的记录,没有加上limit导致返回大量结果集。 select*返回全部列,尤其是多表关联时。 重复查询相同数据,没有采用缓存。

2. 衡量查询开销

1. 衡量查询开销的三个指标

响应时间:服务时间(数据库处理该查询的时间)+排队时间(等待I/O以及等待锁的时间)。 扫描的行数以及返回的行数:它们两个的比例越接近1:1就越好,能够说明查询效率问题。 扫描的返回类型:Explain语句的type类型反映了返回类型,从好到坏分别为全表扫描,索引扫描,范围扫描,唯一索引查询,常数引用等,扫描速度从慢到块。

2. Explain的使用

如下图,因为定义了了索引idx_fk_film_id,所以MYSQL在该索引上使用了ref访问类型,扫描行数为10行。换句话说,这是一次高效的查询。

这里写图片描述

如果删除索引,访问类型会变成一个全表扫描(ALL),预估需要扫描5037条记录,Using Where表示使用where筛选存储引擎返回的记录。

这里写图片描述

对于WHERE条件,MYSQL的扫描条件从好到坏依次为:

速度最快的,利用索引,在索引中利用WHERE条件过滤掉不匹配的记录,这是存储引擎层完成的(Explain的Extra为空)。 稍慢的,使用索引覆盖扫描(Explain的Extra出现Using index),直接从索引中过滤掉不需要的记录并返回命中的结果。这是MYSQL服务器层完成的,无须再回表查询记录。 最慢的,从数据表中返回全部数据,然后过滤掉不必要的记录(Explain的Extra出现Using Where)。这也是MYSQL服务器层完成的。

2. 优化技巧

使用索引覆盖扫描,把所有查询需要用的列都放在索引中。 改变库表结构,例如使用单独的汇总表 重写复杂的查询,让MYSQL优化器能以更优化的方式查询。

3. 重构查询

1. 切分查询

将大的查询拆分成小的查询,每个查询功能完全一样,每次只返回一部分的查询结果。 例如,删除大量数据的时候,如果一次性删除的话,可能会一次锁住很多数据、占满事务日志、消耗资源。将一个大的DELETE语句拆成小的可能尽可能减少对性能的影响,如下图:

这里写图片描述

切分查询,一次删除一万行数据一般是比较高效的。

这里写图片描述

2. 分解关联查询

将连接查询分解成单表查询,如下图:

这里写图片描述
这里写图片描述

这样做的优势如下:

更好的利用MYSQL的查询缓存。 将查询分解后,执行单个查询可以减少锁竞争。 单表查询需要在应用层关联各个表的数据,因此更容易对数据库进行分库分表。 减少冗余数据的查询。

4. 查询执行的基本原理

1.查询流程

这里写图片描述

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

】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇SQLserver海量数据库的查询优化及.. 下一篇mariaDB创建存储过程demo分享

最新文章

热门文章

Hot 文章

Python

C 语言

C++基础

大数据基础

linux编程基础

C/C++面试题目