MySQL Index problem analysis

1. Before watching the video, it mentions an index title: Suppose a table has a federated index (C1,C2,C3,C4)-only the C1,C2,C3 portion of the federated Index can be used
A.where c1=x and C2=x and C4>x and C3=x
B.where c1=x and C2=x and c4=x order by C3
C.where c1=x and C4=x GROUP by C3,C2
D.where c1=? and c5=? ORDER BY C2,C3
E.where c1=? and c2=? and c5=? ORDER BY C3,C2
2. The table statement below, inserted 500,000 data, if the table data is too small, MySQL will be optimized to do a full table scan, and do not need to use the index:
CREATE TABLE ' Mng_role ' (
' ID ' int (ten) unsigned not NULL auto_increment,
' NAME ' varchar (not NULL),
' Create_date ' char (8) Not NULL,
' Create_time ' char (6) DEFAULT NULL,
' UUID ' char (+) is not NULL,
' REMARK ' varchar (+) not NULL DEFAULT ' ',
' reserver ' varchar (+) not NULL DEFAULT ' reserver ',
PRIMARY KEY (' ID '),
UNIQUE KEY ' uk_uuid ' (' UUID ') USING BTREE,
KEY ' Index_name_create_date_time_remark ' (' NAME ', ' create_date ', ' create_time ', ' REMARK ' (255))) USING BTREE
) Engine=innodb auto_increment=510335 DEFAULT Charset=utf8
3. Execution of statements:
A.MARIADB [yjtmng]> EXPLAIN SELECT * from Mng_role WHERE name= ' role100 ' and create_date= ' 20151012 ' and REMARK > ' A ' and Create_time= ' 200000 ' \g
1. Row ***************************
Id:1
Select_type:simple
Table:mng_role
Type:range
Possible_keys:index_name_create_date_time_remark
Key:index_name_create_date_time_remark
key_len:962
Ref:null
Rows:1
extra:using index condition; Using where
Analysis: Type=range that the index is used, the actual is the scope of remark query, MySQL and the index is filtered, there is a using index condition. In fact, this statement changes the filter condition without affecting semantics to: where Name= ' role100 ' and create_date= ' 20151012 ' and create_time= ' 200000 ' and REMARK > ' A ', The index columns used here include: Name,create_date,create_time,remark,key_len is 962=50*3+14*3+255*3+5,5 length because NAME is a variable length

=========================== Sexy Split-line ====================================
B.MARIADB [yjtmng]> EXPLAIN SELECT * from Mng_role WHERE name= ' role100 ' and create_date= ' 20151012 ' and REMARK = ' A ' ORD ER by Create_time\g
1. Row ***************************
Id:1
Select_type:simple
Table:mng_role
Type:ref
Possible_keys:index_name_create_date_time_remark
Key:index_name_create_date_time_remark
key_len:176
Ref:const,const
Rows:1
extra:using index condition; Using where
Analysis: The index used for the query uses only two columns: name,create_date, whereas the Create_time index is used for sorting, and remark is the filter used to index the result set. Why are create_time indexes available for sorting?
1. First we want to know that the InnoDB index data format is similar to an ordered balance tree, when we use the name and Create_date as a filter, just Create_time index has been sorted, the storage engine does not need to sort in memory, You can run the following two statements to see the difference:
MariaDB [yjtmng]> EXPLAIN SELECT * from Mng_role WHERE name= ' role100 ' and create_date= ' 20151012 ' ORDER by Create_ti Me\g
MariaDB [yjtmng]> EXPLAIN SELECT * from Mng_role WHERE name= ' role100 ' and create_date= ' 20151012 ' ORDER by remark\g< /c2>

=========================== sexy Split line ====================================
c.mariadb [yjtmng]> EXPLAIN SELECT * FROM Mng_role WHERE name= ' role100 ' and reserver = ' A ' GROUP by Create_time, Create_date\g
*************************** 1. Row ***************************
id:1
select_type:simple
table:mng_role
type:ref
Possible_keys: Index_name_create_date_time_remark
Key:index_name_create_date_time_remark
key_len:152
Ref:const
Rows:1
Extra:using index condition; Using where; Using temporary; Using Filesort
analysis: here only one index (NAME) is used to find, However, this statement establishes a temporary table (using temporary) in memory and is sorted (using Filesort), which are very resource-intensive. Because of the group by operation, the filter data is first put into a temporary table of memory, then the data is sorted, and finally the data is aggregated.
I'm going to make a change to the statement, which is more efficient because of the sort of index: EXPLAIN SELECT * from Mng_role WHERE name= ' role100 ' and reserver = ' A ' GROUP by Create_date, create_time\g

=========================== sexy Split line ====================================
d.mariadb [yjtmng]> EXPLAIN SELECT * FROM Mng_role WHERE name= ' role2 ' and reserver= ' A ' ORDER by Create_date,create_time\g
*************************** 1. Row ***************************
id:1
select_type:simple
table:mng_role
type:ref
Possible_keys: Index_name_create_date_time_remark
Key:index_name_create_date_time_remark
key_len:152
Ref:const
Rows:1
Extra:using index condition; Using where
analysis: indexes are used to find, only name, create_date for sorting, Create_time

=========================== sexy Split line ====================================
e.mariadb [yjtmng]> EXPLAIN SELECT * FROM Mng_role WHERE name= ' role2 ' and create_date= ' 20151010 ' and reserver= ' A ' ORDER by create_time,create_date\g
* * * * 1. Row ***************************
id:1
select_type:simple
table:mng_role
type:ref
Possible_keys: Index_name_create_date_time_remark
Key:index_name_create_date_time_remark
key_len:176
Ref:const,const
rows:1
extra:using index condition; Using where
analysis: here the name and create_date are the indexes for the lookup, CREATE _time is used to sort the index, but the create_date sort is not valid because the previous where filter condition has been assigned: create_date= ' 20151010 ', directly ignoring the sort of a constant. The following statement requires memory ordering: EXPLAIN SELECT * from Mng_role WHERE name= ' role2 ' and create_date= ' 20151010 ' and reserver= ' A ' ORDER by Remar K\g

4. Summary:
1. Index can be used to find (type:ref), sort, filter (Using index condition):
2. According to the leftmost prefix principle, once a field is queried for a range, the subsequent index will be invalid, and the index needs to be concatenated if the where c1=? And c3=?, this will not work with the index
3. Try to choose a high-sensitivity column as the index, the formula for the degree of differentiation is count (distinct col)/count (*), the higher the clarity the better
4. Indexes cannot be used for calculations, try to expand indexes

MySQL Index problem analysis