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

1. What is a relational database? What is a non-relational database?

The concept of relational database: it can be understood as a second-dimensional table, and each relationship has a relational name, which is commonly referred to as a table name, which refers to a database organized by a relational model.

Non-relational database: relational database has exposed many insurmountable problems, while non-relational database has developed rapidly because of its own characteristics. The emergence of NoSQL database is to solve the challenges brought by large-scale data collection and multiple data types, especially the application problem of big data.

2. Advantages and limitations of Redis, Memcached and MongoDB?

Memcached

Advantages of Memcached:

Memcached can take advantage of multi-cores, and the throughput of a single instance is extremely high, reaching hundreds of thousands of QPS (depending on the byte size of key and value and the hardware performance of the server, the peak of QPS in daily environment is about 4-6w). Suitable for maximum carrying capacity.

Direct configuration of session handle is supported.

Limitations of Memcached:

Only simple key/value data structures are supported, unlike Redis, which supports rich data types.

Cannot be persisted, data cannot be backed up, it can only be used for cache use, and all data is lost after restart.

Data synchronization cannot be performed and data from MC cannot be migrated to another MC instance.

Memcached memory allocation uses Slab Allocation mechanism to manage memory. When there is a large difference in value size distribution, the memory utilization will be reduced, and problems such as kicking out will still occur when the utilization is low. Users are required to pay attention to value design.

Redis

Advantages of Redis:

Support multiple data structures, such as string (string), list (two-way linked list), dict (hash table), set (collection), zset (sorting set), hyperloglog (cardinality estimation)

Support persistence operation, aof and rdb data can be persisted to disk, thus data backup or data recovery and other operations, a better means to prevent data loss.

Support data replication through Replication, through master-slave mechanism, real-time synchronous data replication, support multi-level replication and incremental replication. Master-slave mechanism is an important means for Redis to carry out HA.

Single-thread request, all commands are executed serially, and data consistency does not need to be considered in the case of concurrency.

Support pub/sub message subscription mechanism, which can be used for message subscription and notification.

Simple transaction requirements are supported, but the industry uses few scenarios and is not mature.

Limitations of Redis:

Redis can only use single thread, and its performance is limited by CPU performance, so the maximum CPU of a single instance can reach 5-6wQPS per second (depending on data structure, data size and server hardware performance, the peak of QPS in daily environment is about 1-2w).

Simple transaction requirements are supported, but the industry uses few scenarios and is immature, which is both an advantage and a disadvantage.

Redis consumes more memory on the string type, so you can use dict (hash Table) compressed storage to reduce memory consumption.

Mogodb

Mogodb is a documented database. First explain the database of the document, that is, you can store the data of the xml, json, and bson types. These data are self-reporting (self-describing), showing a hierarchical tree data structure. Redis can use hash to store simple relational data.

Mogodb stores data in json format.

Suitable for scenarios: event logging, content management, or blog platforms, such as comment systems.

3. What are the two ways to save snapshots in Redis? What's the difference between them?

Snapshot mode and AOF mode

Snapshot mode: save data in memory and then to disk for high performance, but a small amount of data may be lost

AOF mode: poor performance, high consistency requirements, you can choose this method, the general production environment both open

4. What is the main health? What is external health? What is an index? What are the advantages and disadvantages of indexes?

Primary key: the primary keyword (primary key, primary key) is the candidate keyword that is selected to uniquely identify the rows of the table. A table has only one key word. The primary key can also be called the primary key. The primary key can consist of a single field or multiple fields, which can be a single-field primary key or a multi-field primary key.

External key: the foreign key of the table is that this field is associated with other tables and is the primary key of other tables.

Index: the equivalent of a catalogue in a book

Advantage: speed up the query table records

Cons: it slows down the insert upadate delete of table records

The index takes up physical disk space

Db.frm saves the table structure

Db.MYD saves data in the table

Db.MYI saves the index information file

5. What is the function of main health and external health? And explain the main characteristics?

Main function: 1) to ensure the integrity of the entity

2) Speed up the operation of the database

3) when a new record is added to the table, ACCESS automatically checks the primary key value of the new record and does not allow it to duplicate the primary key value of other records.

4) ACCESS automatically displays the records in the table in the order of primary key values. If no primary key is defined, the records in the table are displayed in the order in which they are entered.

Features:

1) there can be only one primary key in a table. If you create a primary key on another field, the original primary key is canceled. In ACCESS, although a primary key is not required, it is best to set a primary key for each table.

2) the value of the primary key cannot be repeated or NULL.

External key function: to associate two tables, foreign keys can only refer to the values of the columns in the appearance!

6. What is a storage engine?

Data in MySQL is stored in files (or memory) using a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of functions and capabilities.

By choosing different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

7. Point out the difference between Innodb and MyIsam in MySQL engine, and how to choose storage engine in production environment?

InnoDB: support row lock, support transaction, support external key, batch insert speed is slow, memory use is high, space use is high, data can be compressed, exclusive table space

MyISAM: table locks are supported, transactions are not supported, external keys are not supported, bulk inserts are fast, memory usage is low, space usage is low, data is incompressible, and table spaces are shared.

In the general business of both reading and writing, it is recommended to choose Innodb engine.

Use MyIsam engine only for reading and unwritten business.

8. What is fantasy reading? What is dirty reading? What is unrepeatable?

Illusion: a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies the data in a table, which involves all rows of data in the table. Meanwhile, the second transaction also modifies the data in this table.

This modification inserts a row of new data into the table. Then later, the user who operates the first transaction will find that there are no modified data rows in the table, as if there were hallucinations.

Dirty reading: dirty reading means that when a transaction is accessing the data and making changes to the data that have not yet been committed to the database, another transaction also accesses the data and then uses the data. In fact, this data has not been committed and can not be used properly.

Unrepeatable: refers to reading the same data multiple times within a transaction. Another transaction also accesses the same data before the transaction finishes. Then, between the two reads in the first transaction, the data read by the first transaction may be different due to the modification of the second transaction. In this way, the data read twice in a transaction is different, so it is called unrepeatable read.

9. What is a transaction? What is rollback?

Transaction: the process from the beginning to the correct execution of a sql operation is called a transaction

Transaction rollback: reverts to the state before all actions are taken

10. What are the four levels of transaction isolation? What do they stand for?

Uncommitted reads (read uncommitted): dirty, unrepeatable, and phantom readings occur.

Commit read (read committed): unrepeatable and illusory readings occur.

Repeatable read: phantom reading occurs.

Serializable: the highest isolation level, no dirty, unrepeatable and phantom reads are allowed.

11. What are the characteristics of the transaction? What do they stand for?

Transactions have four characteristics: Atomicity, Consistency, Isolation and Durability. These four features are called ACID features for short.

Atomicity: a transaction is the logical working unit of a database, and all the operations contained in the transaction are either done or not done.

Consistency: the result of transaction execution must be to change the database from one consistency state to another. So when the database contains only the results of a successful transaction commit, the database is said to be in a consistent state. If a failure occurs in the operation of the database system, some transactions are forced to be interrupted before they are completed, and some of the modifications made by these outstanding transactions to the database have been written to the physical database, and the database is in an incorrect state. Or an inconsistent state.

Isolation: the execution of one transaction cannot be interfered with by other transactions. That is, the operations and the data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions can not interfere with each other.

Persistence: also known as permanent, means that once a transaction is committed, its changes to the data in the database should be permanent. Other operations or failures that follow should not have any impact on the execution results.

12. What are the three default libraries of the database? What do they stand for?

Information_schema: mainly stores some database object information in the system, such as user information, column information, permission information, character set information and partition information, etc.

Performance_schema: mainly stores database server performance parameters

Mysql: user rights information for the primary storage system

Test: this database is a test database created automatically by MySQL database management system, and can be used by any user.

13. What is the granularity of MySQL locks? How many lock levels does MySQL have? Can you tell me about his characteristics?

That's what we usually call the lock level.

MySQL has three levels of locks: page level, table level, and row level.

The characteristics of the three locks of MySQL can be roughly summarized as follows:

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

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average.

14. Tell me about the backup methods of the database and the meaning of package startup.

Cold backup (cold backup): mysql service needs to be turned off, and read and write requests are not allowed.

Warm backup: the service is online, but only read requests are supported, but write requests are not allowed

Hot backup (hot backup): while backing up, the business will not be affected.

15. Briefly describe how to build the master-slave database and work mode?

Build: 1. Modify the my.cnf configuration file of the master database, open the log-bin function, set the server-id, and restart the service.

2. Authorized servers on the master server can connect to themselves from the slave server, and have the right to copy data.

3. Modify the my.cnf file from the server, set server-id, and restart the service.

4. Use authorized users on the slave server to test whether you can connect to the login master database server.

5. Set up synchronization account information from the server

Working mode: connect the IO thread of the main database from the IO thread of the server, and obtain the binary log from the main server and save it as a local relay log.

Then the SQL statement in the relay log is executed through the SQL thread to keep the master and slave libraries consistent.

16. What if the master-slave database cannot be synchronized?

1. Continue to perform synchronization by skipping errors, which is suitable for situations where data consistency is not so high

Set global sql_slave_skip_counter = 1

2. Be the master and slave again and synchronize completely

This method is suitable for situations where there is a large difference between master and slave database data, or when the data is required to be completely unified.

Repair master-slave library in hot standby mode

Operation process

1) shut down the slave database first

2) record the log_file file name and location point of the main library

3) Export the database of the master database and copy it to the slave machine

4) delete the old library from the library and import the new library copied from the main library

5) Log_file and location points of the main changes library

6) Open the slave library

17. How to clean up the database fragments?

Check which tablespace takes up the most space

Move the big table

18. What are the functions and advantages of MariaDB clusters?

Features:

Synchronous replication

Real multi-master, that is, all nodes can read and write to the database at the same time

Automatic node membership control, failure nodes are automatically cleared

New nodes join automatic data replication

True parallel replication, row level

Users can connect to the cluster directly, which is exactly the same as MySQL.

Advantages:

Because it is multi-master, there is no Slave lag (delay)

There is no lost transaction.

Have the ability to read and write at the same time

Smaller client latency

The data between nodes is synchronous, while the Master/Slave mode is asynchronous, and the binlog on different slave may be different

19. What kinds of logs are there in the database and what are their functions?

Binary log: this log file records various database operations in binary form, but does not record query operations.

Error log: this log file records information such as MySQL server startup, shutdown, and runtime errors.

Slow log: record all kinds of operations that take longer than the specified time. By analyzing the slow log, you can determine the bottleneck of MySQL server performance.

General query log: this log records MySQL server startup and shutdown information, client connection information, update data record SQL statement and query data record SQL statement

20. How does the sql lock appear in MySQL and how to optimize it?

Use the show full processlist command to view the locked table and determine if it is the efficiency of the sql statement, the lack of indexing, or the database engine.

21. How to optimize MySQL?

1. Add a primary key or index to a frequently queried table

2. Master or slave high availability and read-write separation for the mysql of important data

3. Separate tables or libraries for tables or libraries with a large amount of data to reduce the total table size.

4. Optimize my.cnf in memory parameters. Improve memory usage.

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.