Â
1.  索å¼
1.1.  äºåç´¢å¼
B+treeæ¯ç±äºåæ ã平衡äºåæ ãB-treeæ¼åèæ¥ã
äºåæ æ¯ä¸ªèç¹æå¤ä¸¤ä¸ªåèç¹ï¼å·¦åæ é®å¼æ°¸è¿å°äºå³åæ ï¼å¹¶å°äºæ ¹é®å¼ã
1.2.  平衡äºåæ ç»æ
平衡äºåæ å¨äºåæ ç»æåºç¡ä¸æé«ï¼å¿ 须满足左å³ä¸¤ä¸ªåæ çé«åº¦å·®çç»å¯¹å¼ä¸è¶ è¿1ï¼ä¸å·¦åæ åå³åæ é½æ¯ä¸é¢å¹³è¡¡äºåæ ï¼ï¼éæ¶è¦ä¿è¯æå ¥åçæ´æ£µäºåæ æ¯å¹³è¡¡çï¼ééå·¦ææå³æ使ä¸å¹³è¡¡çæ å平衡ã
1.3.  B-treeç»æ
B-treeå称Btreeï¼æ¯ä¸ªèç¹æå¤4个åèç¹ï¼é¤äºæ ¹èç¹åå¶åèç¹ï¼å ¶ä»èç¹æå°2个åèç¹ãææå¶åèç¹å¨åä¸å±ï¼å¶åèç¹ä¸å æ¬ä»»ä½å ³é®åä¿¡æ¯ã
1.4. Â B+tree
B+tree使Btreeçåä½ï¼æ¯ä¸ç§å¤è·¯æç´¢æ ï¼ææå ³é®ååæ°æ®é½ä¿åå¨å¶åèç¹ä¸ï¼å¹¶ä¸å å«å ³é®åè®°å½çæéã
æ»ç»ï¼B+treeç´¢å¼æ¯ååé¾è¡¨ç»æï¼æ£ç´¢æ¯B-treeå¿«ï¼è®¿é®å ³é®åç顺åºæ¯è¿ç»æ§çï¼ä¸ç¨å访é®ä¸ä¸ä¸ªèç¹ï¼ä¸å¶åèç¹å å«ææçæ°æ®ä¿¡æ¯ã
1.4.1.  èéç´¢å¼åæ®éç´¢å¼
B+treeå为两大类ï¼ä¸ç±»å«èéç´¢å¼ï¼ä¸ç±»å«éèéç´¢å¼ï¼æ®éç´¢å¼ï¼ã
InnoDBåå¨å¼ææ¯ç´¢å¼ç»ç»è¡¨ï¼èéç´¢å¼æ¯ä¸ç§ç´¢å¼ç»ç»è¡¨å½¢å¼ï¼ç´¢å¼é®å¼çé»è¾é¡ºåºå³å®äºè¡¨æ°æ®è¡çç©çåå¨é¡ºåºã
èéç´¢å¼å¶åèç¹åæ¾è¡¨ä¸ææè¡æ°æ®è®°å½çä¿¡æ¯ï¼å³æ°æ®å³ç´¢å¼ãç´¢å¼å³æ°æ®ãå建表æ¶å»ºä¸»é®ï¼èéç´¢å¼ï¼ï¼å¦ä¸å»ºä¸»é®åInnoDBä¼éæ©ç¬¬ä¸ä¸ªä¸å å«ç±Nullå¼å¾å¯ä¸ç´¢å¼ä½ä¸ºä¸»é®ï¼å¦æå¯ä¸ç´¢å¼æ²¡æï¼åé»è®¤ä¸ºè¯¥è¡¨çæä¸ä¸ª6åèå¾rowid为主é®ã
æ®éç´¢å¼å¨å¶åèç¹ä¸å å«ææè¡å¾æ°æ®è®°å½ï¼åªå¨å¶åèç¹åæèªå·±æ¬èº«é®å¼å主é®å¾å¼ãæ£ç´¢æ°æ®ï¼éè¿æ®éç´¢å¼å¶åèç¹ä¸ä¸»é®æ¥è·åæ³è¦æ¥æ¾çè¡æ°æ®è®°å½ã
                      Â
æ®éç´¢å建è¯æ³ï¼
alter table tab_name add index index_name(col1);
æï¼
create index inde_name on tab_name(col1);
æ¥ç表ä¸æåªäºç´¢å¼ï¼
show index from tab_name;
Â
ç´¢å¼å建å®éª
l  å建æµè¯åº
mysql> create database test;
l  å建æµè¯è¡¨
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `address` varchar(20) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Â
l  æ¥ç表ç»æ
[test]>desc t;
+---------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra         |
+---------+-------------+------+-----+---------+----------------+
| id     | int(11)    | NO  | PRI | NULL   | auto_increment |
| name   | varchar(20) | NO  |    | NULL   |               |
| address | varchar(20) | NOÂ Â |Â Â Â Â | NULLÂ Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |
+---------+-------------+------+-----+---------+----------------+
Â
l  å建åå¨è¿ç¨
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_auto_insertdata`$$
CREATE PROCEDURE `proc_auto_insertdata`()
BEGIN
       DECLARE init_data INTEGER DEFAULT 1;
       WHILE init_data <= 60000 DO
       INSERT INTO test.t VALUES(CONCAT('name', init_data), init_data + 10);
       SET init_data = init_data + 1;
       END WHILE;
END$$
DELIMITER ;
Â
l  è°ç¨åå¨è¿ç¨æå ¥æ°æ®
CALL proc_auto_insertdata();
æ°æ®æå ¥å®æï¼çæ°æ®æ件10M
Â
l  æ¥çæ§è¡è®¡å
test> explain select * from t where name='name11';
l  å建索å¼
create index idx_tname on t(name);
l  å次æ¥çæ§è¡è®¡å
Â
ä¼åæ¹æ³
l  æ§è¡è®¡åæ¥çæ¹æ³ï¼
1.     çæ¥è¯¢ç±»åtypeï¼å¦åºç°allï¼ä»£è¡¨å ¨è¡¨æ«æï¼
2.     çkeyåï¼çæ¯å¦ä½¿ç¨l ç´¢å¼ãnull表示没æ使ç¨ç´¢å¼ï¼
3.     çrowsåï¼SQLæ§è¡è¿ç¨ä¸è¢«æ«æçè¡æ°ï¼
4.     çextraåï¼è§å¯æ¯å¦æUsing filesortæUsing temporaryï¼è¿äºå½±åæ§è½ã
5.     çfilteredåï¼ï¼5.7å¢å ï¼5.6ç¨explain extendedå¢å æ¤åï¼ï¼ä»£è¡¨è¿åç»æçè¡å éè¦è¯»åè¡çç¾åæ¯ã
Â
l  SQLä¼åæè·¯ï¼
1.     æ¥ç表çæ°æ®ç±»åæ¯å¦è®¾è®¡çåçï¼æ¯å¦éµå®éåºæ°æ®ç±»åè¶ç®åè¶å°çååã
2.     表ä¸ç¢çæ¯å¦æ´çã
3.     表çç»è®¡ä¿¡æ¯æ¯å¦æ¶éã
4.     æ¥çæ§è¡è®¡åå¦æ²¡ç¨å°ç´¢å¼ï¼éå建ã
5.     å建索å¼åï¼æ¥çç´¢å¼çéæ©æ§ï¼å¤æå段æ¯å¦åéå建索å¼ãéæ©æ§æä¸éå¤çç´¢å¼å¼ï¼åºæ°ï¼cardinalityï¼åè®°å½æ»æ°çæ¯å¼ï¼æ¯å¼è¶é«è¶å¥½ã
6.     å建索å¼åï¼åçæ§è¡è®¡åï¼æ¯å¯¹ååã
Â
l  åçå建索å¼ï¼
1.     ç»å¸¸è¢«æ¥è¯¢çåã
2.     ç»å¸¸ç¨äºè¡¨è¿æ¥çåã
3.     ç»å¸¸æåºåç»çç±»ã
Â
1.4.2. Â ICPãMRRåBKA
ICP(Index Condition Pushdown) æ¯mysql使ç¨ç´¢å¼ä»è¡¨éæ£ç´¢è¡æ°æ®çä¸ç§ä¼åæ¹å¼ã5.6å¼å§æ¯æãä¹ååå¨å¼æåæææ°æ®ç»server使ç¨ç´¢å¼è¿æ»¤å¤çã使ç¨ICPä¹åï¼å¯ä»¥ä½¿ç¨ç´¢å¼çè¯ï¼åå¨å¼æè¿æ»¤å®æ°æ®åç»serverå±ãICPè½åå°å¼æå±è®¿é®åºè¡¨ç次æ°åserverå±è®¿é®åå¨å¼æç次æ°ã
éè¿optimizer_switchåæ°ä¸çindex_condition_pushdowæ¥æ§å¶ï¼é»è®¤å¼å¯ã
[mysql]>show variables like '%pushdown%';
å ³éï¼
set optimizer_switch=âindex_condition_pushdown=on|offâ;
使ç¨ICPä¼åæ¶ï¼æ§è¡è®¡åextraåä¼æ¾ç¤ºUsing index conditionã
5.7ä¸optimizer_switchåæ°é»è®¤å¼ï¼
|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
Â
MBR(Multi-Range Read Optimization) ,5.6åå¢å ãéè¿optimizer_switchåæ°ä¸ä¸¤ä¸ªé项æ§å¶ï¼åæ°é»è®¤å¼å¯ã
mrr_cost_basdï¼éè¿åºäºææ¬çç®æ³æ¥ç¡®å®å¼å¯mrrç¹æ§ï¼onèªå¨ï¼off强å¶å¼å¯ã
MBRä½ç¨ï¼ææ®éç´¢å¼ä¸çå¶åèç¹ä¸æ¾å°ç主é®å¼çéååå¨å°read_rnd_bufferä¸ï¼ç¶åå该bufferä¸å¯¹ä¸»é®å¼æåºï¼ç¶åç¨æåºå·ç主é®å¼éåå»è®¿é®è¡¨ä¸çæ°æ®ï¼å°éæºIOç¼ç¨é¡ºåºIOï¼éä½æ¥è¯¢è¿ç¨IOå¼éã
使ç¨MBRä¼åæ¶ï¼æ§è¡è®¡åextraåä¼æ¾ç¤ºUsing MBRã
Â
BKAï¼Batched Key Accessï¼,æé«è¡¨joinæ§è½çç®æ³ï¼ä½ç¨æ¯è¯»å被join表çè®°å½æ¶å使ç¨é¡ºåºIOã
BKAåçï¼å¤è¡¨joinè¯å¥ï¼ä½¿ç¨ç´¢å¼è®¿é®ç¬¬äºä¸ªjoin表æ¶ï¼ä½¿ç¨ä¸ä¸ªjoin bufferæ¥æ¶é第ä¸ä¸ªæä½å¯¹è±¡çæçç¸å ³åå¼ï¼BKAæ建好keyåï¼æ¹éä¼ ç»å¼æå±åç´¢å¼æ¥æ¾ï¼keyéè¿MBRæ¥å£æ交ç»å¼æã
éè¿optimizer_switchåæ°çbatched_key_accessé项æ§å¶ï¼é»è®¤å ³éã
è¦å¼å¯è¯¥åæ°ï¼å¿ 须强å¶ä½¿ç¨MBRæè¡ã
SET global optimizer_switch=âmrr=on,mrr_cost_based=offâ;
SET global optimizer_switch=âbatched_key_access=onâ;
å½BKA使ç¨æ¶ï¼æ§è¡è®¡åextraåä¼æ¾ç¤ºUsing join buffer(Batched Key Access)ã
1.4.3.  主é®ç´¢å¼åå¯ä¸ç´¢å¼
主é®ç´¢å¼å°±æ¯èéç´¢å¼ï¼æ¯è¡¨åªè½æä¸ä¸ªãå¿ é¡»æ»¡è¶³ä¸ä¸ªæ¡ä»¶ï¼
l  主é®å¼å¿ é¡»å¯ä¸ã
l  ä¸è½å å«nullå¼ã
l  ä¸å®è¦ä¿è¯è¯¥å¼æ¯èªå¢å±æ§ãå¯ä»¥ä¿è¯åå ¥æ°æ®ç顺åºä¹æ¯èªå¢çï¼æé«ååæçã
å建主é®è¯æ³ï¼
alter table tab_name add primary key(col);
Â
å¯ä¸ç´¢å¼ï¼ä¸å 许æéå¤å¼ï¼ä½å 许空å¼ï¼å¯ä»¥æå¤ä¸ªå¯ä¸ç´¢å¼ã
è¯æ³ï¼
alter table tab_name add unique(col);
1.4.4.  è¦çç´¢å¼
æ°æ®å¨ç´¢å¼ä¸ï¼æ¥å°ç´¢å¼ä¸å¿ åå表æ¥è¯¢æ°æ®ãæ§è¡è®¡åextraåä¸ä¼åºç°Using indexã
å¦ä½¿ç¨è¦çç´¢å¼ï¼ä¸å®è¦è®©selectååºæéè¦çåï¼åå³ä¸è½ç´æ¥ååºselect *
1.4.5.  åç¼ç´¢å¼
对äºBLOBãTEXTæå¾é¿çvarcharç±»åçåï¼ä¸ºä»ä»¬åå 个å符建ç«çç´¢å¼ï¼å°±æ¯åç¼ç´¢å¼ãä¸è½åORDER BY æGROUP BYä¸ä½¿ç¨åç¼ç´¢å¼ï¼ä¹ä¸è½ç¨ä½è¦çç´¢å¼ã
alter table tab_name add key(col_name(prefix_length));
注æï¼æå ³é®çåæ°prefix_lengthï¼è¿ä¸ªå¼éè¦æ ¹æ®å®é 表çå 容æ¥å¾å°åéçç´¢å¼éæ©æ§ã
1.4.6.  èåç´¢å¼
èåç´¢å¼åå«å¤åç´¢å¼ï¼æ¯è¡¨ä¸ä¸¤ä¸ªæ两个以ä¸çåå建çç´¢å¼ã
create index idx_c1_c2 on t(c1,c2);
éæ©æ§é«çåæ¾åé¢ã
1.5.  åå¸ç´¢å¼
åå¸ç´¢å¼éç¨åå¸ç®æ³ï¼æé®å¼æ¢ç®ææ°çåå¸å¼ãåå¸ç´¢å¼åªè½è¿è¡çå¼æ¥è¯¢ï¼ä¸è½è¿è¡æåºã模ç³æ¥æ¾ãèå´æ¥è¯¢çãæ£ç´¢æ¶ä¸éè¦åB+treeé£æ ·ä»æ ¹èç¹å°å¶åèç¹é级æ¥æ¾ï¼åªéä¸æ¬¡åå¸ç®æ³å³å¯ç«å³å®ä½å°ç¸åºçä½ç½®ã
1.6.  索å¼æ»ç»
ç´¢å¼ä¼ç¹
l  æé«æ°æ®æ£ç´¢æç
l  æé«æ®åå½æ°æç
l  æé«æåºæç
l  使ç¨è¦çç´¢å¼å¯ä»¥é¿å å表
Â
ç´¢å¼å建å个ä¸è¦
l  éæ©æ§ä½çå段ä¸è¦å建索å¼
l  å¾å°æ¥è¯¢çåä¸è¦å建索å¼
l  大æ°æ®ç±»åå段ä¸è¦å建索å¼
l  尽éé¿å ä¸è¦ä½¿ç¨NULLï¼åºæå®å为NOT NULLã
Â
使ç¨ä¸å°ç´¢å¼çæ åµ
l  éè¿ç´¢å¼æ«æçè¡è®°å½æ°è¶ è¿å ¨è¡¨30%ï¼ä¼åå¨ä¸ä¼èµ°ç´¢å¼ï¼èèµ°å ¨è¡¨æ«æã
l  èåç´¢å¼ä¸ï¼ç¬¬ä¸ä¸ªæ¥è¯¢æ¡ä»¶ä¸æ¯æ左侧åã
l  èåç´¢å¼ä¸ï¼ç¬¬ä¸ä¸ªç´¢å¼å使ç¨èå´æ¥è¯¢ï¼åªè½ä½¿ç¨å°é¨åç´¢å¼ï¼æICPåºç°ã
l  èåç´¢å¼ä¸ï¼ç¬¬ä¸ä¸ªæ¥è¯¢æ¡ä»¶ä¸æ¯æå·¦åç¼åã
l  模ç³æ¥è¯¢æ¡ä»¶åæ左以éé 符%å¼å§ã
l  两个ååç´¢å¼ï¼ä¸ä¸ªç¨äºæ£ç´¢ï¼ä¸ä¸ªç¨æ·æåºãåªè½ä½¿ç¨å°ä¸ä¸ªç´¢å¼ï¼å 为æ¥è¯¢è¯å¥æå¤åªè½ä½¿ç¨ä¸ä¸ªç´¢å¼ï¼èè建ç«èåç´¢å¼ã
l  æ¥è¯¢å段ä¸æç´¢å¼ï¼ä½ä½¿ç¨äºå½æ°è¿ç®ã
Â
æ¥èª â ITPUBå客 â ï¼é¾æ¥ï¼http://blog.itpub.net/29519108/viewspace-2219114/ï¼å¦é转载ï¼è¯·æ³¨æåºå¤ï¼å¦åå°è¿½ç©¶æ³å¾è´£ä»»ã
转载äº:http://blog.itpub.net/29519108/viewspace-2219114/