MySQL performance optimization

1, do not use sequential lookup, because the order lookup is slow, through the characteristics of the specific data structure to improve the query speed, this data structure is can be understood as an index.

2, the index is generally stored on disk in the form of files, index retrieval requires disk I/O operations, in order to minimize disk I/O. The disk is often not strictly on-demand read, but each time it is read-ahead, and the main memory and disk in the page to exchange data, so when the data read is not in main memory, a batch of data from the disk (page) into main memory.

3, no matter in which program optimization, want to fast high performance, directly read the commonly used, less changed data directly into memory, the use of the time directly in memory read, not to read on the disk, reduce I/O operations, so that the program can be more than 10 times times faster. However, due to the limited memory capacity, it is not possible to put all the data in memory.

MySQL Index classification

• Normal Index: the most basic index, without any restrictions.

• Unique index: similar to "normal index", the difference is that the value of the indexed column must be unique, but a null value is allowed.

• PRIMARY KEY index: It is a special unique index and is not allowed to have null values.

• Full-Text indexing: available only for MyISAM tables, generating full-text indexes is a time-consuming space for larger data.

• Combined index: for more MySQL efficiency, you can create a composite index that follows the "leftmost prefix" principle.

Overwrite index (covering Indexes)

is to go directly to the index, directly in memory to get the value, do not need to query the database.
such as paging is going to cover the index, because the performance is relatively high.

Clustered index (Clustered Indexes), the primary key is the aggregate index

The clustered index guarantees that the values of the keywords are similar to the physical locations of the tuples stored (so the string type should not be built
Clustered indexes, especially random strings, can make the system do a lot of moving operations), and a table can only
has a clustered index. Because indexes are implemented by the storage engine, not all engines support the cluster cable
Cited. Currently, only SOLIDDB and InnoDB support.

Non-clustered index

Secondary index The leaf node saves a pointer to the physical location of the row, but rather the primary key value of the row. This means that the pass-through
Find rows over level two indexes.

InnoDB the clustered index on the primary key. If you do not specify a primary key, InnoDB replaces it with an index that has a unique and non-null value. If such an index does not exist, InnoDB defines a hidden primary key and then establishes a clustered index on it. In general, the DBMS stores the actual data in the form of a clustered index, which is

The basis for other two-level indexes.

Some things to be aware of in order to optimize (focus)

1, the index is actually a kind of classification, when a field attribute can not be classified, after the index is no effect, or the collation of two (0 and 1), and each of the data in half, the effect is not strong after the index.

2, the index of the primary key is not the same, to distinguish between understanding.

3. The prefix index can be established when the time store is saved as a timestamp.

4, in what is the field index, you need to depend on the query criteria, do not build an index, waste of memory may not be used.

5, large print (BLOB) do not index, the query will not go index.

6, commonly used to establish the index of the place:

1) clustered index of the primary key
2) foreign key index
3) categories only 0 and 1 do not index, no meaning, no improvement on performance, also affect write performance
4) Use the blur to actually be able to walk the prefix index

7, the unique index must be carefully used, it has a unique constraint, due to the lack of pre-demand, and so on, may cause us to the unique column of the false.

8, because we build the index and want to achieve the highest performance index, this time we should fully consider whether the column is suitable for indexing, can be judged according to the column's degree of sensitivity, the degree of discrimination is too low can not consider the establishment of an index, the higher the efficiency of the higher the distinction.

SELECT COUNT(DISTINCT 列_xx)/COUNT(*) FROM 表

9, write more frequently, can not open the query cache MySQL, because at each write not only to write to the disk also update the data in the cache.

10. Purpose of Indexing:

1) speed up the query, use the index after the query is traceable.
2) reduce I/O operations, retrieving data through indexed paths, rather than randomly retrieving them on disk.
3) Eliminate the sorting of the disks, the indexes are sorted, and the indexes are sorted out.

11, in fact, the principle of building the index is to minimize the disk I/O operations, not in the order of the disk, but in the memory of the order, by sorting rules to specify the disk read the line, and do not need to be randomly read on the disk.

12, due to disk defragmentation disk fragmentation, all the time we can also build a clustered index to reduce this type of problem.

13, when there are 1 million data in a table, and the data used is only 400,000 or 400,000, it is not necessary to consider indexing, there is no performance improvement.

14, when not suitable for indexing:

1) Frequently updated fields are not suitable for indexing
2) fields that are not available in the Where condition are not suitable for indexing, and are not used to make an index meaningless and waste space
3) Table data can be determined less than the need to build an index
4) data duplication and the publication of a more uniform field does not fit the index (the uniqueness of the field is not suitable for indexing), such as gender, true and False value
5) The columns that participate in the column calculations do not fit into the index, such as:

select * from table where amount+100>1000，-- 这样是不走索引的，可以改造为：select * from table where amount>1000-100。

15. It is recommended to use COUNT (*) instead of Count (column) When using count statistics, because the count (*) MySQL is optimized.

16. Two times SQL query when the difference is not small, not according to the time of the two execution to determine the optimization results, the first query and then save the cached data, resulting in a second query faster than the second time, many times we see the illusion.

17, when to open the query cache MySQL, trading system (write more, read less), SQL Optimization test, proposed to close the query cache, forum article class system (write less, read more), it is recommended to open the query cache.

18. The Explain execution plan can only interpret the select operation.

19, query optimization can consider to let the query go Index, walk Index can improve the query speed, index coverage is the fastest, the following is to let the paging go overlay index to improve query speed.

Select * from fentrust e
Inner join (select fid from fentrust limit 4100000, 10) a on a.fid = e.fid

20, sub-query faster than join, although the law is not absolute, but the large table most effective

21, the idea of the optimization of complex SQL statements:

1) First consider whether you can get the relevant information in a table, as few as possible related tables
2) association conditions to go to the primary key or foreign key query conditions, can go to the corresponding index
3) strive to meet the business to walk small collection of data to find
4) INNER JOIN and subquery which is faster, the scene inconsistent speed is also different

22, where condition multi-condition must follow the small result set row large result set front

23, try to avoid large transaction operations, improve the system concurrency, sometimes can not be avoided, instead of timer delay processing.

24, what situation does not go index:

   SELECT  ' Famount '  from  ' fentrust '  WHERE  ' famount ' +  10=  30;< span>--does not use indexes because all indexed columns participate in the calculation of 
 SELECT  ' Famount '  from  ' fentrust '  WHERE  left (  ' Fcreatetime ',  4) <  1990; -The index is not used because the function operation is the same as above 
 SELECT *  from  ' fuser '  WHERE  ' floginname '  Like '  138%  '-Go to index 

                               SELECT * from ' fuser ' WHERE ' floginname ' like '%7488% '--do not go index--Regular expressions do not use indexes, which should be well understood , so why it's hard to see the REGEXP keyword in SQL--the string is not indexed compared to the number; 

                               EXPLAIN SELECT * from ' a ' WHERE ' a ' = 1--Do not go index 

                               SELECT * from fuser WHERE floginname= ' xxx  ' or femail= ' xx  ' or Fstatus=1--if there is or in the condition, it will not be used even if there is a conditional index. In other words, all the fields that are required to be used must be indexed, and we recommend that you avoid using the OR keyword           

25. If MySQL estimates that using a full table scan is faster than using an index, the index is not used.

26. Use UNION ALL to replace or multi-conditional query union.

27, in the Big data table Delete is also a problem, to avoid deleting the process database, you can consider the allocation of deletion, delete 1000 at a time, after the deletion of a will continue to delete

delete from logs where log_date <= ’2012-11-01’ limit 1000

28, Big data table optimization:

1) Create a summary table
2) Create a water table
3) Sub-database sub-table

29, the establishment of the summary table, the first do not consider the sub-database sub-table, the use of timers scheduled to summarize.

30, sub-table, can be divided horizontally or vertically. Vertical sub-table is the use of frequently used data and rarely used data for vertical segmentation, segmentation to different libraries, improve the library's data capacity, such as: 3 months before the transaction can be placed in another library.

31, the establishment of water tables, data redundancy, there is this table records the flow of changes will not have to write complex SQL computing water.

32, sub-Library, multi-database The same library structure, distribution processing concurrency, but also bring the data synchronization problem, you can also use the separate library

32. SQL Optimization Order:

1) Try to make less calculations.
2) join as little as possible.
3) Try to be less ordered.
4) Try to avoid select *.
5) Try to use join instead of subquery.
6) as little or as possible.
7) Try to use union all instead of union.
8) filter as early as possible.
9) Avoid type conversions.
10) Prioritize high-concurrency SQL instead of performing some "big" SQL with low frequency.
11) Optimize from a global perspective, rather than one-sided adjustment.
12) Explain every SQL that runs in the database whenever possible.

33. The following are 30 Big data table optimization points:

1) to optimize the query, avoid full table scans, and first consider indexing on the columns involved in where and order by.
2) should try to avoid null values for the field in the WHERE clause, otherwise it will cause the engine to discard full table scans using the index, such as: Select ID from t where num is null to set the default on num 0, make sure that the NUM column in the table does not have a null value, and then query: Select ID from t where num=0
3) should try to avoid using the! = or <> operator in the WHERE clause, Otherwise, the engine discards full table scanning using the index.
4) should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to discard full table scans using the index, such as: Select ID from t where num=10 or num= 20 This can be queried: Select ID from the Where num=10 union all select IDs from T where num=20
5) in and not in are also used with caution, otherwise it will guide For a full table scan, such as: Select ID from the Where Num in (three-to-three) for consecutive values, you can use between to not use in: The Select ID from t where num between 1 and 3

 

6) The following query will also cause a full table scan: Select ID from t where name like ' Li% ' to improve efficiency, you can consider full-text indexing.
7) If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan: The select id from where [email protected] can be changed to force the query to use the index: SELECT ID from the T with (index name) where [Email&nbs P;protected]
8) should try to avoid expression operations on the field in the Where clause, which will cause the engine to discard the use of the index for a full table scan. For example: Select ID from t where num/2=100 should be changed to: Select ID from t where num=100*2
9) should try to avoid functional operation of the field in the Where clause, This causes the engine to discard the full table scan using the index. For example: Select ID from t where substring (name,1,3) = ' abc ', name begins with ABC ID should be changed to: Select ID from t where name like ' abc% '
do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, or the index may not be used correctly by the system.

 

one) when using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used. And, as far as possible, match the order of the fields to the index order.
) do not write meaningless queries, such as the need to generate an empty table structure: Select Col1,col2 into #t the from T where 1=0 such code will not return any result set, but will consume system resources, Should be changed to this: CREATE TABLE #t (...)
) It is a good choice to use exists instead of in: the Select num from a where num in (select num from B) is replaced with the following statement: Select num From a Where exists (select 1 from b where num=a.num)
) Not all indexes are valid for queries, and SQL is optimized for queries based on the data in the table. When the index column has a large amount of data duplication, the SQL query may not take advantage of the index, such as the table has a field sex,male, female almost half, so even if the sex on the index is not useful for query efficiency.
() the index is not as good as the more, the index can improve the efficiency of the corresponding select, but also reduces the efficiency of insert and update, because the index may be rebuilt when insert or update, so how to build a cable Should be considered carefully, depending on the circumstances. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.

 

) should avoid updating clustered index data columns as much as possible, because the order of clustered indexed data columns is the physical storage order of the table records. Once the column value changes, the order of the entire table record will be adjusted, which will consume considerable resources. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index.
) use numeric fields as much as possible, and if fields with numeric information are not designed as character types, this can degrade query and connection performance and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.
) use Varchar/nvarchar instead of Char/nchar as much as possible, because the first variable long field storage space is small, can save storage space, second for the query, In a relatively small field, the search efficiency is obviously higher.
) do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.
) use table variables instead of temporary tables whenever possible. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index).

 

millions) avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
) temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table.
) When you create a new temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, or if the amount of data is small, in order to mitigate the resources of the system tables, You should create table first, and then insert.
) If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.
) avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.

 

) before using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is usually more efficient.
As with temporary tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data. Routines that include "totals" in the result set are typically faster than using cursors. If development time permits, a cursor-based approach and a set-based approach can all be tried to see which method works better.
set NOCOUNT on at the beginning of all stored procedures and triggers, set NOCOUNT OFF at the end. You do not need to send a DONE_IN_PROC message to the client after each statement that executes the stored procedure and trigger.
try to avoid large transaction operation and improve the system concurrency ability.
try to avoid returning large amounts of data to the client, and if the amount of data is too large, you should consider whether the appropriate requirements are reasonable.

There are questions to follow the message

MySQL performance optimization