±à¼ÍƼö: |
±¾ÎÄÀ´×ÔÓÚ²©¿ÍÔ°£¬±¾ÎÄÖ÷Ҫͨ¹ý¾ÙÀý˵Ã÷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 <> !=
×Ö¶ÎÀàÐͲ»Æ¥Å䣻
|