Each interview question is a knowledge point, whether the face of the interview, should see, after mastering can also be used in the development, and each knowledge point will derive a lot of problems, the more will be, the more knowledge point is the so-called: short step without thousands of miles, not small flow into rivers…

In mysql, a single SQL query has multiple query conditions, which field will you select as the index, based on what?

The SQL example looks like this:

select * from table where field_a = xx and field_b = xx and field_c = xx
Copy the code

Think about which field you would choose as the index, and why, if you have no idea, just listen to me slowly explain…

If you choose field_A, you will have to explain why you chose field_A. It is better to say “no” than to leave the interviewer with the impression that you know how to use mysql

Answer key:

Index (s) : index (s) : index (s) : index (s) : index (s) : index (s) : index (s) : index (s) : index (s) : index (s);

To analyze this problem step by step, I created a test table, Test, insert 100,000 test data into the table, let’s simulate the problem

The table structure is as follows:

CREATE TABLE `test` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `field_a` int(11) DEFAULT NULL,
  `field_b` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='测试表';
Copy the code

Using the stored procedure, add test data:

delimiter ;; create procedure test() begin declare i int; set i=1; while(i<=100000)do insert into test values(i, i, i%100); set i=i+1; end while; end;; delimiter ; Call test();Copy the code

Field_a was added from 1 to 100000, and Field_B was added from 0 to 99. If you look at the data, 100,000 pieces of data have been added

So the SQL in that problem looks like this

select * from test where field_a = xx and field_b = xx
Copy the code

In this case, we need to execute explain without creating the index

For those not familiar with explain, check out this article chenmingyu.top/mysql-optim…

SQL > select * from table where rows = 94001; SQL > select * from table where rows = 94001

SQL > alter table test

The column we need to focus on is cardinality. This column represents an estimate of the number of unique values in the index. The higher the value is, the more differentiated the field is, and the more suitable the column is for indexing. So we can use Cardinality columns to determine if the index is highly selective

Of course, there is another way to calculate whether an index is highly selective: if the Cardinality /table_rows value is as close to 1 as possible, then the index is highly selective; if it is very small, then you should consider deleting the index

Using the Analyze table test, the Cardinality column displays the same values as the ROWS column

The Cardinality value of each field can be compared to the Cardinality value of each field

Now add indexes to FIELD_A and field_B, and the table structure is as follows:

CREATE TABLE `test` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key'.`field_a` int(11) DEFAULT NULL.`field_b` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_field_a` (`field_a`),
  KEY `idx_field_b` (`field_b`))ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='Test table';
Copy the code

Let’s look at the Cardinality column again

Obviously the idX_FIELD_A index has a larger Cardinality value than idX_FIELD_B. Is idX_FIELD_A a more efficient index?

Use force index to force an index.

Use Explain to see

explain select * from test force index(idx_field_a) where field_a = 1 and field_b = 1;
Copy the code

explain select * from test force index(idx_field_b) where field_a = 1 and field_b = 1;
Copy the code

Although the type is ref, but the value of rows is still very different, I can not help but secretly excited, guess and fact gradually coincide…

Execute SQL to see how long it takes

-- 0.001s SQL execution
select * from test force index(idx_field_a) where field_a = 1 and field_b = 1;
-- SQL execution 0.004s
select * from test force index(idx_field_b) where field_a = 1 and field_b = 1;
Copy the code

The desired effect is already out, so the above conjecture is correct

The value of the Cardinality column is visible because the index is created. What if there is no index?

Taking the Cardinality concept again, an estimate that represents the number of unique values, we can use distinct to calculate the number of unique values for a field

Therefore, the following SQL can be used to determine whether the final field is suitable for index creation. Fields with a value equal to 1 or tending to 1 are most suitable for index creation

- the value is 1
select count(distinct field_a)/count(*) from test
A value of 0.001
select count(distinct field_b)/count(*) from test
Copy the code

Field FIELD_A is clearly the best place to index, consistent with the test results