3.MySQL Optimization---Some small summary of single-table query optimization (non-indexed design)

organized from the Internet.
Summary:

The next one is query optimization. In fact, we all know that the query part is far greater than delete and change, so query optimization will take more space to explain. This article will first talk about single-table query optimization (non-indexed design). Then we talk about multi-table query optimization. Index optimization design and library table structure optimization and so on later.

I. Single-Table query optimization: (about the index, after the opening of a single chapter explained)

(1) You can use the EXPLAIN keyword first to let you know how MySQL handles your SQL statements. This can help us to analyze the performance bottleneck of the query statement or the table structure.

(2) Write SQL to specify the required fields, how many fields to write, rather than misuse select *

(3) You can use join to replace subqueries

(4) Using a paging statement: Limit start, count, or conditional WHERE clause, what can be restricted conditions as far as possible to add, check a limit of a bar. Do not abuse. For example, I have done before the peer-to project, just need to know if there is a full-scale borrowing, so you can use limit 1, so that MySQL after finding a data to stop the search, rather than full-text search and then stop.

(5) Open the query cache:

Most MySQL servers have query caching turned on. This is one of the ways to improve query effectiveness. When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to manipulate the table directly to access the cached results.

Query Caching Workflow:

A): The server receives SQL, with Sql+db+query_cache_query_flags as the hash lookup key;

B): The relevant result set is found and returned to the client;

C): Perform permissions validation, SQL parsing, SQL optimization and other columns if no cache is found;

D): After executing SQL, save the result set to the cache

Of course, not every scenario is appropriate for caching, and it is a holistic concept to measure whether open caching has a performance boost to the system. How do you know if you want to open the cache, as follows:

1) by cache hit ratio, cache hit rate = Cache Hit count (qcache_hits)/Query count (com_select),

2) Cache Write rate, write rate = number of cache writes (Qcache_inserts)/number of queries (Qcache_inserts)

3) by the hit-write rate judgment, ratio = hit count (qcache_hits)/write times (Qcache_inserts), high-performance MySQL is called the comparison can reflect the performance improvement of the index, generally up to 3:1 is the query cache valid, and preferably up to 10:1

Related parameters and commands:

The main parameters associated with the cache are shown in the following table. You can use the command show VARIABLES like '%query_cache% ' to view

Cache Data Failure Time

When the structure or data of a table changes, the data in the query cache is no longer valid. Having these insert, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop database causes the cache data to be invalidated. So the query cache is suitable for applications with a large number of identical queries and is not suitable for applications with large amounts of data updates .

You can use the following three SQL to clean up the query cache:

1, FLUSH QUERY CACHE; Cleans up query cache memory fragmentation.

2, RESET QUERY CACHE; Removes all queries from the query cache.

3, FLUSH TABLES; Closes all open tables, and the operation empties the contents of the query cache.

InnoDB and Query caching:

InnoDB sets a transaction counter for each table, which stores the current maximum transaction ID. When a transaction commits, InnoDB uses the transaction ID of the system transaction ID in MVCC with the largest counter of the new current table.

Only transactions that are larger than this maximum ID can use the query cache, and other transactions that are smaller than this ID cannot use the query cache.

Also, in InnoDB, all transactions that have lock operations do not use any query cache

3.MySQL Optimization---Some small summary of single-table query optimization (non-indexed design)