High performance MySQL (5) Indexing strategy-index case analysis

The best way to understand indexes is to combine examples and then analyze an example.

Suppose you want to design an online dating site with many columns, including country, region, city, sex, eye color, and so on. The Web site must support a variety of combinations above to search for users, including the restriction of sorting according to the user's last online time, rating, and so on.

You need to consider whether you want the index to sort or retrieve the data before sorting, because using index sorting strictly restricts the design of indexes and queries. If MySQL uses a range query for an index, it is no longer possible to use another index or to sort the subsequent fields of the index. The next step is to discuss:

1, support a variety of filtration conditions

Country columns are typically not highly selective, but many queries may be used. The selectivity of the sex column is very low, but there are also many queries to use. So, given the frequency of use, it is recommended that you create a different combination (country,sex) as a prefix.

This is contradictory to the previous saying that you should not create an index on a lower-selectivity column for 2 reasons:

A, because sex is used too frequently.

B, the more important point is that the index added this column does not have any harm, even if the query does not use sex, we can bypass it by means of adding and sex in (' m ', ' F ') of the query, so that the write does not filter any rows and can match the leftmost prefix of the index, which is very effective. But don't let the in () list be too long.

Because of the complexity of query conditions, there may be a lot of conforming indexes that need to be common, such as (Sex,country,age), (Sex,country,region,age), (Sex,country,region,city,age), If you want to reuse indexes as much as possible, the in () technique is important, but it's not abusive, and if the list is too long, grouping together can affect performance.

We'll notice that we've been putting the age column on the last side of the index. This is because of the age feature, the age column has many range queries, and the leftmost prefix encounters the first range query, then the subsequent column index is not available.

Therefore, an important principle is to put the scope of the query more columns in the back.

2, avoid multiple range of inquiries

What is a range query? The output from explain is difficult to differentiate between range queries (<,>,between) and list value queries in (), because the type in explain is range, but the two access efficiencies are different for the index use of subsequent fields. The index behind the former cannot be used, but the latter can.

3. Optimized sorting

Using file sorting on small data is quick, but if millions of data, how to sort?

For example, create (sex,rating) indexes for the following sort

Select <clos> from profiles where sex= ' M ' ORDER by rating limit 10;

If the data needs to be leafed, then the query might look like this

Select <cols> from profiles where sex= ' M ' ORDER by rating limit 100000, 10;

This is still a serious problem even with the right index, and the cache can optimize the problem in addition to the up-front calculation, making a better strategy to use deferred correlation

By using the Overwrite index query to return the required primary key, and then depending on the primary key associated with the original table to obtain the required rows, this can reduce the MySQL scan those need to discard the rows

Select <cols> from Profiles inner join (
Select <primary key cols> from profiles
where sex= ' M ' Rating limit 100000,10) as
x using (<primary key cols>);

Next we look at this example:

CREATE TABLE ' Emp3 ' (
  ' id ' int (one) not null DEFAULT ' 0 ',
  ' name ' varchar ' (m) not null,
  ' job ' varchar ' NULL,
  ' NUM1 ' int (a) default null,
  ' num2 ' int (a) default null,
  ' num3 ' int (a) default null,
  ' Job_num ' int (a) default null,
  ' d ' date default NULL,
  PRIMARY key (' id '),
  key ' num1 ' (' num1 ', ' job_num ')
Engine=innodb DEFAULT Charset=utf8;

There are 1 million records, as shown in the chart:

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/