MySQLÐÔÄÜÓÅ»¯
1. ¼ò½é
ÔÚWebÓ¦ÓóÌÐòÌåϵ¼Ü¹¹ÖУ¬Êý¾Ý³Ö¾Ã²ã£¨Í¨³£ÊÇÒ»¸ö¹ØϵÊý¾Ý¿â£©ÊǹؼüµÄºËÐIJ¿·Ö£¬Ëü¶ÔϵͳµÄÐÔÄÜÓзdz£ÖØÒªµÄÓ°Ïì¡£MySQLÊÇĿǰʹÓÃ×î¶àµÄ¿ªÔ´Êý¾Ý¿â£¬µ«ÊÇMySQLÊý¾Ý¿âµÄĬÈÏÉèÖÃÐÔÄܷdz£µÄ²î£¬½ö½öÊÇÒ»¸öÍæ¾ßÊý¾Ý¿â¡£Òò´ËÔÚ²úÆ·ÖÐʹÓÃMySQLÊý¾Ý¿â±ØÐë½øÐбØÒªµÄÓÅ»¯¡£
ÓÅ»¯ÊÇÒ»¸ö¸´ÔÓµÄÈÎÎñ£¬±¾ÎÄÃèÊöMySQLÏà¹ØµÄÊý¾Ý¿âÉè¼ÆºÍ²éѯÓÅ»¯£¬·þÎñÆ÷¶ËÓÅ»¯£¬´æ´¢ÒýÇæÓÅ»¯¡£
2. Êý¾Ý¿âÉè¼ÆºÍ²éѯÓÅ»¯
ÔÚMySQL
ServerÐÔÄܵ÷ÓÅÖУ¬Ê×ÏÈÒª¿¼ÂǵľÍÊÇDatabase SchemaÉè¼Æ£¬ÕâÒ»µãÊǷdz£ÖØÒªµÄ¡£Ò»¸öÔã¸âµÄSchemaÉè¼Æ¼´Ê¹ÔÚÐÔÄܵ÷ÓŵÄMySQL
ServerÉÏÔËÐУ¬Ò²»á±íÏÖ³öºÜ²îµÄÐÔÄÜ£»ºÍSchemaÏàËÆ£¬²éѯÓï¾äµÄÉè¼ÆÒ²»áÓ°ÏìMySQLµÄÐÔÄÜ£¬Ó¦¸Ã±ÜÃâд³öµÍЧµÄSQL²éѯ¡£ÕâÒ»½Ú½«ÏêϸÌÖÂÛÕâÁ½·½ÃæµÄÓÅ»¯¡£
2.1 Schema Design
SchemaµÄÓÅ»¯È¡¾öÓÚ½«ÒªÔËÐÐʲôÑùµÄquery£¬²»Í¬µÄquery»áÓв»Í¬µÄSchemaÓÅ»¯·½°¸¡£2.2½Ú½«½éÉÜQuery
DesignµÄÓÅ»¯¡£SchemaÉè¼ÆͬÑùÊܵ½Ô¤ÆÚÊý¾Ý¼¯´óСµÄÓ°Ïì¡£SchemaÉè¼ÆʱÖ÷Òª¿¼ÂÇ£º±ê×¼»¯£¬Êý¾ÝÀàÐÍ£¬Ë÷Òý¡£
2.1.1 ±ê×¼»¯
±ê×¼»¯ÊÇÔÚÊý¾Ý¿âÖÐ×éÖ¯Êý¾ÝµÄ¹ý³Ì¡£ÆäÖаüÀ¨£¬¸ù¾ÝÉè¼Æ¹æÔò´´½¨±í²¢ÔÚÕâЩ±í¼ä½¨Á¢¹Øϵ£»Í¨¹ýÈ¡ÏûÈßÓà¶ÈÓë²»Ò»ÖÂÏà¹ØÐÔ£¬¸ÃÉè¼Æ¹æÔò¿ÉÒÔͬʱ±£»¤Êý¾Ý²¢Ìá¸ßÊý¾ÝµÄÁé»îÐÔ¡£Í¨³£Êý¾Ý¿â±ê×¼»¯ÊÇÈÃÊý¾Ý¿âÉè¼Æ·ûºÏijһ¼¶±ðµÄ·¶Ê½£¬Í¨³£Âú×ãµÚÈý·¶Ê½¼´¿É¡£Ò²ÓеÚËÄ·¶Ê½£¨Ò²³ÆΪ
Boyce
Codd·¶Ê½£¬BCNF)£©ÓëµÚÎ巶ʽ´æÔÚ£¬µ«ÊÇÔÚʵ¼ÊÉè¼ÆÖкÜÉÙ¿¼ÂÇ¡£ºöÊÓÕâЩ¹æÔò¿ÉÄÜʹµÃÊý¾Ý¿âµÄÉè¼Æ²»Ì«ÍêÃÀ£¬µ«Õâ²»Ó¦Ó°Ï칦ÄÜ¡£
±ê×¼»¯µÄÌص㣺
1) ËùÓеē¶ÔÏ󔶼ÔÚËü×Ô¼ºµÄtableÖУ¬Ã»ÓÐÈßÓà¡£
2) Êý¾Ý¿âͨ³£ÓÉE-RͼÉú³É¡£
3)
¼ò½à£¬¸üÐÂÊôÐÔͨ³£Ö»ÐèÒª¸üкÜÉٵļǼ¡£
4) Join²Ù×÷±È½ÏºÄʱ¡£
5) Select£¬sortÓÅ»¯´ëÊ©±È½ÏÉÙ¡£
6)
ÊÊÓÃÓÚOLTPÓ¦Óá£
·Ç±ê×¼»¯µÄÌص㣺
1) ÔÚÒ»ÕűíÖд洢ºÜ¶àÊý¾Ý£¬Êý¾ÝÈßÓà¡£
2) ¸üÐÂÊý¾Ý¿ªÏúºÜ´ó£¬¸üÐÂÒ»¸öÊôÐÔ¿ÉÄÜ»á¸üкܶà±í£¬ºÜ¶à¼Ç¼¡£
3)
ÔÚɾ³ýÊý¾ÝÊÇÓпÉÄܶªÊ§Êý¾Ý¡£
4) Select£¬orderÓкܶàÓÅ»¯µÄÑ¡Ôñ¡£
5) ÊÊÓÃÓÚDSSÓ¦Óá£
±ê×¼»¯ºÍ·Ç±ê×¼»¯¶¼Óи÷×ÔµÄÓÅȱµã£¬Í¨³£ÔÚÒ»¸öÊý¾Ý¿âÉè¼ÆÖпÉÒÔ»ìºÏʹÓã¬Ò»²¿·Ö±í¸ñ±ê×¼»¯£¬Ò»²¿·Ö±í¸ñ±£ÁôһЩÈßÓàÊý¾Ý£º
1) ¶ÔOLTPʹÓñê×¼»¯£¬¶ÔDSSʹÓ÷DZê×¼»¯
2)
ʹÓÃÎﻯÊÓͼ¡£MySQL²»Ö±½ÓÖ§³Ö¸ÃÊý¾Ý¿âÌØÐÔ£¬µ«ÊÇ¿ÉÒÔÓÃMyISAM±í´úÌæ¡£
3)
ÈßÓàһЩÊý¾ÝÔÚ±í¸ñÖУ¬ÀýÈ罫ref_idºÍname
Ïà¹ØÎĵµ£º
֮ǰ£¬Ò»Ö±À§ÄպܾõÄMySQLµÄÎÊÌâÒòΪÓÐÁËtcmallocºóµÃÒÔ½â¾ö¡£
ÎÊÌâÊÇ£ºÍøÕ¾·ÃÎÊÁ¿²»¸ß£¬¸ß·åʱ²¢·¢ÊýÔÚ300-400Ö®¼ä¡£CPU±È½Ï¸ß£¬ÔÚ30-80%Ö®¼ä²¨¶¯µÃÀ÷º¦£¬Ê¹ÓÃtopÃüÁî¿ÉÒÔ¿´µ½ÊÇmysql½ø³Ìµ¼Ö£¬Í¬Ê±ÓÃiostatºÍsar²é¿´iowaitÖµºÜ¸ßÔÚ20-30Ö®¼ä¡£
µ«ÊÇϵͳ»¹¿ÉÒÔÎȶ¨ÔËÐУ¬È»ºóÖÜÆÚÐԵijöÏÖswap·ÖÇøÕ¼ÓÃÂÊÅÊÉý£¬Ö±½Óµ¼Ö ......
½â¾ömysql“Access denied for user
ÎÒµÄϵͳÊÇubuntu6.06£¬×î½üÐÂ×°ºÃµÄmysqlÔÚ½øÈëmysql¹¤¾ßʱ£¬×ÜÊÇÓдíÎóÌáʾ:
# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ʹÓÃÍøÉϽéÉܵķ½·¨ÐÞ¸ÄrootÓû§µÄÃÜÂ룺
# mysqladmin -uroot -p p ......
»°ËµÖ®Ç°ÓÃphpдµÄÒ»¸öͳ¼ÆÅܵúúõģ¬Í»È»Ò»Ìì²»³öÊý¾ÝÁË£¬×·ÆäÔÒò³öÔÚsqlÓï¾äÉÏ£¬ÓÉÓÚÊý¾ÝÁ¿Ôö´ó¼ÓÉÏ·þÎñÆ÷ÀϾɣ¬ÔËÐÐsqlÓï¾äµÄʱºò³öÏÖÄÚ´æ²»×ãµÄÌáʾ£¬Ëì¾ö¶¨ÓÅ»¯sqlÓï¾äʹÆä²»²úÉúÁÙʱ±í¡£ÐèÇóÇåÎú£¬ÏÂÃæ¾ÙÀý˵Ã÷£º
ÔsqlÓï¾ä£º
SELECT COUNT( DISTINCT md5 ) from active AS a
......
¡¾×ª×Ô£ºhttp://tdcq.javaeye.com/blog/363955£¬·Ç³£¸ÐлÔ×÷Õß¡¿
ÕâƪÎÄÕÂÊÇÍøÉϸ÷ÖÖ½â¾ömysqlÖÐÎÄÂÒÂëÎÊÌâµÄ¼¯´ó³ÉÕߣ¬Ò»°ã¿ÉÒÔ½â¾öÎÊÌâ¡£
mysql ´´½¨
Êý¾Ý¿âʱָ¶¨±àÂëºÜÖØÒª£¬ºÜ¶à¿ª·¢Õ߶¼Ê¹ÓÃÁËĬÈϱàÂ룬µ«ÊÇÎÒʹÓõľÑéÀ´¿´£¬Öƶ¨Êý¾Ý¿âµÄ±àÂë¿ÉÒԺܴó³Ì¶ÈÉϱÜÃâµ¹Èëµ¼³ö´øÀ´µÄÂÒÂëÎÊÌâ¡£
ÎÒÃÇ×ñѵıê×¼ÊÇ£¬Êý ......
1.¹Ø¼ü×Ö
auto_increment
2.×ÔÔöÓ÷¨
Àý:
CREATE TABLE animals ( id mediumint not null auto_increment,
name char(30) not null,
primary key (id));
3.¹ØÓÚ×ÔÔö
Q:Ôõô»ñµÃµ±Ç°µÄ×ÔÔöµÄ×î´óÖµ?
A:select @@identity
Q:Ôõô»ñµÃtableµÄµ±Ç°×ÔÔö×î´óÖµ?
A:select max(i ......