MySQL multi-column index effective rules

MySQL in Myisam,innodb by default is the Btree index, as to how the data structure of btree is not important,
Only need to know the result, since is the index that this data structure finally is orderly, like Xinhua dictionary His directory is according to A,b,c. This is a well-ordered line;
So when you're looking for something, like you're looking for an explanation of the word "Zhong," you'll definitely be positioned at the beginning of the Z section of the table of contents;

The combined index can be understood as such, for example (A,B,C), ABC is ordered, in any section of a under the B is a good order, any section B below C is a good order;

The effective principle of the composite index is that it takes effect from the previous one, and if an intermediate index is not used, the index part before the breakpoint works, and the index after the breakpoint does not work;
Like what

where A=3 and b=45 and c=5 .... These three index sequences use no breakpoints in the middle, all play a role; where a=3 and c=5 ... In this case B is the breakpoint, A has played an effect, C has no effect where b=3 and c=4 ... In this case A is a breakpoint and the index behind a does not work, and the Union index does not exert any effect; where b=45 and A=3 and c=5 .... It all works the same as the first one, and ABC just needs to be on the line, not in the order of the writing.

(A,B,C) A federated index is added to the three columns (the Federated index is not indexed separately on each column)

Also note that (A,B,C) multi-column index and (A,C,B) is not the same, see the above figure also can see the relationship order is not the same;
Analysis of several practical examples to enhance understanding;
Analyze the index used in a sentence

(0)    SELECT * FROM MyTable where a=3 and b=5 and C=4;ABC Three indexes are used in the Where condition, and all play a role (1)    select * FROM mytable where  c=4 and b=6 and a=3; This statement lists only to show that MySQL is not so stupid, where the condition order is automatically optimized by MySQL before query, the effect is the same as the previous sentence (2)    select * FROM MyTable where a=3 and C =7;a uses the index, B is useless, so C is not used for the index effect (3)    select * FROM MyTable where a=3 and b>7 and C=3;a used, B also used, c no use, this place B is the range value, also calculate the breakpoint, It's just a self-employed index (4)    select * FROM MyTable where b=3 and c=4; Because the A index is not used, there is no index effect in BC (5)    select * FROM MyTable where a& Gt;4 and b=7 and c=9;a used to  b not used, C is not used (6)    select * FROM MyTable where a=3 order by B;a used index, b in the result sort also uses the effect of the index, said before, a The b of any of the following is a sequential (7)    select * FROM MyTable where a=3 order by C;a is used for the index, but this place C does not play the sorting effect, because in the break point, use explain can see Filesort (8 )    select * FROM MyTable where b=3 the order by A;b is not used in the index, sort a also does not play an index effect

Add one:

Rapid generation of 1000W test database;

To create a test table:

CREATE TABLE user (      ID int () NOT null auto_increment,       uname  varchar,      regtime  char ()  ,      Age  Int (one)   ,    primary key (ID))  engine=myisam default Charset=utf8 collate=utf8_general_ci  , Auto_increment=1;

To write a stored procedure:

Delimiter $ $SET autocommit = 0$$ Create  procedure test () Begindeclare v_cnt Decimal (Ten)  default 0;dd: Loop          in SERT into  user values    (Null,rand () *10,now (), rand () *50),        (Null,rand () *10,now (), rand () *50),        (NULL, Rand () *10,now (), rand () *50),        (Null,rand () *10,now (), rand () *50),        (Null,rand () *10,now (), rand () *50),        ( Null,rand () *10,now (), rand () *50),        (Null,rand () *10,now (), rand () *50),        (Null,rand () *10,now (), rand () *50),        (Null,rand () *10,now (), rand () *50),        (Null,rand () *10,now (), rand () *50);      commit;        Set v_cnt = v_cnt+10;           If  v_cnt = 10000000 then leave DD;          End If;         End loop DD; end;$$ delimiter;

To call a stored procedure:

Call Test ();

MySQL multi-column index effective rules