This installment will talk about overwriting indexes and deferred association. Before we do that, let’s simply create an order table, Orders, for example. Table contains 3 columns:

  • id: Order ID,intType, primary key growth
  • product_id: commodity ID, which is indexed on this column
  • name: Order name
CREATE TABLE `orders` (
  `id` int(10) NOT NULL COMMENT 'order ID'.`product_id` int(10) DEFAULT NULL COMMENT 'commodity ID'.`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Order Name',
  PRIMARY KEY (`id`),
  KEY `product_idx` (`product_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Copy the code

Cover index

What is an overwrite index?

We know that if MySQL finds data from the index, but the leaf node of the index does not contain the required data field, then we still need to perform a backtable query.

If an index contains (overwrites) all the field values we need to query, we call it a “overwrite index.”

MyISAM

When using the MyISAM storage engine, because we have an index at product_id, SELECT product_id FROM Orders will use the overwrite index:

mysql> EXPLAIN SELECT product_id FROM orders; +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+----- --------+ | id | select_type | table | partitions |type| possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+----- --------+ |1 | SIMPLE      | orders | NULL       | index | NULL          | product_idx | 5       | NULL |    2 |   100.00| Using index | +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+----- --------+1 row in set (0.00 sec)
Copy the code

SELECT id, product_id FROM Orders WHERE product_id = 1 FROM orders WHERE product_id = 1

  1. inproduct_idFound in index treeproduct_id = 1The child node
  2. The child pointer is used to read rows of data on disk
  3. Retrieves the data rowidfield

Because the leaf node of MyISAM stores Pointers to rows, this query has one more step back to the table and cannot use an overwrite index.

mysql> EXPLAIN SELECT id, product_id FROM orders WHERE product_id = 1; +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----- --+ | id | select_type | table | partitions |type| possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----- --+ |1 | SIMPLE      | orders | NULL       | ref  | product_idx   | product_idx | 5       | const |    1 |   100.00| NULL | +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----- --+1 row in set (0.00 sec)
Copy the code

MyISAM index structure

InnoDB

InnoDB differs from MyISAM in that its primary key uses a clustered index, while its secondary index leaves hold the primary key values of rows. That is, a secondary index can cover not only itself, but also the primary key of the row.

InnoDB secondary index leaves contain row primary key values

SELECT id, product_id FROM Orders WHERE product_id = 1 FROM orders WHERE product_id = 1

mysql> EXPLAIN SELECT id, product_id FROM orders WHERE product_id = 1; +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----- --------+ | id | select_type | table | partitions |type| possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----- --------+ |1 | SIMPLE      | orders | NULL       | ref  | product_idx   | product_idx | 5       | const |    1 |   100.00| Using index | +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----- --------+1 row in set (0.01 sec)
Copy the code

You can see that Extra displays Using index, indicating that the query uses an override index. The query trajectory of the query statement is as follows:

  1. At secondary indexproduct_idIs found in the index tree ofproduct_id = 1Leaf node of
  2. Retrieves the row primary key value of the leaf nodeidAlong with all the returns

The query trace does not return the value of the table.

Delays associated

Deferred join refers to “deferred access to columns” and does not directly fetch all required columns.

In the first phase of the query, MySQL uses the overwrite index, and the results from the overwrite index match all the required column values in the outer query.

This is a little abstract, so let’s look at the following example.

Optimize paging with deferred association (LIMIT)

When LIMIT hits a large offset, such as LIMIT 10000, 20, MySQL needs to query 10020 records and return the last 20. The first 10,000 will eventually be abandoned, which is very expensive.

mysql> EXPLAIN SELECT * FROM orders LIMIT 10000.20; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | id  | select_type | table | partitions |type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
Copy the code

One of the easiest ways to optimize such paging queries is to use index override scans whenever possible, rather than querying all columns. We then do the correlation again as needed to return the columns we need.

mysql> EXPLAIN SELECT * FROM orders AS o1 JOIN (SELECT id FROM orders LIMIT 10000.20) AS o2 ON o1.id = o2.id; +----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+------ ----+-------------+ | id | select_type | table | partitions |type| possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+------ ----+-------------+ |1 | PRIMARY     | o1         | NULL       | ALL   | PRIMARY       | NULL        | NULL    | NULL       |    2 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 4       | test.o1.id |    2 |   100.00 | Using index |
|  2 | DERIVED     | orders     | NULL       | index | NULL          | PRIMARY     | 4       | NULL       |    2 |   100.00| Using index | +----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+------ ----+-------------+Copy the code

In this way, MySQL uses an overwrite index when scanning the “inner layer” of the SQL statement. The “outer layer” then finds the relevant rows through the index tree, which directly reduces the amount of data scanned.

conclusion

With overwritten indexes, MySQL only needs to scan the index instead of returning to the table, which greatly reduces data access and makes queries faster and more efficient.

Deferred join is a practical application of overwriting indexes and can be used to optimize paging or other scenarios.

The resources

  • High Performance MySQL [1]

The resources

[1]

The high performance MySQL: book.douban.com/subject/230…

Read more

5 minutes to master using C++\ in Cython

5 minutes to master common configuration files in Python \

Learn the Hook function \ in Python in 5 minutes

Special recommendation \

\

Click below to read the article and join the community