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 -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ÓÐÁ½ÖÖģʽ£¬MYISAMÓëINNODB
Èç¹ûÊÇMYISAMµÄ»°£¬ÔÚdataĿ¼ÏÂÀï»á.frm .myd .myi ÈýÖÖÎļþ£¬ÄÇôֱ½Ó°ÑDATAÒÆ¹ýÈ¥£¬ÅäÖúÃȨÏ޾ͿÉÒÔ»¹ÔÁË¡£
Èç¹ûÊÇINNODBµÄ»°£¬ÄÇÊÇÏ൱µÄ·³°¡¡£ÔÚdataĿ¼ÏÂÖ»ÓÐ.frm(ÕâÖ»ÊÇÒ»¸öÊý¾Ý¸ñʽ£©£¬Õâ¸öÒÆ¹ýÈ¥ºó£¬ÊÇÎÞ·¨»¹ÔÔʼÊý¾ÝµÄ¡£
ÕæÕýµÄÊý¾ÝÊÇÕ ......
Ò»¡¢°²×°·þÎñÆ÷¶Ë
£¨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……¡¡¡¡¡¡¡¡¡¡¡¡¡¡##################################### ......
×÷Õߣº°½Ê¿Î°
·þÎñÆ÷£º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±íÏ ......
1¡¢select * from tablename <Ìõ¼þÓï¾ä> limit 100,15
´Ó100Ìõ¼Ç¼ºó¿ªÊ¼(²»°üÀ¨µÚ100Ìõ¼Ç¼)È¡³ö15Ìõ¼Ç¼ (ʵ¼ÊÈ¡³öµÄÊÇµÚ 101-115Ìõ¼Ç¼Êý¾Ý)
2¡¢select * from tablename <Ìõ¼þÓï¾ä> limit 15
È¡³ö ǰ15Ìõ¼Ç¼Êý¾Ý,limit 15Ï൱ÓÚ limit 0,15 ......