MySQL independent index and joint index selection

There is often a lack of understanding of multi-column indexes. A common mistake is to set up independent indexes for many columns, or to use the wrong order for the index columns. We will discuss the issue of index column order in the next article. First, let’s look at the case of multiple independent indexes, taking the following table structure as an example:

CREATE TABLE test (
  c1 INT,
  c2 INT,
  c3 INT,
  KEY(c1),
  KEY(c2),
  KEY(c3),
);

Using this index strategy is usually the result of some authoritative advice (such as adding indexes to the condition columns used in the WHERE condition). In fact, this is totally wrong, and I would give it 1 star at best. Indexing in this way is several orders of magnitude slower than truly optimized indexing. Sometimes when you can't design an index with more than three stars, it's better to focus on optimizing row order or creating covering indexes than to ignore the WHERE condition.

A covering index means that the execution of a query statement can be obtained only from the index without having to read it from the data table. It can also be said that index coverage is achieved. When a query statement meets the covering index condition, MySQL only needs to use the index to return the data required for the query, thus avoiding the need to return to the table after finding the index, reducing I/O and improving efficiency. For example, there is a common index idx_key1_key2(key1, key2) in the covering_index_sample table. When we use the SQL statement: select key2 from covering_index_sample where key1 = 'keytest';, we can query through the covering index without having to find data rows from the data table.

Creating separate indexes for many columns does not help MySQL improve performance in many cases. MySQL 5.0 and later can slightly optimize this design using an index merge strategy - this approach allows queries on tables with multi-column indexes to limit the use of indexes to locate the required rows.

Index merge is to perform conditional scans on multiple indexes separately and then merge their respective results (intersect/union)

Earlier MySQL versions could use only one index, so MySQL often performed a full table scan when there was no index assistance. For example, the film_actor table has an index on film_id and actor_id, but using both indexes in the WHERE condition is not a good choice:

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 OR film_id = 1;

In earlier MySQL versions, this query results in a full table scan unless you join the two queries together as shown below.

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 UNION ALL 
SELECT film_id, actor_id FROM film_actor WHERE film_id = 1 AND actor_id <> 1;

In MySQL 5.0 and later, the query will use both indexes and merge the final results. Three variants of the algorithm are required to implement this process:

1. Use OR conditions to obtain union data
2. Use AND condition to get intersection data
3. Take the union of the intersection of the data in the above two steps.
   

The above is a bit confusing. In fact, it should be distributed using a single condition (in order to use the index) to find all the data, and then combine the data. Let's use EXPLAIN to check it out.

EXPLAIN SELECT `film_id`,`actor_id` FROM `film_actor` WHERE `actor_id`=1 OR `film_id`=1

It can be seen that the query method is a full table scan, but Extra is used for optimization. MySQL uses this technique when processing complex queries, so you may see nested operations in Extra. This index merging strategy can sometimes work well, but more often should be considered an indication of poor index usage:

1. When the server uses an intersection index (usually with an AND condition), it usually means that you want an index that contains all the related columns, rather than separate indexes that combine the columns.
2. When the server uses union indexes (usually with an OR condition), sometimes the caching, sorting, and merging operations can take up a lot of CPU and memory resources, especially when the indexes do not always have filters, which can cause the scan to return a large number of rows for the merge operation.
3. Remember that the optimizer does not incur these costs - it simply optimizes the number of random page reads. This can make the query "cheaper", causing the full table scan to be actually slower. High CPU and memory usage will affect concurrent queries, but these effects do not occur when you run query statements individually. Therefore, it is sometimes more optimal to rewrite queries to use UNION as was done in MySQL 4.1.
   

When you see index merges when using EXPLAIN analysis, you should check the query statement and table structure to see if it is the optimal approach. You can check this by disabling index merge using optimizer_switch.

Change the index of film_actor to a joint index (delete the original two independent indexes film_id and actor_id) and see the effect. You can see that the full table query is avoided.

ALTER TABLE film_actor ADD INDEX `sindex` (`film_id`,`actor_id`);

The above is the details of the selection of MySQL independent index and joint index. For more information about MySQL independent index and joint index, please pay attention to other related articles on 123WORDPRESS.COM!