What are the benefits of mysql union index? The meaning of joint index

mysql What is the meaning of creating a ‘joint index’ in

Question?
What is the need to create a ‘joint index’? What are the most practical benefits? If it is to query the data faster, wouldn’t it be OK to have a single-column index? Why does ‘joint index’ exist?

Simply speaking, there are two main reasons:

• "One is worth three." If you build a composite index of (a, b, c), it is actually equivalent to building three indexes (a), (a, b), (a, b, c), because each additional index will increase write operations. overhead and disk space overhead. For tables with a large amount of data, this is a lot of overhead!

• Covering index. The same has a compound index (a, b, c), if there is the following sql: select a, b, c from table where a=1 and b = 1. Then MySQL can obtain data directly by traversing the index without returning the table, which reduces a lot of random IO operations. Reducing IO operations, especially random IO, is actually the main optimization strategy of DBA. Therefore, in real practical applications, covering indexes are one of the main optimization methods to improve performance

• The more index columns there are, the less data will be filtered out through the index. A table with 10 million pieces of data has the following sql: select from table where a = 1 and b =2 and c = 3. Assume that each condition can filter out 10% of the data. If there is only a single value index, then Through this index, 1000W10%=100w pieces of data can be filtered out, and then the table can be returned to find the data that matches b=2 and c=3 from the 100w pieces of data, and then sorted and paginated ; If it is a compound index, filter out 1000w 10% 10% *10%=1w through the index, and then sort and paginate. You will know which one is more efficient at a glance

The following table has three key a, b, c

create table test(
    a int,
    b int,
    c int,
);

If we
need to perform a lot of things similar to select * from test where a=10 , b>50, c>20
This type of combination query Then, we may need to create a joint index containing [a, b, c], and a separate [a][ b] The index on [c] is not sufficient. (You can think of an index as a sorted list). Creating an index of (a, b, c) is equivalent to sorting by a, b, c (the sorting rules are

  if(X.a>Y.a)
    return '>';
  else if(X.a<Y.a)
    return &#39;<&#39;;
   else if(X.b>Y.b)
    return &#39;>&#39;;
   else if (X.b<Y.b)
    return &#39;<&#39;;
   else if (X.c>Y.c)
    return  &#39;>&#39;
   else if (X.c<Y.c)
    return  &#39;<&#39;
   esle
    return &#39;==&#39;
）

and Sorting by a and b respectively is different.

The order of a b c is also important, sometimes it can be a c b, or b c a, etc.
If you create (a, b. ,c) The query efficiency of the joint index is as follows:

  优: select * from test where a=10 and b>50
  差: select * from test where a>50

  优: select * from test order by a
  差: select * from test order by b
  差: select * from test order by c

  优: select * from test where a=10 order by a
  优: select * from test where a=10 order by b
  差: select * from test where a=10 order by c

  优: select * from test where a>10 order by a
  差: select * from test where a>10 order by b
  差: select * from test where a>10 order by c

  优: select * from test where a=10 and b=10 order by a
  优: select * from test where a=10 and b=10 order by b
  优: select * from test where a=10 and b=10 order by c

  优: select * from test where a=10 and b=10 order by a
  优: select * from test where a=10 and b>10 order by b
  差: select * from test where a=10 and b>10 order by c

The following is represented by a diagram

Three Notes

In mysql, if the column is of varchar type, please do not use int type to access it
As follows
The product_id in the zz_deals table is of varchar type

mysql> explain select * from zz_deals where  qq_shop_id = 64230 and product_id = '38605906667' ;+----+-------------+-------------+------+------------------------------+------------------------------+---------+-------------+------+-------------+| id | select_type | table       | type | possible_keys                | key                          | key_len | ref         | rows | Extra       |+----+-------------+-------------+------+------------------------------+------------------------------+---------+-------------+------+-------------+|  1 | SIMPLE      | zz_deals | ref  | by_product_id_and_qq_shop_id | by_product_id_and_qq_shop_id | 156     | const,const |    1 | Using where |+----+-------------+-------------+------+------------------------------+------------------------------+---------+-------------+------+-------------+1 row in set (0.00 sec)
mysql> explain select * from zz_deals where  qq_shop_id = 64230 and product_id = 38605906667 ;+----+-------------+-------------+------+------------------------------+------+---------+------+------+-------------+| id | select_type | table       | type | possible_keys                | key  | key_len | ref  | rows | Extra       |+----+-------------+-------------+------+------------------------------+------+---------+------+------+-------------+|  1 | SIMPLE      | zz_deals | ALL  | by_product_id_and_qq_shop_id | NULL | NULL    | NULL |   17 | Using where |+----+-------------+-------------+------+------------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)

Promotion

After two and a half months of preparation, three major revisions, and seventeen minor revisions, le1024 is finally ready to meet you.

#le1024 recommends 1 to 3 paragraphs every day, which are interesting, loving, and fun. Videos with stories.

[Related recommendations]

1.

2.

3.

The above is the detailed content of What are the benefits of mysql union index? The meaning of joint index. For more information, please follow other related articles on the PHP Chinese website!