MySQL Common optimizations

Optimizing GROUP BY statementsBy default, MySQL sorts all group by Col1,col2, ..... The method of querying is like specifying an order by Col1,col2 in a query .... If you explicitly include an ORDER BY clause that contains the same column, MySQL can optimize it without slowing down, although it is still sorted. If the query includes group by but you want to avoid the consumption of sort results, you can specify order by NULL to prohibit sorting. optimizing the ORDER BY statementIn some cases, MySQL can use an index to satisfy the ORDER BY clause without requiring additional sorting. The Where condition and order by use the same index, and the order by IS in the same sequence as the index, and the order by field is ascending or descending. Optimizing INSERT StatementsIf you insert many rows from the same customer at the same time, use the INSERT statement for more than one value table. This is faster than using separate INSERT statements (several times in some cases). Insert into test values (1,3), (1,4) ... If you insert many rows from different customers, you can get a higher speed by using the Insert DELAYED statement. The meaning of Delayed is to let the INSERT statement execute immediately, in fact, the data are placed in the memory queue, and there is no real write to the disk, this is more than each statement is inserted faster, low_priority just the opposite, after all other users read and write to the table is completed before inserting. Keep index files and data files on separate disks (using the options in the Build table); If you do bulk inserts, you can increase the Bulk_insert_buffer_size variable value to increase the speed, but this can only be used on MyISAM tables when loading a table from a text file, Use load DATA INFILE. This is usually 20 times times faster than using many INSERT statements, replacing inserts with the Replace statement according to the application, and ignoring duplicate records using the Ignore keyword, depending on the application. Insert data in large batches1. For MyISAM types of tables, you can quickly import large amounts of data in the following ways.

ALTER table Tblname DISABLE keys;loading the dataalter TABLE tblname ENABLE KEYS;

These two commands are used to open or close an update for a non-unique index of the MyISAM table. When importing large amounts of data into a non-empty MyISAM table, you can increase the efficiency of the import by setting these two commands. For importing large amounts of data into an empty MyISAM table, the default is to import the data first and then create the index, so you don't have to set it up. 2. For InnoDB types of tables, this approach does not improve the efficiency of importing data. For InnoDB types of tables, there are several ways we can improve the efficiency of the import: A. Because tables of the InnoDB type are saved in the order of the primary key, the imported data is arranged in the order of the primary key, which effectively improves the efficiency of the imported data. If the InnoDB table does not have a primary key, an internal column is created by default as the primary key, so if you can create a primary key for the table, you can use this advantage to improve the efficiency of importing data.
B. Perform set unique_checks=0 before importing data, turn off uniqueness check, execute Setunique_checks=1 after import, restore uniqueness check, improve efficiency of import. C. If the application uses autocommit, it is recommended to execute set autocommit=0 before import, turn off Autocommit, execute set autocommit=1 after import, turn on autocommit, and improve the efficiency of the import. Optimization of QueriesRead the main can set Low_priority_updates=1, write the priority to lower, tell MySQL to deal with the read and seek for the query cache optimization of your query

Most MySQL servers have query caching turned on. This is one of the most effective ways to improve sex, and this is handled by the MySQL database engine. When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to manipulate the table directly to access the cached results.

The main problem here is that this is a very easy thing to ignore for programmers. Because, some of our query statements will let MySQL not use the cache. Take a look at the following example:

The query cache does not open $r = mysql_query ("Select username from user WHERE signup_date >= curdate ()");  

splitting a large DELETE or INSERT statement

If you need to perform a large DELETE or INSERT query on an online website, you need to be very careful to avoid your actions to keep your entire site from stopping accordingly. Because these two operations will lock the table, the table is locked, the other operations are not in.

Apache will have a lot of child processes or threads. So, it works quite efficiently, and our servers don't want to have too many child processes, threads and database links, which is a huge amount of server resources, especially memory.

If you lock your watch for a period of time, say 30 seconds, for a site with a high level of access, the 30-second cumulative number of access processes/threads, database links, and open files may not only allow you to park the Web service crash, but may also leave your entire server hanging up.

So, if you have a big deal, you make sure you split it, using the LIMIT condition is a good way. Here is an example:

while (1) {     //do only 1000 mysql_query at a time     ("DELETE from logs WHERE log_date <= ' 2009-11-01 ' LIMIT");     if (mysql_affected_rows () = = 0) {         //have nothing to delete, exit! Break         ;     }     Take a break every time.     

optimization of the WHERE statement

1. Try to avoid expression operations on fields in the WHERE clause
Select ID from Uinfo_jifen where jifen/60 > 10000;
After optimization:
Select ID from Uinfo_jifen where jifen>600000;

2. You should try to avoid function operations on the field in the Where clause, which will cause MySQL to discard the use of the index

Select UID from IMiD where DateDiff (create_time, ' 2011-11-22 ') =0
After optimization
Select UID from imid where create_time> = ' 2011-11-21 ' and create_time< ' 2011-11-23 ';

Optimization of Indexes

MySQL uses the index only for the following operators: <,<=,=,>,>=,between,in, and sometimes like.

Try not to write! = or <> sql, use between or > and < instead, otherwise it may not be indexed

Order BY, Group by, Distinct It's best to index on this column for index sorting

Try to sort by MySQL index

No way to use force index (INDEX_NAME)

Try to avoid innodb. Use a very large size field as the primary key

More frequent fields that are query criteria should create indexes;

Highly selective fields are more suitable for creating indexes;

Fields that are associated with a table typically require a hit index.

Fields that are updated very frequently are not suitable for creating indexes;

Fields that do not appear in the WHERE clause should not create an index.

Fields with too low selectivity are not suitable for creating indexes individually

try not to use sub-queries

Mysql> Explain select Uid_,count (*) from smember_6 where Uid_ in (select Uid_ from Alluid) GROUP by uid_;| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+--------------------+-----------+-------+---------------+---------+---------+------+----------+---- ----------------------+| 1 | PRIMARY | smember_6 | Index | NULL | PRIMARY | 8 | NULL | 53431264 | Using where; Using Index | | 2 | DEPENDENT subquery | Alluid | All | NULL | NULL | NULL | NULL | 2448 | Using Where |--optimized | Mysql> Explain select A.uid_,count (*) from smember_6 A,alluid b where a.uid_=b.uid_ GROUP by uid_;+----+-------------+- ------+------+---------------+---------+---------+------------+------+---------------------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+------+---------------+---------+---------+------------+------+-------------- -------------------+| 1 | Simple | B | All | NULL | NULL | NULL | NULL | 2671 | Using temporary; Using Filesort | | 1 | Simple | A | Ref | PRIMARY | PRIMARY | 4 | ssc.b.uid_ | 1 | Using Index

optimization of Join

If your application has many join queries, you should confirm that the fields of join in two tables are indexed. In this way, MySQL internally initiates the mechanism for you to optimize the SQL statement for join.
Also, the fields that are used for join should be of the same type. For example, if you want to join a DECIMAL field with an INT field, MySQL cannot use its index. For those string types, you also need to have the same character set. (Two tables may not have the same character set)

Table Optimization

Use not NULL where possible
Unless you have a very special reason to use null values, you should always keep your fields not NULL.
Do not assume that NULL does not require space, that it requires extra space, and that your program will be more complex when you compare it.
Of course, this is not to say that you cannot use NULL, the reality is very complex, there will still be cases where you need to use a null value.
Here is an excerpt from MySQL's own documentation:
"NULL columns require additional space in the row to record whether their values is null. For MyISAM tables, each of the NULL column takes one bit extra, rounded up to the nearest byte. "

Fixed-length tables are faster

If all the fields in the table are fixed length, the entire table is considered "static" or "Fixed-length". For example, there are no fields of the following type in the table: Varchar,text,blob. As long as you include one of these fields, the table is not a fixed-length static table, so the MySQL engine will handle it in a different way.
Fixed-length tables can improve performance because MySQL searches faster because these fixed lengths are easy to calculate the offset of the next data, so the nature of reading will be fast. And if the field is not fixed, then every time you want to find the next one, you need the program to find the primary key.
Also, fixed-length tables are more likely to be cached and rebuilt. However, the only side effect is that a fixed-length field wastes some space, because the field is set to allocate so much space whether you use it or not.

Vertical split

"Vertical Segmentation" is a method of turning a table in a database into several tables, which reduces the complexity of the table and the number of fields for optimization purposes. (Previously, in a bank project, saw a table with more than 100 fields, very scary)

Example one: One of the fields in the Users table is the home address, which is an optional field, and you do not need to read or rewrite this field frequently in addition to your personal information when working in a database. So, why not put him in another table? This will make your table better performance, we think is not, a lot of time, I for the user table, only the user ID, user name, password, user role, etc. will be used frequently. A smaller table will always have good performance.

Example two: You have a field called "Last_login" that will be updated every time the user logs in. However, each update causes the table's query cache to be emptied. So, you can put this field in another table, so that you do not affect the user ID, user name, user role of the constant read, because the query cache will help you to add a lot of performance.

In addition, you need to note that these separated fields form the table, you do not regularly join them, otherwise, this performance will be worse than not split, and, it will be a drop of magnitude.

The smaller the column, the quicker it will be.

For most database engines, hard disk operations can be the most significant bottleneck. So it's very helpful to have your data compact, because it reduces access to the hard drive.

See MySQL documentation Storage Requirements View all data types.

If a table has only a few columns (for example, a dictionary table, a configuration table), then we have no reason to use INT to master the keys, using Mediumint, SMALLINT or smaller TINYINT will be more economical. If you don't need to record time, using date is much better than DATETIME.

Of course, you also need to leave enough space for expansion, otherwise, you do this later, you will die very difficult to see, see Slashdot example (November 06, 2009), a simple ALTER TABLE statement took 3 hours, because there are 16 million data.

MySQL Common optimizations