Shulou(Shulou.com)06/01 Report--

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

The display width does not limit the range of values that can be saved in 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 has nothing to do with how much storage space int (M) occupies. It has nothing to do with the number of digits, int (3), int (4),

Int (8) takes up 4 btyes of storage space on disk.

When used in conjunction with the optional extension attribute ZEROFILL, the default supplementary space is replaced by zero. For example, for a column declared as INT (5) ZEROFILL

The value 4 is retrieved as 00004.

Bigint is used in some special cases where bigint can be used when integer values are beyond the range supported by the int data type.

Compare with the same type

Try to avoid using the! = or operator in the WHERE clause, otherwise the engine will give up using the index and do a full table scan

Use less JOIN

No SELECT *

You can find the slower SQL by opening the slow query log

No foreign keys are needed

Fields with sparse value distribution are not suitable for indexing.

The more indexes, the better. To create targeted indexes according to the query, consider indexing the columns involved in the WHERE and ORDER BY commands. You can check whether indexes or full table scans are used according to EXPLAIN.

Choose the right engine:

MyISAM

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

Row locks are not supported. Locks are applied to all tables that need to be read when reading and exclusive locks are added to tables when writing

Transactions are not supported

Foreign keys are not supported

Security recovery after crash is not supported

While the table has a read query, new records can be inserted into the table.

Support for the first 500 characters of BLOB and TEXT, and full-text indexing

Support for delayed updating of indexes, greatly improving write performance

For tables that will not be modified, compressed tables are supported to greatly reduce disk space consumption

InnoDB

InnoDB becomes the default index after MySQL 5.5, which is characterized by:

Supports row locks and uses MVCC to support high concurrency

Support transaction

Foreign keys are supported

Support for security recovery after a crash

Full-text indexing is not supported

Generally speaking, MyISAM is suitable for SELECT-intensive tables, while InnoDB is suitable for INSERT and UPDATE-intensive tables.

System tuning parameters

You can use the following tools for benchmarking:

Sysbench: a modular, cross-platform and multithreaded performance testing tool

Iibench-mysql: a tool for insert performance testing of MySQL/Percona/MariaDB indexes based on Java

TPC-C testing tool developed by tpcc-mysql:Percona

There are many specific tuning parameters. For more information, please refer to the official documentation. Here are some important parameters:

The back_log:back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. In other words, if the connection data of MySql reaches max_connections, new requests will be stored in the stack waiting for a connection to release resources. The number of back_log in the stack will not be granted connection resources if the number of waiting connections exceeds back_log. Can rise from the default 50 to 500

Wait_timeout: database connection idle time, idle connection will consume memory resources. Can be reduced from the default 8 hours to half an hour

Max_user_connection: maximum number of connections. Default is 0. There is no upper limit. It is best to set a reasonable upper limit.

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

Skip_name_resolve: disable DNS parsing of external connections, eliminating DNS parsing time, but require all remote hosts to access with IP

Key_buffer_size: the cache size of the index block. Increasing the cache size will increase the index processing speed and have the greatest impact on the performance of the MyISAM table. If the memory is about 4G, it can be set to 256m or 384m. It is best to query show status like 'key_read%', to ensure that the key_reads / key_read_requests is less than 0.1%.

Innodb_buffer_pool_size: caches data blocks and index blocks, which have the greatest impact on InnoDB table performance. Guarantee (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests as high as possible by querying show status like 'Innodb_buffer_pool_read%',

The innodb_additional_mem_pool_size:InnoDB storage engine is used to store data dictionary information and the memory space of some internal data structures. When there are a large number of database objects, 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, so it is necessary to resize this parameter.

The buffer used by the transaction log of the innodb_log_buffer_size:InnoDB storage engine is generally not recommended to exceed 32MB

Query_cache_size: cache the ResultSet in MySQL, that is, the result set of the execution of a SQL statement, so only for select statements. Any change in the data of a table will invalidate the cached data in the Query Cache of all select statements that reference the table. So, when our data changes very frequently, the loss of using Query Cache may outweigh the gain. Adjust according to the hit rate (Qcache_hits/ (Qcache_hits+Qcache_inserts) * 100). Generally speaking, it is not recommended to be too large. 256MB may be almost enough, and large-scale configuration static data can be adjusted appropriately.

You can view the current system Query catch usage by using the command show status like 'Qcache_%'

Read_buffer_size:MySql read-in buffer size. A request for a sequential scan of the table allocates a read buffer and MySql allocates a memory buffer for it. If sequential scan requests for a table are frequent, its performance can be improved by increasing the value of the variable and the memory buffer size

The buffer size used by sort_buffer_size:MySql to perform sorting. If you want to increase the speed of ORDER BY, first see if you can let MySQL use indexes instead of additional sorting phases. If not, you can try to increase 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 cache is allocated. When sorting a query, MySql scans the buffer first to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySql allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhead.

Record_buffer: each thread that performs a sequential scan allocates a buffer of this size to each table it scans. If you do a lot of sequential scans, you may want to increase this value

Thread_cache_size: saves threads that are not currently associated with a connection but are ready to serve a new connection later, and can quickly respond to thread requests for a connection without creating a new

Table_cache: similar to thread_cache_size, but used to cache table files, has little effect on InnoDB and is mainly used for MyISAM

Upgrade Hardwar

Scale up, needless to say, depending on whether MySQL is CPU-intensive or I-SSD-intensive, MySQL performance can be significantly improved by improving CPU and memory and using SSD.

Separation of reading and writing

It is also a commonly used optimization at present, reading and writing from the main library, generally do not use double-master or multi-master to introduce a lot of complexity, try to use other schemes in this paper to improve performance. At the same time, many split solutions also take into account the separation of read and write.

Caching

Caching can occur at these levels:

MySQL internal: the relevant settings are introduced in the system tuning parameters

Data access layer: for example, MyBatis caches SQL statements, while Hibernate can be accurate to a single record. Here, the cached object is mainly the persistent object Persistence object.

Application service layer: here you can control the cache more precisely and implement more strategies by programming. Here, the cached object is the data transfer object Data Transfer object.

Web layer: caching for web pages

Browser client: client-side cache

You can join the cache at one or more levels according to the actual situation. This article focuses on the caching implementation of the service layer. Currently, there are two main ways:

Write Through: after the data is written to the database, the cache is updated at the same time to maintain the consistency between the database and the cache. This is how most current application caching frameworks such as Spring Cache work. This implementation is very simple, good synchronization, but average efficiency.

Write Back: when there is data to write to the database, only the cache is updated and then synchronized to the database asynchronously in batches. This kind of implementation is more complex and requires more application logic. at the same time, it may cause the database to be out of sync with the cache, but it is very efficient.

Table partition

The partition introduced by MySQL in version 5.1 is a simple horizontal split. Users need to add partition parameters when creating the table, which is transparent to the application without modifying the code.

For users, the partition table is an independent logical table, but the underlying layer is composed of multiple physical child tables. The code for partitioning is actually encapsulated by the objects of a group of underlying tables, but for the SQL layer, it is a black box that completely encapsulates the underlying layer. The way MySQL implements partitioning also means that indexes are also defined according to partitioned child tables, and there is no global index.

The user's SQL statement needs to be optimized for the partition table. The column of partition condition should be included in the SQL condition, so that the query is located on a small number of partitions, otherwise all partitions will be scanned. You can use EXPLAIN PARTITIONS to see that a certain SQL statement will fall on those partitions, thus SQL optimization can be carried out.

The benefits of zoning are:

You can make a single table store more data.

Vertical split

Vertical split database is split according to the relevance of the data tables in the database, for example: there are both user data and order data in a database, so vertical split can put user data into user database and order data into order database. Vertical split table is a way to split a data table vertically. it is common to split a large multi-field table according to commonly used fields and unused fields. The number of data records in each table is generally the same, but the fields are different. Use primary key association.

The advantages of vertical splitting are:

Can make the row data smaller, a data block (Block) can hold more data, in the query will reduce the number of Block O (each query will read less Block)

It can achieve the goal of maximizing the use of Cache. Specifically, when splitting vertically, you can put together the fields that do not change very often, and put the fields that change frequently together.

Data maintenance is simple

The disadvantages are:

The primary key is redundant and redundant columns need to be managed.

Can cause table join JOIN operations (increase CPU overhead) can reduce database pressure by performing join on the business server

There is still the problem of excessive amount of data in a single table (horizontal split is required)

Transaction processing is complex

Horizontal split

Overview

Horizontal split is to divide the data into two parts: table and database through a certain strategy, each piece of data will be distributed to different MySQL tables or libraries to achieve the distributed effect and can support a very large amount of data. The previous table partition is essentially a special sub-table in the library.

The sub-table in the library only solves the problem that the data of a single table is too large. Because the data of the table is not distributed to different machines, it does not play a great role in reducing the pressure on the MySQL server. We still compete for IO, CPU and network on the same physical machine, which should be solved by dividing the library.

In practice, it is often a combination of vertical split and horizontal split, that is, Users_A_M and Users_N_Z are subdivided into Users and UserExtras, so that there are four tables

The advantages of horizontal splitting are:

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

There is less modification on the application side.

Improve the stability and load capacity of the system.

The disadvantages are:

The consistency of fragment transaction is difficult to solve.

Cross-node Join has poor performance and complex logic.

It is difficult to expand the data many times and has a great amount of maintenance.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.