读高性能MySQL(第4版)笔记10_查询性能优化(上)

这篇具有很好参考价值的文章主要介绍了读高性能MySQL(第4版)笔记10_查询性能优化(上)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

读高性能MySQL(第4版)笔记10_查询性能优化(上)文章来源地址https://www.toymoban.com/news/detail-709895.html

1. 三管齐下

1.1. 不做、少做、快速地做

1.2. 如果查询太大,服务端会拒绝接收更多的数据并抛出相应错误

1.3. 如果查询写得很糟糕,即使库表结构再合理、索引再合适,也无法实现高性能

1.4. 查询优化、索引优化、库表结构优化需要齐头并进,一个不落

1.5. Percona Toolkit中的pt-archiver工具

2. 响应时间

2.1. 如果把查询看作一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定的时间

2.2. 如果要优化查询,实际上要优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行得更快

2.3. 优化查询的目的就是减少和消除这些操作所花费的时间

2.4. 查询需要在不同的地方花费时间

2.4.1. 网络、CPU计算、生成统计信息和执行计划、锁等待(互斥等待)等操作

2.4.2. 尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间

2.5. 两部分之和:服务时间和排队时间

2.5.1. 服务时间是指数据库处理这个查询真正花了多长时间

2.5.2. 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成,也可能是等待行锁

2.6. 响应时间既可能是一个问题的结果也可能是一个问题的原因,不同案例情况不同

2.7. 实际上可以使用“快速上限估计”法来估算查询的响应时间

2.7.1. 了解这个查询需要哪些索引以及它的执行计划是什么

2.7.2. 计算大概需要多少个顺序和随机I/O

2.7.3. 用其乘以在具体硬件条件下一次I/O的消耗时间

2.7.4. 把这些消耗都加起来

2.7.5. 获得一个大概参考值来判断当前响应时间是不是一个合理的值

3. 查询的生命周期

3.1. 从客户端到服务器,然后在服务器上进行语法解析,生成执行计划,执行,并给客户端返回结果

3.2. “执行”可以被认为是整个生命周期中最重要的阶段

4. 优化数据访问

4.1. 如果性能很差,最常见的原因是访问的数据太多

4.2. 是否在检索大量且不必要的数据

4.2.1. 访问了太多的行

4.2.2. 访问了太多的列

4.3. 确认MySQL服务器层是否在分析大量不需要的数据行

4.3.1. 会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃

4.3.2. 消耗应用服务器的CPU和内存资源

4.4. 一个常见的错误是,常常会误以为MySQL只会返回需要的数据,实际上MySQL却是先返回全部结果集再进行计算

4.4.1. 最简单有效的解决方法就是在这样的查询后面加上LIMIT子句

4.5. 每次看到SELECT*的时候都需要用怀疑的眼光审视,是不是真的需要返回全部的列,很可能不是必需的

4.5.1. 取出全部列,会让优化器无法完成索引覆盖扫描这类优化

4.5.2. 严格禁止SELECT*的写法,这样做有时候还能避免某些列被修改而带来的问题

4.6. 查询返回超过需要的数据也不总是坏事

4.7. 重复查询相同的数据

4.7.1. 当初次查询的时候将这个数据缓存起来,需要的时候从缓存中取出,这样性能显然会更好

4.8. 检查慢日志记录是找出扫描行数过多的查询的好办法

4.9. 扫描的行数和返回的行数

4.9.1. 查看该查询扫描的行数能够说明该查询找到需要的数据的效率高不高

4.9.2. 理想情况下扫描的行数和返回的行数应该是相同的,但实际中这种“美事”并不多

4.9.3. 扫描的行数与返回的行数的比率通常很低,一般在1:1到10:1之间,不过有时候这个值也可能非常非常大

4.10. 访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等

4.10.1. 访问方式可能无须扫描就能返回结果

4.10.2. 访问方式可能需要扫描很多行才能返回一行结果

4.10.3. 没办法找到合适的访问类型,那么最好的解决办法通常就是增加一个合适的索引

4.11. 应用WHERE条件

4.11.1. 在索引中使用WHERE条件来过滤不匹配的记录

4.11.1.1. 在存储引擎层完成的

4.11.2. 使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录

4.11.2.1. 直接从索引中过滤不需要的记录并返回命中的结

4.11.2.2. 在MySQL服务器层完成的,但无须再回表查询记录

4.11.3. 从数据表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using where)

4.11.3.1. 在MySQL服务器层完成

4.11.3.2. 需要先从数据表中读出记录然后过滤

4.12. 不是说增加索引就能让扫描的行数等于返回的行数

4.13. 优化

4.13.1. 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无须回表获取对应行就可以返回结果了

4.13.2. 改变库表结构

4.13.2.1. 使用单独的汇总表

4.13.3. 重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行

5. 重构查询的方式

5.1. 在优化有问题的查询时,目标应该是找到获得实际需要的结果的替代方法

5.1.1. 但这并不一定意味着从MySQL返回完全相同的结果集

5.1.2. 可以将查询转换为返回相同结果的等价形式,以获得更好的性能

5.2. 以前人们总是认为网络通信、查询解析和优化是一件代价很高的事情

5.2.1. 对于MySQL并不适用

5.2.2. MySQL从设计上让连接和断开连接都很轻量,在返回一个小的查询结果方面很高效

5.2.3. 现代的网络速度比以前要快很多,能在很大程度上降低延迟

5.3. 在MySQL内部,每秒能够扫描内存中上百万行的数据

5.3.1. MySQL响应数据给客户端就慢得多了

5.4. 在其他条件都相同的时候,使用尽可能少的查询当然是更好的

5.4.1. 将一个大查询分解为多个小查询是很有必要的

5.4.2. 如果在一个查询能够胜任时还将其写成多个独立的查询是不明智的

5.5. 切分查询

5.5.1. 删除旧的数据就是一个很好的例子

5.5.1.1. 定期清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询

5.5.1.2. 将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL的性能,同时还可以降低MySQL复制的延迟

5.5.2. 一次删除一万行数据一般来说是一个比较高效而且对服务器影响最小的做法(如果是事务型引擎,很多时候小事务能够更高效)

5.5.3. 如果每次删除数据后,都暂停一会儿再做下一次删除,也可以将服务器上原本一次性的压力分散到一个很长的时间段中,可以大大降低对服务器的影响,还可以大大减少删除时锁的持有时间

5.6. 分解联接查询

5.6.1. 很多高性能的应用都会对联接查询进行分解

5.6.2. 可以对每一个表进行一次单表查询,然后将结果在应用程序中进行联接

5.6.3. 让缓存的效率更高

5.6.4. 将查询分解后,执行单个查询可以减少锁的竞争

5.6.5. 在应用层做联接,可以更容易对数据库进行拆分,更容易做到高性能和可扩展

5.6.6. 查询本身的效率也可能会有所提升

5.6.6.1. 使用IN()代替联接查询,可以让MySQL按照ID顺序进行查询,这可能比随机的联接要更高效

5.6.7. 可以减少对冗余记录的访问

5.6.7.1. 在应用层做联接查询,意味着对于某条记录应用只需要查询一次,而在数据库中做联接查询,则可能需要重复地访问一部分数据

到了这里,关于读高性能MySQL(第4版)笔记10_查询性能优化(上)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 读高性能MySQL(第4版)笔记07_优化数据类型(下)

    2.3.1.1. 与UNIX时间戳相同 2.3.2.1. 会遇到2038年的问题 2.3.2.1.1. 使用带符号的32位INT,可以表达直到2038年的时间 2.3.2.1.2. 使用无符号的32位INT,可以表达直到2106年的时间 2.3.2.1.3. 使用64位,还可以超出这些范围 2.3.3.1. MySQL服务器、操作系统和客户端连接都有时区设置 2.3.3.2. 存

    2024年02月09日
    浏览(40)
  • 读高性能MySQL(第4版)笔记06_优化数据类型(上)

    3.2.2.1. 字符集和排序规则(collation)使字符型数据的比较更复杂 3.2.2.2. 应该将日期和时间存储为MySQL的内置类型而不是字符串类型 3.2.2.3. 应该用整型数据存储IP地址 4.1.1.1. 使用8、16、24、32和64位存储空间 5.2.4.1. VARCHAR(1000)的列则需要1002个字节,因为需要2字节存储长度

    2024年02月09日
    浏览(50)
  • 读高性能MySQL(第4版)笔记05_优化服务器设置

    2.1.3.1. MySQL只需要少量的内存就能保持一个连接(通常是一个相关的专用线程)打开 2.2.1.1. InnoDB缓冲池大小 2.2.1.2. 需要的内存比其他任何组件都多 2.2.1.3. 不仅缓存索引,还缓存行数据、自适应哈希索引、更改缓冲区、锁和其他内部结构等 2.2.1.4. InnoDB严重依赖缓冲池,应

    2024年02月09日
    浏览(55)
  • 读高性能MySQL(第4版)笔记04_操作系统和硬件优化

    4.9.2.1. 允许在内存中更改页面,而不用将更改刷新到磁盘,这通常涉及随机I/O,速度非常慢 4.9.2.2. 将更改的记录写入顺序日志文件,这样要快得多 4.9.2.3. 后台线程可以稍后将修改过的页面刷新到磁盘,这样做可以优化写操作的性能 5.11.2.1. 只有进行特殊的擦除操作之后,

    2024年02月09日
    浏览(39)
  • 高性能MySQL实战(三):性能优化

    大家好,我是 方圆 。这篇主要介绍对慢 SQL 优化的一些手段,而在讲解具体的优化措施之前,我想先对 EXPLAIN 进行介绍,它是我们在分析查询时必要的操作,理解了它输出结果的内容更有利于我们优化 SQL。为了方便大家的阅读,在下文中规定类似 key1 的表示二级索引,key_

    2024年02月11日
    浏览(73)
  • MySQL高性能优化规范建议

    数据库命令规范 数据库基本设计规范 1. 所有表必须使用 Innodb 存储引擎 2. 数据库和表的字符集统一使用 UTF8 3. 所有表和字段都需要添加注释 4. 尽量控制单表数据量的大小,建议控制在 500 万以内。 5. 谨慎使用 MySQL 分区表 6.尽量做到冷热数据分离,减小表的宽度 7. 禁止在表中建

    2024年02月12日
    浏览(52)
  • 《高性能MySQL》——创建高性能的索引(笔记)

    索引(在MySQL中也叫做“键(key)”) 是存储引擎用于快速找到记录的一种数据结构。 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。 在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但当数据量逐渐增大时

    2024年02月07日
    浏览(115)
  • 【QT性能优化】QT性能优化之QT6框架高性能模型视图代理框架千万级数据表分页查询优化

    QT性能优化之QT6框架高性能模型视图代理框架千万级数据表分页查询优化 简介 本文介绍了QT模型视图代理框架中的QT表格控件和QT数据库模块中的QT数据库查询模型结合使用的一个应用实践案例:QT高性能表格控件分页展示千万行数据。本文介绍了这个应用实践案例的运行效果

    2024年02月14日
    浏览(53)
  • 数据库——MySQL高性能优化规范

    所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用 MySQL 保留(如果表名中包含查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过 32 个字符 临时库表必须以 tmp_为前缀并以日期为后缀,

    2024年02月11日
    浏览(104)
  • 读高性能MySQL(第4版)笔记08_创建高性能索引(上)

    2.4.2.1. 按照索引列中的数据大小顺序存储的 2.4.3.1. 键前缀查找只适用于根据最左前缀的查找 2.4.4.1. 在查询某些条件的数据时,存储引擎不再需要进行全表扫描 2.4.4.2. 通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中

    2024年02月08日
    浏览(50)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包