Query optimization for MySQL optimization

Posted by Talks on 2012/02/24 |

The first chapter of the preface
Second connection optimization
Third Article index optimization
Four-piece query optimization
The fifth chapter to the actual combat

Query optimization

Query optimization involves the use of indexes, sorting, group operations, and their writing habits when users query data. (original link http://ddbiz.com/?p=990)

1. Server parameter tuning that affects queries

   In addition to the user's query habits, during the processing of the entire data query, some parameters of the MySQL server also affect the execution of these queries, such as temporary tables, temporary files and so on.

   2. Max_heap_table_size

      Command-line arguments: –max_heap_table_size=#
      INI/CNF Definition: max_heap_table_size
      MySQL variable: max_heap_table_size
      Global variables, dynamically adjustable, default of 16M, adjustable range 32bitOS 16k to 4g,64bitos 16k to the amount of available memory you can provide (max 1Zetta)

      User table for storage engine memory

   3. Tmp_table_size

      Command-line arguments: –tmp_table_size=#
      INI/CNF Definition: tmp_table_size
      MySQL variable: tmp_table_size
      Global variables, dynamically adjustable, default to System setting, maximum 4G

      Tmp_table_size is the size limit of the memory table generated by MySQL at the time of group execution, and, like Max_heap_table_size, this is a byte size value. This value in the system is computed with the President of the table to be queried to determine the number of rows of tables that MySQL can mount. If a limit in the query exceeds this value, in a later version of 3.x, MySQL automatically converts the temporary table to a disk file: the MyISAM engine. The memory tables that are actively created by the user are not included in this restriction.

      According to the status: Created_tmp_disk_tables and Created_tmp_tables to determine whether to increase the tmp_table_size.
      

      Affected by the following parameters: Max_head_table_size
      will affect the following parameters:
      Adjust trigger conditions: When Created_tmp_disk_tables exceeds created_tmp_tables, action should be taken to increase the tmp_table_size value.

      Special Note: In the following cases, the memory temp table will not work, MySQL will directly use MyISAM

      • Case with text or BLOB fields in the table
      • A case in which a field with a length greater than 512 bytes is present in a group by or distinct specified field
      • In the select of Union/union all, the field of select has a condition greater than 512 bytes

      It is possible for MySQL to convert the memory temp table to the MyISAM engine in the following cases

      • The order by in the query is different from the field in Group by
      • In a join query, order BY or group by contains fields that are not the first table
      • Using ORDER by ordering for distinct queries

      Note : The value of Max_heap_table_size and Tmp_table_size when assigned to a connection thread query is min (max_heap_table_size,tmp_table_size), If a site has a lot of concurrent connections (show status like ' thread_running '), then system memory is allocated =min (max_heap_table_size,tmp_table_size) * (Show status Like ' Thread_running '). For example, in a concurrent 1000 site, if each connection can allocate 16m of memory, then temporary memory will be allocated 16m * 1000=16g. If you have so much memory to use, then this is not a problem, otherwise, it is necessary to set the tmp_table_size smaller.
      Of course, this also prompts us, in a real-time site, because each user request can not and should not execute a relatively complex or a lot of records involved in the query, for some statistical work, the statistical database should be separated from the online trancation database.

   5. Max_tmp_tables

      According to the document (MYSQL5.5CE), this parameter has not been enabled!

   7. Query_prealloc_size

      Command-line arguments: –query_prealloc_size=#
      INI/CNF Definition: query_prealloc_size
      MySQL variable: query_prealloc_size
      Global variables, dynamically adjustable, default 8k, Maximum 4G (32bitOS) or higher (64bitOS).
      The query_prealloc_size is used for MySQL memory space settings for parsing and executing SQL statements. When executing large, complex queries, consider adding this value to the session.

   9. query_cache_* Query Result Cache

      Query caching refers to two types of queries: The SELECT statement for the query and the result of the query. Here are the system variable settings for some columns:

      Query_cache_size:select query Results cache, 0 or 40k to 4G (32bitOS) or higher (64bitOS), default is 0;
      Query_cache_type:off: does not cache query results; On: Caches all query results except the start of select Sql_no_cache; DEMAND: Caches only query results that begin with select Sql_cache.
      Query_cache_limit: The maximum number of bytes that can be cached.
      Query_cache_min_res_unit: the minimum block size for query cache allocation, default 4k.
      The default is off in query_cache_wlock_invalidate:mysql5.5, except that other customer queries are organized when write lock is present.

      Precautions:
      –> is prone to large amounts of memory fragmentation for particularly frequent small queries (such as the return result is less than or far less than query_cache_min_res_unit).
      –> for queries with large result sets, a large enough query_cache_min_res_unit can improve the performance of the system.
      Determine whether the query_cache_min_res_unit needs to be adjusted by querying the system state Qcache_free_blocks and Qcache_lowmem_prunes.

      Qcache_free_blocks free memory blocks in the query cache
      Qcache_lowmem_prunes indicates the number of query results that have been removed from the cache because of insufficient query caching.

2. Deep understanding of Query Cache

   Query_cache_size default is 0 in MySQL, which means that the query cache is turned off by default. Since query caching can improve the user's query experience Well, why is it closed? This is to be programmed from the MySQL client. The query cache in Query_cache_size, which caches results according to query statements as key values. Query statements must be identical: case, space, and even query variables. Wait, why does the query variable also affect this key value? The reason is peculiar: MySQL's client-side query statement uses direct variable substitution instead of substituting variables like those in Oracle. Take a look at this query example below:
   
public function GetUserByAccount($dbname, $host, $username, $userpwd, $charset, $account)
{
$sql = "select id from tmp_userhere";
if (CTextHelper::IsValidEmail($account))
$sql .= ‘ where email=:key‘;
else
$sql .= ‘ where username=:key‘;
$sql .= ‘ limit 3‘;
$dsn = "mysql:dbname=".$dbname.";host=".$host;
$dbh = new PDO($dsn, $username, $userpwd);
$charset = isset($charset) ? $charset :"gbk";
$this->dbh->exec("set names ".$charset);
$sth = $dbh->prepare($sql);
$sth->bindParam(‘:key‘, $account);
$r = $sth->execute();
if (!$r) return false;
return $sth->fetchColumn(0);
}

   Although a variable binding is used in the client (PHP code), the actual server trace sees:
   
   For queries of a large data table, the exact same query probability is too small. So you can save the query cache setting completely. In one of my big data queries, let him be 0.

4. User's query habits

   There are many human factors that can affect the efficiency of the query. I'm not going to list them here, and I can see a variety of tips for optimizing statements in the Top of SQL performance tips.

6. Group by

   If you use a group by in a query statement, in general, MySQL will create a temporary table and then calculate and sort the data without using the index. As in the following query:
   

   If the amount of data is large, then this query is bound to be slow. But MySQL has a way to run the group by index, provided that all the fields in group by are columns in an index.

Working On...,and KEEP EYES on IT ...

Query optimization for MySQL optimization