`

MySQL性能优化(一)

阅读更多

性能优化是通过某些有效的方法来提高MySQL的运行速度,减少占用的磁盘空间。性能优化包含很多方面,例如优化查询速度,优化更新速度和优化MySQL服务器等。本文介绍方法的主要有:

  • 优化查询
  • 优化数据库结构
  • 优化MySQL服务器

 

  数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能。语法:SHOW STATUE LIKE ‘value’;其中value参数是常用的几个统计参数。

Connections:连接MySQL服务器的次数

Uptime:MySQL服务器的上线时间;

Slow_queries:慢查询的次数;

Com_select:查询操做的次数;

Com_insert:插入操作的次数;

Com_delete:删除操作的次数;

Com_update:更新操作的次数;

 

1优化查询

       查询操作是最频繁的操作,提高了查询速度可以有效提高MySQL数据库的性能。

       首先要对查询语句进行分析,分析查询语句的命令是EXPLAIN语句和DESCRIBE语句。比如 EXPLAIN SELECT * FROM student  \G;

       索引可以快速定位表中的某条记录。使用索引也可以提高数据库查询的速度,从而提高数据库的性能。如果不使用索引,查询语句将 表中的所有字段。这样查询的速度会很慢。如果使用了索引,查询语句只会查询索引字段。这样就减少查询的记录数,达到提高查询效率的目的。

       现在看一个查询语句中没有索引的使用情况:

SELECT * FROM student  WHERE name = ‘张三’;这样会对student表中的所有数据都查询一下,对比一下name的字段是否是张三。

       然后我们在name字段上建立一个名为index_name的索引:

CREATE INDEX index_name ON student(name);

现在name字段上面已经有索引了,再进行该select语句查询的速度就非常快了,不需要遍历整个表。

       但是有些时候即使查询时使用的是索引,但索引并没有起作用。比如使用了LIKE关键字进行查询时,如果匹配字符串的第一个字符为‘%’,索引不会被使用。如果‘%’不是在第一个位置,索引就会被使用。

       另一种情况是在表的多个字段上创建一个索引,比如

CREATE INDEX index ON student(birth,department);这样只有查询语句条件中使用字段name时,索引才会被用到。因为name字段是多列索引的第一个字段,只有查询条件中使用了name字段才会使索引index起作用。

 

2优化子查询

       很多查询中需要使用子查询。子查询可以使查询语句很灵活,但子查询的执行效率不高。MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后,MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表,其速度比子查询要快。

 

3优化数据库结构

       1将字段很多的表分解成多个表

       有些表在设计时设置了很多的字段。但是这个表中的有些字段的使用频率很低。当这个表的数据量很大时,查询数据的速度就会很慢。对于这种字段特别多的并且有字段的使用频率不高的表,就可以将其分解成多个表。

       2增加中间表

       有时需要经常查询某两个表中的几个字段。如果经常进行联表查询,就会降低MySQL数据库的查询速度。对于这种情况可以建立中间表来提高查询速度。

       先分析经常需要同时查询那几个表中的那些字段。然后将这些字段建立一个中间表,并将原来那几个表的数据插入到中间表中,之后就可以使用中间表来进行查询和统计了。

       3增加冗余字段

       设计数据库表时尽量让表达到三范式。但是,有时为了提高查询速度,可以有意识的在表中增加冗余字段。表的规范程度越高,表与表之间的关系就越多,查询时可能经常需要在多个表之间进行连接查询,而进行连接查询会降低查询速度。比如,学生的信息存储在student表中,院系的信息存储在department表中,通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称,必须从student表中拿到dept_id字段,然后根据这个编号去department表中查找系的名称。如果需要经常需要进行这个操作的话,连接查询会浪费很多时间。因此可以在student表中增加一个冗余字段dept_name,这样就不用每次都进行连接查询了。其实就是一切以业务为主。

       4优化插入记录的速度

       插入记录时,索引和唯一性校验都会影响到插入记录的速度。而且,一次插入多条记录和多次插入一条记录所消耗的时间是不一样的。根据这些情况,分别进行不同的优化。

       禁用索引:插入记录时,MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时,这些排序会降低插入的速度。为了解决这种情况,在插入记录之前先禁用索引。等插入之后再启用索引。对于新创建的表,可以先不创建索引,等记录都导入以后再创建索引。这样可以提高导入数据的速度。

       ALTER TABLE 表名 DISABLE KEYS;

       ALTER TABLE 表名 ENABLE KEYS;

 

       优化INSERT语句:当大量插入数据时,建议使用一个INSERT语句插入多条记录,而不是使用多次INSERT语句。这样可以减少与数据库之间的连接等操作。

       5分析表,检查表,和优化表

分析表的主要作用是分析关键字的分布。检查表的作用是检查表是否存在错误。优化表主要作用是消除删除或者更新造成的空间浪费。

       分析表   ANALYZE TABLE 表名;  使用ANALYZE TABLE分析表的过程中,数据库系统会对表加一个只读锁。在分析表的过程中,只能读取表的内容,不能插入和更新表的内容。ANALYZE TABLE 语句能够分析InnoDB和MyISAM类型的表。

       检查表使用CHECK TABLE语句。在执行过程中也会给表加上只读锁。

       优化表使用OPTIMIZE TABLE语句。只能优化表中的VARCHAR,BLOB,TEXT类型的字段。OPTIMIZE TABLE语句可以消除删除和更新造成的磁盘碎片,从而减少空间浪费。因为如果一个表使用了TEXT或者BLOB这样的数据类型,那么更新,删除等操作就会造成磁盘空间的浪费。因为,更新和删除操作以后,以前分配的磁盘空间不会自动回收。使用OPTIMIZE TABLE语句可以将这些磁盘碎片整理出来,以便再利用。

 

4 优化MySQL服务器

       硬件上的优化:增加内存和提高磁盘读写速度,都可以提高MySQL数据库的查询,更新的速度。另一种提高MySQL性能的方式是使用多块磁盘来存储数据。因为可以从多块磁盘上并行读取数据,这样可以提高读取数据的速度。

       MySQL参数的优化:内存中会为MySQL保留部分的缓冲区。这些缓冲区可以提高MySQL的速度。缓冲区的大小都是在MySQL的配置文件中进行设置的。

       下面对几个重要的参数进行详细介绍:

  • key_buffer_size:表示索引缓存的大小。这个值越大,使用索引进行查询的速度就越快
  • table_cache:表示同时打开的表的个数。这个值越大,能同时打开的表的个数就越多。这个值不是越大越好,因为同时打开的表过多会影响操作系统的性能。
  • query_cache_size:表示查询缓冲区的大小。使用查询缓存区可以提高查询的速度。这个方式只使用与修改操作少且经常执行相同的查询操作的情况;默认值是0.
  • Query_cache_type:表示查询缓存区的开启状态。0表示关闭,1表示开启。
  • Max_connections:表示数据库的最大连接数。这个连接数不是越大越好,因为连接会浪费内存的资源。
  • Sort_buffer_size:排序缓存区的大小,这个值越大,排序就越快。
  • Innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存。这个值越大,查询的速度就会越快。这个值太大了就会影响操作系统的性能。

 

合理配置这些参数可以提高MySQL数据库的性能。配置完参数后,需要重启MySQL服务才会生效。

分享到:
评论

相关推荐

    千金良方:MySQL性能优化金字塔法则.docx

    这一法则的提出,为MySQL性能优化提供了一个系统化的方法论,为开发人员和数据库管理员提供了实用的指导。 数据库基础知识是MySQL性能优化的基础,包括数据模型、数据存储、数据完整性和数据安全性等方面。数据模型...

    MySQL性能优化和高可用架构实践.pptx

    本书《MySQL性能优化和高可用架构实践》是一本详细介绍MySQL性能优化和高可用架构实践的书籍,旨在帮助读者提升MySQL数据库的性能和可靠性。本书的内容涵盖了查询优化的基本原则和最佳实践,例如索引的设计和使用,...

    MySQL性能优化中文手册

    总的来说,MySQL性能优化是一个综合性的过程,涵盖了SQL语句设计、存储过程优化、函数使用、索引管理以及服务器配置等多个层面。通过深入学习和实践,你可以提升MySQL系统的响应速度,满足高并发、大数据量的业务...

    mysql 性能优化与架构设计(word版)

    一、MySQL性能优化 1. 查询优化:查询优化是性能提升的关键。通过编写高效的SQL语句,避免全表扫描,使用索引,减少JOIN操作,以及正确使用聚合函数,可以显著提高查询速度。 2. 索引策略:理解B-TREE、HASH和...

    Mysql性能优化教程

    总之,MySQL性能优化是一个综合性的过程,需要结合理论知识和实践经验,通过对查询优化、索引设计、系统配置等方面的调整,以达到提升系统性能的目标。在高并发环境下,合理的优化策略可以显著提高系统的响应速度和...

    mysql性能优化教程.pdf (by caoz)

    ### MySQL性能优化知识点详解 #### 一、背景及目标 - **目的**:厦门游家公司(4399.com)为了提升员工技能水平,制定了这份MySQL性能优化教程,旨在为已有一定MySQL使用经验的工程师提供实战指导。 - **适用场景*...

    百度mysql性能优化ppt

    【标题】:“百度mysql性能优化ppt”所涉及的知识点涵盖了MySQL数据库在性能调优方面的多个重要环节。在MySQL性能优化中,我们关注的核心是提升数据处理速度,减少资源消耗,从而提高系统的整体效率。 【描述】:...

    MySQL 性能优化神器 Explain 使用分析

    MySQL 性能优化 Explain ,MySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 Explain

    mysql性能优化.pptx

    MySQL性能优化是一个涵盖广泛的主题,涉及多个层面,包括SQL语句优化、索引优化、数据库表结构优化、系统级配置优化以及服务器硬件优化。以下是对这些方面进行详细说明: 1. **SQL语句优化** - **慢查询日志**:...

    十三MySQL性能优化详解.pdf

    MySQL 性能优化详解 ...MySQL 的性能优化是一个复杂的任务,需要从多方面考虑,包括数据库设计、查询优化、服务器端优化和存储引擎优化。只有通过合理的优化,才能提高 MySQL 的性能,满足实际应用的需求。

    mysql性能优化总结

    MySQL性能优化是一个涵盖广泛的主题,涉及数据库设计、SQL查询优化、索引策略等多个方面。以下是对标题和描述中提到的一些关键知识点的详细说明: 1. **表的优化**: - **定长与变长字段的分离**:将定长字段(如...

    MySQL性能优化 SQL优化方法技巧

    ### MySQL性能优化与SQL优化方法技巧 #### 一、引言 MySQL作为一款广泛使用的开源关系型数据库管理系统,其性能优化对于确保应用系统的稳定性和高效运行至关重要。通过对MySQL的优化,不仅能提升系统的响应速度,还...

    MySQL性能优化诊断脚本

    MySQL性能优化诊断脚本是一种非常实用的工具,用于检测和分析MySQL数据库的性能问题。`tuning-primer.sh`就是这样一个脚本,它通过运行一系列的查询和检查,为数据库管理员提供有关系统性能的深入见解,并提出可能的...

    高并发基础之-MySql性能优化.pdf

    总结来说,MySQL性能优化是一个系统工程,涵盖查询优化、数据库结构优化和服务器配置优化等多个层面。通过掌握这些优化手段,我们可以有效减少系统瓶颈,提高数据库响应速度,从而提升整个应用的性能。在实际操作中...

    caoz的mysql性能优化教程

    MySQL性能优化是数据库管理员和...通过学习caoz的MySQL性能优化教程,你将掌握一系列实用技巧,使你的MySQL数据库达到最佳运行状态,满足业务需求,降低运维成本。无论是初学者还是经验丰富的DBA,都能从中获益匪浅。

    mysql性能优化与架构设计

    MySQL性能优化与架构设计是数据库管理员和开发人员必须掌握的关键技能之一。MySQL作为一个广泛使用的开源关系型数据库管理系统,其性能优化对于提升应用的整体性能至关重要。本资料主要关注MySQL的架构理解、性能...

Global site tag (gtag.js) - Google Analytics