Three methods for optimizing SQL in MySQL database

The following articles mainly describe the MySQL database optimization and SQL operation steps. We will describe the MySQL database optimization and SQL three steps, the following is a detailed description of the specific operations in these three steps. I hope you will gain some benefits after browsing them. MySQL database Optimization-SQL Step 1: disk seek capability, with high-speed hard drive (7200 to second)

The following articles mainly describe the MySQL database optimization and SQL operation steps. We will describe the MySQL database optimization and SQL three steps, the following is a detailed description of the specific operations in these three steps. I hope you will gain some benefits after browsing them. MySQL database Optimization-SQL Step 1: disk seek capability, with high-speed hard drive (7200 RPM)

The following articles mainly describe the MySQL database optimization and SQL operation steps. We will describe the MySQL database optimization and SQL three steps, the following is a detailed description of the specific operations in these three steps. I hope you will gain some benefits after browsing them.

MySQL database Optimization-SQLStep 1:

1: high-speed hard drive (7200 RPM). In theory, 7200 channels are searched per second. there is no way to change this. The optimization method is to use multiple hard disks or store data separately.

2: The read/write speed of the hard disk, which is very fast and easier to solve-it can be read and written in parallel from multiple hard disks.

3: The cpu. The cpu processes data in the memory. This is the most common limiting factor when there is a table with a relatively small memory.

4: Memory limit. When the cpu needs to exceed the data suitable for the cpu cache, the cache bandwidth becomes a bottleneck in the memory. However, the memory size is surprisingly large, and this problem will not occur.

MySQL database Optimization-SQL Step 2:(I am using the linux platform of the school website (Linux ADVX.Mandrakesoft.com 2.4.3-19mdk ))

1: Adjust server Parameters

Use the shell> MySQL command (the best combination with PHP) d-help to store a table of all MySQL (the best combination with PHP) options and configurable variables. output the following information:

Possible variables for option -- set-variable (-o) are:

Back_log current value: 5 // Number of connections required for MySQL (the best combination with PHP. back_log indicates how many connection requests can be stored in the stack during the time when MySQL (the best combination with PHP) suspends the connection.

Connect_timeout current value: 5 // MySQL (best combination with PHP) The server waits for a connection time before responding with bad handshake (poor translation)

Delayed_insert_timeout current value: 200 // The time for an insert delayed to wait for the insert before termination

Delayed_insert_limit current value: 50 // The insert delayed processor checks whether any select statements are not executed. If yes, run these statements before resuming.

Delayed_queue_size current value: 1000 // the size of the queue allocated for insert delayed

Flush_time current value: 0 // if it is set to a non-0 value, all tables are closed for each flush_time.

Interactive_timeout current value: 28800 // time the server waits for a foreign interactive connection before closing it

Join_buffer_size current value: 131072 // the buffer size connected to all

Key_buffer_size current value: 1048540 // the buffer size of the clause index block. You can increase it to better process the index.

Lower_case_table_names current value: 0 //

Long_query_time current value: 10 // If a query takes more time than this time, the slow_queried count will increase

Max_allowed_packet current value: 1048576 // size of a package

Max_connections current value: 300 // number of concurrent connections allowed

Max_connect_errors current value: 10 // if there are more than this number of interrupted connections, further connections will be blocked. You can use flush hosts to solve this problem.

Max_delayed_threads current value: 15 // Number of insert delayed processes that can be started

Max_heap_table_size current value: 16777216 //

Max_join_size current value: 4294967295 // Number of connections that can be read

Max_sort_length current value: 1024 // number of bytes used for sorting blob or text

Max_tmp_tables current value: 32 // number of temporary tables simultaneously opened by a connection

Max_write_lock_count current value: 4294967295 // specify a value (usually very small) to start MySQL (the best combination with PHP) d, so that the read lock occurs after a certain number of write locks

Net_buffer_length current value: 16384 // communication buffer size -- reset to this size during Query

Query_buffer_size current value: 0 // buffer size during Query

Record_buffer current value: 131072 // size of the buffer allocated to each table scanned by connections for each Sequential Scan

Sort_buffer current value: 2097116 // size of the buffer allocated for each sort connection

Table_cache current value: 64 // number of tables opened for all connections

Thread_concurrency current value: 10 //

Tmp_table_size current value: 1048576 // temporary table size

Thread_stack current value: 131072 // the size of each thread

Wait_timeout current value: 28800 // time the server waits for a connection before closing it 3

Configuring the above information as needed will help you.

MySQL database Optimization-SQL third:

1: If you create a large number of tables in a database, the open and close operations will be slow. 2: memory usage for MySQL (the best combination with PHP)

A: The keyword cache zone (key_buffer_size) is shared by all threads.

B: Each connection uses some specific thread space. one stack (64 k by default, the variable thread_stack), one connection buffer (the variable net_buffer_length) and one result buffer (net_buffer_length ). under certain circumstances, the connection buffer and result buffer are dynamically extended to max_allowed_packet.

C: All threads share a base memory.

D: No memory shot

E: each request for Sequential Scan is allocated a read buffer (record_buffer)

F: All joins are completed once, and most joins do not even need a temporary table. The most temporary table is a memory-based (heap) table.

G: One Sort buffer and two temporary tables are allocated for sorting requests.

H: All syntax analysis and computation are completed in one local memory.

I: Each index file is opened only once, and the data file is opened once for each concurrent thread.

J: For tables in each blob column, a buffer is dynamically expanded to read blob values.

K: The table processors of all tables in use are saved in a buffer and managed as a fifo.

L: A MySQL (best combination with PHP) admin flush-tables command to close all tables that are not in use and mark all tables in use at the end of the currently executed thread to prepare to close

3: MySQL (best combination with PHP) Locking table

All locks in MySQL (the best combination with PHP) won't become deadlocks. wirte lock: MySQL (the best combination with PHP) Locking Principle: a: If the table is not locked, it will be locked; B; otherwise, it will put the lock request into the write lock queue.

Read lock: MySQL (the best combination with PHP) Locking Principle: a: If the table is not locked, it will be locked; B; otherwise, the lock request will be placed in the read lock queue.

Sometimes many select and insert operations are performed in a table. You can insert rows in a temporary table and occasionally update the real table with the records of the temporary table.

Max_tmp_tables current value: 32 // number of temporary tables simultaneously opened by a connection

Max_write_lock_count current value: 4294967295 // specify a value (usually very small) to start MySQL (the best combination with PHP) d, so that the read lock occurs after a certain number of write locks

Net_buffer_length current value: 16384 // communication buffer size -- reset to this size during Query

Query_buffer_size current value: 0 // buffer size during Query

Record_buffer current value: 131072 // size of the buffer allocated to each table scanned by connections for each Sequential Scan

Sort_buffer current value: 2097116 // size of the buffer allocated for each sort connection

Table_cache current value: 64 // number of tables opened for all connections

Thread_concurrency current value: 10 //

Tmp_table_size current value: 1048576 // temporary table size

Thread_stack current value: 131072 // the size of each thread

Wait_timeout current value: 28800 // time the server waits for a connection before closing it 3

Configuring the above information as needed will help you.

The above content is an introduction to MySQL database Optimization-SQL. I hope you will get some benefits.