How to view and optimize MySql indexes

MySQL supports hash and btree indexes. InnoDB and MyISAM only support Btree indexes, while the Memory and Heap storage engines can support both hash and Btree indexes.

We can query the current index usage with 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 Handler_read_key will be high. This value represents the number of times a row is read by the index value. If the value is low, the performance improvement of adding an index to the table name is not high, so the index is not often used.

If the Handler_read_rnd_next value is high, it means that the query is running inefficiently and an index should be created to remedy the situation. This value means the number of requests to read the next row in the data file. If a lot of table scans are happening, Handler_read_rnd_next will be high. This indicates that the index is incorrect or not being used.

optimization:

Optimize the insert statement:

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

2. If you insert multiple rows from different customers, you can get a higher speed by using the INSERT delayed statement. Delayed means that the INSERT statement is executed immediately. In fact, the data is placed in a memory queue and is not actually written to disk. This is much faster than inserting each statement separately. Low_priority is just the opposite. Insertion is performed after all other users have finished reading and writing the table.

3. Store index files and data files on different disks (using table creation statements)

4. If you are doing batch inserts, you can increase the bulk_insert_buffer_size variable value to increase the speed, but only for MyISAM tables.

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

Optimize the group by statement:

By default, MySQL sorts all group by fields, which is similar to order by. If the query includes a group by but the user wants to avoid the consumption of sorted results, you can specify order by null to suppress the sort.

Optimize the order by statement:

In some cases, MySQL can use an index to satisfy the ORDER BY clause, thus eliminating the need for additional sorting. The where condition and order by use the same index, and the order by order is the same as the index order, and the order by fields are in ascending or descending order.

Optimizing nested queries:

MySQL 4.1 began to support subqueries, but in some cases, subqueries can be replaced by more efficient joins, especially when the passive table to be joined has an index. The reason is that MySQL does not need to create a temporary table in memory to complete this query that logically requires two steps.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.