Welcome to the Linux community forum, and interact with 2 million technical staff to enter MySQL database performance optimization is the main core of this article, from database optimization design to specific operations. Good optimization can greatly improve the server performance. I hope this article will help you. 1. Introduction to MySQL performance optimization in the Web application architecture
Welcome to the Linux community forum and interact with 2 million technical staff> MySQL database performance optimization is the main core of this article, from database optimization design to specific operations. Good optimization can greatly improve the server performance. I hope this article will help you. 1. Introduction to MySQL performance optimization in the Web application architecture
Welcome to the Linux community forum and interact with 2 million technicians>
MySQL database performance optimization is the main core of this article, from database optimization design to specific operations. Good optimization can greatly improve the server performance. I hope this article will help you.
1. MySQL performance optimization Overview
In the Web application architecture, the data persistence layer (usually a relational database) is a key core component and has a very important impact on the system performance. MySQL is currently the most widely used open-source database, but the default setting of MySQL database has very poor performance, just a toy database. Therefore, you must optimize the MySQL database in the product.
Optimization is a complex task. This article describes MySQL-related database design and query optimization, server-side optimization, and storage engine optimization.
2. Database Design and Query Optimization
In MySQL performance optimization, the first thing to consider is the Database Schema design, which is very important. A bad Schema design, even running on the MySQL Server with excellent performance, will also show poor performance. Similar to the Schema, the design of query statements will also affect the performance of MySQL, inefficient SQL queries should be avoided. This section will discuss in detail the optimization of these two aspects.
2.1 Schema Design
Schema optimization depends on the query to be run. Different queries have different Schema optimization solutions. Section 2.2 describes the optimization of Query Design. The Schema design is also affected by the expected dataset size. Schema Design considerations include standardization, data types, and indexes.
2.1.1 Standardization
Standardization is the process of organizing data in databases. This includes creating tables based on design rules and establishing relationships between these tables. By canceling redundancy and inconsistent correlations, this design rule can simultaneously protect data and improve data flexibility. Generally, database standardization is to make the database design conform to a certain level of paradigm, and usually the third paradigm is enough. There are also the fourth paradigm (also known as Boyce Codd paradigm, BCNF) and the fifth paradigm, but it is rarely considered in actual design. Ignoring these rules may make the database design less perfect, but this should not affect the functionality.
Features of standardization:
1) All "objects" are in their own tables, with no redundancy.
2) databases are usually generated by E-R graphs.
3) Concise. To update attributes, only a few records need to be updated.
4) The Join Operation is time-consuming.
5) Select and sort have fewer optimization measures.
6) applicable to OLTP applications.
Non-Standardized features:
1) store a lot of data in a table and ensure data redundancy.
2) the overhead of updating data is very high. updating an attribute may update many tables and many records.
3) data may be lost when data is deleted.
4) Select and order have many optimization options.
5) applicable to DSS applications.
Both standardization and non-standardization have their own advantages and disadvantages. Generally, they can be used together in a database design. Some tables are standardized, while some tables retain some redundant data:
1) Standardized use of OLTP and non-standardized use of DSS
2) use materialized views. MySQL does not directly support this database feature, but it can be replaced by a MyISAM table.
3) redundant data is stored in tables. For example, ref_id and name are stored in the same table. But pay attention to the update issue.
4) for some simple objects, values are directly used for creation. For example, IP address
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 Type
One of the most basic optimizations is to minimize the disk space occupied by tables. This can greatly improve the performance, because the data is small, the disk reads faster, and the table content is processed during the Query Process, which consumes less memory. At the same time, if you create an index on a smaller column, the index also consumes less resources.
You can use the following technology to make the table better and minimize the storage space:
1) use the correct and appropriate type. Do not store numbers as strings.
2) use the most effective (minimum) data type as much as possible. MySQL has many specialized types that save disk space and memory.
3) try to use a smaller integer to make the table smaller. For example, MEDIUMINT is often better than INT, because the MEDIUMINT column uses 25% less space.
4) If possible, the declared column is not null. It makes everything faster and each column can save one bit. NOTE: If NULL is required in the application, you should use it without a doubt, but avoid having it on all columns by default.
5) for MyISAM tables, if there are no variable-length columns (VARCHAR, TEXT, or BLOB columns), use a fixed-size record format. This is fast, but unfortunately it may waste some space. Even if you have used the CREATE option to make the VARCHAR column ROW_FORMAT = fixed, you can also be prompted to use a fixed Length Row.
6) Use sample character set. For example, latin1. use UTF-8 as little as possible because UTF-8 occupies three times the space of latin1. You can use latin1 on fields that do not require UTF-8, such as mail and url.
2.1.3 Index
All MySQL column types can be indexed. Using indexes for related columns is the best way to improve the performance of SELECT operations. Pay attention to the following points when using indexes:
1) MySQL only uses the prefix, such as key (a, B )... Where B = 5 will not use the index.
2) Select an index. Using indexes on columns with few changes is not very good, such as gender columns.
3) define the Unique index in the Unique column.
4) Avoid creating indexes that cannot be used.
5) in the Btree index (InnoDB uses Btree), you can create an index on the columns to be sorted.
6) Avoid duplicate indexes.
7) avoid creating an index on the prefix of an existing index. For example, if index (a, B) exists, remove index ().
8) control 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. integer is recommended.
10) using indexes in queries (using explain for viewing) can reduce the number of disk reads and accelerate data reading.
11) Similar key values are better than random values. Auto_increment is better than uuid.
12) Optimize table can compress and sort indexes, so do not run them frequently.
13) Analyze table can update data.
2.2 Designing queries
The optimization of query statements is a Case by case problem. Different SQL statements have different optimization schemes. Here I will only list some general techniques.
1) When there is an index, make sure that the correct index is used for the query. You can use EXPLAIN select... View the results and analyze the query.
2) use the matching type during query. For example, select * from a where id = 5. If the id here is of the character type and has an index, the index cannot be used for this query, and the full table scan will be performed, resulting in a slow speed. Which of the following statements is true... Where id = "5", with quotation marks indicating the type is a character.
3) Use -- log-slow-queries? Long-query-time = 2 view slow query statements. Use the explain statement to analyze the query and optimize the query.
3. Server Optimization
3.1 install MySQL
MySQL has many release versions. 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 bugs in the compiler and class library may cause potential problems in compiled MySQL.
If the MySQL server is installed on an Intel processor, you can use the intel c ++ compiled version. As mentioned in a PPT in Linux World2005, mySQL compiled using the intel C ++ compiler can query about 30% faster than the normal version. The compiled version of Intel c ++ can be downloaded from the official MySQL website.
[1] [2] [3]