Mysql database is a widely used relational database. Its small size, multi-processor support, open source and free features make it especially popular in small and medium-sized Websites on the Internet. In the process of using mysql, non-standard SQL writing and non-optimal policy selection may lead to system performance and even functional defects.


Just a few days ago, the cloud Business Department of my company held a technical exchange meeting about mysql, one part of which focused on the common problems in the design and use of mysql database in the development process, and proposed relevant optimization solutions. According to the content of the meeting and related information, I have made a summary of this part, combined with my own work experience and understanding to form this article for sharing, hoping to help you to solve the relevant problems in the work.

This paper will carry out the following three issues:

  1. Library table design
  2. Slow SQL problem
  3. How to deal with misoperation and program bug

First, library 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 that is running. Using the mysql plug-in storage engine architecture allows database professionals or software developers designing library tables to select a specific storage engine for specific application requirements without having to manage any special application coding requirements or consider all the low-level implementation details. Thus, although different storage engines have different capabilities, applications are separated from them. In addition, users can store the engine in server, database, and table hierarchies, providing great flexibility.

Storage engines commonly used by mysql include MYISAM, Innodb and Memory. Their respective features are as follows:

  1. MYISAM: full table lock, with high execution speed, a write request will block all read/write requests of the same table, poor concurrency performance, relatively small space, mysql 5.5 and below only MYISAM support full text index, does not support transactions.
  2. Innodb: row-level lock (SQL all go index query), relatively strong concurrency, occupy 2.5 times the space of MYISAM, does not support full text index (5.6 support), support transactions
  3. Memory: Full table lock, stored in Memory, fast, but will occupy Memory space proportional to the amount of data and data will be lost during mysql restart.

Based on the above features, it is recommended that most of the innoDB engine be set. For special services, MYISAM or Memory can be considered, such as full-text indexing support or high execution efficiency.

1.2 Table method

In the process of using database table, in order to reduce the burden of database server and shorten the query time, it is often considered to do the design of split table. Sub-table is divided into two kinds, one is longitudinal sub-table (can be in the same table content, artificial partition stored in a number of different structures of the table) and horizontal sub-table (the large table structure, transverse cut into different tables of the same structure).

Among them, the common ways of vertical sub-table are according to activity sub-table, according to importance sub-table, etc. Its main problems are as follows:

  1. Resource contention between tables;
  2. Lock contention probability is small;
  3. Realize hierarchical storage of core and non-core, such as UDB landing database is divided into one level, two levels and three levels
  4. Solve the database synchronization stress problem.

Horizontal tabulation refers to the division of big data tables according to certain rules, such as time tabulation. Its main problems are as follows:

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

1.3 Index Problems

An index is a structure that sorts the values of one or more columns in a database table. Mysql has four different index types:

  1. PRIMARY key (PRIMARY)
  2. UNIQUE index
  3. Normal INDEX
  4. FULLTEXT index (FULLTEXT, MYISAM and mysql 5.6 + Innodb)

The purpose of creating an index is to speed up the lookup or sorting of the records in the table, and more indexes are not always better, because creating an index comes at a cost: it increases the storage space of the database, and it takes more time to maintain the index when inserting and modifying data.

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

  1. Create few or no indexes. This is the most prominent problem, and it is recommended that dbAs help with the gatekeeping during table building.
  2. Index abuse. Abusing indexes will slow down write requests and slow down the overall database response (mysql under 5.5 only uses one index).
  3. Joint indexes are never considered. In fact, joint indexes are often more efficient than single-column indexes.
  4. Non-optimal column selection. Low-selectivity fields are not suitable for single-column indexes, such as status fields.

Second, slow SQL problem

2.1 Causes of slow SQL

When you encounter slow SQL, you can’t simply blame it on SQL writing problems (although this is the most common factor). In fact, there are many factors that cause slow SQL, including hardware and mysql bugs. According to the probability of occurrence from large to small, listed as follows:

  1. SQL writing Problems
  2. The lock
  3. Service instances compete with each other for I/O/CPU resources
  4. Server hardware
  5. MYSQL BUG

2.2 Slow SQL optimization caused by SQL writing

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

  1. Field type conversion results in no index, such as string type without quotation marks, number type with quotation marks, etc., which may not use index, resulting in full table scan.
  2. Mysql does not support function conversions, so you cannot add a function before a field, otherwise it will not use the index;
  3. Do not add or subtract in front of fields;
  4. If the string is long, index part can be considered to reduce the index file size and improve write efficiency.
  5. Like % does not use index in front;
  6. Separate queries based on the second and subsequent fields of the federated index do not use the index;
  7. Do not use select *;
  8. Please use ascending order as far as possible;
  9. Or query as far as possible with union instead of (Innodb);
  10. Fields with highly selective composite indexes come first;
  11. The order BY/Group by fields are included in the index to reduce sorting, which is more efficient.

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

  1. Avoid SQL of large transaction as far as possible, because SQL of large transaction will affect the concurrency performance and master/slave synchronization of database.
  2. Problems with paging statement limits;
  3. Use TRUNCate instead of DELETE to delete all records in a table.
  4. Don’t let mysql do unnecessary things like computations.
  5. Write SQL with fields, in order to prevent problems caused by table changes, performance is relatively good (involving data dictionary analysis, please query the data);
  6. Use select count(*) on Innodb because Innodb stores statistics;
  7. Use Oder by rand() with caution.

Analysis of diagnostic tools

There are things you can do to prevent slow SQL problems on a daily basis, such as pre-analyzing SQL with diagnostic tools before going live. Common tools are:

  1. mysqldumpslow
  2. mysql profile
  3. mysql explain

Specific use and analysis methods are not described here, there are abundant resources on the Internet for reference.

Four, misoperation, program bug how to do

This question is obviously aimed at young professionals just starting out… It’s not uncommon to see errors and bugs causing data to be deleted or scrambled, but developers who are just starting out can be nervous. A mature enterprise usually has comprehensive data management specifications and abundant data recovery solutions (except start-up companies), and can perform data backup and data disaster recovery. When you find that online data is deleted or altered by mistake due to misoperation or program bug, you should not be in a panic. You should contact the DBA in time and restore the data in the first time (directly stop the service if it is serious) to reduce the impact and loss as much as possible. For important data (such as funding) operations, be sure to test repeatedly during development to ensure there are no problems before launching.