Shulou(Shulou.com)05/31 Report--

This article shows you how to optimize high concurrency in MySQL, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

1. Design of database structure

Specific considerations for table design:

1, the length of the data line should not exceed 8020 bytes, if more than this length, this data will occupy two lines in the physical page, resulting in storage fragmentation, reducing query efficiency.

2, can use numeric type field as far as possible to choose numeric type instead of string type (phone number), which will reduce the performance of queries and connections, and will increase storage overhead. This is because the engine compares each character in the string individually while processing queries and concatenations, whereas only one comparison is required for numeric types.

For immutable character types char and variable character types varchar are 8000 bytes, char queries are fast, but consume storage space, varchar queries are relatively slow but save storage space. When designing fields, you can choose flexibly. For example, CHAR can be selected for fields with little change in length such as user name and password, and VARCHAR can be selected for fields with large change in length such as comments.

4, the length of the field in the *** limit to meet the possible needs of the premise, should be set as short as possible, so as to improve the efficiency of the query, but also in the establishment of the index can also reduce resource consumption.

Second, query optimization

Ensure that the number of visits to the database is reduced as much as possible on the basis of realizing functions (You can use cache to save query results and reduce the number of queries); by searching parameters, minimize the number of rows accessed to the table and minimize the result set, thus reducing the burden on the network; separate operations can be handled separately as much as possible to improve the response speed of each time; when using SQL in the data window, try to put the index used in the first column of the selection; the structure of the algorithm is as simple as possible; When querying, do not use too many wildcards such as SELECT * FROM T1 statements, select a few columns to use, such as SELECTCOL1,COL2 FROM T1; limit the number of rows in the result set as much as possible, such as SELECT TOP 300 COL1,COL2,COL3 FROM T1, because in some cases users do not need so much data.

1. Try to avoid making null value judgments on fields in where clauses, otherwise the engine will abandon using indexes and perform full table scans, such as:

select id from t where num is null

You can set the default value 0 on num, make sure there are no null values in num column in the table, and then query like this:

select id from t where num=0

2. Avoid using!= in where clauses Or operator, otherwise the engine forgoes index use and performs a full table scan. The optimizer will not be able to determine the number of rows to *** by index, so it will need to search all rows of the table.

3. Avoid using or in the where clause to join conditions, otherwise it will cause the engine to abandon the use of indexes and perform a full table scan, such as:

select id from t where num=10 or num=20

You can query:

select id from t where num=10 union all select id from t where num=20

4.in and not in should also be used with caution, because IN will make the system unable to use the index, and can only directly search the data in the table. For example:

select id from t where num in(1,2,3)

For continuous values, you can use between instead of in:

select id from t where num between 1 and 3

5. Try to avoid non-initial searches in indexed character data. This also prevents the engine from utilizing the index.

See the following examples:

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’ SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’ SELECT * FROM T1 WHERE NAME LIKE ‘L%’

Even if the NAME field has an index, the first two queries still cannot use the index to complete the accelerated operation, and the engine has to operate on all the data in the whole table one by one to complete the task. A third query can use indexes to speed things up.

6. Forcing the query optimizer to use an index when necessary, such as using parameters in a where clause, also results in a full table scan. Because SQL resolves local variables only at runtime, the optimizer cannot defer access plan selection to runtime; it must make the selection at compile time. However, if the access plan is established at compile time, the value of the variable is unknown and therefore cannot be selected as an input for the index. For example, the following statement will perform a full table scan:

select id from t where num=@num

You can force queries to use indexes instead:

select id from t with(index) where num=@num

7. Expression operations on fields in where clauses should be avoided as much as possible, as this will cause the engine to forgo indexes in favor of a full table scan. For example:

SELECT * FROM T1 WHERE F1/2=100

should read

SELECT * FROM T1 WHERE F1=100*2 SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’

should read

SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’ SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

should read

SELECT member_number, first_name, last_name FROM members WHERE dateofbirth ='2005-11-30' and createdate0) SELECT SUM(T1.C1) FROM T1WHERE EXISTS( SELECT * FROM T2 WHERE T2.C2=T1.C2)

Both produce the same result, but the latter is obviously more efficient than the former. Because the latter does not generate a large number of locked table scans or index scans.

If you want to verify whether a record exists in the table, don't use count(*), which is inefficient and wastes server resources. It can be replaced by EXISTS. For example:

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

It can be written as:

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

It is often necessary to write a T_SQL statement to compare a parent result set with a child result set to find whether there are records in the parent result set that are not in the child result set, such as:

SELECT a.hdr_key FROM hdr_tbl a--- tbl a indicates that tbl is replaced by alias a WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key) SELECT a.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)

12. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index).

13. Avoid frequent creation and deletion of temporary tables to reduce consumption of system table resources.

14. Temporary tables are not unusable, and appropriate use of them can make certain routines more efficient, for example, when you need to reference a dataset repeatedly from a large or frequently used table. However, for one-time events, *** export tables are used.

15. When creating a temporary table, if the amount of data inserted at one time is large, select into can be used instead of create table to avoid creating a large number of logs to improve speed; if the amount of data is small, in order to ease the resources of the system table, create table first, and then insert.

16. If temporary tables are used, be sure to explicitly delete all temporary tables in the *** of the storage procedure, first truncate the table, and then drop the table, so as to avoid long-term locking of the system table.

17. Set SET NOCOUNT ON at the beginning and SET NOCOUNT OFF at the end of all stored procedures and triggers. There is no need to send a DONE_IN_PROC message to clients after each statement of stored procedures and triggers is executed.

18. Try to avoid large transaction operations and improve system concurrency.

19. Try to avoid returning large amounts of data to the client. If the amount of data is too large, consider whether the corresponding requirements are reasonable.

20. Avoid using incompatible data types. For example, float and int, char and varchar, binary and varbinary are incompatible (conditional). Incompatibility of data types may prevent the optimizer from performing optimizations that could otherwise be performed. For example:

SELECT name FROM employee WHERE salary > 60000

In this statement, if the salary field is of type money, it is difficult for the optimizer to optimize it because 60000 is an integer. We should convert integers to coins at programming time, not at runtime.

21. Make full use of join conditions (the more conditions the faster), in some cases, there may be more than one join condition between two tables, then write the join condition completely in the WHERE clause, it is possible to greatly improve the query speed.

Example:

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

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.