Shulou(Shulou.com)05/31 Report--

This article mainly explains the "summary of mysql database optimization methods", the content of the explanation is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql database optimization methods summary" bar!

Database optimization

Sql statement optimization

Index optimization

Add caching

Separation of reading and writing

Zoning

Distributed database (vertical sharding)

Horizontal slicing www.walekan.com/kj/kjpp/1459

The difference between MyISAM and InnoDB:

1. InnoDB supports transactions, but MyISAM does not. For InnoDB, each SQL language is encapsulated as a transaction by default and automatically committed, which will affect the speed, so it is best to put multiple SQL languages between begin and commit to form a transaction.

2. InnoDB supports foreign keys, but MyISAM does not. Converting an InnoDB table containing foreign keys to MYISAM will fail

3. InnoDB does not save the specific number of rows of the table, and a full table scan is required when performing select count (*) from table. On the other hand, MyISAM uses a variable to save the number of rows of the entire table. When executing the above statement, you only need to read out the variable, which is very fast.

4. Innodb does not support full-text indexing, while MyISAM supports full-text indexing, so MyISAM is more efficient in query.

5. The locking mechanism is different: InnoDB is a row-level lock and myisam is a table-level lock.

Note: when the database cannot determine the row you are looking for, it will also change to lock the entire table.

For example: update table set num = 10 where username like "% test%"

First, SQL statement performance optimization

1. In order to optimize the query, we should avoid full table scanning as far as possible, and first consider establishing indexes on the columns involved in where and order by.

2. Try to avoid judging the null value of a field in the where clause. When creating a table, NULL is the default value, but most of the time you should use NOT NULL, or use a special value, such as 0memmer1 as the default value.

3. Try to avoid using the! = or operator in the where clause. MySQL uses indexes only for the following operators: =, BETWEEN,IN, and sometimes LIKE.

4. Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan. You can use UNION to merge the query: select id from t where num=10 union all select id from t where num=20

5Jining in and not in should also be used with caution, otherwise it will lead to a full table scan. For consecutive values, do not use in if you can use between: Select id from t where num between 1 and 3

6. The following query will also lead to a full table scan: select id from t where name like'% abc%' or select id from t where name like'% abc' to improve efficiency, you can consider full-text search. The index is used by select id from t where name like 'abc%'.

7, if you use parameters in the where clause, it will also cause a full table scan.

8. We should try to avoid expression operations on fields in the where clause and functional operations on fields in the where clause.

9. In many cases, using exists instead of in is a good choice: select num from a where num in (select num from b). Replace select num from a where exists (select 1 from b where num=a.num) with the following statement

10, the index can improve the efficiency of the corresponding select, but it also reduces the efficiency of insert and update, because insert or update may rebuild the index, so how to build the index needs to be carefully considered, depending on the specific situation. It is best to have no more than 6 indexes in a table, and if there are too many, consider whether it is necessary to build indexes on some infrequently used columns.

11. Updating clustered index data columns should be avoided as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the value of this column changes, it will lead to the adjustment of the order of the whole table records, which will consume a lot of resources. If the application system needs to update the clustered index data column frequently, it needs to consider whether the index should be built as a clustered index.

12. Try to use numeric fields, and try not to design character fields that contain only numeric information, which will reduce the performance of queries and connections, and increase storage overhead.

13, use varchar/nvarchar instead of char/nchar as much as possible, because first of all, the storage space of longer fields is small, which can save storage space, and secondly, for queries, searching in a relatively small field is obviously more efficient.

14, it's best not to use "" to return all: select from t, to replace "*" with a specific list of fields, and not to return any fields that you don't need.

15. Try to avoid returning a large amount of data to the client. If the amount of data is too large, you should consider whether the corresponding requirements are reasonable.

16, use the alias of the table (Alias): when joining multiple tables in the SQL statement, use the alias of the table and prefix it on each Column. In this way, you can reduce parsing time and reduce syntax errors caused by Column ambiguity.

17, use the temporary table to temporarily store the intermediate results

An important way to simplify SQL statements is to use temporary tables to temporarily store intermediate results, but the benefits of temporary tables are far more than these. Temporary results are temporarily stored in temporary tables, and the subsequent query is in tempdb, which can avoid scanning the main table many times in the program, and greatly reduce the "shared lock" blocking "update lock" in program execution, reducing blocking and improving concurrent performance.

18, some SQL queries should be added with nolock, read and write will block each other, in order to improve the concurrency performance, you can add nolock to some queries, so that you can write when reading, but the disadvantage is that you may read unsubmitted dirty data. There are three principles for using nolock. Query results for "insert, delete, change" can not add nolock! The query table belongs to frequent page splits, so use nolock carefully! The use of temporary tables can also save "data foreground", play a similar function of Oracle undo table space, can use temporary tables to improve concurrent performance, do not use nolock.

19. The common simplified rules are as follows: do not have more than 5 table joins (JOIN), consider using temporary tables or table variables to store intermediate results. Use less subqueries, do not nest views too deeply, and generally do not nest more than 2 views.

20, pre-calculate the results of the query and put them in the table, and then Select the query. This was the most important means before SQL7.0. For example, the hospitalization fee of the hospital is calculated.

21, using OR words can be decomposed into multiple queries, and multiple queries can be connected through UNION. Their speed is only related to whether or not to use indexes, and if queries need to use federated indexes, UNION all is more efficient. Multiple OR words do not use the index, rewrite into the form of UNION and then try to match the index. A key question is whether indexes are used.

22, in the list of face values after IN, put the values that appear most frequently at the front and those that appear least at the back, reducing the number of judgments.

23, try to put the data processing work on the server to reduce network overhead, such as the use of stored procedures. Stored procedures are compiled, optimized, and organized into an execution plan and stored in the database SQL statements, is a collection of control flow language, of course, fast. For dynamic SQL executed repeatedly, you can use temporary stored procedures, which (temporary tables) are placed in the Tempdb.

24, when the server has enough memory, the number of configuration threads = maximum number of connections + 5, which can achieve maximum efficiency; otherwise, use the number of configuration threads

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.