Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Model Center   Code  
»áÔ±   
   
 
     
   
 ¶©ÔÄ
  ¾èÖú
MYSQLË÷Òý
 
  2296  次浏览      27
 2019-8-19
 
±à¼­ÍƼö:
±¾ÎÄÀ´×ÔÓÚ²©¿ÍÔ°£¬±¾ÎÄÖ÷Ҫͨ¹ý¾ÙÀý˵Ã÷mysqlÈ«ÎÄË÷ÒýÊÇÒ»ÖÖÌØÊâÀàÐ͵ÄË÷Òý£¬Ëü²éÕÒµÄÊÇÎı¾ÖеĹؼü´Ê£¬¶ø²»ÊÇÖ±½Ó±È½ÏË÷ÒýÖеÄÖµ¡£

Ë÷ÒýµÄÀàÐÍ

Ë÷ÒýÓÅ»¯Ó¦¸ÃÊǶԲéѯÐÔÄÜÓÅ»¯×îÓÐЧµÄÊÖ¶ÎÁË¡£

mysqlÖ»ÄܸßЧµØÊ¹ÓÃË÷ÒýµÄ×î×óǰ׺ÁС£

mysqlÖÐË÷ÒýÊÇÔÚ´æ´¢ÒýÇæ²ã¶ø²»ÊÇ·þÎñÆ÷²ãʵÏÖµÄ

B-TreeË÷Òý

B-Treeͨ³£Òâζ×ÅËùÓеÄÖµ¶¼Êǰ´Ë³Ðò´æ´¢µÄ£¬²¢ÇÒÿһ¸öÒ¶×ÓÒ³µ½¸ùµÄ¾àÀëÏàͬ¡£

ͼÖиù½ÚµãûÓл­³öÀ´¡£

B-Tree¶ÔË÷ÒýÁÐÊÇ˳Ðò×éÖ¯´æ´¢µÄ£¬Ë÷ÒýºÜÊʺϲéÕÒ·¶Î§Êý¾Ý¡£

B-TreeË÷ÒýµÄÏÞÖÆ

Èç¹û²»Êǰ´ÕÕË÷ÒýµÄ×î×óÁпªÊ¼²éÕÒ£¬ÔòÎÞ·¨Ê¹ÓÃË÷Òý¡£

²»ÄÜÌø¹ýË÷ÒýÖеÄÁÐ

Èç¹û²éѯÖÐÓÐijÁеķ¶Î§²éѯ£¬ÔòÆäÓÒ±ßËùÓÐÁж¼ÎÞ·¨Ê¹ÓÃË÷ÒýÓÅ»¯²éѯ¡£

ÕâЩÏÞÖÆ¶¼ºÍË÷ÒýÁеÄ˳Ðò´æ´¢ÓйØÏµ¡£»òÕß˵ÊÇË÷Òý˳Ðò´æ´¢µ¼ÖÂÁËÕâЩÏÞÖÆ¡£

¹þÏ£Ë÷Òý£¨hash index£©

¹þÏ£Ë÷Òý»ùÓÚ¹þÏ£±íʵÏֵģ¬Ö»Óо«È·Æ¥ÅäË÷ÒýËùÓÐÁеIJéѯ²ÅÓÐЧ¡£

¶ÔÓÚÿһÐÐÊý¾Ý£¬´æ´¢ÒýÇæ¶¼»á¶ÔËùÓеÄË÷ÒýÁмÆËãÒ»¸ö¹þÏ£Öµ£¨hash code£©£¬¹þÏ£ÖµÊÇÒ»¸ö½ÏСµÄÖµ£¬²¢ÇÒ²»Í¬¼üÖµµÄÐмÆËã³öÀ´µÄ¹þÏ£Öµ²»Ò»Ñù¡£¹þÏ£Ë÷Òý½«ËùÓеĹþÏ£Öµ´æ´¢ÔÚË÷ÒýÖУ¬Í¬Ê±±£´æÖ¸Ïòÿ¸öÊý¾ÝÐеÄÖ¸Õ룬ÕâÑù¾Í¿ÉÒÔ¸ù¾Ý£¬Ë÷ÒýÖÐѰÕÒ¶ÔÓÚ¹þÏ£Öµ£¬È»ºóÔÚ¸ù¾Ý¶ÔÓ¦Ö¸Õ룬·µ»Øµ½Êý¾ÝÐС£

mysqlÖÐÖ»ÓÐmemoryÒýÇæÏÔʽ֧³Ö¹þÏ£Ë÷Òý£¬innodbÊÇÒþʽ֧³Ö¹þÏ£Ë÷ÒýµÄ¡£

¹þÏ£Ë÷ÒýÏÞÖÆ£º

¹þÏ£Ë÷ÒýÖ»°üº¬¹þÏ£ÖµºÍÐÐÖ¸Õ룬²»´æ´¢×Ö¶ÎÖµ£¬ËùÒÔ²»ÄÜʹÓÃ"¸²¸ÇË÷Òý"µÄÓÅ»¯·½Ê½£¬È¥±ÜÃâ¶ÁÈ¡Êý¾Ý±í¡£

¹þÏ£Ë÷ÒýÊý¾Ý²¢²»Êǰ´ÕÕË÷Òýֵ˳Ðò´æ´¢µÄ£¬Ë÷ÒýÒ²¾ÍÎÞ·¨ÓÃÓÚÅÅÐò

¹þÏ£Ë÷ÒýÒ³²»Ö§³Ö²¿·ÖË÷ÒýÁÐÆ¥Åä²éÕÒ£¬ÒòΪ¹þÏ£Ë÷ÒýʼÖÕÊÇʹÓÃË÷ÒýÁеÄÈ«²¿ÄÚÈݼÆËã¹þÏ£ÖµµÄ¡£

¹þÏ£Ë÷ÒýÖ»Ö§³ÖµÈÖµ±È½Ï²éѯ£¬°üÀ¨=£¬in(),<=>£¬²»Ö§³ÖÈκη¶Î§²éѯ¡£ÁÐÈëwhere price>100

·ÃÎʹþÏ£Ë÷ÒýµÄÊý¾Ý·Ç³£¿ì£¬³ý·ÇÓкܶà¹þÏ£³åÍ»£¨²»Í¬µÄË÷ÒýÁÐֵȴÓÐÏàͬµÄ¹þÏ£Öµ£©

Èç¹û¹þÏ£³åÍ»ºÜ¶àµÄ»°£¬Ò»Ð©Ë÷Òýά»¤²Ù×÷µÄ´ú¼ÛÒ²»áºÜ¸ß¡£

ÒòΪÕâЩÏÞÖÆ£¬¹þÏ£Ë÷ÒýÖ»ÊÊÓÃÓÚÄ³Ð©ÌØ¶¨µÄ³¡ºÏ¡£¶øÒ»µ©ÊʺϹþÏ£Ë÷Òý£¬ÔòËü´øÀ´µÄÐÔÄÜÌáÉý½«·Ç³£ÏÔÖø¡£

innodbÒýÇæÓÐÒ»¸öÌØÊâµÄ¹¦ÄÜ¡°×ÔÊÊÓ¦¹þÏ£Ë÷Òý¡±£¬µ±innodb×¢Ò⵽һЩË÷ÒýÖµ±»Ê¹Óõķdz£Æµ·±Ê±£¬ÇÒ·ûºÏ¹þÏ£ÌØµã£¨Èçÿ´Î²éѯµÄÁж¼Ò»Ñù£©£¬Ëü»áÔÚÄÚ´æÖлùÓÚB-TreeË÷ÒýÖ®ÉÏÔÙ´´½¨Ò»¸ö¹þÏ£Ë÷Òý¡£ÕâÊÇÒ»¸öÍêÈ«×Ô¶¯µÄ£¬ÄÚ²¿ÐÐΪ¡£

´´½¨×Ô¶¨Òå¹þÏ£Ë÷Òý£¬ÏñÄ£ÄâinnodbÒ»Ñù´´½¨¹þÏ£Ë÷Òý¡£

ÀýÈçÖ»ÐèÒªºÜСµÄË÷Òý¾Í¿ÉÒÔΪ³¬³¤µÄ¼ü´´½¨Ë÷Òý¡£

˼·£ºÔÚB-Tree»ù´¡ÉÏ´´½¨Ò»¸öα¹þÏ£Ë÷Òý¡£ÕâºÍÕæÕýµÄ¹þÏ£Ë÷Òý²»ÊÇÒ»»ØÊ£¬ÒòΪ»¹ÊÇʹÓÃB-Tree½øÐвéÕÒ£¬µ«ÊÇËüʹÓùþÏ£Öµ¶ø²»ÊǼü±¾Éí½øÐÐË÷Òý²éÕÒ¡£ÐèÒª×öµÄ¾ÍÊÇÔÚ²éѯµÄwhere ×Ó¾äÖÐÊÖ¶¯Ö¸¶¨Ê¹ÓùþÏ£º¯Êý¡£

Àý×Ó£º

Èç¹ûÐèÒª´æ´¢´óÁ¿µÄurl£¬²¢ÐèÒª¸ù¾Ýurl½øÐÐËÑË÷²éÕÒ¡£Èç¹ûʹÓÃB-TreeÀ´´æ´¢URL£¬´æ´¢µÄÄÚÈݾͻáºÜ´ó£¬ÒòΪURL±¾Éí¶¼ºÜ³¤¡£Õý³£Çé¿öÏ»áÓÐÈçϲéѯ£º

mysql> select id from url where url='http://www.mysql.com';

Èôɾ³ýÔ­À´urlÁÐÉϵÄË÷Òý£¬¶øÐÂÔöÒ»¸ö±»Ë÷ÒýµÄurl_crcÁУ¬Ê¹ÓÃcrc32×ö¹þÏ£¡£¾Í¿ÉÒÔʵÏÖÒ»¸öα¹þÏ£Ë÷Òý£»²éѯ¾Í±ä³ÉÏÂÃæµÄ·½Ê½£º

mysql> select id from url where url='http://www.mysql.com'
-> and url_crc=crc32("http://www.mysql.com");

ÕâÑùÐÔÄÜ»áÌá¸ßºÜ¶à¡£

µ±È»ÕâÑùʵÏÖµÄȱÏÝÊÇÐèҪά»¤¹þÏ£Öµ£¬¾ÍÊÇurl¸Ä±ä¶ÔÓ¦¹þÏ£ÖµÒ²Ó¦¸Ã¸Ä±ä¡£¿ÉÒÔÊÖ¶¯Î¬»¤£¬µ±È»×îºÃÊÇʹÓô¥·¢Æ÷ʵÏÖ¡£

´´½¨URL±í

create table URL £¨
id int unsigned NOT NULL auto_increment,
url varchar(255) NOT NULL,
url_crc int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY (url_crc)
£©;

´´½¨´¥·¢Æ÷£º

delimiter //
create trigger url_hash_crc_ins before insert on URL FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

CREATE TRIGGER url_hash_crc_upd BEFORE UPDATE ON URL FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

delimiter ;

mysql> select * from URL;
+----+-----------------------+------------+
| id | url | url_crc |
+----+-----------------------+------------+
| 1 | htttp://www.mysql.com | 1727608869 |
+----+-----------------------+------------+
1 row in set (0.00 sec)

mysql> insert into URL(url) values('htttp://www.');
Query OK, 1 row affected (0.00 sec)

mysql> select * from URL;
+----+-----------------------+------------+
| id | url | url_crc |
+----+-----------------------+------------+
| 1 | htttp://www.mysql.com | 1727608869 |
| 2 | htttp://www. | 1196108391 |
+----+-----------------------+------------+
2 rows in set (0.00 sec)

mysql> UPDATE URL SET url='http://www.baidu.com' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from URL;
+----+-----------------------+------------+
| id | url | url_crc |
+----+-----------------------+------------+
| 1 | htttp://www.mysql.com | 1727608869 |
| 2 | http://www.baidu.com | 3500265894 |
+----+-----------------------+------------+
2 rows in set (0.00 sec)

Èç¹û²ÉÓÃÕâÖÖ·½Ê½£¬²»ÒªÊ¹ÓÃSHA1()ºÍMD5()×÷Ϊ¹þÏ£º¯Êý£¬Ó¦¸ÃÕâ¸öº¯Êý¼ÆËã³öÀ´µÄ¹þÏ£ÖµÊǷdz£³¤µÄ×Ö·û´®£¬»áÀË·Ñ´óÁ¿¿Õ¼ä£¬±È½Ïʱҳ»Ø¸üÂý¡£

¶øÈç¹ûÊý¾Ý±í·Ç³£´ó£¬crc32()»á³öÏÖ´óÁ¿µÄ¹þÏ£³åÍ»£¬¶ø½â¾ö¹þÏ£³åÍ»£¬¿ÉÒÔÔÚ²éѯÖÐÔö¼Óurl±¾Éí£¬½øÐнøÒ»²½Åųý£»

ÈçÏÂÃæ²éѯ¾Í¿ÉÒÔ½â¾ö¹þÏ£³åÍ»µÄÎÊÌ⣺

mysql> select id from url where url='http://www.mysql.com'
-> and url_crc=crc32("http://www.mysql.com");

¿Õ¼äÊý¾ÝË÷Òý£¨R-Tree£©

myisam ±íÖ§³Ö¿Õ¼äË÷Òý£¬¿ÉÒÔÓÃ×÷µØÀíÊý¾Ý´æ´¢¡£

È«ÎÄË÷Òý

È«ÎÄË÷ÒýÊÇÒ»ÖÖÌØÊâÀàÐ͵ÄË÷Òý£¬Ëü²éÕÒµÄÊÇÎı¾ÖеĹؼü´Ê£¬¶ø²»ÊÇÖ±½Ó±È½ÏË÷ÒýÖеÄÖµ¡£µÚ7ÕÂÖлáÏêϸ½éÉÜ

Ë÷ÒýµÄÓŵã

Ë÷Òý´ó´ó¼õÉÙÁË·þÎñÆ÷ÐèҪɨÃèµÄÊý¾ÝÁ¿

Ë÷Òý¿ÉÒÔ°ïÖú·þÎñÆ÷±ÜÃâÅÅÐòºÍÁÙʱ±í

Ë÷Òý¿ÉÒÔ½«Ëæ»úI/O±ä³É˳ÐòI/O

Ë÷ÒýÖ»Òª°ïÖú´æ´¢ÒýÇæ¿ìËÙ²éÕÒµ½¼Ç¼£¬´øÀ´µÄºÃ´¦´óÓÚÆä´øÀ´µÄ¶îÍ⹤×÷ʱ£¬Ë÷Òý²ÅÊÇÓÐЧµÄ¡£¶ÔÓڷdz£Ð¡µÄ±í£¬¾Í²»ÊʺÏË÷Òý¡£ÒòΪȫ±íɨÃèÀ´µÄ¸üÖ±½Ó£¬Ë÷Òý»¹ÐèҪά»¤£¬¿ªÏúÒ²²»Ð¡¡£

¶ø¶ÔÓÚÌØ´óÐÍµÄ±í£¬½¨Á¢ºÍʹÓÃË÷ÒýµÄ´ú¼ÛËæÖ®Ôö³¤¡£ÕâÖÖÇé¿öÏ£¬ÔòÐèÒªÒ»ÖÖ¼¼Êõ¿ÉÒÔÖ±½ÓÇø·Ö³ö²éѯÐèÒªµÄÒ»×éÊý¾Ý£¬¶ø²»ÊÇÒ»Ìõ¼Ç¼¡£ÀýÈç¿ÉÒÔʹÓ÷ÖÇø£¬»òÕß¿ÉÒÔ½¨Á¢ÔªÊý¾ÝÐÅÏ¢±íµÈ¡£¶ÔÓÚTP¼¶±ðµÄÊý¾Ý£¬¶¨Î»µ¥Ìõ¼Ç¼µÄÒâÒå²»´ó£¬Ë÷Òý¾­³£»áʹÓÿ鼶±ðÔªÊý¾Ý¼¼ÊõÀ´Ìæ´úË÷Òý¡£

¸ßÐÔÄܵÄË÷Òý²ßÂÔ

ÕýÈ·µØ´´½¨ºÍʹÓÃË÷ÒýÊÇʵÏÖ¸ßÐÔÄܲéѯµÄ»ù´¡¡£

1 ¶ÀÁ¢µÄÁÐ

¡°¶ÀÁ¢µÄÁС±ÊÇÖ¸Ë÷ÒýÁв»ÄÜÊDZí´ïʽµÄÒ»²¿·Ö£¬Ò²²»ÄÜÊǺ¯ÊýµÄ²ÎÊý¡£

ÀýÈ磺ÏÂÃæÔòÎÞ·¨Ê¹ÓÃactor_idÁеÄË÷Òý£º

mysql> select actor_id from sakila.actor where actor_id + 1 = 5

¶øÏÂÃæµÄactor_id ÁеÄË÷ÒýÔò»á±»Ê¹ÓÃ

mysql> select actor_id from sakila.actor where actor_id = 5 - 1

2 ǰ׺Ë÷ÒýºÍË÷ÒýÑ¡ÔñÐÔ

ǰ׺µÄÑ¡ÔñÐÔ¼ÆË㣺

mysql> select count(DISTINCT city)/count(*) from table_name
Ç°×ºÈ¥ÖØÊý ³ý ×ÜÊý¡£


mysql> select
count(DISTINCT LEFT(city,3)) / count(*) AS sel3,
count(DISTINCT LEFT(city,4)) / count(*) AS sel4,
count(DISTINCT LEFT(city,5)) / count(*) AS sel5,
count(DISTINCT LEFT(city,6)) / count(*) AS sel6,
count(DISTINCT LEFT(city,7)) / count(*) AS sel7
from city;
+--------+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 | sel7 |
+--------+--------+--------+--------+--------+
| 0.7633 | 0.9383 | 0.9750 | 0.9900 | 0.9933 |
+--------+--------+--------+--------+--------+

¿ÉÒÔ¿´µ½µ±Ç°×º³¤¶È´ïµ½6Ö®ºó£¬Ñ¡ÔñÐÔÌáÉýµÄ·ù¶ÈÒѾ­ºÜСÁË¡£

Òò´ËÑ¡Ôñǰ׺³¤¶ÈΪ6£»

ǰ׺Ë÷ÒýÊÇÒ»ÖÖÄÜʹË÷Òý¸üС£¬¸ü¿ìµÄÓÐЧ°ì·¨£¬µ«Ò²ÊÇÓÐȱµãµÄ£º

mysqlÎÞ·¨Ê¹ÓÃǰ׺Ë÷Òý×öorder by ºÍgroup by£¬Ò²ÎÞ·¨Ê¹ÓÃǰ׺Ë÷Òý×ö¸²¸ÇɨÃè¡£

3 ¶àÁÐË÷Òý

ÔÚ¶à¸öÁÐÉϽ¨Á¢µÄµ¥ÁÐË÷Òý´ó²¿·ÖÇé¿öϲ¢²»ÄÜÌá¸ßmysqlµÄ²éѯÐÔÄÜ¡£mysql5.0ÒÔºóÒýÈëÁËÒ»ÖÖ½Ð"Ë÷ÒýºÏ²¢(index merge)"µÄ²ßÂÔ£¬Ò»¶¨³Ì¶ÈÉÏ¿ÉÒÔʹÓñíÉϵĶà¸öµ¥ÁÐË÷ÒýÀ´¶¨Î»Ö¸¶¨µÄÐС£

Àý×Ó£º±ífilm_actorÔÚ×Ö¶Îfilm_id ºÍ actor_idÉϸ÷ÓÐÒ»¸öµ¥ÁÐË÷Òý¡£

mysql> show create table film_actor;
| film_actor | CREATE TABLE `film_actor` (
`actor_id` smallint(5) unsigned NOT NULL,
`film_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_fk_film_id` (`film_id`),
CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> explain select film_id,actor_id from film_actor where actor_id=1 or film_id =1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY,idx_fk_film_id
key_len: 2,2
ref: NULL
rows: 29
Extra: Using union(PRIMARY,idx_fk_film_id); Using where

¿ÉÒÔ¿´µ½Ê¹Óúϲ¢Ë÷Òý£¨index_merge£©¼¼Êõ£¬ÓÅ»¯Á˴˴βéѯ£»

ʵ¼ÊÉÏҲ˵Ã÷Á˱íÉϵÄË÷Òý½¨µÃºÜÔã¸â£¬²»È»¾Í²»ÓÃϵͳÓÅ»¯ÁË£»

ºÏ²¢Ë÷ÒýÓÐÈý¸ö±äÖÖ£ºORÌõ¼þµÄÁªºÏ(union),andÌõ¼þµÄÏཻ(intersection),×éºÏǰÁ½ÖÖÇé¿öµÄÁªºÏÒÔ¼°Ïཻ¡£

µ±³öÏÖ·þÎñÆ÷¶Ô¶à¸öË÷Òý×öÏཻ²Ù×÷ʱ£¨Í¨³£Óжà¸öANDÌõ¼þ£©£¬Í¨³£Òâζ×ÅÐèÒªÒ»¸ö°üº¬ËùÓÐÏà¹ØÁеĶàÁÐË÷Òý£¬¶ø²»ÊǶà¸ö¶ÀÁ¢µÄµ¥ÁÐË÷Òý¡£

µ±·þÎñÆ÷ÐèÒª¶Ô¶à¸öË÷Òý×öÁªºÏ²Ù×÷ʱ£¨Í¨³£Óжà¸öORÌõ¼þ£©£¬Í¨³£ÐèÒªºÄ·Ñ´óÁ¿CPUºÍÄÚ´æ×ÊÔ´ÔÚËã·¨µÄ»º´æ£¬ÅÅÐò£¬ºÍºÏ²¢²Ù×÷ÉÏ¡£ÌرðÊǵ±ÆäÖÐÓÐЩË÷ÒýµÄÑ¡ÔñÐÔ²»¸ß£¬ÐèÒªºÏ²¢É¨Ãè·µ»Ø´óÁ¿Êý¾ÝµÄʱºò¡£

¸üÖØÒªµÄÊÇ£¬ÓÅ»¯Æ÷²»»á°ÑÕâЩ¼ÆËãµ½"²éѯ³É±¾(cost)"ÖУ¬ÓÅ»¯Æ÷Ö»¹ØÐÄËæ»úÒ³Ãæ¶ÁÈ¡¡£

×ÜÖ®Èç¹ûÔÚexplainÖп´µ½Ë÷ÒýºÏ²¢£¬Ó¦¸ÃºÃºÃ¼ì²éһϲéѯºÍ±íµÄ½á¹¹£¬¿´ÊDz»ÊÇÒѾ­ÊÇ×îÓŵġ£Ò²¿ÉÒÔͨ¹ýoptimizaer_switchÀ´¹Ø±ÕË÷ÒýºÏ²¢¹¦ÄÜ¡£Ò²¿ÉÒÔʹÓÃINGORE INDEXÌáʾ ÈÃÓÅ»¯Æ÷ºöÂÔµôijЩË÷Òý¡£

4 Ñ¡ÔñºÏÊʵÄË÷ÒýÁÐ˳Ðò

ÕýÈ·µÄ˳ÐòÒÀÀµÓÚʹÓøÃË÷ÒýµÄ²éѯ£¬²¢ÇÒͬʱÐèÒª¿¼ÂÇÈçºÎ¸üºÃµØÂú×ãÅÅÐòºÍ·Ö×éµÄÐèÒª¡£

ÔÚÒ»¸ö¶àÁÐBTreeË÷ÒýÖУ¬Ë÷ÒýÁеÄ˳ÐòÒâζ×ÅË÷ÒýÊ×ÏȰ´ÕÕ×î×óÁнøÐÐÅÅÐò£¬Æä´ÎÊǵڶþÁеȴý¡£ËùÒÔ£¬Ë÷Òý¿ÉÒÔ°´ÕÕÉýÐò»òÕß½µÐò½øÐÐɨÃ裬ÒÔÂú×㾫ȷ·ûºÏÁÐ˳ÐòµÄORDER BY ,GROUP BY,DISTINCTµÈ×Ó¾äµÄ²éѯÐèÇó¡£

µ±²»ÐèÒª¿¼ÂÇÅÅÐòºÍ·Ö×éʱ£¬½«Ñ¡ÔñÐÔ×î¸ßµÄÁзÅÔÚÇ°ÃæÍ¨³£ÊǺܺõġ£ÕâʱºòË÷ÒýµÄ×÷ÓÃÖ»ÊÇÓÃÓÚÓÅ»¯whereÌõ¼þµÄ²éѯ¡£

ÒÔÏÂÃæµÄ²éѯΪÀý£º

mysql> select * from payment where staff_id =2 and customer_id=584;

ÊÇÓ¦¸Ã´´½¨Ò»¸ö(staff_id,customer_id)Ë÷Òý»¹ÊÇÓ¦¸Ãµßµ¹Ò»Ï£¿¿ÉÒÔÅÜһЩ²éѯÀ´È·¶¨ÔÚÕâ¸ö±íÖÐÖµµÄ·Ö²¼Çé¿ö£¬²¢È·¶¨ÄĸöÁеÄÑ¡ÔñÐÔ¸ü¸ß¡£

mysql> select sum(staff_id=2),sum(customer_id=584) from payment \G;
*************************** 1. row ***************************
sum(staff_id=2): 7992
sum(customer_id=584): 30
1 row in set (0.04 sec)

Ó¦¸Ã½²customer_id·ÅÔÚÇ°Ãæ£¬ÒòΪ¶ÔÓÚÌõ¼þÖµµÄcustomer_idÊýÁ¿¸üС¡£

mysql> select sum(staff_id=2) from payment where customer_id=584 \G;
*************************** 1. row ***************************
sum(staff_id=2): 17
1 row in set (0.00 sec)

¿ÉÒÔ¿´µ½custmoer_id=584ʱstaff_id=2 Ö»ÓÐ17¸ö£»

ÐèҪעÒ⣬²éѯ½á¹û·Ç³£ÒÀÀµÓÚÑ¡¶¨µÄ¾ßÌåÖ¸¶¨Öµ£»

µ±È»»¹¿ÉÒÔʹÓüÆËãÁ½²ÎÊýµÄÑ¡ÔñÐÔ£¬À´È·¶¨Äĸö²ÎÊý·ÅÔÚÇ°Ãæ£º

mysql> select count(DISTINCT staff_id) / count(*) AS staff_id_first, count(DISTINCT customer_id) / count(*) AS customer_id_first from payment\G
*************************** 1. row ***************************
staff_id_first: 0.0001
customer_id_first: 0.0373

ÏÔÈ»customer_idµÄÑ¡ÔñÐÔ£¨ÁÐÈ¥ÖØÊý ³ý ËùÓÐÁÐ×ÜÊý£© ¸üºÃ£¬

Ë÷ÒýÁеĻùÊý£¨¼´Ìض¨Ìõ¼þϵÄÊýÁ¿£©£¬»áÓ°ÏìË÷ÒýÐÔÄÜ£»

¾¡¹Ü¹ØÓÚÑ¡ÔñÐԺͻùÊýµÄ¾­Ñé·¨ÔòÖµµÃÈ¥Ñо¿ºÍ·ÖÎö£¬µ«Ò»¶¨Òª¼Çסwhere ×Ó¾äÖеÄÅÅÐò£¬·Ö×éºÍ·¶Î§Ìõ¼þµÈÆäËûÒòËØ£¬ÕâЩÒòËØ¿ÉÄܶԲéѯµÄÐÔÄÜÔì³É·Ç³£´óµÄÓ°Ïì¡£

5 ¾Û´ØË÷Òý

¾Û´ØË÷Òý²¢²»ÊÇÒ»ÖÖµ¥¶ÀµÄË÷ÒýÀàÐÍ£¬¶øÊÇÒ»ÖÖÊý¾Ý´æ´¢·½Ê½¡£

innodbµÄ¾Û´ØË÷Òýʵ¼ÊÉÏÔÚͬһ½á¹¹Öб£´æÁËBTreeË÷ÒýºÍÊý¾ÝÐС££¨Ö÷¼üÊÇBTreeË÷Òý+¼Ç¼ÊÇÊý¾ÝÐУ©

µ±±íÓо۴ØË÷Òýʱ£¬ËüµÄÊý¾ÝÐÐʵ¼ÊÉÏ´æ·ÅÔÚË÷ÒýµÄÒ¶×ÓÒ³ÖС£ÊõÓï"¾Û´Ø"±íʾÊý¾ÝÐкÍÏàÁڵļüÖµ½ô´ÕµØ´æ´¢ÔÚÒ»Æð¡£

ÏÂͼչʾÁ˾۴ØË÷ÒýÖеļǼÊÇÈçºÎ´æ·ÅµÄ¡£×¢Òâµ½£¬Ò¶×ÓÒ³°üº¬ÁËÐеÄÈ«²¿Êý¾Ý£¬µ«½ÚµãÒ³Ö»°üº¬ÁËË÷ÒýÁС£ÔÚÕâ¸ö°¸ÀýÖУ¬Ë÷ÒýÁаüº¬µÄÊÇÕûÊýÖµ¡£

innodbͨ¹ýÖ÷¼ü¾Û¼¯Êý¾Ý£¬ÉÏͼÖеÄ"±»Ë÷ÒýµÄÁÐ"¾ÍÊÇÖ÷¼üÁС£

¾Û¼¯µÄÓŵ㣺

¿ÉÒÔ°ÑÏà¹ØÊý¾Ý±£´æÔÚÒ»Æð¡£¼õÉÙ´ÅÅÌI/O

Êý¾Ý·ÃÎʸü¿ì

ʹÓø²¸ÇË÷ÒýɨÃèµÄ²éѯ¿ÉÒÔÖ±½ÓʹÓÃÒ³½ÚµãÖеÄÖ÷¼üÖµ

¾Û¼¯µÄȱµã£º

¾Û´ØÊý¾Ý×î´óÏ޶ȵØÌá¸ßÁËI/OÃܼ¯ÐÍÓ¦ÓõÄÐÔÄÜ£¬µ«Èç¹ûÊý¾ÝÈ«²¿¶¼·ÅÔÚÄÚ´æÖУ¬Ôò·ÃÎʵÄ˳Ðò¾ÍûÓÐÄÇÃ´ÖØÒªÁË£¬¾Û´ØË÷ÒýÒ²¾ÍûʲôÓÅÊÆÁË¡£

²åÈëËÙËÙÑÏÖØÒÀÀµÓÚ²åÈë˳Ðò¡£

¸üо۴ØË÷ÒýÁеĴú¼ÛºÜ¸ß¡£

³öÈëÐÂÐлòÕßÖ÷¼ü¸üÐÂÐèÒªÒÆ¶¯Ê±£¬¿ÉÄÜÃæÁÙ"Ò³·ÖÁÑ(page split)"ÎÊÌâ¡£µ±ÐеÄÖ÷¼üÖµÒªÇó±ØÐë²åÈ뵽ij¸öÒÑÂúµÄÒ³ÖÐʱ£¬´æ´¢ÒýÇæ»á½«¸ÃÒ³·ÖÁѳÉÁ½¸öÒ³ÃæÀ´ÈÝÄɸÃÐУ¬Õâ¾ÍÊÇÒ»´ÎÒ³·ÖÁѲÙ×÷¡£Ò³·ÖÁѻᵼÖ±íÕ¼Óøü¶àµÄ´ÅÅ̿ռ䡣

¶þ¼¶Ë÷Òý£¨·Ç¾Û´ØË÷Òý£©¼´ÆÕͨË÷Òý£¬ÔÚÆäÒ¶×Ó½Úµã°üº¬ÁËÒýÓÃÐеÄÖ÷¼üÁС£

innodbºÍmyisamµÄÊý¾Ý·Ö²¼¶Ô±È£º

crate table layout_test(
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
);

¼ÙÉècol1 ȡֵ1--10000£¬°´ÕÕËæ»ú˳Ðò²åÈë¡£col2ȡֵ´Ó1--100Ö®¼äËæ»ú¸³Öµ£¬ËùÒÔÓкܶàÖØ¸´µÄÖµ¡£

myisamµÄÊý¾Ý·Ö²¼·Ç³£¼òµ¥£¬°´ÕÕÊý¾Ý²åÈëµÄ˳Ðò´æ´¢ÔÚ´ÅÅÌÉÏ¡£ÈçÏÂͼ£º

ÕâÖÖ·Ö²¼·½Ê½ºÜÈÝÒ×´´½¨Ë÷Òý£¬ÏÂͼ£¬Òþ²ØÁËÒ³µÄÎïÀíϸ½Ú£¬Ö»ÏÔʾË÷ÒýÖеÄ"½Úµã"

Ë÷ÒýÖеÄÿ¸öÒ¶×Ó½Úµã°üº¬"Ðкš£±íµÄÖ÷¼üºÍÐкÅÔÚÒ¶×Ó½ÚµãÖУ¬ÇÒÒ¶×Ó½Úµã¸ù¾ÝÖ÷¼ü˳ÐòÅÅÁС£

ÄÇcol2ÁÐÉϵÄË÷ÒýÓÖ»áÔõôÑùÄØ£¿ÓÐÊ²Ã´ÌØÊâÂ𣿴ð°¸ÊÇ·ñ¶¨µÄ£¬ËûºÍÆäËûÈκÎË÷ÒýÒ»Ñù¡£

ÊÂʵÉÏ£¬myisamÖÐÖ÷¼üË÷ÒýºÍÆäËûË÷ÒýÔڽṹÉÏûÓÐʲô²»Í¬¡£Ö÷¼üË÷Òý¾ÍÊÇÒ»¸öÃûΪPRIMARYµÄΨһ·Ç¿ÕË÷Òý¡£

innodbµÄÊý¾Ý·Ö²¼¡£ÒòΪinnodbÖ§³Ö¾Û´ØË÷Òý£¬Ë÷ÒýʹÓ÷dz£²»Í¬µÄ·¶Ê½´æ´¢Í¬ÑùµÄÊý¾Ý¡£¿´ÏÂͼ£º

µÚÒ»ÑÛ¿´ÉÏÈ¥£¬¸Ð¾õºÍÇ°ÃæµÄͼ5-5ûÓÐʲô²»Í¬£¬Æäʵ¸Ãͼ£¬ÏÔʾÁËÕû¸ö±í£¬¶ø²»ÊÇÖ»ÓÐË÷Òý¡£ÒòΪÔÚinnodbÖУ¬¾Û´ØË÷Òý"¾ÍÊÇ"±í£¬ËùÒÔ²»ÓÃÏëmyisamÄÇÑùÐèÒª¶ÀÁ¢µÄÐд洢¡£

innodb¶þ¼¶Ë÷ÒýµÄÒ¶×Ó½ÚµãÖд洢µÄ²»ÊÇ"ÐÐÖ¸Õë"£¨¼´²»ÊÇÄǸöÐкţ©£¬¶øÊÇÖ÷¼üÖµ£¬²¢ÒÔ´Ë×÷ΪָÏòÐеÄ"Ö¸Õë"¡£ÕâÑùµÄ²ßÂÔ¼õÉÙÁ˵±³öÏÖÐÐÒÆ¶¯»òÕßÊý¾ÝÒ³·ÖÁÑʱ¶þ¼¶Ë÷ÒýµÄά»¤¹¤×÷¡£µ±È»ÊÇÓÃÖ÷¼üÖµµ±×ö Ö¸Õë»áÈöþ¼¶Ë÷ÒýÕ¼Óøü¶àµÄ¿Õ¼ä£¬Í¬Ê±±ÜÃâÁËÐгöÏÖÒÆ¶¯»òÕßÊý¾Ý·Öҳʱ¶þ¼¶Ë÷ÒýµÄά»¤¡£

¾Û´ØºÍ·Ç¾Û´Ø±íµÄ¶Ô±Èͼ

innodb ×îºÃÖ÷¼üÉèÖÃΪ×ÔÔöÀàÐÍ ÕûÊý£»

Ïò¾Û´ØË÷Òý²åÈë˳ÐòµÄË÷ÒýÖµ

Ïò¾Û´ØË÷ÒýÖвåÈëÎÞÐòµÄÖµ£º

ÕâÑùµÄȱµã:

дÈëµÄÄ¿±êÒ³¿ÉÄÜÒѾ­Ë¢Ðµ½´ÅÅÌÉϲ¢´Ó»º´æÖÐÒÆ³ý£¬»òÕß»¹Ã»ÓмÓÔØµ½»º´æÖУ¬ÕâÑùinnodbÔÚ²åÈëǰ²»µÃ²»ÏÈÕÒµ½²¢´Ó´ÅÅ̶ÁȡĿ±êÒ³µ½ÄÚ´æÖС£µ¼ÖÂÁË´óÁ¿µÄËæ»úI/O¡£

ÒòΪдÈëÊÇÂÒÐòµÄ£¬innodb²»µÃ²»Æµ·±µØ×öÒ³·ÖÁѲÙ×÷£¬ÒÔ±ãΪеÄÐзÖÅä¿Õ¼ä¡£Ò³·ÖÁѻᵼÖÂÒÆ¶¯´óÁ¿Êý¾Ý£¬Ò»´Î²åÈë×îÉÙÐèÒªÐÞ¸ÄÈý¸öÒ³¶ø²»ÊÇÒ»¸öÒ³¡£

ÓÉÓÚÆµ·±µÄÒ³·ÖÁÑ£¬Ò³»á±äµÃÏ¡Êè±»²»¹æÔòµØÌî³ä£¬ËùÒÔ×îÖÕÊý¾Ý»áÓÐË鯬¡£

6 ¸²¸ÇË÷Òý

¸²¸ÇË÷Òý£¬Ò»¸öË÷Òý°üº¬ËùÓÐÐèÒª²éѯµÄ×ֶεÄÖµ¡£

Óŵ㣺

Ë÷ÒýÌõĿͨ³£Ô¶Ð¡ÓÚÊý¾ÝÐдóС£¬ËùÒÔÈç¹ûÖ»ÐèÒª¶ÁÈ¡Ë÷Òý£¬ÄÇômysql¾Í»á¼«´óµØ¼õÉÙÊý¾Ý·ÃÎÊÁ¿¡£

ÒòΪË÷ÒýÊǰ´ÕÕÁÐֵ˳Ðò´æ´¢µÄ£¨ÖÁÉÙÔÚµ¥¸öÒ³ÄÚÊÇÈç´Ë£©£¬ËùÒÔ¶ÔÓÚI/OÃܼ¯Ð͵ķ¶Î§²éѯ»á±ÈËæ»ú´Ó´ÅÅ̶ÁȡÿһÐÐÊý¾ÝµÄI/OÒªÉٵöࡣ

һЩ´æ´¢ÒýÇæÈçMyisamÔÚÄÚ´æÖÐÖ»»º´æË÷Òý£¬Êý¾ÝÔòÒÀÀµÓÚ²Ù×÷ϵͳÀ´»º´æ£¬Òò´ËÒª·ÃÎÊÊý¾ÝÐèÒªÒ»´Îϵͳµ÷Óá£

ÓÉÓÚinnodbµÄ¾Û´ØË÷Òý£¬¸²¸ÇË÷Òý¶Ôinnodb±íÌØ±ðÓÐÓá£

ʹÓø²¸ÇË÷ÒýµÄÇé¿ö£º

mysql> explain select store_id,film_id from inventory \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: inventory
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 3496
Extra: Using index
1 row in set (0.00 sec)

´Ëʱ£¬ÓвéµÄ×Ö¶Îselect store_id,film_id£¬ÓÐÒ»¸ö¶àÁÐË÷Òýidx_store_id_film_id£¬´Ëʱ±ãʹÓõ½Á˸²¸ÇË÷Òý£¬²»»áÔÙ·µ»ØÊý¾Ý±íÈ¥ÕÒÊý¾ÝÐУ¬ÒòΪË÷ÒýÖÐÒѾ­°üº¬ÁË£»

¼ÙÉèË÷Òý¸²¸ÇÁËwhereÌõ¼þÖеÄ×ֶΣ¬µ«ÊÇÕû¸ö²éÑ¯Éæ¼°µÄ×ֶΣ¬À´¿´¿´»á·¢Ê²Ã´Çé¿ö£¬ÒÔ¼°ÈçºÎÖØÐ´²éѯÒÔ½â¾ö¸ÃÎÊÌâ¡£

×¢Ò⣺extraÁеÄ"using index" ºÍtypeÁеÄ"index"²»Í¬£¬typeÁк͸²¸ÇË÷ÒýºÁÎÞ¹ØÏµ£¬ËüÖ»ÊDZíʾÕâ¸ö²éѯ·ÃÎÊÊý¾ÝµÄ·½Ê½£¬»òÕß˵mysql²éÕÒÐеķ½Ê½¡£¶øextraÁеÄ"using index"Ôò˵Ã÷¡£Êý¾ÝʹÓÃÁË ¸²¸ÇË÷Òý£»

ÉÏÃæÀý×ÓÖУ¬Ê¹ÓÃÁËACTORË÷Òý£¬µ«ÊÇûÓÐʹÓø²¸ÇË÷ÒýÖ±½ÓµÃµ½Êý¾ÝÔ­Òò£º

ûÓÐÈκÎË÷ÒýÄܹ»¸²¸ÇÕâ¸ö²éѯ¡£

mysqlÄÜÔÚË÷ÒýÖÐ×î×óǰ׺ƥÅäµÄlike±È½ÏÈç"Apoll%",¶øÎÞ·¨×öͨÅä·û¿ªÍ·µÄlike Èç"%Apoll%"

Ò²Óа취¿ÉÒÔ½â¾öÉÏÃæËµµÄÎÊÌ⣬ʹÆäʹÓø²¸ÇË÷Òý¡£ÐèÒªÖØÐ´²éѯ²¢ÇÉÃîµØÉè¼ÆË÷Òý¡£ÏÈн¨Ò»¸öÈý¸öÁÐË÷Òý(actor,title,prod_id)£»È»ºóÖØÐ´²éѯ£º

ÎÒÃǰÑÕâÖÖ·½Ê½½Ð×öÑÓ³Ù¹ØÁª(defferred join),ÒòΪÑÓ³ÙÁ˶ÔÁеķÃÎÊ¡£

²éѯÔÚ×Ó²éѯÖÐʹÓÃÁ˸²¸ÇË÷Òý£¬²¢ÕÒµ½ÁËprod_id,È»ºó×öÁËÄÚÁ¬½Ó£¬Í¨¹ýprod_idÔÙÈ¥²éÆäËûÁÐ »á¿ìºÜ¶à¡£

µ±È»ÕâÒ»Çж¼Òª»ùÓÚ Êý¾Ý¼¯£¬¼ÙÉèÕâ¸öproducts±íÖÐÓÐ100ÍòÐУ¬ÎÒÃÇÀ´¿´Ò»ÏÂÉÏÃæÁ½¸ö²éѯÔÚÈý¸ö²»Í¬µÄÊý¾Ý¼¯ÉϵıíÏÖ£¬Ã¿¸öÊý¾Ý¼¯¶¼°üº¬100ÍòÐУº

µÚÒ»¸öÊý¾Ý¼¯£¬Sean Carrey ³öÑÝÁË30000²¿×÷Æ·£¬ÆäÖÐÓÐ20000²¿±êÌâ°üº¬ÁËApollo

µÚÒ»¸öÊý¾Ý¼¯£¬Sean Carrey ³öÑÝÁË30000²¿×÷Æ·£¬ÆäÖÐÓÐ40²¿±êÌâ°üº¬ÁËApollo

µÚÒ»¸öÊý¾Ý¼¯£¬Sean Carrey ³öÑÝÁË50²¿×÷Æ·£¬ÆäÖÐÓÐ10²¿±êÌâ°üº¬ÁËApollo

²âÊÔ½á¹û£º

½á¹û·ÖÎö£º

ÔÚµÚÒ»¸öÊý¾Ý¼¯ÖУº

Ô­²éѯ£º´ÓË÷ÒýactorÖжÁµ½30000ÌõÊý¾Ý£¬ÔÙ¸ù¾ÝµÃµ½µÄÖ÷¼üID»ØÊý¾Ý±íÖÐÔÙ¶Á30000ÌõÊý¾Ý£»×ܹ²¶ÁÈ¡60000Ìõ£»

ÓÅ»¯ºóµÄ²éѯ£ºÏÈ´ÓË÷Òýactor2ÖжÁµ½30000Ìõsena carrey£¬Ö®ºóÔÚËùÓÐSean Carrey ÖÐ×ölike ±È½Ï £¬ÕÒµ½20000Ìõprod_id£»Ö®ºó»¹ÊÇÒª»Øµ½Êý¾Ý±íÖУ¬¸ù¾Ýprod_idÔÙ¶ÁÈ¡20000Ìõ¼Ç¼£»×ܹ²¶ÁÈ¡50000Ìõ£»

·ÖÎö£º×ÜÊýËäÈ»ÉÙÁË17%£¬µ«ÊÇ×Ó²éѯÖеÄlike±È½Ï¿ªÏú»á±È½Ï´ó£¬ÏàµÖÖ®ºóЧÂʲ¢Ã»ÓÐʲôÌáÉý¡£

ÔÚµÚ¶þ¸öÊý¾Ý¼¯ÖУº

Ô­²éѯ£º´ÓË÷ÒýactorÖжÁµ½30000ÌõÊý¾Ý£¬ÔÙ¸ù¾ÝµÃµ½µÄÖ÷¼üID»ØÊý¾Ý±íÖÐÔÙ¶Á30000ÌõÊý¾Ý£»×ܹ²¶ÁÈ¡60000Ìõ£»

ÓÅ»¯ºóµÄ²éѯ£ºÏÈ´ÓË÷Òýactor2ÖжÁµ½30000Ìõsena carrey£¬Ö®ºóÔÚËùÓÐSean Carrey ÖÐ×ölike ±È½Ï £¬ÕÒµ½40Ìõprod_id£»Ö®ºó»¹ÊÇÒª»Øµ½Êý¾Ý±íÖУ¬¸ù¾Ýprod_idÔÙ¶ÁÈ¡40Ìõ¼Ç¼£»×ܹ²¶ÁÈ¡30040Ìõ£»

·ÖÎö£º¶ÁÈ¡×ÜÊý½µµÍÁË50%, Ïà±È×Ó²éѯÖеĿªÏú »¹ÊÇÖµµÃ£»

µÚÈý¸öÊý¾Ý¼¯£ºÏÔʾÁË×Ó²éѯЧÂÊ·´¶øÏ½µµÄÇé¿ö¡£ÒòΪË÷Òý¹ýÂËʱ·ûºÏµÚÒ»¸öÌõ¼þµÄ½á¹û¼¯ÒѾ­ºÜС£¬Ë÷Òý×Ó²éѯ´øÀ´µÄ³É±¾·´¶ø±È´Ó±íÖÐÖ±½ÓÌáÈ¡ÍêÕûÐиü¸ß¡£

7 ʹÓÃË÷ÒýɨÃèÀ´×öÅÅÐò

£¨¼´order by £¬group by ʹÓõ½ÁËË÷Òý£©

mysqlÉè¼ÆË÷ÒýʱӦ¸Ã¾¡Á¿Í¬Ê±Âú×ãÅÅÐò£¬ÓÐÓÖÓë²éÕÒÐС£

Ö»Óе±Ë÷ÒýµÄÁÐ˳ÐòºÍorder by×Ó¾äµÄ˳ÐòÍêȫһÖ£¬²¢ÇÒËùÓÐÁеÄÅÅÐò·½Ïò(µ¹Ðò»òÕýÐò)¶¼ÊÇÒ»Ñùʱ£¬mysql²ÅÄÜʹÓÃË÷ÒýÀ´¶Ô½á¹û×öÅÅÐò¡£

Èç¹û²éѯÐèÒª¹ØÁª¶àÕÅ±í£¬ÔòÖ»Óе±order by ×Ó¾äÒýÓõÄ×Ö¶ÎÈ«²¿ÎªÒ»¸ö±íʱ£¬²ÅÄÜʹÓÃË÷Òý×öÅÅÐò¡£

order by ×Ó¾äÂú×ã×î×óǰ׺µÄÒªÇ󣬻òÕß×î×óǰ׺Ϊ³£Êý£¬ÅÅÐò·½ÏòÒ²ÒªÒ»Ö£»

idx_a_b (a,b)

Äܹ»Ê¹ÓÃË÷Òý°ïÖúÅÅÐòµÄ²éѯ£º

order by a

Âú×ã×î×óǰ׺ҪÇó

a = 3 order by b

Âú×ã×î×óǰ׺Ϊ³£Êý

order by a,b

Âú×ã×î×óǰ׺ҪÇó

order by a desc,b desc

Âú×ã×î×óǰ׺ҪÇó

a>5 order by a£¬b

Âú×ã×î×óǰ׺ҪÇó

²»ÄÜʹÓÃË÷Òý°ïÖúÅÅÐòµÄ²éѯ

order by b

²»Âú×ã×î×óǰ׺ҪÇó

a >5 order by b

²»Âú×ã×î×óǰ׺£¬ÇÒ£¬×î×óǰ׺²»Êdz£Êý

a in (1,3) order by b

²»Âú×ã×î×óǰ׺£¬ÇÒ£¬×î×óǰ׺²»Êdz£Êý

oder by a asc ,b desc

ÅÅÐò·½Ïò²»Ò»ÖÂ

idx_a_b_c(a,b,c)

where a = 5 order by c

²»ÄÜʹÓÃË÷Òý½øÐÐÅÅÐò£¬²»ÄÜ¿çÔ½Ë÷ÒýÏî½øÐÐÅÅÐò£»Ò²ÊÇÒ»ÖÖ²»Âú×ã×î×óǰ׺µÄÇé¿ö£»

8 ѹËõ£¨Ç°×ºÑ¹Ëõ£©Ë÷Òý

myisamʹÓÃǰ׺ѹËõÀ´¼õÉÙË÷ÒýµÄ´óС£¬´Ó¶øÈøü¶àµÄË÷Òý¿ÉÒÔ·ÅÈëÄڴ棬ÕâÔÚijЩÇé¿öÏÂÄܼ«´óµØÌáÉýÐÔÄÜ¡£Ä¬ÈÏֻѹËõ×Ö·û´®£¬µ«Í¨¹ý²ÎÊýÉèÖÃÒ²¿ÉÒÔ¶ÔÕûÊýѹËõ¡£

9 ÈßÓàºÍÖØ¸´Ë÷Òý

mysqlÔÊÐíÔÚÏàͬÁÐÉÏ´´½¨¶à¸öË÷Òý£¬µ«ÐèÒªµ¥¶Àά»¤Öظ´µÄË÷Òý£¬²¢ÇÒÓÅ»¯Æ÷ÔÚÓÅ»¯²éѯµÄʱºòÒ²ÐèÒªÖð¸ö¿¼ÂÇ£¬Õâ»áÓ°ÏìÐÔÄÜ¡£

ÖØ¸´Ë÷Òý£º

ʵ¼ÊÉÏÔÚIDÉϽ¨ÁËÈý¸öË÷Òý£¬Õâ¾ÍÊÇÖØ¸´Ë÷Òý¡£

ÈßÓàË÷Òý£º

ÒÑÓÐË÷Òý(A,B),ÔÙÖØ½¨Ë÷Òý(A)¾ÍÊÇÈßÓàË÷Òý£»

¶ø´Ëʱ(B,A)£¬Ôò²»ÊÇÈßÓàË÷Òý¡£Ë÷Òý(B)Ò²²»ÊÇË÷Òý(A,B)µÄÈßÓàË÷Òý£»

ÒÑÓÐË÷Òý(A),ÔÙ½¨Ë÷Òý(A,ID),ÆäÖÐIDÊÇÖ÷¼ü£¬¶ÔinnodbÀ´ËµÖ÷¼üÁÐÒѾ­°üº¬ÔÚ¶þ¼¶Ë÷ÒýÖÐÁË£¬ËùÒÔÕâÒ²ÊÇÈßÓàË÷Òý£»

´ó¶àÊýÇé¿ö¶¼²»ÐèÈßÓàË÷Òý£¬Ó¦¸Ã¾¡Á¿À©Õ¹ÒÑÓеÄË÷Òý¶ø²»ÊÇ´´½¨ÐÂË÷Òý¡£

µ±È»ÓÐʱºòÒ²ÊÇÐèÒªÈßÓàË÷ÒýµÄ£¬ÒòΪÀ©Õ¹ÒÑÓеÄË÷Òý»áµ¼ÖÂÆä±äµÃÌ«´ó£¬´Ó¶øÓ°ÏìÆäËûʹÓøÃË÷ÒýµÄ²éѯµÄÐÔÄÜ¡£

´´½¨Ë÷Òý

µ¥ÁÐË÷Òý

create index idx_test1 on tb_student(name);

ÁªºÏË÷Òý

create index idx_test2 on tb_student(name,age)

Ë÷ÒýÖÐÏȸù¾ÝnameÅÅÐò£¬nameÏàͬµÄÇé¿öÏ£¬¸ù¾ÝageÅÅÐò

Éè¼ÆË÷ÒýÔ­Ôò£º

ËÑË÷µÄË÷ÒýÁС£

²»Ò»¶¨ÊÇËùҪѡÔñµÄÁУ»¼´where ºóÃæµÄ²éѯÌõ¼þ¼ÓË÷Òý£¬¶ø²»ÊÇselect ºóÃæµÄÑ¡ÔñÁÐ

ʹÓÃΨһË÷Òý¡£

ʹÓöÌË÷Òý¡£

Èç¹û¶Ô×Ö·û´®ÁнøÐÐË÷Òý£¬Ó¦¸ÃÖ¸¶¨Ò»¸öǰ׺³¤¶È£¬Ö»ÒªÓпÉÄܾÍÓ¦¸ÃÕâÑù×ö¡£

ÀûÓÃ×î×óǰ׺¡£

²»Òª¹ý¶ÈË÷Òý

innodb±í£¬Ö¸¶¨Ö÷¼ü£¬²¢ÇÒÊÇ×ÔÔöµÄ×îºÃ£»

BTREEË÷ÒýºÍHASHË÷Òý£º

¶¼¿ÉÒÔÓÃÔÚ£¬where col=1 or col in (15,18,20),ÕâÑùµÄ¶¨Öµ²éѯÖУ»

¶øÔÚ·¶Î§²éѯÖУ¬where col>1 and col<10 »òÕß col like 'ab%' or col between 'lisa' and 'simon';´ËʱֻÓÐBTREEË÷ÒýÄÜʹÓã»HASHË÷ÒýÔÚÕâÖÖÇé¿öÖУ¬²»»á±»Ê¹Óõ½£¬»á¶ÔÈ«±í½øÐÐɨÃ裻

ά»¤Ë÷ÒýÓë±í

ά»¤Ë÷ÒýºÍ±í

ά»¤±íÓÐÈý¸öÖ÷ҪĿµÄ£º

ÕÒµ½²¢ÐÞ¸´Ë𻵵ıí

ά»¤×¼È·µÄË÷Òýͳ¼ÆÐÅÏ¢

¼õÉÙË鯬

ÕÒµ½²¢ÐÞ¸´Ë𻵵ıí

check table tb_name:¼ì²éÊÇ·ñ·¢ÉúÁ˱íËð»µ

repair table tb_name:

¸üÐÂË÷Òýͳ¼ÆÐÅÏ¢

mysqlÓÅ»¯Æ÷ͨ¹ýÁ½¸öAPIÀ´ÁË½â´æ´¢ÒýÇæµÄË÷ÒýÖµµÄ·Ö²¼ÐÅÏ¢£¬ÒÔ¾ö¶¨ÈçºÎʹÓÃË÷Òý¡£

records_in_range():ͨ¹ýÏò´æ´¢ÒýÇæ´«ÈëÁ½¸ö±ß½çÖµ»ñÈ¡ÔÚÕâ¸ö·¶Î§´ó¸ÅÓжàÉÙÌõ¼Ç¼¡£

info()£º¸Ã½Ó¿Ú·µ»Ø¸÷ÖÖÀàÐ͵ÄÊý¾Ý£¬°üÀ¨Ë÷ÒýµÄ»ùÊý£¨Ã¿¸ö¼üÖµÓжàÉÙÌõ¼Ç¼£©

mysqlÓÅ»¯Æ÷ʹÓõÄÊÇ»ùÓڳɱ¾µÄÄ£ÐÍ£¬¶øºâÁ¿³É±¾µÄÖ÷ÒªÖ¸±ê¾ÍÊÇÒ»¸ö²éѯÐèҪɨÃè¶àÉÙÐС£Èç¹û±íûÓÐͳ¼ÆÐÅÏ¢£¬»òÕßͳ¼ÆÐÅÏ¢²»×¼È·£¬ÓÅ»¯Æ÷¾ÍºÜ¿ÉÄÜ×ö³ö´íÎóµÄ¾ö¶¨¡£

analyze table £ºÖØÐÂÉú³Éͳ¼ÆÐÅÏ¢£»

mysql> show index from actor\G;
*************************** 1. row ***************************
Table: actor
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: actor_id
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: actor
Non_unique: 1
Key_name: idx_actor_last_name
Seq_in_index: 1
Column_name: last_name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

Cardinality£¬ÏÔʾÁË´æ´¢ÒýÇæ¹ÀËãË÷ÒýÁÐÓжàÉÙ¸ö²»Í¬µÄȡֵ¡£

mysql5.6 ÒÔºó¿ÉÒÔͨ¹ý²ÎÊýinnodb_analyze_is_persistent£¬À´¿ØÖÆanalyze ÊÇ·ñÆô¶¯£»
¼õÉÙË÷ÒýºÍÊý¾ÝµÄË鯬

Êý¾ÝË鯬ÈýÖÖÀàÐÍ£º
ÐÐË鯬(row fragmentation)
Êý¾ÝÐб»´æ´¢Îª¶à¸öµØ·½µÄ¶à¸öƬ¶ÎÖС£
ÐмäË鯬(Intra-row fragmentation)
Âß¼­ÉÏ˳ÐòµÄÒ³£¬ÔÚ´ÅÅÌÉϲ»ÊÇ˳Ðò´æ´¢µÄ¡£
Ê£Óà¿Õ¼äË鯬(Free space fragmentation)
Êý¾ÝÒ³ÖÐÓдóÁ¿µÄ¿ÕÓà¿Õ¼ä¡£

ʹÓÃÃüÁ
optimize table tb_name£¬ÇåÀíË鯬¡£

mysql> OPTIMIZE TABLE actor;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+-------------------------------------------------------------------+
| sakila.actor | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| sakila.actor | optimize | status | OK |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)

¶ÔÓÚ²»Ö§³Ö¸ÃÃüÁîµÄÒýÇæ¿ÉÒÔͨ¹ýÒ»¸ö²»×öÈκβÙ×÷£¨no-op£©µÄalter table ²Ù×÷À´Öؽ¨±í¡£

mysql> alter table actor engine=innodb;
Query OK, 200 rows affected (0.02 sec)
Records: 200 Duplicates: 0 Warnings: 0

Ë÷ÒýÏîµÄÖµ·¢Éú¸Ä±ä£¬´ËʱË÷ÒýÏîÔÚË÷Òý±íÖеÄλÖ㬾ÍÐèÒª·¢Éú¸Ä±ä£¬ÕâÑùÒ»¸öÐÐΪ³ÆÎªË÷Òýά»¤£»

ÒòΪÈç¹û²»½øÐÐË÷Òýά»¤µÄ»°£¬¾ÍÊÇ˵Ë÷ÒýÏîµÄÖµ¸Ä±äºó£¬²¢Ã»ÓÐÖØÐÂÅÅÐò£¬ÕâÑù¸Ä±äÏî¶àÁËÖ®ºó£¬¾Í²»ÊÇÒ»¸ö˳ÐòÅÅÐòÁË£¬¾ÍÆð²»µ½Ë÷ÒýµÄЧ¹ûÁË£»

Ë÷Òýά»¤ÓÉÊý¾Ý¿â×Ô¶¯Íê³É

²åÈë/ÐÞ¸Ä/ɾ³ýÿһ¸öË÷ÒýÐж¼±ä³ÉÒ»¸öÄÚ²¿·â×°µÄÊÂÎñ

Ë÷ÒýÔ½¶à£¬ÊÂÎñÔ½³¤£¬´ú¼ÛÔ½¸ß

Ë÷ÒýÔ½¶à¶Ô±íµÄ²åÈëºÍË÷Òý×Ö¶ÎÐ޸ľÍÔ½Âý

¼ÙÉèÒ»¸ö±í´øÁËÁ½¸öË÷Òý£»

ÄÇôϵͳ»á×ܹ²´´½¨3ÕÅ±í£¬Ò»¸öÊý¾Ý±í£¬Á½¸öË÷Òý±í£»

ÔÚÐÞ¸ÄÒ»¸öË÷ÒýÏîÊý¾ÝµÄʱºò£¬»áÄÚ²¿·â×°³ÉÒ»¸öÊÂÎñ£¬Í¬Ê±ÕâÈýÕÅ±í½øÐÐÐ޸ģ»

ʹÓÃË÷Òý

1.ʹÓÃWHERE²éѯÌõ¼þ½¨Á¢Ë÷Òý

select a,b from tab where c=?;

idx_c (c)

select a,b from tab where c=? and d=?

idx_cd(c,d)

2.ÅÅÐòORDER BY,GROUP BY,DISTINCT ×Ö¶ÎÌí¼ÓË÷Òý

3.ÁªºÏË÷ÒýÓëǰ׺²éѯ

ÁªºÏË÷ÒýÄÜΪǰ׺µ¥ÁУ¬¸´ÁвéѯÌṩ°ïÖú

ÔÚmysql5.6ǰ£¬where a? and c? Ö»Äܲ¿·Ö

ºÏÀí´´½¨ÁªºÏË÷Òý£¬±ÜÃâÈßÓà

(a),(a,b),(a,b,c)

ÆäʵֻÐèÒª¶Ô(a,b,c)½¨Á¢Ë÷Òý¼´¿É£»

Ë÷ÒýÓë×Ö¶ÎÑ¡ÔñÐÔ

ij¸ö×Ö¶ÎÆäÖµµÄÖØ¸´³Ì¶È£¬³ÆÎª¸Ã×ֶεÄÑ¡ÔñÐÔ£»

Ñ¡ÔñÐԺܲîµÄ×Ö¶Îͨ³£²»Êʺϴ´½¨µ¥ÁÐË÷Òý

ÄÐÅ®±ÈÀýÏà·ÂµÄ±íÖÐÐÔ±ð²»Êʺϴ´½¨µ¥ÁÐË÷Òý

Èç¹ûÄÐÅ®±ÈÀý¼«²»Æ½ºâ£¬Òª²éѯµÄÓÖÊÇÉÙÊý·½£¨Àí¹¤ÔºÐ£²éÅ®Éú£©¿ÉÒÔ¿¼ÂÇʹÓÃË÷Òý

ÁªºÏË÷ÒýÖÐÑ¡ÔñÐԺõÄ×Ö¶ÎÓ¦¸ÃÅÅÔÚÇ°Ãæ

³¤×ֶεÄË÷Òý

Ôڷdz£³¤µÄ×Ö¶ÎÉϽ¨Á¢Ë÷ÒýÓ°ÏìÐÔÄÜ

innodbË÷Òýµ¥×ֶΣ¨utf8£©Ö»ÄÜȡǰ767bytes

¶Ô³¤×ֶδ¦ÀíµÄ·½·¨

email À࣬½¨Á¢Ç°×ºË÷Òý

Mail_addr varchar(2048)

idx_mailadd (Mail_addr(30))----Ö»±£´æÇ°30¸ö×Ö·ûΪË÷Òý

mysqlÔÊÐí¶Ô×ֶνøÐÐǰ׺Ë÷Òý

¶Ô³¤×Ö¶ÎÎÒÃÇÒ²¿ÉÒÔÖ÷¶¯Ö»È¡×ֶεÄǰ°ë²¿·Ö£»

סַÀ࣬·Ö²ð×Ö¶Î

Home_address varchar(2048)

idx_Homeadd (Home_addr(30)) ???? -×öǰ׺Ë÷ÒýºÜ¿ÉÄÜÐв»Í¨µÄ£¬ÒòΪºÜ¿ÉÄÜǰ°ë¶Î¶¼ÊÇÏàͬµÄÊ¡ÊÐÇø½ÖµÀÃû³Æ

·½·¨£º·Ö²ð×Ö¶Î

Province varchar(1024), City varchar(1024),District varchar£¨1024£©£¬Local _ address varchar (1024)

È»ºó½¨Á¢ÁªºÏË÷Òý»òµ¥ÁÐË÷Òý£»

Ë÷Òý¸²¸ÇɨÃè(Ö±½ÓʹÓÃË÷ÒýÖеÄÊý¾Ý£¬²»ÐèÒª´ÓÊý¾Ý±íÖзµ»ØÊý¾Ý)

×îºËÐÄSQL¿¼ÂÇË÷Òý¸²¸Ç

select name from tb_user where UserId=?

Key idx_uid_name(userid,name)

²»ÐèÒª»Ø±í»ñÈ¡name×ֶΣ¬IO×îÉÙ£¬Ð§ÂÊ×î¸ß£»

ÎÞ·¨Ê¹ÓÃË÷Òý

Ë÷ÒýÁнøÐÐÊýѧÔËËã»òº¯ÊýÔËËã

where id+1 = 10 ¡Á

where id = (10-1) ¡Ì

year(col) < 2007 ¡Á

col < '2007-01-01'¡Ì

맪¸´ºÏË÷ÒýµÄǰ׺×Ö¶Î

idx_abc (a,b,c):

where b=? and c=? ¡Á

idx_bc(b,c) ¡Ì

×¢Ò⣺idx_adb £¨a,b,c£©°üº¬ idx_a (a),°üº¬idx_ab(a,b),ÔÚ5.6Ö®ºó»¹°üº¬idx_ac£¨a,c£©

ǰ׺ͨÅä¡®_¡¯ ºÍ¡®%¡¯Í¨Åä·û

LIKE '%XXX%' ¡Á

LIKE 'XXX%' ¡Ì

µ±Ê¹Óõ½ like'%xx%'ʱ£¬ÎÞ·¨Ê¹ÓÃË÷Òý£¬½â¾ö°ì·¨ÊÇ£¬Ê¹ÓÃÈ«ÎÄË÷ÒýÔÚ5.6Ö®ºó¡£»òÕߣ¬Ê¹ÓÃÁ¬½Ó ÄÚ²ãɨÃè È«Ë÷Òý±í£¬Ö®ºóÕÒµ½·ûºÏÌõ¼þµÄ£¬Ôٻص½±íÖÐ ²éÕÒ ¼Ç¼£¬ÕâÑù¿ÉÒÔ½µµÍIOÏûºÄ£¬ÒòΪ Ò»°ãÀ´½² Ë÷Òý±í ±È½ÏС£¬È«É¨Ë÷Òý±íµÄ»°Ïà¶Ô¿ªÏú ±È ȫɨÊý¾Ý±í£¬ÒªÐ¡ºÜ¶à£»

ÓÃOR·Ö¸î¿ªµÄÌõ¼þ£¬Èç¹ûorǰµÄÌõ¼þÖеÄÁÐÓÐË÷Òý£¬¶øºóÃæµÄÁÐÖÐûÓÐË÷Òý£¬ÄÇôËùÉæ¼°µÄË÷Òý¶¼²»»á±»Óõ½¡£ÒòΪºóÃæµÄ²éѯ¿Ï¶¨Òª×ßÈ«±íɨÃ裬ÔÚ´æÔÚÈ«±íɨÃèµÄÇé¿öÏ£¬¾ÍûÓбØÒª¶àÒ»´ÎË÷ÒýɨÃèÔö¼ÓI/O·ÃÎÊ£¬Ò»´ÎÈ«±íɨÃè¹ýÂËÌõ¼þ¾Í×ã¹»ÁË¡£

whereÌõ¼þʹÓÃNOT,<>,!=

×Ö¶ÎÀàÐÍÆ¥Åä

²¢²»¾ø¶Ô£¬µ«ÊÇÎÞ·¨Ô¤²âµØ»áÔì³ÉÎÊÌ⣬²»ÒªÊ¹Óã»

Àý×Ó£ºa int(11) , idx_a (a)£»

where a = '123' ¡Á

where a = 123 ¡Ì

ÓÉÓÚÀàÐͲ»Í¬£¬mysqlÐèÒª×öÒþʽÀàÐÍת»»²ÅÄܽøÐбȽϡ£

×¢Òâ×ֶεÄÀàÐÍ£¬ÓÈÆäÊÇintÐÍʱÈç¹ûʹÓÃ×Ö·ûÐÍȥƥÅ䣬Äܵõ½ÕýÈ·½á¹û£¬¶ø²»»áʹÓÃË÷Òý£»Í¬ÑùÈç¹û×Ö¶ÎÊÇ£¬varcharÐÍ£¬ÄÇôwhere ºóÃæÈç¹ûÊÇÒ»¸ö INT£¬Ò²ÊDz»ÄÜʹÓÃË÷Òý£»

mysql±È½Ïת»»¹æÔò£º

Á½¸ö²ÎÊýÖÁÉÙÒ»¸öÊÇnullÊDz»ÐèҪת»»£»

Á½¸ö²ÎÊýÀàÐÍÒ»Ñùʱ²»ÐèҪת»»£»

TIMESTAMP/DATATIME ºÍ ³£Á¿ ±È½Ï-->³£Á¿×ª»»Îªtimestamp/datetime

decimalºÍÕûÊý±È½Ï---------------------->ÕûÊýת»»Îªdecimal

decimalºÍ¸¡µãÊý------------------------->decimalת»»Îª¸¡µãÊý

Á½¸ö²ÎÊý¶¼»á±»×ª»»Îª¸¡µãÊýÔÙ½øÐбȽϣº

Èç¹û×Ö·û´®ÐÍ£¬±È½Ï£¬=£¬+£¬-£¬µÈ£»

Ò»¸ö×Ö·û´®ºÍÒ»¸öÕûÐÎ-------------------->¾ùת»»³É¸¡µãÐÍ

mysql> select '18015376320243459'=18015376320243459;
+---------------------------------------+
| '18015376320243459'=18015376320243459 |
+---------------------------------------+
| 1 |

mysql> select '1801'+0;
+----------+
| '1801'+0 |
+----------+
| 1801 |
+----------+

Èç¹û age int(10), index_age(age);

mysql> explain select name from indextest where age='30'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: idx_age
key: idx_age
key_len: 1
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)

Ë÷ÒýÊÇÊýÖµ£»²éѯÌõ¼þÊÇ×Ö·û´®'30',ת»»³ÉÈ·¶¨ÊýÖµ30£¬×ª»»Ê¹ÓÃË÷Òý£»

Ë÷Òýʱ×Ö·û´®£¬²éѯÊÇÊýֵʱ£¬ÎÞ·¨Ê¹ÓÃË÷Òý£»

ʹÓÃcastº¯Êý¶Ôage×öÏÔʾµÄÀàÐÍת»»£¬»áʹË÷ÒýÏûʧ£»

¼´¶ÔË÷ÒýÏî×öÈκεĺ¯Êý¸Ä±ä£¬¶¼»áʹË÷ÒýʧЧ£»

×ܽá

BTREE

´æ´¢Ë÷ÒýÏîÓëÖ÷¼ü

BTREEË÷Òý¿ÉÓÃÔÚ¶¨Öµ²éѯ£¬·¶Î§²éѯ£¬

HASH

´æ´¢¹þÏ£ÖµÓëÐÐÖ¸Õë

½öÓÃÓÚ¶¨Öµ²éѯ£¬´´½¨Î±¹þÏ£Ë÷Òý£»

ǰ׺µÄÑ¡ÔñÐÔ¼ÆËã£¨È¥ÖØÇ°×ºÊý³ý×ÜÊý£©

mysql> select count(DISTINCT city)/count(*) from table_name

Ë÷ÒýºÏ²¢£¨index merge£©£ºËµÃ÷´Ëʱ±íÉÏË÷Òý£¬±í½á¹¹µÈÐèÒªÓÅ»¯ÁË£»

Ñ¡ÔñºÏÊʵÄË÷ÒýÁÐ˳Ðò£ºÐèÒª¸ù¾Ý±íÖÐʵ¼ÊÊý¾Ý½øÐÐÑ¡Ôñ£¬Ñ¡ÔñÐԸߵķÅÔÚǰ£»

¾Û´ØË÷Òý£ºinnodbµÄ¾Û´ØË÷Òýʵ¼ÊÉÏÔÚͬһ½á¹¹Öб£´æÁËBTreeË÷ÒýºÍÊý¾ÝÐÐ

myisamµÄÊý¾Ý·Ö²¼

myisam°´ÕÕÊý¾Ý²åÈëµÄ˳Ðò´æ´¢ÔÚ´ÅÅÌÉÏ

Ö÷¼üË÷Òýʱ£¬×Ô¶¯Ôö¼ÓÐкţ¬±íµÄÖ÷¼üºÍÐкÅÔÚÒ¶×Ó½ÚµãÖУ¬ÇÒÒ¶×Ó½Úµã¸ù¾ÝÖ÷¼ü˳ÐòÅÅÁУ»

ÆäËûÁÐË÷ÒýºÍÖ÷¼üË÷ÒýÎÞÇø±ð£»

innodbÊý¾Ý·Ö²¼:

ʹÓþ۴ØË÷Òý£»

¶þ¼¶Ë÷Òý°üº¬Ë÷ÒýÏîºÍÖ÷¼üÖµ

¸²¸ÇË÷Òý£º

extraÖÐusing index£»

ÑÓ³Ù¹ØÁª(defferred join)£»

µ±È»¸²¸ÇË÷Òý²¢²»ÊǶ¼ÄÜÌáÉýÐÔÄÜ£¬ÐèÒª¸ù¾Ý¼¯ÌåÊý¾Ý¼¯£»

ʹÓÃË÷Òý½øÐÐÅÅÐò£¬²»ÄÜ¿çÔ½Ë÷ÒýÏî½øÐÐÅÅÐò£»

Ë÷Òýά»¤£ºÓÉÊý¾Ý¿â×Ô¶¯Íê³É£¬½«DML·â×°³ÉÄÚ²¿ÊÂÎñ£¬Ë÷ÒýÔ½¶à´ú¼ÛÔ½¸ß£¬

¸üÐÂË÷Òýͳ¼ÆÐÅÏ¢£º

records_in_range()»ñÈ¡·¶Î§ÖÐÓжàÉÙ¼üÖµ£¬

info()»ñÈ¡Ë÷Òý»ùÊý

ÇåÀíË鯬£º

optimize table tbl,

alter table tbl engine=innodb;

ʹÓÃË÷Òý

where

order by ¡¢group by¡¢distinct,

ÁªºÏË÷Òý£º×¢ÒâÈßÓ࣬ѡÔñÐԺõķÅÔÚÁªºÏË÷Òý×ó²à£»

³¤×ֶεÄË÷Òý£º

½¨Á¢Ç°×ºË÷Òý

·Ö²ð×ֶν¨Á¢ÁªºÏË÷Òý£¬

ÎÞ·¨Ê¹ÓÃË÷Òý£º

Ë÷ÒýÁнøÐÐÊýѧÔËËã»òº¯ÊýÔËËã

δ×ñÊØ×î×óǰ׺ԭÔò

orÌõ¼þºóÒ»ÁÐûÓÐË÷Òý

whereÌõ¼þʹÓÃnot <> !=

×Ö¶ÎÀàÐͲ»Æ¥Å䣻

 

   
2296 ´Îä¯ÀÀ       27
Ïà¹ØÎÄÕÂ

»ùÓÚEAµÄÊý¾Ý¿â½¨Ä£
Êý¾ÝÁ÷½¨Ä££¨EAÖ¸ÄÏ£©
¡°Êý¾Ýºþ¡±£º¸ÅÄî¡¢ÌØÕ÷¡¢¼Ü¹¹Óë°¸Àý
ÔÚÏßÉ̳ÇÊý¾Ý¿âϵͳÉè¼Æ ˼·+Ч¹û
 
Ïà¹ØÎĵµ

GreenplumÊý¾Ý¿â»ù´¡Åàѵ
MySQL5.1ÐÔÄÜÓÅ»¯·½°¸
ijµçÉÌÊý¾ÝÖÐ̨¼Ü¹¹Êµ¼ù
MySQL¸ßÀ©Õ¹¼Ü¹¹Éè¼Æ
Ïà¹Ø¿Î³Ì

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ