Shulou(Shulou.com)06/01 Report--

Preface

No matter which kind of service, the optimization is nothing more than two aspects: the first is the optimization of the hardware, and the second is the optimization of the system and the service itself.

1. Query the number of times to connect to the MySQL server mysql > show status like 'connections' +-+-+ | Variable_name | Value | +-+-+ | Connections | 3 | +-+-+ 1 row in set (0.01 sec) 2. Query the running time of MySQL server mysql > show status like 'uptime' / / Unit is "second" +-+-+ | Variable_name | Value | +-+-+ | Uptime | 127th | +-+-+ 1 row in set (0.00 sec) 3. Number of query operations mysql > show status like 'com_select' +-+-+ | Variable_name | Value | +-+-+ | Com_select | 12 | +-+-+ 1 row in set (0.00 sec) 4. Number of insert operations mysql > show status like 'com_insert' +-+-+ | Variable_name | Value | +-+-+ | Com_insert | 1 | +-+-+ 1 row in set (0.00 sec) 5. Number of update operations mysql > show status like 'com_update' +-+-+ | Variable_name | Value | +-+-+ | Com_update | 1 | +-+-+ 1 row in set (0.00 sec) 6. Number of deletion operations mysql > show status like 'com_delete' +-+-+ | Variable_name | Value | +-+-+ | Com_delete | 0 | +-+-+ 1 row in set (0.00 sec) 7. The number of slow queries for querying MySQL servers mysql > show status like 'slow_queries' +-+-+ | Variable_name | Value | +-+-+ | Slow_queries | 21 | +-+-+ 1 row in set (0.00 sec) 2. Analyze the SQL statement. 1. Use the explain keyword to analyze mysql > explain select * from stu_info\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: stu_info # Table name partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL # which column or constant is used with the index to query records rows: 3 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec)

The above select_type is explained as follows:

Select_type: indicates the type of select statement where simple is a simple query (excluding join query and subquery) Primary main query Union join query 2 、 Using index to improve query efficiency mysql > explain select * from stu_info where s_id=3\ G # Analysis of query results without index is as follows: * * 1. Row * * id: 1 select_type: SIMPLE table: stu_info Partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 # need to query three rows to find (the total data of this table is only three rows) filtered: 33.33 Extra: Using where1 row in set 1 warning (0.00 sec) mysql > create index index_01 on stu_info (s_id) # create index mysql > explain select * from stu_info where s_id=3\ G # query again * * 1. Row * * id: 1 select_type: SIMPLE table: stu_info partitions: NULL type: refpossible_ Which index name is used by keys: index_01 # key: index_01 key_len: 5 ref: const rows: 1 # after creating the index You can find it in line 1 of the query. Filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)

Considerations for using the index are as follows:

After indexing, the most efficient query is not in the first place with like 'xx%'%; if you use a multi-field index, except for the fastest query in the first field, the index will not be indexed, and the index will not take effect; if you create the field set by the index, the values on the left and right sides of the query index combination or belong to the values under the index setting field.

For other considerations on using indexes, you can refer to the blog post: MySQL Index Type details

3. Profiling analysis and query

Through the slow log query, we can know which SQL statements are inefficient. Through explain, we can know the specific implementation of SQL statements, index use, and so on. We can also check the execution status with the show command. If you feel that the explain information is not detailed enough, you can get more accurate information about the SQL execution consumption of system resources through the profiling command. Profiling is off by default. You can view it with the following statement:

1. Check whether profiling enables mysql > show variables like'% profiling%'. +-+-+ | Variable_name | Value | +-+-+ | have_profiling | YES | | profiling | OFF | # OFF indicates that it is not enabled | profiling_history_size | 15 | | +-+ 3 rows in set (0.00 sec) mysql > select @ @ profiling | +-+ | @ @ profiling | +-+ | 0 | # 0 means +-+ 1 row in set is not enabled, 1 warning (0.00 sec) 2, enable profilingmysql > set profiling=1; # enable Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select @ @ profiling # qu+-+ | @ @ profiling | +-+ | 1 | +-+ 1 row in set, 1 warning (0.00 sec) 3. Execute the SQL statement to be tested mysql > select * from bank +-+-+ | name | money | +-+-+ | lu | 1000 | qi | 1000 | zhang | 2000 | +-+-+ 3 rows in set (0.00 sec) 4. View the ID corresponding to the SQL statement and analyze it mysql > show profiles +-+ | Query_ID | Duration | Query | +-+ | 1 | 0.00012925 | select @ @ profiling | | 2 | 0.00401325 | SELECT DATABASE () | 3 | 0.01405400 | show databases | 4 | 0.00034675 | show tables | 5 | 0.00011475 | show tabels | 6 | 0.00029225 | show tables | 7 | 0.00041200 | select * from bank | 8 | 0.00020225 | select * from bank | +-- -+-+ 8 rows in set 1 warning (0.00 sec) mysql > show profile for query 7 # query detailed analysis of sql statements +-- +-+ | Status | Duration | +-+-+ | starting | 0.000161 | | checking permissions | 0.000010 | | Opening tables | 0 . 000016 | | init | 0.000047 | System lock | 0.000013 | | optimizing | 0.000004 | statistics | 0.000013 | preparing | 0.000009 | executing | 0.000004 | Sending data | 0.000050 | end | 0.000004 | query end | 0.000008 | closing tables | 0.000007 | | freeing Items | 0.000012 | | logging slow query | 0.000041 | | cleaning up | 0.000013 | +-+-+ 16 rows in set 1 warning (0.00 sec)

In the return result of the above command, status is the state in profile, and duration is the time-consuming in status, so we focus on which state is the most time-consuming, which of these states can be optimized, and you can also view more information, such as CPU and so on. The syntax is as follows:

Mysql > show profile block io for query 7\ Gmysql > show profile all for query 7\ G

In addition to the block io and all above, it can also be replaced with cpu (displaying user cpu time, system cpu time), ipc (displaying sending and receiving overhead information), page faults (displaying overhead information related to page errors), and swaps (displaying information related to the number of exchanges).

Note: after the test is complete, remember to turn off the debugging function so as not to affect the normal use of the database.

Fourth, optimize the structure of database tables.

The optimization of the database table structure can probably start from the following aspects:

Decompose a table with a large number of fields into multiple tables to avoid too many table fields as far as possible; increase intermediate tables and reasonably increase redundant fields; optimize the speed of inserting records; disable the index before inserting data, which will make the creation of the index ineffective. Command: ALTER TABLE table_name DISABLE KEYS; according to the actual situation, disable uniqueness checking before inserting records, command: set unique_checks=0; multiple commands for inserting data are best integrated into one Use load data infle to insert data in bulk. For innodb engine tables, the following can be optimized: disable uniqueness checking: set unique_checks=0; disables foreign key checking: set foreign_key_checks=0; disables autocommit: set autocommit=0; analysis table, check table, and optimization table

The so-called analysis table is to analyze the distribution of keywords, the check table is to check for errors, and the optimized table is a waste of space caused by deletion or update.

1. Analysis table

Analysis tables can be analyzed one or more tables at a time, can only be read during the analysis, and cannot be inserted or updated. The syntax of the parsing table is as follows:

Mysql > analyze table bank +-+ | Table | Op | Msg_type | Msg_text | +-+ | test01.bank | analyze | status | OK | | +-+ 1 row in set (0.00 sec) |

The result returned above is explained: Table is the table name, what is the operation performed by op, msg_type information level (status is normal, info is information, note note, warning warning, error error), msg_text is the display message.

2. Check list

Check for errors, keyword statistics, check views for errors Check table table name option = {quick | fast | medium | extended | changed} Quick does not scan rows, does not check incorrect connections Fast only checks tables that have not been properly closed Medium scan lines to verify that deleted connections are valid, and can also calculate the keyword checksum of each line. Extended makes a comprehensive keyword search for all keywords in each row, Changed only checks tables that have been changed since the last check and tables that have not been closed correctly, Option is only valid for myisam, but not for innodb tables, and will add a read-only lock to the table during execution.

Mysql > check table bank +-+ | Table | Op | Msg_type | Msg_text | +-+ | test01.bank | check | status | OK | +- -+ 1 row in set (0.00 sec) 3, Optimization table

To eliminate the waste of space caused by deletion or update, the syntax format of the command is: Optimize [local | no_write_to_binlog] table tb1_name... Both the optimized myisam table and the innodb table are valid, but only the varchar\ text\ blob numeric type in the table is optimized and read-only locks are applied during execution.

Mysql > optimize table bank\ gateway * 1. Row * * Table: test01.bank Op: optimizeMsg_type: noteMsg_text: Table does not support optimize Doing recreate + analyze instead** 2.row * * Table: test01.bank Op: optimizeMsg_type: statusMsg_text: OK2 rows in set (0.04 sec)

-this is the end of this article. Thank you for reading-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.