MYSQLÖÐͳ¼Æ²éѯ½á¹û×ÜÐÐÊýµÄ±ã½Ý·½·¨¡¾×ªÔØ¡¿
ת×Ô£ºhttp://ginew.blog.163.com/
½ñÌìÔĶÁ wordpress´úÂëʱ£¬·¢ÏÖÒ»¸öMYSQLµÄ¹Ø¼ü´Ê SQL_CALC_FOUND_ROWS ²é¿´ÊÖ²áºó·¢Ïִ˹ؼü´ÊµÄ×÷ÓÃÊÇÔÚ²éѯʱͳ¼ÆÂú×ã¹ýÂËÌõ¼þºóµÄ½á¹ûµÄ×ÜÊý£¨²»ÊÜ Limit µÄÏÞÖÆ£©
ÀýÈ磺 SELECT SQL_CALC_FOUND_ROWS tid from cdb_threads WHERE fid=14 LIMIT 1,10;
¼ÙÉèÂú×ãÌõ¼þµÄÓÐ1000Ìõ£¬ÕâÀï·µ»Ø10Ìõ¡£Á¢¼´Ê¹Óà SELECT found_rows() AS rowcount; Ôò·µ»ØµÄ rowcount Ϊ1000£»
ÕâÑù½ÚÊ¡ÁËSELECT count(*) AS rowcountµÄÖØ¸´²éѯ£¬¿ÉÒÔ½ÚÊ¡±È½Ï¿É¹ÛµÄʱ¼ä
===============================
ÒÔÏÂÊǸ÷ÅÔÚÔÚÓÎζÖеÄÓ¦Óãº
function mail_list_sent($uid, $start)
{
// ×¢ÒâSQL_CALC_FOUND_ROWS uidÖ®¼äûÓжººÅ
$query = "SELECT SQL_CALC_FOUND_ROWS uid, real_name, current_city, msg_uid, sender_flag, msg_title, msg_content from ".TT_DBTABLEPRE."mailbox as mb1, ".TT_DBTABLEPRE."user as usr1 WHERE mb1.sender_id=usr1.uid AND mb1.sender_id=$uid AND sender_flag > 0 LIMIT $start, ".TT_PAGESIZE;
$mails = $this->db->fetch_all($query);
//²éѯSELECTÖÐÂú×ãÌõ¼þµÄÐÐÊý£¬ÓëLIMIT×Ó¾äÎÞ¹Ø
$max_count = $this->db->fetch_first("SELECT found_rows() AS rowcount");
$tmp['state_code'] = 200;
$tmp['info'] = "OK";
$tmp['list'] = $mails;
$data = json_encode($tmp);
return $data;
}
¸Ðл×÷Õß·ÖÏí£¡
Ïà¹ØÎĵµ£º
ÔÚ´¦ÀíÒ»¸ö´óÊý¾ÝÁ¿Êý¾Ý¿âµÄʱºò
ͻȻ·¢ÏÖmysql¶ÔÓÚcount(*)µÄ²»Í¬´¦Àí»áÔì³É²»Í¬µÄ½á¹û
±ÈÈçÖ´ÐÐ
SELECT count(*) from tablename
¼´Ê¹¶ÔÓÚǧÍò¼¶±ðµÄÊý¾ÝmysqlÒ²Äܷdz£Ñ¸Ëٵķµ»Ø½á¹û
¶ø¶ÔÓÚ
SELECT count(*) from tablename WHERE…..
mysqlµÄ²éѯʱ¼ä¿ªÊ¼ÅÊÉý
×Ðϸ²éÔÄÀÛÏÂÊÖ²á,·¢ÏÖµ±Ã»ÓÐWHEREÓï¾ä¶ÔÓÚÕ ......
×ܽáÁËһЩMySQLÓÅ»¯·½ÃæµÄ¼¼ÇÉ
Ò». Æô¶¯²ÎÊýÓÅ»¯
ÐÞ¸Ä my.cnf (»òÕßmy.ini)£¬¼ÓÈë/ÐÞ¸ÄÒÔϼ¸ÐÐ
#É趨»º´æµÄÁ¬½ÓÊý,½ÚÊ¡Á¬½ÓʱµÄ¿ªÏú
back_log = 64
#½ûÓÃÎļþϵͳÍâ²¿Ëø
external-locking = 0
#½ûÓÃBDB,Èç¹ûÄãȷʵ²»ÐèÒªµÄ»°,innodbÒ²ÊÇÈç´Ë
skip-bdb
#Ë÷Òý»º³å,Èç¹ûÊÇרÓõÄÊý¾Ý¿â·þÎñÆ÷,¿ÉÒÔÉèÖøߴï·þÎñÆ÷ÄÚ´ ......
Ò»Ö±ÒÔÀ´ÎÒµÄÎÄÕ¶¼ÊÇÒÔ¹¤×÷±Ê¼ÇµÄÐÎʽ´æÔÚ¡£Õâ¶ÔÓÚ×Ô¼º¶ÁÆðÀ´Ã»Ê²Ã´´ó°£¬µ«ÊǶÔÓÚÆäËû¶ÁÕß¿ÉÄܾÍÓÐЩÃÔã¡£ÎÒ¾¡Á¿¸Ä¸Ä£¬»»ÖÖ·½Ê½¡£
1. MySQL µÄ°æ±¾
Èç¹ûÒªÖ§³Ö·ÖÇø±í£¬ÔòÓ¦¸ÃʹÓà 5.1.x ÒÔÉϵİ汾¡£Èç
mysql> show variables like '%version%';
+-------------------------+------------------------------ ......
1¡¢MySQL³£ÓÃÃüÁî
create database name; ´´½¨Êý¾Ý¿â
use databasename; Ñ¡ÔñÊý¾Ý¿â
drop database name Ö±½Óɾ³ýÊý¾Ý¿â£¬²»ÌáÐÑ
show tables; ÏÔʾ±í
describe tablename; ±íµÄÏêϸÃèÊö
select ÖмÓÉÏdistinctÈ¥³ýÖØ¸´×Ö¶Î
......
http://immike.net/blog/2007/04/09/how-not-to-optimize-a-mysql-query/
ÕâÆªÎÄÕ½¨Ò飺
£¨1£©²»ÒªÊ¹ÓÃһЩmysqlµÄtip£¬È磺SQL_SMALL_RESULT, HIGH/LOW_PRIORITY, and INSERT DELAYED£¬STRAIGHT_JOIN£¬³ý·ÇûÓÐÆäËû·½·¨£¬ÒòΪÕâÑùʧȥÁËSQLµÄ¿ÉÒÆÖ²ÐÔ
£¨2£©Êʵ±¶Ô±í½øÐÐË÷Òý
£¨3£©±ÜÃâ¶Ô²»±ØÒªÊý¾ÝµÄ²éѯ
£¨4£©´¦Àí× ......