1/10/2024 0 Comments Mysql optimizer cost modelYour ultimate goal is to submit SELECT Statement to find data rows, Instead of excluding data rows. MySQL The query optimizer has several goals, But the main goal is to use indexes as much as possible, And use the strictest index to eliminate as many rows of data as possible. MySQL 8.0 New features use_invisible_indexes: Whether to use invisible index ,MySQL 8.0 You can create invisible Indexes, This switch controls whether the optimizer uses invisible Indexes ,on. MySQL Optimizer function switch optimizer_switch.The longer you work, the more problems you have, The more I feel “ Saying ” unfading : Because the recent study plan is to study in depth MySQL Optimizer : Some achievements in the learning process It will also be posted here, One is to sort out what you already know and what you have learned. Refer to the last part of this article for table structure and data preparation ". 0104 To explore the MySQL How does the optimizer evaluate indexes and JOIN The choice of orderįrom, Thank you, blogger This paper takes a case study to see MySQL How the optimizer selects indexes and JOIN The order.MySQL Optimizer cost More articles on Computing The next experiment is to be continued : Relational query. Therefore ,innodb Back to found_rows=6556( Contains the scan index xpchild_id_c1 and primary key Of ), So the final calculation is cost More than using pk Of cost.įinally calculated cost=7868.21 Far above pk Indexed cost.Ĭonclusion : Finally, I chose pk The index of range scanning Calculate the general index of costīecause you can use the leading column to query, The difference in query efficiency is that the non primary key index needs to go back to the clustered index to query the non indexed columns. Such, adopt pk Scanned cost Far less than the cost of full table scan in the first stage. Io_cost = param->table->file->read_time(keynr,param->range_count,found_records) įound_read_time = cpu_cost + io_cost + 0.01 Get_index_only_read_time: Here, if there is an overlay index (covering index) Then the coverage index is calculated cost.įull index scan The calculation method of :Ĭpu_cost= (double) found_records / TIME_FOR_COMPARE In the overlay index length The shortest one. Get_key_scans_params: according to sel_tree Find better costįind_shortest_key: Select. Get_best_group_min_max: I didn't use it here The total cost:read_time= (double) head->file->scan_time() + scan_time + 1.1 Īccording to possible index, Generate sel_tree Scan_time= (double) records / TIME_FOR_COMPARE + 1 Innodb scan time:prebuilt->table->stat_clustered_index_sizeīe equal to innodb The clustered index of this table is page Number, In itself innodb Cluster index table, It's calculated here io cost=16 Innodb Of io cost: s->read_time=(ha_rows) s->table->file->scan_time() Calculate the number of full table scans cost: Step1:update_ref_and_keys: I got two in all possible index,ġ. S->found_records=s->records=s->read_time=1 s->worst_seeks=1.0 Ĭonclusion : single key Query for ,join_tab Of type=JT_CONST, Indexes record and read time All are 1 resulting join->best_read=1.0 experiment 2: Range queriesĮxplain select * from xpchild where id > 100 If (s->type = JT_SYSTEM || s->type = JT_CONST) Table->quick_condition_rows= table->file->stats.records Ĭhoose plan: choice join The order of Experimental processĮxplain select * from xpchild where id =100 test_quick_select: Evaluate every one of them join table The number of records from the query, The comparison is different index Of cost, Number of records returned, Choose the best one. update_ref_and_keys: Add indexes that you can use. server Layer Statistics, Save in table_share in.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |