MySQL optimization and type

MySQL also supports choosing to specify the display width of integer values (for example, INT (4)) within parentheses after the type keyword. Int (m) in the integer data type, m represents the maximum display width, and the optional display width specifies that the width is filled from the left when the value of the width less than the specified column width is displayed.

The display width does not limit the range of values that can be saved within the column, nor does it limit the display of values that exceed the specified width of the column.

in int (m), the value of M is not related to how much storage space the Int (m) occupies. is not related to the number of digits, int (3), int (4),

an int (8) occupies 4 btyes of storage space on the disk.

When combined with optional extended attribute Zerofill, the default supplemental space is replaced with 0. For example, for a column declared as int (5) Zerofill,

The value 4 is retrieved as 00004.

BigInt is used in some special cases when the integer value exceeds the range supported by the INT data type, you can use bigint.

Compare using the same type

Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for full table scanning using the index

Use less Join

No SELECT *

Slower SQL can be found by turning on the slow query log

No foreign keys

Fields with very sparse value distributions do not fit the index

The better the index is, the better, based on the query's targeted creation, consider indexing the columns involved in the Where and order by commands, depending on the explain to see if an index or a full table scan is used

Choose the right Engine:

MyISAM

The MyISAM engine is the default engine for MySQL 5.1 and earlier, and it features:

Row locks are not supported, locks are added to all tables that need to be read, and locks are added to the table when writing

Transaction not supported

Foreign keys are not supported

Post-crash security recovery is not supported

Supports inserting a new record into a table while the table has read queries

Supports the first 500 character indexes of BLOBs and text, supporting full-text indexing

Supports deferred update of indexes, greatly improves write performance

Supports compressed tables for tables that are not modified, greatly reducing disk space consumption

InnoDB

InnoDB is the default index after MySQL 5.5, and it features:

Support for row locks, with MVCC to support high concurrency

Support Transactions

Support for foreign keys

Support for post-crash security recovery

Full-text indexing is not supported

Overall, MyISAM is suitable for select-intensive tables, while InnoDB is suitable for insert and update-intensive tables

System Tuning Parameters

There are several tools that you can use to benchmark your tests:

Sysbench: A modular, cross-platform and multi-threaded performance testing tool

Iibench-mysql: Java-based MYSQL/PERCONA/MARIADB index for insert performance testing tools

TPC-C test tool developed by Tpcc-mysql:percona

Specific tuning parameters more content, specifically can refer to official documents, here are some of the more important parameters:

The Back_log:back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. That is, if the MySQL connection data reaches max_connections, the new request will be present in the stack, waiting for a connection to release the resource, the number of that stack is back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be granted. Can be from the default of 50 liters to 500

Wait_timeout: Database connection idle time, idle connection consumes memory resources. Can be reduced from the default of 8 hours to half an hour

Max_user_connection: Maximum number of connections, default is 0 No limit, preferably set a reasonable limit

Thread_concurrency: Number of concurrent threads, set to twice times the number of CPU cores

Skip_name_resolve: Disable DNS resolution for external connections, eliminate DNS resolution time, but require IP access for all remote hosts

Key_buffer_size: The cache size of the index block increases the processing speed of the index, which has the greatest impact on the performance of the MyISAM table. For memory 4G or so, can be set to 256M or 384M, by querying show status like ' key_read% ', ensure key_reads/key_read_requests under 0.1% best

Innodb_buffer_pool_size: Cache data blocks and index blocks that have the greatest impact on InnoDB table performance. By querying show status like ' innodb_buffer_pool_read% ', guarantee (innodb_buffer_pool_read_requests–innodb_buffer_pool_reads)/ The higher the innodb_buffer_pool_read_requests, the better.

INNODB_ADDITIONAL_MEM_POOL_SIZE:INNODB storage engine is used to store data dictionary information and some internal data structure of memory space size, when the database objects are very large, Adjust the size of this parameter appropriately to ensure that all data can be stored in memory to improve access efficiency, when too small, MySQL will record warning information to the database error log, it is necessary to adjust this parameter size

Innodb_log_buffer_size:innodb the buffer used by the transaction log of the storage engine, which is generally not recommended for more than 32MB

Query_cache_size: Caches resultset in MySQL, which is a result set executed by an SQL statement, so only for SELECT statements. Any change in the data of a table causes all the SELECT statements referencing the table to fail cached data in the query cache. So, when our data changes very frequently, using the query cache may not be worth the candle. According to the hit Ratio (qcache_hits/(qcache_hits+qcache_inserts) *100) to adjust, it is generally not recommended too large, 256MB may have been almost, large-scale configuration of static data can be suitably adjusted.
You can view the current system query catch usage size by command show status like ' qcache_% '

Read_buffer_size:mysql read-in buffer size. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. If the sequential scan request for a table is very frequent, you can increase its performance by increasing the value of the variable and the memory buffer size

Sort_buffer_size:mysql the buffer size used to perform the sort. If you want to increase the speed of the order by, first see if you can let MySQL use the index instead of the extra sort stage. If not, you can try increasing the size of the sort_buffer_size variable

The random read buffer size of the read_rnd_buffer_size:mysql. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

Record_buffer: Each thread that makes a sequential scan allocates a buffer of that size for each table it scans. If you do many sequential scans, you may want to increase the value

Thread_cache_size: Save a thread that is not currently associated with a connection but is prepared for a new connection service behind it, can quickly respond to a thread request for a connection without creating a new

Table_cache: Similar to thread_cache_size, but used to cache table files, the InnoDB effect is small, mainly used for MyISAM

Upgrading hardware

Scale up, which is not much to say, depending on whether MySQL is CPU-intensive or I/o-intensive, improves MySQL performance significantly by increasing CPU and memory, using SSDs

Read/write separation

is also the most commonly used optimization, from the library read the main library write, generally do not adopt the dual master or multi-master introduced a lot of complexity, as far as possible to use the other scenarios in the text to improve performance. At the same time, many split solutions also consider the read-write separation

Cache

Caches can occur at these levels:

MySQL internal: In the system tuning parameters describes the relevant settings

Data Access layer: For example, MyBatis cache for SQL statements, and hibernate can be accurate to a single record, where the object being cached is mostly persisted objects persistence object

Application Service layer: Here can be programmed to achieve more precise control of the cache and more implementation of the policy, where the object is the data transfer objects data Transfer object

Web tier: Caching for Web pages

Browser client: User-Side Caching

The cache can be joined at one or more levels, depending on the actual situation. There are two main ways to introduce the cache implementation of the service layer:

Write Through: After the data is written to the database, the cache is updated to maintain consistency between the database and the cache. This is also how most of the current application caching frameworks work like the spring cache. This implementation is very simple, good synchronization, but efficiency in general.

Writeback (write back): when there is data to write to the database, only the cache is updated, and the cached data is synchronized to the database asynchronously and in bulk. This implementation is more complex, requires more application logic, and may result in a database and cache out of sync, but the efficiency is very high.

Table partitioning

MySQL introduced in the 5.1 version of the partition is a simple horizontal split, users need to build the table when the partition parameter, the application is transparent without the need to modify the code

A partitioned table is a separate logical table for the user, but the bottom layer consists of multiple physical sub-tables, and the code that implements the partition is actually encapsulated by an object of a set of underlying tables, but to the SQL layer it is a completely encapsulated black box. The way MySQL implements partitioning also means that the index is defined by the Sub-table of the partition, with no global index



The user's SQL statement is to be optimized for the partition table, the columns in the SQL condition with the partition condition, so that the query is positioned on a small number of partitions, otherwise it will scan all partitions, you can see by explain partitions a SQL statement will fall on those partitions, For SQL optimization

The benefits of partitioning are:

Allows you to store more data in a single table

Vertical split

Vertical Sub-Library is based on the database inside the data table of the correlation of the split, such as: a database within the existence of both user data and order data, then vertical splitting can put the user data into the user library, the order data placed in the order database. Vertical sub-table is a way to split the data table vertically, it is common to divide a large table of multiple fields by characters commonly used and non-characters commonly used segments, the number of data records in each table is generally the same, but the fields are different, using the primary key association

The advantages of vertical splitting are:

Can make the row data smaller, a block of data can hold more data, the query will reduce the number of I/O times (each query when the block is read less)

Can achieve the purpose of maximizing the use of the cache, specifically in the vertical split can be Geofang together, will often change the place together

Simple Data Maintenance

The disadvantages are:

Redundancy in primary key requires management of redundant columns

Causes the table join JOIN operation (increased CPU overhead) to reduce the database pressure by joining on the business Server

There is still a problem with too much single-table data (horizontal splitting is required)

Transaction processing Complexity

Split horizontally

Overview

Horizontal splitting is a kind of strategy to store data shards, sub-library sub-tables and sub-Library two parts, each piece of data will be dispersed to different MySQL tables or libraries, to achieve distributed results, to support a very large amount of data. The preceding table partition is essentially a special in-Library sub-table

In the Library sub-table, simply to solve the problem of single table data is too large, because the table data is not distributed to different machines, so to alleviate the pressure of MySQL server, and not much role, we still compete with a physical machine on the IO, CPU, network, this will be through the sub-Library to solve



The actual situation is often the combination of vertical splitting and horizontal splitting, the users_a_m and Users_n_z will be disassembled into the users and Userextras, so that altogether four sheets

The advantages of horizontal splitting are:

There is no single-library big data and high concurrency performance bottleneck

Less application-side retrofit

Improved system stability and load capacity

The disadvantages are:

Shard transaction consistency is difficult to resolve

Poor cross-node join performance with complex logic

Data multiple expansion difficulty and maintenance volume

MySQL optimization and type