mysql笔记:11. 性能优化

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


性能优化是通过合理安排资源,调整系统参数使MySQL运行更快、更节省资源。主要包括查询速度优化、更新速度优化、MySQL服务器优化等。

概览

MySQL数据库优化是多方面的,原则上是减少系统的瓶颈和资源的占用、增加系统的反应速度。
MySQL常用的性能参数值如下表:

参数名 功能
connections 连接服务器的次数
uptime 服务器的上线时间
slow_queries 慢查询的次数
com_select 查询操作的次数
com_insert 插入操作的次数
com_update 更新操作的次数
com_delete 删除操作的次数

查询命令语法:

mysql> show status like 'NAME';

查询速度优化

在数据库中,一般对数据的查询操作是最频繁的,提高数据的查询速度可以有效提升数据库的性能。

1. 分析查询语句

通过分析查询语句,可以了解查询语句的执行情况,找出查询语句的不足之处,从而优化查询语句。
分析查询语句有两种方式:EXPLAIN和DESCRIBE。

1.1 EXPLAIN

基本语法:

EXPLAIN [EXTENDED] SELECT select_options

参数select_type:SELECT类型,可以为以下任何一种:

含义
SIMPLE 简单SELECT(不使用UNION或子查询)
PRIMARY 最外面的SELECT
UNION UNION中的第二个或后面的SELECT语句
DEPENDENT UNION UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT UNION的结果。
SUBQUERY 子查询中的第一个SELECT
DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
DERIVED 导出表的SELECT(FROM子句的子查询)

1.2 DESCRIBE

基本语法:

DESCRIBE SELECT select_options

2. 使用索引优化查询

索引可以快速定位表中的某条记录,使用索引可以提高数据库的查询速度,从而提高数据库的性能。如果不使用索引,查询语句将扫描表中全部记录,速度会很慢;使用索引,查询语句可以根据索引快速定位到待查询记录,从而减少查询的记录数,达到提高查询速度的目的。
一般情况下,索引可以提高查询速度,但并不是所有使用带有索引的字段查询都会起作用。例如下面几种特殊情况:

  • 使用LIKE关键字的查询语句
  • 使用多列索引的查询语句
  • 使用OR关键字的查询语句

3. 优化子查询

子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个的条件。它可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
其优点是查询语句很灵活,但是其执行效率不高。
执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。因此,子查询的速度会受到一定的影响。如果查询的数据量比较大,这种影响也随之增大。
在MySQL中,可以使用连接(JOIN)查询来替代子查询。它不需要建立临时表,速度比子查询要快。如果查询中使用索引的话,性能会更好。

数据库结构优化

合理的数据库结构不仅可以使数据库占用更小的磁盘空间,而且能够使查询速度更快。设计时,需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面内容。

1. 分解表

对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新的表。因为当一个表的数据量很大时,会由于使用频率低的字段而变慢。

2. 建立中间表

对于需要经常联合查询的表,可以建立中间表提高查询效率。

3. 增加冗余字段

设计数据库表时应该尽量遵循数据库范式理论的规约,尽可能减少冗余字段,让数据库看起来更加精致。但是,合理地加入冗余字段可以提高查询速度。
表的规范化程度越高,表与表之间的关系就越多,需要连接查询的情况也就越多。
不过冗余字段会导致一些新问题。如,一个表中的冗余字段值修改了,就要同步更新其他表中的该字段值,否则就会使原本一致的数据变得不一致。

4. 优化插入速度

插入记录时,影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。

4.1. MyISAM引擎表

  • 禁用索引
    对于非空表,插入记录时,MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据,建立索引会降低插入记录的速度。可以在插入记录前禁用索引,数据插入完毕后再开启索引。
# 禁用索引
ALTER TABLE table_name DISABLE KEYS;

# 启用索引
ALTER TABLE table_name ENABLE KEYS;

空表批量导入数据时不需要进行此操作,因为MyISAM引擎的表是在导入数据之后才建立索引的。

  • 禁用唯一性检查
    插入数据时,MySQL会对插入的记录进行唯一性校验。这种校验也会降低插入速度。可以先禁用,插入数据后再开启。
# 禁用唯一性校验
SET UNIQUE_CHECKS=0;

# 启用唯一性校验
SET UNIQUE_CHECKS=1;
  • 使用批量插入

  • 使用LOAD DATA INFILE批量导入

4.2. InnoDB引擎表

  • 禁用唯一性检查
    和MyISAM引擎的使用一样。
  • 禁用外键检查
# 禁用外键检查
SET foreign_key_checks=0;

# 启用外键检查
SET foreign_key_checks=1;
  • 禁止自动提交
# 禁用自动提交
SET autocommit=0;

# 启用自动提交
SET autocommit=1;

5. 分析表、检查表和优化表

MySQL提供了分析表、检查表和优化表的语句。

5.1. 分析表

分析表主要是分析关键字的分布,语法:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2, ...]

5.2. 检查表

MySQL数据库中可以使用CHECK TABLE检查表,主要是检查InnoDB和MyISAM类型的表是否存在错误,语法:

CHECK TABLE table_name1 [, table_name2, ...] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

5.3. 优化表

优化表主要是消除删除或者更新造成的空间浪费。该语句对InnoDB和MyISAM类型的表都有效。主要是优化表中的VARCHAR、BLOB或TEXT类型的字段。语法:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2, ...]

MySQL服务器的优化

1. 服务器硬件优化

服务器的硬件性能直接决定着MySQL数据库的性能。优化方向:

  • 配置较大的内存
  • 配置调整磁盘系统
  • 合理分布磁盘I/O
  • 配置多处理器

2. MySQL参数优化

通过优化MySQL参数也可以在不提升硬件的前提下提高资源利用率,从而达到提高服务器性能的目的。配置参数都在my.cnf或者my.ini文件中。文章来源地址https://www.toymoban.com/news/detail-843667.html

到了这里,关于mysql笔记:11. 性能优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

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

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

相关文章

  • MySQL——性能优化与关系型数据库

    吞吐与延迟:有些结论是反直觉的,指导我们关注什么。 没有量化就没有改进:监控与度量指标,指导我们怎么去入手。 80/20原则:先优化性能瓶颈问题,指导我们如何去优化。 过早的优化是万恶之源:指导我们要选择优化的时机。 脱离场景谈性能都是耍流氓:指导我们对

    2024年02月01日
    浏览(52)
  • MySQL数据库IO性能优化方法论

    作者:禅与计算机程序设计艺术 随着互联网信息化的发展,网站日益繁荣,用户对网站访问速度要求越来越高。如何提升网站数据库IO性能从而实现快速响应?本文将从数据库的优化角度出发,结合实际应用场景,进行系统地剖析、归纳和总结,为读者提供一个系统性、完整

    2024年02月06日
    浏览(56)
  • MySQL数据库性能优化中常用的方法是什么?

    MySQL 是目前广泛使用的关系型 数据库 系统,随着数据量的不断增加和业务需求的提升,MySQL数据库性能优化已经成为开发人员和DBA必须面对的一个重要问题。 查询语句是MySQL数据库中最常用的操作之一,也是造成性能问题的主要原因之一。以下是一些常见的查询语句优化方法

    2024年01月19日
    浏览(54)
  • MySQL数据库的性能优化----(一步一个脚印)

    一、MySQL数据库的优化目标、基本原则: 1、优化目标: MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,无论是索引优化、还是表结构优化,参数优化,最后都可以归纳到这这两个分类中: (1)减少 I/O 次数: I/O是数据库最容易瓶颈的地方,大部分数据库操作中超过90%的时间

    2024年02月03日
    浏览(91)
  • MySQL语句通过腾讯云数据库智能管家的性能与语法优化

    最近公司项目迁移至腾讯云,用的腾讯云MySQL服务器,MySQL负载一直很高,借助云管家优化了一部分SQL语句,提升了部分性能和释放了部分,MySQL内存占用      

    2024年02月15日
    浏览(62)
  • 掌握MySQL分库分表(一)数据库性能优化思路、分库分表优缺点

    不能⼀上来就说分库分表! 根据实际情况分析,两个角度思考:不分库分表、分库分表 软优化 数据库参数调优 分析慢查询SQL语句,分析执行计划,进行sql改写和程序改写 优化数据库索引结构 优化数据表结构优化 引入NOSQL和程序架构调整 硬优化 提升系统硬件(更快的IO、更

    2023年04月19日
    浏览(61)
  • 轻松入门MySQL:深入学习数据库表管理,创建、修改、约束、建议与性能优化(3)

    在项目中,正确设置数据表的限定条件是数据存储过程中的关键一步。我们频繁需要创建和修改表以管理各种业务数据。让我们深入学习创建和修改数据表的方法,以产品信息表( product_info )为例。 创建数据表 首先,让我们了解产品信息表的结构: product_id product_name cate

    2024年02月20日
    浏览(62)
  • mysql笔记:11. 性能优化

    性能优化是通过合理安排资源,调整系统参数使MySQL运行更快、更节省资源。主要包括查询速度优化、更新速度优化、MySQL服务器优化等。 MySQL数据库优化是多方面的,原则上是减少系统的瓶颈和资源的占用、增加系统的反应速度。 MySQL常用的性能参数值如下表: 参数名 功能

    2024年03月27日
    浏览(42)
  • 数据库性能优化的基本方法

    一、基本方法 数据库表结构优化。优化表结构,避免过度冗余设计和数据重复。 索引优化。根据查询需求,建立合适的索引,提高查询速度。 查询语句优化。编写高效的查询语句,避免全表扫描和子查询,减少 JOIN 操作。 数据库缓存优化。使用缓存技术,将频繁访问的数据

    2024年02月15日
    浏览(66)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包