MySQL Performance tuning

1. Macro-tuning can be considered three parts, respectively, hardware, network, software, the main consideration here is software tuning

(1) Software tuning includes: table Design (Paradigm, field type, data storage engine), SQL statement Index, configuration file parameters, file system, operating system, MySQL version, architecture

2. Table Design

(1) First paradigm (1NF): The fields in a database table are single attributes and cannot be divided. This single attribute consists of basic data types, including integer, character, logical, date, and so on. (As long as the relational database conforms to the first normal form)

(2) Second paradigm (2NF): Requires that an entity's attributes depend entirely on the primary key, that is, there can be only one primary key. Also that non-critical fields are dependent on the primary key

(3) Third paradigm (3NF): No non-critical fields are dependent on the transfer function of any of the candidate key fields.

3. Field type selection: The general principle is to keep small, can be used in less than the number of bytes of the field is not large.

3.1 Numeric types

(1) Enter mobile phone number with bigint

(2) IP address can be unsigned int integer, and the use of Inet_ntoa () is responsible for converting the number to an IP address, Inet_aton () is responsible for converting IP address to Digital

(3) Select the minimum integer type as required

3.2 Character types

(1) The maximum length of the varchar is calculated: varchar (32766)

3.3 Time Type

4. Adopt a more appropriate locking mechanism

The 4.1 MySQL lock is divided into three types:

(1) Table-level lock: The cost is small, lock fast, there will be no deadlock, lock granularity is large, the probability of conflict is the highest, the lowest degree of concurrency. MyISAM engine

(2) Row-level lock: High overhead, locking slow, deadlock, lock granularity is minimal, the probability of conflict is the lowest, the concurrency is the highest. InnoDB engine

(3) Page Lock: in the middle. NDB engine

4.2 MyISAM Engine:

(1) Read operation to the MyISAM table (read lock), other processes can read, but will block the same table write operations;

(2) The write operation to the MyISAM table will block other threads from reading and writing to the same table, and the read and write operations of other processes will be performed only when the write lock is released;

4.3 InnoDB engine: The InnoDB storage engine is implemented by locking the index entries on the index, which means that the InnoDB uses row-level locks only if the data is retrieved through the index criteria, otherwise the INNODB will take a table lock (to prevent high concurrency access, A large number of transactions are suspended due to the inability to obtain the required locks immediately, which can consume a large amount of computer resources, causing serious performance problems, which could be resolved by setting the appropriate lock wait timeout threshold parameter innode_lock_wait_timeout, which is typically set to 100s)

5 Select the appropriate transaction isolation level

5.1 Properties of the transaction:

(1) atomicity

(2) Consistency

(3) Isolation

(4) Durability

5.2 Indeterminate Read conditions

(1) Dirty read: One transaction begins to read a row of data, another transaction has updated this data but is not committed in time. A rollback may occur for another transaction.

(2) Non-repeatable reads: A transaction repeats two times for the same row of data, but gets different results. For example, on the way to two reads, there was another transaction that modified and committed the data for that row.

(3) Two update problem: a special case that cannot be read repeatedly. Two concurrent transactions read the same row of data at the same time, and one of them modifies the commit and the other commits the modification. This causes the first write operation to fail.

(4) Phantom reading: The transaction was queried two times during the operation, and the result of the second query contained data that did not appear in the first query. This is because there is another transaction that inserts data during the two queries.

5.3 INNODDB Transaction ISOLATION LEVEL

(1) Read UNCOMMITTED reads UNCOMMITTED: Dirty reads are generated. ("Exclusive write lock")

(2) Read Committed: Will produce non-repeatable reads. ("Instant shared read lock" and "exclusive write lock")

(3) Repeated read REPEATABLE READ: Generates Phantom reads. ("Shared read lock" and "exclusive write lock")

(4) Serilizable serialization: Provide strict transaction isolation, transaction strict serialization, transactions can only be executed one by one, cannot be executed concurrently.

6. SQL optimization and rational use of indexes

6.1 To locate a slow-executing SQL statement by turning on slow log: (explain optimizer query)

To turn on slow logging: In the MY.CNF configuration file, add the following parameters:

Slow_query_log=1

Slow_query_log_file=mysql.slow

long_query_time=2 (more than 2 seconds of SQL will be logged)

6.2 SQL Optimization Case study

(1) Not in sub-query optimization: Try to avoid subqueries, using the LEFT JOIN table connection

(2) Pattern matching like '%xxx% ': in MySQL, like ' xxx% ' can be indexed, and like '%xxx% ' does not; Use Overlay index for optimization

(3) Limit paging optimization

(4) count (*) statistics how to speed up: Use secondary index count (secondary index) faster than COUNT (*);

(5) If there is an OR condition in the SQL statement, the index will not be used, and it will be changed to union ALL result set merge

(6) unnecessary sequencing;

(7) unnecessary nested SELECT queries

(8) Unnecessary connection of the table itself,

(9) Replace the HAVING clause with a WHERE clause

6.3 Proper use of indexes: indexes have some overhead, such as the need to update indexes when writing and updating or deleting operations. In addition, the benefits of index performance gains can be enjoyed only when a column is used in a WHERE clause

(1) Comparison of single and federated indexes: Federated indexes follow the leftmost principle

(2) field use function, will not be used to index

(3) Fatal non-quotation marks result in full table scan, unable to use index:

(4) When the amount of data fetched exceeds 20% of the data in the table, the optimizer does not use the index, but the full table scan

(5) Consider not indexing for some columns:

(6) Group BY, Order by optimization

(7) MYSQL5.6 InnoDB engine supports full-text indexing

(8) MYSQL5.6 Support explain Update/select

(9) MySQL5.6 optimized index merge indexed, that is, you can implement a SQL can use two indexes

7. MY.CNF Configuration file Tuning

7.1 Per_thread_buffers Optimization: Allocates memory for each user process connected to MySQL

(1) Read_buffer_size: A sequential scan for a table that represents the buffer size allocated for each thread.

(2) Read_rnd_buffer_size: Used for random reading of tables, indicating the buffer size allocated per thread

(3) Sort_buffer_size: With the order by and group by sort operations in the table, using Filesort appears because the sorted fields are not indexed, and this parameter can be used to increase the buffer size allocated per thread in order to improve performance.

(4) Thread_stack: Represents the stack size of each thread

(5) Join_buffer_size:

(6) Max_connections: This parameter is used to set the maximum number of connections, default 100. The general setting is 512 to 1000.

7.2 global_buffers Optimization: Used to cache data blocks retrieved from a data file in memory, which can greatly improve the performance of querying and updating data.

(1) Innodb_buffer_pool_size: Core parameter, default is 128MB

(2) Innodb_additional_mem_pool_size: Used to store data dictionary information and other internal data structures, the more tables, need to allocate more memory, a version set to 16MB

(3) Innodb_log_buffer_size: The buffer used by the transaction log.

7.3 Query cache is used in different environments: its function is to cache SELECT statements and result sets. The query cache will never return stale data, and any related entries in the query cache will be erased when the data is modified.

If there are many write operations in the environment, it is not appropriate to open query_cache_type, and if read operations are frequent and write operations are infrequent, query_cache_type=1 is turned on.

MySQL Performance tuning