MySQL Foundation tuning

How to optimize MySQL Foundation

1, using the index to speed up the query Speed 2, the use of query cache or side-hanging cache, improve access speed cache: k/v key: Query statement hash value value: Query statement execution results which queries may not be cached? The query statement contains a UDF (user-defined Functions) stored function user-defined variable temp table MySQL system table or a query with column-level permissions with indeterminate result values (n ow ()); query cache-Related server variables: query_cache_limit: Maximum query results that can be cached, (maximum single-statement result set size) statements with large result sets, explicit use of sql_no_cache to avoid first caching and then moving out , that is, when querying with the SELECT * from students Sql_no_cache Query_cache_min_res_unit: the smallest allocation unit of memory block, cache too small query result assembly wasted memory space small value Will reduce space waste, but will lead to more frequent memory allocation and recycling operations, larger values will lead to wasted space query_cache_size: The total available size of the query cache space, in bytes, must be an integer multiple of 1024 query_cache_strip_co Mments query_cache_type: Cache function enabled or not; on: enabled; off: Disabled; DEMAND: Cache On Demand, cache only queries with Sql_cache in SELECT statements  Query_cache_wlock_invalidate: If a table is locked by another connection, the query results can still be returned from the query cache, the default is off, and on indicates no; state variable: mysql> SHOW                    GLOBAL STATUS like ' qcache% '; +-------------------------+----------+                    | variable_name |         Value |           +-------------------------+----------+                    | Qcache_free_blocks |                    1 | | Qcache_free_memory |                    16759688 | | Qcache_hits |   0 | # #查询缓存的命中率 | Qcache_inserts |                    0 | | Qcache_lowmem_prunes |                    0 | | qcache_not_cached |                    0 | | Qcache_queries_in_cache |                    0 | | Qcache_total_blocks |                    1 |        +-------------------------+----------+ mysql> show global status like '%com_select% '; +---------------+-------+        | variable_name |        Value | +---------------+-------+        | Com_select |    12 | # #表示查询的次数 +---------------+-------+ hit rate: Qcache_hits/com_select 3, InnoDB storage Engine related parameters: Innodb_buffer_pool_ Size: index, data, buffer when data is inserted, data is first saved in memory buffer, saved to disk from memory buffer, 70-80% as a dedicated server, and if the dataset itself is small, rootAccording to the data change amplitude and the plan time length to set the reasonable value, is slightly larger than the estimated target value; The number of segments (instances) of the Innodb_buffer_pool_instances:buffer_pool, the memory buffer is divided into a plurality of zone ends, so that the lock does not lock the entire table, may only is part of the lock table. 4, transaction log: Innodb_log_files_in_group: The number of things in a set of log files, at least 2; innodb_log_file_size: Log file size, default is 5 m, it is recommended to increase this value; Innodb_flus H_logs_at_trx_commit:0:log buffer (memory) is synchronized to log file one time per second, and the log file to data file is synchronized, which is the data in the memory buffer is synchronized to the thing log once every second, and the same        Step into the data file. 1: Log buffer is synchronized to log file at each commit, while the log file is synchronized to the data file, and 2: Log buffer is synchronized to log file each time it is committed, but the log file to the data file is not synchronous operation; Recommendation: Turn off autocommit, and then set this value to 1 or 2; Many advanced features of INNODB_FILE_PER_TABLE:INNODB depend on this parameter; Innodb_read_io_threads:inn Odb_write_io_threads the number of IO threads written and read by the file, which can be adjusted appropriately based on concurrency and CPU cores, innodb_open_files:innodb the maximum number of files that can be opened; Innodb_thread_concurre ncy= the number of threads that can be used at the kernel level, typically twice times the CPU skip_name_resolve:max_connections:5, table partitioning: Table partitioning can improve the efficiency of query and write operations, partition the table after the table structure file or one, but the tablespace file becomes multiple  , you can query or change the data only in the partition of the table, without having to query the whole table, greatly improve efficiency, so that each partition separately managed, separate use according to the scope: MariaDB [mydb]> CREATE TABLE students (ID INT, Name VARchar (+), age TINYINT UNSIGNED not NULL, gender ENUM (' F ', ' M ')) PARTITION by range (PARTITION Youngman values less th        An (+), partition middleman values less than (a), partition Oldman values less than maxvalue); [[email protected] ~] #for i in {1..1000};d o mysql-e "insert into mydb.students values ($i, ' stu$i ', $[$[random%90]+18 ], ' ${gender[$[random%2]]} ';d one [[email protected] mydb] #ls # #可以看到表结构文件只有一个, but the tablespace file is divided into three db.opt St Udents.frm Students.par students#p#middleman.ibd students#p#oldman.ibd students#p#youngman.ibd According to hash: Mar IADB [mydb]> CREATE TABLE students (ID INT, name CHAR (+) not NULL, age TINYINT UNSIGNED, gender ENUM (' F ', ' M ')) Partit           ION by hash (ID) partitions 5; [[email protected] mydb] #for i in {1..1000};d o mysql-e "insert into mydb.students values ($i, ' stu$i ', $[$[random%90] +18], ' ${gender[$[random%2]]} ';d one [[email protected] mydb] #ls db.opt students.frm Students.par St Udents#P #p0.ibd students#p#p1.ibd students#p#p2.ibd students#p#p3.ibd STUDENTS#P#P4.IBD Indicates the number of partitions; Note that names cannot be hashed, because the number of names  According to the type is char or varchar, will fill the space, so is not OK, can not be hashed, the hash value is not determined according to the list: MariaDB [mydb]> CREATE TABLE students (ID INT, name CHAR (+) NOT NULL, age TINYINT UNSIGNED, gender ENUM (' F ', ' M '), Majorid TINYINT UNSIGNED not null) PARTITION by list (Majo        RID) (PARTITION p0 values in (1,4,7), PARTITION P1 values in (2,5,8), PARTITION P2 values in (3,6,9)); [[email protected] mydb] #for i in {1..1000};d o mysql-e "insert into mydb.students values ($i, ' stu$i ', $[$[random%90]  +18], ' ${gender[$[random%2]]} ', $[$[random%9]+1]) ";d one [[email protected] mydb] #ls db.opt students.frm Students.par students#p#p0.ibd students#p#p1.ibd Students#p#p2.ibd6, SQL mode: Defines the settings for the response behavior of mysqld for violations such as constraints; commonly used MO De:traditional strict_trans_tables strict_all_tables Modification method: mysql> S            ET global sql_mode= ' mode ';Mysql> SET @ @global. sql_mode= ' mode '; Summary: 1, use the index to speed up query Speed 2, use the query cache or the side-hanging cache to provide access speed, using variables to improve the hit ratio. 3, table partitioning can improve the efficiency of query and write operations

MySQL Foundation tuning