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

1. What are the three paradigms of the database?

The first paradigm emphasizes the atomicity of columns, that is, each column of a database table is an indivisible atomic data item.

The second paradigm: requires that the attributes of the entity are completely dependent on the primary keyword. Full dependency means that there can be no attributes that depend on only part of the primary keyword.

The third paradigm: any non-primary attribute does not depend on other non-primary attributes.

two。 There are a total of 7 pieces of data in a self-increasing table. Delete the last 2 pieces of data, restart the MySQL database, and insert another piece of data. What is id at this time?

If the table type is MyISAM, then id is 8.

If the table type is InnoDB, then id is 6.

The InnoDB table only records the maximum id of the self-incrementing primary key in memory, so rebooting will result in maximum id loss.

3. How do I get the current database version?

Use select version () to get the current MySQL database version.

4. Tell me what ACID is.

Atomicity (atomicity): all operations in a transaction, either completed or not completed, do not end in the middle. An error occurs during the execution of a transaction and is Rollback to the state it was in before the transaction started, as if the transaction had never been executed. That is, the transaction is inseparable and irreducible.

Consistency (consistency): the integrity of the database is not compromised before the transaction starts and after the transaction ends. This means that the data written must fully comply with all default constraints, triggers, cascading rollbacks, and so on.

Isolation (isolation): the ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistencies caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted (Read uncommitted), read commit (read committed), repeatable read (repeatable read) and serialization (Serializable).

Durability (persistence): after the transaction is completed, the modification of the data is permanent, even if the system failure will not be lost.

5. What is the difference between char and varchar?

Char (n): fixed-length types, such as subscription char (10), when you type "abc" three characters, they still take up 10 bytes of space, and the other seven are empty bytes.

Chat advantages: high efficiency; disadvantages: take up space; applicable scenarios: store the md5 value of the password, fixed length, it is very suitable to use char.

Varchar (n): variable length, the stored value is the byte occupied by each value plus the length of a byte used to record its length.

Therefore, it is more appropriate to consider varcahr in terms of space and char in terms of efficiency, and the two need to be weighed.

6. What is the difference between float and double?

Float can store up to 8 decimal digits and occupy 4 bytes in memory.

Double can best store 16-bit decimal numbers and occupy 8 bytes in memory.

7. What is the difference between the inner connection, the left connection and the right connection of MySQL?

Internal connection keywords: inner join; left connection: left join; right connection: right join.

The inner join displays the matching associated data; the left join shows all the tables on the left and the table on the right shows the qualified data; the right join is just the opposite.

8. How is the MySQL index implemented?

An index is a data structure that satisfies a specific search algorithm, and these data structures point to the data in some way, so as to find the data efficiently.

Specifically, different data engines implement different indexes in MySQL, but at present, the indexes of mainstream database engines are all implemented by B+ tree. The search efficiency of B+ tree can reach the performance of dichotomy. After finding the data area, we find the complete data structure, and the performance of all indexes is better.

9. How to verify that the index of MySQL meets the requirements?

Use explain to see how SQL executes queries to analyze whether your index meets the requirements.

Explain syntax: explain select * from table where type=1.

10. Tell me about the transaction isolation of the database?

The transaction isolation of MySQL is based on MySQL. Added to the ini configuration file, at the end of the file:

Transaction-isolation = REPEATABLE-READ

Available configuration values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

READ-UNCOMMITTED: uncommitted read, minimum isolation level, can be read by other transactions before the transaction is committed (phantom read, dirty read, non-repeatable read).

READ-COMMITTED: commit read, one transaction commits before it can be read by other transactions (can cause phantom reading, non-repeatable reading).

REPEATABLE-READ: repeatable. The default level ensures that when the same data is read multiple times, its value is the same as that at the beginning of the transaction. It is forbidden to read uncommitted data from other transactions (which will cause illusory reading).

SERIALIZABLE: serialization, the most expensive and reliable isolation level that prevents dirty, unrepeatable, and phantom reads.

Dirty read: indicates that one transaction can read uncommitted data in another transaction. For example, one transaction attempts to insert record A, while the transaction is not committed, and then another transaction attempts to read record A.

Unrepeatable: refers to reading the same data multiple times within a transaction.

Illusion: refers to different result sets returned by multiple queries in the same transaction. Compared to a transaction A, there are n records in the first query, but there are 1 records in the second query under the same conditions, which is like an illusion. The reason for the illusion is that another transaction adds or deletes or modifies the data in the first transaction result set, and when the data content of the same record is modified, the records of all data rows become more or less.

11. Tell me something about the commonly used engines in MySQL?

InnoDB engine: the InnoDB engine provides support for database acid transactions, as well as row-level locks and foreign key constraints. It is designed to handle a database system with big data capacity. When MySQL is running, InnoDB establishes a buffer pool in memory to buffer data and indexes. However, the engine does not support full-text search, and it is relatively slow to start, it will not save the number of rows of the table, so when carrying out the select count (*) from table instruction, you need to scan the whole table. Because the granularity of the lock is small, the write operation will not lock the whole table, so using it in scenarios with high concurrency will improve efficiency.

MyIASM engine: the default engine for MySQL, but does not support transactions, row-level locks and foreign keys. Therefore, when the insert and update statements are executed, that is, when the write operation is performed, the table needs to be locked, which results in inefficiency. However, unlike InnoDB, the MyIASM engine saves the number of rows of the table, so when you make the select count (*) from table statement, you can read the saved values directly without scanning the whole table. Therefore, if the table has far more read operations than write operations and does not require transaction support, MyIASM can be used as the first choice of the database engine.

twelve。 Tell me about MySQL's row lock and table lock?

MyISAM only supports table locks, while InnoDB supports table and row locks. The default is row locks.

Table-level lock: low overhead, fast locking, no deadlock. The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.

Row-level locks: expensive, slow locking, deadlocks will occur. The lock strength is small, the probability of lock conflict is small, and the degree of concurrency is the highest.

13. What about optimistic locks and pessimistic locks?

Optimistic lock: every time you go to get the data, you think that someone else will not change it, so it will not be locked, but when you submit the update, you will determine whether someone else has updated the data during this period.

Pessimistic lock: every time you go to get the data, you think that someone else will change it, so it will be locked every time you get the data, so that others will block it when they want to take the data until the lock is released.

The optimistic lock of the database needs to be implemented by yourself, add a version field to the table, and add 1 to the successful value of each modification, so that you can first compare whether the version you own is consistent with the current version of the database, and if it is inconsistent, you will not modify it, thus realizing the optimistic lock.

14. What are the means of troubleshooting MySQL problems?

Use the show processlist command to view all current connection information.

Use the explain command to query the SQL statement execution plan.

Open the slow query log and view the SQL of the slow query.

15. How to optimize the performance of MySQL?

Create an index for the search field.

Avoid using select * and list the fields you need to query.

Vertical partition table.

Select the correct storage engine.

Conclusion

Encourage yourself and encourage each other with these words. The harder you work, the luckier you are. If you are not the official second generation, the rich second generation or the red second generation, then please remember: diligence is the only shortcut to change your destiny.

You are welcome to leave your views in the message area and discuss and improve together. If today's article gives you new inspiration and a new understanding of the improvement of learning ability, please share it with more people.

Readers are welcome to join the programmer * * knowledge wharf * * technology group and reply "add group" in the official account background.

Cdn.xitu.io/2019/10/2/16d8a808b767b831?imageView2/0/w/1280/h/960/format/webp/ignore-error/1 ">

Guess you still want to see it.

1. What is the purpose of the jvm tuning always asked in the interview? 2. What kind of career plan should a programmer have? It's worth thinking about! 3. 3 minutes of technical information per day | interview questions + answers | jvm (1) 4. 3 minutes of technical information per day | interview questions + answers | jvm (2) 5. 3 minutes daily technical practical information | interview questions + answers | Redis (1)

Follow "programmer knowledge Wharf" and watch more wonderful content.

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.