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

MySQL in big data, high concurrency scenarios of SQL sentence optimization and practice is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

SQL sentence Optimization and "Best practices" of MySQL in big data and High concurrency scenarios

Mainly for small and medium-sized applications or websites, this paper focuses on the optimization of SQL sentences in daily program development. The so-called "big data" and "high concurrency" are only for small and medium-sized applications. Please ignore the professional database operation and maintenance god. The following practice is for individuals in the actual development work, for the relative "big data" and relatively "high concurrency" scenarios some coping strategies, some measures have not gone through strict comparative testing and principle analysis.

Reduce the impact of queries on the result set and avoid full table scans.

Influencing the result set is the core of SQL optimization. The impact result set is not the number of records returned by the query, but the number of results scanned by the query. Analyzing the value of the SQL,rows column through Explain or Desc is the impact result set (it can also be obtained by the number after the Rows_examined of the slow query log).

Here are some of my common SQL optimization strategies:

Get rid of unnecessary queries and searches. In fact, in the practical application of the project, many query conditions are optional, and the redundant functions that can be avoided from the source can be cut off as far as possible, which is the simplest and roughest solution.

Rational use of indexes and composite indexes. Indexing is the most effective method in SQL optimization. Fields commonly used in finding, deleting, updating, and sorting can be indexed appropriately. Note, however, that a single query cannot use multiple indexes at the same time, but only one index. When there are many query conditions, you can use a composite index in which multiple fields are merged. Keep in mind that when using a composite index, the field order of the query criteria needs to be consistent with the field order of the composite index.

Be careful with conditions such as notin that may not be able to use an index. The index can not work all the time, when there are "notin", "! =", "like'%xx%'", "isnull" and other conditions, the index is invalid. When using these conditions, place them to the right of the conditions that can effectively use the index. When designing the table structure, it is suggested that int type should be used instead of varchar type as much as possible, and int type can be replaced by "! =" in part. At the same time, it is convenient to meet some requirements that need to be sorted by type. As for readability, it is a wise choice to improve the database design documents. At the same time, it is recommended to set all possible fields to "notnull" and set the default value to avoid the judgment of "isnull" in the where sentence.

Do not perform functions, arithmetic operations, or other expression operations to the left of the "=" in the where clause, or the system will not be able to use the index correctly. Use as few MySQL functions as possible, like Now () can be implemented and assigned by the program, and some functions can be replaced indirectly by establishing redundant fields appropriately.

Using or in a where condition may cause the index to be invalid. It can be replaced by "unionall" or "union" (which filters duplicate data and is less efficient than the former), or programmatically directly obtains the data twice and then merges it to ensure the effective use of the index.

Not using select*, can not improve the query efficiency, but mainly reduce the amount of output data and improve the transmission speed.

Avoid type conversions. "Type conversions" here refer to the type conversions that occur when the type of the field is inconsistent with the type of the parameter passed in the where clause.

Optimization of paging query. In the case of a large number of pages, for example, the result set affected by limit10000,10 is 10010 rows, the query speed will be slow. The recommended solution is to first query only the primary key selectidfromtablewhere..orderby..limit10000,10 (please index the search criteria and sort), and then use the primary key to get the data.

Statistics related queries. The impact result set is often huge, and some of the SQL statements themselves have been difficult to optimize. Therefore, you should avoid executing statistics-related queries during peak business hours, or only from the slave library. Part of the statistical data can be saved through redundant data structures, and it is recommended to save the data in memory and cache (such as redis), and then write to the database according to a certain strategy.

Do not use any connected table query, through sub-database and sub-table to achieve load balancing.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.