MySQL Query optimization

The reason for poor query performance is that too much data has been accessed

• All columns are returned when a multi-table connection is made

select * from sakila.actor inner join sakila.file_actor using(actior_id)inner join sakila.film using(film_id)where sakila.film.title = ‘AronMan‘

It's the right thing to do.

select sakila.actor.* from sakila.actor inner join sakila.file_actor using(actior_id)inner join sakila.film using(film_id)where sakila.film.title = ‘AronMan‘

• Decomposition Connection Technology

select*tagjoinon tag_post.tag_id=tag.idjoinon tag_post.post_id=post.idwheretag.tag=‘mysql‘

After the connection is exploded

selectfromwhere tag=‘mysql‘selectfromwhere tag_id=1234selectfromwherein(123,456,789)

Broken connections look wasteful, but they have a huge advantage.

1. High cache efficiency
2. Short time to lock the watch under the MyISAM engine
3. The application-side connection makes it easier to extend the database and place the table on a different database server
4. The query itself is more efficient
5. Reduce access to extra rows

When do I use exploded connections?

1. Large number of queries can be cached
2. Multiple MyISAM tables are used
3. Data is distributed across different servers
4. Use in to replace connections for large tables
5. A connection references the same table multiple times

Optimizing Connections

1. Make sure that the on or using column has an index
2. Make sure that group by or order by refers to only one column, so that you can use the index

Pessimistic lock

select chairid from seat where booked is null for updateupdate seat set booked=‘x‘ where chairid=1commit

Indexing and query optimization

Extract partial self-mysql performance optimizations-slow query analysis, optimized indexing and configuration

Type of index

? Normal index: This is the most basic index type, no uniqueness, and so on.

? Uniqueness Index: Basically the same as a normal index, but all index column values remain unique.

? Primary key: The primary key is a unique index, but must be specified as "PRIMARY key".

? Full-Text indexing: MySQL supports full-text indexing and full-text retrieval starting from 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column.

Use multi-column indexes to pay attention to the leftmost prefix problem

Sometimes MySQL does not use indexes, even if there is an index available. One scenario is when the optimizer estimates that using an index will require MySQL to access most of the rows in the table. (In this case, the table scan might be faster). However, if such a query uses limit to search only a subset of rows, MySQL uses the index because it can find several rows more quickly and return them in the results.

Recommendations for proper index establishment:

(1) Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caches, and are processed faster.

(2) Simple data types are better: integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.

(3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

This section is a few trivial suggestions and points for attention when it comes to indexing and writing SQL statements.

1. Limit 1 is used when the result set has only one row of data

2. Avoid select *, always specify the columns you need

The more data is read from the table, the more slowly the query becomes. He increases the time it takes for the disk to operate, or if the database server is separate from the Web server. You will experience a very long network delay, simply because the data is not required to be transferred between servers.

1. Use a connection (join) instead of a subquery (sub-queries). Connection (join) is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query effort.

2. Using enum, CHAR instead of varchar, use reasonable field property length

3. Use not NULL where possible

4. Fixed-length tables are faster

5. Splitting a large delete or INSERT statement

6. The smaller the query, the faster it gets.

Where condition

In the query, the Where condition is also a relatively important factor, as little as possible and is reasonable where condition is very important, as far as possible in multiple conditions, will extract as little data as possible in front of the conditions, reduce the next where the query time of the condition.

Some where conditions cause the index to be invalid:

? There are in the query condition of the WHERE clause! =,mysql will not be able to use the index.

? When the WHERE clause uses the MySQL function, the index will be invalid, for example: SELECT * from TB where left (name, 4) = ' xxx '

? When searching for matches using like, the index is valid: the SELECT * from TBL1 where name is ' xxx% ', and the index is invalid if the '%xxx% '

Skill Finishing

1. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.

2, to optimize the query, should try to avoid full table scan, first of all should consider the where and order by the columns involved in the index.

3, should try to avoid the null value of the field in the Where clause to judge, otherwise it will cause the engine to abandon the use of the index for a full table scan, such as:

selectfromwhereisnull

You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:

selectfromwhere num=0

4, try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon the use of the index for a full table scan, such as:

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

You can query this:

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

5, the following query will also result in a full table scan: (Can not be placed before the percent sign)

select id from t where name like ‘%abc‘

To be more efficient, consider full-text indexing.

6, in and not in also to use caution, otherwise it will cause a full table scan, such as:

selectfromwherein(1,2,3)

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

idfromwherebetween1and3

7, if the use of parameters in the WHERE clause, 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:

selectfromwhere [email protected]

You can force the query to use the index instead:

selectfromwith(indexwhere [email protected]

8. You should try to avoid expression operations on the field in the Where clause, which causes the engine to discard full table scans using the index. Such as:

selectfromwhere num/2=100

should read:

selectfromwhere num=100*2

9, should try to avoid in the WHERE clause function operations on the field, which will cause the engine to abandon the use of the index for a full table scan. Such as:

idfromwhere substring(name,1,3)=’abc’

should read:

select id from t where name like ‘abc%’select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10. 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.

11. 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 the field order should be consistent with the index order as much as possible.

12, do not write some meaningless queries, such as the need to generate an empty table structure:

select col1,col2 into #t from t where 1=0

This type of code does not return any result sets, but consumes system resources and should be changed to this:

create table #t(…)

13, a lot of times with exists instead of in is a good choice:

numfromanuminnumfrom b)

Replace with the following statement:

select num from a where exists(select 1 from b where num=a.num)

14, not all indexes are valid for the query, SQL is based on the data in the table to query optimization, when the index column has a large number of data duplication, SQL query may not use the index, such as the table has a field sex,male, female almost half, So even if you build an index on sex, it doesn't work for query efficiency.

15, the index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. 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.

16. You should avoid updating clustered index data columns as much as possible, because the order of the clustered index data columns is the physical storage order of the table records, which can consume considerable resources once the column values change to the order in which the entire table is recorded. 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.

17, try to use numeric fields, if only the value of the field is not designed as a character type, which will reduce the performance of query and connection, 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.

18, as far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, and secondly for the query, in a relatively small field search efficiency is obviously higher.

19. Do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.

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

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

22. 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.

23. When creating a 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 lot of log to improve the speed, if the amount of data is small, in order to mitigate the resources of the system table, create table First, Then insert.

24. 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.

25. 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.

26. 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.

27. 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.

28. 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.

29, try to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.

30, try to avoid large transaction operation, improve the system concurrency ability.

MySQL Query optimization