数据库内核月报 － 2019 / 11

  CREATE TABLE t1 (t1_1 INT, t1_2 INT);
  CREATE TABLE t2 (t2_1 INT, t2_2 INT)

  1) SELECT * FROM t1 JOIN t2 ON (t1.t1_1 = t2.t2_1);
  2) SELECT * FROM t1 JOIN t2 ON (t1.t1_1 = t2.t2_1 AND t1.t1_2 = t2.t2_2);
  3) SELECT * FROM t1 JOIN t2 ON (t1.t1_1 = t2.t2_1 AND t2.t2_2 > 43);
  4) SELECT * FROM t1 JOIN t2 ON (t1.t1_1 + t1.t1_2 = t2.t2_1);
  5) SELECT * FROM t1 JOIN t2 ON (FLOOR(t1.t1_1 + t1.t1_2) = CEIL(t2.t2_1 = 
t2.t2_2));

SELECT * FROM t1 JOIN t2 ON (t1.col1 < t2.col1);
SELECT * FROM t1
   JOIN t2 ON (t1.col1 = t2.col1)
   JOIN t3 ON (t2.col1 < t3.col1);

mysql> select * from memory_summary_global_by_event_name where event_name like "%hash_join%"\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/sql/hash_join
                 COUNT_ALLOC: 139
                  COUNT_FREE: 139
   SUM_NUMBER_OF_BYTES_ALLOC: 2577684
    SUM_NUMBER_OF_BYTES_FREE: 2577684
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 0
             HIGH_COUNT_USED: 27
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
   HIGH_NUMBER_OF_BYTES_USED: 358580
1 row in set (0.00 sec)

mysql> select * from file_summary_by_event_name where event_name like "%hash%"\G
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/sql/hash_join
               COUNT_STAR: 90
           SUM_TIMER_WAIT: 900042640
           MIN_TIMER_WAIT: 0
           AVG_TIMER_WAIT: 10000205
           MAX_TIMER_WAIT: 267632555
               COUNT_READ: 35
           SUM_TIMER_READ: 79219890
           MIN_TIMER_READ: 0
           AVG_TIMER_READ: 2263240
           MAX_TIMER_READ: 10019380
 SUM_NUMBER_OF_BYTES_READ: 271578
              COUNT_WRITE: 35
          SUM_TIMER_WRITE: 516922895
          MIN_TIMER_WRITE: 0
          AVG_TIMER_WRITE: 14769175
          MAX_TIMER_WRITE: 267632555
SUM_NUMBER_OF_BYTES_WRITE: 271578
               COUNT_MISC: 20
           SUM_TIMER_MISC: 303899855
           MIN_TIMER_MISC: 0
           AVG_TIMER_MISC: 15194860
           MAX_TIMER_MISC: 63246820
1 row in set (0.00 sec)

// Get the estimated number of rows produced by the join.
  const size_t rows_produced_by_join = QEP_TAB::->position()->prefix_rowcount;

  // Get how many rows we managed to put in the row buffer, and reduce it by a
  // given factor.
  const double reduction_factor = 0.9;
  const size_t rows_in_hash_table = row_buffer.Rows() * reduction_factor;

  // See how many rows we expect to find in the build input
  const size_t remaining_rows = rows_produced_by_join - rows_in_hash_table;

  // Finally, the number of files needed is the number of remaining rows divided
  // by the number of rows the row buffer can hold.
  const size_t files_needed = remaining_rows / rows_in_hash_table;

HashJoinRowBuffer(const TableCollection &tables, size_t max_mem_available);
//Construct a row buffer that will hold the data given by "tables", and at most "max_mem_available" bytes of data.
bool Init(std::uint32_t hash_seed);
//Initialize the row buffer with the given seed to be used in the xxHash64 hashing.
void Clear(std::uint32_t hash_seed);
//Clears the row buffer of all data.
StoreRowResult StoreRow(const std::vector<Item_func_eq *> &join_conditions);
//Store the rows that currently lies in the tables' record buffers, where the key is extracted from the given join conditions.
void LoadRange(const Key &key);
//Prepare the row buffer for reading, by loading all rows that matches the given key.
bool Next();
//Get the next matching row in the row buffer.
BufferRow *GetCurrentRow() const;
//Return a pointer to the current matching row

bool StoreFromTableBuffers(const TableCollection &tables, MEM_ROOT *mem_root);
//Takes the row that currently lies in the tables record buffers and store it in this object. The data is allocated on the supplied MEM_ROOT.
void LoadIntoTableBuffers(const TableCollection &tables);
//Takes the data in this object and puts it back to the tables record buffer.
const uchar *data() const;
//Returns a pointer to the data
size_t data_length() const;
//Returns the length of the data.

bool Init(size_t file_buffer_size);
// Initialize the chunk file, and set the IO_CACHE with a buffer size of "file_buffer_size"
ha_rows num_rows() const;
// Return the number of rows that this chunk file holds
bool WriteRowToChunk(const hash_join_buffer::TableCollection &tables);
// Write the row that lies in the tables' record buffer out to this chunk file.
void PositionFile(ha_rows row_index);
// Position the chunk file for read at the given row index.
bool PutNextRowInTableBuffers(const hash_join_buffer::TableCollection &tables);
// Take the row that the chunk file is positioned at and put it back to the tables' record buffer. The file position is advanced by one row.
bool PrepareForRead();
// Flush all the file contents to disk. This must be called after we are done writing out data to the chunk file.