Shulou(Shulou.com)05/31 Report--

This article is about how MySQL uses indexes. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Transaction configuration items for MYSQL

Innodb_flush_log_at_trx_commit=1

Indicates that the transaction log is written to disk immediately when the transaction is committed, and the data and index are updated.

Innodb_flush_log_at_trx_commit=0

When a transaction commits, the transaction log is not immediately written to disk, every 1 second

Innodb_flush_log_at_trx_commit=2

When the transaction commits, write to the disk file immediately (here only write to the kernel buffer, but not flush to the disk immediately, but flush to the disk every 1 second, and update the data and index at the same time

Long_query_time: set the threshold for slow query. SQL that exceeds the second set value will be recorded in the slow log. The default value is 10s.

Slow_query_log: specifies whether to enable slow log

Log_slow_queries: specify whether to enable slow log (this parameter will be replaced by slow_query_log for compatibility reservation)

Slow_query_log_file: specify the location of slow log files, which can be empty. The system will give a default file host_name-slow.log.

Min_examined_row_limit: query check returns SQL that is less than the row specified in this parameter is not recorded in the slow query log

Log_queries_not_using_indexes: whether slow query logs that do not use indexes are recorded to the index

Of course, there are a lot of students here to see the demand, usually when we are running, monitoring Server, let me look at the files on a machine, I want to build a monitoring system, need to monitor each Slow log file, and parsing is very uncomfortable.

In order to solve this problem, MySQL kindly provides us with a systematic table to view, which is convenient for us to operate. For example, I want to look at the first 10 slowest statements.

It is important to note here that this method is only based on MySQL 5.1, and the process is as follows:

First, let's take a look at the default output mode. The script is as follows:

Show variables like 'log_output'

As you can see, the default output mode here is FILE, file, and we will change this to Table.

Set global log_output='TABLE';select sleep (10); select * from mysql.slow_log; Thank you for reading! This is the end of the article on "how to use the index in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.