Summary of common mysql database optimization operations (experience sharing) and mysql Experience Sharing

Summary of common mysql database optimization operations (experience sharing) and mysql Experience Sharing

Preface

For a data-centric application, the quality of the database directly affects the performance of the program, so the database performance is crucial. Therefore, we need to know about the optimization operations of the mysql database. This article mainly summarizes the common optimization operations in the mysql database. I will not talk about them below. Let's take a look at the details.

I. Index

Put Index first. Needless to say, we have been quietly using this optimization method, that is, primary key Index. Sometimes we may not care about it. If we define an appropriate index, the database query performance (speed) will be improved several times or even dozens of times.

Common Index

The function is to increase the query speed.

Create a table and create an index

Create table tbl_name (field Name field type [integrity constraints], ~ Index [index name] (column_name ));

Create an index

CREATE INDEX index_name ON tab_name (column_name)

Delete Index

DROP INDEX index_name FROM tab_name 

View Indexes

SHOW index FROM tab_name

Primary Key Index

Acceleration query and unique constraints

Create a table and create an index

Create table tbl_name (field Name field type [integrity constraints], ~ Primary key (column_name ));

Create an index

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

Delete Index

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

Unique Index

Acceleration query and unique constraints

Create a table and create an index

Create table tbl_name (field Name field type [integrity constraints], ~ Unique [index name] (column_name ));

Create an index

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

Delete Index

DROP UNIQUE INDEX index_name FROM tab_name 

Ii. Less SELECT *

Some people may select the database to be queried, which is inappropriate. We should take the data we need, rather than the full data, because when we select, it will increase the burden on the web server, increase the load of network transmission, and the query speed will naturally decrease.

Iii. EXPLAIN SELECT

It is estimated that many people have not seen this function, but we strongly recommend it here. Explain shows how mysql uses indexes to process select statements and connect tables. It can help you select better indexes and write more optimized query statements. The main purpose is to add the explain command before the select statement.

Explain select [query field names] FROM tab_name...

4. Enable query Cache

Query cache is enabled on most MySQL servers. This is one of the most effective ways to improve performance, and it is processed by the MySQL database engine. When many identical queries are executed multiple times, these query results are stored in a cache, the cache results are directly accessed for the same query in the future without having to operate the table.

The first step is to set query_cache_type to ON, and then query whether the variable have_query_cache is available:

show variables like 'have_query_cache'

Then, allocate the memory size to the query cache to control the maximum value of the cache query results. The related operations are modified in the configuration file.

5. Use NOT NULL

Many tables contain columns that can be NULL, even if the application well does not need to save NULL, this is because NULL is the default attribute of the column. Generally, it is better to specify the column as not null unless the NULL value needs to be stored.

If a query contains columns that can be NULL, it is more difficult for MySQL to optimize because columns that can be NULL make the index, index statistics, and values more complex. Columns that can be NULL use more storage space, and special processing is also required in MySQL. When a column that can be NULL is indexed, each index record requires an extra byte. In MyISAM, it may even lead to a fixed size index (for example, an index with only one integer column) to a variable-size index.

Generally, changing a column that can be NULL to not null reduces the performance improvement. Therefore, it is NOT necessary to search for a well in the existing schema to modify this situation, unless you are sure this will cause problems. However, if you plan to create an index on a column, try to avoid designing a column that can be NULL. Of course, there are also exceptions. For example, InnoDB uses a separate bit to store NULL values, which provides a good spatial efficiency for sparse data. However, this does not apply to MyISAM.

6. Storage engine Selection

For how to select MyISAM and InnoDB, If you need transaction processing or foreign keys, InnoDB may be a good method. If you need full-text indexing, MyISAM is usually a good choice because it is built in the system. However, we do not often test the 2 million rows of records. Therefore, even if it is slower, we can use Sphinx to obtain full-text indexes from InnoDB.

The size of data is an important factor that affects your choice of storage engines. Large-sized data sets tend to use InnoDB because they support transaction processing and fault recovery. The small size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. MyISAM may need

InnoDB only needs a few minutes to do these tasks in hours or even days.

The habit of operating database tables may also be a factor that has a great impact on performance. For example, COUNT () can be very fast in the MyISAM table, but it may be very painful in the InnoDB table. Primary Key query will be quite fast in InnoDB, but be careful that if our primary key is too long, it will also cause performance problems. A large number of inserts statements are faster in MyISAM, but the updates is faster in InnoDB-especially when the concurrency is large.

So which one do you use? Based on experience, if it is a small application or project, MyISAM may be more suitable. Of course, using MyISAM in a large environment can also be very successful, but this is not always the case. If you plan to use a project with a large amount of data and require transaction processing or foreign key support, you should use InnoDB directly. But remember that InnoDB tables require more memory and storage. Converting a GB MyISAM table to an InnoDB table may cause a bad experience.

7. Avoid using or in the where clause to connect

If a field has an index and a field does not have an index, the engine will discard the index and perform a full table scan, for example:

select id from t where num=10 or Name = 'admin'

You can query it as follows:

select id from t where num = 10union allselect id from t where Name = 'admin'

8. Use varchar/nvarchar

Replace char/nchar with varchar/nvarchar, because the storage space of the variable-length field is small, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

9. Avoid returning large data volumes

Here we should consider using limit to limit the returned data volume. If a large amount of data is returned each time, the query speed will be reduced.

10. where clause Optimization

When a parameter is used in the where clause, a full table scan is performed. Because SQL only parses local variables at run time, the optimizer cannot postpone the selection of an access plan to run; it must be selected during compilation. However, if an access plan is created during compilation, the value of the variable is still unknown and thus cannot be used as an input for index selection.

Avoid performing expression operations on fields in the where clause as much as possible, and avoid performing function operations on fields in the where clause. This will cause the engine to stop using indexes for full table scanning. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the where clause. Otherwise, the system may not be able to correctly use the index.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.