Mysql optimization and mysql Performance Optimization

Source: Internet
Author: User

Mysql optimization and mysql Performance Optimization

1. view the mysql server configuration information show variables. You can also use show variables like 'key % '. 2. view the status values of the mysql server running show status, which is the current session, if you quit, it becomes invalid. Show global status; global session. Restart the database or shut down the database. 1) Use the show status statement to query database performance, for example, show status like 'value'. The value parameter has connections: Number of times the mysql server is connected; uptime: mysql Server launch time; slow_queries: Slow Query Count; com_select: Query operation count; com_insert: insert operation count; com_update: update operation count; com_delete: number of delete operations. Note: There are some parameters in msyql to query InnoDB tables. For example, the InnoDB_rows_read parameter indicates the number of records queried by the select statement; InnoDB_rows_inserted indicates the number of records inserted by the insert statement; and InnoDB_rows_updated indicates the number of records updated by the update statement; the InnoDB_rows_deleted parameter indicates the number of records deleted by the delete statement. 3. You can use explain or desc to analyze the query statement format: explain select statement; mysql> explain select * from pet71 \ G *************************** 1. row ** id: 1 select_type: SIMPLE table: pet71 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: NULL1 row in set (0.00 sec) id: indicates the number of the select statement; select_type: indicates the type of the select statement. Simple indicates a simple query, excluding connection queries and subqueries; primary indicates the primary query or the query statement at the outermost layer; union indicates the second or later query statement of the connection query; table: The queried table. type: the connection type of the table. Among them, system indicates that the table has only one record; const indicates that the table has multiple records, but only one record is queried from the table; all indicates that the table has been completely scanned; eq_ref indicates that when multiple tables are connected, the following tables use the unique or primary key; ref indicates that when multi-table queries are performed, the following tables use normal indexes; unique_subquery indicates that unique or primary key is used in the subquery; index_subquery indicates that the common index is used in the subquery; range indicates that the query range is provided in the query statement; index indicates that the indexes in the table are completely scanned; possible_keys indicates the indexes that may be used in the query; key: indicates the index used in the query; key_len: indicates the length of the index field; ref: indicates the column or constant used together with the index to query records; rows: indicates the number of queried rows; extra: indicates the attachment information in the query process 1) optimize index query a. When the like keyword is used in the query statement for query, if the first character of the matching string is "%", The index will not be used. If "%" is not in the first position, the index will be used. B. Multi-column indexes are used only when the first field is used in the query condition. C. When the query statement contains only the or keyword, if the columns of the two conditions before or are both indexed, the query will use the index. If the column with a condition before or is not an index, no index is used in the query. 2) Optimize the query results of the inner query statements in the subquery to create a temporary table. Therefore, the speed will be affected. You can use connection query instead of subquery. 4. Optimize database structure 1). Some tables have many fields and can be divided into multiple tables. Fields with large data volumes, such as the remarks column, can be stored in a single table. To display this field, you can use table join query. 2) add an intermediate table: Create a new table to store the data queried by the joined table, and then query the intermediate table directly. 3) adding redundant fields 5. Optimizing the insert record speed when inserting records, index and uniqueness check will affect the speed of inserting records. It takes different times to insert multiple records at a time. 1) disable INDEX alter table name disable keys; re-enable index alter table name enable keys after insertion; 2) disable uniqueness check set unique_ckecks = 0; re-enable the uniqueness check set unique_ckecks = 1; 3). Optimize the insert statement: insert into food values (null, a, B), (null, B, c ); or insert into food values (null, a, B) insert into food values (null, B, c); the first reduces the connection to the database and the second inserts fast note: the load data infile statement imports data faster than the insert statement. 6. analysis tables, checklists, and optimized table analysis tables are used to analyze the segments of keywords. The purpose of the checklist is to check whether there is an error in the checklist and whether there is an error in the view. Optimize tables to eliminate space waste caused by deletion or update. 1) analyze table name 1 [, table name 2...]; during the analysis table, the database system adds a read-only lock to the table. During analysis, only records in the table can be read, and records cannot be updated or inserted. The analysis statement can analyze InnoDB and MyISAM tables. For example, mysql> analyze table pet60; + tables + --------- + ---------- + | Table | Op | Msg_type | Msg_text | + --------------- + --------- + ---------- + | test1.pet60 | analyze | status | OK | + --------------- + --------- + ---------- + 1 row in set (0.53 sec) OP: indicates the operation executed. Analyze indicates the analysis operation. Check indicates checking and searching. Optimize indicates the optimization operation. Msg_type indicates the information type. The displayed values are usually status, warning, error, and information. Msg_text indicates the display information. 2) Check the table to check whether there are errors in InnoDB and MyISAM tables. The read-only lock is also applied to the table during execution of the statement. Syntax: check table name 1 [, table name 2...] [option]. The optioin parameter has five parameters: quick, fast, changed, medium, and extended. The execution efficiency of these five parameters is reduced in turn. The option is only valid for tables of the MyISAM type and invalid for tables of the InnoDB type. 3) Optimize the table. This statement is valid for InnoDB and MyISAM tables. However, the optimize table statement can only optimize varchar, blob, or text fields in the table. For example, optilmize table name 1 [, table name 2...]; this statement can be used to eliminate disk fragments caused by deletion and update, thus reducing space waste. The read-only lock is also applied to the table during execution of the statement. Note: If a table uses a data type such as text or blob, the previously allocated disk space will not be automatically reclaimed after the update or delete operations, which may result in a waste of disk space. The optimization statement can be used to sort out the disk fragments for future reuse. 7. Optimizing mysql servers includes server hardware optimization and mysql service parameter optimization. 1) optimizing server hardware a. You can set more buffers for mysql in the memory. Computer 4G memory can be selected as the my-innodb-heavy-4G.ini mysql database configuration file. However, this configuration file mainly supports InnoDB Storage engine tables. 2G computer memory can select my-huge.ini as the configuration file. B. data can be stored on multiple disks. data can be read from multiple disks in parallel, improving the reading speed. The image mechanism can be used to synchronize mysql servers on different computers. The data on these servers is the same, reducing the pressure on a single server. 2) Optimize mysql parameter memory to reserve some cache areas for mysql. The cache size is set in the mysql configuration file. Important configuration parameters in mysql are in the [mysqld] group of my. cnf or my. ini file. For example, key_buffer_size indicates the size of the index cache. The greater the value, the faster the index is to be queried. Table_cache: the number of tables opened at the same time. The larger the value, the more tables that can be opened at the same time. Query_cache_size: the size of the query cache. The default value is 0. It is only applicable to operations with few modification operations and the same query is often executed. Query_cache_type: indicates the enabled status of the query buffer. If the value is 0, the query is disabled. If the value is 1, the query cache is enabled. If the value is 2, the query cache is used as required. When the value is 1, add the SQL _no_cache keyword to the query statement. This query statement does not use the query cache. You can use the flush query cache statement to clear fragments in the query cache. When the value is 2, the select statement uses the SQL _cache keyword to query the cache. For example, select SQL _cache * from score; max_connectioins: indicates the maximum number of connections to the database. Sort_buffer_size: indicates the size of the cache area. The higher the value, the faster the sorting speed. Read_buffer_size: indicates the buffer size reserved for each thread. This buffer zone is used when the thread needs to continuously read records from the table. Set session read_buffer_size = n; you can set the value of this parameter temporarily. Read_rnd_buffer_size: indicates the buffer size reserved for each thread. Similar to read_buffer_size. However, it is mainly used to store records read in a specific sequence. You can also use set session read_rnd_buffer_size = n to temporarily set the value of this parameter. Innodb_buffer_pool_size: indicates the maximum cache of InnoDB tables and indexes. The greater the value, the faster the query speed. Innodb_flush_log_at_trx_commit: indicates when to write data in the buffer zone to the log file and write the log file to the disk. This parameter has three values: 0, 1, and 2. The default value of this parameter is 1. If it is 0, data is written to the log file every 1 second and the log file is written to the disk; if the value is 1, data is written to the log file and the log file is written to the disk each time a transaction is committed. If the value is 2, data is written to the log file every time a transaction is committed, write log files to the disk every 1 second.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.