Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

MySQLË÷ÒýµÄ²é¿´´´½¨ºÍɾ³ý


1£®Ë÷Òý×÷ÓÃ
ÔÚË÷ÒýÁÐÉÏ£¬³ýÁËÉÏÃæÌáµ½µÄÓÐÐò²éÕÒÖ®Í⣬Êý¾Ý¿âÀûÓø÷ÖÖ¸÷ÑùµÄ¿ìËÙ¶¨Î»¼¼Êõ£¬Äܹ»´ó´óÌá¸ß²éѯЧÂÊ¡£ÌرðÊǵ±Êý¾ÝÁ¿·Ç³£´ó£¬²éÑ¯Éæ¼°¶à¸ö±íʱ£¬Ê¹ÓÃË÷ÒýÍùÍùÄÜʹ²éѯËٶȼӿì³ÉǧÉÏÍò±¶¡£
ÀýÈ磬ÓÐ3¸öδË÷ÒýµÄ±ít1¡¢t2¡¢t3£¬·Ö±ðÖ»°üº¬ÁÐc1¡¢c2¡¢c3£¬Ã¿¸ö±í·Ö±ðº¬ÓÐ1000ÐÐÊý¾Ý×é³É£¬Ö¸Îª1¡«1000µÄÊýÖµ£¬²éÕÒ¶ÔÓ¦ÖµÏàµÈÐеIJéѯÈçÏÂËùʾ¡£
 
SELECT c1,c2,c3 from t1,t2,t3 WHERE c1=c2 AND c1=c3
´Ë²éѯ½á¹ûÓ¦¸ÃΪ1000ÐУ¬Ã¿Ðаüº¬3¸öÏàµÈµÄÖµ¡£ÔÚÎÞË÷ÒýµÄÇé¿öÏ´¦Àí´Ë²éѯ£¬±ØÐëѰÕÒ3¸ö±íËùÓеÄ×éºÏ£¬ÒÔ±ãµÃ³öÓëWHERE×Ó¾äÏàÅäµÄÄÇЩÐС£¶ø¿ÉÄܵÄ×éºÏÊýĿΪ1000×1000×1000£¨Ê®ÒÚ£©£¬ÏÔÈ»²éѯ½«»á·Ç³£Âý¡£
Èç¹û¶Ôÿ¸ö±í½øÐÐË÷Òý£¬¾ÍÄܼ«´óµØ¼ÓËÙ²éѯ½ø³Ì¡£ÀûÓÃË÷ÒýµÄ²éѯ´¦ÀíÈçÏ¡£
£¨1£©´Ó±ít1ÖÐÑ¡ÔñµÚÒ»ÐУ¬²é¿´´ËÐÐËù°üº¬µÄÊý¾Ý¡£
£¨2£©Ê¹Óñít2ÉϵÄË÷Òý£¬Ö±½Ó¶¨Î»t2ÖÐÓët1µÄֵƥÅäµÄÐС£ÀàËÆ£¬ÀûÓñít3ÉϵÄË÷Òý£¬Ö±½Ó¶¨Î»t3ÖÐÓëÀ´×Ôt1µÄֵƥÅäµÄÐС£
£¨3£©É¨Ãè±ít1µÄÏÂÒ»Ðв¢Öظ´Ç°ÃæµÄ¹ý³Ì£¬Ö±µ½±éÀút1ÖÐËùÓеÄÐС£
ÔÚ´ËÇéÐÎÏ£¬ÈÔÈ»¶Ô±ít1Ö´ÐÐÁËÒ»¸öÍêȫɨÃ裬µ«Äܹ»ÔÚ±ít2ºÍt3ÉϽøÐÐË÷Òý²éÕÒÖ±½ÓÈ¡³öÕâЩ±íÖеÄÐУ¬±ÈδÓÃË÷ÒýʱҪ¿ìÒ»°ÙÍò±¶¡£
ÀûÓÃË÷Òý£¬MySQL¼ÓËÙÁËWHERE×Ó¾äÂú×ãÌõ¼þÐеÄËÑË÷£¬¶øÔÚ¶à±íÁ¬½Ó²éѯʱ£¬ÔÚÖ´ÐÐÁ¬½Óʱ¼Ó¿ìÁËÓëÆäËû±íÖеÄÐÐÆ¥ÅäµÄËÙ¶È¡£
2.  ´´½¨Ë÷Òý
ÔÚÖ´ÐÐCREATE TABLEÓï¾äʱ¿ÉÒÔ´´½¨Ë÷Òý£¬Ò²¿ÉÒÔµ¥¶ÀÓÃCREATE INDEX»òALTER TABLEÀ´Îª±íÔö¼ÓË÷Òý¡£
1£®ALTER TABLE
ALTER TABLEÓÃÀ´´´½¨ÆÕͨË÷Òý¡¢UNIQUEË÷Òý»òPRIMARY KEYË÷Òý¡£
 
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
 
ÆäÖÐtable_nameÊÇÒªÔö¼ÓË÷ÒýµÄ±íÃû£¬column_listÖ¸³ö¶ÔÄÄЩÁнøÐÐË÷Òý£¬¶àÁÐʱ¸÷ÁÐÖ®¼äÓöººÅ·Ö¸ô¡£Ë÷ÒýÃûindex_name¿ÉÑ¡£¬È±Ê¡Ê±£¬MySQL½«¸ù¾ÝµÚÒ»¸öË÷ÒýÁи³Ò»¸öÃû³Æ¡£ÁíÍ⣬ALTER TABLEÔÊÐíÔÚµ¥¸öÓï¾äÖиü¸Ä¶à¸ö±í£¬Òò´Ë¿ÉÒÔÔÚͬʱ´´½¨¶à¸öË÷Òý¡£
2£®CREATE INDEX
CREATE INDEX¿É¶Ô±íÔö¼ÓÆÕͨË÷Òý»òUNIQUEË÷Òý¡£
 
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
 
table_name¡¢index_nameºÍcolumn_list¾ßÓÐÓëALTER TABLEÓï¾äÖÐÏàͬµÄº¬Ò壬Ë÷ÒýÃ


Ïà¹ØÎĵµ£º

MYSQL »ù±¾ÃüÁî

½øÈëMySQL:mysql -u root -p password;
Í˳öMySQL:quit/exit;
´´½¨Êý¾Ý¿â£ºcreate database database-name;
ÏÔʾËùÓеÄÊý¾Ý¿â: show databases; ×¢ÒâÓиös;
ɾ³ýÊý¾Ý¿â£ºdrop database databasename;
lÁ¬½ÓÊý¾Ý¿â£ºuse databasename;Ìáʾ£ºdatabase changed;
Ñ¡Ôñµ±Ç°Êý¾Ý¿â£ºselect database();
µ±Ç°Êý¾Ý¿âËù°üº¬µÄ ......

ÈçºÎÒÆÖµ£¨»Ö¸´¡¢»¹Ô­£©MysqlÖеÄinnoDBµÄÊý¾Ý¿â¡£

½ñÌì¸ãÁËÒ»Ì죬ÖÕÓڸ㶨ÁË¡£
¶ÔÓÚMysqlÓÐÁ½ÖÖģʽ£¬MYISAMÓëINNODB
Èç¹ûÊÇMYISAMµÄ»°£¬ÔÚdataĿ¼ÏÂÀï»á.frm .myd .myi ÈýÖÖÎļþ£¬ÄÇôֱ½Ó°ÑDATAÒÆ¹ýÈ¥£¬ÅäÖúÃȨÏ޾ͿÉÒÔ»¹Ô­ÁË¡£
Èç¹ûÊÇINNODBµÄ»°£¬ÄÇÊÇÏ൱µÄ·³°¡¡£ÔÚdataĿ¼ÏÂÖ»ÓÐ.frm(ÕâÖ»ÊÇÒ»¸öÊý¾Ý¸ñʽ£©£¬Õâ¸öÒÆ¹ýÈ¥ºó£¬ÊÇÎÞ·¨»¹Ô­Ô­Ê¼Êý¾ÝµÄ¡£
ÕæÕýµÄÊý¾ÝÊÇÕ ......

Red Hat enterprise linux 5 mysql°²×°²½Öè

Ò»¡¢°²×°·þÎñÆ÷¶Ë
£¨1£©¡¢ÔÚÓÐÁ½¸örmpÎļþµÄĿ¼ÏÂÔËÐÐÈçÏÂÃüÁ
[root@test1 local]# rpm -ivh MySQL-server-5.0.26-0.i386.rpm
ÏÔʾÈçÏÂÐÅÏ¢£º
warning: MySQL-server-5.0.26-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing……¡¡¡¡¡¡¡¡¡¡¡¡¡¡##################################### ......

MySQL 5.5 m2 VS MS Sql Server 2000 SE BenchMark

×÷Õߣº°½Ê¿Î°
·þÎñÆ÷£ºIBM X346
CPU:Intel Xeon 3.0 *4
Äڴ棺2GB
Ó²ÅÌ£ºRAID5
OS£ºwin2003 SE
Ò»¡¢MySQL 5.5 M2
MySQL ĬÈÏÒýÇæInnoDB£¬ÓÅ»¯Ç°ºó¼¸ºõÎÞ²î±ð
¶þ¡¢SQL SERVER 2000 SE
     ´ÓÕâû´Î²âÊÔ¿´£¬MysqlÔÚÊÂÎñÐÔÄܱíÏÖ²»ÈçSQL SERVER¡£Í¬Ê±£¬Á½ÕßÔÚĬÈÏÇé¿ö£¬ÅúÁ¿Insert£¬Mysql±íÏ ......

mysql sqlÓï¾äÖеÄlimitÓ÷¨

1¡¢select * from tablename <Ìõ¼þÓï¾ä> limit 100,15
´Ó100Ìõ¼Ç¼ºó¿ªÊ¼(²»°üÀ¨µÚ100Ìõ¼Ç¼)È¡³ö15Ìõ¼Ç¼ (ʵ¼ÊÈ¡³öµÄÊÇµÚ 101-115Ìõ¼Ç¼Êý¾Ý) 
2¡¢select * from tablename <Ìõ¼þÓï¾ä> limit 15
È¡³ö ǰ15Ìõ¼Ç¼Êý¾Ý,limit 15Ï൱ÓÚ limit 0,15 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ