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

How to optimize the SQL and index of MySQL database? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Partitions allow multiple parts of a single table to be allocated across file systems according to specified rules. Different parts of the table are stored as separate tables in different locations.

How do SQL and index optimization find problematic SQL?

Use MySQL slow check log to monitor the SQL of efficiency problems

Show variables like 'slow_query_log'set global slow_query_log_file =' / home/mysql/sql_log/mysql-show.log'set global log_queries_not_using_indexs = on Set global long_query_time = 1 Storage format of slow log # Time: 140606 12 User@Host 30 User@Host 30 affix 17 peg / host information for performing SQL # User@Host: root [root] @ localhost [] / / execution information of SQL # Query_time:0.000031 Lock_time:0.000000 Rows_sent: 0 Rows_examined:0// SQL execution time SET timestamp = 1402029017 SQL content select CONCAT ('storage engine:',@@storage_engine) as INFO; analysis tool for slow log

-mysqldumpslow

-pt-query-digest

How to find a problematic SQL through the slow query log?

1. SQL with a large number of queries and a long time for each query

Usually the first few queries for pt-query-digest analysis

2.IO big SQL

Note the Rows examine item in pt-query-digest analysis

3. SQL that missed the index

Note the comparison between Rows examine and Rows Send in pt-query-digest analysis

Related recommendations:

Mac uses terminal to run mysql,mysql terminal, mysql mac,mysql directory, mysql path, macmysql

Mysql partition | mysql partition table | mysql partition | by range

Thank you for reading! After reading the above, do you have a general understanding of MySQL database SQL and index optimization? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.