Methods of optimizing MySQL Database

Methods of optimizing MySQL Database

To create a large number of tables in a database, it is slow to perform 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 is really needed
　　
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 a record of where to specify a condition B: when performing a join, retrieving rows from other tables 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 can be 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
　　
(Ab>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 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 the 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 times times faster than the insert.
　　
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 by deleting all rows from a table is much faster than deleting most of the table