Let's share------MySQL optimization

MySQL Optimization Topic Development

--Wang Yaoyu

First, SQL Optimized

1. Analysis and positioning strategies

1. Learn the frequency of SQL execution through show status

2, location execution inefficient SQL statement: ① through slow log positioning; ② use the show processlist command to view the threads currently in progress

3. Analysis of inefficient SQL by explain

4. Analyze SQL through Show profile

5, through the Trace Analysis Optimizer selection

2. Optimization

1. Insert data in large quantities and quickly import large amounts of data (more significantly faster under the MyISAM engine) using the following methods:

ALTER TABLE name disable keys; Loading the data

Alter table name enable keys;

2. Optimize INSERT statement:

① inserts many rows into the same customer and uses the INSERT statement of more than one value table to greatly reduce the connection between the client and the database. For example, insert into table name values (1,3), (1,4)

② inserts many rows for different customers, you can use the Insert delayed statement for higher speed

③ Storing index files and data files on separate disks

④ loading a table from a text file uses the load data infile, which is about 20 times times faster than insert

3. Optimize the ORDER BY statement:

Two kinds of sorting methods in ①mysql: The first is sequential index sequence scan to return ordered data directly, and the second kind is

By sorting the returned data (Filesort sort)

②filesort optimization: Two scan algorithm and one-time scanning algorithm

4. Optimize the GROUP BY statement: use GROUP by NULL to avoid the consumption of user-ordered results.

5. Optimize nested queries: Subqueries can complete multiple step queries at once, while avoiding transactions or table deadlocks.

In some cases, however, a connection query can replace a subquery.

6, optimize or condition: Correct use or condition query, at that time when the column query with independent index, or operation can quickly

Finds the result, but cannot use an OR operation on a column that has a composite index

7, optimize the paging query:

① idea One: Complete the sort paging operation on the index, and finally the other column content required to query the original table based on the primary key, which allows MySQL to scan as few pages as possible to increase paging efficiency

② thought two: Convert limit query to a location query. This converts the limit m,n to limit n only for

The sort field does not appear with duplicate values in the environment

8. Use SQL prompt:

①use index: After the table name in the query statement, add use index to provide a list of the MySQL reference index, so that MySQL no longer consider other indexes

②ignore index: Use ignore index to allow MySQL to ignore one or more indexes

③force index: Force MySQL to use a specific index, which in some cases avoids full-second scanning

Two Index issues

1, the storage classification of the index

①b-tree index: Most common indexes, most engines support

②hash index: only memory engine support

③r-tree Index (spatial index): Rarely used, only understood

④full-text (full-text index): primarily for full-text indexing, InnoDB support from mysql5.6

2. Using indexed scenes in Mysql

① Match All values: Specify a specific value for all columns in the index, that is, the criteria for matching all columns in the index

Example: SELECT * from rental where rental_date= ' 2017-06-27 17:40:59 ' and customer_id=343;

② range query for matching values: Range lookup for indexed values

Example: SELECT * from rental where customer_id>=373 and customer_id<400;

③ matches the leftmost prefix: only the leftmost column in the index is used to find.

Example: The federated index on the A+b+c field can be used by the equivalent query of a, a+b, a+b+c, but not by the equivalent query of B and B+c.

④ the query is more efficient when it queries only the index and the columns of the document query are in the indexed fields

⑤ match column prefix: Only the first column in the index is used, and only the first part of the index is included

Find

The ⑥ index match section is exact and the rest of the range matches:

Example: Select a_id from rental where rental_date= ' 2006-02-14 ' and customer_id >=300 and customer_id < 400

⑦ If the column name is an index, use column_name is null to use the index

3. Scenarios with indexes but not indexed

① a like query that starts with% is not able to take advantage of the index

An index is not used when an implicit conversion of the ② data type occurs, especially if the column type is a string, it is important to enclose the string constant value in quotation marks in the Where condition, otherwise the index is invalid.

③ a composite index, if the query condition does not satisfy the leftmost principle, the index is not used

④ If MySQL estimates that using an index is slower than a full table scan, the index is not used

⑤ the condition that is separated by or, if the column in the condition before or is indexed, and the subsequent column does not have an index, then the index involved is not used

Third, Optimizing Database Objects

1. Optimize the data type of the table: what data type the table needs to use is judged by the application. You can use the Function procedure analyse () to analyze the currently applied table and make recommendations for optimization based on the current table.

2, by splitting to improve the efficiency of the table access:

① Vertical split: Place the main code and some columns into a table, and then place the main code and other columns in the other table

② Horizontal split: puts data rows into two separate tables based on the values of one or more columns

3, using the intermediate table to improve the speed of statistical query:

① Intermediate table copies the source table part of the data, and is "isolated" from the source table, statistical query on the intermediate table will not adversely affect the online users

② on the intermediate table can be flexibly added to the index or to increase the temporary use of new fields, so as to improve statistical query efficiency and auxiliary statistical query role

Four, Lock Problem

1, 3 kinds of MySQL lock

① table (Level) Lock: Low overhead, lock fast, no deadlock, lock size is large, lock collision probability is highest, concurrency is lowest (MyISAM, lock entire table, can read at the same time, not writable)

② row (level) Lock: Overhead, locking slow, deadlock, lock granularity is low, lock collision probability is lowest, concurrency is highest (InnoDB, single row of records locking)

③ page Lock: Overhead and lock time between table and row locks, deadlock, lock granularity between table and row locks, concurrency generally

2, MyISAM of the table-level lock

① Two modes: Table shared read lock (all users can read the same table lock at the same time, but cannot go to other operations)

Table Exclusive write Lock (a user cannot manipulate a table that has the table lock added when it is written to it). The read and write operations of the MyISAM table and the write operation are serial, allowing only one of the above operations to be

② Lock: MyISAM before executing the query statement (SELECT), will automatically give all the table read lock, before performing the update operation (update, DELETE, INSERT, etc.), will automatically add write lock to the table involved, this process does not require user intervention, is by the MyISAM Engine Auto-complete lock

③myisam Lock Scheduling: The read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. When a process requests a read lock on a MyISAM table and another process requests a write lock on the same table, MySQL causes the write process to acquire the lock first. Not only that, even if the read request goes to the lock waiting queue, and the write request is reached, the write lock is inserted before the read lock request

3, the InnoDB lock

① several lock modes:

Shared Lock (S Lock): Allows a transaction to read one line, preventing other transactions from acquiring exclusive locks on the same data set

Exclusive lock (X Lock): Allows transactions that obtain exclusive locks to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set. In addition, in order to allow row and table lock coexistence, the implementation of multi-granularity lock mechanism

Intent shared Lock (is lock): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before it is shared with a data row.

Intent exclusive Lock (IX Lock): The transaction intends to add an exclusive lock to the data row, and the transaction must first obtain the IX lock of the table before adding an exclusive lock to the data row.

② Lock mode: Intent Lock is InnoDB automatic addition, no user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement

③ when querying without an index condition, InnoDB does use a table lock instead of a row lock

④ because the MySQL row lock is for the index plus lock, not for the record plus the lock, so although it is access to the record, but if it is using the same index key, there will be a lock conflict Beijing Changping District Building Materials West Road Xisanqi office Building One Floor Tel: 400-618-9090

⑤ When a table has multiple indexes, different transactions can use different indexes to lock different rows, and the InnoDB uses row locks to lock the data, whether it is using a primary key index, a unique index, or a normal index.

4, the Lock use summary:

① table locks are more suitable for applications that are primarily query-based and have only a small number of update data by index criteria, such as WEB applications;

② row locks are more suitable for applications that have a large number of simultaneous updates of different data by index and concurrent queries, into some online transaction processing;

Five, Application Optimization

1. Using connection pooling

Connection pooling: Where the database needs to be accessed, it has been pre-created and can be directly accessed and assigned to the application, greatly reducing the resources required to create a new connection. At the end of the visit, the link is re-returned to the connection pool for new access to use

2. Reduce access to MySQL

① Avoid duplicate searches of the same data

② using Query Cache

③ Add the cache layer PS: The ③ and the ② role is similar, but the cache layer is larger than the query cache, the level is deep, the cache layer can be seen as a MySQL level two database, query cache equivalent to the internal MySQL cache

3, Load balancing: is a reuse of an optimization method, the use of some equalization algorithm, the fixed load distribution to different servers, so as to reduce the load balance of a single server, to achieve the goal of optimization.

① the use of MySQL replication shunt query operation: One primary server undertakes more operations, and many from the server bear the production query operations, the master-slave replication to achieve data synchronization. Multiple slave servers are used to ensure availability, on the one hand, you can create different indexes to meet the needs of different queries.

② adopts distributed Database architecture: Distributed database architecture is suitable for large data volume and high load, it has good expansibility and high availability. (This condition only supports the InnoDB storage engine)

4. Other optimization measures

① for MyISAM tables that do not have row operations removed, insert operations and query operations can be performed in parallel because the insert operation does not block during a table query without a delete operation

② takes full advantage of the fact that the column has a default value, and inserts the value explicitly only if the inserted value differs from the default value. This will reduce the parsing required by MySQL to improve the insertion speed.

③ table fields try not to use self-growth variables, and the self-growth of the field in high concurrency can have a greater effect on efficiency

Concept Supplement

① update is missing (Lost update): When two or more transactions select the same row, and then update the row based on the value originally selected, a missing update problem occurs because each transaction is unaware of the presence of other transactions-the last update overwrites the updates made by other firms.

② Dirty Read (Dirty Reads): A transaction is modifying a record, before the transaction is completed and submitted, the data of this record is in an inconsistent state, at this point, another transaction to read the same record, if not control, the second transaction read the "dirty" data, and thus do further processing , an uncommitted data dependency is generated. This phenomenon is visually called "Dirty reading".

③ non-repeatable read (non-repeatable Reads): A transaction reads some data some time after reading the previously read data, but found that its read data has changed, or some records have been deleted! This phenomenon is called "non-repeatable reading". Beijing Changping District Building Materials West Road Xisanqi office Building First Floor Tel: 400-618-9090

④ Phantom Read (Phantom Reads): A transaction re-reads the previously retrieved data in the same query condition, but finds that other transactions have inserted new data that satisfies its query criteria, which is called "Phantom Reading."

⑤ Deadlock (Deadlock): Refers to two or more two processes in the execution process, because of contention for resources caused by a mutual waiting phenomenon, these are always waiting for each other process known as the deadlock process. Table-level locks do not generate deadlocks, so resolving deadlocks is mostly true for the most commonly used InnoDB. PS: Before reading the data, lock it, prevent other transactions to modify the data to avoid dirty read, non-repeatable read, Phantom read

⑥ pessimistic lock (Pessimistic lock): The characteristic of pessimistic lock is to acquire the lock first, then carry on the business operation, namely "pessimistic" thinks acquires the lock is very likely to fail, therefore must first make sure obtains the lock succeeds again carries on the business operation

⑦ optimistic Lock (optimistic lock): Optimistic locking characteristics of the first business operations, not the last resort to take the lock. That is, "optimistic" that the lock is likely to be successful, so in the end of business operations need to actually update the data of the last step to get the lock.

⑧ Hot backup: A hot backup is a way to back up a database in the case of a database run. That is, a hot backup is a backup of the system in its normal running state.

⑨ Cold backup: A cold backup occurs when the database has been shut down normally, and a complete database is provided when it shuts down normally. A way to copy critical files to another location during cold backup. Cold backup is the fastest and safest method.

Let's share------MySQL optimization