Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Sharing skills for optimizing SQL statements

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article introduces you to optimize SQL statement skills sharing, the content is very detailed, interested friends can refer to reference, I hope to help you.

The index is not built as much as possible, the principle is:

First: the index of a table is not the more the better, there is no specific number, according to past experience, a table index can not exceed 6 at most, because the more indexes, update and insert operations will also have a performance impact, involving the index of the new and rebuild operations.

Second: The methodology for indexing is:

columns frequently used by most queries;

Columns that are rarely modified;

Indexes need to be built on columns with large data differentials

Let's discuss how to optimize sql.

SQL Statement Optimization Guide

1. SQL statement model structure optimization guidance

a. Index optimization for ORDER BY + LIMIT combination

If a SQL statement is of the form SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

This SQL statement optimization is relatively simple, in the [sort] field to create an index.

b. Index optimization for WHERE + ORDER BY + LIMIT combination

If a SQL statement is of the form SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

This statement, if you still use the index method in the first example, although you can use the index, but it is not efficient. A more efficient approach is to create a union index (columnX,sort)

c. WHERE+ORDER BY Multiple fields +LIMIT

If a SQL statement is of the form SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

For this statement, you might add an index like this:(x,y,uid). But actually the better effect is (uid,x,y). This is caused by MySQL's sorting mechanism.

2. Composite index (index of type (x,y,uid))

Select* from users where area ='beijing' and age=22;

If we create indexes on area and age separately, since mysql queries can only use one index at a time, although this has improved a lot of efficiency compared to full table scanning without indexing, it will bring higher efficiency if we create composite indexes on area and age columns.

When index fields are used as a condition, if the index is a composite index, the index must be used until the first field in the index is used as a condition, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.

For example, if we create such an index (area, age,salary), then it is equivalent to creating three indexes (area,age,salary),(area,age),(area), which is called the best left prefix property.

3, like sentence optimization

SELECT id FROM A WHERE name like '%abc%'

Because "%" is used before abc, the query must go through the whole table query, unless necessary, do not add % before the keyword, optimized as follows

SELECT id FROM A WHERE name like 'abc%'

4, where clause use!= OR operator optimization

Use!= in the where clause Or operator, the index will be discarded and a full table query will be performed.

SQL:SELECT id FROM A WHERE ID != 5 is optimized as: SELECT id FROM A WHERE ID>5 OR ID='2016-11-30' and create "2016-07-23" , can be optimized as utime>"2016-07-23 00:00:00"

18. Use smaller fields as possible

MySQL reads data from disk and stores it in memory, and then reads it using cpu cycles and disk I/O, which means that the smaller the data type takes up the smaller the space, the better the efficiency of reading or packing from disk to memory, but don't be too persistent in reducing the data type.

Changing the table would require refactoring, which indirectly could cause code changes, which is a headache, so a balance needs to be found.

Inner join and left join, right join, subquery

First: inner join is also called equivalent join, left/rightjoin is outer join.

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

After many aspects of confirmation, inner join performance is faster, because inner join is an equivalent join, and perhaps the number of rows returned is relatively small. However, we should remember that some sentences implicitly use equivalent connections, such as:

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

Recommendation: Use inner join connection as much as possible

Second: the performance of subqueries is slower than that of outer joins, so try to replace subqueries with outer joins.

Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

Table A data for a hundred thousand tables, table B for millions of tables, in the local execution takes about 2 seconds, we can see through the explanation that the subquery is a related subquery (DEPENDENCE SUBQUERY);Mysql is to perform A full table query on the appearance A first, and then execute subqueries one by one according to uuid. If the outer table is a large table, we can imagine that the query performance will be worse than this.

A simple optimization is to replace the subquery with an innerjoin method, and the query statement is changed to:

Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000; This statement executes the test in less than a second;

Third: When using JOIN, you should use small results to drive the results (left join left table results as small as possible, if there are conditions should be placed on the left first processing, right join the same reverse), and try to involve multi-table union queries split into multiple queries (multiple table queries are inefficient, easy to lock tables and block). For example:

Select * from A left join B A.id =B.ref_id where A.id>10; can be optimized as: select * from (select * from A wehre id >10) T1 left join B on T1.id =B.ref_id;

20, exist instead of in

SELECT * from A WHERE idin (SELECT id from B)SELECT * from A WHERE id EXISTS(SELECT 1 from A.id= B.id)

in is to traverse comparison in memory

exist needs to query the database, so when B has a large amount of data, exists is more efficient than in.

In() is executed only once, caching all id fields in table B, and then checking whether the id of table A is equal to the id in table B. If the id is equal, the records in table A are added to the result set until all records in table A are traversed.

In the process of operation principle as follows code

List resultSet={}; Array A=(select * from A); Array B=(select id from B); for(int i=0;i

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report