1.17 MySQL Table Access Methods

1. Single-Table Access Methods

• The way MySQL executes a query statement is called an access method or access type.

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),  
    # Clustered index
    PRIMARY KEY (id),
    # Secondary index
    KEY idx_key1 (key1),
    # Unique index among secondary indexes
    UNIQUE KEY idx_key2 (key2),
    # Secondary index
    KEY idx_key3 (key3),
    # Composite index among secondary indexes
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

1.1. const

1.1.1. Equality Comparison Through a Primary Key or Unique Secondary Index Matches Only One Record

1.1.1.1. Equality Comparison Between the Primary Key and a Constant

SELECT * FROM single_table WHERE id = 1438;

• 

1.1.1.2. Equality Comparison Between a Unique Secondary-Index Column and a Constant

SELECT * FROM single_table WHERE key2 = 3841;

• 

1.2. ref

1.2.1. Equality Comparison Between an Ordinary Secondary-Index Column and a Constant Matches Multiple Contiguous Records

1.2.1.1. Equality Comparison Between an Ordinary Secondary-Index Column and a Constant

SELECT * FROM single_table WHERE key1 = 'abc';

• 

1.3. ref_or_null

1.3.1. Equality Comparison Between an Ordinary Secondary-Index Column and a Constant, While Also Finding Records Whose Column Value Is NULL

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

• 

1.4. range

1.4.1. Range Matching Through an Index Rather Than Comparison with a Constant

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

• 

1.5. index

1.5.1. Traverse the Secondary Index to Filter Records

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

1.6. all

1.6.1. Full Table Scan

2. Multi-Table Join Methods

2.1. Nested-Loop Join

• The driving table is accessed only once, but the driven table may be accessed multiple times.

  • The number of accesses depends on the number of records in the result set after the single-table query on the driving table.

• Multiple nested for loops.

  for each row in t1 { # Here this means iterating over every record in the result set satisfying the single-table query on t1
          for each row in t2 { # For a certain record from t1, iterate over every record in the result set satisfying the single-table query on t2
              for each row in t3 { # For a combination of records from t1 and t2, perform a single-table query on t3
                  if row satisfies join conditions, send to client
                  }
          }
  }

2.2. Block Nested-Loop Join

• When there is a very large amount of data in the driven table, the data needs to be loaded from disk into memory, and this loading needs to happen once for each record in the driving table.

  for each row in t1 {
      # load t2 rows from disk
  }

• A batched version of Nested-Loop Join that uses a Buffer.

  # load t2 rows from disk into buffer
  for each row in t1 {
      for each row in t2 { # load t2 rows from buffer
      }
  }

• The buffer size can be set through join_buffer_size.

• Only columns in the query list (select) and columns in filter conditions (on, where) are placed in the join buffer.

2.3. Index Join

• You only need to add an index to the columns of the driven table.