[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_
Ïà¹ØÎĵµ£º
<!--MySql Çý¶¯³ÌÐò eg. mysql-connector-java-5.0.4-bin.jar-->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<!-- JDBC URL -->
......
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
ʵ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃ棬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
Æäʵ£¬ÎÒÃǵĺºÓï×ÖµäµÄÕýÎı¾Éí¾ ......
¸ù¾ÝÄãµÄʹÓÃÄ¿µÄÎÒ¾õµÃÕâ¸öº¯ÊýÓÐÁ½·½ÃæµÄÓÃ;£º
·ÀÖ¹SQL Injection¹¥»÷£¬Ò²¾ÍÊÇÄã±ØÐëÑéÖ¤Óû§µÄÊäÈë
²Ù×÷Êý¾ÝµÄʱºò±ÜÃâ²»±ØÒªµÄ×Ö·ûµ¼Ö´íÎó
mysql_real_escape_string() º¯ÊýתÒå SQL Óï¾äÖÐʹÓõÄ×Ö·û´®ÖеÄÌØÊâ×Ö·û¡£
ÏÂÁÐ×Ö·ûÊÜÓ°Ï죺
\x00
\n
\r
\
'
"
\x1a
Èç¹û³É¹¦£¬Ôò¸Ãº¯Êý·µ»Ø±»×ªÒåµÄ×Ö·û´ ......
Ä¿Ç°LAMP (Linux + Apache + MySQL + PHPspan style="font-family: Verdana;">) ½ü¼¸ÄêÀ´·¢Õ¹Ñ¸ËÙ£¬ÒѾ³ÉΪWeb ·þÎñÆ÷µÄÊÂʵ±ê×¼¡£LAMPÕâ¸ö´ÊµÄÓÉÀ´×îÔçʼÓڵ¹úÔÓÖ¾“c't Mag
azine”£¬Michael KunzeÔÚ1990Äê×îÏÈ°ÑÕâЩÏîÄ¿×éºÏÔÚÒ»Æð´´ÔìÁËLAMPµÄËõд×Ö¡£ÕâЩ×é¼þËäÈ»²¢²»ÊÇ¿ª¿ªÊ¼¾ÍÉè¼ÆΪһÆðʹ ......
¡¾×ª¡¿http://www.linuxsir.org/main/?q=node/241
1.°²×°»·¾³
²Ù×÷ϵͳ£ºRed Hat Linux Enterprise AS 4.0
Êý¾Ý¿â£ºMySQL 5.0.24
Web·þÎñÆ÷£ºApache 2.2.3
½Å±¾ÓïÑÔ£ºPHP 5.1.6
2.°²×°MySQL 5.0.24
-------------ÏÂÔØÈí¼þ°ümysql-5.0.24.tar.gz£¬µØÖ·http://www.mysql.com-------------
# tar zvxf mysql-5.0.24.ta ......