MySQL Performance optimization experience

CSDN Blog Relocation

1. Introduction
In the Web application architecture, the data persistence layer (usually a relational database) is a key core part, which has a very important impact on the performance of the system. MySQL is currently the most used open source database, but MySQL database default settings performance is very poor, just a toy database. Therefore, the use of MySQL database in the product must be optimized as necessary.
Optimization is a complex task, this document describes MySQL-related database design and query optimization, server-side optimization, storage engine optimization.

2. Database design and query optimization
in MySQL server performance tuning, the first thing to consider is the database schema design, which is very important. A bad schema design can show poor performance even if it runs on a performance-tuned MySQL server, similar to schema, the design of query statements can also affect MySQL performance and should avoid writing inefficient SQL queries. This section will discuss these two aspects of optimization in detail. The optimization of the

2.1 schema Design
Schema depends on what type of query will be run, and the schema optimization scheme is different for different query. Section 2.2 describes the optimization of query design. The schema design is also affected by the expected size of the dataset . Schema design is mainly concerned with: standardization, data type, index.

2.1.1 Normalization

Normalization is the process of organizing data in a database. This includes creating tables based on design rules and establishing relationships between them, and by eliminating redundancy and inconsistent correlation, the design rules can simultaneously protect data and improve the flexibility of the data. Typically, database normalization is a form of database design that conforms to a certain level of paradigm, usually satisfies the third normal form (the property does not depend on other non-primary properties and depends only on the primary property of the related table ). The also has the fourth paradigm (also known as the Boyce Codd paradigm, BCNF) and the fifth paradigm, but is seldom considered in practical design. Ignoring these rules may make the database design less than perfect, but this should not affect functionality.
Features of normalization:

1) All "objects" are in its own table, with no redundancy.
2) The database is usually generated by the E-r diagram.
3) Concise, updated properties usually require only a few records to be updated.
4) Join operation is time consuming.
5) Select,sort optimization measures are relatively small.
6) suitable for OLTP applications. ( oltp-online transaction processing is the main application of traditional relational database, it is mainly for basic, daily transaction, such as bank transaction )

Non-standardized features:

1) Store a lot of data in a single table, data redundancy.
2) Update data overhead is large, updating a property may update many tables, many records.
3) It is possible to lose data when deleting data.
4) Select,order has a lot of optimization options.
5) suitable for DSS applications. That is, OLAP (online analysis processing, on-line Analytical Processing) ( DSS) is a decision support system (decision) that assists decision makers through data, models, and knowledge, Computer application system for semi-structured or unstructured decision making with human-computer interaction

Both standardization and non-standardization have their own advantages and disadvantages, usually in one database design can be mixed, some tables standardized, some tables retain some redundant data:

1) Standardization of OLTP usage, non-standardized use of DSS
2) Use materialized views. MySQL does not directly support this database feature, but it can be replaced with a MyISAM table.
3) Redundant some data in the table, for example, the ref_id and name exist in the same table. However, be aware of the update issue.
4) for some simple objects, use value directly as the building. such as IP address, etc.
5) Reference by Primary/unique KEY. MySQL can optimize this operation, for example:
Java code

1. Select City_name
2. From City,state
3. where State_id=state.id and state.code= ' CA ' "converted to" select City_name from city where state_id=

2.1.2 Data types
One of the most basic optimizations is to make the table occupy as little space as possible on disk. This can result in a very high performance boost, because the data is small, the disk is read faster, and the table content is processed in less memory during the query. At the same time, indexing on smaller columns also takes up less resources.
You can use the following techniques to make the table more performance and minimize storage space:

1) Use the correct appropriate type and do not store the number as a string.
2) Use the most effective (minimum) data types possible. MySQL has a lot of specialized types that save disk space and memory.
3) Use a smaller integer type as much as possible 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 declaration is listed as not NULL. It makes anything faster and each column can save one bit. Note If you do need null in your application, you should definitely use it, just to avoid having it on all columns by default.
5) for MyISAM tables, use a fixed-size record format if there are no variable-length columns (VARCHAR, text, or BLOB columns). It's faster but unfortunately it may be a waste of space. Even if you've already used the Create option to row_format=fixed the varchar column, you can also prompt for a fixed-length row.
6) Use sample character set, such as Latin1. Use as little utf-8 as possible, because the utf-8 occupies 3 times times more space than latin1. You can use latin1, such as Mail,url, on fields that do not need to use utf-8.

2.1.3 Index
All MySQL column types can be indexed. Using indexes on related columns is the best way to improve the performance of select operations. The following points should be noted when using indexes:

1) MySQL will only use prefixes, such as key (A, B) ... where b=5 will not be used in the index.
2) Use the index to select the sex. It is not very good to use indexes on columns that vary very little, such as the sex column.
3) define unique index on the unique column.
4) Avoid creating indexes that are not used.
5) in Btree index (InnoDB using btree), you can index on the columns that need to be sorted.
6) Avoid duplicate indexes.
7) Avoid indexing on an existing index prefix. For example, if Index (a, B) is present, remove index (a).
8) controls the length of a single index. Use Key (name (8)) to index several characters in front of the data.
9) The shorter the key value the better, the best use of integer.
10) in the query to use the index (using explain viewing), you can reduce the number of read disk, speed reading data.
11) Similar key values are better than random. Auto_increment is better than a UUID.
Optimize table can compress and sort index, and be careful not to run frequently.
Analyze table can update data.

2.2 Designing queries (Query SQL Optimization)
The optimization of a query statement is a case-by-case problem, and different SQL has different optimization schemes, and here I only list some common tricks.

1) In the case of the index, try to ensure that the query uses the correct index. You can use explain select ... View the results and analyze the query.
2) Use a matching type when querying. For example SELECT * from a where id=5, if the ID is a character type, and there is index, this query is not used to index, will do a full table scan, the speed will be very slow. The right one should be ... where id= "5", enclose the quotation mark to indicate that the type is a character.
3) Use--log-slow-queries–long-query-time=2 to view the slow statement of the query. Then use explain to analyze the query and make the optimization.

3. Server-Side optimization
3.1 MySQL Installation
MySQL has a lot of distributions and it's 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 with the compiled MySQL.
If the server that is installing MySQL is using Intel's processor, you can use the Intel C + + compiled version, as mentioned in a PPT in Linux World2005, the MySQL query compiled with the Intel C + + compiler is about 30% faster than the normal version. The Intel C + + compiled version can be downloaded from the MySQL official website.

3.2 Server Settings optimization
MySQL default settings performance is poor, so to do some adjustment of parameters. This section describes some of the general parameter adjustments that do not involve a specific storage engine (mainly referred to as MYISAM,INNODB, and related optimizations are described in 4).

--character-set: If it is a single language, use simple character set such as Latin1. Try to use less utf-8,utf-8 and occupy more space.
--memlock: Lock MySQL can only run in memory and avoid swapping, but it is possible to have an error if memory is insufficient.
--max_allowed_packet: To be large enough to accommodate larger SQL queries, there is not much impact on performance, mainly to avoid packet errors.
--max_connections:server the maximum allowable connection. Too big will appear out of memory.
--table_cache:mysql keeps the number of open table at the same time. Opening table overhead is relatively large. The general setting is 512.
--query_cache_size: The amount of memory used to cache the query.
--datadir:mysql stores the root directory of the data, and the installation files separate on different disks can improve a bit of performance.

4. Storage Engine Optimization
MySQL supports different storage engines, mainly using MyISAM and InnoDB.

4.1 MyISAM
MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations, it is the default storage engine, unless you configure MySQL to use a different engine by default.

4.1.1 MyISAM Characteristics
4.1.1.1 MyISAM Properties

1) Do not support transactions, downtime destroy table
2) Use a smaller amount of memory and disk space
3) Table-based locks, which can cause serious performance problems when updating data concurrently
4) MySQL caches only index, data is cached by OS

4.1.1.2 Typical MyISAM usages

1) Log system
2) read-only or mostly read-operation applications
3) Full table scan
4) Bulk Import data
5) Low concurrent read/write with no transaction

4.1.2 MyISAM Optimization Essentials

1) The Declaration column is not NULL and can reduce disk storage.
2) Use optimize table to defragment and reclaim free space. Note that only after very large data changes are run.
3) It is forbidden to use index when deleting/updating/adding large amounts of data. Use the Alter TABLE T DISABLE KEYS.
4) Set myisam_max_[extra]_sort_file_size large enough to significantly increase the speed of the repair table.

4.1.3 MyISAM Table Locks

1) Avoid concurrent insert,update.
2) You can use insert delayed, but it is possible to lose data.
3) Refine the query statement.
4) Horizontal partitioning.
5) Vertical partitioning.
6) If all does not work, use InnoDB.

4.1.4 MyISAM Key Cache

1) Set key_buffer_size variable. Myisan's primary cache setting, which caches the index data for a MyISAM table, affects only MyISAM. You typically set 25-33% memory size in a server that uses only MyISAM. The
2) can use several different key Caches (for some hot data).

a) SET GLOBAL test.key_buffer_size=512*1024;
B) CACHE INDEX t1.i1, t2.i1, T3 in test;

2) preload index into the cache can improve query speed. Because preloading index is sequential, so it's very fast.

a) LOAD INDEX into CACHE t1, T2 IGNORE LEAVES;

4.2 InnoDB
InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB provides row level lock and also provides an Oracle-style, non-locking read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to widen the lock in the InnoDB because the row level lock is suitable for very small spaces in InnoDB. InnoDB also supports foreign key constraints. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.
InnoDB is designed to maximize performance when dealing with large amounts of data. It has very high CPU usage.
The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.
Many large database sites that require high performance use the InnoDB engine. The famous Internet news site slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and some other sites handle an average of 800 insertions/updates per second on InnoDB.
4.2.1 InnoDB characteristics
4.2.1.1 InnoDB Properties

1) Support transaction, ACID, foreign key.
2) Row level locks.
3) support for different isolation levels.
4) requires more memory and disk space than the MyISAM.
5) No key compression.
6) Data and indexes are in memory hash table.

4.2.1.2 InnoDB Good for

1) applications that require transactions.
2) high-concurrency applications.
3) Automatic recovery.
4) faster operation based on primary key.

4.2.2 InnoDB Optimization Essentials

1) Try to use Short,integer's primary key.
2) Press primary key order when Load/insert data. If the data is not sorted by primary key, then the database operation is sorted first.
3) The load data is for setting the set Unique_checks=0,set foreign_key_checks=0, which avoids the overhead of foreign key and uniqueness constraint checks.
4) Use prefix keys. Because InnoDB does not have a key compression function.

4.2.3 InnoDB server-side settings

Innodb_buffer_pool_size: This is the most important setting for InnoDB, which has a decisive impact on InnoDB performance. The default setting is only 8M, so the default database setting below InnoDB performance is poor. You can set 60-80% memory on a database server that has only the InnoDB storage engine. To be more precise, set the memory size to 10% larger than InnoDB tablespaces under the memory capacity allowed.

Innodb_data_file_path: Specifies the space for table data and index storage, which can be one or more files. The last data file must be automatically expanded, and only the last file will allow automatic expansion. Thus, when the space is exhausted, the auto-expansion data file will automatically grow (in 8MB) to accommodate the additional data. For example: Innodb_data_file_path=/disk1/ibdata1:900m;/disk2/ibdata2:50m:autoextend two data files on different disks. The data is first placed in the ibdata1, and when it reaches 900M, the data is placed in the IBDATA2. Once the 50MB,IBDATA2 is reached, it will automatically grow in 8MB units. If the disk is full, you need to add a data file to the other disk.

Innodb_autoextend_increment: The default is 8M, if the amount of insert data is more, you can increase it appropriately.

Innodb_data_home_dir: Place table space Data directory, default in MySQL data directory, set to and MySQL installation file different partition can improve performance.

Innodb_log_file_size: This parameter determines the recovery speed. If it's too big, recovery will be slow, too small. Impact query performance, generally take 256M to balance performance and recovery speed
。
Innodb_log_buffer_size: Disk speed is very slow, directly log writes to the disk will affect the performance of InnoDB, this parameter sets the size of the log buffer, generally 4M. If you have a large blob operation, you can increase it appropriately.

innodb_flush_logs_at_trx_commit=2: This parameter sets the processing of log information in memory when the transaction commits.

1) = 1 o'clock, when each transaction commits, the log buffer is written to the log file, and the log file is refreshed with the disk operation. Truly ACID. Slow speed.
2) = 2 o'clock, when each transaction commits, the log buffer is written to the file, but the log file is not refreshed with disk operations. Only the operating system crashes or loses power to delete the last second of the transaction, or the transaction will not be lost.
3) = 0 o'clock, the log buffer is written to the log file once per second, and the log file is refreshed with disk operations. The crash of any mysqld process will delete the last second of the transaction before the crash

Innodb_file_per_table: You can store each InnoDB table and its index in its own file.

Transaction-isolation=read-comitted: If the application can run at the Read-commited isolation level, this setting will have some performance gains.

Innodb_flush_method: How to set InnoDB Sync io:

1) default– use Fsync ().
2) O_sync open files in SYNC mode, usually slower.
3) O_direct, use DIRECT IO on Linux. Can significantly increase the speed, especially on RAID systems. Avoid additional data duplication and double buffering (MySQL buffering and OS buffering).

Innodb_thread_concurrency:innodb kernel The maximum number of threads.

1) Minimum set to (Num_disks+num_cpus) * *.
2) You can disable this restriction by setting the 1000来 to

5. Caching
There are many kinds of caches, and adding an appropriate caching strategy to your application can significantly improve the performance of your application. Because the application cache is a relatively large topic, this part needs further investigation.

6. Reference
1) http://www.mysqlperformanceblog.com/
2) Advanced MySQL performance optimization, Peter Zaitsev, Tobias asplund, MySQL Users Conference 2005
3) improving MySQL Server performance with Intel C + + Compiler,peter Zaitsev,linux World 2005
4) MySQL performance optimization, Peter Zaitsev, Percona Ltd, OPEN SOURCE DATABASE CONFERENCE 2006
5) MySQL Server Settings Tuning, Peter Zaitsev, co-founder, Percona Ltd, 2007
6) MySQL Reference Manual

MySQL Performance optimization experience