MySQL Database optimization

mysql| Data | database | Optimization decided to begin to learn Oracle, with MySQL has not been a short time, write these today is a MySQL trip to their own account of it. The following is just my personal experience in the process of using MySQL, There may be many flaws and mistakes, please correct me!!



First, in order to make a system faster, the most important part is the basic design, but some things are not insurmountable in the current situation, such as the system common bottlenecks.

All I can think of:

1: Disk seek ability to high speed hard drive (7200 rpm), theoretically seek 7,200 times per second. There is no way to change, the optimization method is to----with multiple hard drives, or to spread data storage.

2: Read and write speed of the hard disk, this speed is very fast (limited by my knowledge, only know in dozens of or even hundred MB per second). This is easier to solve--you can read and write in parallel from multiple hard disks.

3:CPU.CPU processes the data in memory, which is the most common limiting factor when there is a table with a smaller relative memory.

4: Memory limitations. When the CPU needs to exceed the CPU-cached data, the bandwidth of the cache is a bottleneck in memory---but now the memory is so alarming that it doesn't usually happen.

Step Two:

(I am using the Linux platform of the school website (Linux ADVX). Mandrakesoft.com 2.4.3-19mdk))

1: Adjust the server parameters

Use shell>mysqld-help This command sound factory a table of all MySQL options and configurable variables. Output The following information:

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

Back_log current Value:5//requires the number of connections that MySQL can have. Back_log indicates how many connection requests can be in the stack when MySQL pauses to accept connections

Connect_timeout current Value:5//mysql server waits for a connection before it is answered with bad handshake (poor translation)

Delayed_insert_timeout current value:200//An insert delayed waits for an insert before terminating

Delayed_insert_limit current VALUE:50//insert delayed processor will check that any SELECT statements are not executed and, if so, execute them before continuing

Delayed_queue_size current value:1000//How many teams are allocated for insert delayed

Flush_time Current value:0//If set to non 0, then every flush_time time, all tables are closed

Interactive_timeout current value:28800//server wait time on Ocean Interactive connection before closing it

Join_buffer_size current value:131072//buffer size with all connections

Key_buffer_size current value:1048540//term the size of the buffer of the index block, increasing it to handle the index better

Lower_case_table_names Current value:0//

Long_query_time Current Value:10//If a query takes longer 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 simultaneous connections allowed

Max_connect_errors Current VALUE:10//If there are more than this number of interrupt connections, will block further connections, you can use the flush hosts to resolve

Max_delayed_threads current VALUE:15//The number of processing insert delayed that can be started

Max_heap_table_size Current value:16777216//

Max_join_size current value:4294967295//number of connections allowed to read

Max_sort_length current value:1024//number of bytes used in the sort blob or text

Max_tmp_tables current VALUE:32//number of temporary tables open at the same time for a connection

Max_write_lock_count current value:4294967295//Specify a value (usually very small) to start the mysqld so that a read lock appears after a certain number of write locks

Net_buffer_length current value:16384//communication buffer size-reset to that size at query time

Query_buffer_size current value:0//query-time buffer size

Record_buffer current value:131072//the size of the buffer allocated for each table scanned by the connection for each sequential scan

Sort_buffer current value:2097116//the size of the buffer allocated for each of the sorted connections

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

Thread_concurrency Current Value:10//

Tmp_table_size current value:1048576//Temp Table size

Thread_stack current value:131072//size of each thread

Wait_timeout current value:28800//server waits on a connection before it closes 3



Configuring the above information according to your own needs will help you.



Third:

1: If you create a large number of tables in a database, it will be slow to open, close, and create (table) operations.

2:mysql using Memory

A: the keyword cache (key_buffer_size) is shared by all threads

B: Each connection uses some specific thread space. A stack (default 64k, variable thread_stack), a connection buffer (variable net_buffer_length), and a result buffer (net_buffer_length). In specific cases, The connection buffer and the result buffer are dynamically expanded to max_allowed_packet.

C: All threads share a base memory

D: no memory innuendo

E: Each request for sequential scans is assigned a read buffer (record_buffer)

F: All joins are completed once and most connections can be done without a temporary table. The most temporary table is a memory based (heap) table

G: Sort requests assign a sort buffer and 2 temporary tables

H: All parsing and calculations are done in one local memory

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

J: For each BLOB column table, a buffer is dynamically enlarged to read the BLOB value

K: Table processors for all the tables being used are stored in a buffer and managed as a FIFO.

L: A mysqladmin flush-tables command closes all tables that are not in use and marks all tables in use at the end of the currently executing thread ready to close

3:mysql Lock Table

All locks in MySQL will not become deadlocks.

Wirte Lock:

MySQL locking principle: A: If the table is not locked, then lock; b Otherwise, put the lock request into the write lock queue

Read Lock:

MySQL locking principle: A: If the table is not locked, then lock; b Otherwise, put the lock request into the Read lock queue



Sometimes you do a lot of select,insert in a table, you can insert rows in a temporary table, and occasionally update real tables with records from temporary tables.

A: Use the Low_priority property to give a particular insert,update or delete a lower priority

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

C: By using Set Sql_low_priority_updates=1, you can specify from a specific thread that all changes should be done by a lower priority

D: Specify a Select with High_priority

E: If you use Insert....select .... A problem occurs, using the MyISAM table------because it supports concurrent select and insert

4: The most basic optimization is to make the data on the hard disk occupy the smallest space. If the index is on the smallest column, then the index is minimal. Implementation method:

A: Use the smallest possible data type

B: If possible, the Declaration table column is not NULL.

C: If it is possible to use the data type that becomes, such as varchar (but the speed will be affected by some)

D: Each table should have the shortest possible primary index

E: Create an index that you really need

F: If an index has a unique prefix on the first few characters, then simply index the prefix----MySQL supports indexing on part of a character column

G: If a table is frequently scanned, try splitting it into more tables





Fourth Step

1: The use of the index, the importance of the index is not said, the function is not said, just say how to do.

The first thing to make clear is that all MySQL indexes (primary,unique,index) are stored in the B-tree. Index Main terms:

A: Quickly find the record where you specify the condition

B: Retrieving rows from other tables when performing a join

C: Find Max () and min () values for specific indexed columns

D: If sorting or grouping is preceded by the prefix of a available key, sort or group a table

E: A query may be used to optimize the retrieval of values without accessing the data file. If the columns of some tables are numeric and are exactly the prefix of a column, for faster values can be removed from the index tree

2: Query speed for storing or updating data

Grant's execution is slightly less efficient.

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

Query speed for select: if you want to make a select ... where ... Faster, I can think of only indexing. You can run Myisamchk--analyze on one table to better optimize the query. You can use Myisamchk--sort-index--sort-records=1 to sort an index and data with an index.

3:mysql optimization 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: Using Constants

(A<b and B=c) and a=100 > B>5 and B=c and a=5

3.3: Delete 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 evaluates only once

3.5: In a table, none of the Where count (*) retrieves information directly from the table

3.6: Tables of all constants read before any other table in the query

3.7: The best coupling combination of the external coupling table is to have tried all possibilities to find

3.8: Create a temporary table if there is an order by word and a different GROUP BY clause or a column by or group by containing the first table that is not from the join

3.9: If Sql_small_result is used, then MSYQL uses a table in memory

3.10: Each table is indexed to the query and uses an index that spans fewer than 30% rows.

3.11 Skip rows that do not match the HAVING clause before the output of each record



4: Optimize LEFT Join

In MySQL, a LEFT JOIN B is implemented in the following way

A: Table B depends on table A

B: Table A relies on all tables with the LEFT join condition (except B)

C: All left join conditions are moved to the WHERE clause

D: Perform all the join optimizations except that a table is always read after all the tables he relies on. If there is a circular dependency, then an error occurs

E: All standard where optimizations are performed

F: If there is a row in a that matches the WHERE clause, but there is no matching left join condition in B, then all rows that are set to NULL in B

G: If you use a LEFT join to find rows that do not exist in some tables and there is a column_name is null test in the Where section (column_name is a NOT NULL column). Well, MySQL will stop looking after more rows after it has found a row that matches the left join condition

5: Optimization Limit

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

B: If you use limit# and order By,mysql if you find line #, you end the sort, not the table

C: When combined with limit# and distinct, MySQL will stop if it finds the line #

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

E:limit 0 will always return an empty collection very quickly.

F: The size of the temporary table how much space is needed to solve the query using limit# calculation

6: Optimize insert

Inserting a record is composed of the following:

A: Connection (3)

B: Send a query to the server (2)

C: Analysis of the query (2)

D: Inserting records (1* record size)

E: Inserting an index (1* index)

F: Off (1)

The above figures can be seen as proportional to the total time

Some ways to improve insertion speed:

6.1: If you insert many rows from one connection at the same time, insert with multiple values, which is faster than using multiple statements

6.2: If you insert many rows from a different connection, use the Insert delayed statement faster

6.3: With MyISAM, if there are no deleted rows in the table, you can insert rows while select:s is running

6.4: When loading a table from a text file, use the load data infile. This is usually 20 faster than the insert.

Times

6.5: The table can be locked and inserted--the main speed difference is that after all INSERT statements are completed, the index buffer is only saved to the hard disk once. It is generally faster to save as many times as you have a different INSERT statement. If you can insert all rows with a single statement, the lock is not required. Locking also lowers the overall time of the connection. But the maximum wait 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 you do not use locks, the 2,3,4 will be completed before 1 and 5. If the lock is used, the 2,3,4 will probably be completed after 1 and 5. But the overall time should be almost 40%. Because the insert,update,delete operation is quick in MySQL, better overall performance is achieved by locking more than about 5 consecutive inserts or updates of a row. If you do a lot of inserts, you can do a lock table and occasionally do a unlock tables (about every 1000 lines) to allow additional threads to access the tables. This will still result in good performance. The load data infile is still very fast on the load.

To get some faster speed on the load data infile and INSERT, expand the keyword buffer.

7 Optimizing the speed of update

Its speed depends on the size of the data being updated and the number of indexes being updated

Another way to make the update faster is to postpone the modification and then make a lot of changes on one line. If you lock the table, do a lot of the changes in one line faster than once

8 Optimizing Delete Speed

The time to delete a record is proportional to the number of indexes. For faster deletion of records, you can increase the size of the index cache

Deleting all rows from a table is much faster than deleting most of this table



Fifth Step

1: Select a table type

1.1 Static MyISAM

This format is the simplest and safest format, and is the fastest in the disk format. Speed comes from the ease with which data can be found on disk. When the lock has an index and the static format something is, it's very simple, just the line length multiplied by the quantity. And when you scan a table, it's easy to read a constant number of records at a time with disk reads. Security comes from if the computer is down when writing a static MyISAM file, Myisamchk can easily point out where each row starts and ends, so it usually reclaims all records, except for a partially written record. All indexes in MySQL are always rebuilt

1.2 Dynamic MyISAM

Each line of this format must have a header indicating how long it is. When a record becomes longer during a change, it can end in more than one position. can use optimize tablename or myisamchk to organize a table. If you have static data that is accessed/altered in the same table as some varchar or BLOB columns, move the dynamic column into another table to avoid fragmentation.

1.2.1 Compression MyISAM, using optional myisampack tools to generate

1.2.2 Memory

This format is useful for small/medium tables. For copying/Creating a common lookup table to the Ocean heap table it is possible to speed up multiple table joins, with the same data may be 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 speed it up, you can create a temporary table with tablename2 and Tablename3 joins, because the same column (tablename1.a) is used to find 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 Features of static tables

1.3.1 the default format. Used when a table does not contain Varchar,blob,text columns

1.3.2 all char,numeric and decimal columns to column widths

1.3.3 very fast.

1.3.4 Easy to Buffer

1.3.5 easily rebuilt after down because the record is in a fixed position

1.3.6 do not have to be organized (with MYISAMCHK) unless a huge amount of records is deleted and the storage size is optimized

1.3.7 typically requires more storage than dynamic tables



1.4 Characteristics of dynamic table

1.4.1 If the table contains any Varchar,blob,text columns, use this format

1.4.2 All string columns are dynamic

1.4.3 each record with a bit before it is placed.

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

1.4.5 Each record uses only the space needed, and if a record becomes large, it is divided into segments as needed, which results in a record fragment

1.4.6 If you update the row with information that exceeds the length of the line, the row is segmented.

1.4.7 is not easy to rebuild after the system down, because one record can be multiple segments

1.4.8 the expected line length for a dynamic dimension record 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

There is a penalty of 6 bytes for each connection. Whenever a change causes a record to become larger, a dynamic record is connected. Each new connection has at least 20 bytes, so the next variable may be in the same connection. If not, there will be another connection. can use Myisamchk-malicious check for how many connections. All connections can be deleted with Myisamchk-r.



1.5 Characteristics of the compressed table

1.5.11 read-only tables made using the Myisampack utility.

1.5.2 uncompressed code exists in all MySQL distributions so that connections without myisampack can also read tables with Myisampack compression

1.5.3 occupies a small disk space

1.5.4 Each record is compressed individually. The header of a record is a fixed-length (1~~3 byte) depending on the maximum record of the table. Each column is compressed in a different way. Some common types of compression are:

A: There's usually a different Huffman table for each column.

B: Suffix blank compression

C: Prefix blank compression

D: Use 1-bit storage for a number with a value of 0

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

G: If the column has only a small set of possible values, the column type is converted to an enum

H: Columns can use a combination of the above compression methods

1.5.5 can process fixed or dynamic length records to not handle blobs or text columns

1.5.6 can decompress with Myisamchk

MySQL can support different index types, but the general type is ISAM, which is a B-tree index and can be roughly computed for index file size (key_length+4) *0.67, sum on all keys.

The string index is blank compressed. If the first index is a string, it can compress the prefix if the string column has many trailing blanks or a varchar column with a headquarters-length, blank compression makes the index file smaller. If many strings have the same prefix.

1.6 Features of the memory table

The heap table inside MySQL uses a 100% dynamic hash with every occasional overflow and there is no problem with the deletion. You can access things using an equation only by using one of the cables in the heap table (usually the ' = ' operator)

The disadvantages of the heap table are:

1.6.1 need enough extra memory for all the heap tables you want to use at the same time

1.6.2 cannot search in one part of the index

1.6.3 cannot search for the next item sequentially (that is, use this index to make an order by)

1.6.4mysql can't figure out how many rows there are between 2 values. This is used by the optimizer to determine which index to use, but it does not even require disk finder in another respect