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

This article focuses on "mysql database development common problems and optimization", 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 development common problems and optimization" it!

1. Database table design 1.1 engine selection

In mysql 5.1, a new plug-in storage engine architecture was introduced to allow the storage engine to be loaded into a new mysql server. The use of mysql plug-in storage engine architecture allows database professionals or software developers who design library tables to choose a special storage engine for specific application requirements without managing any special application coding requirements or considering all the underlying implementation details. Therefore, although different storage engines have different capabilities, the application is separate from it. In addition, users can store the engine at three levels: server, database, and table, providing great flexibility.

The storage engines commonly used by mysql include MYISAM, Innodb, and Memory, each of which has the following characteristics:

MYISAM: full table lock, with high execution speed, one write request, please block all read and write requests of the same table, poor performance, relatively small space, mysql 5.5 and below only MYISAM supports full-text indexing, not transactions.

Innodb: row-level lock (SQL all go index query), relatively strong concurrency, occupies 2.5 times the space of MYISAM, does not support full-text index (5.6support), and supports transactions.

Memory: full table lock, stored in memory, fast, but takes up memory space proportional to the amount of data, and data is lost when mysql is restarted.

Based on the above characteristics, it is recommended that most of them be set to innodb engine, and MYISAM or Memory should be considered for special business, such as full-text indexing support or high execution efficiency.

1.2 Sub-table method

In the process of using the database table, in order to reduce the burden of the database server and shorten the query time, the sub-table design is often considered. There are two kinds of sub-tables, one is vertical sub-table (the contents that could have been in the same table are artificially divided and stored in tables with different structures) and horizontal sub-table (large table structure is horizontally cut into different tables with the same structure).

Among them, the common ways of vertical sub-table are activity sub-table, importance sub-table and so on. The main problems solved are as follows:

Resource contention between tables

The probability of lock contention is small

Implement core and non-core hierarchical storage, such as splitting the UDB login library into first-level, second-level and third-level libraries.

The problem of database synchronization pressure is solved.

Horizontal sub-table refers to the division of big data scale according to some specific rules, such as time sub-table. The main problems solved are as follows:

Performance problems caused by excessive size of single meter

Single server space problem caused by single table is too large.

1.3 Index problem

An index is a structure that sorts the values of one or more columns in a database table, and indexing helps to obtain information more quickly. Mysql has four different index types:

Primary key Suothis (PRIMARY)

Unique index (UNIQUE)

General Index (INDEX)

Full-text indexing (FULLTEXT, MYISAM and Innodb above mysql 5.6)

The purpose of establishing an index is to speed up the search or sorting of the records in the table, and the more indexes, the better, because there is a price to pay for creating an index: first, it increases the storage space of the database; second, it takes more time to maintain the index when inserting and modifying data.

When designing a table or index, the following problems often occur:

Little or no indexing. This problem is the most prominent, it is suggested that DBA can help to check when building the table.

Index abuse. Misuse of indexes will slow down write requests and slow down the response speed of the overall database (only one index can be used for mysql below 5.5).

Never consider federated indexes. In fact, federated indexes are often more efficient than single-column indexes.

Non-optimal column selection. Fields with low selectivity are not suitable for single-column indexing, such as fields of type status.

Second, the cause of slow SQL caused by slow SQL

In the case of slow SQL, the cause cannot be simply attributed to SQL writing problems (although this is the most common factor). In fact, there are many factors that lead to slow SQL, including hardware and mysql's own bug. According to the probability of occurrence, from large to small, it is listed as follows:

Problems in writing SQL

Lock

Business instances compete for IO/CPU resources with each other

Server hardware

MYSQL BUG

2.2 slow SQL optimization caused by SQL writing

For slow SQL caused by SQL writing, it is relatively convenient to optimize. As mentioned in the previous section, the correct use of indexes can speed up queries, so we need to pay attention to the rules related to indexes when writing SQL:

Field type conversion results in no index, such as string type without quotation marks, numeric type with quotation marks, etc., which may lead to full table scan without using index.

Mysql does not support function conversion, so you cannot add a function in front of the field, otherwise the index will not be used.

Do not add or subtract before the field

If the string is long, you can consider the part of the index to reduce the size of the index file and improve the writing efficiency.

Like% does not need an index in the front

No index is needed for individual queries based on the second and subsequent fields of the federated index

Do not use select *

Please use ascending order as much as possible.

Try to use union instead of Innodb for or query.

The highly selective fields of the composite index are at the top.

The order by / group by field is included in the index to reduce sorting, which is more efficient.

In addition to the above rules for using indexes, there are a few points that SQL needs to pay special attention to when writing:

Try to avoid the SQL of large transactions. The SQL of large transactions will affect the concurrent performance of the database and master-slave synchronization.

The problem of paging statement limit

To delete all records in the table, please use truncate, not delete

Don't let mysql do superfluous things, such as calculating

Input and write SQL with fields to prevent problems caused by later table changes, and the performance is also excellent (when it comes to data dictionary parsing, please query the information yourself)

Use select count (*) on Innodb because Innodb stores statistics

Use Oder by rand () with caution.

Third, analysis and diagnosis tools

In the daily development work, we can do some work to prevent slow SQL problems, such as using diagnostic tools to analyze SQL before launch. Common tools are:

Mysqldumpslow

Mysql profile

Mysql explain

The specific use and analysis methods will not be described here, there are rich resources on the Internet for reference.

IV. What to do in case of misoperation or program bug

Obviously, this question is mainly aimed at young colleagues who are just starting to work. In fact, misoperation and program bug lead to data deletion or confusion is not uncommon, but new developers will be more nervous. A mature enterprise will often have perfect data management standards and rich data recovery programs (except start-ups), and will carry out data backup and data disaster recovery. When you find that misoperation or program bug has caused online data to be deleted or changed by mistake, you must not panic. You should contact DBA in time to recover the data as soon as possible (stop the service directly in serious cases) and reduce the impact and loss as much as possible. For the operation of important data (such as funds), be sure to test repeatedly during development to ensure that there are no problems before going online.

At this point, I believe that everyone on the "mysql database development of common problems and optimization" have a deeper understanding, might as well to the actual operation of it! 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.