Mysql optimization practice (explain & Index) and mysql practice
Lab environment:
| 1. SQL tool: Navicat2 and SQL database. Use openstack database as an example. |
I. mysql index Query
| Show index from instances |
Result field explanation:
Table: database Table name Non_unique: the index value is 0 if it cannot contain duplicate words. Yes, it is 1. Key_name: name of the index.
The column serial number in the index, starting from 1. How is a column name stored in an index. In MySQL, there are values 'A' (ascending) or NULL (unclassified ). The estimated number of unique values in the index. You can update analyze table or myisamchk-a by running analyze table. The base number is counted based on the statistical data stored as an integer. Therefore, this value is not required to be accurate even for small tables. The larger the base, the larger the number of machines that MySQL uses the index when performing the union operation. If a column is partially indexed, it is the number of indexed characters. If the entire column is indexed, the value is NULL. Indicates how keywords are compressed. If it is not compressed, It is NULL. If the column contains NULL, the value is YES. If NO, the column is NO. Used Index methods (BTREE, FULLTEXT, HASH, RTREE ). Comment: Comment.
|
2. Will the primary key of Mysql automatically create an index? Create a ttx_index database table without a primary key: Query index: No index is displayed.
Change the field id of the ttx_index database table, set it as the primary key, and query the index again:
It is concluded that the primary key of the database is automatically indexed in Mysql.
Iii. Mysql performance optimization tool: explain1. First, check the index of the instances database table:
2. EXPLAIN the usage of EXPLAIN:
| Explain select * FROM instances |
Based on the above results, it takes 0.027 ms for this query to have no available indexes.
Detailed description of the explain field:
Table: displays the data of this row about which table Type: this is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and ALL. Possible_keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain. Key: actually used index. If it is NULL, no index is used. In rare cases, MYSQL selects an optimized index. In this case, you can use index (indexname) in the SELECT statement to force an INDEX or use ignore index (indexname) to force MYSQL to IGNORE the INDEX. Key_len: the length of the index used. The shorter the length, the better. Ref: indicates which column of the index is used. If possible, it is a constant. Rows: the number of rows that MYSQL deems necessary to check to return the requested data Extra: Extra information about how MYSQL parses the query. We will discuss it in the following table, but here we can see that the bad examples are Using temporary and Using filesort, which means MYSQL cannot use indexes at all, and the result is that the retrieval will be slow.
Description returned by the extra column:
Distinct: Once MYSQL finds the row that matches the row, it does not search again. Not exists: MYSQL optimizes left join. Once it finds a row that matches the left join standard, it no longer searches. Range checked for each Record (index map: #): no ideal index is found. Therefore, for each row combination in the preceding table, MYSQL checks which index is used, use it to return rows from the table. This is one of the slowest connections using indexes. Using filesort: When you see this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition. Using index: the column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are in the same index. When Using temporary sees this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group. Where used uses the WHERE clause to limit which rows match the next table or return the rows to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order) The system table has only one row: system table. This is a special case of the const connection type. Const: the maximum value of a record in the table can match this 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 first reads this value and treats it as a constant. Eq_ref: During the connection, MYSQL reads a record from the table from the Union of each record in the previous table during query, it is used when you query all data that uses the index as the primary key or unique key. Ref: This connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index-the fewer the better Range: This connection type uses an index to return rows in a range, such as> or <what happens when something is searched Index: This connection type performs a full scan of each record in the preceding table (better than ALL, because the index is generally smaller than the table data) ALL: this connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.
|
|
So how can we make the SQL query take the index query?
| Explain select * FROM instances WHERE id = 1 |
The SQL statement is indexed. Because the id in the table is the primary key, mysql automatically creates an index. Therefore, when the id is used as the where condition for query, the database automatically performs an index.
In the next experiment, what will happen when I do not go through the index or query the data with id = 1?
| SELECT id, display_name FROM instances WHERE id = 1 |
| Explain select * FROM instances WHERE display_name = "vm1" |
Conclusion: During the query, if the field in the where condition has an index (which does not take the index, it depends on the field in the where condition), mysql will automatically take the index when executing the SQL statement.
However, there is a problem: the query takes 0.001 ms without indexing, and the performance does not seem to improve?
| Select count (*) FROM instances |
The total number of instance data items in the database table is only 74, so the index cannot take advantage of its performance. Next we will create w pieces of data manually:
| Insert instances (display_name) select display_name from instances |
Note: The preceding statement can be used to insert new data in the form of database table indexes.
Query the total number of items again:
| Select count (*) FROM instances |
This time the data is nearly million. Verify the above index performance again:
1. To compare the authenticity, change display_name of the data record with id = 1 to the unique name test_index_dispaly_name.
| SELECT id, display_name FROM instances WHERE id = 1 |
2. Do not go to index query:
| SELECT * FROM instances WHERE display_name = 'test _ index_dispaly_name' |
3. Index query by id:
| SELECT * FROM instances WHERE id = 1 |
Conclusion: For more than million pieces of data, the efficiency of non-indexing is quite significant (the efficiency difference is almost ).
4. In which cases will the SQL statements not be indexed?
| Time relationship. This is not summarized yet. You can add time later. If necessary, search online. |
