How MySQL looks at index usage and optimizes

Hash and Btree indexes are supported in MySQL. InnoDB and MyISAM only support Btree indexes, while memory and heap storage engines can support hash and btree indexes

We can query the current index usage by using the following statement:

Show status like '%handler_read% ';

+-----------------------+-------+
| variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+

If the index is working, the value of the Handler_read_key is high, which represents the number of times a row is read by the index value, and a low value table name increases the performance of the index, so the index is not often used

If a high value of handler_read_rnd_next means that the query is running inefficiently, an index remediation should be established, meaning that the number of requests in the data file reads the next line. If a large number of table scans are in progress, the Handler_read_rnd_next values will be high. The description index is incorrect or does not utilize the index.

Optimization:

To refine the INSERT statement:

1. Try to use INSERT into test values (), (), (), () ...

2. If you insert multiple lines from different customers, you can get higher speed by using the Insert delayed statement, delayed meaning is to have the INSERT statement executed immediately, in fact, the data are placed in the memory queue, and did not actually write to the disk, which is more than the number of each statement inserted faster; Low_ The priority is just the opposite, when all other users have read and write to the table before inserting.

3. Storing index files and data files on separate disks (using the Build table statement)

4. If you are using BULK INSERT, you can increase the Bulk_insert_buffer_size variable value method to increase the speed, but only for the MyISAM table

5. When loading a table from a text file, use the load data file, which is typically 20 times times faster than using insert

To optimize the GROUP BY statement:

By default, MySQL sorts all group by fields, similar to order by. If the query includes group by but the user wants to avoid the consumption of the sort results, you can specify order by NULL to prohibit sorting.

To optimize the ORDER BY statement:

In some cases, MySQL can use an index to satisfy an order by sentence, thus eliminating the need for additional sorting. The Where condition and order by use the same index, and the order by IS in the same order as the index, and the order by field is ascending or descending.

Refine nested queries:

mysql4.1 begins to support subqueries, but in some cases, subqueries can be replaced by more efficient joins, especially when the passive table of joins is to be indexed, because MySQL does not need to create a temporary table in memory to complete this logical two-step query effort.

How MySQL looks at index usage and optimizes