MySQL Database interview summary (i)

1. Database optimization 1) database paradigm

　　The first paradigm (1NF): The emphasis is on the atomicity of the column, that is, the column cannot be divided into several other columns.

If the phone column can be split---home phone, corporate phone

　　The second paradigm (2NF): The first is 1NF, another contains two parts, one is the table must have a primary key, and the other is not included in the primary key column must be completely dependent on the primary key, and not only rely on a part of the primary key.

　　

　　The third paradigm (3NF): First is 2NF, the other non-primary key column must be directly dependent on the primary key, there can be no delivery dependency.

such as Student table (school number, name, age, gender, institution, institution address, college phone)

Such a table structure, there is the above relationship. Study number------(university address, college phone)

Take it apart, as follows.

(Student number, name, age, gender, institution)--(institution, institution address, school phone)

Databases that meet these specifications are concise and well-structured, and there is no exception to insert (insert), delete, and update operations.

2) data type selection

　　Number type:　　　

float and double selection (choose float as far as possible)

Differentiate open tinyint/int/bigint, can determine the field that does not use negative numbers, suggest adding unsigned definition

The ability to select numeric types instead of string types with fields of numeric type

Character type

　　Char,varchar,text choice: Not the last resort to use the TEXT data type, fixed Length field, the proposed use of CHAR type (fill in the blanks), indefinite long field to use varchar (automatically adapt to length, over stage), and only set the appropriate maximum length

Time Type

　　Sort By Selection Priority date (exact to day), TIMESTAMP, DATETIME (accurate to time)

Enum

　　For the Status field, you can try to use an ENUM to hold

Avoiding null fields makes it difficult to query optimizations and occupy additional index space

3) Character encoding

The same content uses different character sets to indicate that the size of the space used is significantly different, so by using the appropriate character set, you can help us reduce the amount of data as much as possible, thereby reducing the number of IO operations.

1. The Pure Latin word RP The content, select the latin1 character encoding

2. Chinese can choose Utf-8

The data type of 3.MySQL can be accurate to the field, so when we need large database to hold multi-byte data, we can reduce the amount of data storage greatly by using different data types for different fields of different table, decrease the number of IO operations and improve the cache hit ratio.

2. SQL optimization

1. To optimize the query, avoid full-table scanning as far as possible, and first consider establishing an index on the columns involved in the Where and order by. 2. You should try to avoid null values in the WHERE clause to judge the field, otherwise it will cause the engine to abandon using the index for full table scan, such as: Select ID from the where num is null can set the default value of 0 on NUM, to ensure that the table NUM column does not have n The ull value then queries: Select ID from t where num=0

3. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index. 4. You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for a full table scan, such as: Select ID from t where num=10 or num=20 can query: Select ID from t WH Ere num=10 UNION ALL select IDs from T where num=205.in and not in are also to be used with caution, otherwise it will result in a full table scan, such as: Select ID from t where num in  For consecutive values, do not use between in: The Select ID from t where num between 1 and 36. The following query will also cause a full table scan: Select ID from t where name is like '%abc% ' 7. You should try to avoid expression operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. For example: Select ID from t where num/2=100 should be changed to: Select ID from t where num=100*28. You should try to avoid function operations on fields in the WHERE clause, which will cause the engine to discard full table scans using the index. For example: Select ID from t where substring (name,1,3) = ' abc '--name the ID beginning with ABC should be changed to: Select ID from t where name like ' abc% ' 9. Do not be in the wher The "=" in the E clause is left to perform functions, arithmetic operations, or other expression operations, or the index may not be used correctly by the system. 10. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible. 11. Do not write meaningless queries, such as the need to generate an empty table structure: Select Col1,col2 into #t from T where 1=0 such code will not return any result set, but will consume system resources, should be changed to this: Create Table #t (.. .) 12. Many times use exists instead In is a good choice: Select num from a where num in (select num from B) is replaced with the following statement: Select Num from a where exists (select 1 from B W Here Num=a.num) 13. Not all indexes are valid for queries, SQL is query-optimized based on the data in the table, and when there is a lot of data duplication in the index columns, the SQL query may not take advantage of the index, as there are fields Sex,male, female, and almost half of the table. So even if you build an index on sex, it doesn't work for query efficiency. 14. The index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary. 15. Use numeric fields as much as possible, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of queries and connections and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once. 16. Use varchar instead of char as much as possible, because the first variable-length field has a small storage space and can save storage space, and secondly, in a relatively small field, search efficiency is obviously higher for queries. 17. Do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available. 18. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

19. Temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table. 20. When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, and if the amount of data is small, create table to mitigate the resources of the system tables. Then insert.

21. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables. 22. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data. 23. Before using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is generally more efficient.

24. As with temporary tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data.

Routines that include "totals" in the result set are typically faster than using cursors. If development time permits, a cursor-based approach and a set-based approach can all be tried to see which method works better.

25. Try to avoid large transaction operation and improve the system concurrency ability.

26. Try to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.

3, index optimization 1), CREATE INDEX,

　　The following conditions are not appropriate for indexing

• Too few table records
• Tables that are frequently inserted, deleted, modified
• Table fields with repeated data and distributed averages

2),Composite Index

These fields can be used as composite indexes if the data in one table always appears at the same time when there are multiple fields in the query

Index

An index is a structure that sorts the values of one or more columns in a database table.

Advantages:

L greatly speed up the retrieval speed of data

L Create a unique index that guarantees the uniqueness of each row of data in a database table

L can speed up the connection between tables and tables

Disadvantages:

The index needs to occupy the physical space.

When the data in the table is added, deleted and modified, the index should be maintained dynamically.

Reduced Data Maintenance speed

Index classification:

L General Index

Create INDEX zjj_temp_index_1 on zjj_temp_1 (first_name);

Drop index zjj_temp_index_1;

L Unique index , the value of the indexed column must be unique, but a null value is allowed

Create unique index zjj_temp_1 on zjj_temp_1 (ID);

L primary key index , which is a special unique index and does not allow null values.

　　L Combined Index

Reference Blog: https://www.jianshu.com/p/5052f6a454ef

http://blog.csdn.net/jie_liang/article/details/77340905

Https://www.cnblogs.com/wmbg/p/6800354.html

MySQL Database interview summary (i)