[MySQLÓÅ»¯] ÈçºÎ²éÕÒSQLЧÂʵØÏµÄÔÒò
[MySQLÓÅ»¯] -- ÈçºÎ²éÕÒSQLЧÂʵØÏµÄÔÒò
ʱ¼ä:2010-2-28À´Ô´:HaCMS¿ªÔ´ÉçÇø ×÷Õß:chusong
²éѯµ½Ð§ÂÊµÍµÄ SQL Óï¾ä ºó£¬¿ÉÒÔͨ¹ý EXPLAIN »òÕß DESC ÃüÁî»ñÈ¡ MySQL ÈçºÎÖ´ÐÐ SELECT Óï¾äµÄÐÅÏ¢£¬°üÀ¨ÔÚ SELECT Óï¾äÖ´Ðйý³ÌÖбíÈçºÎÁ¬½ÓºÍÁ¬½ÓµÄ˳Ðò£¬±ÈÈçÎÒÃÇÏë¼ÆËã 2006 ÄêËùÓй«Ë¾µÄÏúÊ۶ÐèÒª¹ØÁª sales ±íºÍ company ±í£¬²¢ÇÒ¶Ô profit ×Ö¶Î×öÇóºÍ£¨ s ...
²éѯµ½Ð§ÂÊµÍµÄ SQL Óï¾ä ºó£¬¿ÉÒÔͨ¹ý EXPLAIN »òÕß DESC ÃüÁî»ñÈ¡ MySQL ÈçºÎÖ´ÐÐ SELECT Óï¾äµÄÐÅÏ¢£¬°üÀ¨ÔÚ SELECT Óï¾äÖ´Ðйý³ÌÖбíÈçºÎÁ¬½ÓºÍÁ¬½ÓµÄ˳Ðò£¬±ÈÈçÎÒÃÇÏë¼ÆËã 2006 ÄêËùÓй«Ë¾µÄÏúÊ۶ÐèÒª¹ØÁª sales ±íºÍ company ±í£¬²¢ÇÒ¶Ô profit ×Ö¶Î×öÇóºÍ£¨ sum £©²Ù×÷£¬ÏàÓ¦ SQL µÄÖ´Ðмƻ®ÈçÏ£º
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
2 rows in set (0.00 sec)
ÿ¸öÁеĽâÊÍÈçÏ£º
Extra £ºÖ´ÐÐÇé¿öµÄ˵Ã÷ºÍÃèÊö¡£
ÔÚÉÏÃæµÄÀý×ÓÖУ¬ÒѾ¿ÉÒÔÈ·ÈÏÊÇ ¶Ô a ±íµÄÈ«±íɨÃèµ¼ÖÂЧÂʵIJ»ÀíÏ룬ÄÇô ¶Ô a ±íµÄ year ×ֶδ´½¨Ë÷Òý£¬¾ßÌåÈçÏ£º
mysql> create index idx_sales_year on sales(year);
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
´´½¨Ë÷Òýºó£¬ÕâÌõÓï¾äµÄÖ´Ðмƻ®ÈçÏ£º
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: idx_sales_year
key: idx_sales_
Ïà¹ØÎĵµ£º
Ò»¡¢Ô¼Êø
Ô¼Êø¶¨Ò幨ÓÚÁÐÖÐÔÊÐíÖµµÄ¹æÔò£¬ÊÇÇ¿ÖÆÍêÕûÐԵıê×¼»úÖÆ¡£
ʹÓÃÔ¼ÊøÓÅÏÈÓÚʹÓô¥·¢Æ÷¡¢¹æÔòºÍĬÈÏÖµ¡£²éѯÓÅ»¯Æ÷ҲʹÓÃÔ¼Êø¶¨ÒåÉú³É¸ßÐÔÄܵIJéѯִÐмƻ®¡£SQL Server 2005Ö§³ÖÎåÀàÔ¼Êø£º
1. NOT ......
1. ËÀËøÔÀí
¸ù¾Ý²Ù×÷ϵͳÖе͍Ò壺ËÀËøÊÇÖ¸ÔÚÒ»×é½ø³ÌÖеĸ÷¸ö½ø³Ì¾ùÕ¼Óв»»áÊͷŵÄ×ÊÔ´£¬µ«Òò»¥ÏàÉêÇë±»ÆäËû½ø³ÌËùÕ¾Óò»»áÊͷŵÄ×ÊÔ´¶ø´¦ÓÚµÄÒ»ÖÖÓÀ¾ÃµÈ´ý״̬¡£
ËÀËøµÄËĸö±ØÒªÌõ¼þ£º
»¥³âÌõ¼þ(Mutual exclusion)£º×ÊÔ´²»Äܱ»¹²Ïí£¬Ö»ÄÜÓÉÒ»¸ö½ø³ÌʹÓá£
ÇëÇóÓë±£³ÖÌ ......
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
ʵ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃæ£¬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
Æäʵ£¬ÎÒÃǵĺºÓï×ÖµäµÄÕýÎı¾Éí¾ ......
key_buffer_size - Õâ¶ÔMyISAM±íÀ´Ëµ·Ç³£ÖØÒª¡£Èç¹ûÖ»ÊÇʹÓÃMyISAM±í£¬¿ÉÒÔ°ÑËüÉèÖÃΪ¿ÉÓÃÄÚ´æµÄ 30-40%¡£ºÏÀíµÄֵȡ¾öÓÚË÷Òý´óС¡¢Êý¾ÝÁ¿ÒÔ¼°¸ºÔØ¡£
¼Çס£¬MyISAM±í»áʹÓòÙ×÷ϵͳµÄ»º´æÀ´»º´æÊý¾Ý£¬Òò´ËÐèÒªÁô³ö²¿·ÖÄÚ´æ¸øËüÃÇ£¬ºÜ¶àÇé¿öÏÂÊý¾Ý±ÈË÷Òý´ó¶àÁË¡£¾¡¹ÜÈç´Ë£¬ÐèÒª×ÜÊǼì²éÊÇ·ñËùÓÐµÄ key_buffer ¶¼±»ÀûÓÃÁË¡£ ......
[MySQLÓÅ»¯] -- ÈçºÎʹÓÃSQL Profiler ÐÔÄÜ·ÖÎöÆ÷
ʱ¼ä:2010-2-28À´Ô´:HaCMS¿ªÔ´ÉçÇø ×÷Õß:diesou
mysql µÄ sql ÐÔÄÜ·ÖÎöÆ÷Ö÷ÒªÓÃ;ÊÇÏÔʾ sql Ö´ÐеÄÕû¸ö¹ý³ÌÖи÷Ïî×ÊÔ´µÄʹÓÃÇé¿ö¡£·ÖÎöÆ÷¿ÉÒÔ¸üºÃµÄչʾ³ö²»Á¼ SQL µÄÐÔÄÜÎÊÌâËùÔÚ¡£ ÏÂÃæÎÒÃǾÙÀý½éÉÜһϠMySQL SQL Profiler µÄʹÓ÷½·¨£º Ê×ÏÈ£¬¿ªÆô MySQL SQL ......