General strategies for SQL optimization: index optimization, SQL rewriting, parameter optimization, optimizer

The index optimization

Select * from VVshop_order. Vv_order WHERE receive_phone=’151011324532′; For the case analysis

explain select * from vv_order where order_no=23;Copy the code

Results:

Analysis: you can see that this SQL scan the entire table more than 300,000 records, can be optimized by adding indexes

alter table vv_order add index orderno_idx(order_no);Copy the code

Note:

  • Indexes are invalidated when the incoming data type is inconsistent with the library table data type
  • Do not create a separate index for each query field. Instead, create a single column index or a combination index based on your actual needs
  • Use Explain + Extended to check the execution plan of the SQL, whether indexes are used, and whether implicit conversions have occurred
  • Avoid using functions in query criteria

Rewrite the SQL

Paging optimization

SQL select buyer from buyer where sellerid=100 limit M, buyer from buyer where sellerid=100 limit M, buyer from buyer where sellerid=100 limit M, buyer from buyer where sellerid=100 limit M, buyer from buyer where sellerid=100 limit M, buyer from buyer where sellerid=100 limit M, buyer from buyer where sellerid=100 limit M

Select buyer t1.* from buyer t1, (select id from buyer sellerid=100 and buyer sellerid=100) T2 where buyer t1.Copy the code
Subquery optimization

  1. If the number of queries is large, change in to exist, or optimize to the following form

    SELECT first_name
FROM employees emp,
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
WHERE emp.emp_no = sal.emp_no;Copy the code

  2. Avoid queries that return all fields and return only the required field data

Do not use select *
Or is rewritten as in

The efficiency of OR is n, and the efficiency of IN is log(n), keeping the number of in up to 200

Instead of using functions and triggers, it is implemented through an application
Use less joins to ensure that field types are always joined or compared
Use between for continuous values

Parameter optimization

The optimizer

other

Factors affecting the effectiveness of IN

  1. Eq_range_index_dive_limit parameters

    The default is 10

    Eq_range_index_dive_limit = 0 Only index dive can be used

    0 < eq_range_INDEx_dive_limit <= N Use index statistics

    Eq_range_index_dive_limit > N Only index dive can be used

field
  1. Set the field type based on actual usage
  2. Do not have too many fields in a single table. 20 or less is recommended
  3. Avoid null fields, which are difficult to optimize and take up extra index space
  4. Use an integer to store IP addresses

Tuning system parameters

Benchmarking tool
  1. Sysbench: Modular, cross-platform, and multithreaded performance testing tool
  2. Iibench -mysql: Java-based insert performance testing tool
  3. TPCC -mysql: TPC-C test tool developed by Percona
Here are some important parameters:

back_log

The backlog value indicates how many requests can be on the stack for a short time before MySQL temporarily stops answering new requests. That is, if MySql connection data reaches maxConnections, incoming requests will be stored on the backlog waiting for a connection to release resources. If the backlog is greater than that, no connection resources will be granted. It can be raised from the default 50 to 500

wait_timeout

Idle database connections occupy memory resources. It can be reduced from the default 8 hours to half an hour

maxuserconnection

Thread_concurrency: maximum number of concurrent threads. Set this parameter to twice the number of CPU cores

skipnameresolve

Disable DNS resolution for external connections, eliminating DNS resolution time. However, all remote hosts must use IP addresses to access external connections

keybuffersize

The cache size of index blocks, which increases the index processing speed, has the greatest impact on MyISAM table performance. Set keyreads/keyreadRequests below 0.1% by querying show status like’keyread%’ to 256M or 384M memory

innodbbufferpool_size

Cache data blocks and index blocks, which have the greatest impact on InnoDB table performance. Show status like ‘Innodbbufferpoolread%’ Assurance (Innodbbufferpoolreadrequests – Innodbbufferpoolreads)/Innodbbufferpoolreadrequests, the higher the better

innodbadditionalmempoolsize

InnoDB storage engines used to store data dictionary information as well as some internal data structure size of memory space, when the database object is very much, adjust the parameter to make sure that all the size of the data can be stored in the memory access efficiency, when is too small, MySQL will record the Warning information to the database in the error log, This is where you need to resize this parameter

innodblogbuffer_size

InnoDB stores transaction logs for the engine using a buffer that is generally not recommended to exceed 32MB

querycachesize

Cache a ResultSet in MySQL, which is the ResultSet executed by an SQL statement, so only for select statements. Any change in the data of a table invalidates the data cached in the Query Cache for all SELECT statements referencing that table. So, when our data changes very frequently, using Query Cache may not be worth the cost. Adjust according to hit ratio (Qcachehits/(Qcachehits+Qcache_inserts)*100)). Generally, large size is not recommended; 256MB May be sufficient, and large configuration static data can be scaled appropriately.

You can run the show status like ‘Qcache_%’ command to check the current size of the system Query catch

readbuffersize

MySql read buffer size. A sequential scan of the table will allocate a read buffer, and MySql will allocate a memory buffer for it. If sequential scan requests to the table are very frequent, you can improve performance by increasing the value of this variable as well as the memory buffer size

sortbuffersize

The buffer size used by MySql to perform sorts. If you want to increase the speed of ORDER BY, first see if you can have MySQL use indexes instead of the extra sort phase. If not, try increasing the size of the sortBufferSize variable

readrndbuffer_size

MySql random read buffer size. When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySql scans the buffer first to avoid disk search and improve the query speed. If a large amount of data needs to be sorted, you can adjust the value to a higher value. MySql allocates this buffer space for each client connection, so try to set this value appropriately to avoid excessive memory overhead.

record_buffer

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do a lot of sequential scans, you may want to increase this value

threadcachesize

Save threads that are not currently associated with a connection but are ready to serve a later connection, allowing you to quickly respond to a connected thread request without creating a new one

table_cache

Similar to ThreadCachesize, but used to cache table files, not very effective with InnoDB, mainly used for MyISAM

References

Ali cloud slow SQL optimization challenge contest analysis

SQL optimizer principles – Query optimizer overview

MYSQL query SQL statement performance optimization method

MySQL–eq_range_index_dive_limit

MySQL > select * from range where range = ‘in’;

This article is written BY misguided Lao Nong and licensed BY CC BY 4.0CN. It can be reproduced and quoted freely, but the author must be signed and indicate the source of the article.