Shulou(Shulou.com)06/01 Report--

1. What is the three paradigms of database? The first normal form (1NF): fields are atomic and cannot be divided. (all relational database systems satisfy that the fields in the database table of the first normal form are single attribute and cannot be divided.) the second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to satisfy the second normal form (2NF), it must first satisfy 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? General index: create unique index on database table: similar to ordinary index, except that the value of index column in MySQL database must be unique, but null primary key index is allowed: it is a special unique index, no null value is allowed. It is common to create a primary key index combination index at the same time when creating a table: in order to further extract the efficiency of MySQL, it is necessary to consider establishing a combination 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 the index usually uses the basic operation command of B-tree and its variant B + tree 5.MySQL: whether MySQL is running: run the command service mysqlstatus on Debian, 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 stop service Shell login MySQL: run the command mysql-u root-p list all databases: run the command show databases; to switch to a database and work on it: run the command use databasename; to enter the database named databasename and list all tables in a database: show tables; gets the names and types of all Field objects in the table: the replication principle and process of describe table_name;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. Mixed-type replication is supported starting with mysql5.0: statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurate. 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 transactions and foreign keys. 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, while MyISAM does not support InnoDB supports foreign keys, and MyISAM does not support table primary keys > MyISAM: allows tables to exist without any indexes and primary keys, and the index is the address where rows are saved. > InnoDB: if no primary key or non-empty unique index is set, a 6-byte primary key is automatically generated (invisible to the user). 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. > 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 generally 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 type of fixed length, while varchar is a type of variable length. The meaning of 50 in varchar (50): the meaning of 20 in 50 bytes of int (20): M indicates the maximumdisplay width (maximum display width) for integer types in int (M). The four transaction isolation level names supported by InnoDB in The maximumlegal display width is 255.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.

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 you do not use the MyISAM table, the recommended value is 16m (8-64m) > keybuffersize setting considerations > 1. The size of a single keybuffer cannot exceed 4G. If you set 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 key*buffer to cache specific index innodbbufferpool_size > represents buffer pool byte size, InnoDB cache table and index data memory area. 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. > advantages and disadvantages of query cache > > 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 results. The former is in the same order as the table column, and the latter is in the specified field order. Table field renaming, the former does not need to be modified, the latter can be modified the latter can build an index for optimization, the former can not optimize the readability of the latter is higher than the former 16.HAVNG clause and WHERE similarities and differences? Syntactically: where uses the column name in the table, having uses the select result alias to affect the result range: where reads the number of rows of data from the table, and having returns the number of rows on the client side index: where can use the index, having can not use the index, only after the temporary result set operation where can not use the aggregation function, having is dedicated to use the aggregation function. 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

Your concern and forwarding is the motivation for me to move on. Thank you for your reading and appreciation.

Welcome to the official account of Wechat: Murong Qianyu's structure notes. Welcome to follow us and make progress.

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.