Shulou(Shulou.com)06/01 Report--

The following brings you about the optimization of mysql database what are the main methods, if interested, then take a look at this article, I believe that after reading the optimization of mysql database what are the main methods to help you a little bit.

Methods to optimize mysql database: establish Index index, use select statement less, open query cache, select suitable storage engine, avoid using or in where clause to connect and avoid large amount of data return, etc.

For a data-centric application, the database directly affects the performance of the program, so the database performance is very important. So mysql database optimization operation we all have to understand, this article mainly summarizes the mysql database common optimization operation, the following words do not say much, to see the detailed introduction it.

1. Index

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

2. Less use of SELECT*

Some people may query the database, encounter to query will select, this is inappropriate behavior. We should take the data we want, not all of it, 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.

3. EXPLAIN SELECT

Many people have never seen this feature, but it is highly recommended here. Explain shows how mysql uses indexes to process select statements and join tables. It helps to select better indexes and write more optimized query statements. The main use is to add explanation before select.

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

IV. Open query cache

Most MySQL servers have query caching enabled. This is one of the most effective ways to improve performance and is handled by MySQL's database engine. When many of the same queries are executed multiple times, the query results are placed in a cache so that subsequent queries of the same query can access the cached results without manipulating 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, the memory size is allocated to the query cache, controlling the maximum value of cached query results. Related actions are modified in the configuration file.

5. Use NOT NULL

Many tables contain Nullable columns, even if the application well does not need to hold NULL, because Nullable is the default property for columns. It is usually best to specify columns as NOT NULL unless you really need to store NULL values.

If the query contains Nullable columns, it is harder for MySQL to optimize because Nullable columns make indexes, index statistics, and value comparisons more complex. Nullable columns use more storage space and require special handling in MySQL. When Nullable columns are indexed, each index record requires an extra byte, which in MyISAM may even cause a fixed-size index (e.g., an index with only one integer column) to become a variable-size index.

Generally, changing a Nullable column to NOT NULL gives a small performance boost, so it is not necessary to first look for wells in an existing schema to modify this situation unless you are sure that this will cause problems. However, if you plan to index columns, you should try to avoid columns designed to be NULL. There are exceptions, of course; for example, it is worth mentioning that InnoDB uses separate bits to store NULL values, so it is very space efficient for sparse data. This does not apply to MyISAM.

VI. Selection of storage engines

For how to choose MyISAM and InnoDB, InnoDB may be the better way if you need transactions or foreign keys. If you need full-text indexing, MyISAM is usually a good choice because it's built in, however, we don't really test two million rows very often. So, even if it's a bit slower, we can get full-text indexes from InnoDB by using Sphinx.

Data size is an important factor in which storage engine you choose, and large size datasets tend to choose InnoDB because of its support for transaction processing and failover. The size of the database determines how long it takes to recover from a failure. InnoDB can use transaction logs for data recovery, which is faster. MyISAM may need

For hours or even days, InnoDB takes minutes.

Your habit of manipulating database tables can also be a big performance factor. For example, COUNT() can be very fast in MyISAM tables, but painful in InnoDB tables. Primary key queries are fairly fast under InnoDB, but be careful if our primary key is too long it can cause performance problems. A large number of inserts statements are faster under MyISAM, but updates are faster under InnoDB--especially when concurrency is high.

So, which one are you going to use? As a rule of thumb, MyISAM may be more suitable for smaller applications or projects. Of course, MyISAM can have great success in large environments, but not always. If you are planning to use a large data volume project and need transaction processing or foreign key support, then you really should go straight to InnoDB. But remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables may give you a very bad experience.

7. Avoid using or to connect in where clauses

If a field has an index and a field has no index, it will cause the engine to abandon the index and perform a full table scan, such as:

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

You can query:

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

8. Use varchar/nvarchar more often

Use varchar/nvarchar instead of char/nchar, because first of all, variable length fields have small storage space, which saves storage space, and second, for queries, it is obviously more efficient to search in a relatively small field.

IX. Avoid large data volume return

Here, consider using limit to limit the amount of data returned. If you return a large amount of data that you don't need each time, it will also slow down the query speed.

X. Where clause optimization

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

Avoid expression operations on fields in where clauses and avoid function operations on fields in where clauses--this will cause the engine to forgo indexes in favor of a full table scan. Do not perform functions, arithmetic operations, or other expression operations to the left of "=" in where clauses, or the system may not use indexes correctly.

Read the above details on what are the main methods to optimize mysql database, whether there is any harvest. If you want to know more about it, you can continue to pay attention to our industry information section.

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.