Summary of common optimization operations of MySQL database (experience sharing)

Preface

For a data-centric application, the quality of the database directly affects the performance of the program, so database performance is crucial. Therefore, everyone should understand the optimization operations of MySQL database. This article mainly summarizes the common optimization operations in MySQL database. Let’s take a look at the detailed introduction.

1. Index

Putting the Index first, needless to say, this optimization method we have been using quietly, that is the primary key index. Sometimes we may not care, but if we define appropriate indexes, the database query performance (speed) will be improved several times or even dozens of times.

Normal index

The function is to improve the query speed.

Create table and index

CREATE TABLE tbl_name(
Field Name Field Type [Integrity Constraint],
~
index [index name] (column_name)
);

Create Index

CREATE INDEX index_name ON tab_name (column_name)

Deleting an Index

DROP INDEX index_name FROM tab_name

View Index

SHOW index FROM tab_name

Primary key index

The purpose is to speed up queries and unique constraints

Create table and index

CREATE TABLE tbl_name(
Field Name Field Type [Integrity Constraint],
~
PRIMARY KEY(column_name)
);

Create Index

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

Deleting an Index

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

Unique Index

The purpose is to speed up queries and unique constraints

Create table and index

CREATE TABLE tbl_name(
Field Name Field Type [Integrity Constraint],
~
unique [index name] (column_name)
);

Create Index

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

Deleting an Index

DROP UNIQUE INDEX index_name FROM tab_name

2. Use SELECT * less

Some people may select everything they want to query when querying the database, which is inappropriate behavior. We should take the data we need instead of all of it, because when we select, it will increase the burden on the web server, increase the load on network transmission, and naturally reduce the query speed.

EXPLAIN SELECT

It is estimated that many people have not seen this function, but it is highly recommended to use it here. EXPLAIN shows how MySQL uses indexes to process select statements and join tables. It can help choose better indexes and write more optimized query statements. The main usage is to add explain before select.

EXPLAIN SELECT [search field name] FROM tab_name ...

4. Enable query cache

Most MySQL servers have query cache enabled. This is one of the most effective ways to improve performance, and it is handled by the MySQL database engine. When many identical queries are executed multiple times, the query results will be put into a cache, so that subsequent identical queries can directly access the cached results without operating the table.

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

show variables like 'have_query_cache'

After that, allocate memory size to the query cache to control the maximum value of cached query results. Related operations are modified in the configuration file.

5. Using NOT NULL

Many tables contain columns that are NULL (empty value), even if the application does not need to save NULLs, because NULL is the default property of the column. It is usually best to specify that the column is NOT NULL unless there is a real need to store NULL values.

Queries involving NULL-capable columns are more difficult for MySQL to optimize because NULL-capable columns make indexes, index statistics, and value comparisons more complex. Columns that can be NULL use more storage space and require special handling in MySQL. When a NULL-capable column is indexed, one extra byte is required for each index record, which in MyISAM can even cause a fixed-size index (such as an index on a single integer column) to become a variable-size index.

Usually the performance improvement of changing a NULLable column to NOT NULL is small, so there is no need to first find and modify this situation in the existing schema unless you are sure that this will cause problems. However, if you plan to create an index on a column, you should try to avoid designing it to be NULL-capable. Of course there are exceptions. For example, it is worth mentioning that InnoDB uses a separate bit to store NULL values, so it has good space efficiency for sparse data. This does not apply to MyISAM.

6. Storage Engine Selection

Regarding how to choose MyISAM and InnoDB, if you need transaction processing or foreign keys, then InnoDB may be a better choice. If you need full-text indexing, then MyISAM is usually a good choice because it is built into the system, however, we don't often test 2 million rows. So, even if it is a bit slower, we can get full-text indexes from InnoDB by using Sphinx.

The size of the data is an important factor that affects your choice of storage engine. Large data sets tend to choose InnoDB because it supports transaction processing and fault recovery. The size of the database determines the length of time it takes to recover from a failure. InnoDB can use transaction logs to recover data, which is faster. MyISAM may need

Instead of taking hours or even days to do these tasks, InnoDB only takes minutes.

Your habits of operating database tables may also be a factor that has a significant impact on performance. For example: COUNT() will be very fast in a MyISAM table, but may be painful in an InnoDB table. Primary key queries will be very fast under InnoDB, but we need to be careful that if our primary key is too long, it will cause performance problems. Large numbers of inserts will be faster with MyISAM, but updates will be faster with InnoDB - especially when there is a lot of concurrency.

So, which one do you use? According to experience, if it is some small application or project, then MyISAM may be more suitable. Of course, there are times when MyISAM can be used with great success in large environments, but this is not always the case. If you are planning to use a project with very large amounts of data and need transaction processing or foreign key support, then you really should use InnoDB directly. But remember that InnoDB tables require more memory and storage, and converting a 100GB MyISAM table to an InnoDB table may give you a very bad experience.

7. Avoid using or to connect in the where clause

If one field has an index and the other field does not, the engine will abandon the index and perform a full table scan, such as:

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

You can query like this:

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

8. Use varchar/nvarchar more often

Use varchar/nvarchar instead of char/nchar because firstly, variable-length fields take up less storage space and can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

9. Avoid returning large amounts of data

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

10. Where clause optimization

Using parameters in the where clause results in a full table scan because SQL resolves local variables only at run time, but the optimizer cannot defer the choice of an access plan until run time; it must make the choice at compile time. However, if the access plan is built at compile time, the value of the variable is still unknown and cannot be used as an input for index selection.

Try to avoid expression operations on fields in the where clause, and avoid function operations on fields in the where clause, as this will cause the engine to abandon the use of indexes and perform a full table scan. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the where clause, otherwise the system may not use the index correctly.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.