MySQL Index optimization continued

(1) Index type:

1. Btree Index: Abstract can be understood as "orderly" quick find structure
   Btree index is used by default in Myisam,innodb

2. Hash Index: Hash index calculation is very fast, but the data is randomly placed, unable to optimize the range, cannot take advantage of the prefix index, sorting can not be optimized
   The memory table uses the hash index by default
   
   

(ii) Single-row and multi-column indexes
First, single-row index
Second, multi-column index
How can I make a multi-column index work?
1. On a multi-column index, the index uses matching to match the left-hand prefix rule

Have the following Federated Index: index (A,B,C)

SELECT * from test_table where a=1 and b=2 and c=3; Indexes are used from left to right, although there are greater than numbers, but half-block indexes are also used

SELECT * from test_table where a=1 and c=3 and b=3; MySQL is very smart, although the criteria column of the query is not in order, but MySQL will be automatically recognized so can still use the index

SELECT * from test_table where a=1 and b=2 and c>3; Indexes are used from left to right, although there are greater than numbers, but half-block indexes are also used

SELECT * from test_table where a=1 and b>2 and c=3; The index is used from left to right, although there is a greater than sign, but also uses a half-block index, still the C column index will be used, so the index is used

SELECT * from test_table b=2 and c=3; The index takes effect from left to right if the subsequent index column does not take effect, this does not use the A index, so an index is not used to

SELECT * from test_table b=2; Ditto

SELECT * from test_table c=3; Ditto

Group statistics suggestions to sort by grouped fields before counting


We wanted to know if an SQL statement used the index the way we wanted. We can use explain to view the execution plan of the SQL statement:
Explain usage Note:

Just precede the SQL statement with explain.

Mysql> explain select * from T where c1= ' a ' and c5= ' e ' order by C2,C3;

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

| 1 | Simple | T | Ref | C1 | C1 | 3 |    Const | 6 | Using where; Using Index |

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

Table: Represents the data table used
Type: The connection type has multiple values, and different values reflect the different optimization levels of the SQL statement

Index used by Keys:mysql

Index length used by Key_len:mysql

Rows:mysql the number of rows to execute the query, the larger the value, the worse the index is not used.

Extra:mysql to resolve the query in detail there are several parameters that need to be explained below
Using Filesort: Describes using data sorting before executing the SQL statement

Using index: Indicates that the index is used

Using temporary: Description MySQL created a temporary table to accommodate the results

(iii) Use of show profiles

The general situation is that we consider the need to establish a table to index is due to the slow query, this time we can open the show profiles to understand the progress of our optimization, and the feasibility of the program.

Show Profiles: The default is generally closed, but session level can turn on this feature, when turned on allows MySQL to collect all the relevant information when executing SQL statements, through this can be more explicit understanding of the results of optimization.

1. See if show profile is turned on

Mysql> Show variables like "%pro%";

+---------------------------+-------+

| variable_name | Value |

+---------------------------+-------+

| have_profiling | YES |

| Profiling | OFF |

| Profiling_history_size | 15 |

| protocol_version | 10 |

|       Proxy_user | |

| Slave_compressed_protocol | OFF |

| Stored_program_cache | 256 |

+---------------------------+-------+

2. Open option if profiling is turned off
Mysql> set profiling=1;

3. View the SQL statement run information

SQL statements executed in the system after the profile is opened are logged

Mysql> Show Profiles;

+----------+-------------+------------------------------------------------------------------------------------- -------------------+

| query_id | Duration | Query |

+----------+-------------+------------------------------------------------------------------------------------- -------------------+

|  1 | 0.00031950 | Show variables like "%pro%" |

|  2 | 0.00020125 | Show Tables |

|  3 | 0.40749175 | Select COUNT (*) from B2b_my_test_value |

|  4 | 0.00008600 | Show Processlist |

|  5 | 0.40956300 | Select COUNT (*) from B2b_my_test_value |

|  6 | 0.00007225 | Show Profile for Query |

|  7 | 0.00004150 | Show Profile for Query |

| 8 | 23.30362350 | Select AVG (' My_age '), ' My_aihao ' from B2b_my_test_value Group by ' My_aihao ' |

|  9 | 0.00018900 | Show index from B2b_my_test_value |

|  10 | 0.00015050 | Explain select AVG (' My_age '), ' My_aihao ' from B2b_my_test_value Group by ' My_aihao ' |

|  11 | 0.00014875 | Explain select AVG (' My_age '), ' My_aihao ' from B2b_my_test_value Group by ' My_aihao ' |

|  12 | 0.00016725 | Explain select AVG (' My_age '), ' My_aihao ' from B2b_my_test_value Group by ' My_aihao ' ORDER by ' My_aihao ' |

|  13 | 0.00018725 | Explain select AVG (' My_age '), ' My_aihao ' from B2b_my_test_value Group by ' My_aihao ' |

|  14 | 0.00020875 | Show index from B2b_my_test_value |

|  15 | 0.00008125 | Explain select AVG (' My_age '), ' My_aihao ' from B2b_my_test_value where group by ' My_aihao ' |

+----------+-------------+------------------------------------------------------------------------------------- -------------------+

Rows in Set, 1 Warning (0.00 sec)

So that we can easily understand the time of SQL execution

MySQL Index optimization continued