Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL index management optimization method tutorial", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and study the "MySQL index management optimization method tutorial"!

1. Integrate DDL statements

In the process of adding an index to a MySQL table, a very important problem is the blocking of DDL statements, which is a simple optimization improvement when combining multiple alter statements into a single SQL statement.

For example:

Alter table test add index (username)

Alter table test drop index name,add index name (last_name,first_name)

Alter table test add column laset_visit date null

Change it to:

Alter table test

Add index (username)

Drop index name

Add index name (last_name,first_name)

Add column laset_visit date null

This optimization can greatly improve the performance of management tasks.

2. Remove duplicate indexes

Duplicate indexes have two main effects: first, all DML statements run more slowly because more work is needed to maintain data and index consistency; and second, the database will have a larger disk footprint, which will lead to more backup and recovery time.

For example:

Create table test

(id int unsinged not null

First_name varchar (30) not null

Last_name varchar (30) not null

Joined date not null

Primary key (id)

Index (id)

);

The index on the id column in this DDL is a duplicate index and needs to be removed.

Duplicate indexes also occur when the leftmost part of a given index is included in other indexes.

Create table test

(id int unsinged not null

First_name varchar (30) not null

Last_name varchar (30) not null

Joined date not null

Primary key (id)

Index name1 (last_name)

Index name2 (last_name,first_name)

);

The index name1 is redundant because the column in which the index is located is already contained in the leftmost part of the index name2.

3. Delete unused indexes

In addition to the fact that duplicate indexes are not used, there are other indexes that may not be used, which can affect performance as well as duplicate indexes.

4. Monitor invalid indexes

When defining multi-column indexes, be sure to determine whether each column you specify is really valid. You can find indexes that may contain unused columns by analyzing the key_len columns of all SQL statements on the specified table.

Thank you for reading, the above is the content of the "MySQL Index Management Optimization method tutorial". After the study of this article, I believe you have a deeper understanding of the MySQL index management optimization method tutorial, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.