ä½è ï¼é«é¹(ç½åå «æª)
æç« æ«å°¾æä»èä½çãæ·±å ¥ç解 MySQL 主ä»åç 32 讲ãï¼æ·±å ¥éå½»ç解 MySQL 主ä»ï¼GTID ç¸å ³ææ¯ç¥è¯ã
æ¬ææ¥æºï¼è½¬è½½èªå ¬ä¼å·-èå¶è¶é¦
*ç±å¯çå¼æºç¤¾åºåºåï¼ååå 容æªç»ææä¸å¾éæ使ç¨ï¼è½¬è½½è¯·èç³»å°ç¼å¹¶æ³¨ææ¥æºã
æ¬æ使ç¨æºç çæ¬ï¼5.7.22
å¼æ为ï¼Innodb
æåº(filesort)ä½ä¸º DBA ç»ä¸å¼çè¯é¢ï¼ä¹ç»å¸¸ææå讨论å®ï¼æ¯å¦å¸¸è§çé®é¢å¦ä¸ï¼
- æåºçæ¶åï¼ç¨äºæåºçæ°æ®ä¼ä¸ä¼å¦ Innodb ä¸æ ·å缩空å符åå¨ï¼æ¯å¦ varchar(30)ï¼æåªæ¯åå¨äº 1 个å符æ¯å¦ä¼å缩ï¼è¿æ¯æç § 30 个å符计ç®ï¼
- max_length_for_sort_data/max_sort_length å°åºæ¯ä»ä¹å«ä¹ï¼
- original filesort algorithm(å表æåº) å modified filesort algorithm(ä¸å表æåº) çæ ¹æ¬åºå«æ¯ä»ä¹ï¼
- 为ä»ä¹ä½¿ç¨å°æåºçæ¶åæ ¢æ¥è¯¢ä¸ç Rows_examined ä¼æ´å¤§ï¼è®¡ç®æ¹å¼å°åºæ¯ä»ä¹æ ·çï¼
å¨ MySQL é常æå¦ä¸ç®æ³æ¥å®ææåºï¼
- å åæåº(ä¼å éå order by limit è¿åå°éè¡å¸¸ç¨ï¼æé«æåºæçï¼ä½æ¯æ³¨æ order by limit n,mï¼å¦æ n è¿å¤§å¯è½ä¼æ¶åå°æåºç®æ³çåæ¢)
- å åæåº(å¿«éæåº)
- å¤é¨æåº(å½å¹¶æåº)
ä½æ¯ç±äºè½åæéæ¬æä¸è§£éè¿äºç®æ³ï¼å¹¶ä¸æ¬æä¸èèä¼å éåç®æ³çåæ¯é»è¾ï¼åªä»¥å¿«éæåºåå½å¹¶æåºä½ä¸ºåºç¡è¿è¡æµç¨åæãæ们å¨æ§è¡è®¡åä¸å¦æåºç° filesort åæ ·é常代表使ç¨å°äºæåºï¼ä½æ¯æ§è¡è®¡åä¸çä¸åºæ¥ä¸é¢é®é¢ï¼
- æ¯å¦ä½¿ç¨äºä¸´æ¶æ件ã
- æ¯å¦ä½¿ç¨äºä¼å éåã
- æ¯ original filesort algorithm(å表æåº) è¿æ¯ modified filesort algorithm(ä¸å表æåº)ã
å¦ä½æ¥çå°å¨åé¢è¿è¡æè¿°ãæ¬æè¿ä¼ç»åºå¤§éçæåºæ¥å£ä¾æå ´è¶£çæå使ç¨ï¼ä¹ç»èªå·±çä¸ç¬è®°ã
ä¸ãä»ä¸ä¸ªé®é¢åºå
è¿æ¯æè¿ä¸ä¸ªæåéå°çæ¡ä¾ï¼å¤§æ¦ææå°±æ¯è¯´æçè¡¨å¨ Innodb ä¸åªæ 30G å·¦å³ï¼ä¸ºä»ä¹ä½¿ç¨å¦ä¸è¯å¥è¿è¡æåºæä½å临æ¶æä»¶å± ç¶è¾¾å°äº 200 å¤ Gãå½ç¶è¯å¥å¾åæï¼æ们å¯ä»¥å ä¸é®ä¸ºä»ä¹ä¼æè¿æ ·çè¯å¥ï¼æ们åªéè¦ç 究åçå³å¯ï¼å¨æ¬æç第åä¸é¨åä¼è¿è¡åå 解éåé®é¢éç°ã
临æ¶æ件å¦ä¸ï¼
ä¸é¢æ¯è¿äºæ¡ä¾ä¿¡æ¯ï¼
show create table tG*************************** 1. row ***************************Table: tCreateTable: CREATE TABLE `t`(`ID` bigint(20) NOT NULL COMMENT 'ID',`UNLOAD_TASK_NO` varchar(50) NOT NULL ,`FORKLIFT_TICKETS_COUNT` bigint(20) DEFAULT NULL COMMENT 'å车票æ°',`MANAGE_STATUS` varchar(20) DEFAULT NULL COMMENT '管çç¶æ',`TRAY_BINDING_TASK_NO` varchar(50) NOT NULL ,`STATISTIC_STATUS` varchar(50) NOT NULL ,`CREATE_NO` varchar(50) DEFAULT NULL ,`UPDATE_NO` varchar(50) DEFAULT NULL ,`CREATE_NAME` varchar(200) DEFAULT NULL COMMENT 'å建人å称',`UPDATE_NAME` varchar(200) DEFAULT NULL COMMENT 'æ´æ°äººå称',`CREATE_ORG_CODE` varchar(200) DEFAULT NULL COMMENT 'å建ç»ç»ç¼å·',`UPDATE_ORG_CODE` varchar(200) DEFAULT NULL COMMENT 'æ´æ°ç»ç»ç¼å·',`CREATE_ORG_NAME` varchar(1000) DEFAULT NULL COMMENT 'å建ç»ç»å称',`UPDATE_ORG_NAME` varchar(1000) DEFAULT NULL COMMENT 'æ´æ°ç»ç»å称',`CREATE_TIME` datetime DEFAULT NULL COMMENT 'å建æ¶é´',`UPDATE_TIME` datetime DEFAULT NULL COMMENT 'æ´æ°æ¶é´',`DATA_STATUS` varchar(50) DEFAULT NULL COMMENT 'æ°æ®ç¶æ',`OPERATION_DEVICE` varchar(200) DEFAULT NULL COMMENT 'æä½è®¾å¤',`OPERATION_DEVICE_CODE` varchar(200) DEFAULT NULL COMMENT 'æä½è®¾å¤ç¼ç ',`OPERATION_CODE` varchar(50) DEFAULT NULL COMMENT 'æä½ç ',`OPERATION_ASSIST_CODE` varchar(50) DEFAULT NULL COMMENT 'è¾
å©æä½ç ',`CONTROL_STATUS` varchar(50) DEFAULT NULL COMMENT 'æ§å¶ç¶æ',`OPERATOR_NO` varchar(50) DEFAULT NULL COMMENT 'æä½äººå·¥å·',`OPERATOR_NAME` varchar(200) DEFAULT NULL COMMENT 'æä½äººå称',`OPERATION_ORG_CODE` varchar(50) DEFAULT NULL COMMENT 'æä½é¨é¨ç¼å·',`OPERATION_ORG_NAME` varchar(200) DEFAULT NULL COMMENT 'æä½é¨é¨å称',`OPERATION_TIME` datetime DEFAULT NULL COMMENT 'æä½æ¶é´',`OPERATOR_DEPT_NO` varchar(50) NOT NULL COMMENT 'æä½äººæå±é¨é¨ç¼å·',`OPERATOR_DEPT_NAME` varchar(200) NOT NULL COMMENT 'æä½äººæå±é¨é¨å称',`FORKLIFT_DRIVER_NAME` varchar(200) DEFAULT NULL ,`FORKLIFT_DRIVER_NO` varchar(50) DEFAULT NULL ,`FORKLIFT_DRIVER_DEPT_NAME` varchar(200) DEFAULT NULL ,`FORKLIFT_DRIVER_DEPT_NO` varchar(50) DEFAULT NULL ,`FORKLIFT_SCAN_TIME` datetime DEFAULT NULL ,`OUT_FIELD_CODE` varchar(200) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `IDX_TRAY_BINDING_TASK_NO`(`TRAY_BINDING_TASK_NO`), KEY `IDX_OPERATION_ORG_CODE`(`OPERATION_ORG_CODE`), KEY `IDX_OPERATION_TIME`(`OPERATION_TIME`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8descSELECT ID, UNLOAD_TASK_NO, FORKLIFT_TICKETS_COUNT, MANAGE_STATUS, TRAY_BINDING_TASK_NO, STATISTIC_STATUS, CREATE_NO, UPDATE_NO, CREATE_NAME, UPDATE_NAME, CREATE_ORG_CODE, UPDATE_ORG_CODE, CREATE_ORG_NAME, UPDATE_ORG_NAME, CREATE_TIME, UPDATE_TIME, DATA_STATUS, OPERATION_DEVICE, OPERATION_DEVICE_CODE, OPERATION_CODE, OPERATION_ASSIST_CODE, CONTROL_STATUS, OPERATOR_NO, OPERATOR_NAME, OPERATION_ORG_CODE, OPERATION_ORG_NAME, OPERATION_TIME, OPERATOR_DEPT_NO, OPERATOR_DEPT_NAME, FORKLIFT_DRIVER_NAME, FORKLIFT_DRIVER_NO, FORKLIFT_DRIVER_DEPT_NAME, FORKLIFT_DRIVER_DEPT_NO, FORKLIFT_SCAN_TIME, OUT_FIELD_CODEFROM tGROUP BY id , UNLOAD_TASK_NO , FORKLIFT_TICKETS_COUNT ,MANAGE_STATUS , TRAY_BINDING_TASK_NO , STATISTIC_STATUS ,CREATE_NO , UPDATE_NO , CREATE_NAME , UPDATE_NAME ,CREATE_ORG_CODE , UPDATE_ORG_CODE , CREATE_ORG_NAME ,UPDATE_ORG_NAME , CREATE_TIME , UPDATE_TIME , DATA_STATUS ,OPERATION_DEVICE , OPERATION_DEVICE_CODE , OPERATION_CODE ,OPERATION_ASSIST_CODE , CONTROL_STATUS , OPERATOR_NO ,OPERATOR_NAME , OPERATION_ORG_CODE , OPERATION_ORG_NAME ,OPERATION_TIME , OPERATOR_DEPT_NO , OPERATOR_DEPT_NAME ,FORKLIFT_DRIVER_NAME , FORKLIFT_DRIVER_NO ,FORKLIFT_DRIVER_DEPT_NAME , FORKLIFT_DRIVER_DEPT_NO ,FORKLIFT_SCAN_TIME , OUT_FIELD_CODE;+----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|+----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+| 1| SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 5381145| 100.00| Using filesort |+----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+1 row inset, 1 warning (0.00 sec)
ä¹è®¸ä½ ä¼æçè¿ä¸ªè¯å¥æä»ä¹ç¨ï¼æ们å ä¸èèåè½ï¼æ们åªèè为ä»ä¹å®ä¼çæ 200G ç临æ¶æ件è¿ä¸ªé®é¢ã
æ¥ä¸æ¥æå°åé¶æ®µè¿è¡æåºçæµç¨è§£æï¼æ³¨æäºæ´ä¸ªæåºçæµç¨åå¤äºç¶æâCreating sort indexâä¸é¢ï¼æ们以 filesort å½æ°æ¥å£ä¸ºå¼å§è¿è¡åæã
äºãæµè¯æ¡ä¾
为äºæ´å¥½ç说æåé¢çæµç¨æä»¬ä½¿ç¨ 2 个é¤äºå段é¿åº¦ä¸åï¼å ¶ä»å®å ¨ä¸æ ·ç表æ¥è¯´æï¼ä½æ¯éè¦æ³¨æè¿ä¸¤ä¸ªè¡¨æ°æ®éå¾å°ï¼ä¸ä¼åºç°å¤é¨æåºï¼å¦ææ¶åå¤é¨æåºçæ¶åæ们éè¦å设å®ä»¬æ°æ®éå¾å¤§ãå ¶æ¬¡è¿éæ ¹æ® original filesort algorithm å modified filesort algorithm è¿è¡ååï¼ä½æ¯è¿ä¸¤ç§æ¹æ³è¿æ²¡è®²è¿°ï¼ä¸ç¨å¤ªå¤çä¼ã
- original filesort algorithm(å表æåº)
mysql> show create table tests1 G*************************** 1. row ***************************Table: tests1CreateTable: CREATE TABLE `tests1`(`a1` varchar(300) DEFAULT NULL,`a2` varchar(300) DEFAULT NULL,`a3` varchar(300) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row inset(0.00 sec)mysql> select* from tests1;+------+------+------+| a1 | a2 | a3 |+------+------+------+| a | a | a || a | b | b || a | c | c || b | d | d || b | e | e || b | f | f || c | g | g || c | h | h |+------+------+------+8 rows inset(0.00 sec)mysql> desc select* from tests1 where a1='b' order by a2,a3;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| 1| SIMPLE | tests1 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row inset, 1 warning (0.00 sec)
- modified filesort algorithm(ä¸å表æåº)
mysql> desc select* from tests2 where a1='b' order by a2,a3;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row inset, 1 warning (0.00 sec)mysql> show create table tests2 G*************************** 1. row ***************************Table: tests2CreateTable: CREATE TABLE `tests2`(`a1` varchar(20) DEFAULT NULL,`a2` varchar(20) DEFAULT NULL,`a3` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row inset(0.00 sec)mysql> select* from tests2;+------+------+------+| a1 | a2 | a3 |+------+------+------+| a | a | a || a | b | b || a | c | c || b | d | d || b | e | e || b | f | f || c | g | g || c | h | h |+------+------+------+8 rows inset(0.00 sec)mysql> desc select* from tests2 where a1='b' order by a2,a3;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row inset, 1 warning (0.01 sec)
æ´ä¸ªæµç¨æä»¬ä» filesort å½æ°æ¥å£å¼å§è®¨è®ºãä¸é¢ç¬¬ä¸å°ç¬¬åé¨å为æåºç主è¦æµç¨ã
ä¸ãé¶æ®µ 1ï¼ç¡®è®¤æåºå段å顺åº
è¿é主è¦å°æåºé¡ºåºåå ¥å° Filesort ç±»ç sortorder ä¸ï¼æ¯å¦æ们ä¾åä¸ç order by a2,a3 å°±æ¯ a2 å a3 åï¼ä¸»è¦æ¥å£ä¸º Filesort::make_sortorderï¼æ们æç §æºç æ述为 sort å段(æºç ä¸ä¸º sort_length)ï¼æ¾ç¶æ们å¨æåºçæ¶åé¤äº sort å段以å¤ï¼è¿åºè¯¥å å«é¢å¤çå段ï¼å°åºå å«åªäºå段就ä¸æ¹æ³ original filesort algorithm(å表æåº) å modified filesort algorithm(ä¸å表æåº) æå ³äºï¼ä¸é¢è¿è¡è®¨è®ºã
åãé¶æ®µ 2ï¼è®¡ç® sort å段çé¿åº¦
è¿é主è¦è°ç¨ä½¿ç¨ sortlength å½æ°ï¼è¿ä¸æ¥å°ä¼å¸¦å ¥ max_sort_length åæ°ç设置è¿è¡å¤æï¼é»è®¤æ åµä¸ max_sort_length 为 1024 åèã
è¿ä¸æ¥å¤§æ¦æ¥éª¤ä¸ºï¼
1ã循ç¯æ¯ä¸ä¸ª sort å段
2ã计ç®æ¯ä¸ä¸ª sort å段çé¿åº¦ï¼å ¬å¼ä¸º â å®ä¹é¿åº¦ * 2
æ¯å¦è¿éä¾åä¸æå®ä¹äº a1 varchar(300)ï¼é£ä¹å®ç计ç®é¿åº¦ â 300 * 2(600)ï¼ä¸ºä»ä¹æ¯ * 2 å¢ï¼è¿åºè¯¥æ¯å Unicode ç¼ç æå ³ï¼è¿ä¸æ¥å¯ä»¥åèå½æ° my_strnxfrmlen_utf8ã
åæ¶éè¦æ³¨æè¿éæ¯çº¦çäºï¼å 为æºç ä¸è¿æ¯å ¶ä»çèè(æ¯å¦ï¼å符æ¯å¦ä¸ºç©º)ï¼ä½æ¯å ç¨ä¸å¤ä¸èèäºã
3ãå¸¦å ¥ max_sort_length åæ°è¿è¡è®¡ç®
好äºï¼æäºä¸é¢ä¸ä¸ª sort å段çé¿åº¦ï¼é£ä¹è¿éå°±å max_sort_length è¿è¡æ¯è¾ã
å¦æè¿ä¸ªè¿ä¸ª sort åæ®µå¤§äº max_sort_length çå¼ï¼é£ä¹ä»¥ max_sort_length 设置为åï¼è¿æ¥ä»£ç å¦ä¸ï¼
set_if_smaller(sortorder->length, thd->variables.max_sort_length);
å æ¤ï¼å¦æ sort å段çæ个åæ®µè¶ è¿äº max_sort_length 设置ï¼é£ä¹æåºå¯è½ä¸é£ä¹ç²¾ç¡®äºã
å°äºè¿éï¼æ¯ä¸ª sort å段çé¿åº¦ä»¥å sort å段çæ»é¿åº¦å·²ç»è®¡ç®åºæ¥ï¼æ¯å¦åé¢ç»ç两个ä¸åååä¸ï¼
- (a2 varchar(300) a3 varchar(300) order by a2,a3)ï¼æ¯ä¸ª sort å段约为 300 * 2 åèï¼ä¸¤ä¸ªå段çæ»é¿åº¦çº¦ä¸º 1200 åèã
- (a2 varchar(20) a3 varchar(20) order by a2,a3)ï¼æ¯ä¸ª sort å段约为 20 * 2 åèï¼ä¸¤ä¸ªå段çæ»é¿åº¦çº¦ä¸º 80 åèã
并ä¸å¼å¾æ³¨æçæ¯ï¼è¿éæ¯æç §å®ä¹å¤§å°ï¼å¦ varchar(300) ï¼ä»¥ 300 个å符æ¥è®¡ç®é¿åº¦çï¼èä¸æ¯æ们é常çå°ç Innodb ä¸å®é å ç¨çå符æ°éãè¿æ¯æåºä½¿ç¨ç©ºé´å¤§äº Innodb å®é æ°æ®æ件大å°çä¸ä¸ªåå ã
ä¸é¢æ们以(a2 varchar(300) a3 varchar(300) order by a2,a3)为ä¾å®é çç debug çç»æå¦ä¸ï¼
(gdb) p sortorder->field->field_name$4 = 0x7ffe7800fadf"a3"(gdb) p sortorder->length$5 = 600(gdb) p total_length$6 = 1202(è¿éa2,a3 å¯ä»¥ä¸ºNULLåèªå äº1个åè)(gdb)
å¯ä»¥çåºæ²¡æé®é¢ã
4ã循ç¯ç»æï¼è®¡ç®åº sort å段çæ»é¿åº¦ã
åé¢æ们ä¼çå° sort å段ä¸è½ä½¿ç¨å缩(pack)ææ¯ã
äºãé¶æ®µ 3ï¼è®¡ç®é¢å¤å段ç空é´
对äºæåºèè¨ï¼æ们å¾æ¸ æ¥é¤äºsort å段以å¤ï¼é常æ们éè¦çæ¯å®é çæ°æ®ï¼é£ä¹æ å¤ä¹ä¸¤ç§æ¹å¼å¦ä¸ï¼
- original filesort algorithmï¼åªåå¨ rowid æè 主é®å为é¢å¤çå段ï¼ç¶åè¿è¡å表æ½åæ°æ®ãæ们æç §æºç çæè¿°ï¼å°è¿ç§å ³èå表çå段å«å ref å段(æºç ä¸åéå«å ref_length)ã
- modified filesort algorithmï¼å°å¤äº read_set(éè¦è¯»åçå段) å ¨é¨æ¾å°é¢å¤å段ä¸ï¼è¿æ ·ä¸éè¦å表读åæ°æ®äºãæ们æç §æºç çæè¿°ï¼å°è¿äºé¢å¤åå¨çå段å«å addon å段(æºç ä¸åéå«å addon_length)ã
è¿éä¸æ¥å°±æ¯è¦æ¥å¤æå°åºä½¿ç¨é£ç§ç®æ³ï¼å ¶ä¸»è¦æ åå°±æ¯åæ° max_length_for_sort_dataï¼å ¶é»è®¤å¤§å°ä¸º 1024 åèï¼ä½æ¯åé¢ä¼çå°è¿éç计ç®ä¸º(sortå段é¿åº¦ + addon å段çæ»å)æ¯å¦è¶ è¿äº max_length_for_sort_dataãå ¶æ¬¡å¦æ使ç¨äº modified filesort algorithm ç®æ³ï¼é£ä¹å°ä¼å¯¹ addon å段çæ¯ä¸ªå段åä¸ä¸ª pack(æå )ï¼ä¸»è¦ç®çå¨äºå缩é£äºä¸ºç©ºçåèï¼èç空é´ã
è¿ä¸æ¥ç主è¦å ¥å£å½æ°ä¸º Filesort::get_addon_fields ä¸é¢æ¯æ¥éª¤è§£æã
1ã循ç¯æ¬è¡¨å ¨é¨å段
2ãæ ¹æ® read_set è¿æ»¤åºä¸éè¦åå¨çå段
è¿éå¦æä¸éè¦è®¿é®å°çå段èªç¶ä¸ä¼å å«å¨å ¶ä¸ï¼ä¸é¢è¿æ®µæºç è¿æ»¤ä»£ç ï¼
if(!bitmap_is_set(read_set, field->field_index)) //æ¯å¦å¨read setä¸continue;
3ãè·åå段çé¿åº¦
è¿éå°±æ¯å®é çé¿åº¦äºæ¯å¦æ们ç a1 varchar(300)ï¼ä¸å符é为 UTF8ï¼é£ä¹å ¶é¿åº¦ â 300 * 3(900)ã
4ãè·åå¯ä»¥ pack(æå ) å段çé¿åº¦
åä¸é¢ä¸åï¼å¯¹äº int è¿äºåºå®é¿åº¦ç±»åçå段ï¼åªæå¯åé¿åº¦çç±»åçå段æéè¦è¿è¡æå ææ¯ã
5ã循ç¯ç»æï¼è·å addon å段çæ»é¿åº¦ï¼è·åå¯ä»¥ pack(æå ) å段çæ»é¿åº¦
循ç¯ç»æåå¯ä»¥è·å addon å段çæ»é¿åº¦ï¼ä½æ¯éè¦æ³¨æ addon å段å sort å段å¯è½å å«éå¤çå段ï¼æ¯å¦ä¾ 2 ä¸ sort å段为 a2,a3ï¼addon å段为 a1,a2,a3ã
å¦æ满足å¦ä¸æ¡ä»¶ï¼
addon å段çæ»é¿åº¦ + sort å段çæ»é¿åº¦ > max_length_for_sort_data
é£ä¹å°ä½¿ç¨ original filesort algorithm(å表æåº) çæ¹å¼ï¼å¦åä½¿ç¨ modified filesort algorithm çæ¹å¼è¿è¡ãä¸é¢æ¯è¿ä¸å¥ä»£ç ï¼
if(total_length + sortlength > max_length_for_sort_data) //å¦æé¿åº¦å¤§äºäºmax_length_for_sort_data åéåºäº{ DBUG_ASSERT(addon_fields == NULL);return NULL;//è¿åNULLå¼ ä¸æå
äº ä½¿ç¨ original filesort algorithm(å表æåº)}
æ们å¨åå°æ¬æ第äºé¨åä¾åä¸ç第 1 个æ¡ä¾ï¼å 为æ们对 a1,a2,a3 é½æ¯éè¦è®¿é®çï¼ä¸ä»ä»¬ç大å°å为 varchar(300) UTF8ï¼é£ä¹ addon å段é¿åº¦å¤§çº¦ä¸º 300 * 3 * 3 = 2700 åè ï¼å ¶æ¬¡æ们åé¢è®¡ç®äº sort å段大约为 1202 åèï¼å æ¤ 2700 + 1202 æ¯è¿è¿å¤§äº max_length_for_sort_data çé»è®¤è®¾ç½® 1024 åèçï¼å æ¤ä¼ä½¿ç¨ original filesort algorithm æ¹å¼è¿è¡æåºã
å¦ææ¯ç¬¬äºé¨åä¾åä¸ç第 2 个æ¡ä¾å¢ï¼æ¾ç¶è¦å°å¾å¤äº(æ¯ä¸ªå段 varchar(20))ï¼å¤§çº¦å°±æ¯ 20 * 3 * 3(addon å段)+ 82(sort å段) å®æ¯å°äº 1024 åèçï¼å æ¤ä¼ä½¿ç¨ modified filesort algorithm çæåºæ¹å¼ï¼å¹¶ä¸è¿äº addon å段åºæ¬é½å¯ä»¥ä½¿ç¨æå (pack)ææ¯ï¼æ¥èç空é´ãä½æ¯éè¦æ³¨æçæ¯æ 论å¦ä½(sort å段)æ¯ä¸è½è¿è¡æå (pack)çï¼èåºå®é¿åº¦ç±»åä¸éè¦æå (pack)å缩空é´ã
å ãé¶æ®µ 4ï¼ç¡®è®¤æ¯è¡çé¿åº¦
æäºä¸é¢ç就计ç®åæ¯ä¸è¡çé¿åº¦(å¦æå¯ä»¥æå æ¯æå åçé¿åº¦)ï¼ä¸é¢å°±æ¯è¿ä¸ªè®¡ç®è¿ç¨ã
if(using_addon_fields())//å¦æ使ç¨äº æå
ææ¯ æ£æµ addon_fields æ°ç»æ¯å¦åå¨ ä½¿ç¨modified filesort algorithmç®æ³ ä¸å表æåº{ res_length= addon_length; //æ»çé¿åº¦ 3个 varchar(300) uft8 为 3*300*3}else//使ç¨original filesort algorithmç®æ³{ res_length= ref_length; //rowid(主é®é¿åº¦)/* The reference to the record is considered as an additional sorted field */ sort_length+= ref_length; //å®é
ä¸å°±æ¯rowid(主é®) +æåºå段é¿åº¦ å表æåº}/* Add hash at the end of sort key to order cut values correctly. Needed for GROUPing, rather than for ORDERing. */if(use_hash) sort_length+= sizeof(ulonglong); rec_length= sort_length + addon_length;//modified filesort algorithm sort_length 为æåºé®é¿åº¦ addon_lenth 为访é®å段é¿åº¦ï¼original filesort algorithm rowid(主é®) +æåºå段é¿åº¦ ï¼å 为addon_length为0
好äºæ们ç¨å¾®æ»ç»ä¸ä¸ï¼
- original filesort algorithmï¼æ¯è¡é¿åº¦ä¸º sort å段çæ»é¿åº¦ + ref å段é¿åº¦(主é®æè rowid)ã
- modified filesort algorithmï¼æ¯è¡çé¿åº¦ä¸º sort å段çæ»é¿åº¦ + addon å段çé¿åº¦(éè¦è®¿é®çå段æ»é¿åº¦)ã
å½ç¶å°åºä½¿ç¨é£ç§ç®æ³åèä¸ä¸é¨åãä½æ¯è¦æ³¨æäºå¯¹äº varchar è¿ç§å¯åé¿åº¦æ¯ä»¥å®ä¹ç大å°ä¸ºåäºï¼æ¯å¦ UTF8 varchar(300) å°±æ¯ 300 * 3 = 900 èä¸æ¯å®é åå¨ç大å°ï¼èåºå®é¿åº¦æ²¡æååã好äºï¼è¿æ¯å头çç第äºé¨åç两个ä¾åï¼åå«è®¡ç®å®ä»¬çè¡é¿åº¦ï¼
- ä¾å 1ï¼æ ¹æ®æ们ç计ç®ï¼å®å°ä½¿ç¨ original filesort algorithm æåºæ¹å¼ï¼æç»ç计ç®è¡é¿åº¦åºè¯¥ä¸º(sort å段é¿åº¦ + rowid é¿åº¦)å â 1202 + 6 åèï¼ä¸é¢æ¯ debug çç»æï¼
(gdb) p rec_length$1 = 1208
- ä¾å 2ï¼æ ¹æ®æ们ç计ç®ï¼å®å°ä½¿ç¨ modified filesort algorithm æåºæ¹å¼ï¼æç»è®¡ç®è¡é¿åº¦åºè¯¥ä¸º(sort å段é¿åº¦ + addon å段é¿åº¦)å â 82 + 20 * 3 * 3 (ç»æ为 262)ï¼æ³¨æè¿éæ¯çº¦çäºæ²¡æ计ç®é空çå ç´ åå¯åé¿åº¦å ç´ ï¼ä¸é¢æ¯ debug çç»æï¼
(gdb) p rec_length$2 = 266
å¯ä»¥çåºè¯¯å·®ä¸å¤§ã
ä¸ãé¶æ®µ 5ï¼ç¡®è®¤æ大å ååé
è¿éçåé å åå°±ååæ° sort_buffer_size 大å°æå ³äºãä½æ¯æ¯ä¸æ¯æ¯æ¬¡é½ä¼åé è³å° sort_buffer_size 大å°çå åçå¢ï¼å ¶å®ä¸æ¯ï¼MySQL ä¼å¤ææ¯å¦è¡¨å¾å°çæ åµï¼ä¹å°±æ¯åä¸ä¸ªç®åçè¿ç®ï¼ç®çå¨äºèçå åçå¼éï¼è¿éæ们å°æ¥æè¿°ã
1ã大æ¦è®¡ç®åº Innodb å±ä¸»é®å¶åç»ç¹çè¡æ°
è¿ä¸æ¥ä¸»è¦éè¿(èéç´¢å¼å¶åç»ç¹ç空é´å¤§å° / èéç´¢å¼æ¯è¡å¤§å° * 2)计ç®åºä¸ä¸ªè¡çä¸éï¼è°å ¥å½æ° ha_innobase::estimate_rows_upper_boundï¼æºç å¦ä¸ï¼
num_rows= table->file->estimate_rows_upper_bound();//ä¸éæ¥èªäºInnodb å¶åèéç´¢å¼å¶åç»ç¹/èéç´¢å¼é¿åº¦ *2
ç¶åå°ç»æåå¨èµ·æ¥ï¼å¦æ表å¾å°é£ä¹è¿ä¸ªå¼ä¼é常å°ã
2ãæ ¹æ®åé¢è®¡ç®çæ¯è¡é¿åº¦è®¡ç®åº sort buffer å¯ä»¥å®¹ä¸çæ大è¡æ°
è¿ä¸æ¥å°è®¡ç® sort buffer å¯ä»¥å®¹çº³çæ大è¡æ°å¦ä¸ï¼
ha_rows keys= memory_available / (param.rec_length + sizeof(char*));//å¯ä»¥æåºç è¡æ° sort buffer ä¸æ大 å¯ä»¥æåºçè¡æ°
3ã对æ¯ä¸¤è çæå°å¼ï¼ä½ä¸ºåé å åçæ å
ç¶å对æ¯ä¸¤ä¸ªå¼ä»¥å°å¼ä¸ºåï¼å¦ä¸ï¼
param.max_keys_per_buffer= (uint) min(num_rows > 0? num_rows : 1, keys);//åå¨è¡æ°ä¸é å å¯ä»¥æåº è¡æ°ç å°å¼
4ãæ ¹æ®ç»æåé å å
åé å¦ä¸ï¼
table_sort.alloc_sort_buffer(param.max_keys_per_buffer, param.rec_length);
ä¹å°±æ¯æ ¹æ®æ»ç计ç®åºçè¡é¿åº¦å计ç®åºçè¡æ°è¿è¡åé ã
å «ãé¶æ®µ 6ï¼è¯»åæ°æ®ï¼è¿è¡å åæåº
å°è¿éåå¤å·¥ä½å·²ç»å®æäºï¼æ¥ä¸å°±æ¯ä»¥è¡ä¸ºåä½è¯»åæ°æ®äºï¼ç¶å对è¿æ»¤æ where æ¡ä»¶çå©ä¸çæ°æ®è¿è¡æåºãå¦æéè¦æåºçæ°æ®å¾å¤ï¼é£ä¹çæåºå åå满åä¼è¿è¡å åæåºï¼ç¶åå°æåºçå 容åå ¥å°æåºä¸´æ¶æ件ä¸ï¼çå¾ ä¸ä¸æ¥åå¤é¨çå½å¹¶æåºã
ä½ä¸ºå½å¹¶æåºèè¨ï¼æ¯ä¸ä¸ªå½å¹¶çæ件çæ®µå¿ é¡»æ¯æåºå¥½çï¼å¦åå½å¹¶æåºæ¯ä¸è½å®æçï¼å æ¤å满æåºå ååéè¦åå åæåºãå¦æåä¸æ»¡å¢ï¼é£ä¹åä¸æ¬¡å åæåºå°±å¥½äºã
ä¸é¢æ们æ¥ççè¿ä¸ªè¿ç¨ï¼æ´ä¸ªè¿ç¨éä¸å¨ find_all_keys å½æ°ä¸ã
1ã读åéè¦çæ°æ®
å®é ä¸å¨è¿ä¸æ¥ä¹åè¿ä¼å read_set çæ´æ¹ï¼å ä¸ºå¯¹äº original filesort algorithm(å表æåº) çç®æ³æ¥è®²ä¸ä¼è¯»åå ¨é¨éè¦çå段ï¼ä¸ºäºç®åèµ·è§ä¸åæè¿°äºãè¿ä¸æ¥å°±æ¯è¯»åä¸è¡æ°æ®äºï¼è¿éä¼è¿å ¥ Innodb å±è¯»åæ°æ®ï¼å ·ä½æµç¨ä¸å解éäºï¼ä¸é¢æ¯è¿ä¸è¡ä»£ç ï¼
error= file->ha_rnd_next(sort_form->record[0]); //读åä¸è¡æ°æ®
2ãå° Rows_examined å 1
è¿éè¿ä¸ªææ 对åºçå°±æ¯æ ¢æ¥ä¸ç Rows_examined äºï¼è¿ä¸ªææ å¨ææåºçæ åµä¸ä¼åºç°éå¤è®¡ç®çæ åµï¼ä½æ¯è¿éè¿æ¯æ£ç¡®çï¼éå¤çé¨ååé¢å说ã
3ãè¿æ»¤æ where æ¡ä»¶
è¿éå°ä¼è¿æ»¤æ where æ¡ä»¶ä¸ä¸æ»¡è¶³æ¡ä»¶çè¡ï¼ä»£ç å¦ä¸ï¼
if(!error && !qep_tab->skip_record(thd, &skip_record) && !skip_record)//è¿éåwhereè¿æ»¤æ¡ä»¶ çæ¯è¾
4ãå°è¡æ°æ®åå ¥å° sort buffer ä¸
è¿ä¸æ¥å°ä¼ææ°æ®åå ¥å° sort buffer ä¸ï¼éè¦æ³¨æè¿éä¸æ¶åæåºæä½ï¼åªæ¯åå¨æ°æ®å°å åä¸ãå ¶ä¸åä¸ºäº 2 é¨åï¼
- åå ¥ sort å段ãå¦ææ¯ original filesort algorithm é£ä¹ rowid(主é®) ä¹å å«å¨å ¶ä¸äºã
- åå ¥ addon å段ï¼è¿æ¯ modified filesort algorithm æä¼æçï¼å¨åå ¥ä¹åè¿ä¼è°ç¨ Field::pack 对å¯ä»¥æå (pack)çå段è¿è¡å缩æä½ãå¯¹äº varchar å段çæå å½æ°å°±æ¯ Field_varstring::packï¼ç®åç说åå¨çæ¯å®é ç大å°ï¼èéå®ä¹ç大å°ã
æ´ä¸ªè¿ç¨ä½äº find_all_keys->Sort_param::make_sortkey å½æ°ä¸ãè¿ä¸æ¥è¿æ¶åå°äºæ们éå¸¸å ³å¿çä¸ä¸ªé®é¢ï¼å°åºæåºçæ°æ®å¦ä½åå¨çé®é¢ï¼éè¦ä»ç»é 读ãä¸é¢æ们就 debug ä¸ä¸ç¬¬äºé¨åä¸ä¸¤ä¸ªä¾åçä¸ååå¨æ¹å¼ã
æ¢ç¶è¦å»çå åä¸çæ°æ®ï¼æ们åªè¦çå®æç»æ·è´çå åæ°æ®æ¯ä»ä¹å°±å¥½äºï¼é£ä¹çç¸å°ä¼å¤§ç½ï¼æ们åªéè¦å°æç¹æ¾å° find_all_keys å½æ°ä¸ï¼åå®ä¸è¡æ°æ®ç Sort_param::make_sortkey æä½åçå åå°±è¡äºï¼å¦ä¸ï¼
- ä¾å 1(å段é½æ¯ varchar(300))ï¼å®å°ä½¿ç¨ original filesort algorithm(å表æåº) çæ¹å¼ï¼æç»åºè¯¥åå¨çæ¯ sort å段(a2,a3)+ rowidãæåºçç»æå¦ä¸ï¼
mysql> select* from test.tests1 where a1='b' order by a2,a3;+------+------+------+| a1 | a2 | a3 |+------+------+------+| b | d | d || b | e | e || b | f | f |+------+------+------+3 rows inset(9.06 sec)æ们以第äºè¡ä¸ºæ¥çç®æ
ç±äºç¯å¹ çå ³ç³»ï¼æå±ç¤ºå ¶ä¸çä¸é¨åï¼å 为è¿é大约æ 1200 å¤ä¸ªåèï¼å¦ä¸ï¼
(gdb) x/1300bx start_of_rec0x7ffe7ca79998: 0x01 0x00 0x45 0x00 0x20 0x00 0x20 0x000x7ffe7ca799a0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7ca799a8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7ca799b0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7ca799b8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7ca799c0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7ca799c8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00...è¿åé¢è¿æ大éç 0X20 0X00
æ们çå°äºå¤§éç 0X20 0X00ï¼è¿æ£æ¯å ä½ç¬¦å·ï¼å®é æç¨çæ°æ®ä¹å°±åªæ 0x45 0x00 è¿ä¸¤ä¸ªåèäºï¼è 0x45 æ£æ¯æ们ç大ååæ¯ Eï¼ä¹å°±æ¯æ°æ®ä¸ç eï¼è¿åæ¯è¾å符éæå ³ãè¿éç 0X20 0X00 å ç¨äºå¤§éç空é´ï¼æ们æåè®¡ç® sort å段大约为 1200 åèï¼å®é ä¸åªæå°éçå 个åèæç¨ã
è¿éå¯¹äº sort å段èè¨ï¼æ¯å®é åå¨çæ°æ®å¤§å¾å¤ã
- ä¾å 2(å段é½æ¯ varchar(20))ï¼å®å°ä½¿ç¨ modified filesort algorithmï¼æç»åºè¯¥åå¨çæ¯ sort å段(a2,a3)+ addon å段(éè¦çå段ï¼è¿éå°±æ¯ a1,a2,a3)
æåºçç»æå¦ä¸ï¼
mysql> select* from test.tests2 where a1='b' order by a2,a3;+------+------+------+| a1 | a2 | a3 |+------+------+------+| b | d | d || b | e | e || b | f | f |+------+------+------+æ们以第ä¸è¡ä¸ºæ¥çç®æ
è¿éæ°æ®ä¸å¤§ï¼éè¿å缩ååªæ 91 个åèäºï¼æ们æ´ä½æ¥çå¦ä¸ï¼
(gdb) p rec_sz$6 = 91(gdb) x/91x start_of_rec0x7ffe7c991bc0: 0x01 0x00 0x44 0x00 0x20 0x00 0x20 0x000x7ffe7c991bc8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7c991bd0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7c991bd8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7c991be0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x000x7ffe7c991be8: 0x20 0x01 0x00 0x44 0x00 0x20 0x00 0x200x7ffe7c991bf0: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x200x7ffe7c991bf8: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x200x7ffe7c991c00: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x200x7ffe7c991c08: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x200x7ffe7c991c10: 0x00 0x20 0x07 0x00 0x00 0x01 0x62 0x010x7ffe7c991c18: 0x64 0x01 0x64
è¿å°±æ¯æ´è¡è®°å½äºï¼æ们åç°å¯¹äº sort å段èè¨æ²¡æå缩ï¼ä¾æ§æ¯ 0x20 0x00 å ä½ï¼èå¯¹äº addon å段(éè¦çå段ï¼è¿éå°±æ¯ a1,a2,a3)èè¨ï¼è¿éå°äºå¾å¤ï¼å 为åäºæå (pack)å³ï¼
0x01 0x62ï¼æ°æ® b 0x01 0x64ï¼æ°æ® d 0x01 0x64ï¼æ°æ® d è 0x01 åºè¯¥å°±æ¯é¿åº¦äºã
ä¸ç®¡æä¹è¯´ï¼å¯¹äº sort å段èè¨ä¾æ§æ¯å®é åå¨çæ°æ®å¤§å¾å¤ã
5ãå¦æ sort buffer å满ï¼å¯¹ sort buffer ä¸çæ°æ®è¿è¡æåºï¼ç¶ååå ¥å°ä¸´æ¶æ件ã
å¦æéè¦æåºçæ°æ®éå¾å¤§çè¯ï¼é£ä¹ sort buffer è¯å®æ¯ä¸è½å®¹ä¸çï¼å æ¤å¦æå满åå°±è¿è¡ä¸æ¬¡å åæåºæä½ï¼ç¶åå°æåºå¥½çæ°æ®åå ¥å°å¤é¨æåºæ件ä¸å»ï¼è¿å«åä¸ä¸ª chunkãå¤é¨æ件çä½ç½®ç± tmpdir åæ°æå®ï¼åå以 MY å¼å¤´ï¼æ³¨æå¤é¨æåºé常éè¦ 2 个临æ¶æ件ï¼è¿éæ¯ç¬¬ 1 个ç¨äºåå¨å åæåºç»æç临æ¶æ件ï¼ä»¥ chunk çæ¹å¼åå ¥ãå¦ä¸ï¼
if(fs_info->isfull()) //å¦æsort bufferæ»¡äº å¹¶ä¸sort bufferå·²ç»æåºå®æ{if(write_keys(param, fs_info, idx, chunk_file, tempfile)) //åå
¥å°ç©çæ件 å®æå
åæåº å¦æå
åä¸ä¼æ»¡è¿éä¸ä¼å ä¼å¨create_sort_index ä¸æåºå®æ{ num_records= HA_POS_ERROR;goto cleanup;} idx= 0; indexpos++;}
æç»ä¼è°å ¥ write_keys å½æ°è¿è¡æåºååå ¥å¤é¨æåºæ件ï¼è¿éæ ¸å¿å°±æ¯å æåºï¼ç¶å循ç¯æ¯æ¡æåºæ件åå ¥å°å¤é¨æåºæ件ãä¸é¢ææ¥éªè¯ä¸ä¸åå ¥ä¸´æ¶æ件çé¿åº¦ï¼æå°ç¬¬äºé¨åä¸çä¾å 2 æ°æ®æ©å¤§äº N ååï¼è®©å ¶ä½¿ç¨å¤é¨æ件æåºï¼ä¸é¢æ¯éªè¯ç»æï¼æç¹ write_keys å³å¯ï¼
1161if(my_b_write(tempfile, record, rec_length))(gdb) p rec_length$8 = 91
å¯ä»¥æ¯è¡çé¿åº¦è¿æ¯ 91 åè(æå å缩å)ï¼ååé¢çå°çé¿åº¦ä¸è´ï¼è¯´æè¿äºæ°æ®ä¼å®å®æ´æ´çåå ¥å°å¤é¨æåºæ件ï¼è¿æ¾ç¶ä¼æ¯æ们æ³è±¡ç大å¾å¤ã
好äºå°è¿éæ°æ®å·²ç»æ¾åºæ¥äºï¼å¦æè¶ è¿ sort buffer ç大å°ï¼å¤é¨æåºéè¦çç»æå·²ç»åå¨å¨ä¸´æ¶æ件 1 äºï¼å¹¶ä¸å®æ¯åç(chunk)åå¨å°ä¸´æ¶æ件çï¼å®ä»¥ MY å¼å¤´ã
ä¹ãé¶æ®µ 7ï¼æåºæ¹å¼æ»ç»è¾åº
è¿é对ä¸é¢çæåºè¿ç¨åäºä¸ä¸ªé¶æ®µæ§çæ»ç»ï¼ä»£ç å¦ä¸ï¼
Opt_trace_object(trace, "filesort_summary").add("rows", num_rows).add("examined_rows", param.examined_rows).add("number_of_tmp_files", num_chunks).add("sort_buffer_size", table_sort.sort_buffer_size()).add_alnum("sort_mode", param.using_packed_addons() ?"": param.using_addon_fields() ?"": "");
æ们解æä¸ä¸ï¼
- rowsï¼æåºçè¡æ°ï¼ä¹å°±æ¯åºç¨ where è¿æ»¤æ¡ä»¶åå©ä¸çè¡æ°ã
- examined_rowsï¼Innodb å±æ«æçè¡æ°ï¼æ³¨æè¿ä¸æ¯æ ¢æ¥è¯¢ä¸ç Rows_examinedï¼è¿éæ¯åç¡®çç»æï¼æ²¡æéå¤è®¡æ°ã
- number_of_tmp_filesï¼å¤é¨æåºæ¶ï¼ç¨äºä¿åç»æç临æ¶æ件ç chunk æ°éï¼æ¯æ¬¡ sort buffer 满æåºååå ¥å°ä¸ä¸ª chunkï¼ä½æ¯ææ chunk å ±ååå¨äºä¸ä¸ªä¸´æ¶æ件ä¸ã
- sort_buffer_sizeï¼å é¨æåºä½¿ç¨çå å大å°ï¼å¹¶ä¸ä¸å®æ¯ sort_buffer_size åæ°æå®ç大å°ã
- sort_modeï¼è¿é解éå¦ä¸
1ãsort_key, packed_additional_fieldsï¼ä½¿ç¨äº modified filesort algorithm(ä¸å表æåº)ï¼å¹¶ä¸ææå (pack)çå段ï¼é常为å¯åå段æ¯å¦ varcharã
2ãsort_key, additional_fieldsï¼ä½¿ç¨äº modified filesort algorithm(ä¸å表æåº)ï¼ä½æ¯æ²¡æéè¦æå (pack)çå段ï¼æ¯å¦é½æ¯åºå®é¿åº¦å段ã
3ãsort_key, rowidï¼ä½¿ç¨äº original filesort algorithm(å表æåº)ã
åãé¶æ®µ 8ï¼è¿è¡æç»æåº
è¿éæ¶å 2 个é¨åå¦ä¸ï¼
- å¦æ sort buffer ä¸æ»¡ï¼åè¿éå¼å§è¿è¡æåºï¼è°å ¥å½æ° save_indexã
- å¦æ sort buffer 满äºï¼åè¿è¡å½å¹¶æåºï¼è°å ¥å½æ° merge_many_buff->merge_buffersï¼æåè°å ¥ merge_index å®æå½å¹¶æåºã
对äºå½å¹¶æåºæ¥è®²ï¼è¿éå¯è½ä¼çæå¦å¤ 2 个临æ¶æ件ç¨äºåå¨æç»æåºçç»æï¼å®ä»¬ä¾ç¶ä»¥ MY å¼å¤´ï¼ä¸ä¾ç¶æ¯åå¨å¨ tmpdir åæ°æå®çä½ç½®ãå æ¤å¨å¤é¨æåºä¸å°å¯è½ä¼çæ 3 个临æ¶æ件ï¼æ»ç»å¦ä¸ï¼
- 临æ¶æ件 1ï¼ç¨äºåå¨å åæåºçç»æï¼ä»¥ chunk 为åä½ï¼ä¸ä¸ª chunk ç大å°å°±æ¯ sort buffer ç大å°ã
- 临æ¶æ件 2ï¼ä»¥åé¢ç临æ¶æ件 1 为åºç¡ï¼åå½å¹¶æåºã
- 临æ¶æ件 3ï¼å°æåçå½å¹¶æåºç»æåå¨ï¼å»æ sort å段ï¼åªä¿ç addon å段(éè¦è®¿é®çå段)æè ref å段(ROWID æè 主é®)ï¼å æ¤å®ä¸è¬ä¼æ¯åé¢ 2 个临æ¶æ件å°ã
ä½æ¯å®ä»¬ä¸ä¼åæ¶åå¨ï¼è¦ä¹ 临æ¶æ件 1 å临æ¶æ件 2 åå¨ï¼è¦ä¹ 临æ¶æ件 2 å临æ¶æ件 3 åå¨ã
è¿ä¸ªå¾å®¹æéªè¯ï¼å°æç¹æ¾å° merge_buffers å merge_index ä¸å°±å¯ä»¥éªè¯äºï¼å¦ä¸ï¼
临æ¶æ件1å临æ¶æ件2åæ¶åå¨ï¼[[email protected] test]# lsof|grep tmp/MYmysqld 8769 mysql 70u REG 252,3 79167488 2249135/mysqldata/mysql3340/tmp/MYt1QIvr(deleted)mysqld 8769 mysql 71u REG 252,3 58327040 2249242/mysqldata/mysql3340/tmp/MY4CrO4m (deleted)临æ¶æ件2å临æ¶æ件3å
±ååå¨ï¼[[email protected] test]# lsof|grep tmp/MYmysqld 8769 mysql 70u REG 252,3 360448 2249135/mysqldata/mysql3340/tmp/MYg109Wp(deleted)mysqld 8769 mysql 71u REG 252,3 79167488 2249242/mysqldata/mysql3340/tmp/MY4CrO4m (deleted)
ä½æ¯ç±äºè½åæé对äºå½å¹¶æåºçå ·ä½è¿ç¨æ并没æä»ç»å¦ä¹ äºï¼è¿éç»ä¸ä¸ªå¤§æ¦çæ¥å£ã
注æè¿éæ¯æ¬¡è°ç¨ merge_buffers å°ä¼å¢å Sort_merge_passes 1 次ï¼åºè¯¥æ¯å½å¹¶ç次æ°ï¼è¿ä¸ªå¼å¢éç大å°å¯ä»¥ä¾§é¢åæ åºå¤é¨æåºä½¿ç¨ä¸´æ¶æ件ç大å°ã
åä¸ãæåºçå ¶ä»é®é¢
è¿éå°æè¿° 2 个é¢å¤çæåºé®é¢ã
1ãoriginal filesort algorithm(å表æåº) çå表
æåå¯¹äº original filesort algorithm(å表æåº) æåºæ¹å¼èè¨ï¼å¯è½è¿éè¦åä¸ä¸ªå表è·åæ°æ®çæä½ï¼è¿ä¸æ¥å¯è½ä¼ç¨å°åæ° read_rnd_buffer_size å®ä¹çå å大å°ã
æ¯å¦æ们第äºé¨åä¸ç¬¬ 1 个ä¾åå°ä¼ä½¿ç¨å° original filesort algorithm(å表æåº)ï¼ä½æ¯å¯¹äºå表æä½æå¦ä¸æ åï¼
- å¦æ没æ使ç¨å°å¤é¨æåºä¸´æ¶æ件å说ææåºéä¸å¤§ï¼å使ç¨æ®éçå表æ¹å¼ï¼è°å ¥å½æ° rr_from_pointersï¼ä¹å°±æ¯åè¡å表æ¹å¼ã
- å¦æ使ç¨å°äºå¤é¨æåºä¸´æ¶æ件å说ææåºéè¾å¤§ï¼éè¦ä½¿ç¨å°æ¹éå表æ¹å¼ï¼è¿ä¸ªæ¶å大æ¦çæ¥éª¤å°±æ¯è¯»å rowid(主é®) æåºï¼ç¶åæ¹éå表ï¼è¿å°ä¼å¨ read_rnd_buffer_size æå®çå åä¸å®æï¼è°å ¥å½æ° rr_from_cacheãè¿ä¹æ¯ä¸ç§ä¼åæ¹å¼ï¼å 为å表ä¸è¬æ¯æ£åçï¼ä»£ä»·å¾å¤§ã
2ãå ³äºæåºä¸ Rows_examined ç计ç®
é¦å è¿ä¸ªå¼æ说çæ¯æ ¢æ¥è¯¢çä¸ç Rows_examinedï¼å¨æåºä¸ä¼åºç°éå¤è®¡æ°çå¯è½ï¼åé¢ç¬¬å «é¨åå·²ç»è¯´æäºä¸ä¸ï¼è¿ä¸ªå¼å¨ç¬¬å «é¨åè¿æ¯æ£ç¡®çï¼ä½æ¯æå符å where æ¡ä»¶çæ°æ®å¨è¿åçæ¶åè¿ä¼è°ç¨å½æ° evaluate_join_recordï¼ç»æ Rows_examined ä¼å¢å 符å where æ¡ä»¶çè¡æ°ãè¿æ¯ä»¥æ们第äºé¨åç两个ä¾å为ä¾ï¼
mysql> select* from test.tests1 where a1='b' order by a2,a3;+------+------+------+| a1 | a2 | a3 |+------+------+------+| b | d | d || b | e | e || b | f | f |+------+------+------+3 rows inset(5.11 sec)mysql> select* from test.tests2 where a1='b' order by a2,a3;+------+------+------+| a1 | a2 | a3 |+------+------+------+| b | d | d || b | e | e || b | f | f |+------+------+------+3 rows inset(5.28 sec)mysql> desc select* from tests2 where a1='b' order by a2,a3;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row inset, 1 warning (0.00 sec)8 rows inset(0.00 sec)mysql> desc select* from tests2 where a1='b' order by a2,a3;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row inset, 1 warning (0.01 sec)
æ ¢æ¥è¯¢å¦ä¸ï¼ä¸è¦çº ç»æ¶é´(å 为ææ æ debug åæ¢äºä¸ä¼)ï¼æ们åªå ³æ³¨ Rows_examinedï¼å¦ä¸ï¼
# Time: 2019-12-23T12:03:26.108529+08:00# [email protected]: root[root] @ localhost [] Id: 4# Schema: Last_errno: 0 Killed: 0# Query_time: 5.118098 Lock_time: 0.000716 Rows_sent: 3 Rows_examined: 11 Rows_affected: 0# Bytes_sent: 184SET timestamp=1577073806;select* from test.tests1 where a1='b' order by a2,a3;# Time: 2019-12-23T12:03:36.138274+08:00# [email protected]: root[root] @ localhost [] Id: 4# Schema: Last_errno: 0 Killed: 0# Query_time: 5.285573 Lock_time: 0.000640 Rows_sent: 3 Rows_examined: 11 Rows_affected: 0# Bytes_sent: 184SET timestamp=1577073816;select* from test.tests2 where a1='b' order by a2,a3;
æ们å¯ä»¥çå° Rows_examined é½æ¯ 11ï¼ä¸ºä»ä¹æ¯ 11 å¢ï¼æ¾ç¶æ们è¦æ«ææ»çè¡æ°ä¸º 8(è¿éæ¯å ¨è¡¨æ«æï¼è¡¨æ»å ± 8 è¡æ°æ®)ï¼ç¶åè¿æ»¤åéè¦æåºçç»æ为 3 æ¡æ°æ®ï¼è¿ 3 æ¡æ°æ®ä¼éå¤è®¡æ°ä¸æ¬¡ãå æ¤å°±æ¯ 8 + 3 = 11ï¼ä¹å°±æ¯è¯´æ 3 æ¡æ°æ®éå¤è®¡æ°äºã
åäºãéè¿ OPTIMIZER_TRACE æ¥çæåºç»æ
è¦ä½¿ç¨ OPTIMIZER_TRACE åªéè¦âSET optimizer_trace="enabled=on";âï¼è·å®è¯å¥åæ¥ç information_schema.OPTIMIZER_TRACE å³å¯ãåé¢ç¬¬ä¹é¨åæ们解éäºæåºæ¹å¼æ»ç»è¾åºçå«ä¹ï¼è¿éæ们æ¥ççå ·ä½çç»æï¼æ们è¿æ¯ä»¥ç¬¬äºé¨åç 2 个ä¾å为ä¾ï¼
- ä¾ 1ï¼
"filesort_priority_queue_optimization": {"usable": false,"cause": "not applicable (no LIMIT)"},"filesort_execution": [],"filesort_summary": {"rows": 3,"examined_rows": 8,"number_of_tmp_files": 0,"sort_buffer_size": 1285312,"sort_mode": ""
- ä¾ 2ï¼
"filesort_priority_queue_optimization": {"usable": false,"cause": "not applicable (no LIMIT)"},"filesort_execution": [],"filesort_summary": {"rows": 3,"examined_rows": 8,"number_of_tmp_files": 0,"sort_buffer_size": 322920,"sort_mode": ""
ç°å¨æä»¬æ¸ æ¥äºï¼è¿äºæ»ç»å®é ä¸æ¯å¨æ§è¡é¶æ®µçæçï¼éè¦æ³¨æå ç¹å¦ä¸ï¼
- è¿éç examined_rows åæ ¢æ¥è¯¢ä¸ç Rows_examined ä¸ä¸æ ·ï¼å 为è¿éä¸ä¼æéå¤è®¡æ°ï¼æ¯åç¡®çã
- è¿éè¿ä¼è¯´ææ¯å¦ä½¿ç¨äºä¼å éåæåºå³âfilesort_priority_queue_optimizationâé¨åã
- éè¿âsort_buffer_sizeâå¯ä»¥åç°ï¼è¿é并没æåé åæ° sort_buffer_size æå®ç大å°ï¼è约äºå åï¼è¿å¨ç¬¬ä¸é¨å说æäºã
å ¶ä»ææ å¨ç¬¬ä¹é¨åå·²ç»è¯´æè¿äºï¼ä¸åæè¿°ã
åä¸ãåå°é®é¢æ¬èº«
好äºï¼å¤§æ¦çæµç¨ææè¿°äºä¸éï¼è¿äºæµç¨é½æ¯ä¸»è¦æµç¨ï¼å®é ä¸çæµç¨å¤æå¾å¤ãé£ä¹æ们åå°æå¼å§çæ¡ä¾ä¸æ¥ãä»ç max_sort_length å max_length_for_sort_data å为é»è®¤å¼ 1024ã
æ¡ä¾ä¸ç group by å®é å°±æ¯ä¸ä¸ªæåºæä½ï¼æ们ä»æ§è¡è®¡åå¯ä»¥çåºæ¥ï¼é£ä¹å åæä¸ä¸å®ç sort å段ãå¾æ¾ç¶ group by åçé½æ¯ sort å段ï¼å ¶ä¸å段 CREATE_ORG_NAME å ¶å®ä¹ä¸º varchar(1000)ï¼å®çå ç¨ç©ºé´ä¸º(1000 * 2)å 2000 åèï¼ä½æ¯è¶ è¿äº max_sort_length ç大å°ï¼å æ¤ä¸º 1024 åèï¼ç¸åçè¿æ UPDATE_ORG_NAME å段ä¹æ¯varchar(1000)ï¼ä¹ä¼ååæ ·å¤çï¼å ¶ä»å段ä¸ä¼è¶ è¿ max_sort_length çéå¶ï¼å¹¶ä¸å¨ç¬¬äºé¨åè¯´è¿ sort å段æ¯ä¸ä¼è¿è¡å缩çã
æ大æ¦ç®äºä¸ä¸ sort å段çå ¨é¨å¤§å°çº¦ä¸º (3900 * 2)åèï¼å¯ä»¥çå°ä¸è¡æ°æ®ç sort å段åºæ¬è¾¾å°äº 8K ç容éï¼è addon å段çé¿åº¦(æªæå å缩å)ä¼æ´å¤§ï¼æ¾ç¶è¶ è¿ max_length_for_sort_data ç设置ï¼å æ¤å¯¹äºè¿æ ·çæåºæ¾ç¶ä¸å¯è½ä½¿ç¨ modified filesort algorithm(ä¸å表æåºäº)ï¼ä½¿ç¨çæ¯ original filesort algorithm(å表æåº)ï¼å æ¤ä¸è¡çè®°å½å°±æ¯(sort å段 + 主é®)äºï¼ä¸»é®å¤§å°å¯ä»¥å¿½ç¥ï¼æç»ä¸è¡è®°å½ç大å°å°±æ¯ 8K å·¦å³ï¼è¿ä¸ªå¼é常ä¼è¿è¿å¤§äº Innodb å缩ååå¨ varchar å段ç大å°ï¼è¿ä¹æ¯ä¸ºä»ä¹æ¬ä¾ä¸è½ç¶è¡¨åªæ 30G å·¦å³ä½æ¯ä¸´æ¶æ件达å°äº 200G 以ä¸çåå äºã
好äºï¼æ们æ¥éç°ä¸ä¸é®é¢ï¼æ们使ç¨ç¬¬äºé¨åçä¾ 1ï¼æ们å°å ¶æ°æ®å¢å¤ï¼åçä¸æ们çä¾ 1ä¼ä½¿ç¨å° original filesort algorithm(å表æåº) çæ¹å¼ï¼å 为è¿é sort å段(a2,a3)çæ»é¿åº¦ + addon å段(a1,a2,a3)çé¿åº¦çº¦ä¸º 300 * 2 * 2 + 300 * 3 * 3 è¿æ¾ç¤ºå¤§äºäº max_length_for_sort_data çé¿åº¦ï¼ å æ¤è¿ä¸ªæåºä¸è¡çé¿åº¦å°±æ¯ sort å段(a2,a3)+ ref å段(ROWID)ï¼å¤§çº¦å°±æ¯ 300 * 2 * 2 + 6 =1 206 åèäºãä¸é¢æ¯è¿ä¸ªè¡¨çæ»æ°æ®å Innodb æ件大å°(æè¿éå«å bgtest5 表)ï¼
mysql> show create table bgtest5 G*************************** 1. row ***************************Table: bgtest5CreateTable: CREATE TABLE `bgtest5`(`a1` varchar(300) DEFAULT NULL,`a2` varchar(300) DEFAULT NULL,`a3` varchar(300) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row inset(0.01 sec)mysql> SELECT COUNT(*) FROM bgtest5;+----------+| COUNT(*) |+----------+| 65536|+----------+1 row inset(5.91 sec)mysql> desc select* from bgtest5 order by a2,a3;+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+| 1| SIMPLE | bgtest5 | NULL | ALL | NULL | NULL | NULL | NULL | 66034| 100.00| Using filesort |+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+1 row inset, 1 warning (0.00 sec)
注æè¿éæ¯å ¨è¡¨æåºäºï¼æ²¡æ where è¿æ»¤æ¡ä»¶äºï¼ä¸é¢æ¯è¿ä¸ªè¡¨ ibd æ件ç大å°ï¼
[[email protected] test]# du -hs bgtest5.ibd11M bgtest5.ibd[[email protected] test]#
ä¸é¢æ们就éè¦å° gdb çæç¹æå¨ merge_many_buffï¼æ们çç®çå°±æ¯è§å¯ä¸´æ¶æ件 1 ç大å°ï¼è¿ä¸ªæ件åé¢è¯´è¿äºæ¯åå¨å åæåºç»æçï¼å¦ä¸ï¼
[[email protected] test]# lsof|grep tmp/MYmysqld 8769 mysql 69u REG 252,3 79101952 2249135/mysqldata/mysql3340/tmp/MYzfek5x(deleted)
å¯ä»¥çå°è¿ä¸ªæ件ç大å°ä¸º 79101952 åèï¼å³ 80M å·¦å³ï¼è¿åæ们计ç®çæ»é 1206(æ¯è¡å¤§å°) * 65535(è¡æ°) 约为 80M ç»æä¸è´ãè¿è¿è¿è¶ è¿äº ibd æ件çå¤§å° 11Mï¼å¹¶ä¸è¦ç¥éï¼éåè¿ä¼çæä¸ä¸ªå¤§å°å·®ä¸å¤çæ件æ¥åå¨å½å¹¶æåºçç»æå¦ä¸ï¼
[[email protected] test]# lsof|grep tmp/MYmysqld 8769 mysql 69u REG 252,3 79167488 2249135/mysqldata/mysql3340/tmp/MYzfek5x(deleted)mysqld 8769 mysql 70u REG 252,3 58327040 2249242/mysqldata/mysql3340/tmp/MY8UOLKa (deleted)
å æ¤å¾å°è¯æï¼æåºç临æ¶æ件è¿è¿å¤§äº ibd æ件çç°è±¡æ¯å¯è½åºç°çã
ååãå ¨ææ»ç»
æ¬æåäºå¾å¤ï¼è¿ééè¦åä¸ä¸ªè¯¦ç»çæ»ç»ï¼
æ»ç» 1ï¼æåºä¸ä¸è¡è®°å½å¦ä½ç»ç»ï¼
- ä¸è¡æåºè®°å½ï¼ç± sort å段 + addon å段ç»æï¼å ¶ä¸ sort å段为 order by åé¢çå段ï¼è addon å段为éè¦è®¿é®çå段ï¼æ¯å¦âselect a1,a2,a3 from test order by a2,a3âï¼å ¶ä¸ sort å段为âa2,a3âï¼addon å段为âa1,a2,a3âãsort å段ä¸çå¯åé¿åº¦å段ä¸è½æå (pack)å缩ï¼æ¯å¦ varcharï¼ä½¿ç¨çæ¯å®ä¹ç大å°è®¡ç®ç©ºé´ï¼æ³¨æè¿æ¯æåºä½¿ç¨ç©ºé´è¾å¤§çä¸ä¸ªéè¦å ç´ ã
- å¦æå¨è®¡ç® sort å段空é´çæ¶åï¼æ个å段ç空é´å¤§å°å¤§äºäº max_sort_length 大å°åæç § max_sort_length æå®ç大å°è®¡ç®ã
- ä¸è¡æåºè®°å½ï¼å¦æ sort å段 + addon å段çé¿åº¦å¤§äºäº max_length_for_sort_data ç大å°ï¼é£ä¹ addon å段å°ä¸ä¼åå¨ï¼èä½¿ç¨ sort å段 + ref å段代æ¿ï¼ref å段为主é®æè ROWIDï¼è¿ä¸ªæ¶åå°±ä¼ä½¿ç¨ original filesort algorithm(å表æåº) çæ¹å¼äºã
- å¦æ addon å段å å«å¯åå段æ¯å¦ varchar å段ï¼åä¼ä½¿ç¨æå (pack)ææ¯è¿è¡å缩ï¼èç空é´ãå¯ä»¥åè第ä¸ã第åã第äºã第å ãç¬¬å «é¨åã
æ»ç» 2ï¼æåºä½¿ç¨ä»ä¹æ ·çæ¹æ³è¿è¡ï¼
- original filesort algorithm(å表æåº)
å¦æ使ç¨çæ¯ sort å段 + ref å段è¿è¡æåºï¼é£ä¹å¿ é¡»è¦å表è·åéè¦çæ°æ®ï¼å¦ææåºä½¿ç¨äºä¸´æ¶æ件(ä¹å°±æ¯è¯´ä½¿ç¨å¤é¨å½å¹¶æåºï¼æåºéè¾å¤§)åä¼ä½¿ç¨æ¹éå表ï¼æ¹éå表ä¼æ¶åå° read_rnd_buffer_size åæ°æå®çå å大å°ï¼ä¸»è¦ç¨äºæåºåç»æè¿åã
å¦ææåºæ²¡æ使ç¨ä¸´æ¶æ件(å åæåºå°±å¯ä»¥å®æï¼æåºéè¾å°)åéç¨åè¡å表ã
- modified filesort algorithm(ä¸å表æåº)
å¦æ使ç¨çæ¯ sort å段 + addon å段è¿è¡æåºï¼é£ä¹ä½¿ç¨ä¸å表æåºï¼ææéè¦çå段åå¨æåºè¿ç¨ä¸è¿è¡åå¨ï¼addon å段ä¸çå¯åé¿åº¦å段å¯ä»¥è¿è¡æå (pack)å缩èç空é´ã
å ¶æ¬¡ sort å段å addon å段ä¸å¯è½æéå¤çå段ï¼æ¯å¦ä¾ 2 ä¸ï¼sort å段为 a2ãa3ï¼addon å段为 a1ãa2ãa3ï¼è¿æ¯æåºä½¿ç¨ç©ºé´è¾å¤§çå¦å¤ä¸ä¸ªåå ã
å¨ OPTIMIZER_TRACE ä¸å¯ä»¥æ¥çå°ä½¿ç¨äºé£ç§æ¹æ³ï¼åè第åäºé¨åã
æ»ç» 3ï¼æ¯æ¬¡æåºä¸å®ä¼åé sort_buffer_size åæ°æå®çå å大å°åï¼
ä¸æ¯è¿æ ·çï¼MySQL ä¼åä¸ä¸ªåæ¥ç计ç®ï¼éè¿æ¯è¾ Innodb ä¸èéç´¢å¼å¯è½åå¨çè¡ä¸éå sort_buffer_size åæ°æå®å¤§å°å åå¯ä»¥å®¹çº³çè¡ä¸éï¼è·åå®ä»¬å°å¼è¿è¡ç¡®è®¤æç»å ååé ç大å°ï¼ç®çå¨äºèçå å空é´ã
å¨ OPTIMIZER_TRACE ä¸å¯ä»¥çå°ä½¿ç¨çå å大å°ï¼åèç¬¬å «ã第åäºé¨åã
æ»ç» 4ï¼å ³äº OPTIMIZER_TRACE ä¸ç examined_rows åæ ¢æ¥è¯¢ä¸ç Rows_examined æä»ä¹åºå«ï¼
- æ ¢æ¥è¯¢ä¸ç Rows_examined å å«äºéå¤è®¡æ°ï¼éå¤çé¨å为 where æ¡ä»¶è¿æ»¤ååäºæåºçé¨åã
- OPTIMIZER_TRACE ä¸ç examined_rows ä¸å å«éå¤è®¡æ°ï¼ä¸ºå®é Innodb å±æ«æçè¡æ°ã
å¯ä»¥åè第åä¸é¨åã
æ»ç» 5ï¼å¤é¨æåºä¸´æ¶æ件ç使ç¨æ¯ä»ä¹æ ·çï¼
å®é ä¸ä¸ä¸ªè¯å¥ç临æ¶æ件ä¸æ¢ä¸ä¸ªï¼ä½æ¯å®ä»¬é½ä»¥ MY å¼å¤´ï¼å¹¶ä¸é½æ¾å°äº tmpdir ç®å½ä¸ï¼lsof å¯ä»¥çå°è¿ç§æ件ã
- 临æ¶æ件 1ï¼ç¨äºåå¨å åæåºçç»æï¼ä»¥ chunk 为åä½ï¼ä¸ä¸ª chunk ç大å°å°±æ¯ sort buffe rç大å°ã
- 临æ¶æ件 2ï¼ä»¥åé¢ç临æ¶æ件 1 为åºç¡ï¼åå½å¹¶æåºã
- 临æ¶æ件 3ï¼å°æåçå½å¹¶æåºç»æåå¨ï¼å»æ sort å段ï¼åªä¿ç addon å段(éè¦è®¿é®çå段)æè ref å段(ROWID æè 主é®)ï¼å æ¤å®ä¸è¬ä¼æ¯åé¢ 2 个临æ¶æ件å°ã
ä½æ¯å®ä»¬ä¸ä¼åæ¶åå¨ï¼è¦ä¹ä¸´æ¶æ件1å临æ¶æ件 2 åå¨ï¼è¦ä¹ä¸´æ¶æ件 2 å临æ¶æ件 3 åå¨ã对äºä¸´æ¶æ件ç使ç¨å¯ä»¥æ¥ç Sort_merge_passesï¼æ¬å¼å¤å°ä¼ä¾§é¢ååºåºå¤é¨æåºéç大å°ã
å¯ä»¥åè第åé¨åã
æ»ç» 6ï¼æåºä½¿ç¨äºåªç§ç®æ³ï¼
è½ç¶æ¬æä¸æ¶åç®æ³ï¼ä½æ¯å é¨æåºæ 2 ç§ç®æ³éè¦ç¥éï¼
- å åæåº(ä¼å éå order by limit è¿åå°éè¡å¸¸ç¨ï¼æé«æåºæçï¼ä½æ¯æ³¨æ order by limit n,m å¦æ n è¿å¤§å¯è½ä¼æ¶åå°æåºç®æ³çåæ¢)
- å åæåº(å¿«éæåº) å¨éè¿ OPTIMIZER_TRACE å¯ä»¥æ¥çæ¯å¦ä½¿ç¨ä½¿ç¨äºä¼å éåç®æ³ï¼åè第åäºé¨åã
æ»ç» 7ï¼âCreating sort indexâå°åºæ¯ä»ä¹ç¶æï¼
æ们åé¢è®²çå ¨é¨æåºæµç¨é½ä¼å å«å¨è¿ä¸ªç¶æä¸ï¼å æ¬ï¼
- è·åæåºéè¦çæ°æ®(æ¯å¦ä¾åä¸å ¨è¡¨æ«æä» Innodb å±è·åæ°æ®)
- æ ¹æ® where æ¡ä»¶è¿æ»¤æ°æ®
- å åæåº
- å¤é¨æåº
æ»ç» 8ï¼å¦ä½é¿å 临æ¶æ件è¿å¤§çæ åµï¼
é¦å åºè¯¥èèæ¯å¦å¯ä»¥ä½¿ç¨ç´¢å¼æ¥é¿å æåºï¼å¦æä¸è½åéè¦èèä¸é¢çè¦ç¹ï¼
- order by åé¢çå段满足éæ±å³å¯ï¼å°½å¯è½çå°ã
- order by åé¢æ¶åçå段尽é为åºå®é¿åº¦çå段类åï¼èä¸æ¯å¯åå段类åå¦ varcharãå 为 sort å段ä¸è½å缩ã
- ä¸è¦è¿å¤§çå®ä¹å¯åå段é¿åº¦ï¼åºè¯¥åçå®ä¹ï¼ä¾å¦ varchar(10) è½å¤æ»¡è¶³éæ±ä¸è¦ä½¿ç¨ varchar(50)ï¼è¿äºç©ºé´è½ç¶å¨ Innodb å±åå¨ä¼å缩ï¼ä½æ¯ MySQL å±ç¡®å¯è½ä½¿ç¨å ¨é¿åº¦(æ¯å¦ sort å段)ã
- å¨æ¥è¯¢ä¸å°½éä¸è¦ç¨(select *) è使ç¨éè¦æ¥è¯¢çå段ï¼è¿å°ä¼åå° addon å段ç个æ°ï¼å¨æå¦å¤ä¸ä¸ªæç« è¿è®²è¿°äº(select *)çå ¶ä»ç缺ç¹
åèï¼https://www.jianshu.com/p/ce063e2024ad
æåæ¨èé«é¹çä¸æ ãæ·±å ¥ç解 MySQL 主ä»åç 32 讲ãï¼æ³è¦éå½»äºè§£å¦ä¹ MySQL 主ä»åççæåä¸å®¹éè¿ã