`
aslijiasheng
  • 浏览: 58372 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

MySQL性能优化建议

 
阅读更多

 

 

 

  1. 为查询缓存优化你的查询


  大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。

  这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面的示例:



  上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。


  2. EXPLAIN 你的 SELECT 查询

  使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

  EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。

  挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后,你会看到一张表格。下面的这个示例中,我们忘记加上了group_id索引,并且有表联接:




  当我们为 group_id 字段加上索引后:



  我们可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。


  

  3. 为搜索字段建索引   匹配索引 要类型 相同  比如 INDEX    ID  INT   不能 where ID ="5"   INT 和CHAR 类型不一样


  索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。





  从上图你可以看到那个搜索字串 “last_name LIKE ‘a%’”,一个是建了索引,一个是没有索引,性能差了4倍左右。

  另外,你应该也需要知道什么样的搜索是不能使用正常的索引的。例如,当你需要在一篇大的文章中搜索一个词时,如: “WHERE post_content LIKE ‘%apple%’”,索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引(比如说:搜索关键词或是Tag什么的)


  4. 在Join表的时候使用相当类型的例,并将其索引

  如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的SQL语句的机制。

  而且,这些被用来Join的字段,应该是相同的类型的。例如:如果你要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样)




  

  5. 永远为每张表设置一个ID


  我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。

  就算是你 users 表有一个主键叫 “email”的字段,你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。

  而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……

  在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。


  6. 使用 ENUM 而不是 VARCHAR    char是一种固定长度的类型,varchar则是一种可变长度的类型  固定长度的字段做索引好

  ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。

  如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。

 
  7. 从 PROCEDURE ANALYSE() 取得建议


  PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据,并会给你一些有用的建议。只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。

  例如,如果你创建了一个 INT 字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段,因为数据不多,你可能会得到一个让你把它改成 ENUM 的建议。这些建议,都是可能因为数据不够多,所以决策做得就不够准。

  在phpmyadmin里,你可以在查看表时,点击 “Propose table structure” 来查看这些建议



  一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。


    8. Prepared Statements

  Prepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用 prepared statements 获得很多好处,无论是性能问题还是安全问题。

  Prepared Statements 可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击。当然,你也可以手动地检查你的这些变量,然而,手动的检查容易出问题,而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候,这样的问题会好一些。

  在性能方面,当一个相同的查询被使用多次的时候,这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数,而MySQL只会解析一次。

  虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势,所以这会使得网络传输非常有效率。

  当然,也有一些情况下,我们需要避免使用Prepared Statements,因为其不支持查询缓存。但据说版本5.1后支持了。

  在PHP中要使用prepared statements,你可以查看其使用手册:mysqli 扩展 或是使用数据库抽象层,如: PDO.





  9. 无缓冲的查询


  正常的情况下,当你在当你在你的脚本中执行一个SQL语句的时候,你的程序会停在那里直到没这个SQL语句返回,然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。

  mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存,尤其是那些会产生大量结果的查询语句,并且,你不需要等到所有的结果都返回,只需要第一行数据返回的时候,你就可以开始马上开始工作于查询结果了。

  然而,这会有一些限制。因为你要么把所有行都读走,或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且, mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以,是否使用无缓冲的查询你需要仔细考虑。


  10. 把IP地址存成 UNSIGNED INT

  很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2。

  我们必需要使用UNSIGNED INT,因为 IP地址会使用整个32位的无符号整形。

  而你的查询,你可以使用 INET_ATON() 来把一个字符串IP转成一个整形,并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中,也有这样的函数 ip2long() 和 long2ip()。




  


  11. 垂直分割

  “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖)

  示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。

  示例二: 你有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。

  另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。


  12. 拆分大的 DELETE 或 INSERT 语句

  如果你需要在一个在线网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

  Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

  如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你泊WEB服务Crash,还可能会让你的整台服务器马上掛了。

  所以,如果你有一个大的处理,你定你一定把其拆分,使用 LIMIT 条件是一个好的方法。下面是一个示例:




  13. 越小的列会越快

  对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。

  参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。

  如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。

  当然,你也需要留够足够的扩展空间,不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(2009年11月06日),一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据。


  14. 选择正确的存储引擎

  在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。
  

      MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

  InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。

  下面是MySQL的手册


  target=”_blank”MyISAM Storage Engine

  InnoDB Storage Engine


 
 

分享到:
评论

相关推荐

    百度mysql性能优化ppt

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

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

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

    MySQL性能优化诊断脚本

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

    Mysql性能优化教程.doc

    ### MySQL性能优化教程知识点概述 #### 一、MySQL执行优化 **1.1 背景及目标** - **背景**: 针对已具备一定MySQL使用经验的工程师,特别是适用于高并发、海量数据处理的互联网环境。 - **目标**: 通过对MySQL性能...

    大牛-高级教程MySQL性能优化实战

    MySQL性能优化是数据库管理员和开发人员在处理大数据量和高并发场景时的必备技能。本教程将深入探讨如何提升MySQL数据库的性能,确保系统能够高效、稳定地运行。以下是关于MySQL性能优化的一些关键知识点: 1. **...

    MySQL性能优化的21个最佳实践.pdf

    本文档着重分享了MySQL数据库的性能优化实践,旨在帮助读者提升数据库操作的性能。下面详细介绍了各项优化技巧。 首先,针对查询缓存进行优化是提高数据库性能的有效方法之一。MySQL服务器默认开启了查询缓存,它...

    【荐】mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    通过对my.cnf/my.ini配置文件的各项关键参数进行中文注释与解析,帮助DBA及运维人员更好地理解每个配置项的作用及其对MySQL性能的影响。 #### 关键配置项详解 ##### 1. **[client]** - **port=3306**:设定MySQL...

    高级MySQL性能优化

    根据提供的文件信息,我们可以梳理出《高级MySQL性能优化》这一主题涵盖的详细知识点。 首先,从文档的标题《高级MySQL性能优化》来看,这篇文章应专注于为有一定基础的MySQL用户提供进阶性能调优的技巧和策略。这...

    MySQL数据库性能优化研究.docx

    1. MySQLTuner:一款流行的 MySQL 性能诊断工具,可以检查 MySQL 服务器的配置和性能,并提供优化建议。 2. Percona Toolkit:一款强大的 MySQL 管理工具,提供了多种性能测试和优化工具。 3. Sysbench:一款开源的...

    MySQL性能优化权威指南

    MySQL性能优化是一门深奥的技术,旨在通过调整和调整数据库的配置参数,来提高数据库的性能和效率。《MySQL性能优化权威指南》这本书深入地介绍了如何在实际应用中实现MySQL性能的优化。本书详细讲述了数据库连接池...

    Mysql性能优化方案l建议1

    MySQL性能优化是一个复杂而关键的任务,它涉及到多个层面的调整和设计策略,旨在提升数据库的运行效率,降低响应时间,以及提高系统的整体可用性。以下是一些基于标题和描述中的关键点的详细说明: 一、数据库架构...

    MySQL性能优化概述

    ### MySQL性能优化概述 在数据库管理系统(DBMS)领域中,MySQL因其卓越的性能、可靠性和易用性而被广泛采用。随着应用系统的扩展和数据量的增长,对MySQL进行性能优化变得至关重要。本文将深入探讨MySQL性能优化的...

    【36】最新精选蚂蚁-MySQL语句性能优化视频教程下载 .txt

    根据提供的文件信息,我们可以推断出本教程主要围绕MySQL语句性能优化展开,这是一项非常重要的数据库管理技能,尤其对于那些处理大量数据的应用...希望这篇教程能够帮助大家更好地理解和掌握MySQL性能优化的相关知识。

    MySQL的SQL查询性能优化技术.pdf

    MySQL的SQL查询性能优化技术是数据库管理中至关重要的一环,对于提升系统效率和用户体验有着显著影响。本篇主要探讨了优化SQL查询的一些基本原则、企业级监控、配置与参数化、数据管理和性能考量等方面。 首先,...

    mysql介绍和性能优化.rar

    本教程将深入探讨MySQL的基本概念、安装配置、操作语法以及性能优化策略。 一、MySQL基本概念 1. 数据库:存储数据的容器,可以包含多个表。 2. 表:数据的二维结构,由列和行组成。 3. 列:定义表中的字段,每个...

Global site tag (gtag.js) - Google Analytics