How to obtain channels for SQL with performance problems

  1. Obtain SQL with performance problems through user feedback

  2. You can obtain the SQL with performance problems by slowly querying logs

  3. Obtain SQL with performance problems in real time

Slow query log description

  • Slow_quey_log =on Starts recording slow query logs

  • Slow_query_log_file specifies the path and file where slow query logs are stored (by default, in MySQL’s data directory)

  • Long_query_time Specifies the threshold for logging slow query log SQL execution (10 seconds by default, 0.001 seconds is usually appropriate)

  • Log_queries_not_using_indexes Specifies whether to record SQL that does not use an index

    set global sql_query_log=on;

    sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=tests --mysql-user=sbtest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock run

Slowly query the log analysis tool

mysqldumpslow

  • Summarize the same SQL except for the query criteria and print the analysis results in the order specified in the parameters

    mysqldumpslow -s r -t 10 slow-mysql.log

    -s order(c,t,l,r,at,al,ar)

    1. C Sort queries by number of times
    2. T Sort the query by the total time
    3. L Sort by lock time in the query
    4. R sorts by the total rows of data returned in the query
    5. Average number of AT, AL and AR to sort

    -t top[specifies to take the first few as the final output]

pt-query-digest

pt-query-digest \

- the explain h = 127.0.0.1, u = root, p = p @ ssWord \

slow-mysql.log

Pt-query-digest –explain h=127.0.0.1 slow-mysql.log > slow.rep

Obtain SQL with performance problems in real time

select id,user,host,db,command,time,state,info FROM information_schema.processlist WHERE time>=60

Why is the query speed so slow?

  1. The client sends an SQL request to the server
  2. The server checks whether the SQL can be hit in the query cache
  3. SQL is parsed and preprocessed by the server, and then the optimizer generates the corresponding execution plan
  4. Based on the execution plan, the storage engine API is called to query the data
  5. Returns the result to the client

For a system with frequent reads and writes, the use of the query cache may reduce the efficiency of query processing, and it is not recommended to use the query cache

2. Parameters involved: Query_cache_type Setting whether the query cache is available [ON,OFF,DEMAND] DEMAND indicates that only SQL_CACHE and SQL_NO_CACHE are used in the query statement to control whether the query_cache_size is required Query_cache_limit Specifies the maximum amount of memory available to the query cache (SQL_NO_CACHE improves efficiency). Query_cache_wlock_invalidate Specifies whether a locked table returns data from the cache Query_cache_min_res_unit Sets the minimum unit of memory blocks allocated by the query cache. 3.MySQL interacts with the storage engine to parse the SQL according to the execution plan. The syntax parsing stage of optimizing SQL query plan is to parse MySQL statements through keywords and generate a corresponding parse tree. The MySQL parser will use MySQL syntax rules to verify and parse queries, including checking whether the syntax uses the correct key walk; Whether the order of keywords is correct and so on; The pre-processing stage is to further check whether the parse tree is valid according to MySQL rules to check whether the tables and data columns involved in the query exist and whether there is ambiguity in the name or alias and so on. The query plan phase of the optimizer SQL selects the optimal execution plan based on the cost model. The following are 7 factors that influence the selection of the optimal query plan. Statistics are not accurate 2. Cost estimates in execution plans are not equal to the actual cost of execution plans 3. What the MySQL optimizer considers optimal may not be the same as what you consider optimal 4. This may affect the speed of the current query. 5.MySQL sometimes generates execution plans based on fixed rules. 6.MySQL does not consider the cost that is not under its control. Redefine the association order of the table. 2. Convert the outer join to the inner join. 3. Optimize count(),min() and Max ()[select tables optimozed Away] 5. Convert an expression to a constant expression 6. Subquery optimization 7. Terminate the query early 8. Optimize the in() conditionCopy the code

How do you determine the time consumed in each phase of query processing

  • Using profile[not recommended, will be removed from mysql in the future]

    1. set profiling = 1; Start profile, which is a session-level configuration.
    2. Execute the query
    3. show profiles; [View information on the total time consumed by each query]
    4. show profile for query N; [Time consumed at each stage of the query]
    5. show profile cpu for query N; [View information about the time and CPU consumed in each phase]

  • Using performance_schema

    1. Monitoring and history table information required to start

      update setup_instruments set enabled=’yes’,timed=’yes’ where name like ‘stage%’;

      update setup_consumers set enabled=’yes’ where name like ‘events%’;

    2. SELECT a.thread_id, sql_text, c.event_name, (c.timer_end – c.timer_start) / 1000000000 AS ‘duration(ms)’ FROM events_statements_history_long a JOIN threads b on a.thread_id=b.thread_id JOIN events_stages_history_long c ON c.thread_id=b.thread_id AND c.event_id between a.event_id and a.end_event_id WHERE b.processlist_id=CONNECTION_ID() AND a.event_name=’statement/sql/select’ ORDER BY a.thread_id,c.event_id

Specific SQL query optimization

  • Update and delete large tables

    delimiter ? use 'imooc'? drop procedure if exists 'p_delete_rows'? Create definer='root'@'127.0.0.1' PROCEDURE 'P_delete_rows '() begin DECLARE v_rows int; set v_rows int, while v_rows=1, while v_rows>0 do delete from test where id>=9000 and id<=19000 limit 5000; select row_count() into v_rows; select sleep(5); end while; end ? delimiter;Copy the code

  • How do I modify the table structure of a large table

    1. If the column type of a table is changed and the width of the column is changed, the table is still locked

    2. The delay of the primary and secondary databases cannot be resolved

    Modification method:

      pt-online-schema-change 
  --alter="modify c varchar(150) not null default''" 
  --user=root --password=PassWord D=testDataBaseName,t=tesTableName 
  --charset=utf-8 --execute
Copy the code

  • How do I optimize not in and <> queries

    SELECT customer_id, first_name, last_name, Email FROM customer WHERE customer_id NOT IN (SELECT customer_id FROM payment) # first_name, a.last_name, a.email FROM customer a LEFT JOIN payment b ON a.customer_id = b.customer_id WHERE b.customer_id IS NULLCopy the code

  • Select count(*) from product_comment where product_id=999; select * from product_comment where product_id=999;

    Create table product_comment_cnt(product_id int, CNT int); create table product_comment_cnt(product_id int, CNT int) Select sum(CNT) from(select CNT from product_comment_cnt where (select CNT from product_comment_cnt where (select * from product_comment_cnt where (select * from product_comment_cnt where (select * from product_comment_cnt where (select * from product_comment_cnt where (select * from product_comment_cnt where () product_id=999 union all select count(*) from product_comment where product_id=999 and timestr>DATE(NOW()) ) aCopy the code