MySQL Database Development FAQs and optimization

MySQL database is a widely used relational database, its small size, multi-processor support, open source and free features make it in the Internet small and medium-sized web site usage is particularly high. In the process of using MySQL, irregular SQL writing, non-optimal policy selection can lead to system performance or even functional defects.
　　　　

Just a few days ago, I was in the company's cloud division held a technical seminar on MySQL, one part is focused on the development process of MySQL database design and use of common problems, and put forward the relevant optimization plan. According to the content of the meeting and access to relevant information, I have a summary of this part, combined with their work experience and understanding of the formation of this article for sharing, hoping to help colleagues to solve the relevant problems in the work.

This article will expand on the following three issues:

1. 1. Library table Design
   2. Slow SQL issues
   3. What to do in case of misoperation or program bug

First, the library table Design 1.1 engine selection

In MySQL 5.1, a new plug-in storage engine architecture was introduced, allowing the storage engine to be loaded into the new MySQL server being shipped. Using the MySQL plug-in storage engine architecture, it allows database professionals or software developers who design library tables to choose a dedicated storage engine for specific application requirements, without the need to manage any special application coding requirements or to consider all the underlying implementation details. Therefore, although different storage engines have different capabilities, the application is decoupled from it. In addition, users can store engines in server, database, and table three tiers, providing great flexibility.

MySQL's common storage engine includes MYISAM, Innodb, and memory, each of which features the following:

1. 1. MYISAM: Full table lock, with high execution speed, one write request block all read and write requests in the same table, concurrency performance is poor, occupy space is relatively small, MySQL 5.5 and below only MYISAM support full-text index, do not support transactions.
   2. Innodb: Row-level locks (SQL takes index queries), concurrency is relatively strong, occupying space is 2.5 times times the MYISAM, does not support full-text indexing (5.6 start support), support transactions.
   3. Memory: Full table lock, stored in the RAM, fast, but will occupy the amount of data proportional to the memory space and data will be lost when MySQL restarts.

Based on the above features, most of the recommendations are set to the InnoDB engine, and the special business considers the use of MYISAM or Memory, such as full-text indexing support or very high execution efficiency.

1.2 Minute Table method

In the database table use process, in order to reduce the burden of the database server, shorten the query time, often consider the design of the table. The Sub-table is divided into two types, one is the vertical sub-table (will be able to be in the same table of content, artificially divided into a number of different structures of the table) and the transverse table (the large table structure, transverse cutting for the same structure of the different tables).

Among them, the longitudinal sub-table common way has according to the activity degree table, according to the importance table and so on. The main solutions are as follows:

1. 1. The problem of resource contention between table and table;
   2. The probability of lock contention is small;
   3. Implementation of core and non-core hierarchical storage, such as the UDB Landing library split into a level two level three library;
   4. Resolves the database synchronization stress problem.

Horizontal sub-table refers to the classification of large data scale according to certain rules, such as the time table. The main solutions are as follows:

1. 1. Performance problems caused by large single-meter;
   2. Single-server space problem caused by large single-table.

1.3 Index issues

An index is a structure that sorts the values of one or more columns in a database table, and indexing facilitates faster access to information. There are four different types of indexes for MySQL:

1. 1. Primary key Cable This (PRIMARY)
   2. Unique index (unique)
   3. Normal index (indexed)
   4. Full-text index (fulltext, MYISAM and MySQL 5.6 + Innodb)

The purpose of indexing is to speed up the lookup or sorting of records in a table, as well as the number of indexes, because there is a cost to creating the index: one is to increase the storage space for the database, and the other is to spend more time maintaining the index when inserting and modifying the data.

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

1. 1. Do not build indexes or indexes. This is the most prominent issue, and it is recommended that DBAs work together to help check when building a table.
   2. Index abuse. Abusing the index will slow down the write request and slow down the overall database response (only one index is used for MySQL under 5.5).
   3. The federated index is never considered. In fact, the efficiency of a federated index is often more efficient than a single-column index.
   4. Non-optimal column selection. Low-selectivity fields do not fit into a single-column index, such as a field of status type.

Second, slow SQL problem 2.1 causes slow SQL

In the case of slow SQL, it is not easy to boil down to the problem of SQL writing (although this is the most common factor), which actually causes slow SQL to have many factors, even including the hardware and MySQL itself bugs. According to the probability of occurrence from large to small, listed as follows:

1. 1. SQL Authoring Issues
   2. Lock
   3. Business instances are competing with each other for IO/CPU resources
   4. Server hardware
   5. MYSQL BUG

2.2 Slow SQL optimizations due to SQL writing

It is relatively convenient to optimize for slow SQL due to SQL writing. As the correct use of indexes, as mentioned in the previous section, can speed up queries, we need to pay attention to index-related rules when writing SQL:

1. 1. field type conversions result in the use of no indexes, such as String type without quotation marks, numeric type quotes, etc., which may result in a full table scan without index;
   2. MySQL does not support function conversions, so the field cannot be preceded by a function, otherwise this will not be indexed;
   3. Do not add or subtract in front of the field;
   4. String longer can consider the index of a part to reduce the size of the index file, improve write efficiency;
   5. Like% is not used in front of the index;
   6. The index is not used for the second and subsequent fields of the federated index, as a separate query;
   7. Do not use SELECT *;
   8. Please use ascending order as far as possible;
   9. or query as far as possible to use Union instead (INNODB);
   10. Compound index high-selectivity fields are in front;
   11. The Order By/group by field includes reduced sorting in the index, which is more efficient.

In addition to the above-mentioned index usage rules, there are a few things that you need to pay special attention to when writing SQL:

1. 1. As far as possible to avoid large transactions of SQL, large transaction of SQL will affect the concurrency of the database and master-slave synchronization;
   2. The problem of the paging statement limit;
   3. Delete the table all records please use TRUNCATE, do not use delete;
   4. Don't let MySQL do superfluous things, such as calculations;
   5. Write SQL with fields to prevent subsequent table changes caused by the problem, the performance is also relatively excellent (involving data dictionary resolution, please self-query data);
   6. Use SELECT COUNT (*) on INNODB, because INNODB will store the statistical information;
   7. Use Oder by rand () with caution.

Iii. Analytical diagnostic tools

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

1. 1. Mysqldumpslow
   2. MySQL profile
   3. MySQL explain

Specific use and analysis methods are not mentioned here, there is a wealth of resources on the Internet can be consulted.

Iv. What to do when wrong operation, program bug

The question is clearly focused on the young colleagues who have just started work ... In fact, the problem of mis-operation and program bug is not uncommon, but the development workers will be more nervous. A mature enterprise will often have sound data management practices and a richer data recovery program (except for startups) that will perform data backup and data disaster. When you find that the wrong operation or program bug causes the online data to be mistakenly deleted or mistakenly changed, must not panic, should promptly contact the DBA, the first time for data recovery (serious when directly stop service), minimize the impact and loss. For important data, such as funds, you must test repeatedly during development to ensure that there are no problems before you go online.

MySQL Database Development FAQs and optimization