################################ file name: mysqloptimize, php ## topic: ## optimization of mysql database: _Shakespeare (network Knight) ## last modified: 2002--09--20 ## e-mail: shakespareshmily@sina.com.cn ########### ##################### Under the determination to start learning Oracle, use mysql already in short time, write these It is an explanation of a MySQL trip to yourself. The following is just a little personal experience in using the MySQL process, maybe there are many leaks and mistakes, please refer to it !!
First, in order to make a system faster, the most important part is the basic design, but some things are unable to pass over, such as the common bottlenecks. I can think of: 1: Disk seeking ability, High-speed hard drive (7200 rpm), theoretically seek 7,200 per second. This is no way to change, the optimized method is ---- use multiple hard drives, or distribute data to data. 2: Hard disk read and write Speed, this speed is very fast (limited to my own knowledge, only knowing the hundreds or even hundreds of MB per second). This is easier to solve - can read and write in parallel from multiple hard drives. 3: CPU.cpu processing Data in memory When there is a relatively small table, this is the most common constraint. 4: Memory restrictions. When the CPU needs to exceed data suitable for the CPU cache, the cache bandwidth becomes a bottleneck of memory. --- But now the memory is amazing, generally don't have this problem. 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 shell>
MySQLD-Help This command sound factory with all MySQL options and configurable variables. Output: Possible variables for option - set-variable (-o) Are: back_log current value: 5 // Require mysql available Number of connections. BACK_LOG points out how many connection requests in the time of MySQL are suspended accepted can be present in the stack CONNECT_TIMEOUT CURRENT VALUE: 5 // mysql server before answering the time before answering the time DELAYED_INSERT_TIMEOUT CURRENT Value: 200 // An INSERT Delayed Wait before INSERT Time DELAYED_INSERT_LIMIT CURRENT VALUE: 50 // INSERT Delayed processor will check if any select statement is not executed, if so, do these statements before delayed_Queue_size Current Value: 1000 / / Sign up for INSERT DELAYED FLUSH_TIME CURRENT VALUE: 0 // If set to 0, then each flush_time time, all tables are turned off in Interactive_timeout current value: 28800 // The server is connected to the ocean interconnection Waiting for the time join_buffer_size current value: 131072 // Use a buffer size key_buffer_size current value: 1048540 // to use the size of the buffer of the voice block, add it to better process index LOWER_CASE_TABLE_NAMES CURRENT VALUE: 0 // long_query_time Current Value: 10 // If a query is used in greater than this time, the SLOW_QUERIED count will increase Max_allowed_packet current value: 1048576 // Size Max_Connections Current Value: 300 // Allows the number of simultaneous connections MAX_CONNECT_ERRORS CURRENT VALUE: 10 // If there is more interrupt connections, further connections will be blocked, you can use flush hosts to solve the max_delayed_threads current Value. : // 15 can start processing of the number of insert delayed max_heap_table_size current value: 16777216 // max_join_size current value: 4294967295 // number of connections allowed to read the max_sort_length current value: 1024 // byte ordering used when text or blob number max_tmp_tables current value: 32 // number of simultaneously open a connection temporary table of max_write_lock_count current value: 4294967295 // specifies a value (usually small) to start mysqld, so that the locking net_buffer_length current value read occurs after a certain number of write lock : 16384 // Communication buffer size - reset to this size query_buffer_size current value: 0 // query time value: 0 // Query:
131072 // Each sequential scanning connection to the size of the buffer allocated for each table of its scan, Sort_Buffer Current Value: 2097116 / / Size-sized connection-assigned buffer Table_Cache Current Value: 64 / / For all Connecting the number of THREAD_CONCURRENCY CURRENT VALUE: 10 // TMP_TABLE_SIZE: 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 // The server is turned off before 3 A connection waiting time will be helpful to configure the above information according to your needs.
Third: 1: If you create a lot of table in a database, perform the operation open, close, create (table), will be very slow. 2: MySQL Using Memory A: Keyword Cache (Key_Buffer_Size) by all threads Sharing B: Each connection uses some specific thread space. One stack (default is 64K, variable thread_stack), a connection buffer (variable net_buffer_length) and a result buffer (NET_BUFFER_LENGTH). Connection buffer and results The buffer is dynamically expanded to max_allowed_packet.c: all threads share a base memory D: no memory image E: Each sequential scanned request assigns a read buffer (RECORD_BUFFER) F: all joints have completed and most coupons You can even do any temporary table. The most temporary table is a memory-based (HEAP) table G: Sorting request assigns a sort buffer and 2 temporary table H: All syntaxial analysis and calculations are completed in a local memory i: Each The index file is only opened once, and the data file opens once for each concurrently running thread. J: Table of each blob, a buffer dynamic is expanded to read the blob value K: all the table being used The table is saved in a buffer and is managed as a FIFO .l: A mysqladmin flush-tables command Turn off all the tables not using and tag all the table preparations in use at the end of the currently executive thread 3: mysql lock All locks in MySQL will not become a deadlock. Wirte lock: Mysql lock principle: A: If the table is not locked, lock; b Otherwise, put the lock request in the write lock queue in the write lock queue READ lock: mysql lock principle: a If the table is not locked, then lock; b Otherwise, put the lock request in the read lock queue, sometimes a lot of SELECT, INSERT operations in a table, can insert rows in a temporary table and occasionally use temporary tables Record the updated real table A: Use the low_priority property to a specific INSERT, UPDATE or DELETE lower priority B: max_write_lock_count specify a value (usually small) to start the mysqld, so that the READ lock is displayed after a certain number of Write locks. c: By using set sql_low_priority_updates = 1, you can specify all changes from a specific thread to complete d: specify a SelectE with high_priority: If you use INSERT .... SELECT ..., problems, use Myisam table - ---- Because it supports the SELECT and INSERT4 it supports concurrency, the most basic optimization is minimal of the space occupied on the hard disk. If the index is on the smallest column, then the index is also minimal. Implementation method: A: A: Use as little data type B: If possible, the declaration table is not null.c: If possible, if possible, if varchar (but speed will be affected) D: Each table should be Possible short main index E: Create an index of index f: If an index has a unique prefix on the first few characters, only indexing this prefix ---- MySQL supports the index G: If a table is often scanned, try to split it for more tables.
Step 4 1: The use of indexes, the importance of indexes is not said, the function does not say, just say how to do it. First, all MySQL indexes (Primary, Unique, Index) are stored in the B tree. Index main terms: A: Quickly find the log of WHERE specified condition B: When performing a connection, row from another table C: Find the max () and min () value for a specific index: If you sort or group in one available The front prefix, sort or grouping of the keys E: a query may be used to optimize the retrieval value without accessing the data file. If the column of some tables is a digital type and it is exactly a prefix for a column. For faster, the value can take out from the index tree 2: The execution of the query speed GRANT of the storage or update data will be slightly reduced efficiency. The function of mysql should be highly optimized. You can use Benchmark (loop_count, expression) to find out if there is a problem with the Query speed of SELECT: if you want a SELECT. . . WHERE. . . Faster, I can think of only an index. You can run Myisamchk - Analyze in a table to better optimize queries. You can use myisamchk - sort-index - sort-records = 1 to set up an index and data. 3: MySQL Optimization WHERE clause 3.1: Delete 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 = 53.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 = 63.4: Index usage constant expressions only calculate once 3.5: in a table, no one WHERE's count (*) retrieves information directly from the table 3.6: All constants reads out before any other tables in the query 3.7: The best connection combination for external joint table is to try all possible 3.8: if there is one Order By words and a different group by clause or ordery by or group by contains columns from the first table from the coupling, then create a temporary table 3.9: If you use SQL_SMALL_RESULT, then MSYQL uses a table in memory 3.10: The index of each table gives the query and uses an index that spans less than 30%. 3.11 Skip does not match the Having clause before each record output
4: Optimization Left Join in MySQL A LEFT JOIN BE A: Table B Rely on Table A B: Table A Depending on all tables with the Left Join Conditions (except B) C: All Left Join Conditions are shifted In the WHERE clause D: All coup optimization, except that a table is always read after all his relying on the table. If there is a loop dependent, an error will occur, and all standard WHERE Optimization f: If there is a line in A, there is no clause in B, but there is no matching Left Join condition, then, it is generated in B. All lines G: If you use Left Join to find rows that do not exist in some tables and have column_name is null tests in the WHERE section (Column_name is a Not Null column). Then, MySQL will stop looking for 5: Optimization Limita: Optimize Limita: If you need to scan the entire table with limit, when MySQL needs to scan the entire table, its effect is equivalent to Index B: If you use Limit # with order by, MySQL If you find the # line, you will end sorting, not sorting the positive table C: When combined with Limit # and Distinct, mysql finds the # line, will stop D: As long as MySQL has already sent the first # to the customer, MySQL will give up the query E: Limit 0 will always return an empty collection. f: The size of the temporary table uses the limit # calculation how much space needs to solve the query 6: Optimize INSERT insertion 1 constructed: A: Connection (3) B: Send query to the server (2) C: Analyze query (2) D: Insert record (1 * Record size) E: Insert Index (1 * Index) F: Close (1) The above number can be viewed and the total time proportionally improves the insert speed: 6.1: If you connect from one connection at the same time Insert a lot of lines, use multiple values of INSERT, this is more fast than using multiple statements 6.2: If you insert a lot from different connections, use the IISAM, with Myisam, if there is no deleted line in the table, Inserting row 6.4 while SELECT: S is running 6.4: When loading a table from a text file, use Load Data Infile. This is usually 20 times faster than INSERT. 6.5: You can lock the table and then insert - the main speed difference is completed after all INSERT statements are completed, and the index buffer is only stored once. It is generally saved as many times as many times with different INSERT statements. If you can insert all rows with a single statement, you don't need it. Lock also reduces the overall time of connection. But the maximum wait time for some threads will rise. For example: Thread 1 Does 1000 INSERTSTHREAD 2, 3 and 4 does 1 INSERT THREAD 5 DOES 1000 INSERTS If you do not lock,
2, 3, 4 will be completed before 1 and 5. If locking, 2, 3, 4 will be completed after 1 and 5. But the overall time should be 40%. Because INSERT, UPDATE, DELETE operation is very fast in MySQL, and it will achieve better overall performance by chaining something that is more than about 5 consecutive plugs or update lines. If you do a lot of lines, you can do a Lock Tables, which occasionally make a Unlock Tables (approximately 1000 rows) to allow additional thread access tables. This will still lead to good performance. Load Data Infile is still very fast to load data. In order to get some faster speeds, the keyword buffer is expanded to Load Data Infile and INSERT. 7 Optimize Update's speed It is dependent on the size of the updated data and the number of updated indexs to make Update faster another method to postpone modification, and then do a lot of modifications. If you lock the table, a lot of modifications to do a row is more than once to make a fast 8 optimized delete speed to delete a record time and the number of indexes is proportional. In order to delete a record faster, you can increase the size of the index cache from a table to remove all rows than to delete most of this table.
Step 5 1: Select a Table Type 1.1 Static Myisam This format is the simplest and safe format, which is the fastest in the disk format. The speed comes from the difficulty of data that can be found on the disk. When it is locked with an index and static format, it is very simple, just multiplying the line length. And when scanning a table, it is easy to read a constant record per time with a disk read. Security comes from the computer down when a static Myisam file is written, Myisamchk is easy to indicate where the start and end each line, so it usually can recover all records, except for some of the records written. All indexes in MySQL are often rebuilt 1.2 Dynamic Myisam This format must have a header must have a header indicating how long it is. When a record is growing during the change, it can end over one position. Can use Optimize Tablename or Myisamchk to organize a table. If there is a static data such as some VARCHAR or BLOB columns like some varchar or blob columns, the dynamic column is moved into another table to avoid fragmentation. 1.2.1 Compress Myisam, generates 1.2.2 memory with optional MyisamPack tools. This format is useful for small / medium-sized tables. To copy / create a common lookup table to the Ocean HEAP table may speed up multiple table links, which may have a triple time with the same data. select tablename.a, tablename2.a from tablename, tablanem2, tablename3 where tablaneme.a = tablename2.a and tablename2.a = tablename3.a and tablename2.c = 0;! In order to accelerate it, can be created in the junction tablename2 and tablename3 A temporary table because it looks out with the same column (TableName1.a). 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. That is not included in the table without including varchar, blob, and text columns 1.3.2 All Char, Numeric and Decimal column are filled to column width 1.3.3 Very fast 1.3.4 Easy buffer 1.3.5 Easy to rebuild after DOWN, because records Fixed position 1.3.6 Do not have to be reorganized (with Myisamchk) unless a huge record is deleted and optimized storage size 1.3.7 usually requires more storage space than dynamic tables
1.4 Dynamic Table Features 1.4.1 If the table contains any varchar, blob, text column, using this format 1.4.2 All string columns are dynamic 1.4.3 for each record. One bit. 1.4.4 Usually more disk space is required for the minimum length 1.4.5 Each record simply uses the required space, if a record is large, it is divided into many segments, which causes the record fragment 1.4. 6 If you update the line with more than a line length, the line is segmented. 1.4.7 After the system DOWN is lost, it is not good to rebuild, because a record can be a multi-segment 1.4.8 on the dynamic size record length is 3 (Number of Column 7) / 8 (Number of Char columns) Packed Size of Numeric Columns Length of strings (Number of Null Column 7) / 8 There is 6 bytes of punishment for each connection. Whenever the change causes the record, there is a dynamic record being connected. Each new connection has at least 20 bytes, so the next change will be in the same connection. If not, there will be another connection. How many connections can be used with Myisamchk - vicious checks. All connections can be deleted with Myisamchk -r.
1.5 Characteristics of the compressed table 1.5.1 A read-only table created with myisampack utility. 1.5.2 Decompression Codes exist in all MySQL distributions to make a connection without MyisamPack can also read table 1.5.3 occupied by MyisamPack, which is small disk space 1.5.4 Each record is separately compressed. A recorded head is a fixed length (1 ~~ 3 bytes) depending on the maximum record of the table. Each column is compressed in different ways. Some commonly used compression types are: A: Typically there is a different Hafman watch B: suffix blank compression C: prefix blank compression d: Use 1 bit storage E: If the value of the integer column A small range, the column uses the minimum possible type to store. For example, if all the values are between 0 and 255, a Bigint can be used as a Tinyint storage G: If only a small set of possible values, the column type is converted to ENUM H: Column can use the combination of compression methods above. 1.5.5 can handle the logging of the length or dynamic length, can not process the blob or Text column 1.5.6 can use Myisamchk to decompress MySQL to support different index types, but the general type is ISAM, this is a B tree index and The index file is calculated as (key_length 4) * 0.67, the sum of the keys on all keys. The string index is blank compressed. If the first index is a string, it can block the compressed prefix if the character is column with a lot of tail blank or a headquartered VARCHAR column, blank compression makes the index file smaller. If many strings have the same prefix. 1.6 Memory Table Features Mysql's HEAP table uses 100% dynamic hashing that overflows and has no problem with deletion. It can only be used by using an index in a stack table (usually using the '=' operator) stack table is: 1.6.1 All stacks you want to use at the same time need enough additional memory 1.6 .2 Can't search at a part of the index 1.6.3 You cannot search for the next entry in order (ie, use this index to make an order by) 1.6.4Mysql cannot calculate how many rows between 2 values. This is used by the optimizer to determine which index is used, but in another aspect, no disk seek (... to be continued ...)
