MySQL performance Learning 1

1. Use the explain statement to view the analysis results, as shown in figure
Explain select * From test1 where id = 1;
:
Id selecttype table Type possible_keys key key_len ref rows extra columns

Where, type = const indicates that the index is located once. If key = primary, the primary key is used.
Type = All, indicates full table scan, and key = NULL indicates no index is used;
Suppose in the test1 table, the structure is as follows:
ID name

If the ID is auto-incrementing and the name has an index set, you will find
Type = ref, because it is considered to be multiple matching rows, in the joint query, it is generally ref

2. combined index in MySQL
Assume that the table has IDs, key1, key2, key3, and a composite index.
For example:
Where key1 = ....
Where key1 = 1 and key2 = 2
Where key1 = 3 and key3 = 3 and key2 = 2
According to the leftmost principle, indexes can be used.
For example
From test where key1 = 1 order by key3
If you use explain for analysis, only the normal_key index is used, but only the WHERE clause works. The order by clause must be sorted.

3. Use slow query analysis:
In my. ini:
Long_query_time = 1
Log-Slow-queries = D: \ mysql5 \ logs \ mysqlslow. Log
Records that exceed 1 second in the slow query log
You can use mysqlsla for analysis. In mysqlreport
DMS analyzes the percentage of select, update, insert, delete, replace, and so on.

4. MyISAM and InnoDB locking
In MyISAM, note the table lock. For example, after multiple update operations and then select, you will find that the Select Operation is locked and must wait for all

After the update operation is complete, select
If InnoDB is used, the row lock is used. The above problem does not exist.
5. MySQL transaction configuration items
Innodb_flush_log_at_trx_commit = 1
Indicates that the transaction log is written to the disk immediately when the transaction is committed, and the data and index are also updated.

Innodb_flush_log_at_trx_commit = 0
When a transaction is committed, the transaction log is not immediately written to the disk and written every 1 second.
Innodb_flush_log_at_trx_commit = 2
When the transaction is committed, immediately write the disk file (here only write to the kernel buffer, but not immediately refresh to the disk, but refresh to the disk every one second, the same

When updating data and indexes,