`
tiankong6622
  • 浏览: 55213 次
社区版块
存档分类
最新评论

mysql数据库优化总结(心得)

阅读更多

本篇文章是对mysql数据库优化进行了详细的总结与介绍,需要的朋友参考一下

 

 1. 优化你的MySQL查询缓存

在MySQL服务器上进行查询,可以启用高速查询缓存。让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时,如果结果是从缓存中提取,那是相当快的。
但主要的问题是,它是那么容易被隐藏起来以至于我们大多数程序员会忽略它。在有些处理任务中,我们实际上是可以阻止查询缓存工作的。

复制代码代码如下:

 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
 $today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");


2. 用EXPLAIN使你的SELECT查询更加清晰
使用EXPLAIN关键字是另一个MySQL优化技巧,可以让你了解MySQL正在进行什么样的查询操作,这可以帮助你发现瓶颈的所在,并显示出查询或表结构在哪里出了问题。

EXPLAIN查询的结果,可以告诉你那些索引正在被引用,表是如何被扫描和排序的等等。
实现一个SELECT查询(最好是比较复杂的一个,带joins方式的),在里面添加上你的关键词解释,在这里我们可以使用phpMyAdmin,他会告诉你表中的结果。举例来说,假如当我在执行joins时,正忘记往一个索引中添加列,EXPLAIN能帮助我找到问题的所在。

添加索引到group_id field后

3. 利用LIMIT 1取得唯一行
有时,当你要查询一张表是,你知道自己只需要看一行。你可能会去的一条十分独特的记录,或者只是刚好检查了任何存在的记录数,他们都满足了你的WHERE子句。
在这种情况下,增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描,而不是去扫描整个表或索引。
 

复制代码代码如下:

 // do I have any users from Alabama?   
 // what NOT to do:   
 $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }     
 // much better:   
 $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 } 
 


4.索引中的检索字段
索引不仅是主键或唯一键。如果你想搜索表中的任何列,你应该一直指向索引。

5. 保证连接的索引是相同的类型
如果应用程序中包含多个连接查询,你需要确保你链接的列在两边的表上都被索引。这会影响MySQL如何优化内部联接操作。
此外,加入的列,必须是同一类型。例如,你加入一个DECIMAL列,而同时加入另一个表中的int列,MySQL将无法使用其中至少一个指标。即使字符编码必须同为字符串类型。

复制代码代码如下:

 // looking for companies in my state   
 $r = mysql_query("SELECT company_name FROM users  
     LEFT JOIN companies ON (users.state = companies.state)  
     WHERE users.id = $user_id");   

 // both state columns should be indexed   
 // and they both should be the same type and character encoding   
 // or MySQL might do full table scans


6. 不要使用BY RAND()命令
这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND()命令时就开始创建了。
如果您真的需要随机显示你的结果,有很多更好的途径去实现。诚然这需要写更多的代码,但是能避免性能瓶颈的出现。问题在于,MySQL可能会为表中每一个独立的行执行BY RAND()命令(这会消耗处理器的处理能力),然后给你仅仅返回一行。

复制代码代码如下:

 // what NOT to do:   
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");   
 // much better:   
 $r = mysql_query("SELECT count(*) FROM user");   
 $d = mysql_fetch_row($r);   
 $rand = mt_rand(0,$d[0] - 1);   
 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1"); 


7. 尽量避免SELECT *命令
从表中读取越多的数据,查询会变得更慢。他增加了磁盘需要操作的时间,还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟,仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列,这是一个非常良好的习惯。

复制代码代码如下:

 // not preferred   
 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";   
 // better:   
 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";    
 // the differences are more significant with bigger result sets 


8. 从PROCEDURE ANALYSE()中获得建议
PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了,能为你的重大决策服务。



9. 准备好的语句
准备好的语句,可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下,能给应用程序以有效的保护,防止SQL注入攻击。当然你也可以手动过滤,不过由于大多数程序员健忘的性格,很难达到效果。

复制代码代码如下:

 // create a prepared statement   
 if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {    
     // bind parameters   
     $stmt->bind_param("s", $state);    
     // execute   
     $stmt->execute();    
     // bind result variables   
     $stmt->bind_result($username);     
     // fetch value   
     $stmt->fetch();    
     printf("%s is from %s\n", $username, $state);     
     $stmt->close();   
 } 


10. 将IP地址存储为无符号整型
许多程序员在创建一个VARCHAR(15)时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时,你只占用4个字节的空间,这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

 

11.永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段,你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。
而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……
在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。

12.使用ENUM而不是VARCHAR
ENUM类型是非常快和紧凑的。在实际上,其保存的是TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。
如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用ENUM而不是VARCHAR。
MySQL也有一个“建议”(见第十条)告诉你怎么去重新组织你的表结构。当你有一个VARCHAR字段时,这个建议会告诉你把其改成ENUM类型。使用PROCEDURE ANALYSE() 你可以得到相关的建议。

13.从PROCEDURE ANALYSE()取得建议p程序员站
PROCEDURE ANALYSE() 会让MySQL帮你去分析你的字段和其实际的数据,并会给你一些有用的建议。只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。
例如,如果你创建了一个INT字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段的类型改成MEDIUMINT。或是你使用了一个VARCHAR字段,因为数据不多,你可能会得到一个让你把它改成ENUM的建议。这些建议,都是可能因为数据不够多,所以决策做得就不够准。
在phpmyadmin里,你可以在查看表时,点击“Propose table structure”来查看这些建议

MySQL性能优化的21条最佳经验

 

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

14.尽可能的使用NOT NULL php程序员站
除非你有一个很特别的原因去使用NULL值,你应该总是让你的字段保持NOT NULL。这看起来好像有点争议,请往下看。
首先,问问你自己“Empty”和“NULL”有多大的区别(如果是INT,那就是0和NULL)?如果你觉得它们之间没有什么区别,那么你就不要使用NULL。(你知道吗?在Oracle里,NULL 和 Empty的字符串是一样的!)
不要以为 NULL 不需要空间,其需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。
下面摘自MySQL自己的文档:

15. 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.

MySQL性能优化的21条最佳经验

16.无缓冲的查询
正常的情况下,当你在当你在你的脚本中执行一个SQL语句的时候,你的程序会停在那里直到没这个SQL语句返回,然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。 
关于这个事情,在PHP的文档中有一个非常不错的说明:mysql_unbuffered_query()函数:

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

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

17.把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()。

18.固定长度的表会更快
如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。

并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。 php程序员站
使用“垂直分割”技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的。

19.垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖)
示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢?这样会让你的表有更好

分享到:
评论

相关推荐

    mysql数据库优化的学习心得

    MySQL 数据库优化技巧总结 MySQL 数据库优化是数据库管理和开发者非常关心的一个问题。由于 MySQL 数据库的性能优化可以直接影响到整个应用系统的性能和用户体验,因此掌握 MySQL 数据库优化技巧对开发者和数据库...

    AirNet自动化系统中MySQL数据库维护心得.pdf

    总的来说,AirNet自动化系统中的MySQL数据库维护涵盖了安装、备份、升级、性能优化和安全等多个方面。通过系统性的维护工作,可以确保数据库的稳定运行,从而保障空管系统的正常运行,保证飞行安全和效率。

    MySQL开发实战:电子商务订单系统数据库开发的实验心得与案例解析

    - 安装MySQL数据库; - 使用数据库管理工具(如phpMyAdmin或MySQL Workbench)进行管理和操作。 - **数据库设计**: - 设计订单(Order)、用户(User)和产品(Product)等相关表; - 创建合理的外键关联以确保数据...

    Mysql数据库主从心得整理1

    MySQL 数据库主从复制是一种常见的高可用性和数据冗余策略,它允许从一个主数据库(Master)异步或半同步地复制数据到一个或多个从数据库(Slave)。这个过程涉及到多个线程和日志机制,确保数据的一致性。 1. **...

    数据库优化语句以及心得

    数据库优化是提升应用性能的关键环节,尤其是在大数据量和高并发的场景下。本文主要讨论了SQL优化中的执行计划和索引策略,适用于Java开发者和其他数据库使用者。以下是对这些知识点的详细解析: 首先,我们来看...

    MySQL性能优化心得1

    MySQL数据库性能优化是一个涵盖多个方面的复杂过程,包括存储引擎的选择、数据库设计、查询优化、服务器配置以及参数调整等。以下是对这些关键领域的详细说明: **一、存储引擎的优化** 1. **MyISAM**: MyISAM是...

    精选_基于PHP和MYSQL数据库实现的libilibi电影论坛网站_源码打包

    总的来说,“libilibi”电影论坛的实现是一个集PHP编程、MySQL数据库设计、安全策略、用户体验优化等多个方面于一体的项目。开发者需要深入理解这些技术,才能构建出一个稳定、高效、用户体验良好的在线社区。通过...

    基于JavaEE JSP语言 MVC模式 MySQL数据库的动漫论坛的设计与实现毕业设计论文说明书 2万+字 共55页.pdf

    【基于JavaEE JSP语言 MVC模式 MySQL数据库的动漫论坛的设计与实现】 本毕业设计主要探讨的是如何使用JavaEE中的JSP技术,结合MVC(Model-View-Controller)设计模式和MySQL数据库,来构建一个动漫论坛系统。这个...

    MYSQL使用心得(三)----性能与管理优化

    在MySQL的使用过程中,性能与管理优化是提升数据库效率的关键环节。本文将深入探讨如何通过配置文件、查询优化、索引策略以及维护实践来优化MySQL的性能和管理。 首先,我们关注的是`my.cnf`配置文件。这是MySQL...

    mysql的使用心得

    通过以上总结,我们可以看到MySQL提供了丰富的功能和工具来帮助开发者和数据库管理员更好地管理和优化数据库。无论是基本的数据操作、性能优化还是高级的功能特性,合理运用这些技巧都能够显著提升数据库的性能和...

    使用三大框架ssh+mysql基础web案例

    **MySQL数据库**:MySQL是一种流行的开源关系型数据库管理系统,以其高性能、高可靠性和易于使用而受到广大开发者的喜爱。在SSH框架中,通常会使用Hibernate作为数据访问层,与MySQL进行交互,实现CRUD(创建、读取...

    MySQL学习总结(Linux系统环境)

    在MySQL学习总结(Linux系统环境)中,我们探讨的是如何在Linux操作系统环境下安装、配置、管理和使用MySQL数据库。MySQL是一款广泛应用于Web应用的开源关系型数据库管理系统,它以其高效、稳定和易于管理的特点深受...

    mysql安装心得

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,广泛应用于各种规模的企业和项目中。本文将详细介绍Linux平台上MySQL 5.6数据库的安装过程,包括多种安装方式、依赖包的检查以及一些关键配置的设置。 1. 日期与...

    mysql 学习总结

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,其高效性和易用性深受开发者的喜爱。本篇文章将深入探讨MySQL的安装过程、索引的创建及其背后...希望这份MySQL学习总结能帮助你进一步理解和掌握数据库的核心知识。

    数据库实验 数据库系统环境搭建和基本操作.docx

    实验要求搭建基本的数据库开发平台,选择安装SQL SERVER, ACCESS, ORACLE, MYSQL等开发环境,並熟悉数据库软件操作环境与语法使用规则。 知识点1:数据库系统环境搭建 * 数据库系统环境搭建是指在实验中搭建基本的...

    MYSQL初装优化精要

    通过对MySQL初装后的综合优化,可以显著提升数据库性能,并确保长期稳定运行。硬件选择、参数配置、SQL查询优化以及系统级优化工具的合理应用,都是实现这一目标的关键步骤。希望本文提供的指导能够帮助广大用户更好...

    Mysql优化、MyCat搭建、分库分表、读写分离、负载均衡

    MySQL优化是数据库管理中的关键环节,它涉及到性能提升、资源有效利用和系统...以上就是关于"Mysql优化、MyCat搭建、分库分表、读写分离、负载均衡"的相关知识点,这些内容对于管理和优化大型MySQL数据库系统至关重要。

Global site tag (gtag.js) - Google Analytics