Mysql代码学习

1. 看起来普通的inner join总是会优化成小表驱动大表。

#0  sub_select (join=0x7f490c1f3fc0, qep_tab=0x7f490c1f5608, end_of_records=false) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_executor.cc:1226
#1  0x00000000014d7648 in do_select (join=0x7f490c1f3fc0) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_executor.cc:957
#2  0x00000000014d55af in JOIN::exec (this=0x7f490c1f3fc0) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_executor.cc:206
#3  0x000000000156f095 in handle_query (thd=0x7f490c0126c0, lex=0x7f490c0149e8, result=0x7f490c04cd58, added_options=0, removed_options=0) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_select.cc:191
#4  0x0000000001524833 in execute_sqlcom_select (thd=0x7f490c0126c0, all_tables=0x7f490c04c0f0) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_parse.cc:5164
#5  0x000000000151e1ca in mysql_execute_command (thd=0x7f490c0126c0, first_level=true) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_parse.cc:2827
#6  0x00000000015257cf in mysql_parse (thd=0x7f490c0126c0, parser_state=0x7f49780c4550) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_parse.cc:5597
#7  0x000000000151b0e5 in dispatch_command (thd=0x7f490c0126c0, com_data=0x7f49780c4cb0, command=COM_QUERY) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_parse.cc:1491
#8  0x000000000151a01e in do_command (thd=0x7f490c0126c0) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/sql_parse.cc:1032
#9  0x000000000164d92e in handle_connection (arg=0x553ad00) at /home/ryan.ly/mysql-community/mysql-5.7.35/sql/conn_handler/connection_handler_per_thread.cc:313
#10 0x0000000001cdc45a in pfs_spawn_thread (arg=0x53ef690) at /home/ryan.ly/mysql-community/mysql-5.7.35/storage/perfschema/pfs.cc:2197
#11 0x00007f4996f50e25 in start_thread () from /lib64/libpthread.so.0
#12 0x00007f4995740f1d in clone () from /lib64/libc.so.6

执行select * from t1,t where a1=b;断住之后，分别打印p *qep_tab->read_record.table和p *qep_tab->read_record.table.next会发现，哪个表的数据少，哪个表在table里，而数据多的会在table.next里。不断地变换两个表的数据，总是这样的表现。

1. mysql单表上的过滤条件没有按照selectivity进行排序，而且不是按照用户写的顺序进行计算的，居然是按照反顺序计算的，很神奇，8.0也是如此。

如下所示：

select name from employees1 where BF='bf' and name='a';

实际计算的时候是先算name='a'，后算BF='bf'。

而如果改成：

select name from employees1 where name='a' and BF='bf';

实际计算的时候是先算BF='bf'，后算name='a'。

详见：

Item *substitute_for_best_equal_field(THD *thd, Item *cond,
                                      COND_EQUAL *cond_equal,
                                      JOIN_TAB **table_join_idx)
    
static Item *eliminate_item_equal(THD *thd, Item *cond,
                                  COND_EQUAL *upper_levels,
                                  Item_equal *item_equal)

eliminate_item_equal里用了个add_at_head方法，把后面的表达式加到了前面。