Today, database operations are increasingly becoming the performance bottleneck of the entire application, especially for Web applications. Concerning the database performance, this is not just something that DBAs need to worry about, but it is something that our programmers need to pay attention. When designing the database table structure and operating the database (especially the SQL statements used in table queries), we need to pay attention to the performance of data operations. Here, we will not talk about the optimization of many SQL statements, but only for the database with the most Web application MySQL. I hope the following optimization techniques will be useful to you.
1. Optimize Your query for the query Cache
Query cache is enabled on most MySQL servers. This is one of the most effective ways to improve performance, and it is processed by the MySQL database engine. When many identical queries are executed multiple times, these query results are stored in a cache, the cache results are directly accessed for the same query in the future without having to operate the table.
The main problem here is that this is easy for programmers to ignore. Because some of our query statements will make MySQL not use cache. See the following example:
SQL code
// Query cache disabled
$ R = mysql_query ("SELECT username FROM user WHERE signup_date> = CURDATE ()");
SQL code
// Enable query Cache
$ Today = date ("Y-m-d ");
$ R = mysql_query ("SELECT username FROM user WHERE signup_date> = '$ today '");
The difference between the preceding two SQL statements is CURDATE (). The query cache of MySQL does not work for this function. Therefore, SQL functions such as NOW (), RAND (), and other such functions do not enable the query cache, because the returned results of these functions are variable. Therefore, all you need is to use a variable to replace the MySQL function and enable the cache.
2. EXPLAIN your SELECT query
The EXPLAIN keyword helps you know how MySQL processes your SQL statements. This helps you analyze the performance bottleneck of your query statement or table structure.
The EXPLAIN query results also show you how your index primary key is used and how your data tables are searched and sorted ...... And so on.
SELECT one of your SELECT statements (we recommend that you SELECT the most complex one with multi-table join) and add the keyword "EXPLAIN" to the front. You can use phpmyadmin to do this. Then, you will see a table. In the following example, we forget to add the group_id index and have table join:
After adding an index to the group_id field:
We can see that the previous result shows that 7883 rows are searched, and the last one only searches 9 and 16 rows of two tables. Viewing the rows column allows us to find potential performance problems.
3. Use LIMIT 1 when only one row of data is required
When you query a table, you know that only one result is returned, but you may need to fetch the cursor or check the number of returned records.
In this case, adding LIMIT 1 can increase performance. In this way, the MySQL database engine will stop searching after finding a piece of data, rather than continuing to query the next piece of data that matches the record.
The example below is just to find out if there are "China" users. Obviously, the latter will be more efficient than the former one. (Note that Select * is the first and Select 1 is the second)
SQL code
// Inefficient:
$ R = mysql_query ("SELECT * FROM user WHERE country = 'China '");
If (mysql_num_rows ($ r)> 0 ){
//...
}
// Efficient:
$ R = mysql_query ("SELECT 1 FROM user WHERE country = 'China' LIMIT 1 ");
If (mysql_num_rows ($ r)> 0 ){
//...
}
4. Storage Engine Optimization
MySQL supports different storage engines, including MyISAM and InnoDB.
4.1 MyISAM
MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless MySQL is configured to use another engine by default.
4.1.1 MyISAM features
4.1.1.1 MyISAM Properties
1) transactions are not supported and tables are damaged due to downtime
2) use smaller memory and disk space
3) Table-based locks may cause serious performance problems when concurrently updating data.
4) MySQL only caches indexes, and data is cached by OS
4.1.1.2 Typical MyISAM usages
1) Log System
2) read-only or mostly read-only applications
3) full table Scan
4) Batch import data
5) Low-concurrency read/write without transactions
4.1.2 Key Points of MyISAM Optimization
1) The declared column is not null, which can reduce disk storage.
2) use optimize table for fragment and free space recovery. Note that it only runs after a very large data change.
3) When Deleting/updating/adding has a large amount of data, index is forbidden. Use alter table t disable keys.
4) set myisam_max _ [extra] _ sort_file_size to be large enough to significantly increase the repair table speed.
4.1.3 MyISAM Table Locks
1) avoid concurrent insert and update.
2) insert delayed can be used, but data may be lost.
3) Optimize the query statement.
4) Horizontal partitioning.
5) vertical partitioning.
6) If none of them work, use InnoDB.
4.1.4 MyISAM Key Cache
1) set key_buffer_size variable. MyISAN's most important cache settings are used to cache index data in the MyISAM table. This parameter only affects MyISAM. Generally, the memory size is set to 25-33% in the Server that only uses MyISAM.
2) You can use several different Key Caches (for some hot data ).
A) set global test. key_buffer_size = 512*1024;
B) CACHE INDEX t1.i1, t2.i1, t3 IN test;
2) Preload index to Cache can increase the query speed. Because the preloading index is sequential, It is very fast.
A) load index into cache t1, t2 ignore leaves;
4.2 InnoDB
InnoDB provides MySQL with a transaction security (ACID-compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB provides row level lock and an Oracle-like non-locked read in the SELECT statement. These features increase the deployment and performance of multiple users. There is no need to expand the lock in InnoDB, because row level lock in InnoDB is suitable for a very small space. InnoDB also supports the foreign key constraint. In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query.
InnoDB is designed to achieve maximum performance when processing a large amount of data. Its CPU usage efficiency is very high.
The InnoDB Storage engine has been fully integrated with the MySQL server. the InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the memory. InnoDB stores its tables and indexes in a tablespace. The tablespace can contain several files (or original disk partitions ). This is different from the MyISAM table. For example, in the MyISAM table, each table is in a separate file. The InnoDB table can be any size, even on an operating system with a file size limited to 2 GB.
Many large database sites that require high performance use the InnoDB engine. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. Stores more than 1 TB of data on InnoDB, and some other sites process an average of 800 inserts/updates per second on InnoDB.
4.2.1 InnoDB features
4.2.1.1 InnoDB Properties
1) supports transactions, ACID, and foreign keys.
2) Row level locks.
3) different isolation levels are supported.
4) it requires more memory and disk space than MyISAM.
5) No key compression.
6) data and indexes are cached in the memory hash table.
4.2.1.2 InnoDB Good
1) applications requiring transactions.
2) highly concurrent applications.
3) Automatic Recovery.
4) Fast primary key-based operations.
4.2.2 InnoDB optimization key points
1) try to use the short and integer primary keys.
2) Load/Insert data is in the primary key order. If the data is not sorted by the primary key, sort the data first and then perform database operations.
3) SET UNIQUE_CHECKS = 0 and SET FOREIGN_KEY_CHECKS = 0 in the Load data to avoid overhead for checking Foreign keys and uniqueness constraints.
4) use prefix keys. Because InnoDB does not have the key compression function.
4.2.3 InnoDB server side settings
Innodb_buffer_pool_size: This is the most important setting of InnoDB and has a decisive impact on InnoDB performance. The default setting is only 8 M, so the InnoDB performance under the default database settings is poor. You can set a memory of 60-80% on the database server that only has the InnoDB Storage engine. More precisely, set a memory size of 10% larger than that of InnoDB tablespaces when the memory capacity permits.
Innodb_data_file_path: Specifies the table data and index storage space, which can be one or more files. The last data file must be automatically expanded, and only the last file can be automatically expanded. In this way, when the space is used up, the data file is automatically expanded (in 8 Mb) to accommodate additional data. For example, innodb_data_file_path =/disk1/ibdata1: 900 M;/disk2/ibdata2: 50 M: autoextend two data files are stored on different disks. Data is first placed in ibdata1. When the data reaches MB, it is placed in ibdata2. Once it reaches 50 MB, ibdata2 will automatically increase in 8 MB. If the disk is full, add a data file to another disk.
Innodb_autoextend_increment: The default value is 8 Mb. If there is a large volume of insert data at a time, you can add it as appropriate.
Innodb_data_home_dir: directory for storing tablespace data. It is in the mysql data directory by default. Setting it to a partition different from the MySQL installation file can improve performance.
Innodb_log_file_size: this parameter determines the recovery speed. If it is too large, the rediscovery process will be slow. If it is too small, the query performance will be affected. Generally, MB can take into account both the performance and the rediscovery speed.
Innodb_log_buffer_size: the disk speed is very slow. Writing logs directly to the disk affects InnoDB performance. This parameter sets the log buffer size, generally 4 MB. If there is a large blob operation, you can increase it as appropriate.
Innodb_flush_logs_at_trx_commit = 2: This parameter sets the processing of log information in the memory when the transaction is committed.
1) = 1. When each transaction is committed, the log buffer is written to the log file to refresh the disk operations of the log file. Truly ACID. Slow speed.
2) = 2. When each transaction is committed, the log buffer is written to the file, but the disk operation is not refreshed. Transactions in the last second will be deleted only when the operating system crashes or power is down. Otherwise, transactions will not be lost.
3) = 0, the log buffer is written to the log file once per second, and the log file is refreshed by disk operations. Any crash of the mysqld process will delete the last second of the transaction before the crash
Innodb_file_per_table: stores each InnoDB table and its indexes in its own files.
Transaction-isolation = READ-COMITTED: Doing This setting can improve performance if the application can run at the READ-COMMITED isolation level.
Innodb_flush_method: sets the InnoDB synchronous IO method:
1) Default-use fsync ().
2) O_SYNC is usually slow to open files in sync mode.
3) O_DIRECT: Use Direct IO on Linux. This can significantly increase the speed, especially on RAID systems. Avoid extra data replication and double buffering (mysql buffering and OS buffering ).
Innodb_thread_concurrency: Maximum number of threads in InnoDB kernel.
1) set to at least (num_disks + num_cpus) * 2.
2) You can disable this restriction by setting it to 1000.
5. Use an equivalent type of example when joining a table and index it
If your application has many JOIN queries, you should confirm that the Join fields in the two tables are indexed. In this way, MySQL will launch a mechanism to optimize the Join SQL statement for you.
In addition, these fields 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 their indexes. For those STRING types, the same character set is required. (The character sets of the two tables may be different)
SQL code
// Find company in state
$ R = mysql_query ("SELECT company_name FROM users
Left join companies ON (users. state = companies. state)
WHERE users. id = $ user_id ");
// The two state fields should be indexed and of the same type, the same character set.
Author: "Wang Yan"