The following respectively create three tables, and insert 1W simple data for testing, the details are as follows: [1] test_a has a primary key but no index create TABLE ' test_a ' ( ' id ' int () unsigned not NULL AUT o_increment, ' title ' varchar (+) not null, ' content ' text not null, ' number ' int (ten) unsigned not NULL, PRIMARY key (' id ')) engine=innodb auto_increment=0 DEFAULT charset=utf8; [2] Test_b has primary key and single column index create TABLE ' Test_b ' ( ' id ' int () unsigned not NULL auto_increment, ' title ' varchar (+) not null, ' content ' Text not null, ' number ' int (ten) unsigned not null, PRIMARY KEY (' id '), UNIQUE KEY ' Titleindex ' (' title ' Using btree, UNIQUE KEY ' numberindex ' (' number ') using BTREE) Engine=innodb auto_increment=0 DEFAULT Charset=utf8; &NBSP;[3] Test_c has primary key and combined index create TABLE ' test_c ' ( ' id ' int () unsigned not NULL auto_increment, ' title ' varchar (+) not null, ' content ' text not null, ' number ' int (ten) unsigned not null, PRIMARY KEY (' id '), & nbsp KEY ' Titlenumberindex ' (' title ', ' number ') USING BTREE) Engine=innodb auto_increment=0 DEFAULT charset=utf8; The following three tables are tested separately by single-column index and combined index , and the following time is the average of multiple tests. &NBSP;[1] Single-column index [1.1] query the specified field [sql] SELECT * from test_a WHERE title = ' title_5000 '; [use] 12ms[sql] SELECT * from test_b WHERE title = ' title_5000 '; [Use] <1ms description: You can see that the index time is 12ms, the index is less than 1ms, or the difference is very large. [sql] SELECT * from test_a WHERE content = ' content_5000 '; [use] 13ms[sql] SELECT * from test_b WHERE content = ' content_5000 '; [Use] 13ms description: Because the content fields are not indexed, the time is basically the same. [1.2] Test like query [sql] SELECT * from test_a WHERE title like '%5000 '; [use] 13ms[sql] SELECT * from Test_b WHERE title like '%5000 '; [Use] 13ms description: The time is basically the same, so the index is not hit. [sql] SELECT * from test_a WHERE title like ' 5,000% '; [use] 12ms[sql] SELECT * from the Test_b WHERE title like ' 5,000% '; [Use] <1ms description: If like is a prefix match, it will hit the index, otherwise it will not hit. Additional query results are empty, but do not affect the role of the description index. [1.3] Test OR statement [sql] SELECT * from test_a WHERE title = ' title_5000 ' or content = ' content_5000 '; [Use] 13ms[sql] SELECT * from test_b WHERE title = ' title_5000 ' OR content = ' content_5000 '; [Use] 13ms description: The time is basically the same, so the index is not hit. [sql] SELECT * from test_a WHERE title = ' title_5000 ' OR number = ' 5000 '; [use] 13ms[sql] SELECT * from test_b WHERE title = ' title_5000 ' OR number = ' 5000 '; [Use] <1ms Description: If the or both fields are indexed then hit, otherwise only one index is not hit. [1.4] Test in statement [sql] SELECT * from Test_a WHERE title in (' title_4999 ', ' title_5000 ', ' title_5001 '); [use] 12ms[sql] SELECT * from Test_b WHERE title in (' title_4999 ', ' title_5000 ', ' title_5001 '); [Use] <1ms Description: In statements can also be hit indexed. [1.5] Test (between,>,>=,<,<=) statement [sql] SELECT * from test_a WHERE number <= 5000; [use] 14ms[sql] SELECT * from test_b WHERE number <= 5000; [use] 14ms[sql] SELECT * from test_a WHERE number < 5000; [use] 14ms[sql] SELECT * from Test_b WHERE number < 5000; [use] 14ms[sql] SELECT * from test_a WHERE number > 5000; [use] 14ms[sql] SELECT * from test_b WHERE number > 5000; [use] 14ms[sql] SELECT * FROM test_a WHERE number >= 5000; [use] 14ms[sql] SELECT * from test_b WHERE number >= 5000; [use] 14ms[sql] SELECT * from Test_b Force INDEX (numberindex) WHERE number >= 5000; [use] 14ms[sql] SELECT * from test_a WHERE number between 4999 and 5001; [use] 11ms[sql] SELECT * from Test_b WHERE number between 4999 and 5001; [Use] <1ms description: Between can hit the index, other comparison symbol misses the index, forcing the use of the index effect is not obvious, this should be with the MySQL index adequacy judgment, if the index is greater than 30% will be used to scan the full table, specifically after I verify the detailed introduction. [1.6] field using function [sql] SELECT * from Test_a WHERE SUBSTR (title,7,4) = ' 5000 '; [use] 13ms[sql] SELECT * from Test_b WHERE SUBSTR (title,7,4) = ' 5000 '; [Use] 13ms description: Using a function on a field does not hit the index. &NBSP;[2] Composite index [2.1] Test OR statement [sql] SELECT * from test_a WHERE title = ' title_5000 ' or content = ' content_5000 '; [use] 13ms[sql] SELECT * from Test_c WHERE title = ' title_5000 ' OR content = ' content_5000 '; [Use] 13ms Description: The or statement in the composite index does not have a hit index. &NBSP;[2.2] Test and statement [sql] SELECT * from test_a WHERE title = ' title_5000 ' and number = 5000; [use] 12ms[sql] SELECT * from tEst_c WHERE title = ' title_5000 ' and number = 5000; [use] <1ms[sql] SELECT * from test_a WHERE number = 5000; [use] 12ms[sql] SELECT * from test_c WHERE number = 5000; [use] 12ms[sql] SELECT * from test_a WHERE title = ' title_5000 '; [use] 12ms[sql] SELECT * from Test_c WHERE title = ' title_5000 '; [Use] <1ms description: The most left-hand prefix matching principle is proved. The explain interpretation Command explain command in mysql shows how MySQL uses the index to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements. Using the method, add explain to the SELECT statement, such as: 1explain select Surname,first_name form A, a where a.id= B.idexplain Column Explanation: Table: Displays the data for this row about the type of the table: This is an important column that shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and Allpossible_keys: Displays the indexes that may be applied to this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain key: The index that is actually used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used in the SELECT statement to force an index or ignore index (indexname) to force MySQL to ignore the indexes Key_len: the length of the index used. Without loss of accuracy, the shorter the better ref: which column of the display index is used, if possible, is a constant rows:mysql the number of rows that must be checked to return the requested data extra: Additional information about how MySQL parses the query. will be discussed in Table 4.3, but the bad examples here can be seen are using temporary and using filesort, meaning MySQLThe index cannot be used at all, and the result is a very slow retrieval of the meaning of the description returned by the extra column distinct: Once MySQL finds a row that matches the row, it no longer searches for the not exists:mysql optimizes the left JOIN, once it finds a match left Join standard line, no longer searches for the range checked for each Record (index map:#): No ideal index is found, so for every combination of rows from the preceding table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections to use the index using Filesort: When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria: The column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when the entire request column of the table is part of the same index Temporary see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by where used uses the WHERE clause to restrict which rows will match the next table or return to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem the interpretation of different connection types (sorted in order of efficiency) the system table has only one row: the system table. This is a special case of a const connection type const: The maximum value of one record in a table can match the query (the index can be a primary key or a unique index). Because there is only one row, this value is actually a constant, because MySQL reads this value first and treats it as a constant: In the connection, MySQL reads a record from the table in the previous table, and the union of each record in the Eq_ref. It uses ref when the query uses all indexes as primary or unique keys: This connection type occurs only if the query uses keys that are not unique or primary keys, or parts of those types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records match the index-the less the better range: This connection type uses the index to return rows in a range, such as when using > or < to find things index: This connection type makes a full scan of each record in the previous table (better than all, because the index is generally less than the table data) all: this evenConnection type a full scan of each previous record joint, which is generally bad, should try to avoid
MySQL Index optimization instance description
