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

The main content of this article is "performance Analysis of MySQL Database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "MySQL database performance analysis" it!

1. Brief introduction of MySQL performance optimization

In the Web application architecture, the data persistence layer (usually a relational database) is the key core part, which has a very important impact on the performance of the system. MySQL is currently the most widely used open source database, but the default performance of the MySQL database is very poor, just a toy database. Therefore, the use of MySQL database in the product must be optimized as necessary.

Optimization is a complex task. This paper describes MySQL-related database design and query optimization, server-side optimization, storage engine optimization.

two。 Database design and query optimization

In MySQL performance optimization, the first consideration is Database Schema design, which is very important. A poor Schema design will show poor performance even if it runs on a performance-tuned MySQL Server; like Schema, the design of query statements can affect the performance of MySQL, and you should avoid writing inefficient SQL queries. This section will discuss the optimization of these two aspects in detail.

2.1 Schema Design

The optimization of Schema depends on what kind of query will be run, and different query will have different Schema optimization schemes. Section 2.2 describes the optimization of Query Design. Schema design is also affected by the expected dataset size. The main considerations of Schema design: standardization, data type, index.

2.1.1 Standardization

Standardization is the process of organizing data in a database. These include creating tables according to design rules and establishing relationships between them, which can protect data and improve data flexibility at the same time by eliminating redundancy and inconsistent dependencies. Usually database standardization is to make the database design conform to a certain level of paradigm, usually to meet the third paradigm. There are also the fourth normal form (also known as Boyce Codd normal form, BCNF) and the fifth normal form, but it is rarely considered in the actual design. Ignoring these rules may make the design of the database imperfect, but this should not affect functionality.

Characteristics of standardization:

1) all "objects" are in its own table and have no redundancy.

2) the database is usually generated by Emurr graph.

3) succinct, updating attributes usually requires only a few records to be updated.

4) Join operation is time-consuming.

5) there are few measures to optimize Select,sort.

6) suitable for OLTP applications.

Non-standardized features:

1) storing a lot of data in a table, the data is redundant.

2) updating data is very expensive, updating an attribute may update many tables and records.

3) it is possible to lose data when deleting data.

4) Select,order has many optimization options.

5) suitable for DSS applications.

Both standardized and non-standardized have their own advantages and disadvantages, usually they can be mixed in a database design, some tables are standardized, and some tables retain some redundant data:

1) use standardization for OLTP and non-standardization for DSS

2) use materialized views. MySQL does not directly support this database feature, but you can use MyISAM tables instead.

3) redundant data in the table, such as storing ref_id and name in the same table. But pay attention to the update problem.

4) for some simple objects, use value directly as a build. Such as IP address, etc.

5) Reference by PRIMARY/UNIQUE KEY.MySQL can optimize this operation, for example:

Java code

Select city_name from city,state where state_id=state.id and state.code='CA' "converted to" select city_name from city where state_id=12

2.1.2 data types

One of the most basic optimizations is to make the table occupy as little space on disk as possible. This can lead to a significant performance improvement because the data is small, the disk reads faster, and the table contents are processed with less memory during the query process. At the same time, indexing on smaller columns takes up less resources.

You can use the following techniques to improve table performance and minimize storage space:

1) use the correct and appropriate type, and do not store numbers as strings.

2) use the most efficient (smallest) data types as possible. MySQL has many specialization types that save disk space and memory.

3) use smaller integer types to make the table smaller as much as possible. For example, MEDIUMINT is often better than INT because the MEDIUMINT column uses 25% less space.

4) if possible, declare as NOT NULL. It makes everything faster and saves one person per column. Note that if you do need NULL in your application, you should no doubt use it, but avoid having it on all columns by default.

5) for the MyISAM table, if you do not have any variable length columns (VARCHAR, TEXT, or BLOB columns), use a fixed size record format. This is faster, but unfortunately it may waste some space. Even if you have asked VARCHARcolumn row _ FORMAT=fixed with the CREATE option, you can also prompt you to use fixed-length lines.

6) use sample character set, such as latin1. Use utf-8 as little as possible, because utf-8 takes up three times as much space as latin1. You can use latin1 on fields that do not need to use utf-8, such as mail,url, and so on.

2.1.3 Index

All MySQL column types can be indexed. Using indexes on related columns is the best way to improve the performance of SELECT operations. You should pay attention to the following points when using an index:

1) MySQL will only use prefixes, such as key (a, b)... Where bread5 will not use the index.

2) use the index selectively. It is not good to use indexes on columns that have little change, such as gender columns.

3) define Unique index. Unique on the column.

4) avoid building indexes that are not used.

5) in Btree index (InnoDB uses Btree), you can index the columns that need to be sorted.

6) avoid duplicate indexes.

7) avoid building an index on the prefix of an existing index. For example, remove index (a) if index (a) exists.

8) controls the length of a single index. Use key (name (8)) to index the first few characters of the data.

9) the shorter the key value, the better, it is best to use integer.

10) to use the index in the query (using explain to view), you can reduce the number of times to read the disk and speed up the reading of data.

11) similar bond values are better than random ones. Auto_increment is better than uuid.

12) Optimize table can compress and sort index, so be careful not to run it frequently.

13) Analyze table can update the data.

2.2 Designing queries

Query optimization is a Case by case problem, different sql has different optimization solutions, here I only list some general techniques.

1) in the case of index, try to ensure that the query uses the correct index. You can use EXPLAIN select... View the results and analyze the query.

2) use matching types when querying. For example, select * from a where id=5, if id is a character type and has index, this query will not use index and will do a full table scan, which will be very slow. The right thing should be... Where id= "5", plus quotation marks, indicates that the type is a character.

3) use-- log-slow-queries-long-query-time=2 to view statements with slow queries. Then use explain to analyze the query and optimize it.

3. Server-side optimization

3.1 MySQL installation

There are many distributions of MySQL, and it is best to use the binary version released by MySQL AB. You can also download the source code for compilation and installation, but some bug of compilers and class libraries may make the compiled MySQL potentially problematic.

At this point, I believe that you have a deeper understanding of the "performance analysis of MySQL database", you might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.