- 1. What is the difference between char, varchar and text types in mysql?
- 2. What are the commonly used storage engines for mysql and what are the differences?
- 2. What are the three paradigms of database design?
- 3. How to optimize sql?
- 1.sql optimization principle
- 2. The select statement grammatical order
- 2. Select statement execution order
- 3. Index categories and application scenarios
- 4. Avoid scenes without indexing
- 5. Other optimizations of the SELECT statement
- 5. Table building optimization
- to sum up
Below, let me summarize which database interview questions are available for everyone
1. What is the difference between char, varchar and text types in mysql?
1. CHAR is a fixed-length type, suitable for storing very short or similar length attributes, such as gender, ID card number, mobile phone number and other fields. VARCHAR is a variable-length type, suitable for storing variable attributes whose maximum length is known. The length of text is not set. When the maximum length of the attribute is unknown, text is suitable.
2. Comparison of query speed: char is the fastest, varchar is the second, and text is the slowest.
3. When reading char data, if there is a space at the end, spaces will be lost. Varchar and text will not be
4. The range of char is 0～255, and varchar is the most. The length is 64k, but note that 64k here is the length of the entire row, and text can store up to 64k.
5. For MySQL versions above 5.0, the n in char and varchar represents the number of characters
2. What are the commonly used storage engines for mysql and what are the differences?
mysql commonly used storage engines include InnoDB, MyISAM, Memory
1. InnoDB is the default engine of mysql, supports transactions, supports table-level locks and row-level locks, can support higher concurrency, supports foreign key constraints, supports self-growing columns, there are Buffer management, through the buffer pool, all indexes and data are cached to speed up queries.
2. MyISAM used to be the default engine of mysql, with high insertion and query speed, but does not support transactions.
3. Memory stores data directly in memory, which is especially suitable for tables with small data volumes. Insertion and query are very fast, but once the server goes down, the data will be lost.
2. What are the three paradigms of database design?
1. The first normal form: the emphasis is on the atomicity of the columns, that is, each column is the smallest unit of data that cannot be subdivided.
2. The second paradigm: to meet the first paradigm, the table must have a primary key and other columns that are not primary keys must be completely dependent on all primary keys rather than partial primary keys.
3. The third paradigm: To meet the second paradigm, non-primary key columns must directly depend on the primary key, and there can be no transitive dependence.
3. How to optimize sql?
1.sql optimization principle
Maximize the use of indexes, avoid full table scans as much as possible, and reduce invalid data queries
2. The select statement grammatical order
1. SELECT 2. DISTINCT <select_list> 3. FROM <left_table> 4. <join_type> JOIN <right_table> 5. ON <join_condition> 6. WHERE <where_condition> 7. GROUP BY <group_by_list> 8. HAVING <having_condition> 9. ORDER BY <order_by_condition> 10.LIMIT <limit_number>
2. Select statement execution order
FROM <表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。 ON <筛选条件> # 对笛卡尔积的虚表进行筛选 JOIN <join, left join, right join...> <join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中 WHERE <where条件> # 对上述虚表进行筛选 GROUP BY <分组条件> # 分组 <SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的 HAVING <分组筛选> # 对分组后的结果进行聚合筛选 SELECT <返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外 DISTINCT # 数据除重 ORDER BY <排序条件> # 排序 LIMIT <行数限制>
3. Index categories and application scenarios
The indexes in mysql are divided into PRIMARY (primary key index), INDEX (ordinary index), UNIQUE (unique index), FULLTEXT (full text index). Generally, tables that are frequently queried with more than 100,000 single-table data are considered to be indexed. The specific conditions are as follows:
(1) The primary key automatically establishes a unique index;
(2) The fields frequently used as query conditions should be indexed;
(3) The query is related to other tables Indexes should be created for related fields, such as foreign key relationships;
(4) Indexes should be created for the statistical or grouped fields in the
query ; (5) Indexes are created for the fields sorted in the query;
(6) Frequently updated fields are not suitable for index creation , Because each update not only updates records, but also updates indexes and saves index files;
(7) Too few table records are not suitable for index creation;
(8) Fields with repeated data and even distribution are not suitable for index creation when entering gender
4. Avoid scenes without indexing
(1) Try to avoid fuzzy query at the beginning of the field, which will cause the database engine to abandon the index for full table scan, and try to use fuzzy query behind the field;
(2) Try to avoid using in and not in, which will cause the engine to scan the whole table. It is a continuous value, you can use between instead, if it is a subquery, you can use exists instead;
(3) Try to avoid using or, which will cause the database engine to abandon the index for full table scan, you can use union instead of or;
(4) Try to avoid doing it The judgment of the null value will cause the database engine to abandon the index and perform the full table scan. You can add the default value of 0 to the field to judge the value of 0;
(5) Try to avoid performing expressions and function operations on the left side of the equal sign of the where condition. Will cause the database engine to abandon the index to perform a full table scan;
(6) When the amount of data is large, avoid using the where 1=1 condition. Usually in order to facilitate the assembly of query conditions, we will use this condition by default, and the database engine will abandon the index for full table scan;
(7) The query conditions cannot use <> or !=;
(8) Where conditions only include composite index non-leading columns ;
(9) Implicit type conversion causes no index to be used;
(10) The condition of order by must be consistent with the condition in where, otherwise order by will not use the index for sorting;
5. Other optimizations of the SELECT statement
(1) Avoid select *
(2) Avoid functions with uncertain results. For business scenarios such as master-slave replication, using functions with uncertain results such as now(), rand(), sysdate(), current_user(), etc. can easily lead to inconsistent data between the master library and the slave library.
(3) When multi-table related query, the small table is in the front and the large table is in the back.
(4) Use the alias of the table. When connecting multiple tables in a SQL statement, please use the alias of the table and prefix the alias to each column name.
(5) Replace the HAVING sentence with the where sentence. The conditions in HAVING are generally used to filter aggregate functions. In addition, the conditions should be written in the where sentence.
(6) Adjust the connection order in the Where sentence. mysql parses the where clause from left to right and top to bottom. According to this principle, the conditions for filtering more data should be put forward, and the result set should be reduced as quickly as possible.
5. Table building optimization
(1) Create an index in the table, and give priority to the fields used by where and order by.
(2) Try to use numeric fields (such as gender, male: 1 female: 2)
(3) Use varchar/nvarchar instead of char/nchar
to sum up
Tip: Here is a summary of the article:
For example, the above is what we are going to talk about today. This article only briefly introduces the use of pandas, and pandas provides a large number of functions and methods that enable us to process data quickly and conveniently.