A typical Internet product architecture includes access layer, logical processing layer and storage layer. The storage layer carries the tasks of data landing and persistence, and provides data query function support for the logical processing layer. When it comes to the storage layer, it is necessary to talk about the database, and the degree of database knowledge is also the knowledge point of the interview.

Database is divided into relational database and non-relational database, that is, WE often say SQL and NoSQL, the two database representative products are MySQL and Redis, this time we mainly in the form of interview questions to learn the basic knowledge of relational database MySQL.

The interview begins, so prepare to be grilled on your interviewer’s soul.

Relational database

What is a relational database?

Relational database, refers to the use of a relational model to organize the data of the database, its in the form of rows and columns to store data, in order to facilitate user understanding, relational database this series of rows and columns is called a table, a group of tables constitute a database. The user retrieves data from the database through a query, which is an execution code used to qualify certain areas of the database.

In simple terms, a relational schema is a two-dimensional table model.

What are the advantages of relational databases?

Advantages of relational databases:

  • Easy to understand

    The structure of relational 2d tables is very close to the real world, 2d tables, easy to understand.

  • Support for complex queries Makes it easy to do very complex data queries on a table or across tables using SQL statements.

  • Support transaction reliable transaction processing and transaction integrity, so that the high security performance of data access requirements can be achieved.

The MySQL database

What is the SQL

The Structured Query Language (SQL) is a special purpose programming Language. It is a database Query and programming Language used to access data and Query, update, and manage relational database systems.

What is MySQL?

MySQL is a relational database management system. MySQL is one of the most popular relational database management systems. Common relational databases include Oracle, SQL Server, Access and so on.

In the past, MySQL has become the most popular open source database due to its high performance, low cost and good reliability, and has been widely used in small and medium-sized websites on the Internet.

MySQL and MariaDB?

MySQL was originally developed by The Swedish company MySQL AB. The founder of MySQL was Ulf Mikael Widenius, commonly known by his nickname Monty.

It is now owned by Oracle (ORCL) after being acquired by Oracle (ORCL). Oracle has significantly increased the price of the MySQL Commercial edition, causing some concern in the free software community about whether Oracle will continue to support the MySQL Community edition.

MySQL was founded by the same old guy named Monty who founded MariaDB, a branch project based on MySQL.

MariaDB is intended to maintain a high degree of compatibility with MySQL, ensuring direct substitution with library binary parity, and precise matching with the MySQL API application Program interface (API) and commands. Some of the open source software that used MySQL gradually moved to MariaDB or other databases.

So if you see your company using MariaDB, don’t be in any doubt that it’s MySQL at heart. Learn MySQL and learn MariaDB.

An egg

MariaDB is named after Monty’s youngest daughter Maria, just like MySQL is named after his other daughter My, the two famous databases are named after the two daughters respectively, you or you, the old man 😄🐮🐮🐮

How do I view the current MySQL version?

Run the mysql -v command on the system cli

Connect to the MySQL command line and enter:

> status;

Server:			MySQL
Server version:		5.5.45
Protocol version:	10
Copy the code

Or select the version ();

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | version () | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 5.5.45 XXXXX | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Basic data types

What data types does MySQL have?

MySQL has a variety of data types. Common types are described as follows:

Integer type: BIT, BOOL, TINY INT, SMALL INT, MEDIUM INT, INT, BIG INT

Floating point number types: FLOAT, DOUBLE, and DECIMAL

The value is a string of CHAR, VARCHAR, TINY TEXT, TEXT, MEDIUM TEXT, LONGTEXT, TINY BLOB, BLOB, MEDIUM BLOB, or LONG BLOB

Date type: Date, DateTime, TimeStamp, Time, Year

Other data types: BINARY, VARBINARY, ENUM, SET…

What’s the difference between CHAR and VARCHAR?

CHAR is a fixed length character type and VARCHAR is a variable length character type, which is discussed below based on MySQL5.0 and above.

In common

CHAR(M) and VARCHAR(M) both indicate that the column can store M characters, not bytes!!

CHAR type Characteristics

  • CHAR can store up to 255 characters (note that it is not bytes). Characters have different encoding sets, such as UTF8 encoding (3 bytes), GBK encoding (2 bytes), and so on.
  • forCHAR(M)If the actual stored data length is less than M, MySQL will automatically fill it with space characters to the right, but those Spaces will be removed during the retrieval operation.

VARCHAR type characteristics

  • The maximum length of a VARCHAR is 65535 bytes.
  • The VARCHAR stores the actual string plus 1 or 2 bytes to record the actual length of the string. If the string length is less than 255 bytes, it is recorded in 1 byte, and if the string length is more than 255 bytes, it is recorded in 2 bytes. [12] ^

How many UTF8 encoded Characters can VARCHAR(50) store?

The number of Chinese characters stored depends on the version.

For mysql versions below 4.0, varchar(50) refers to 50 bytes. If you store characters encoded in UTF8 format (each character is 3 bytes), you can only store 16 characters.

In mysql 5.0 or above, varchar(50) refers to 50 characters, whether it is a number, letters or UTF8 encoding Chinese characters, can be stored 50 characters.

Can int(10) and bigint(10) store the same amount of data?

No, for the following reasons:

  • Int can store a four-byte signed integer.
  • Bigint can store eight bytes of signed integers.

So the amount of data that can be stored varies, and the number 10 is just the width of the display. [13] ^

  • The display width specifies the maximum number of digits that Mysql can display. If the number of digits is smaller than the specified width, the left side of the number will be filled with space, which is not easy to see.
  • If a value larger than the display width is inserted, the value can still be inserted and displayed as long as it does not exceed the range of values for the type.
  • Specifies when creating a tablezerofillOption is insufficient to display the width of the section0Fill. If it is 1, it will be0000000001.
  • If no display width is specified, bigint defaults to a width of 20 and int to a width of 11.

Storage engine correlation

What are the types of MySQL storage engines?

Common storage engines include InnoDB storage engine and MyISAM storage engine. InnoDB is the default transaction engine for MySQL.

To view the engines currently supported by a database table, use the following query statement:

The Engine field in the query result table indicates the type of storage Engine. show table status from 'your_db_name' where name='your_table_name';Copy the code

What is the application scenario of InnoDB storage engine?

InnoDB is MySQL’s default “transaction engine” and is set to handle a large number of short-lived transactions, which are mostly committed and rarely rolled back.

What are the features of InnoDB storage engine?

MVCC (MultiVersion Concurrency Control) is adopted to support high Concurrency. In addition, four standard isolation levels are implemented and the next-key locking policy is adopted to prevent phantom reading.

The table of the engine is built based on cluster index, which has high performance for primary key query. The secondary index must contain a primary key column, so if the primary key column is large, all other indexes will be large. Therefore, if the table has a large number of indexes, the primary key should be as small as possible. In addition, InnoDB’s storage format is platform independent.

InnoDB has a number of optimizations such as predictable prereads for disk reads, adaptive Hash index that automatically creates a hash index in memory to speed up reads, and insert buffer that speeds up inserts.

InnoDB supports true hot backup through several mechanisms and tools. MySQL’s other storage engines do not support hot backup. To get a consistent view, you need to stop writing to all tables, and in mixed read-write scenarios, stopping writing may also mean stopping reading.

What are the four features of the InnoDB engine?

Insert buffer

Insert Buffers are used for Insert and update operations on non-clustered indexes. If the index is in the pool, Insert it directly. Otherwise, Insert it into the Insert Buffer. Merge the Insert Buffer and auxiliary index leaf nodes at a certain frequency to merge multiple inserts into one operation to improve the Insert performance of non-clustered indexes.

Double write

Double Write consists of two parts: one is a 2MB Double Write buffer in memory, and the other is 128 consecutive pages of the shared table space on the physical disk, also of 2MB size. When flushing dirty pages from the buffer pool, instead of writing directly to disk, memcpy is used to copy the dirty pages to this region of memory, and then doublewrite the dirty pages to the physical disk of the shared tablespace twice, 1MB at a time, and then call fsync immediately to synchronize the disk. Avoid problems with OS buffered writes.

Adaptive Hash Index

InnoDB will create hash indexes for hot pages based on the frequency and mode of access to improve query efficiency. The index is constructed from B+ tree pages in the cache pool, so it is very fast to build. InnoDB storage engine monitors the queries for each index page on the table. If it is observed that creating a hash index will increase the speed, then create a hash index, so it is called adaptive hash index.

Buffer pool

In order to improve database performance, the concept of cache pool is introduced. The innodb_buffer_pool_size parameter can be used to set the size of the cache pool, and the innodb_buffer_pool_instances parameter can be used to set the number of instances of the cache pool. The cache pool is used to store the following contents:

The types of data pages cached in the buffer pool are: Index pages, data pages, undo pages, Insert buffer, Adaptive Hash Index, InnoDB store lock Info and data Dictionary information.

What are the application scenarios of MyISAM storage engine?

MyISAM is the default storage engine for MySQL 5.1 and earlier. MyISAM offers a number of features, including full-text indexing, compression, spatial functions (GIS), and more, but MyISAM does not “support transaction and row-level locking” and can still be used for read-only data, or for tables that are small enough to tolerate repair operations.

What are the MyISAM storage engine features?

MyISAM “does not support row-level locking but locks the entire table”. A shared lock is applied to all tables that need to be read during reads, and an exclusive lock is applied to all tables that need to be read during writes. But it is possible to insert new records into a table at the same time as a read operation. This is called concurrent insertion.

MyISAM tables can be checked and repaired manually or automatically. However, unlike transaction recovery and crash recovery, there can be some “data loss” and repair operations can be very slow.

For MyISAM tables, indexes can be created based on the first 500 characters of even BLOB and TEXT fields. MyISAM also supports “full-text indexes,” which are indexes created based on word segmentation and can support complex queries.

If the DELAY_KEY_WRITE option is specified, the modified index data is not immediately written to disk at the completion of each modification, but to the key buffer in memory. The corresponding index block is only written to disk when the key buffer is cleaned up or the table is closed. This method can greatly improve the write performance. However, when the database or host crashes, the index is damaged and you need to repair it.

There are five major differences between MyISAM and InnoDB storage engines

  • InnoDB supports things, MyISAM does not
  • InnoDB supports row-level locking while MyISAM supports table-level locking
  • InnoDB supports MVCC, MyISAM does not
  • InnoDB supports foreign keys, MyISAM does not
  • InnoDB does not support full-text indexes, whereas MyISAM does

SELECT COUNT(*) on which engine is faster?

SELECT COUNT(*) is often used to COUNT the total number of rows in a table. It performs faster in the MyISAM storage engine, provided there is no WHERE condition.

This is because MyISAM is optimized for the number of rows in a table. It stores the number of rows in a table with an internal variable. If the query condition does not have a WHERE condition, it is asked how many rows are in the table, and MyISAM can quickly return the result.

InnoDB tables also have a variable that stores the number of rows in the table, but this value is an estimate, so it doesn’t really mean much.

MySQL basic knowledge q&A

What are the three paradigms of database design?

1 normal form: 1NF is the atomicity constraint on properties, requiring properties to be atomic and non-decomposable; (All relational databases satisfy 1NF)

2 normal form: 2NF is a unique constraint on records, requiring records to have a unique identity, that is, the uniqueness of the entity;

3 normal form: 3NF is a constraint on the redundancy of fields, that is, no field can be derived from another field, and it requires that fields have no redundancy. No redundant database design can do this

But there is no redundant database is not necessarily the best, sometimes in order to improve the operation efficiency, it is necessary to lower the standards of paradigm, appropriate reserves the redundant data, particular way is: comply with the third paradigm, when in the conceptual data model design to lower the standard of paradigm of work put into a physical data model design, reduce the paradigm is to increase the field, allowing redundancy.

What are the categories of SQL statements?

  1. DDL: Data Definition Language (CREATE ALTER DROP)
  2. DML: Data manipulation statement (INSERT Update DELETE)
  3. DTL: Data Transaction Statement (COMMIT COLLback savapoint)
  4. DCL: Data Control Statement (Grant REVOKE)

What are the differences between DELETE, DROP, and TRUNCate in database delete operations?

  • Drop a table when it is no longer needed.
  • When the table is retained but all records need to be deleted, use truncate to delete the records in the table.
  • Use delete to delete partial records in a table when a partial record is to be deleted (generally there is a WHERE clause constraint).

What is a MySql view?

A view is a virtual table that does not store data, only dynamic data for the statement at the time of definition.

Create view syntax:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
Copy the code

Parameter description:

  • OR REPLACE: Replaces an existing view if it exists.

  • ALGORITHM: view selection ALGORITHM. The default ALGORITHM is UNDEFINED. MySQL automatically selects the ALGORITHM to use.

  • DEFINER: Specifies the creator or DEFINER of the view. If this option is not specified, the user who created the view is the DEFINER.

  • SQL SECURITY: SQL SECURITY, default is DEFINER.

  • Select_statement: A SELECT statement that creates a view that can SELECT data from a base table or other view.

  • WITH CHECK OPTION: The CASCADED view is CASCADED by default.

What are the advantages of using MySQL views?

  1. Easy to operate. View users do not need to care about the structure, association conditions and filtering conditions of the corresponding table, which is already a result set of filtered compound conditions for users.

  2. Data is more secure. View Users can only access the result set in the view. Views allow users to restrict their access to tables to certain rows and columns.

  3. Data isolation. The impact of source table structure changes on users is masked, and the view structure remains unchanged. [^ 1]

What is the default port number for MySql services?

The default port is 3306

> show variables like ‘port’;

Use DISTINCT to filter multi-column rules?

DISTINCT is used to deduplicate selected data. The single column usage is easy to understand. For example, there is the following data table TAMB:

   name    	   number
   Tencent      1
   Alibaba      2
   Bytedance    3
   Meituan      3
Copy the code

SELECT DISTINCT name FROM table tamb

   name    	  
   Tencent   
   Alibaba    
   Bytedance 
   Meituan  
Copy the code

If you want to duplicate the number column and display the name, you might write the query:

SELECT DISTINCT number, name FROM table tamb

The rule for removing DISTINCT parameters is as follows: All parameters after DISTINCT are treated as a filter condition, that is, the whole (number, name) is removed. Only if the combination of DISTINCT parameters is different, the result is as follows:

      	number   name
         1  Tencent
         2  Alibaba
         3  Bytedance
         3  Meituan
Copy the code

The result does not seem to achieve the desired effect of deweighting, so how to achieve the “number column to re-display the name”? We can use the Group By statement:

SELECT number, name FROM table tamb GROUP BY number

      	number   name
         1  Tencent
         2  Alibaba
         3  Bytedance
Copy the code

What is a stored procedure?

One or more sets of SQL statements that have the following characteristics:

  • Stored procedures can achieve faster execution.

  • Stored procedure can be written with flow control statement, which has strong flexibility and can complete complex judgment and more complex operation.

  • Stored procedures can be leveraged as a security mechanism.

  • Stored procedures can reduce network traffic

    Delimiter separators create procedure | proc proc_name () begin SQL statement end delimiter delimiter. — restore the delimiter, so as not to affect the use of later statements — the default delimiter is; However, in order to be reused throughout the stored procedure, custom delimiters (other than \) are generally required

    show procedure status like “”; Drop PROCEDURE if EXISTS; — Delete the stored procedure

Stored procedures and functions seem to be similar, what’s the difference between them?

Stored procedures and functions are a set of SQL statements that have been compiled in advance and stored in the database. Calling stored procedures and functions can simplify a lot of work of application developers, reduce data transfer between the database and the application server, and improve the efficiency of data processing.

The same

  • Stored procedures and functions are collections of SQL statements that operate on the database for repeatable execution.

  • Stored procedures and functions are cached after compilation, and the next use will directly hit the compiled SQL statement, reducing network interaction and improving efficiency.

The difference between

  • The identifier of a function is function and that of a stored procedure is PROCEDURE.

  • Functions return a single value or table object, while stored procedures do not return a value, but can return multiple values through the OUT parameter.

  • Functions have more restrictions, such as no temporary tables, only table variables, some functions are not available, etc. Stored procedures have fewer restrictions.

  • In general, stored procedures implement more complex functions, while functions implement more specific functions

  • The parameters of a function can only be of type IN. The parameters of a stored procedure can be of type INOUT or INOUT.

  • Stored functions are called using SELECT, and stored procedures are called using call.

I’m a Python developer, and I’ve compiled a series of tutorials for you to learn about the Python system.