MySQL Query optimization

Poor query performance is due to access to too much data

• 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 tables on different database servers
4. The query itself is more efficient
5. Reduce access to extra rows

When do I use exploded connections?

1. Ability to cache large numbers of queries
2. Multiple MyISAM tables are used
3. Data is distributed across different server
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 simply refers to a single column. This enables you to 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 primary index type. There are no restrictions such as uniqueness.

? Uniqueness Index: Basically the same as a normal index. However, 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 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 expects that the index will require most of the rows in the MySQL Access table. (In this case, the table scan might be faster).

However, suppose such a query uses limit to search only a subset of the rows. MySQL uses the index because it can find several rows faster and return in the results.

Recommendations for proper index establishment:

(1) Smaller data types are usually better: smaller data types typically require less space in disk, memory, and CPU caches. Faster processing.

(2) Simple data types are better: integer data is less expensive to handle than characters, because of the complexity of strings.

In MySQL, you should use the built-in date and time data types. Instead of storing the time with a string, and storing the IP address with an integer data type.

(3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL. Columns that contain null values are very difficult to query optimization. Because they make indexes, index statistics, and comparison operations more complex. You should replace the null value with 0, a special value, or an empty string

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. Queries can become slower. He adds the time that the disk needs to be manipulated, or if the database server is separate from the webserver. You will experience a very long network delay, simply because the data is not necessary to transfer between servers.

1. Use a connection (join) to replace the subquery (sub-queries).

   Connection (join) is more efficient. It is because MySQL does not need to create a temporary table in memory to complete this logic requires a two-step query work.

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, and it is very important to have as few and reasonable a where condition as possible. Try to be in more than one condition. Put in front the conditions that will extract as little data as possible. Reduces the query time for the latter where 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 '

? Use like to search for matches. The index is valid: the SELECT * from TBL1 where name is like ' xxx% ' and the index is invalid when like '%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. The full table scan should be avoided as much as possible, and first consider establishing an index on the columns involved in the Where and order by.

3, you should try to avoid null value inference in the WHERE clause, otherwise it will cause the engine to abandon the use of indexes for a full table scan, such as:

selectfromwhereisnull

To 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

Able to query like this:

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

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

select id from t where name like ‘%abc‘

To be more efficient, you can consider full-text indexing.

6, in and not should be used with caution. Doing so will result in a full table scan, such as:

selectfromwherein(1,2,3)

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

idfromwherebetween1and3

7. Assuming that the use of a parameter in the WHERE clause also results in a full table scan. Because SQL only parses local variables at execution time, the optimizer cannot defer the selection of the access plan to execution. It must be selected at compile time.

However, suppose you set up an access plan 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]

To 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 in the function operation of the field. This causes the engine to discard the full table scan using the index. 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-hand side of the "=" in the WHERE clause. Otherwise the system may not be able to use the index correctly.

11. When using an indexed field as a condition, assume that the index is a composite index. Then you must use the first field in the index as a condition to ensure that the system uses that index. Otherwise, the index will not be used, and the order of the fields should match the order of the indexes as much as possible.

12, do not write some meaningless query. If you 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 set, but consumes system resources. Should be changed to this:

create table #t(…)

13. It is a good choice to replace in with exists in a very long time:

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 for query optimization. When the index column has a large amount of data, the SQL query may not take advantage of the index, as there are fields in the table Sex,male, female almost half, so even if you build an index on sex, query efficiency does not work.

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 may be rebuilt index, so how to build indexes need careful consideration, depending on the details of 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. Whenever possible, avoid updating clustered index data columns, because the order of the clustered index data columns is the physical storage order of the table records, and once the column values are changed, the order of the entire table records will be adjusted, which can consume considerable resources. If the application system needs to update clustered index data columns frequently. Then you need to consider whether the index should be built as a clustered index.

17, try to use a numeric field, if only the field containing numeric information should not be designed as a character type, which will reduce the performance of queries and connections. Storage overhead is added. This is due to the fact that the engine handles queries and connections one at a time in each character in the string, whereas for a digital type it only needs to be done more than once.

18, as far as possible to replace Char/nchar with Varchar/nvarchar, because the first variable long field storage space is small, can save storage space, second for the query, in a relatively small field search efficiency is obviously higher.

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

20, try to use table variables to replace the temporary table. Assuming that the table variable includes 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, such as when you need to refer to large tables repeatedly or when you frequently use a dataset in a table. However, for a one-time event, it is best to use an export table.

23. When creating a temporary table, it is assumed that the amount of data inserted at once is very large. You can use SELECT INTO instead of CREATE table. Avoid causing a lot of log. To lift the speed, assuming that the amount of data is not large, in order to mitigate the resources of the system table, create table first, then insert.

24, assume that the use of the temporary table. Be sure to explicitly delete all temporary tables at the end of the stored procedure. TRUNCATE TABLE first, then drop table, which avoids longer locking of system tables.

25, to avoid the use of cursors, because the efficiency of the cursor is poor. Assuming that the cursor operation has more than 10,000 rows of data, you should consider rewriting.

26. Before using a cursor-based method or a temporary table method. The set-based approach is often more effective when you should look for a set-based solution to solve this problem.

27, the same as the temporary table. Cursors are not not unusable. Using Fast_forward cursors for small datasets is often preferable to other progressive processing methods, especially if you have to reference several table competencies to obtain the required data. Routines that include "totals" in the result set are typically faster than using cursors.

If development time agrees, both the cursor-based approach and the set-based approach can be tried. 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 operations. Improve the system concurrency capability.

MySQL Query optimization