This is the third day of my August challenge, continuing with mysql performance optimization from the previous article
1.1.1. Considerations for federated index queries* * * *
MySQL can create indexes for multiple fields. An index can contain up to 16 fields. For a federated index, the index takes effect only if the first of these fields is used in the query criteria.
1.1.2. Queries using the OR keyword* * * *
The index takes effect only when the OR keyword is used in the query conditions and the columns before and after the OR condition are indexes. Otherwise, the index does not take effect.
1.1.3. Try not to use the OR keyword*
When using OR, if one of the conditions is not an index field, the entire index is invalidated. In or union is recommended
1.1.4. Try not to use functions in query conditions*
Using mysql functions in conditions can cause index invalidation. Most MySQL servers have query caching enabled. This is one of the most effective ways to improve performance and is handled by MySQL’s database engine. When many of the same queries are executed multiple times, the query results are placed in a cache so that subsequent queries can access the cache results without manipulating the table. The main problem here is that it’s easy for programmers to overlook this. Because some of our queries will make MySQL not use caching. Take a look at the following example: Signup_date >= CURDATE() SELECT username FROM user WHERE signup_date >= CURDATE( Signup_date >= 2015-01-12 The difference between the above SQL statements is CURDATE(). MySQL query cache does not apply to this function. Therefore, SQL functions such as NOW() and RAND() and others do not enable query caching because their returns are variable. So, all you need is a variable instead of a MySQL function to enable caching.
1.1.5. LIMIT 1 is used when there is only one row of data* * * *
Sometimes when you query a table, you already know that the result will only have one result, but because you might need to fetch the cursor, or you might check the number of records returned. In this case, adding LIMIT 1 can increase performance. In this case, the MySQL database engine will stop searching after it finds a piece of data, rather than continuing to search for the next piece of data that matches the record.
1.1.6. In associative queries, always use small result sets to drive large result sets* * * *
Nested Loop is the only form of Join in MySQL. Nested loops are used to implement joins. Loop drive the result set, the greater the need at this time, so the driver table visited the more naturally, and every visit to the driver table, even requires very little logic IO, more cycles, nature also can’t be small, total and each loop can avoid the need to consume CPU, so the CPU computational cost will increase. Conversely, fewer cycles are required, and the total IO and CPU operations are reduced. Moreover, even the Join algorithm of non-nested Loop, such as Hash Join in Oracle, is also the optimal choice for small result sets to drive large result sets.
1.1.7. Fetch only Columns that you need; Don’t select* * * * * * * * * * * * *
Fetch only Columns you need in a Query at any time, especially if you want to sort a Query. For any Query, the returned data needs to be sent back to the client through network packets. If more columns are taken out, the amount of data to be transmitted will naturally increase, which is a waste both in terms of network bandwidth and network transmission buffer. The impact is even greater for queries that need to be sorted. There are two kinds of sorting algorithms in MySQL: One is the old algorithm prior to MySQL4.1, which first fetches the fields that need to be sorted and the pointer information that can be directly located to the relevant row data, and then sorts them in the sorting area (set by sort_buffer_size). After sorting, the Columns needed are retrieved again through the row pointer information, that is to say, this algorithm needs to access the data twice. The second sorting algorithm is an improved algorithm used from MySQL4.1. It takes out all Columns needed at one time and directly returns the data to the requesting client after sorting in the sorting area. The new algorithm only needs to access the data once, which reduces a large number of random I/OS and greatly improves the efficiency of Query statements with sorting. However, this improved sorting algorithm needs to take out and cache much more data than the first algorithm. If we take out unnecessary Columns, it will greatly waste the memory needed in the sorting process.
1.1.8. Try not to use subqueries* * * *
MySQL supports subqueries since version 4.1. You can use subqueries to perform SQL operations that logically require multiple steps at a time. Although subqueries are flexible, they are not executed efficiently. When executing sub-queries, MYSQL needs to create temporary tables and then delete these temporary tables after the query is complete. Therefore, the sub-query speed is affected to some extent. Optimization: Sub queries can be replaced by JOIN queries, which do not need to create temporary tables and are faster than sub queries. In addition, join association conditions, preferably also index fields!
2. 4) Database structure optimization* * * *
A good database design scheme for the performance of the database often get twice the result with half the effort. You need to consider data redundancy, speed of query and update, and whether the data type of the field is reasonable.
2.1. Choose the appropriate data type ()* * *
The main principles for optimizing data types to improve performance are as follows:
- By selecting smaller data types to reduce storage space, the I/O resources required for querying the same data are reduced.
- Speed up data comparison with appropriate data types
Numbers: Use as little space as possible. Use integers instead of decimals. Strings: Use numbers whenever possible. Date type: number (millisecond value) Timestamp: saves the value in milliseconds
2.2. Do not use TEXT, BLOB types* * * *
More disk and memory space will be wasted. A large number of unnecessary large field queries will eliminate hot data, leading to a sharp decrease in memory hit ratio and affecting database performance. If necessary, separate a table and use the primary key to correspond to avoid affecting the index efficiency of other fields
2.3. Define the field as NOT NULL and raise the default value* * * *
A) Null columns make index/index statistics/value comparisons more complex, which is more difficult to optimize for MySQL; b) Null columns require special processing within MySQL, which increases the complexity of database processing records; C) More storage space is required for null columns in each row of the table or index. D) Only is NULL or is not NULL can be used for null processing. Instead of =, in, <, <>,! =, not in these operation symbols. Such as: where the name! = ‘zhangsan’, if a record whose name is null exists, the query result will not contain the record whose name is null
2.4. Split a table with many fields into multiple tables* * * *
Generally, the number of fields in a table cannot exceed 50. For a table with many fields, if some fields are not frequently used, you can separate these fields to form a new table. Because when a table has a large amount of data, it is slowed down by the presence of infrequently used fields. Vertical table: Too many fields are divided by column Horizontal table (caution, as logic becomes complicated) : Too much data in the table exceeds 10 million. Horizontal table: Divided by row. Mycat automatic database table
2.5. Add intermediate tables* * * *
For tables that require frequent joint queries, you can create intermediate tables to improve query efficiency. By creating an intermediate table, you insert the data that needs to be queried through the federated query into the intermediate table, and then change the original federated query to a query against the intermediate table. A, B, C A.x,A.y, a.zb.xc.y (A.x,A.y, A.zb.xc.y)
2.6. Add redundant fields appropriately* * * *
A(X, Y, Z, B.x,C.y) should try to follow the rules of the paradigm theory when designing data tables, reduce redundant fields as much as possible, and make the database design look delicate and elegant. However, reasonable addition of redundant fields can improve the query speed. The more normalized a table is, the more relationships there are between tables, the more queries need to be joined, and the worse the performance. Note: if the value of the **** redundant field is changed in one table, you need to find a way to update it in another table, otherwise you will have data inconsistency problems. *
2.7. Try not to design table associated fields (foreign key)* * * *
Foreign keys cause table to table coupling. Update and DELETE operations involve associated tables, affecting SQL performance and even causing deadlocks. High concurrency may degrade database performance. In high-concurrency big data service scenarios, performance takes precedence over database performance
2.8. More than ten million data will be divided into tables* * * *
3. Optimization of insert data* * * *
When inserting data, indexes, uniqueness check, and the number of inserted data items affect the insertion speed. Insert data optimization, different storage engine optimization means are different, in MySQL commonly used storage engine, MyISAM and InnoDB, the difference between the two:www.cnblogs.com/panfeng412/…
3.1. MyISAM* * * *
3.1.1. Disable index* * * *
For a non-empty table, MySQL creates an index for the inserted record based on the index of the table. If a large amount of data is inserted, indexing can slow down data insertion. To solve this problem, you can disable indexes before bulk inserts and then enable indexes after the inserts are complete. ALTER TABLE table_name DISABLE KEYS** ** ALTER TABLE table_name ENABLE KEYS** Do not need to ALTER TABLE table_name ENABLE KEYS** Do not need to ALTER TABLE table_name ENABLE KEYS**
3.1.2. Disable uniqueness checking* * * *
The uniqueness check reduces the speed of inserting records. You can disable the uniqueness check before inserting records and enable it after data is inserted. SET UNIQUE_CHECKS = 0; SET UNIQUE_CHECKS = 1;
3.1.3. Batch Inserting Data* * * *
When inserting data, you can use an INSERT statement to INSERT one or more data.
3.1.4. The use of the LOAD DATA INFILE* * * *
When you need to import DATA in batches, the LOAD DATA INFILE statement is much faster than the INSERT statement.
InnoDB* * * *
3.1.5. Disable uniqueness checking* * * *
1.1.1 Perform a check to disable foreign keys before Data insertion and restore data after data insertion to provide data insertion speed.
Disable: SET foreign_key_checks = 0; Enable: SET foreign_key_checks = 1; . Disable foreign key check ****
1.1.2. Disable automatic submission* * * *
Automatic commit of prohibited transactions before data insertion and recovery after data insertion can improve the insertion speed. Disable: SET autocommit = 0; SET autocommit = 1;
4. Server optimization* * * *
4.1. Optimizing server hardwareThe hardware performance of **** server directly determines the performance of MySQL database. The performance bottleneck of hardware directly determines the running speed and efficiency of MySQL database.
Consider the following aspects: 1. Configure a large memory. Large enough memory is one of the ways to improve the performance of MySQL database. Memory uses much faster I/O than hard disks. Therefore, the system buffer capacity can be increased to keep data in memory for a longer time, reducing disk I/O. 2. Configure high-speed disks, such as SSDS. 3. Allocate disk I/O to multiple devices to reduce resource competition and improve parallel operation capability. 4, configure multi-core processor, MySQL is multi-threaded database, multi-processor can improve the ability to execute multiple threads at the same time.
1.1. Optimize MySQL parameters* * * *
By optimizing the parameters of MySQL, resource utilization can be improved and MySQL server performance can be improved. Mysqld = mysqld; mysqld = mysqld; mysqld = mysqld;
