What are the common techniques for MySQL database?

1. How to choose the type of server?

The meaning of each parameter in the MySQL server configuration window is as follows.

This option is used to specify the configuration type of the server. Click the down button to the right of this option to see 3 options. The specific meanings of the

3 options are as follows:

This option represents a typical personal desktop workstation for development. Assume that there are multiple desktop applications running on the machine. Configure the MySQL server to use minimal system resources.

Server Machine (server): This option represents the server. MySQL server can run together with other applications, such as FTP, email and web servers. The MySQL server is configured to use an appropriate proportion of system resources.

DedicatedMySQL Server Machine (dedicated MySQL server): This option represents a server that only runs the MySQL service. It is assumed that no other applications are running. MySQL server is configured to use all available system resources. As a beginner, it is recommended to choose the [Development Machine] option, which takes up less system resources.

2. How to use special characters in MySQL?

Such as single quotation mark ('), double quotation mark ("), backslash () and other symbols, these symbols cannot be directly input and used in MySQL, otherwise they will produce unexpected results. In MySQL, These special characters are called escape characters. They need to start with a backslash symbol ('') when inputting. Therefore, when using single quotes and double quotes, you should enter (') or (") respectively. When entering backslashes, you should Input (), other special characters include carriage return (), line feed (), tab (ab), backspace (), etc. When inserting these special characters into the database, they must be escaped.

3. How does MySQL perform case-sensitive string comparison?

MySQL is not case-sensitive under the Windows platform, so the string comparison function is not case-sensitive. To perform a case-sensitive comparison, use the keyword BINARY before the string. For example, by default, the return result of 'a'='A' is 1. If the BINARY keyword is used, the result of BINARY'a'='A' is 0. In case of case sensitivity, 'a' and 'A' Not the same.

MySQL statement optimization skills

The optimization of MySQL database performance is the only way for the development of MySQL database. The optimization of MySQL database performance is also a witness to the progress of MySQL database. The following is an introduction to MySQL statement optimization. Some tips:

1. Try to avoid using != or <> operators in the where clause, otherwise the engine will give up using the index and perform a full table scan.

In order to optimize queries, full table scans should be avoided as much as possible. Then, we should prioritize building indexes on the columns involved in where and order by.

3. Try to avoid judging the null value of the field in the where clause, otherwise the engine will give up using the index and perform a full table scan, such as:

select id from t where num is null

You can set the default value 0 on num, ensure that there is no null value in the num column in the table, and then query like this:

select id from t where num=0

4. Try to avoid using or in the where clause to connect conditions, otherwise the engine will give up using the index and perform a full table scan, such as:

select id from t where num=10 or num=20

You can query like this:

select id from t where num=10

union all

select id from t where num=20

5. The following query will also result in a full table scan: (cannot precede the percent sign)

select id from t where name like '�c%'

To improve efficiency, you can Consider full-text search.

6. In and not in should also be used with caution, otherwise it will lead to a full table scan, such as:

select id from t where num in(1,2,3)

For continuous values, don’t use in if you can use between:

select id from t where num between 1 and 3

7. If you use parameters in the where clause, also Will result in a full table scan. Because SQL resolves local variables only at runtime, the optimizer cannot defer selection of an access plan until runtime; it must make the selection at compile time. If the access plan is built at compile time and the value of a variable is unknown, this value cannot be used as an input for index selection. For example, the following statement will perform a full table scan:

select id from t where num=@num

You can change it to force the query to use an index:

select id from t with( index (index name)) where num=@num

8. Try to avoid performing expression operations on fields in the where clause, which will cause the engine to give up using the index and perform a full table scan. For example:

select id from t where num/2=100

should be changed to:

select id from t where num=100*2

9. Try to avoid performing function operations on fields in the where clause, which will cause the engine to give up using the index and perform a full table scan. For example:

select id from t where substring(name,1,3)='abc'–name id starting with abc

select id from t where datediff(day,createdate,' 2005-11-30′)=0–’2005-11-30′The generated id

should be changed to:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30′ and createdate<'2005-12-1′

10. Do not perform functions, arithmetic operations or other expression operations on the left side of "=" in the where clause, otherwise the system may not be able to use the index correctly.

11. When using an index field as a condition, if the index is a composite index, the first field in the index must be used as the condition to ensure that the system uses the index, otherwise the index will not be used. will be used, and the field order should be consistent with the index order as much as possible.

12. Do not write meaningless queries. For example, if you need to generate an empty table structure:

select col1,col2 into #t from t where 1=0

This The class code will not return any result set, but it will consume system resources. It should be changed to this:

create table #t(…)

13. Many times, exists is used instead of in. Good choice:

select num from a where num in(select num from b)

Replace with the following statement:

select num from a where exists(select 1 from b where num=a.num)

14. Not all indexes are valid for queries. SQL optimizes queries based on the data in the table. When there is a large amount of duplicate data in the index column, the SQL query may Indexes will not be used. For example, if there is a field sex in a table, and almost half are male and half female, then even if an index is built on sex, it will not have any effect on query efficiency.

15. The more indexes, the better. Although the index can improve the efficiency of the corresponding select, it also reduces the efficiency of insert and update, because the index may be rebuilt during insert or update, so how to Indexing requires careful consideration and will depend on the circumstances. It is best not to have more than 6 indexes on a table. If it exceeds this number, you need to consider whether it is necessary to create indexes on some infrequently used columns.

16. Avoid updating clustered index data columns as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column value changes, the order of the entire table records will be adjusted. It consumes considerable resources. If the application system needs to frequently update clustered index data columns, you need to consider whether the index should be built as a clustered index.

17. Try to use numeric fields. If the fields contain only numerical information, try not to design them as character fields. This will reduce the performance of query and connection, and increase storage overhead. The engine compares each character in the string one by one when processing queries and joins, so numeric types only need to be compared once instead of one by one.

18. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, variable length fields have small storage space and can save storage space. Secondly, for queries, search efficiency in a relatively small field is high. Obviously higher.

19. Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any unused fields.

20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the indexes are very limited (only primary key indexes).

21. Avoid frequently creating and deleting temporary tables to reduce the consumption of system table resources.

22. Temporary tables are not unusable. Using them appropriately can make certain routines more efficient, for example, when you need to repeatedly reference a large table or a certain data set in a commonly used table. However, for one-off events, it's better to use an export table.

23. When creating a temporary table, if the amount of data inserted at one time is large, you can use select into instead of create table to avoid causing a large number of logs to increase speed; if the amount of data is not large, in order to ease the system For table resources, you should first create the table and then insert it.

24. If temporary tables are used, all temporary tables must be explicitly deleted at the end of the stored procedure. First truncate the table, and then drop the table. This can avoid long-term locking of system tables.

25. Try to avoid using cursors because cursors are less efficient. If the data operated by the cursor exceeds 10,000 rows, you should consider rewriting it.

26. Try to avoid large transaction operations and improve system concurrency.

The above is the detailed content of What are the common techniques for MySQL database?. For more information, please follow other related articles on the PHP Chinese website!