SQL Analysis and Optimization – Web Design Vancouver

Slow SQL?

From the perspective of the database: each SQL execution needs to consume a certain amount of I/O resources, and the speed of SQL execution determines the length of time the resources are occupied. Assuming that the total resources are 100, a piece of slow SQL occupies 30 resources for a total of 1 minute. Then in this one minute, the total amount of resources that can be allocated by other SQL is 70, and the cycle is like this. When the resources are allocated, all new SQL executions will be queued up. From an application point of view: a Long SQL execution time means waiting. In OLTP applications, the user experience is poor

Priority of governance

1. master database -> slave database

• The current database is basically a read-write separation architecture. Reading is executed on the slave and writing is executed on the master.
• Since the data of the slave library is copied from the master library, the master library waits more, which will increase the replication delay with the slave library.

1. SQL priority governance with many executions
2. If there is a type of SQL with high concurrency and centralized access to a certain table, it should be managed first.

Mysql execution principle

The green part is the actual execution part of SQL. You can find two major steps of SQL execution: analysis and execution.

Take com_query as an example, dispatch_command will first call alloc_query to allocate memory for the query buffer, and then call parsing

Analysis: Lexical analysis->Syntax analysis->Logical plan->Query optimization->Physical execution plan

Check whether there is a query cache result available, if not or if the cache is invalid, call mysql_execute_command to execute execution: check user and table permissions -> add shared read lock on the table -> fetch data to query cache -> cancel shared read lock

Influencing factors – Pay Per Click Service Vancouver

If the parameters of the MySQL database and the impact of hardware I/O are not considered, the main factors affecting the efficiency of SQL execution are the summary of I/O and CPU consumption:

1. Data volume: The larger the data volume, the more I/O times are required
2. How to get data

• Is the data in the cache or on disk
• Can it be quickly addressed by index

1. Data processing method

• For sorting, subquery, etc., you need to fetch the data into a temporary table first, and then process the data
• Increased I/O and consumes a lot of CPU resources

Solutions – Chinese Search Engine Optimization

1. Store data in a faster place.

• If the amount of data is not large and the frequency of change is not high, but the frequency of access is high, you should consider placing the data in the application-side cache or a cache such as Redis to improve the access speed. If the data is not filtered, correlated, sorted, etc., and only accessed according to the key, and does not consider the need for strong consistency, you can also consider choosing a NoSQL database.

1. Consolidate I/O appropriately

• Execute select c1 from t1 and select c2 from t1 respectively. Compared with executing select c1, c2 from t1, the latter has a lower overhead.
• The increase in execution time also needs to be considered when merging.

1. Leverage distributed architecture

• When faced with massive amounts of data, the usual approach is to distribute the data and I/O to multiple hosts for execution.

Optimization of your SQL Suggestions:

Database considerations

1. Important SQL must be indexed, for example: Where condition column of select, update, delete statement; order by、group by、distinc by

2. Limitations of mysql index: MySQL does not currently support functional indexes; When using unequal (!= or <>), mysql cannot use the index. Separate >,<,in may or may not go. It is related to the result set. Try to add limiter or in combination with the business and try to change it. Into union

3) After the filter field uses a single-row function (such as abs (column)), MYSQL cannot use the index.

4) MYSQL cannot use the index when the join condition field type in the join statement is inconsistent

5) When using the LIKE operation, if the condition starts with a wildcard (such as’%abc…’), MYSQL cannot use the index.

6) When using a non-equivalent query, MYSQL cannot use Hash index.

7) BLOB and TEXT type columns can only create prefix indexes

3. Common sql specifications for mysql:

• The SQL statement is as simple as possible. Large SQL statements are divided into small SQL statements as much as possible. MySQL does not support complex SQL.
• The transaction should be simple, the duration of the entire transaction should not be too long, and the SQL should be submitted in time after the end.
• Limit the size of the data set operated by a single firm, not exceeding 10,000 records
• It is forbidden to use triggers, functions, and stored procedures.
• Reduce business coupling, leaving room for scale-out and sharding
• Avoid performing mathematical operations in the database (databases are not good at mathematical operations and logical judgments)
• Avoid using select *, select which fields need to be queried, to avoid the buffer pool being filled with useless data.
• The SQL statement that uses OR in the condition must be rewritten to use IN() (OR is much less efficient than IN)
• The number of data in IN() is recommended to be controlled within 500. Exist can be used instead of in. Exist is more efficient than in some scenarios, so try not to use it
• Pay attention to the efficiency of limit paging. The larger the limit, the lower the efficiency. You can rewrite the limit, for example select id from test limit 10000,10 can be rewritten as select id from test where id> 10000 limit 10
• Use LIMIT 1 when only one row of data is required.
• When acquiring a large amount of data, it is recommended to acquire data in batches, each acquisition of fewer than 10,000 data, the result set should be less than 1M
• Avoid using large tables for JOIN, use group by grouping, automatic sorting
• The SQL statement prohibits implicit conversion, for example, select id from test where id=’1′, where an id column is a number such as an int Types of.
• In SQL, try not to use like, and it is forbidden to use like matching with a prefix of %.
• Reasonably choose union all and union
• It is forbidden to use queries without where conditions in OLTP-type systems.
• Using prepared statements, only passing parameters, is more efficient than passing SQL statements; parsing once, using multiple times; reducing SQL Injection probability.
• The use of order by rand() is prohibited.
• It is forbidden for a single SQL statement to update multiple tables at the same time.
• Do not update or query the database in batches during peak business periods, and avoid altering tables during peak business periods.
• It is forbidden to execute complex statistical analysis statements such as sum and count on the main library, and the slave library can be used for execution.