MySQL optimization-The basics you need to know

After a lapse of 1.5, the period has been wanted to write but feel no substantive content can be recorded, this article for [high-performance MySQL] Learning log collation Sharing (interested in the proposal to read the original book).

Optimization should be throughout the product development cycle, the development process to consider a number of performance issues and impact, the problem is only beginning to rebuild the cost of optimization is lower, so these optimization knowledge should actually be required to have the common sense.

1, MySQL framework of some knowledge

1.1 The whole picture, the logical structure is as follows:

About optimization, is a more complex process, may involve operating system configuration, network/disk IO, memory, file systems and applications, etc., knowledge depth and broad, this article only for the MySQL server itself some of the optimized points to record.

1.2 Choosing the right storage engine is critical, and the MySQL lockdown model and concurrency are as follows:

Lock Policy	Concurrent	System overhead	Engine example
Table-Level plus lock	Minimum	Minimum	MyISAM, Merge, Memory
Row-level plus lock	High	High	NDB Cluster
Row-level lock support for MVCC	Highest	Highest	InnoDB

Description of some storage engines:

MyISAM

1. MyISAM is a table-level lock.
2. And does not support data recovery (service sudden crash should be used before check).
3. Transactions are not supported.
4. Only the index is cached in memory.
5. Data is tightly stored and the data is read more quickly.

InnoDB

1. Suitable for a large number of short transactions, using the MVCC mechanism to control the concurrency lock problem, row lock.
2. The default isolation level is repeatable read, and the Next-key locking policy is used to prevent phantom reads.
3. The InnoDB table is based on a clustered index, and the non-primary key index also contains primary key columns, so the primary key definition is as small as possible.

Memory

1. The data is stored in memory, the restart table structure is preserved, but the data is lost.
2. Hash indexes are supported, but table-level locks are only suitable for low writes, and text and blobs are not supported.

Note: temporary table and Memory table are two different things, the temporary table is visible only in a single connection, and the disconnection disappears.

Archive

1. Only insert and select are supported, and indexes are not supported. When inserting, the zlib algorithm is used to compress and occupy small space.
2. Any query will result in a full table scan.

NDB Cluster:

1. Contains data node, Manager node, SQL node, and redundancy in multiple data nodes.
2. Require high-performance network environment, to solve the replication delay problem.
3. The engine is not designed as a general-purpose storage engine and needs to be thoroughly understood before it can be used to meet the application scenario.

Selection reference factors for the storage engine:

1. Transaction, INNODB processing the transaction-type scenario is good.
2. Concurrency, MyISAM is very efficient if there is a small amount of insertions and reads, and the data is acceptable for crash loss.
3. Backup, whether online backup is required.
4. Crash recovery, MyISAM crash recovery time is very long in the case of large amounts of data.
5. Special features, such as sometimes used to the memory engine.

For general applications, InnoDB is usually a very conventional engine choice.

2, the relationship between the character set (although not related to optimization, but worth mentioning)

2.1 Creating built-in objects

For objects created by the MySQL service, without specifying a character set: Table Inherits database, database inherits server default character.

Therefore, it is generally recommended to specify the default character set for the database in My.cnf, as follows:

[Mysqld]collation-server=utf8_general_cicharacter-set-server=utf8

At the same time, it is recommended to set up the database explicitly define the database character set, easy to migrate or upgrade (if the migrated target server is not defined, dump import may not correspond to the character set, resulting in garbled, etc.).

2.2 The character set used for client/server communication

The character set settings used by communications typically include Character_set_client, character_set_connection, Character_set_result, and their relationships such as:

Use the show variables like '%character% ' under the command of the MySQL client; Displays the character set parameters used by the current connection.

Note: For MySQL client commands, modify the parameters described above by configuring the following parameters (view the path to the default load profile via MySQL--help--verbose):

[Mysql]default-character-set=utf8

For JDBC, you can add a URL such as Useunicode=yes&characterencoding=utf-8 to specify a specific character set, and it is generally recommended that you use a uniform character set to prevent unknown hassles.

3. Optimize data type

The optimization data type usually contains the following notes:

1. Smaller data types are usually faster, often not appropriate for a table structure that is automatically generated using ORM (for example, some projects use Hibernate to create tables automatically, generally not recommended, just start to save time, the cost still needs to return).

2. Try to avoid using null, MySQL is difficult to optimize the use of empty columns of the query, you can consider the use of 0/special value/empty string and other alternatives, if you plan to index the column, try to avoid setting to empty.

3. Integers have tinyint,smallint,mediumint,int,bigint, they need 8,16,24,32,64 bit storage space respectively.

4. Primary key: Usually the integer as the primary key has very fast index speed, and can self-growth, try to avoid using string type as an identifier, pay special attention to completely random strings (MD5,SHA1 () or UUID ()), they produce new values will be arbitrarily saved a lot of space in the range, Slowing down insert (inserted values are randomly placed into the index, resulting in paging, random disk and clustered index fragmentation on the clustered storage engine) and some select (because it distributes in disk and memory everywhere, resulting in multiple reads) of the query.

Note: If you do not care about the performance or the small amount of data applications, automatic generation of code and so on in order to catch the project, the UUID than the MD5,SHA1 is not evenly distributed and has a certain order.

5. String types (VRAHCR and char), varchar uses variable-length strings, Row_format defaults to dynamic, and fixed-length space is consumed if fixed is used. varchar uses an extra 1-2 bytes of storage length (less than 255 uses 1 bytes). MySQL uses fixed memory to store varchar values, such as varchar (20) and varchar (50) to hold the same length of data, even if they consume the same amount on disk, but the memory consumption is quite different.

4, about the index of some knowledge

For MySQL index, the default is B-tree (currently only this), so to understand some b-tree knowledge is helpful (need to find a book specifically to explain the structure), there are some points to refer to:

1. If you want to index a long string, the index becomes very large and slow, you can simulate a hash index, taking a string with a lower probability of conflict in the previous part.

2. Redundant indexes: If you create a composite index on the (A, B) column, you do not need to index the column A separately (for B-tree), because the index of the left is in effect with the leftmost prefix match, but it is not redundant to index B separately (because it is not the leftmost prefix).

3. InnoDB does not unlock rows until after the transaction has been committed, so the locking of rows is minimized. Lock more rows than needed to increase competition and reduce concurrency. InnoDB you can only filter unwanted data at the storage engine level to not lock unwanted rows, and if you return to the MySQL service and then use where filtering, you cannot lock down rows that are not needed. If you want to lock the first line that is not needed:

MySQL optimization-The basics you need to know