MySQL optimization
Determined to start learning Oracle, with MySQL has been a short time, today write down this is a MySQL trip to their own an account of it. Here are just a few of my personal experience in the use of MySQL, perhaps there are many flaws and errors, please correct me!!
First, in order to make a system faster, the most important part is the basic design, but some things are insurmountable in the existing situation, such as the system common bottlenecks.
What I can think of:
1: The disk seek ability, with high speed hard disk (7200 RPM), theoretically seek 7,200 times per second. There is no way to change this, the optimization method is to----with more than one hard disk, or to spread the data storage.
2: Hard disk read and write speed, this speed is very fast (limited to my knowledge limit, only know at dozens of or even hundreds MB per second). This is easier to solve--read and write concurrently from multiple hard drives.
3:CPU.CPU handles data in memory, which is the most common limiting factor when there are tables with relatively small memory.
4: Memory limit. When the CPU needs to exceed the data that is appropriate for the CPU cache, the bandwidth of the cache becomes a bottleneck in memory---but now the memory is staggering, and this problem is generally not the case.
Step Two:
(I am using the Linux platform of the school website (Linux ADVX). Mandrakesoft.com 2.4.3-19mdk))
1: Tuning Server parameters
With 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 MySQL to have a number of connections. Back_log points out how many connection requests can be present in the stack when MySQL pauses to accept the connection
Connect_timeout current Value:5//mysql server waits for a connection before responding with bad handshake (not translated)
Delayed_insert_timeout current value:200//an insert delayed the time to wait for insert before terminating
Delayed_insert_limit current VALUE:50//insert delayed processor will check if any of the SELECT statements are not executed, and if so, execute these statements before proceeding
Delayed_queue_size current value:1000//How many teams are allocated for the 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//The time the server waits on a foreign interactive connection before closing it
Join_buffer_size current value:131072//buffer size with all connections
Key_buffer_size current value:1048540//the size of the buffer of the term index block, which increases it to better handle the index
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 is more than that number of interrupt connections, further connections will be blocked and the flush hosts can be used 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 to use when sorting blobs or text
Max_tmp_tables current VALUE:32//The number of temporary tables that a connection opens at the same time
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//the size of the communication buffer--reset to that size at query time
Query_buffer_size current value:0//buffer size at query
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 sort of connection
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 shutting it down 3
Configuring the above information according to your needs will help you.
Third:
1: If you create a large number of tables in a database, then performing open, close, create (table) operations will be slow.
2:mysql using Memory
A: Keyword buffers (key_buffer_size) are shared by all threads
B: Each connection uses some specific thread space. A stack (default is 64k, variable thread_stack), a connection buffer (variable net_buffer_length), and a result buffer (net_buffer_length). Under certain circumstances, The connection buffer and the result buffer are dynamically extended to Max_allowed_packet.
C: All threads share a single base memory
D: no memory innuendo
E: Each request to do a sequential scan allocates a read buffer (record_buffer)
F: All junctions 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 request assigns a sort buffer and 2 temporary tables
H: All parsing and computation is done in a local memory
I: Each index file is opened only once, and the data file is opened once for each concurrently running thread
J: For each BLOB column of the table, a buffer is dynamically expanded to read into the BLOB value
K: The table processor for all the tables being used is stored in a buffer and is managed as a FIFO.
L: A mysqladmin flush-tables command closes all unused tables and marks all tables used at the end of the currently executing thread ready to close
3:mysql Locking Table
All locks in MySQL do not become deadlocks.
Wirte Lock:
MySQL lock principle: A: If the table is not locked, then lock; b Otherwise, put the lock request into the write lock queue
Read Lock:
MySQL lock principle: A: If the table is not locked, then lock; b Otherwise, put the lock request into the Read lock queue
Sometimes there are many select,insert operations in a table, you can insert rows in a temporary table and occasionally update a real table with the records of a temporary table
A: Use the low_priority attribute to give a specific insert,update or delete a lower priority
B:MAX_WRITE_LOCK_COUNT specifies a value (usually very small) to start mysqld so that a read lock occurs 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 .... The problem occurs, using the MyISAM table------because it supports concurrent select and insert
4: The most basic optimization is to make the data occupy the smallest amount of space on the hard disk. If the index is on the smallest column, 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 a data type that becomes, such as varchar (but speed will be affected by some)
D: Each table should have the shortest possible primary index
E: Create an index that you do need
F: If an index has a unique prefix on the first few characters, then just index the prefix----MySQL supports the index on a part of a character column
G: If a table is often scanned, then try to split it into more tables
Fourth Step
1: Index of the use of the importance of the index is not said, the function is not said, just how to do.
First, make clear that all MySQL indexes (primary,unique,index) are stored in the B-tree. Index Main terms:
A: Quickly find the record where the condition is specified
B: When a junction is executed, rows are retrieved from other tables
C: Find Max () and min () values for a specific index column
D: Sort or Group A table if sorting or grouping is prefixed to the front of an available key
E: A query may be used to optimize the retrieval value without accessing the data file. If the columns of some tables are numeric and are exactly the prefix of a column, for faster, the value can be removed from the index tree
2: Query speed for storing or updating data
Grant's execution will be slightly less efficient.
The MySQL function 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 have a select ... where ... Faster, the only thing I can think of is to build an index. You can run myisamchk--analyze on a table to better refine the query. You can use Myisamchk--sort-index--sort-records=1 to set an index and data to be sorted by 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>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: constant expression used by index is evaluated only once
3.5: In a table, there is not a where count (*) to retrieve information directly from the table
3.6: All constants of the table are read in the query before any other table
3.7: External junction table The best coupling combination is trying to find all the possibilities
3.8: If there is an order by sentence and a different GROUP BY clause or an order BY or group by contains a column that is not the first table from the junction, create a temporary table
3.9: If Sql_small_result is used, then MSYQL uses a table in memory
3.10: Index Each table to the query and use an index that spans less than 30% rows.
3.11 Skipping rows that do not match the HAVING clause before each record output
4: Optimize LEFT Join
In MySQL a LEFT JOIN B is implemented as follows
A: Table B depends on table A
B: Table A relies 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 the coupling optimizations except that a table is always read after all of the tables he relies on. If there is a cyclic dependency, then an error occurs
E: Do all the standard where optimization
F: If there is a row in a that matches the WHERE clause, but there is no matching left join condition in B, all rows that are set to NULL are generated in B
G: If you use a LEFT join to find rows that do not exist in some tables and have a column_name is null test in the Where section (column_name is a NOT NULL column). So, after it has found a row that matches the left join condition, MySQL will stop looking after more rows
5: Optimize limit
A: If you select only one row with limit, when MySQL needs to scan the entire table, it acts as an index
B: If you use limit# and order by,mysql if the # # line is found, the sort will end without sorting a table
C: When combining 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 temp table uses limit# to calculate how much space is needed to resolve the query
6: Optimize insert
Inserting a record is composed 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 Index (1* Index)
F: Off (1)
These figures can be seen as proportional to the total time.
Some ways to improve insertion speed:
6.1: If you insert many rows from a connection at the same time, insert with multiple values, which is faster than using multiple statements
6.2: If you insert many rows from different connections, use the Insert delayed statement faster
6.3: With MyISAM, if there are no deleted rows in the table, you can insert the row while the select:s is running
6.4: When loading a table from a text file, use load data infile. This is usually 20 faster than insert.
Times
6.5: The table can be locked and then inserted--the main speed difference is that after all INSERT statements are completed, the index buffers are only deposited to the hard disk once. It is generally faster to deposit as many times as there are different insert statements. If you can insert all the rows with a single statement, the lock is not required. Locking also reduces the overall time of the connection. However, the maximum wait time for some threads will rise. For example:
Thread 1 does inserts
Thread 2,3 and 4 does 1 insert
Thread 5 does inserts
If you do not use locks, 2,3,4 will be completed before 1 and 5. If locking is used, 2,3,4 will probably be completed after 1 and 5. But the overall time should be 40% faster. Because the insert,update,delete operation is fast in MySQL, it will achieve better overall performance by locking in more than about 5 successive insertions or updates of a row. If you do a lot of line insertions, you can do a lock tables, and occasionally do a unlock tables (about every 1000 lines) to allow additional threads to access the table. 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 Update speed
Its speed depends on the size of the data being updated and the number of indexes being updated
Another way to make update faster is to postpone the modification, and then make a lot of changes on one line. If you lock a table, do a lot of changes in one row than do a quick
8 Optimize 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 the table
Fifth Step
1: Select a table type
1.1 Static MyISAM
This format is the simplest and safest format, and it is the fastest in the disk format. Speed comes from the ease with which data can be found on disk. When locking something that has an index and a static format, it is simple, just the line length multiplied by the quantity. And when scanning a table, it is easy to read a constant number of records with a disk read every time. Security comes from If a static MyISAM file is written to cause the computer to drop, Myisamchk can easily indicate where each line starts and ends, so it is usually able to reclaim all records except those that are partially written. 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 grows 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/changed 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, generated with optional myisampack tools
1.2.2 Memory
This format is useful for small/medium tables. copying/Creating a common lookup table to the foreign heap may speed up multiple table junctions, and using 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 the 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 Characteristics of static tables
1.3.1 the default format. Used when the table does not contain the Varchar,blob,text column
1.3.2 all the char,numeric and decimal columns to the column width
1.3.3 very fast.
1.3.4 Easy to Buffer
1.3.5 easy to rebuild after down because the record is in a fixed position
1.3.6 do not have to be re-organized (with MYISAMCHK) unless a huge amount of records is deleted and the storage size is optimized
1.3.7 usually requires more storage space than dynamic tables
1.4 Features of dynamic tables
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 a bit before each record.
1.4.4 usually requires more disk space than a fixed-length table
1.4.5 Each record uses only the space required, and if a record becomes large, it is divided into many segments as needed, which results in a record fragmentation
1.4.6 If the row is updated with information over the length of the line, the row is segmented.
1.4.7 is not good for rebuilding a table after the system is down, because a record can be a multi-segment
1.4.8 the expected line length for dynamic dimension Records is (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 change may be in the same connection. If not, there will be another connection. You can use Myisamchk-viciously to check how many connections there are. All connections can be deleted with Myisamchk-r.
1.5 Characteristics of the compression table
1.5.11 read-only tables made with the Myisampack utility.
1.5.2 Decompression code exists in all MySQL distributions so that no myisampack connections can read tables compressed with Myisampack
1.5.3 occupies a small amount of disk space
1.5.4 Each record is individually compressed. The header of a record is a fixed length (1~~3 bytes) which depends on the maximum record of the table. Each column is compressed in a different way. Some of the commonly used compression types are:
A: There is usually a different Huffman table for each column.
B: Suffix blank compression
C: Prefix blank compression
D: Use 1-bit storage with a number of value 0
E: If the value of an integer column has a small range, the column is stored using 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 the enum
H: Columns can use a combination of the above compression methods
1.5.5 can handle fixed or dynamic length records, to not handle blobs or text columns
1.5.6 can be decompressed with Myisamchk
MySQL can support different index types, but the general type is ISAM, which is a B-tree index and can roughly calculate the size of the index file for (key_length+4) *0.67, the sum of all the keys.
The string index is blank compressed. If the first index is a string, it can compress the prefix if there are many trailing blanks in the string column or a varchar column that can corridor the full length of a headquarters, blank compression makes the index file smaller. If many strings have the same prefix.
1.6 Features of memory tables
The heap table inside MySQL uses a 100% dynamic hash every even overflow and has no problem with the deletion. You can only access things by using an equation in the heap table (usually with the ' = ' operator)
The disadvantages of the heap table are:
1.6.1 All heap tables you want to use at the same time require enough extra memory
1.6.2 cannot search in one part of an index
1.6.3 cannot search for the next entry sequentially (that is, use this index to make an order by)
1.6.4mysql cannot figure out how many rows are between 2 values. This is used by the optimizer to decide which index to use, but it does not even require a disk seek in another way 
