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

This article mainly explains "what are the classic MySQL interview questions". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the classic MySQL interview questions"?

1. What is the three paradigms of database?

The first normal form (1NF): fields are atomic and cannot be divided. (all relational database systems meet the requirements of the first paradigm that the fields in the database table are single-attribute and cannot be further divided.)

The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to meet the second normal form (2NF) must first meet the first normal form (1NF). It is required that each instance or row in the database table must be uniquely localized. You usually need to add a column to the table to store the unique identity of each instance. This unique attribute column is called the primary keyword or primary key.

To meet the third normal form (3NF), we must first meet the second normal form (2NF). In short, the third normal form (3NF) requires that a database table does not contain non-primary keyword information that has already been contained in other tables. So the third normal form has the following characteristics: > > 1. Each column has only one value > > 2. Every line can be distinguished. > > 3. Each table does not contain non-primary keyword information that the other tables already contain.

two。 What experience do you have in database optimization?

Using PreparedStatement is generally better than Statement performance: a sql is sent to the server to execute, involving steps: syntax checking, semantic analysis, compilation, caching.

Foreign key constraints will affect insert and delete performance, if the program can ensure the integrity of the data, then remove the foreign key when designing the database.

Appropriate redundancy is allowed in the table, such as the number of replies to topic posts and the last reply time, etc. 4. UNIONALL is much faster than UNION, so use UNIONALL if you can confirm that the merged two result sets do not contain duplicate data and do not need to be sorted. Both the UNION and UNION ALL keywords merge two result sets into one, but they are different in terms of usage and efficiency. > 1. Processing of duplicate results: UNION will filter out duplicate records after table linking, and Union All will not remove duplicate records. > 2. Processing of sorting: Union will sort according to the order of the fields; UNION ALL simply merges the two results and returns.

3. Please briefly describe what kinds of indexes are commonly used?

Normal index: that is, to create an index on a database table

Unique index: similar to a normal index, except that the value of the index column in the MySQL database must be unique, but null values are allowed

Primary key index: it is a special unique index and no null values are allowed. Generally, the primary key index is created at the same time when the table is created.

Combinatorial index: in order to further extract the efficiency of MySQL, it is necessary to consider establishing a combinatorial index. Multiple fields in the database table are combined as a combined index.

4. And how does the index work in the mysql database?

Database index is a sorted data structure in database management system to help quickly query and update data in database tables. The implementation of index usually uses B-tree and its variants B + tree.

Basic operation commands for 5.MySQL:

Whether MySQL is running: run the command service mysqlstatus on Debian, and run the command service mysqld status on RedHat

Start or stop the MySQL service: run the command service mysqld start to start the service; run the command service mysqld stop to stop the service

Shell login MySQL: run the command mysql-u root-p

List all databases: run the command show databases

Switch to a database and work on it: run the command use databasename; to enter the database named databasename

List all tables in a database: show tables

Get the name and type of all Field objects in the table: describe table_name

The replication principle and process of 6.mysql.

Mysql's built-in replication function is the basis for building large, high-performance applications. Distribute the data of Mysql to multiple systems. The mechanism of this distribution is to copy the data of one host of Mysql to another host (slaves) and execute it again. * during replication, one server acts as the master server, while one or more other servers act as the slave server.

The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs can record updates sent to the slave server. When a slave server connects to the master server, it informs the master server of the location of the last successful update read in the log.

Receive any updates that have occurred since then from the server, then block and wait for the primary server to notify the new update. The process is as follows

1. The primary server records updates to a binary log file.

two。 The slave server copies the binary log of the master server into its own relay log (replay log). 3. Redo the time in the relay log from the server and apply the update to your database.

What types of replication are supported by 7.mysql?

Statement-based replication: a SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient. Row-based replication is automatically selected when it is found that exact replication is not possible.

Line-based replication: copy the changes instead of executing the command from the server. Support starts with mysql5.0

Mixed type replication: statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

What is the difference between myisam and innodb in 8.mysql?

Transaction support > * MyISAM: the emphasis is on performance. Each query is atomic and executes faster than the InnoDB type, but does not provide transaction support. > * InnoDB: provides advanced database features such as transaction support, foreign keys and so on. Transaction security (transaction-safe (ACID compliant)) table with transaction (commit), rollback (rollback), and crash repair capability (crash recovery capabilities).

InnoDB supports row-level locks, while MyISAM supports table-level locks. > > when users operate on a myisam table, the select,update,delete,insert statement automatically locks the table. If the locked table meets insert concurrency, you can insert new data at the end of the table.

InnoDB supports MVCC, but MyISAM does not

InnoDB supports foreign keys, but MyISAM does not

Table primary key > * MyISAM: allows a table without any indexes and primary keys to exist, and the index is the address where the rows are saved. > * InnoDB: if no primary key or non-empty unique index is set, a 6-byte primary key (invisible to the user) will be automatically generated. The data is part of the primary index, and the additional index holds the value of the primary index.

InnoDB does not support full-text indexing, while MyISAM does.

Portability, backup and recovery > * MyISAM: data is stored in the form of files, so it is very convenient in cross-platform data transfer. You can operate on a table separately during backup and restore. > * InnoDB: the free solution can be to copy data files, back up binlog, or use mysqldump, which is relatively painful when the amount of data reaches tens of gigabytes

Storage structure > * MyISAM: each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type. The .frm file stores the table definition. The data file has the extension .MYD (MYData). The index file has the extension .myi (MYIndex). > * InnoDB: all tables are stored in the same data file (or multiple files or independent tablespace files). The size of InnoDB tables is only limited by the size of operating system files, which is usually 2GB.

The difference between varchar and char in 9.mysql and the meaning of 50 in varchar (50)?

The difference between varchar and char: char is a fixed-length type, while varchar is a variable-length type.

The meaning of 50 in varchar (50): store a maximum of 50 bytes

The meaning of 20 in int (20): M indicates the maximumdisplay width (maximum display width) for integer types in int (M). The maximumlegal display width is 255.

The four transaction isolation level names supported by InnoDB in 10.MySQL, and what are the differences between them?

Read Uncommitted (read uncommitted) > > at this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty Read.

Read Committed (read submission) > > this is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (rereadable) > > this is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows when reading data concurrently. But in theory, this leads to another thorny problem: PhantomRead. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control gap locking) mechanism. Note: in fact, multiple versions only solve the problem of unrepeatable reading, while the addition of gap locks (what it calls concurrency control here) solves the problem of phantom reading.

Serializable (serializable) > > this is the highest isolation level, and it solves the problem of phantom reading by forcing transaction sorting so that it is not possible to conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

Let your eyes rest and go on!

11. There is a large field X (for example, text type) in the table, and field X is not updated frequently and is mainly read. What are the benefits of splitting the field into subtables?

If there is a text,blob type in the field, and there is not much access to these fields, putting them together becomes a disadvantage. The record storage of MYSQL database is stored by row, and the block size is fixed (16K). The smaller each record is, the more records are stored in the same block. At this point, large fields should be removed so that efficiency can be improved when dealing with most small field queries. When you need to query large fields, the associated query is inevitable, but it is also worth it. After splitting, the UPDAE of the field will have to UPDATE multiple tables.

What is the row lock of the InnoDB engine in 12.MySQL accomplished (or implemented) by adding to it?

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

What are the global parameters that control memory allocation in 13.MySQL?

Keybuffersize: > * keybuffersize specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values Keyreadrequests and Keyreads, you can see whether the keybuffersize setting is reasonable. The keyreads / keyreadrequests ratio should be as low as possible, and at least 1 SHOW STATUS LIKE'keyread%' is better. (the above state values can be obtained using SHOW STATUS LIKE'keyread%'). > * keybuffersize only works on MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table. You can use the check status value createdtmpdisktables to learn more. For machines with 1 GB of memory, if the MyISAM table is not used, the recommended value is 16m (8-64m) > * keybuffersize setting considerations > 1. The size of a single keybuffer cannot exceed 4G. If you set it to more than 4G, you may encounter the following three bug: > http://bugs.mysql.com/bug.php?id=29446

> http://bugs.mysql.com/bug.php?id=29419

> http://bugs.mysql.com/bug.php?id=5731

> > 2. It is recommended that the keybuffer be set to 1amp 4 of physical memory (for MyISAM pilot), or even 30% of physical memory. If the keybuffersize setting is too large, the system will frequently change pages and degrade system performance. Because MySQL uses the operating system's cache to cache data, we have to leave enough memory for the system; in many cases the data is much larger than the index. > 3. If the machine performance is superior, you can set up multiple keybuffer to allow different keybuffer to cache specialized indexes.

Innodbbufferpool_size > indicates the buffer pool byte size and the memory area of InnoDB cache table and index data. The default value for mysql is 128m. The maximum is related to your CPU architecture, 4294967295 (2 ^ 32-1) in 32-bit operating systems and 18446744073709551615 (2 ^ 64-1) in 64-bit operating systems. > in 32-bit operating systems, the maximum practical size for CPU and operating systems is lower than the maximum set. If the size of the set buffer pool is greater than 1G, the value of setting innodbbufferpoolinstances is greater than 1. > data is read and written very fast in memory, and innodbbufferpoolsize reduces read and write to the disk. The in-memory data is flushed to disk at one time after the data is submitted or the checkpoint condition is met. However, memory is also used by other processes in the operating system or database, and the bufferpool size is generally set to 3-4 to 4-5 of the total memory. If set up incorrectly, memory usage may be wasted or overused. For busy servers, buffer pool is divided into multiple instances to improve system concurrency and reduce read-write cache contention between threads. The size of buffer pool is first affected by innodbbuffer*pool_instances, but of course it is less affected.

Querycachesize > * when mysql receives a query of type select, mysql performs hash calculation on the query to get a hash value, and then matches the hash value into querycache. If there is no match, the hash value is stored in a hash linked list, and the query result set is stored in cache. Each hash node of the linked list storing hash values stores the address of the corresponding query result set in cache. And some related information about the table involved in the query If the same query is matched by the hash value, the corresponding query result set in the cache is returned directly to the client. If any of the data in any of the tables of the mysql changes, the query cache is notified that all the cache of the query associated with the table needs to be invalidated and the memory address occupied is freed. > * query cache advantages and disadvantages > > 1. Resource consumption caused by hash calculation and hash lookup of query statements. Mysql will calculate the hash of each received select type query and then find out whether the cache of the query exists. Although the efficiency of hash calculation and search is high enough, the consumption caused by a query can be ignored, but when it comes to high concurrency and there are thousands of query, the cost of hash calculation and search will be paid more attention; > > 2. The failure of query cache. If the table changes more frequently, the failure rate of query cache will be very high. Table change refers not only to changes in the data in the table, but also to any changes in structure or index; > 3. Query for different sql but the same result set will be cached, which will cause excessive consumption of memory resources. Sql character case, spaces or comments are different, the cache is considered to be a different sql (because their hash values will be different); > > 4. Unreasonable setting of relevant parameters will result in a large number of memory fragments, which will be described later.

Readbuffersize > is the size of the MySQL read buffer. A request for a sequential scan of the table allocates a read buffer and MySQL allocates a memory buffer for it. The readbuffersize variable controls the size of this buffer. If sequential scan requests for tables are very frequent, and you think frequent scans are too slow, you can improve its performance by increasing the value of the variable and the memory buffer size.

14. If there is only one field VARCHAR (N) type in a table and utf8 is encoded, what is the maximum value of N (accurate to an order of magnitude)?

Because each character of utf8 takes up to 3 bytes. The length of the row defined by MySQL cannot exceed 65535, so the maximum value of N is calculated as (65535-1-2) / 3. The reason for subtracting 1 is that the actual storage starts at the second byte, the reason for subtracting 2 is that the actual character length is stored in the list length, and dividing by 3 is due to the utf8 limit: each character takes up to 3 bytes.

* 15. What are the advantages and disadvantages of [SELECT] and [SELECT all fields]?

The former needs to parse the data dictionary, while the latter does not need

The output order of the result is the same as that of the table column, and the latter is in the order of the specified fields.

The table field needs to be renamed, the former does not need to be modified, and the latter needs to be changed

The latter can be optimized by establishing an index, but the former cannot be optimized.

The latter is more readable than the former.

What are the similarities and differences between 16.HAVNG clause and WHERE?

Syntactically: where uses the column names in the table, and having uses select result aliases

Affect the result range: the number of rows that where reads data from the table, and the number of rows that having returns to the client

Index: where can use index, having can not use index, can only operate in temporary result set

Aggregate functions cannot be used after where, and having uses aggregation functions specifically.

17.MySQL when the record does not exist insert, when the record exists update, how to write the statement?

INSERT INTO table (a dint bpene c) VALUES (1m m 2pm 3) ON DUPLICATE KEYUPDATE c=c+1

18.MySQL 's insert and update's select statement syntax SQL insert into student (stuid,stuname,deptid) select 10 book xzm statement journal 3 from student where stuid > 8 position update student an inner join student b on b.stuID=10 seta.stuname=concat (b.stuname, b.stuID) where a.stuID=10 Thank you for your reading, the above is the content of "what are the classic MySQL interview questions". After the study of this article, I believe you have a deeper understanding of what the classic MySQL interview questions have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.