本文详细介绍了MySQL从5.5版本开始的行数估计算法，该算法在原有的基础上增加了对页面中间区域的采样点，以更准确地估算记录范围内的行数。通过分析`btr_estimate_n_rows_in_range_on_level`函数的源码，可以看到算法如何计算第一个和最后一个页面的记录数，并在页面数量超过一定限制时进行平均值估算。
（1）5.5之前
Rows = ((Records_PLeft + Records_PRight)/2)*Page_Num
Records_PLeft是指记录存在的第一个page上的记录数Records_PRight是指记录存在的最后一个page上的记录数Page_Num是指存在数据的page数量（2）5.5及之后Rows = ((Records_PLeft + Records_P1 + Records_P2 + ... + Records_P10 + Records_PRight)/10)*Page_Num改进后的行数记录方式，除了第一个和最后一个page之外，增加了对左侧的1到10个的采样点。（如左侧小于11个页，则计算第一个页到最后一个页之间的行数。具体的mysql 5.5源码：3233 static 3234 ib_int64_t 3235 btr_estimate_n_rows_in_range_on_level( //这个函数是用于估计记录所在的第一个page到最后一个page之间的行数 3236 /*==================================*/ 3237 dict_index_t* index, /*!< in: index */ 3238 btr_path_t* slot1, /*!< in: left border */ 3239 btr_path_t* slot2, /*!< in: right border */ 3240 ib_int64_t n_rows_on_prev_level, /*!< in: number of rows //行数的返回值 3241 on the previous level for the 3242 same descend paths; used to 3243 determine the numbe of pages 3244 on this level */ 3245 ibool* is_n_rows_exact) /*!< out: TRUE if the returned 3246 value is exact i.e. not an 3247 estimation */ 3248 { 3249 ulint space; 3250 ib_int64_t n_rows; 3251 ulint n_pages_read; 3252 ulint page_no; 3253 ulint zip_size; 3254 ulint level; 3255 3256 space = dict_index_get_space(index); 3257 3258 n_rows = 0; 3259 n_pages_read = 0; 3260 3261 /* Assume by default that we will scan all pages between 3262 slot1->page_no and slot2->page_no */ 3263 *is_n_rows_exact = TRUE; 3264 3265 /* add records from slot1->page_no which are to the right of 3266 the record which serves as a left border of the range, if any */ 3267 if (slot1->nth_rec < slot1->n_recs) { //计算第一个page中行数 3268 n_rows += slot1->n_recs - slot1->nth_rec; 3269 } 3270 3271 /* add records from slot2->page_no which are to the left of 3272 the record which servers as a right border of the range, if any */ 3273 if (slot2->nth_rec > 1) { //加上最后一个page中的行数 3274 n_rows += slot2->nth_rec - 1; 3275 } 3276 3277 /* count the records in the pages between slot1->page_no and 3278 slot2->page_no (non inclusive), if any */ 3279 3280 zip_size = fil_space_get_zip_size(space); 3281 3282 /* Do not read more than this number of pages in order not to hurt 3283 performance with this code which is just an estimation. If we read 3284 this many pages before reaching slot2->page_no then we estimate the 3285 average from the pages scanned so far */ 3286 # define N_PAGES_READ_LIMIT 10 //限制读取的page数量为10个 3287 3288 page_no = slot1->page_no; 3289 level = slot1->page_level; //找到slot1的下一个page 3290 3291 do { 3292 mtr_t mtr; 3293 page_t* page; 3294 buf_block_t* block; 3295 3296 mtr_start(&mtr); 3297 3298 /* Fetch the page. Because we are not holding the 3299 index->lock, the tree may have changed and we may be 3300 attempting to read a page that is no longer part of 3301 the B-tree. We pass BUF_GET_POSSIBLY_FREED in order to 3302 silence a debug assertion about this. */ 3303 block = buf_page_get_gen(space, zip_size, page_no, RW_S_LATCH, 3304 NULL, BUF_GET_POSSIBLY_FREED, 3305 __FILE__, __LINE__, &mtr); 3306 3307 page = buf_block_get_frame(block); 3308 3309 /* It is possible that the tree has been reorganized in the 3310 meantime and this is a different page. If this happens the 3311 calculated estimate will be bogus, which is not fatal as 3312 this is only an estimate. We are sure that a page with 3313 page_no exists because InnoDB never frees pages, only 3314 reuses them. */ 3315 if (fil_page_get_type(page) != FIL_PAGE_INDEX //该page是复用的，需要根据指针查看真正的page，将被调用3316 || btr_page_get_index_id(page) != index->id 3317 || btr_page_get_level_low(page) != level) { 3318 3319 /* The page got reused for something else */ 3320 mtr_commit(&mtr); 3321 goto inexact; 3322 } 3323 3324 /* It is possible but highly unlikely that the page was 3325 originally written by an old version of InnoDB that did 3326 not initialize FIL_PAGE_TYPE on other than B-tree pages. 3327 For example, this could be an almost-empty BLOB page 3328 that happens to contain the magic values in the fields 3329 that we checked above. */ 3330 3331 n_pages_read++; 3332 3333 if (page_no != slot1->page_no) { 3334 /* Do not count the records on slot1->page_no, 3335 we already counted them before this loop. */ 3336 n_rows += page_get_n_recs(page); 3337 } 3338 3339 page_no = btr_page_get_next(page, &mtr); 3340 3341 mtr_commit(&mtr); 3342 3343 if (n_pages_read == N_PAGES_READ_LIMIT //如果page的读取量超过10个或者树的末尾 3344 || page_no == FIL_NULL) { 3345 /* Either we read too many pages or 3346 we reached the end of the level without passing 3347 through slot2->page_no, the tree must have changed 3348 in the meantime */ 3349 goto inexact; 3350 } 3351 3352 } while (page_no != slot2->page_no); 3353 3354 return(n_rows); 3355 3356 inexact: 3357 3358 *is_n_rows_exact = FALSE; 3359 3360 /* We did interrupt before reaching slot2->page */ //在到达最后之前结束了循环 3361 3362 if (n_pages_read > 0) { 3363 /* The number of pages on this level is 3364 n_rows_on_prev_level, multiply it by the 3365 average number of recs per page so far */ 3366 n_rows = n_rows_on_prev_level //当page读取大于0时，则进行估计行数的计算 3367 * n_rows / n_pages_read; 3368 } else { 3369 /* The tree changed before we could even 3370 start with slot1->page_no */ 3371 n_rows = 10; 3372 } 3373 3374 return(n_rows); 3375 }
扫一扫
560
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
