MySQL Crawl SQL Slow query statement

When the MySQL server is abnormal ( slow ), first of all to consider whether the SQL statement caused the database slow, if the situation is more urgent, we will SHOW full processlist immediately; To see, but I recommend that you use the- e parameter , in a non-interactive way, because you can use grep and other commands to filter the results, more convenient and intuitive to see the results

a method of grasping SQL slow query statement , there are 2 kinds:

1, temporary Emergency Crawl

Through show full processlist; The way, executes several times, has the same statement, may be the SQL slow query statement;

SHOW full processlist; #查看MySQL the thread that is running

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/95/70/wKioL1kVPRThMW4wAACNCMJfTpg663.png "title=" 1_ copy. png "alt=" Wkiol1kvprthmw4waacncmjftpg663.png "/>

The most critical of this command is the State column, which is listed in the following categories:

checking table    #正在检查数据表 (this is automatic).  Closing tables    #正在将表中修改的数据刷新到磁盘中, and closing tables that have been exhausted. This is a quick operation, and if not, you should confirm that the disk space is full or that the disk is under heavy load.  Connect Out       #复制从服务器正在连接主服务器.  Copying to tmp table on disk  #由于临时结果集大于  tmp_table_size, Saving memory by converting temporary tables from memory storage to disk storage.  Creating tmp table  #正在创建临时表以存放部分查询结果.  deleting from main table  #服务器正在执行多表删除中的第一部分, just delete the first table.  deleting from reference tables   #服务器正在执行多表删除中的第二部分, deleting records from other tables.  Flushing tables   #正在执行  flush tables, waiting for other threads to close the data table.  Killed    #发送了一个kill请求给某线程, the thread will check the kill flag bit and discard the next kill request. MySQL checks the kill flag   bit in each of the main loops, but in some cases the thread may die in a short period of time. If the line regulation regulation is locked by another thread, the kill request will take effect as soon as the lock is released.  Locked     #被其他查询锁住了.  Sending data   #正在处理  SELECT  Records of the query, and is sending the results to the client.  sorting for group  #正在为  GROUP BY  do sort.  Sorting for order   #正在为  ORDER BY  do sort.  Opening tables   #这个过程应该会很快, unless otherwise disturbed by other factors. For example, a data table cannot be opened by another thread until the  ALTER TABLE  or  LOCK TABLE  statement line is complete.   is trying to open a table.  Removing duplicates   #正在执行一个  SELECT DISTINCT  Way of inquiry, However, MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again, and then send the results to the client.  Reopen table  #获得了对一个表的锁, but this lock must be obtained after the table structure has been modified. The lock has been released, the data table is closed, and the data table is being tried again.  Repair by sorting  #修复指令正在排序以创建索引.  Repair with keycache  #修复指令正在利用索引缓存一个一个地创建新索引. It will be slower than  Repair by sorting .  Searching rows for update   #正在讲符合条件的记录找出来以备更新. It must be completed before  UPDATE  is required to modify the relevant records.  Sleeping  #正在等待客户端发送新请求 . system lock  #正在等待取得一个外部的系统锁. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can suppress the external system lock by increasing the  --skip-external-locking parameter.  Upgrading lock INSERT DELAYED  #正在尝试取得一个锁Table to insert a new record.  Updating  #正在搜索匹配的记录, and modify them.  INSERT DELAYED  #已经处理完了所有待处理的插入操作, waiting for a new request. Summary: The general simple query should be completed in 2 seconds, if the time-out may exist exception in addition, the above state   most of the problem, for the troubleshooting provided, similar error code;

2, Periodic analysis-how to record slow query logs

I work, usually in the matching file, set 3 parameters

• Query over 2 seconds

• Queries that do not follow an index

• Add slow query log # # log file in MY.CNF definition

through the above 3 parameters, collect the slow query log, through writing scripts, mysqladmin for log cutting, using MSYQLSLA tools for analysis, and then 8 times a day through scheduled tasks to execute, regularly send the results by mail, sent to the company's DBA, or himself, Core Development, CC CTO

Msyqlsla Tool Address : http://blog.itpub.net/7607759/viewspace-692828

Second, we caught the slow query, how to optimize?

optimization is the most basic is to create the index, how to create the appropriate index, so we will use the Explian command, added in front of the slow query statement, and we can add parameters Sql-no-cache \g, see if we go index, note that there is a key, Really show whether to walk the index, if not walk the index, it is necessary to set up, then how to set it???

For example, we query all users of the current system, and this query statement enables the PRIMARY primary key index (see key)

Mysql> explain select User,host from Mysql.user \g*************************** 1. Row *************************** id:1 select_type:simple table:user Type:indexpossible_keys: NULL key:primary key_len:228 ref:null rows:6 extra:using index1 row in Set (0.0 0 sec)

View table structure (RPI primary key index)

mysql> desc mysql.user;+------------------------+-----------------------------------+------+----- +-----------------------+-------+| field                   | Type                                | Null | Key | Default                | extra |+------------------------+----------- ------------------------+------+-----+-----------------------+-------+| host                    | char (60)                            | no   | pri |                        |        | |  User                    | char (            )               | NO    | PRI |                        |       |

Third, how to create an index?

We can index the column after the where condition, try to select the columns on the large table with the unique value of the index, (for example, the gender column unique value is not suitable for indexing), if the condition column has several columns, the unique value is very few, we can establish the joint index to achieve the optimization purposes, the federated index has the prefix attribute, Queries are frequently placed in front of the column, the details are not said, confirm how to index, we start to create the index

1, there are 2 ways to create an index:

• Alter

• Create

when the amount of data is particularly large, we try to choose database at a trough or select Night To avoid any impact on site visits, except in emergency situations;

Iv. Higher-level optimization

You can also use the Select Profile feature to optimize every detail of the SQL statement, and I don't use a lot of it here, usually the company's professional DBA to handle it.

Of course, you will encounter a special long SQL statement, the optimization of spare time, I will send the SQL statement to the core development, processing, such as 1 statements is very slow, I can be 2, walk the index, there may be very high, can also use to improve the product, improve the structure of the way, such as the statement is not optimized, We can put in the internal query from the library;

There are some more advanced, listen to the old boy teacher Introduction: Can add cache (NOSQL), static, asynchronous message queue, etc., follow-up learning continue .....

Profile optimization Case: http://blog.csdn.net/isoleo/article/details/46508669

This article is from the "funny Brother Notes" blog, be sure to keep this source http://qiuyt.blog.51cto.com/1229789/1924958

MySQL Crawl SQL Slow query statement