php之MySQL数据库优化策略的详解-mysql教程-PHP中文网QQ群微信公众号php之MySQL数据库优化策略的详解本文简单讲述了PHP数据库编程之MySQL优化策略。分享给大家供大家参考，具体如下：前些天看到一篇文章说到PHP的瓶颈很多情况下不在PHP自身，而在于数据库。我们都知道，PHP开发中，数据的增删改查是核心。为了提升PHP的运行效率，程序员不光需要写出逻辑清晰，效率很高的代码，还要能对query语句进行优化。虽然我们对数据库的读取写入速度上却是无能为力，但在一些数据库类扩展像memcache、mongodb、redis这样的数据存储服务器的帮助下，PHP也能达到更快的存取速度，所以了解学习这些扩展也是非常必要，这一篇先说一下MySQL常见的优化策略。几条MySQL小技巧1、SQL语句中的关键词最好用大写来书写，第一易于区分关键词和操作对象，第二，SQL语句在执行时，MySQL会将其转换为大写，手动写大写能增加查询效率(虽然很小)。2、如果我们们经对数据库中的数据行进行增删，那么会出现数据ID过大的情况，用ALTER TABLE tablename AUTO_INCREMENT=N，使自增ID从N开始计数。3、对int类型添加 ZEROFILL 属性可以对数据进行自动补04、导入大量数据时最好先删除索引再插入数据，再加入索引，不然，mysql会花费大量时间在更新索引上。5、创建数据库书写sql语句时 ，我们可以在IDE里创建一个后缀为.sql的文件，IDE会识别sql语法，更易于书写。更重要的是，如果你的数据库丢失了，你还可以找到这个文件，在当前目录下使用/path/mysql -uusername -ppassword databasename & filename.sql来执行整个文件的sql语句（注意-u和-p后紧跟用户名密码，无空格）。数据库设计方面优化1、数据库设计符合第三范式，为了查询方便可以有一定的数据冗余。2、选择数据类型优先级 int & date,time & enum,char&varchar & blob，选择数据类型时，可以考虑替换，如ip地址可以用ip2long()函数转换为unsign int型来进行存储。3、对于char(n)类型，在数据完整的情况下尽量较小的的n值。4、在建表时用partition命令对单个表分区可以大大提升查询效率，MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用，分区方式为：CREATE TABLE tablename{
}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码
PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区
PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围5、选择数据库引擎时要注意innodb 和 myisam的区别。存储结构：MyISAM在磁盘上存储成三个文件。而InnoDB所有的表都保存在同一个数据文件中，一般为2GB事务支持：MyISAM不提供事务支持。InnoDB提供事务支持事务。表锁差异：MyISAM只支持表级锁。InnoDB支持事务和行级锁。全文索引：MyISAM支持 FULLTEXT类型的全文索引(不适用中文，所以要用sphinx全文索引引擎)。InnoDB不支持。表的具体行数：MyISAM保存有表的总行数，查询count(*)很快。InnoDB没有保存表的总行数,需要重新计算。外键：MyISAM不支持。InnoDB支持索引方面优化1、innodb是聚簇索引，存储索引时必须有主键，如果没有指定，引擎会自动生成一个隐藏的主键，生成一个主索引，索引内存放的是主键的物理地址，数据靠主键存放，每次使用索引时要先找到主索引，然后找到主索引下的数据。优点通过主键查找特别快，缺点是次级索引会变慢，因为需要先通过次级索引（次级索引里是主索引的位置。）找到主索引，然后通过主索引找数据。并且如果主键无规律，插入新值时需要移动较多数据块，会影响效率，所以要尽量使用有规律递增的int型做主键。还有因为数据紧跟着主键放，所以如果数据中有数据量特别大的列(text/blob)，innodb查询时会跳过很多数据块，也会导致慢。2、myisam的索引各个索引都相同统一指向磁盘上各个行的地址，都是轻量级的指针数据。缺点是各个索引的建立不是通过主键，查询没有聚簇索引查找主键快。但其因为存储的是地址，所以在插入新值时比较方面移动改变。3、进行多条件查询时，对多条件分别建立索引时，执行sql查询时，MySQL只会选择一个最贴近的索引来使用，所以如果需要多条件查询，要建立联合索引，即使会造成数据冗余。联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename
来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引 ALTER TABLE tablename ADD INDEX (column(n));
来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。数据查询方面优化数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很&慢&的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。慢查询日志：1、在my.ini或my.cnf的[mysqld]下添加slow_query_log_file=/path //设置日志存储路径long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:-g pattern 使用正则表达式-t n返回前n条数据-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序EXPLAIN语句使用方法，在要执行的查询语句前面加EXPLAINEXPLAIN SELECT * FROM得到形如下图的结果：下面是对每一项的解释：id 查询语句的id，简单查询无意义，多重查询时可以看出执行查询的顺序select-type 执行的查询语句的类型，对应多重查询，有simple/primary/union等。tabel 查询语句查询的数据表type
获得数据的类型 常见的类型效率从高到低为 null&const&eq_ref&ref&range&index&allpossible-keys:可能使用到的索引key 使用到的索引key_len索引长度ref 使用哪个列与索引一起从表中选择。rows
查找到数据要扫描的大概行数，可看出索引的优劣extra
常见的有using filesort 查询到数据后进行文件排序，较慢，需要优化索引using where 读取整行数据后进行判断过滤，是否符合where条件using index 索引覆盖，即在牵引中已经有这存储了目标数据，直接读取索引，很快。PROFILE用SELECT @@frofiling来查看PROFILE的开启状态。如果未开启，用SET profiling=1来开启。开启之后，再执行查询语句，MySQL会自动记录profile信息。应用show profiles查看所有的sql信息，结果为 Query_ID Duration Query三列结果，分别是查询ID，用时和所用的sql语句。我们可以使用SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等大型存储方面优化数据库主从复制和读写分离1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。2、配置主服务器master修改my.ini/my.conf[mysqld]
log-bin=mysql-bin //启用二进制日志
server-id=102 //服务器唯一ID3、配置从服务器slavelog-bin=mysql-bin //启用二进制日志
server-id=226 //服务器唯一ID4、在主服务器上授权从服务器GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'5、在从服务器上使用change master to master_host=&masterip&,master_user=&masteruser&,master_password=&masterpasswd&;6、然后使用start slave命令开始进行主从复制。不要忘记在每次修改配置后重启服务器，然后可以在主从服务器上用show master/slave status查看主/从状态。实现数据库的读写分离要依赖MySQL的中间件，如mysql_proxy，atlas等。通过配置这些中间件来对主从服务器进行读写分离，使从服务器承担被读取的责任，从而减轻主服务器的负担。数据库的sharding在数据库中数据表中的数据量非常庞大的时候，无论是索引还是缓存等压力都很大，对数据库进行sharding，使之分别以多个数据库服务器或多个表存储，以减轻查询压力。方式有垂直切分、水平切分和联合切分。垂直切分：在数据表非常多的时候，把数据库中关系紧密(如同一模块，经常连接查询)的表切分出来分别放到不同的主从server上。水平切分：在表不多，而表里的数据量非常大的时候，为了加快查询，可以用哈希等算法，将一个数据表分为几个，分别放到不同的服务器上，加快查询。水平切分和数据表分区的区别在于其存储介质上的不同。联合切分：更多的情况是数据表和表中的数据量都非常大，则要进行联合切分，即同时进行垂直和水平分表，将数据库切分为一个分布式的矩阵来存储。这些数据库的优化方式，每一种拿出来都可以写作一篇文章，可谓是博大精深，了解并记忆了这些方式，可以在有需要的时候进行有目的的选择优化，达到数据库效率的高效。接下来我们会进一步总结一下常用的PHP数据库类扩展memcache、redis和mongodb的基本使用场景和使用方式。以上就是php之MySQL数据库优化策略的详解的详细内容，更多请关注php中文网其它相关文章！共3篇923点赞收藏分享：.&猜你喜欢12345678910
PHP中文网：独家原创，永久免费的在线，php技术学习阵地！
All Rights Reserved | 皖B2-QQ群:关注微信公众号PHP实现Mysql数据库连接、查询、记录集操作代码实例
在网站开发中，经常需要对Mysql进行操作，大体上需要经过以下几个步骤：My链接、Mysql数据库查询、Mysql记录集操作等，如果每次都重复上述操作，不但繁琐，而且代码冗余度高，对此我整理了部分利用PHP实现Mysql数据库操作的代码，以函数的形式贴出，可根据自行需要添加其他功能或者整合成Mysql数据库类。
Mysql数据库链接代码
function dbConnect($hostname,$username,$pass,$db_name,$pconnect = 0)
$func = empty($pconnect) ? '_connect' : 'mysql_pconnect';
if(!$connect) {
$connect = @$func($hostname,$username,$pass) or die("Mysql_Error : ".mysql_error()."
Mysql Error Num : ".mysql_errno()."");
@mysql_select_db($db_name, $connect) or die(" Mysql_Error : ".mysql_error()."
Mysql Error Num : ".mysql_errno()."");
参数$hostname,$username,$pass,$db_name分别代表Mysql数据库服务器地址，用户名，密码，以及连接的数据库名，通常情况下hostname一般都是localhost或者127.0.0.1。参数$pconnect默认为0，表示通常情况下是以mysql_connect函数连接Mysql数据库。
mysql_connect与mysql_pconnect的区别：当执行完当前PHP程序后，PHP自动关闭mysql_connect建立的数据库连接，而mysql_pconnect返回一个持久稳固的数据库连接，在一定时间内有下一个连接请求时可以被复用，节省了反复连接Mysql数据库的时间，使得访问速度加快，其适用于并发访问量不大的情况，如并发访问量比较大，则可能会因为Mysql已达到最大连接数, 使之后的请求得不到满足。
mysql_error函数：返回上一个Mysql操作产生的文本错误信息。mysql_errno函数返回上一个Mysql操作中的错误号码，如果没有出错则返回0。
Mysql数据库查询代码
function query_error($query)
$temp_bar = "
=============================================================================
$result = mysql_query($query, $connect) or die("DB ERROR
".$temp_bar." Mysql_Query : ".$query."
Mysql_Error : ".mysql_error()."
Mysql Error Num : ".mysql_errno()."".$temp_bar);
注释：此函数为Mysql数据库查询函数，等于同mysql_query函数的功能，如果出错则输出出错信息（SQL语句），其实为了防止暴露网站数据库的结构，正式商用时，最好不要输出SQL执行语句。
Mysql记录集操作函数代码（mysql_fetch_array）
function fetch_array($result,$result_type = MYSQL_ASSOC,$records = "one")
if ($records == "one") {
return @mysql_fetch_array($result,$result_type);
for ($i=0;num_rows($result);$i++)
$info[$i] = @mysql_fetch_array($result,$result_type);
free_result($result);
注释：此函数的功能由mysql_fetch_array函数延生而来，在此基础上我增加了对Mysql数据库记录集的读取功能，并以数组形式返回获取的值。
mysql_fetch_array函数是mysql_fetch_row函数的扩展版本。第二个参数 result_type有三个值：MYSQL_ASSOC，MYSQL_NUM 和 MYSQL_BOTH。默认值是 MYSQL_BOTH。MYSQL_BOTH：得到一个同时包含关联和数字索引的数组。MYSQL_ASSOC：只得到关联索引（如同mysql_fetch_assoc()那样），MYSQL_NUM ：得到数字索引（如同 mysql_fetch_row()那样）。
报错信息函数代码
function error_msg($msg, $url= "")
if($connect) {
mysql_close($connect);
switch ($url)
$url = "history.go(-1)";
case "close":
$url = "window.close()";
$url = "document.location.href = '$url'";
if (!empty($msg)) {
echo "&script language='javascript'&alert('$str');$&/script&";
echo "&script language='javascript'&$&/script&";
注释：此函数的功能主要以alert的形式报错并进行页面跳转，是一个通用函数，报错或跳转之前其会先将Mysql数据库连接关闭，用到了mysql_close函数。
调用说明：
从上述Mysql数据库操作的函数代码中，我们可以看到$connect变量是一个全局变量，首先将上述几个函数放入一个文件，如mysqlconnect.php中，然后在声明相关变量并赋值，在dbConnect函数声明后调用此Mysql数据库连接函数，即：
$hostname = "mysqlserveraddr";
$username = "yourusername";
$pass = "youruserpass";
$db_name = "yourdatabase";
$connect = dbConnect($hostname,$username,$pass,$db_name);
　　通过上面几个Mysql数据库连接、数据库查询、数据库记录集操作函数代码的讲解，在PHP网站开发中Mysql数据库操作的基本函数已包括，根据需要可在此代码基础上改成Mysql数据库类或者利用PHP添加其他的Mysql数据库操作函数都是可行的。查询数据库字段中最大最小值的SQL语句 简明现代魔法
专注于互联网主流的各种技术
-& 查询数据库字段中最大最小值的SQL语句
查询数据库字段中最大最小值的SQL语句
求数据库字段最值
SQL语句如下：
select 字段 from 表
where 字段 = ( select max(字段) from 表)
SQL语句如下：
select 字段 from 表
where 字段 = ( select min(字段) from 表)
//连接数据库
$myconn=mysql_connect("localhost","root","");
mysql_select_db("nowamagic",$myconn);
select article_ID
from article
where article_ID=(select max(article_ID)
from article)
$strSql2="
select article_ID
from article
where article_ID=(select min(article_ID)
from article)
$result=mysql_query($strSql,$myconn) or die(mysql_error());
$result2=mysql_query($strSql2,$myconn) or die(mysql_error());
$row_max=mysql_fetch_array($result);
$row_min=mysql_fetch_array($result2);
// 输出ID最大值
//echo $row_max["article_ID"];
//echo "";
// 输出ID最小值
//echo $row_min["article_ID"];
$article_count = $row_max["article_ID"] - $row_min["article_ID"];
从数据库字段中查询出最大值，赋给$row_然后再查询出最小值，赋给$row_min；然后用最大值减去最小值再加1，就可以得出数据库记录的条数。这里从文章数据库article得出的是文章的数目。
若是单纯地求数据库的记录总数的话，其实用一条SQL语句就可以搞定的。
$conn=mysql_connect("localhost","root","") or die("数据库连接错误".mysql_error());
mysql_select_db("nowamagic",$conn) or die("数据库访问错误".mysql_error());
mysql_query("set names utf-8");
$query="select count(*) as total from article order by article_ID desc";
$result=mysql_query($query);
$message_count=mysql_result($result,0,"total");
echo $message_
运行结果如下：
随机文章推荐
(44) (105) (232) (53) (34) (314) (39) (35) (21) (4) (1) (9) (14) (56) (66) (1) (160) (27) (57) (31) (77) (31) (12) (7) (18) (61)
注：如需转载本文，请注明出处（原文链接），谢谢。更多精彩内容，请进入首页。
你也许还会对以下文章感兴趣
喜欢本文，就分享它吧
您的名字：
您的邮件：
您的网站：
copyright (C) 2009 简明现代魔法 && 学习、分享、进步
power by Gonn 感谢所有关心和支持本站的朋友们}