MySQL database optimization Step 5

MySQL databaseIs a small relational database management system, MySQL databaseOptimizationIt is a very important task in the MySQL database operation process. MySQL database optimization can simplify MySQL database operations.

Step 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.

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> mysqld-help command to output the following information:

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

Back_log current value: 5 // the number of connections that mysql can have. back_log indicates how many connection requests can be stored in the stack during the time when mysql suspends the connection.

Connect_timeout current value: 5 // mysql Server waits for a connection time before responding with bad handshake

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 mysqld, 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.

Step 3:

1: If you create a large number of tables in a database, the open and close operations will be slow. 2: mysql uses memory.

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 mysqladmin 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 be closed.

3: mysql lock table

All locks in mysql will not become deadlocks. wirte locks: mysql locks Principle: a: If the table is not locked, the lock; B; otherwise, the lock request is put into the write lock queue.

Read lock: mysql 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.

A: Use the low_priority attribute to give a specific insert, update, or delete with a lower priority.

B: max_write_lock_count specifies a value (usually very small) to start mysqld, so that the read lock occurs after a certain number of write locks.

C: By using set SQL _low_priority_updates = 1, you can specify all the changes from a specific thread to be completed with a lower priority.

D: Use high_priority to specify a select

E: if an error occurs when using insert... select..., use myisam Table ------ because it supports concurrent select and insert

4: the most basic optimization is to minimize the space occupied by data on the hard disk. If the index is on the smallest column, the index is also the smallest. Implementation Method:

A: Use the smallest possible data type.

B: If possible, the declared table column is NOT NULL.

C: If possible, use a data type such as varchar (but the speed may be affected)

D: Each table should have the primary index as short as possible. e: create the required index.

F: if an index has a unique prefix on the first few characters, then only the index prefix-mysql supports the index on a part of a character column.

G: If a table is frequently scanned, try to split it into more tables.

Step 4

1: When Using indexes, the importance of indexes will not be mentioned, and functions will not be mentioned. first, make sure that all mysql indexes (primary, unique, index) are stored in Tree B. index terms:

A: Quickly locate record B with the where condition. When the join is executed, search for Row c from other tables to find the max () and min () values for specific index columns.

D: If sorting or grouping is prefixed at the beginning of an available key, sorting or grouping a table

E: A query may be used to optimize the search value without accessing the data file. if the columns in some tables are numeric and exactly the prefix of a column, you can retrieve the values from the index tree for faster speed.

2: The query speed for storing or updating data. grant execution will slightly reduce the efficiency.

Mysql functions should be highly optimized. You can use benchmark (loop_count, expression) to find out if there is a problem with the query.

Select query speed: If you want to make a select... where... faster. All I can think of is creating an index. you can run myisamchk -- analyze on a table to optimize the query. you can use myisamchk -- sort-index -- sort-records = 1 to sort an index and data.

3: mysql optimizes the where clause

3.1: Remove unnecessary parentheses:

(A AND B) AND c OR (a AND B) AND (a AND d)> (a AND B AND c) OR (a AND B AND c AND d)

3.2: Constant

(AB> 5 AND B = c AND a = 5

3.3: delete a constant condition

(B> = 5 AND B = 5) OR (B = 6 AND 5 = 5) OR (B = 100 AND 2 = 3)> B = 5 OR B = 6

3.4: the constant expression used by the index is calculated only once.

3.5: in a table, no where count (*) directly retrieves information from the table

3.6: All constants are read before any other table in the query.

3.7: The best join combination for external join tables is to try to find all possibilities.

3.8: if an order by clause and a different group by clause, order by, or group by clause contain columns not from the first joined table, create a temporary table.

3.9: If SQL _small_result is used, msyql uses a table in the memory.

3.10: indexes of each table are used for queries and indexes that span less than 30% rows.

3.11 skip rows that do not match the having clause before each record is output

4: optimize left join

In mysql, a left join B is implemented as follows:

A: Table B depends on Table.

B: Table a depends on all tables used in the left join condition (except for B)

C: All left join conditions are moved to the where clause.

D: perform all join optimization. Except a table, it is always read after all the tables it depends on. If there is a circular dependency, an error will occur.

E: perform all the standard where optimization f: If a row matches the where clause, but B does not have any matching left join conditions, all rows generated in B that are set to NULL

G: If left join IS used to locate rows that do NOT exist in some tables and column_name is null in the where clause (column_name is not null ). after mysql finds a row that matches the left join condition, it will stop searching for more rows.

5: optimized limit

A: If you use limit to select only one row, when mysql needs to scan the entire table, it serves as an index.

B: If limit # And order by are used, if mysql finds row #, the sorting will end, instead of sorting the entire table.

C: When limit # and distinct are combined, if mysql finds row #, it stops

D: As long as mysql has sent the first line to the customer, mysql will discard the query

E: limit 0 always returns an empty set quickly.

F: Use limit for the temporary table size # calculate the amount of space required to solve the query

6: optimize insert

Insert a record consists of the following:

A: Connection (3)

B: Send a query to the server (2)

C: Analysis query (2)

D: insert record (1 * record size)

E: insert an index (1 * index)

F: Disable (1)

The preceding figure is proportional to the total time.

Some methods to improve the insert speed:

6.1: if many rows are inserted from a connection at the same time, insert with multiple values is faster than using multiple statements.

6.2: if many rows are inserted from different connections, the insert delayed statement is faster.

6.3: myisam is used. If no row is deleted in the table, the row can be inserted While select: s is running.

6.4: when loading a table from a text file, load data infile is usually 20 times faster than insert.

6.5: The table can be locked and inserted. The major speed difference is that after all the insert statements are completed, the index buffer is saved to the hard disk only once. it is generally faster to store data as multiple insert statements. if you can use a single statement to insert all rows, locking is not required. locking also reduces the overall connection time. however, the maximum waiting time for some threads will rise. for example:

Thread 1 does 1000 inserts

Thread 2, 3 and 4 does 1 insert

Thread 5 does 1000 inserts

If no lock is used, 2, 3, 4 will be completed before 1 and 5. if locked, 2, 3, 4, it may be completed after 1 and 5. however, the overall time should be 40% faster. because the insert, update, and delete operations are fast in mysql, you can achieve better overall performance by locking more than five consecutive inserts or updates of a row. if you insert many rows, you can create a lock tables, and occasionally create an unlock tables (about every 1000 rows) to allow other threads to access the table. this will still lead to good performance. load data infile is still very fast for loading data.

To speed up load data infile and insert, expand the keyword buffer.

7. Optimize the update speed

The speed depends on the size of the updated data and the number of updated indexes.

Another way to make update faster is to delay modification, and then make a lot of modifications on one row and one row. If the table is locked, make a lot of modifications on one row and one row faster than one at a time.

8. Optimize the delete speed

The time for deleting a record is proportional to the number of indexes. to delete the record more quickly, you can increase the index cache size to delete all rows from a table much faster than deleting most of the table.

Step 5

1: select a table Type 1.1 static myisam

This format is the simplest and safest format. It is the fastest disk format. the speed comes from the difficulty that data can be found on the disk. when locking an index or static format, it is very simple, just multiply the length of the row by the number. in addition, when scanning a table, it is easy to read constant records from a disk each time. security comes from the fact that if the computer is down when a static myisam file is written, myisamchk can easily point out where each row starts and ends. Therefore, it can usually retrieve all records, except for some written records. all indexes in mysql can always be rebuilt.

1.2 dynamic myisam

In this format, each line must have a header indicating how long it is. when a record changes for a long time, it can end at more than one position. you can use optimize tablename or myisamchk to organize a table. if you want to access/change static data as some varchar or blob columns in the same table, move the dynamic column into another table to avoid fragmentation.

1.2.1 compress myisam and use the optional myisampack tool to generate

1.2.2 memory

This format is useful for small/medium-sized tables. copying/creating a common query table to a foreign heap table may speed up the joining of multiple tables. using the same data may take several times faster.

Select tablename. a, tablename2.a from tablename, tablanem2, tablename3 where

Tablaneme. a = tablename2.a and tablename2.a = tablename3.a and tablename2.c! = 0;

To accelerate it, you can use tablename2 and tablename3 to create a temporary table, because you can use the same column (tablename1.a) to search for it.

Create temporary table test TYPE = HEAP

SELECT

Tablename2.a as a2, tablename3.a as a3

FROM

Tablenam2, tablename3

WHERE

Tablename2.a = tablename3.a and c = 0;

SELECT tablename. a, test. a3 from tablename, test where tablename. a = test. a1;

SELECT tablename. a, test, a3, from tablename, test where tablename. a = test. a1 and ....;

1.3 static table features

1.3.1 default format. used when the table does not contain varchar, blob, and text Columns

1.3.2 fill all char, numeric, and decimal columns with column width

1.3.3 fast

1.3.4 easy Buffering

1.3.5 it is easy to re-create after being down because the record is in a fixed position

1.3.6 do not need to be reorganized (with myisamchk) unless a large number of records are deleted and the storage size is optimized

1.3.7 generally requires more storage space than dynamic tables

1.4 Characteristics of Dynamic tables

1.4.1 if the table contains any varchar, blob, and text columns, use this format

1.4.2 all string columns are dynamic

1.4.3 each record is preceded by a single digit.

1.4.4 usually requires more disk space than a fixed-length table

1.4.5 each record only uses the required space. If a record becomes large, it is divided into many segments as needed, resulting in record fragmentation.

1.4.6 if the row is updated with information that exceeds the length of the row, the row is segmented.

1.4.7 it is difficult to recreate the table after the system is down, because a record can be multiple segments

1.4.8 the expected row length for dynamic size records is 3 + (number of columns + 7)/8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7)/8

Penalty of 6 bytes for each connection. A dynamic record is connected whenever the change causes the record to become larger. each new connection has at least 20 bytes, so the next change may be in the same connection. if not, there will be another connection. you can use myisamchk-to check the number of connections. all connections can be deleted using myisamchk-r.

1.5 features of a compressed table

1.5.1 a read-only table created using the myisampack utility.

1.5.2 The decompression code is stored in all mysql distributions so that connections without myisampack can read tables compressed with myisampack.

1.5.3 occupies a small disk space

1.5.4 each record is compressed separately. The header of a record is a fixed length (1 ~~ 3 bytes) depending on the maximum table record. Each column is compressed in different ways. Some common compression types are:

A: Normally, there is a different table for each column. Table B: suffix blank compression c: prefix blank compression d: Use a number with a value of 0 to use one-bit storage.

E: if the value of an integer column has a small range, the column uses the smallest possible type for storage. For example, if all values are between 0 and 255, A bigint can be stored as a tinyint.

G: If a column has only one small set of possible values, the column type is converted to enum h: the combination of the preceding compression methods can be used for the column.

1.5.5 can process records with a fixed or dynamic length, but cannot process blob or text columns 1.5.6 and decompress with myisamchk

Mysql supports different index types, but the general type is isam. This is a B-tree index and can roughly calculate the size of the index file as (key_length + 4) * 0.67, the sum of all keys.

String indexes are empty compressed. If the first index is a string, it can compress the prefix. If the string column contains many trailing spaces or a varchar column with the full length of the primary path, empty compression will make the index file smaller. if many strings have the same prefix.

1.6 features of memory tables

The heap table in mysql uses the 100% dynamic hash for every occasional overflow and there are no deletion problems. you can only use an index in the heap table to access things using equations (usually using the '=' operator)

The disadvantages of heap tables are:

1.6.1 all heap tables to be used at the same time require sufficient additional memory

1.6.2 cannot be searched in one part of the Index

1.6.3 The next entry cannot be searched in sequence (that is, an order by index is used)

1.6.4mysql cannot calculate the approximate number of rows between two values. This is used by the optimizer to determine which index to use, but on the other hand, it does not even need disk seek.

Through the five steps described above, we can optimize the MySQL database. I hope everyone will be able to gain some benefits from the content mentioned above.