MySQLµÄË÷ÒýÓÅ»¯·ÖÎö(¶þ)
Ò»¡¢Ë÷ÒýÓÅ»¯
1£¬µ¥±íË÷ÒýÓÅ»¯
½¨±í

CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT(10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL, views INT(10) UNSIGNED NOT NULL, comments INT(10) UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, content TEXT NOT NULL ); INSERT INTO article(author_id,category_id,views,comments,title,content) VALUES (1,1,1,1,'1','1'), (2,2,2,2,'2','2'), (1,1,3,3,'3','3');
²éѯ°¸Àý
- ²éѯcategory_idΪ1ÇÒcomments ´óÓÚ1µÄÇé¿öÏ£¬views×î¶àµÄarticle_id¡£
- ²éѯÓï¾ä£ºSELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
- ·ÖÎöÓï¾ä£ºEXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
¡¡¡¡
- ·ÖÎö£º¿É¿´µ½ÉÏÊötypeΪALL£¬Ö¤Ã÷δÃüÖÐË÷Òý£¬²¢ÇÒUsing filesortʹÓÃÁËÎļþÅÅÐò¡£¹Ê¶ø¿É½øÐÐÓÅ»¯µ½Ê¹ÓÃË÷Òý¡£
ÓÅ»¯Ò»£º
- ´´½¨Ë÷Òý£ºCREATE INDEX idx_article_ccv ON article(category_id,comments,views);
- ²é¿´µ±Ç°Ë÷Òý£ºSHOW INDEX FROM article;
¡¡¡¡
- ²é¿´Ö´Ðмƻ®£ºEXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
¡¡¡¡
- ·ÖÎö£º(ɾ³ýË÷Òý£ºDROP INDEX idx_article_ccv ON article;)
- ÒòΪ°´ÕÕB+TreeË÷ÒýµÄ¹¤×÷ÔÀí£¬ÏÈÅÅÐò category_id£¬Èç¹ûÓöµ½ÏàͬµÄ category_id ÔòÔÙÅÅÐòcomments£¬Èç¹ûÓöµ½ÏàͬµÄ comments ÔòÔÙÅÅÐò views¡£
- ×î×óǰ׺ƥÅäÔÔò£¬µ±comments×Ö¶ÎÔÚÁªºÏË÷ÒýÀï´¦ÓÚÖмäλÖÃʱ£¬ÒòΪ
comments>1
Ìõ¼þÊÇÒ»¸ö·¶Î§Öµ£¨Ëùν range£©£¬MySQL ÎÞ·¨ÀûÓÃË÷ÒýÔÙ¶ÔºóÃæµÄviews²¿·Ö½øÐмìË÷£¬¼´ range ÀàÐͲéѯ×ֶκóÃæµÄË÷ÒýÎÞЧ¡£ - Èç¹û½«Ìõ¼þcomments > 1¸Ä³Écomments = 1£¬Ôòµ±Ç°Ë÷ÒýΪÀàÐÍΪref£¬²¢ÇÒ²»´æÔÚfilesort¡£µ«ÊDz»Âú×ãÌâÒâ
ÓÅ»¯¶þ£º
- ´´½¨Ë÷Òý£ºcreate index idx_article_ccv on article(category_id, views);
- ²é¿´µ±Ç°Ë÷Òý£ºSHOW INDEX FROM article;
¡¡¡¡
- Ôٴβ鿴ִÐмƻ®£ºEXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
¡¡¡¡
- ¿É¿´µ½´ËʱÂú×ãÌõ¼þ£¬Îª·½±ãºóÐø²âÊÔ´ËʱÒÀȻɾ³ýµ±Ç°Ë÷Òý£ºDROP INDEX idx_article_ccv ON article;
2£¬Á½±íË÷ÒýÓÅ»¯
½¨±í

CREATE TABLE IF NOT EXISTS class( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(id) ); CREATE TABLE IF NOT EXISTS book( bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(bookid) ); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
²éѯ°¸Àý
- ʵÏÖÁ½±íµÄÁ¬½Ó£¬Á¬½ÓÌõ¼þÊÇ class.card = book.card
- ²éѯÓï¾ä£ºSELECT * FROM class LEFT JOIN book ON class.card = book.card;
- ·ÖÎöÓï¾ä£ºEXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
¡¡¡¡
- ·ÖÎö£ºtype ÓÐ ALL £¬rows Ϊ±íÖÐÊý¾Ý×ÜÐÐÊý£¬ËµÃ÷ class ºÍ book ½øÐÐÁËÈ«±í¼ìË÷
Ìí¼ÓÓÒ±íË÷Òý
- CREATE INDEX idx_book_card ON book(card);
- ·ÖÎöÓï¾ä£ºEXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
¡¡¡¡
- ·ÖÎö£ºÂú×ãÌõ¼þ£¬¹Ê¶øÉ¾³ýµ±Ç°Ë÷Òý£¬·½±ãºóÐø²âÊÔ£ºDROP INDEX idx_book_card ON book;
- ÕâÊÇÓÉ×óÁ¬½ÓÌØÐÔ¾ö¶¨µÄ¡£LEFT JOINÌõ¼þÓÃÓÚÈ·¶¨ÈçºÎ´ÓÓÒ±íËÑË÷ÐУ¬×ó±ßÒ»¶¨¶¼ÓУ¬365betÌåÓýÔÚÏßÓÒ±ßÊÇ365betÌåÓýÔÚÏßÃǵĹؼüµã£¬Ò»¶¨ÐèÒª½¨Á¢Ë÷Òý¡£
- ×ó±íÁ¬½ÓÓÒ±í£¬ÔòÐèÒªÄÃ×Å×ó±íµÄÊý¾ÝÈ¥ÓÒ±íÀïÃæ²é£¬Ë÷ÒýÐèÒªÔÚÓÒ±íÖн¨Á¢Ë÷Òý
Ìí¼Ó×ó±íË÷Òý
- CREATE INDEX idx_class_card ON class(card);
- ·ÖÎöÓï¾ä£ºEXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
¡¡¡¡
- ·ÖÎö£ºÓÐÉÏͼ¿É¿´³öÀ´Ë÷ÒýδÆðµ½Ì«´óµÄ×÷Óã¬ÒòΪÊÇ×óÁ¬½Ó¡£×ó±íÇý¶¯ÓұߵÄʱºòÓÉÓÚ×ó±í²»ÂÛÈçºÎ¶¼ÐèҪѻ·±éÀú£¬¹Ê¶ørows»¹ÊÇÔÀ´µÄ£¬ÆäʵÕâÑù²¢Ã»ÓÐÌ«´óµÄЧ¹û
- ½«×óÁ¬½Óת³ÉÓÒÁ¬½Ó£ºEXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
¡¡¡¡
- ·ÖÎö£ºÂú×ãÌõ¼þ·½±ã£¬ºóÐø²âÊÔɾ³ýµ±Ç°Ë÷Òý£ºDROP INDEX idx_class_card ON class
- ÕâÊÇÒòΪRIGHT JOINÌõ¼þÓÃÓÚÈ·¶¨ÈçºÎ´Ó×ó±íËÑË÷ÐУ¬ÓÒ±ßÒ»¶¨¶¼ÓУ¬365betÌåÓýÔÚÏß×ó±ßÊÇ365betÌåÓýÔÚÏßÃǵĹؼüµã£¬Ò»¶¨ÐèÒª½¨Á¢Ë÷Òý¡£
- class RIGHT JOIN book £ºbook ÀïÃæµÄÊý¾ÝÒ»¶¨´æÔÚÓÚ½á¹û¼¯ÖУ¬365betÌåÓýÔÚÏßÃÇÐèÒªÄÃ×Å book ±íÖеÄÊý¾Ý£¬È¥ class ±íÖÐËÑË÷£¬365betÌåÓýÔÚÏßË÷ÒýÐèÒª½¨Á¢ÔÚ class ±íÖÐ
3£¬Èý±íË÷ÒýÓÅ»¯
½¨±í

CREATE TABLE IF NOT EXISTS phone( phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(phoneid) )ENGINE=INNODB; INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
²éѯ°¸Àý£º
- ʵÏÖÈý±íÁª²é£ºSELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card
- ·ÖÎöÓï¾ä£ºEXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card
¡¡¡¡
- ½áÂÛ£º
- type ÓÐALL£¬rows Ϊ±íÊý¾Ý×ÜÐÐÊý£¬ËµÃ÷ class¡¢ book ºÍ phone ±í¶¼½øÐÐÁËÈ«±í¼ìË÷
- Extra ÖÐ Using join buffer £¬±íÃ÷Á¬½Ó¹ý³ÌÖÐʹÓÃÁË join »º³åÇø
´´½¨Ë÷Òý£º
-
ALTER TABLE book ADD INDEX x (card); ALTER TABLE phone ADD INDEX y (card);
- ·ÖÎöÓï¾ä£ºEXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card
¡¡¡¡
- ·ÖÎö£º½øÐÐ LEFT JOIN £¬ÓÀÔ¶¶¼ÔÚÓÒ±íµÄ×Ö¶ÎÉϽ¨Á¢Ë÷Òý
4£¬×ܽá
¡¡¡¡½« left join ¿´×÷ÊÇÁ½²ãǶÌ× for Ñ»·
- ¾¡¿ÉÄܼõÉÙJoinÓï¾äÖеÄNestedLoopµÄÑ»·×Ü´ÎÊý£»
- ÓÀÔ¶ÓÃС½á¹û¼¯Çý¶¯´óµÄ½á¹û¼¯£¨ÔÚ´ó½á¹û¼¯Öн¨Á¢Ë÷Òý£¬ÔÚС½á¹û¼¯ÖбéÀúÈ«±í£©£»
- ÓÅÏÈÓÅ»¯NestedLoopµÄÄÚ²ãÑ»·£»
- ±£Ö¤JoinÓï¾äÖб»Çý¶¯±íÉÏJoinÌõ¼þ×Ö¶365betÌåÓýÔÚÏßѾ±»Ë÷Òý£»
- µ±ÎÞ·¨±£Ö¤±»Çý¶¯±íµÄJoinÌõ¼þ×ֶα»Ë÷ÒýÇÒÄÚ´æ×ÊÔ´³ä×ãµÄǰÌáÏ£¬²»ÒªÌ«ÁßϧJoinBufferµÄÉèÖã»
¶þ¡¢Ë÷ÒýʧЧ
´´½¨±í£º

CREATE TABLE staffs( id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'ÐÕÃû', `age` INT NOT NULL DEFAULT 0 COMMENT'ÄêÁä', `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'ְλ', `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'Èëְʱ¼ä' )CHARSET utf8 COMMENT'Ô±¹¤¼Ç¼±í'; INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW()); INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW()); INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
1£¬Ë÷ÒýʧЧ׼Ôò
- ȫֵƥÅä365betÌåÓýÔÚÏß×î°®
- ×î¼Ñ×óǰ׺·¨Ôò£ºÈç¹ûË÷ÒýÁ˶àÀý£¬Òª×ñÊØ×î×óǰ׺·¨Ôò¡£Ö¸µÄÊDzéѯ´ÓË÷ÒýµÄ×î×óǰÁпªÊ¼²¢ÇÒ²»Ìø¹ýË÷ÒýÖеÄÁС£
- ²»ÔÚË÷ÒýÁÐÉÏ×öÈκβÙ×÷£¨¼ÆËã¡¢º¯Êý¡¢£¨×Ô¶¯orÊÖ¶¯£©ÀàÐÍת»»£©£¬»áµ¼ÖÂË÷ÒýʧЧ¶ø×ªÏòÈ«±íɨÃè
- ´æ´¢ÒýÇæ²»ÄÜʹÓÃË÷ÒýÖз¶Î§Ìõ¼þÓұߵÄÁÐ
- ¾¡Á¿Ê¹Óø²¸ÇË÷Òý£¨Ö»·ÃÎÊË÷ÒýµÄ²éѯ£¨Ë÷ÒýÁкͲéѯÁÐÒ»Ö£©£©£¬¼õÉÙ
select *
- mysqlÔÚʹÓò»µÈÓÚ£¨!=»òÕß<>£©µÄʱºòÎÞ·¨Ê¹ÓÃË÷Òý»áµ¼ÖÂÈ«±íɨÃè(²¢²»¾ø¶Ô£¬Ð迼Âdzɱ¾ÎÊÌ⣬ÀýÈçid!=''ʱ»¹ÊÇ»áÓõ½Ë÷ÒýµÄ)
is null
£¬is not null
Ò²ÎÞ·¨Ê¹ÓÃË÷Òý£¨²¢²»¾ø¶Ô£¬Ð迼Âdzɱ¾ÎÊÌ⣩- likeÒÔͨÅä·û¿ªÍ·£¨¡¯%abc¡¡¯£©mysqlË÷ÒýʧЧ»á±ä³ÉÈ«±íɨÃè²Ù×÷(Èç¹ûÊÇ'a%'ÔòʹÓÃrangeË÷Òý)
- ×Ö·û´®²»¼ÓÒýºÅË÷ÒýʧЧ
- ÉÙÓÃor£¬ÓÃËüÁ¬½Óʱ»áË÷ÒýʧЧ
2£¬Ë÷ÒýʧЧ°¸Àý
a)´´½¨¸´ºÏË÷Òý
#´´½¨¸´ºÏË÷Òý
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
#²é¿´Ë÷Òý
SHOW INDEX FROM staffs;
¡¡¡¡
b)whereÌõ¼þÆ¥Åä
- µ±Ë³ÐòÆ¥Åäʱ£º
¡¡¡¡
- µ±²»´æÔÚ×î×óË÷Òý£¨name£©Ê±£º¿ÉÒÔ¿´µ½´ËʱË÷ÒýʧЧ
¡¡¡¡
- µ±ÖмäË÷Òý²»´æÔÚ£¨age£©Ê±£º¿ÉÒÔ¿´µ½´ËʱÓÐЧË÷ÒýÖ»ÓÐÒ»¸öconst£¬¼´£ºÖ»ÓÐnameÓÐЧ
¡¡¡¡
- µ±ÔÙË÷ÒýÁÐÉϼÆËã»òÕßʹÓú¯Êýʱ£¬»áµ¼ÖÂË÷ÒýʧЧ£ºÊ¹ÓÃleft(name,4)='July'
¡¡¡¡
- µ±Ê¹Ó÷¶Î§Ë÷ÒýÖ®ºó£¬ºóÐøµÄË÷Òý¾Í»áʧЧ£ºnameÌõ¼þ²»±ä£¬°ÑageÓÉÔÀ´µÄµÈÓÚ±ä³É´óÓÚ£¬´ËʱË÷ÒýÀàÐ;ÍÓÉref -> range
¡¡¡¡
- ¾¡Á¿Ê¹Óø²¸ÇË÷Òý£¨Ö»·ÃÎÊË÷ÒýµÄ²éѯ£¨Ë÷ÒýÁкͲéѯÁÐÒ»Ö£©£©£¬¼õÉÙ
select *
¡¡¡¡
- likeÖÐʹÓÃ%µÄλÖþö¶¨ÊÇ·ñʹÓÃË÷Òý(Èç¹û%ÔÚ×ó±ß»òÕß×óÓÒ¶¼ÓÐË÷Òý»áʧЧ£¬Èç¹û%Ö»ÔÚÓÒ±ßË÷ÒýÓÐЧ)
¡¡¡¡
-
- Èç¹ûȷʵÊÇÐèÒª½«%·ÅÔÚ×ó±ß¿É²ÉÓø²¸ÇË÷ÒýÓÅ»¯(Ö»²éѯÐèÒªµÄÁв¢ÃüÖе½Ë÷ÒýÉÏ)
¡¡¡¡
- ×Ö·û´®²»¼Óµ¥ÒýºÅË÷ÒýʧЧ£ºname=2000ºÍname='2000'
¡¡¡¡
- ÉÙÓÃor£¬ÓÃËüÁ¬½Óʱ»áË÷ÒýʧЧ
¡¡¡¡
- mysqlÔÚʹÓò»µÈÓÚ£¨!=»òÕß<>£©µÄʱºòÎÞ·¨Ê¹ÓÃË÷Òý»áµ¼ÖÂÈ«±íɨÃè(µ±È»Ò²²¢²»¾ø¶Ô£¬ÕâÀïÓÐÒ»¸ö»ØÖ´³É±¾ÎÊÌâ)
¡¡¡¡
- is null£¬is not null »áµ¼ÖÂË÷ÒýʧЧ£ºkey = null 365ÌåÓýͶעË÷ÒýʧЧ(²¢²»¾ø¶Ô£¬»á¿¼Âdzɱ¾ÎÊÌâ)
¡¡¡¡
×¢ÒâÔÚin¡¢!=¡¢is nullºÍis not null£¬µ½µ×ʲôʱºòË÷Òý£¬Ê²Ã´Ê±ºò²ÉÓÃÈ«±íɨÃèÄØ£¿¡¡ÏêÇéÃèÊöÇëµã»÷²é¿´
³É±¾¡£¶ÔÓÚʹÓöþ¼¶Ë÷Òý£¨innodb£©½øÐвéѯÀ´Ëµ£¬³É±¾×é³ÉÖ÷ÒªÓÐÁ½¸ö·½Ã棺
¶ÁÈ¡¶þ¼¶Ë÷Òý¼Ç¼µÄ³É±¾
½«¶þ¼¶Ë÷Òý¼Ç¼ִÐлرí²Ù×÷£¬Ò²¾ÍÊǵ½¾Û´ØË÷ÒýÖÐÕÒµ½ÍêÕûµÄÓû§¼Ç¼µÄ²Ù×÷Ëù¸¶³öµÄ³É±¾¡£
ºÜÏÔÈ»£¬ÒªÉ¨ÃèµÄ¶þ¼¶Ë÷Òý¼Ç¼ÌõÊýÔ½¶à£¬ÄÇôÐèÒªÖ´ÐеĻرí²Ù×÷µÄ´ÎÊýÒ²¾ÍÔ½¶à£¬´ïµ½ÁËij¸ö±ÈÀýʱ£¬Ê¹Óöþ¼¶Ë÷ÒýÖ´ÐвéѯµÄ³É±¾Ò²¾Í³¬¹ýÁËÈ«±íɨÃèµÄ³É±¾
£¨¾ÙÒ»¸ö¼«¶ËµÄÀý×Ó£¬±È·½ËµÒªÉ¨ÃèµÄÈ«²¿µÄ¶þ¼¶Ë÷Òý¼Ç¼£¬ÄǾÍÒª¶ÔÿÌõ¼Ç¼ִÐÐÒ»±é»Ø±í²Ù×÷£¬×ÔÈ»²»ÈçÖ±½ÓɨÃè¾Û´ØË÷ÒýÀ´µÄ¿ì£©¡£
365betÌåÓýÔÚÏßMySQLÓÅ»¯Æ÷ÔÚÕæÕýÖ´Ðвéѯ֮ǰ£¬¶ÔÓÚ365ÌåÓýͶע¿ÉÄÜʹÓõ½µÄË÷ÒýÀ´Ëµ£¬¶¼»áÔ¤ÏȼÆËãÒ»ÏÂÐèҪɨÃèµÄ¶þ¼¶Ë÷Òý¼Ç¼µÄÊýÁ¿¡£365betÌåÓýÔÚÏß¶ÔÓÚÒÔÉÏÈýÖÖ²éѯÌõ¼þÊÇ·ñ»áÃüÖÐË÷Òý¾ÍÈ¡¾öÓÚ¶þ¼¶Ë÷Òý²éѯµÄ³É±¾ÓëÈ«¾Ö²éѯ³É±¾µÄ¸ßµÍ¡£
Èý¡¢Ë÷Òý°¸Àý
1£¬½¨±í
create table test03( id int primary key not null auto_increment, c1 char(10), c2 char(10), c3 char(10), c4 char(10), c5 char(10) ); insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5'); insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5'); insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5'); insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5'); insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5'); #´´½¨¸´ºÏË÷Òý create index idx_test03_c1234 on test03(c1,c2,c3,c4);
¡¡¡¡
2£¬°¸Àý
#Ö»ÓÐwhere EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4'; EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1'; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4'; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3'; #whereÌõ¼þÓëorder by ½áºÏ EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2; #whereÓëgroup by½áºÏ EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3; EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;
3£¬°¸Àý·ÖÎö
a£©where²éѯ
¡¡¡¡
b£©whereÓëorder by½áºÏ
¡¡¡¡
¡¡¡¡
c£©whereÓëgroup by
¡¡¡¡
¡¡¡¡½áÂÛ£ºgroup by »ù±¾É϶¼ÐèÒª½øÐÐÅÅÐò(ʹÓÃÇé¿ö»ù±¾Óëorder byÏàͬ£¬Ë÷Òý˳Ðò¾ù»á³öÏÖÔÚwhereÖ®ºó)£¬µ«·²Ê¹Óò»µ±£¬»áÓÐÁÙʱ±í²úÉú¡£
4£¬Ë÷ÒýʧЧ×ܽá
a)½¨Òé
- ¶ÔÓÚµ¥¼üË÷Òý£¬¾¡Á¿Ñ¡ÔñÕë¶Ôµ±Ç°query¹ýÂËÐÔ¸üºÃµÄË÷Òý
- ÔÚÑ¡Ôñ×éºÏË÷ÒýµÄʱºò£¬µ±Ç°queryÖйýÂËÐÔ×îºÃµÄ×Ö¶ÎÔÚË÷Òý×Ö¶Î˳ÐòÖУ¬Î»ÖÃÔ½¿¿×óÔ½ºÃ¡£
- ÔÚÑ¡Ôñ×éºÏË÷ÒýµÄʱºò£¬¾¡Á¿Ñ¡Ôñ¿ÉÒÔÄܰüº¬µ±Ç°query²éѯÌõ¼þÖÐwhere×Ó¾ä¸ü¶à×ֶεÄË÷Òý
- ¾¡¿ÉÄÜͨ¹ý·ÖÎöͳ¼ÆÐÅÏ¢ºÍµ÷ÕûqueryµÄд·¨À´´ïµ½Ñ¡ÔñºÏÊÊË÷ÒýµÄÄ¿µÄ
b)°¸Àý
¡¡¡¡
¡¡