MySQL face test

MySQL face test

1. What are the three paradigms of the database?

1. First Normal (1NF): The field is atomic and cannot be re-divided. (All relational database systems satisfy the first paradigm the fields in the database table are single attributes, no further points)
2. The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). Requires that each instance or row in a database table must be divided by a unique region. It is often necessary to add a column to the table to store unique identities for each instance. This unique attribute column is called the primary key or primary key.
3. Satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key information already contained in other tables. > So the third paradigm has the following characteristics: >>1. Each column has only one value >>2. Each row can be distinguished. >>3. Each table does not contain non-primary key information that other tables already contain.

2. What are the database optimization experience?

1. With PreparedStatement, generally higher performance than statement: A SQL sent to the server to execute, involving steps: Grammar check, semantic analysis, compilation, caching.
2. There are foreign key constraints that affect insert and delete performance, and if the program guarantees data integrity, the foreign key is removed when the database is designed.
3. Allow appropriate redundancy in the table, such as the number of replies to a topic post and the last reply time
4. Union ALL is much faster than union, so if you can confirm that the combined two result sets do not contain duplicate data and do not require sorting, then use UNION ALL. The >>union and union ALL keywords combine two result sets into one, but they differ in both usage and efficiency. >1. Processing of duplicate results: union filters out duplicate records after the link is made, and union all does not remove duplicate records. >2. Processing of sorting: the Union will be sorted in the order of the fields; UNION all simply merges two results and returns.

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

1. Normal index: The index is created for the database table
2. Unique index: Similar to a normal index, the difference is that the value of the MySQL database index column must be unique, but allow a null value
3. Primary KEY index: It is a special unique index and is not allowed to have null values. Creating a primary key index at the same time as the table is generally in progress
4. Composite index: In order to further extract the efficiency of MySQL, we should consider the establishment of a composite index. Combine multiple fields from a database table together as a composite index.

4. And what is the working mechanism of indexing in MySQL database?

Database index is a sort of data structure in the database management system, which helps to quickly query and update data in database tables. The implementation of an index typically uses B-trees and their variants

Basic operation commands for 5.MySQL:

1. MySQL is running: Run command service MySQL status on Debian, Run command service mysqld status on Redhat
2. Turn on or stop the MySQL service: Run the Command service mysqld start services; Run the command service mysqld stop stop Service
3. Shell Log in MySQL: Run command mysql-u root-p
4. List all databases: Run command show databases;
5. Switch to a database and work on it: Run the command use databasename;
6. List all tables in a database: show tables;
7. Gets the name and type of all Field objects in the table:D escribe table_name;

6.mysql replication principle and process.

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. * One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates. The process is as follows 1. The master server logs the update to the binary log file. 2. Copy the binary log of the primary server from the server to its own trunk log (replay log). 3. The time from the server redo relay log to apply the update to its own database.

What types of replication are supported for 7.mysql?

1. Statement-based replication: An SQL statement executed on the primary server that executes the same statement from the server. MySQL uses statement-based replication by default and is more efficient. Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.
2. Row-based replication: Copy the changed content past, rather than executing the command from the server again. Support Starting from mysql5.0
3. Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

What is the difference between MyISAM and InnoDB in 8.mysql?

2. Transaction Support > MyISAM: Emphasis is on performance, with atomicity per query, which executes several times faster than the InnoDB type, but does not provide transactional support. > InnoDB: Provides advanced database features such as transaction support transactions, external keys, and more. Transaction Security (Transaction-safe (ACID compliant)) Table with transaction (commit), rollback (rollback), and crash-repair capability (crash recovery capabilities).
4. InnoDB supports row-level locks, while myisam supports table-level locks. >> when you manipulate the MyISAM table, the Select,update,delete,insert statement automatically locks the table, and if the table locking later satisfies insert concurrency, you can insert new data at the end of the table.
6. InnoDB supports MVCC, and MyISAM does not support
8. InnoDB supports foreign keys, while MyISAM does not support
10. Table primary key > MyISAM: Allows tables without any indexes and primary keys to exist, and the index is the address of the save row. > InnoDB: If you do not set a primary key or a non-null unique index, a 6-byte primary key is automatically generated (the user is not visible), the data is part of the primary index, and the attached index holds the value of the primary index.
12. InnoDB does not support full-text indexing, and MyISAM supports it.
14. Portability, backup and Recovery > MyISAM: Data is stored as a file, so it is convenient for cross-platform data transfer. You can operate on a table separately for backup and recovery. > InnoDB: Free solution can be copy data file, backup Binlog, or use mysqldump, when the amount of data reached dozens of G is relatively painful
16. Storage structure > MyISAM: Each MyISAM is stored on disk as three files. The first file name begins with the name of the table, and the extension indicates the file type. frm file stores the table definition. The data file has an extension of. MYD (MYData). The extension of the index file is. MYI (Myindex). > InnoDB: All tables are stored in the same data file (possibly multiple files, or stand-alone tablespace files), and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB.

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

1. The difference between varchar and char: char is a fixed-length type, and varchar is a variable-length type.
2. varchar (50) 50 meaning: up to 50 bytes
3. The meaning of 20 in int (20): M indicates the maximum display width in int (m) for integer types. The maximum legal display width is 255.

What are the four types of transaction isolation level names supported by InnoDB in 10.MySQL, and what are the differences between tiers?

2. READ UNCOMMITTED (Read UNCOMMITTED content) >> at that isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).
4. Read Committed (read submissions) >> This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.
6. Repeatable Read (reread) >> This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control gap Lock) mechanism. Note: In fact, multi-version only solves the non-repeatable reading problem, and the gap lock (which is what it calls concurrency control) solves the Phantom reading problem.
7. Serializable (Serializable) >> This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to collide with each other. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result. <table> <thead> <tr> <th align= "left" > Isolation level </th> <th align= "left" > Dirty read (Dirty Read) </th> <th align= "left" > Non-repeatable read (nonrepeatable read) </th> <th align= "left" > Phantom Read (Phantom Read) </ th> </tr> </thead> <tbody><tr> <td align= "left" > Uncommitted read (READ UNCOMMITTED) </td> &LT;TD align= "Left" > Possible </td> <td align= "left" > Possible </td> <td align= "left" > Possible </td> </ tr> <tr> <td align= "left" > Submitted read (Read committed) </td> <td align= "left" > Impossible </td> <td Align= "Left" > Possible </td> <td align= "left" > Possible </td> </tr> <tr> <td align= "left" > REPEATABLE READ (Repeatable Read) </td> <td align= "left" > Impossible </td> <td align= "left" > Impossible </td> <td Align= "Left" > Possible </td> </tr> <tr> <td align= "left" > Serializable(SERIALIZABLE) </td> <td align= "left" > Impossible </td> <td align= "left" > Impossible </td> <td align= " Left "> Impossible </td> </tr> </tbody></table>

11. The table has a large segment X (for example: Text type), and the field x is not updated frequently to read as the main, what is the benefit of splitting the field into a child table?

If there is a large number of fields (TEXT,BLOB) in the field, and there are not many accesses to these fields, then it becomes a disadvantage to put them together. The record store for the MySQL database is stored on a row, the block size is fixed (16K), and the smaller the record, the more records are stored in the same block. At this point, the large number of paragraphs should be removed, so that most small sections of the query, you can improve efficiency. When you need to query large fields, the associated query at this time is unavoidable, but it is also worthwhile. After splitting, the Updae of the field will update multiple tables.

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

InnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is achieved by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

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

1. KeyBufferSize: > * keyBufferSIZE Specifies the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status value keyReadRequests and keyreads, you know key .BufferThe size setting is reasonable. Proportional KeyReads/keyReadRequests should be as low as possible, at least 1:100,1:1000 better (the above status values can use Show status like ' keyread% ' obtained). > * KeyBuffersize only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used. You can use the Check status value createdTmpDiskTables Learn more. For 1G memory machines, if the MyISAM table is not used, the recommended value is 16M (8-64m) > * keyBufferSize setting considerations >>>1. Single keybuffer size can not exceed 4G, if set more than 4G, you may encounter the following 3 bugs: >>>>> http://bugs.mysql.com/bug.php?id=29446 <br/ > >>>>> http://bugs.mysql.com/bug.php?id=29419 <br/> >>>>>/http bugs.mysql.com/bug.php?id=5731 <br/> >>>2. Recommended KeyBuffer is set to 1/4 of physical memory (for MyISAM engines), or even for 30%~40% of physical memory, if keyBufferThe size setting is too large for the system to change pages frequently, reducing system performance. Because MySQL caches data using the operating system's cache, we have to leave enough memory for the system, and in many cases the data is much larger than the index. >>>3. Multiple keys can be set if the machine is superior in performancebuffer, respectively, to let the different keyBuffer to cache a specialized index

3. InnoDB buffer pool_size > represents the buffer pool byte size, InnoDB the memory area of the cache table and index data. The default value for MySQL is 128M. The maximum value is related to your CPU architecture, on a 32-bit operating system, the maximum value is 4294967295 (2^32-1), the 64-bit operating system, and the maximum value is 18446744073709551615 (2^64-1). > in 32-bit operating systems, the maximum CPU and operating system size is lower than the maximum value set. If the size of the buffer pool is set larger than 1G, the value of InnoDB buffer Pool instances is greater than 1. > * Data read and write in memory very fast, InnoDB buffer Pool Size reduces read and write to the disk. The memory data is flushed to disk only once when the data is submitted or the checkpoint condition is met. However, memory is also used by the operating system or other processes in the database, typically setting the buffer pool size to 3/4 to 4/5 of total memory. If set improperly, memory usage may be wasted or used too much. For busy servers, the buffer pool is divided into multiple instances to increase system concurrency and reduce contention for read and write caching between threads. The size of the buffer pool is first affected by InnoDB buffer pool_instances, which is of course less affected.

5. Query cache size > when MySQL receives a select type of query, MySQL hashes the query to get a hash value, Then through the hash value to the query cache to match, if there is no match, the hash value is stored in a hash list, and the query result set is stored in the cache, Each hash node of the linked list that holds the hash value holds the address of the corresponding query result set in the cache, and some information about the table that is involved in the query, and if the hash value matches the same query, The corresponding query result set in the cache is returned directly to the client. If any of the data in any of the tables in MySQL changes, the query cache is notified that the cache for the query associated with the table is invalidated and frees the memory address that is occupied. > Query Cache Pros and cons >> 1. The hash calculation of the query statement and the resource consumption resulting from the hash lookup. MySQL will hash the query for each received select type and then find out if the query's cache exists, although the hash calculation and lookup efficiency is high enough, the cost of a query can be ignored, but when it comes to high concurrency, When there are thousands of query, the cost of hash calculation and search is paid attention to; >> 2. The failure of the query cache. If the table changes more frequently, it will result in a very high loss of query cache. Table changes not only refer to changes in the data in the table, but also any changes in structure or index; >> 3. query for different SQL but the same result set is cached, which results in a transition in memory resource consumption. SQL is different from character case, space or comment, the cache is considered different SQL (because their hash value will be different); >> 4. The relevant parameter settings are not reasonable and will cause a lot of memory fragmentation, and the related parameter settings will be described later.

7. Readbuffersize > is MySQL read-in buffer sizes. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The readbuffersize variable controls how large this buffer is. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer.

14. If there is only one field varchar (N) type in a table, UTF8 encoding, what is the maximum value of N (accurate to the order of magnitude)?

Because UTF8 each character occupies a maximum of 3 bytes. The length of the MySQL definition row 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 from the second byte, minus 2 because the actual character length is stored in the list length, divided by 3 because of the UTF8 limit: Each character occupies a maximum of 3 bytes.

What are the pros and cons of the 2 formulations of [select *] and [select all Fields]?

1. The former wants to parse the data dictionary, which does not require
2. The resulting output sequence is the same as the table column order, which is in the order of the specified fields.
3. Rename the table field, the former does not need to modify, the latter need to change
4. The latter can be indexed for optimization, the former cannot be optimized
5. The latter is much more readable than the former

What are the similarities and differences between 16.HAVNG clauses and where?

1. Syntax: Where to use the column name in the table, having a select result alias
2. Impact result Range: Where the number of rows of data is read from the table, having the number of rows returned to the client
3. Index: Where can use index, having cannot use index, only in temporary result set operation
4. You cannot use aggregate functions after a where, having a special use of aggregate functions.

17.MySQL when the record does not exist insert, when the record is present, update, how is the statement written?

INSERT into table (a,b,c) VALUES (All-in-all) on DUPLICATE KEY UPDATE c=c+1;

18.MySQL INSERT and UPDATE SELECT statement syntax

' SQL insert into student (Stuid,stuname,deptid) Select Ten, ' Xzm ', 3 from student where Stuid > 8;

Update student A INNER join student B on b.stuid=10 set A.stuname=concat (B.stuname, b.stuid) where a.stuid=10; `

MySQL face test