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');
View Code

²éѯ°¸Àý

  • ²éѯ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)));
View Code

²éѯ°¸Àý

  • ʵÏÖÁ½±íµÄÁ¬½Ó£¬Á¬½ÓÌõ¼þÊÇ 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)));
View Code

²éѯ°¸Àý£º

  • ʵÏÖÈý±íÁª²é£º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 Ñ­»·

  1. ¾¡¿ÉÄܼõÉÙJoinÓï¾äÖеÄNestedLoopµÄÑ­»·×Ü´ÎÊý£»
  2. ÓÀÔ¶ÓÃС½á¹û¼¯Çý¶¯´óµÄ½á¹û¼¯£¨ÔÚ´ó½á¹û¼¯Öн¨Á¢Ë÷Òý£¬ÔÚС½á¹û¼¯ÖбéÀúÈ«±í£©£»
  3. ÓÅÏÈÓÅ»¯NestedLoopµÄÄÚ²ãÑ­»·£»
  4. ±£Ö¤JoinÓï¾äÖб»Çý¶¯±íÉÏJoinÌõ¼þ×Ö¶365betÌåÓýÔÚÏßѾ­±»Ë÷Òý£»
  5. µ±ÎÞ·¨±£Ö¤±»Çý¶¯±íµÄ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());
View Code

1£¬Ë÷ÒýʧЧ׼Ôò

  1. ȫֵƥÅä365betÌåÓýÔÚÏß×î°®
  2. ×î¼Ñ×óǰ׺·¨Ôò£ºÈç¹ûË÷ÒýÁ˶àÀý£¬Òª×ñÊØ×î×óǰ׺·¨Ôò¡£Ö¸µÄÊDzéѯ´ÓË÷ÒýµÄ×î×óǰÁпªÊ¼²¢ÇÒ²»Ìø¹ýË÷ÒýÖеÄÁС£
  3. ²»ÔÚË÷ÒýÁÐÉÏ×öÈκβÙ×÷£¨¼ÆËã¡¢º¯Êý¡¢£¨×Ô¶¯orÊÖ¶¯£©ÀàÐÍת»»£©£¬»áµ¼ÖÂË÷ÒýʧЧ¶ø×ªÏòÈ«±íɨÃè
  4. ´æ´¢ÒýÇæ²»ÄÜʹÓÃË÷ÒýÖз¶Î§Ìõ¼þÓұߵÄÁÐ
  5. ¾¡Á¿Ê¹Óø²¸ÇË÷Òý£¨Ö»·ÃÎÊË÷ÒýµÄ²éѯ£¨Ë÷ÒýÁкͲéѯÁÐÒ»Ö£©£©£¬¼õÉÙselect *
  6. mysqlÔÚʹÓò»µÈÓÚ£¨!=»òÕß<>£©µÄʱºòÎÞ·¨Ê¹ÓÃË÷Òý»áµ¼ÖÂÈ«±íɨÃè(²¢²»¾ø¶Ô£¬Ð迼Âdzɱ¾ÎÊÌ⣬ÀýÈçid!=''ʱ»¹ÊÇ»áÓõ½Ë÷ÒýµÄ)
  7. is null£¬is not null Ò²ÎÞ·¨Ê¹ÓÃË÷Òý£¨²¢²»¾ø¶Ô£¬Ð迼Âdzɱ¾ÎÊÌ⣩
  8. likeÒÔͨÅä·û¿ªÍ·£¨¡¯%abc¡­¡¯£©mysqlË÷ÒýʧЧ»á±ä³ÉÈ«±íɨÃè²Ù×÷(Èç¹ûÊÇ'a%'ÔòʹÓÃrangeË÷Òý)
  9. ×Ö·û´®²»¼ÓÒýºÅË÷ÒýʧЧ
  10. ÉÙÓÃ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)½¨Òé

  1. ¶ÔÓÚµ¥¼üË÷Òý£¬¾¡Á¿Ñ¡ÔñÕë¶Ôµ±Ç°query¹ýÂËÐÔ¸üºÃµÄË÷Òý
  2. ÔÚÑ¡Ôñ×éºÏË÷ÒýµÄʱºò£¬µ±Ç°queryÖйýÂËÐÔ×îºÃµÄ×Ö¶ÎÔÚË÷Òý×Ö¶Î˳ÐòÖУ¬Î»ÖÃÔ½¿¿×óÔ½ºÃ¡£
  3. ÔÚÑ¡Ôñ×éºÏË÷ÒýµÄʱºò£¬¾¡Á¿Ñ¡Ôñ¿ÉÒÔÄܰüº¬µ±Ç°query²éѯÌõ¼þÖÐwhere×Ó¾ä¸ü¶à×ֶεÄË÷Òý
  4. ¾¡¿ÉÄÜͨ¹ý·ÖÎöͳ¼ÆÐÅÏ¢ºÍµ÷ÕûqueryµÄд·¨À´´ïµ½Ñ¡ÔñºÏÊÊË÷ÒýµÄÄ¿µÄ

b)°¸Àý

¡¡¡¡

¡¡

posted @ 2021-01-17 22:48  MXCФijij  ÔĶÁ(231)  ÆÀÂÛ(0±à¼­  ÊÕ²Ø