Linux Operations (database project) face questions

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

Relational database concepts: can be understood as a second-dimensional table, each relationship has a relationship name, is usually said table name, refers to the love of the relational model to organize the database

Non-relational database: The relational database exposes a lot of difficult problems, and the non-relational database has been developed very rapidly because of its own characteristics. NoSQL databases are created to address the challenges of multiple data types in large-scale data sets, especially big data application challenges.

2. What are the advantages and limitations of Redis, memcached, and MongoDB?

Memcached

Advantages of memcached:

The memcached can take advantage of multicore advantages, with a very high single-instance throughput of hundreds of thousands of QPS (depending on key, the byte size of value, and server hardware performance, which is around 4-6w in the daily environment). Suitable for maximum carrying capacity.

Supports direct configuration for session handle.

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 data is lost after reboot.

Data synchronization cannot be performed and data in the MC cannot be migrated to another instance of MC.

Memcached memory allocation uses the slab allocation mechanism to manage memory, which causes memory utilization to decrease when the value size distribution is large, and causes problems such as kicking out when low utilization occurs. Users need to focus on value design.

Redis

Advantages of Redis:

Supports multiple data structures such as String (string), list (doubly linked list), Dict (hash table), set (set), Zset (sort set), Hyperloglog (cardinality estimation)

Support persistent operation, AOF and RDB data can be persisted to disk, so as to carry out data backup or data recovery operations, better prevent data loss means.

Support the data replication through replication, through the master-slave mechanism, can be in real-time synchronous replication of data, support multi-level replication and incremental replication, master-slave mechanism is an important means of Redis ha.

Single-threaded requests, all commands are executed serially, and there is no need to consider data consistency issues in the concurrency scenario.

Support PUB/SUB Message subscription mechanism, can be used for message subscription and notification.

Supports simple transaction requirements, but industry usage scenarios are rare and immature.

Limitations of Redis:

Redis can only use a single thread and performance is limited to CPU performance, so the single instance CPU can reach 5-6wqps per second (depending on data structure, data size and server hardware performance, and the QPS peak in daily environments is around 1-2w).

Support simple transaction requirements, but the industry use of the scene is very small, immature, both advantages and disadvantages.

Redis consumes more memory on string types, and can use Dict (hash table) to compress storage to reduce memory consumption.

Mogodb

Mogodb is a document-type database. First explain the database of the document, that is, you can store XML, JSON, Bson type of data. These data are self-describing (self-describing), presenting hierarchical tree-like data structures. Redis can store simple relational data in hash.

MOGODB Store JSON-formatted data.

Suitable for scenarios: event logging, content management or blogging platforms, such as commenting systems.

3. What is the way Redis two saves snapshots? What difference do they have?

Snapshot mode and aof mode

Snapshot mode: Save data in memory, save to disk, high performance, but there may be a small amount of data loss

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

4. What is the main health? What is an external health? What is an index? What are the pros and cons of indexes?

Key: The primary key (primary key, primary key) is a candidate keyword that is singled out as the unique identifier for the row of the table. A table has only one primary key. The primary key can also be called the primary key. A primary key can consist of one field or multiple fields, each of which becomes a single-field primary key or a multiple-fields primary key.

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

Index: Equivalent to a directory in a book

Advantages: Speed up the query table record

Disadvantage: Slows down write (insert upadate delete) to table record

Index consumes physical disk space

DB.FRM Save Table structure

Db. MYD Saving table Data

Db. MYI Saving index information files

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

Main health function: 1) Ensure the integrity of the entity;

2) speed up the operation of the database

3) When you add a new record to a table, Access automatically checks the primary key value of the new record and does not allow the value to repeat with the primary key value of other records.

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

Characteristics:

1) There can be only one primary key in a table. If you establish a primary key on another field, the original primary key is canceled. In Access, although the primary key is not required, it is a good idea to set a primary key for each table.

2) The value of the primary key cannot be duplicated, nor is it null (NULL).

External health effect: make two tables to form an association, foreign key can only reference the value of the column in the outside!

6. What is a storage engine? How do you choose a storage engine in your production environment?

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

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

7. What is Phantom reading? What is dirty reading? What is non-repeatable reading?

Phantom read: A phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table This modification involves all rows of data in the table. The second transaction also modifies the data in this table

This modification inserts a new row of data into the table. Then it happens that the user who operates the first transaction finds that there are no modified rows of data in the table, as if the illusion had occurred.

Dirty read: Dirty read refers to when a transaction is accessing the data and the data has been modified and this modification has not yet been committed to the database, then another transaction also accesses the data, and then use this data, the actual data is not committed and not normal use

Non-repeatable read: Refers to reading the same data multiple times within a transaction. Another transaction also accesses the same data when the transaction has not ended. Then between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This way, the data that is read two times within a transaction is not the same and is therefore called non-repeatable read.

8. What is a transaction? What is rollback?

Transactions: Your one-time SQL operation from the start to the correct execution of the junction rate is called a transaction

Transaction rollback: Revert to the state before all actions are not operational

9. What are the four types of four levels of transaction isolation? What do you mean by each other?

READ UNCOMMITTED: Dirty reads, non-repeatable reads, and phantom reads occur.

Read committed: Non-repeatable read and Phantom reads occur.

Repeat read (REPEATABLE Read): Phantom reads occur.

Serialization (Serializable): The highest isolation level, dirty reads, non-repeatable reads, and Phantom reads are not allowed.

10. What are the major features of the transaction? What do you mean by each other?

A transaction has four characteristics: atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability). These four properties are referred to as ACID properties.

Atomicity: A transaction is a logical unit of work for a database, and each operation contained in a transaction is either done or not

Consistency: The result of a transactional execution must be to change the database from one consistent state to another. Therefore, when the database contains only the results of successful transaction commits, the database is said to be in a consistent state. If a database system fails in operation and some transactions have not yet been completed, some of the modifications made to the database have been written to the physical database, and the database is in an incorrect state, or an inconsistent state.

Isolation: Execution of one transaction cannot interfere with other transactions. That is, the operations within one transaction and the data used are isolated from other concurrent transactions, and the transactions performed concurrently cannot interfere with each other.

Sustainability: Also known as permanence, when a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on its execution results

11. What library is the default three library for a database? What do you mean by each other?

INFORMATION_SCHEMA: Some database-to-image information such as user information, column information, permission information, character set information and partition information in the primary storage system

Performance_schema: Primary Storage Database Server performance parameters

MySQL: User rights information for primary storage systems

Test: The database is automatically created for the MySQL database management system, and can be used by any user

12. What is the granularity of MySQL lock? How many lock levels does MySQL have? What about his character?

is usually what we call the lock level.

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

MySQL features of these 3 types of locks can be broadly summarized as follows:

Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.

Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.

Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, lock granularity bounds between table and row locks, and concurrency is common

13, say the database of several backup ways and Baoqi meaning?

Cold (cold Backup): Need to shut down the MySQL service, read and write requests are not allowed in the state;

Win Bei (warm backup): Service online, but only support read requests, do not allow write requests;

Hot backup: The business is not affected at the same time as the backup.

14, how to build master-slave database and working mode?

Build: 1, Main database modify MY.CNF configuration file, open Log-bin function, set Server-id, restart service

2, on the primary server authorized users can connect from the server from the self, and have the right to copy data

3. Modify the My.cnf file from the server, set the Server-id, restart the service

4. Test if you can connect to the primary database server by using an authorized user from the server

5. Create synchronization account information from the server

Operating mode: From the server's IO thread to the IO thread of the primary database, and get the binary log from the master server, save as local trunk log,

The SQL statements in the relay log are then executed through the SQL thread, thus keeping the master-slave library consistent

15, master and slave database can not synchronize how to do?

1, by skipping the error to continue to perform synchronization, for the data consistency is not so high case

Set global sql_slave_skip_counter = 1;

2, re-master from, fully synchronized

This method is suitable for the case that the master-slave database data is large or the data is completely unified.

Hot-Standby Repair master-Slave Library

Operation Process

1) Close the database first

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

3) Export the database of the main library and copy it to the slave library machine.

4) Delete the previous old library from the library, and enter the new library that was copied from the main library.

5) Log_file and location points of the changes main library

6) Open from library

16. How to clean up database fragments?

See which table space is most space-intensive

Move to a large table

17. What are the functions and advantages of the MARIADB cluster?

Function:

Synchronous replication

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

Automatic node member control, failed nodes are automatically cleared

New node joins data automatic replication

True parallel replication, row-level

Users can connect directly to the cluster, using the same feeling on MySQL exactly

Advantage:

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

There is no loss of transactions

Ability to expand both read and write

Smaller client latency

Data between nodes is synchronous, and Master/slave mode is asynchronous, and binlog on different slave may be different

18. Point out the difference between InnoDB and MyISAM in MySQL engine, which one is good?

InnoDB: Support row locks, support transactions, support external health, BULK insert slow, high memory usage, high space usage, data compression, exclusive table space

MyISAM: Support table lock, do not support transactions, not support external health, BULK insert speed, low memory usage, low space usage, data is not compressed, shared table space

In the general of both read and write business, it is recommended to choose the InnoDB engine.

19, MySQL appears SQL Lock is how, how to optimize?

Use show full processlist to view the command for the lock table, determine whether it is an efficiency issue with the SQL statement, whether it is not indexed, or a problem with the database engine.

20. How to optimize MySQL?

1. Table with frequent queries plus primary key or index

2. High availability and read/write separation of important data from MySQL master or slave

3, the data volume of the table or library, the sub-table or sub-Library, reduce the total table size.

4, optimize my.cnf in memory parameters. Increase the memory usage.

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1788484

Linux Operations (database project) face questions