Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the MySQL interview questions?" in the operation of the actual case, many people will encounter such a dilemma, and then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Difference and advantage comparison between non-relational database and relational database

A non-relational database (which feels like the translation is not very accurate) is called NoSQL, or Not Only SQL, not just SQL. Non-relational database does not need to write some complex SQL statements, its internal storage is in the form of key-value, which can be thought of as a phone book, and each person's name (key) corresponds to the telephone (value). The common non-relational databases are Hbase, Redis, MongoDB and so on. Non-relational database does not need to be parsed by SQL, so its performance is very high; the scalability of non-relational database is relatively strong, and there is no coupling between data, so if you meet the need to add new fields, you can directly add a key-value key-value pair.

Relational databases exist in the form of tables and access data in the form of rows and columns. A series of rows and columns in relational databases are called tables, and numerous tables constitute the database. Common relational databases include Oracle, DB2, Microsoft SQL Server, MySQL and so on. Relational database can support complex SQL query, can reflect the relationship between data and tables; relational database also supports transactions, which is easy to commit or rollback.

The disadvantages between them are satisfied based on each other's advantages.

Four characteristics of MySQL transaction

When it comes to MySQL transactions, I'm sure you can think of four features: atomicity, consistency, isolation, and persistence. Here's a description of the four features of the transaction.

Atomicity (Atomicity): atomicity means that the operations containing transactions in MySQL are either successful or all failed rollback, so if the transaction operation is successful, it must be applied to the database, and if the operation fails, it can not have any impact on the database.

"here is a concept: what is a transaction in MySQL? a transaction is a set of operations that make up the units of this set of operations, either all succeed or all fail, and this feature is a transaction. In MySQL, transactions are implemented at the engine layer, and only databases or tables using the innodb engine support transactions.

Consistency: consistency means that the state of a transaction is consistent before and after execution. For example, if the total money between An and B is 1000 yuan, then no matter how much money is transferred between An and B, the money of the two users must add up to 1000 after the end of the transaction, which is the consistency of the transaction.

Durability: persistence means that once a transaction is committed, the change is permanent, even if the database encounters a special situation such as a failure.

Isolation (Isolation): isolation needs to be emphasized. When multiple transactions are in progress at the same time, dirty reads (dirty read), unrepeatable reads (non-repeatable read), and phantom reads (phantom read) may occur. In order to solve these concurrency problems, the concept of isolation is proposed.

"dirty reading: transaction A reads the updated data of transaction B, but transaction B does not commit, and then transaction B performs a rollback operation, then the data read by transaction An is unrepeatable: transaction A performs multiple read operations, transaction B performs update operations and commits during multiple reads by transaction A, and the data read by transaction An is inconsistent after commit. Illusion: transaction A changes the scores of all students in the database from A-> B. at this time, transaction B manually inserts a record with a score of A. after transaction A has been changed, it is found that there is still a record that has not been modified. then this situation is called phantom reading.

There are four isolation levels for SQL: read uncommitted (read uncommitted), read committed (read committed), repeatable read (repetable read), and serialization (serializable). Let's explain it separately.

Read uncommitted: read uncommitted means that the changes made by a transaction can be seen by other transactions before it is committed.

Read committed: read committed means that changes made by a transaction can only be seen by other transactions after it has been committed.

Repeatable readability: repeatable readability means that the data seen during the execution of a transaction is consistent with the data seen at startup. Uncommitted changes are not visible to other transactions.

Serialization: as the name implies, for the same row of records, write locks are added to write and read locks are added. When there is a read-write lock conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

These four isolation levels can solve three kinds of problems: dirty reading, unrepeatable reading and phantom reading. The summary is as follows

The isolation level from low to high is: read unsubmitted

< 读已提交 < 可重复读 < 串行化 隔离级别越高，越能够保证数据的完整性和一致性，但是对并发的性能影响越大。大多数数据库的默认级别是读已提交(Read committed)，比如 Sql Server、Oracle ，但是 MySQL 的默认隔离级别是 可重复读(repeatable-read)。 MySQL 常见存储引擎的区别 MySQL 常见的存储引擎，可以使用 SHOW ENGINES 命令，来列出所有的存储引擎 可以看到，InnoDB 是 MySQL 默认支持的存储引擎，支持事务、行级锁定和外键。 MyISAM 存储引擎的特点 在 5.1 版本之前，MyISAM 是 MySQL 的默认存储引擎，MyISAM 并发性比较差，使用的场景比较少，主要特点是 不支持事务操作，ACID 的特性也就不存在了，这一设计是为了性能和效率考虑的。 不支持外键操作，如果强行增加外键，MySQL 不会报错，只不过外键不起作用。 MyISAM 默认的锁粒度是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易发生死锁的情况。 MyISAM 会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是 .frm(存储表定义)、.MYD(MYData,存储数据)、MYI(MyIndex,存储索引)。这里需要特别注意的是 MyISAM 只缓存索引文件，并不缓存数据文件。 MyISAM 支持的索引类型有 全局索引(Full-Text)、B-Tree 索引、R-Tree 索引 Full-Text 索引：它的出现是为了解决针对文本的模糊查询效率较低的问题。 B-Tree 索引：所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节点 R-Tree索引：它的存储方式和 B-Tree 索引有一些区别，主要设计用于存储空间和多维数据的字段做索引,目前的 MySQL 版本仅支持 geometry 类型的字段作索引，相对于 BTREE，RTREE 的优势在于范围查找。 数据库所在主机如果宕机，MyISAM 的数据文件容易损坏，而且难以恢复。 增删改查性能方面：SELECT 性能较高，适用于查询较多的情况 InnoDB 存储引擎的特点 自从 MySQL 5.1 之后，默认的存储引擎变成了 InnoDB 存储引擎，相对于 MyISAM，InnoDB 存储引擎有了较大的改变，它的主要特点是 支持事务操作，具有事务 ACID 隔离特性，默认的隔离级别是可重复读(repetable-read)、通过MVCC（并发版本控制）来实现的。能够解决脏读和不可重复读的问题。 InnoDB 支持外键操作。 InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。 和 MyISAM 一样的是，InnoDB 存储引擎也有 .frm文件存储表结构 定义，但是不同的是，InnoDB 的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。 InnoDB 有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。 InnoDB 和 MyISAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。 增删改查性能方面，如果执行大量的增删改操作，推荐使用 InnoDB 存储引擎，它在删除操作时是对行删除，不会重建表。 MyISAM 和 InnoDB 存储引擎的对比 锁粒度方面：由于锁粒度不同，InnoDB 比 MyISAM 支持更高的并发；InnoDB 的锁粒度为行锁、MyISAM 的锁粒度为表锁、行锁需要对每一行进行加锁，所以锁的开销更大，但是能解决脏读和不可重复读的问题，相对来说也更容易发生死锁 可恢复性上：由于 InnoDB 是有事务日志的，所以在产生由于数据库崩溃等条件后，可以根据日志文件进行恢复。而 MyISAM 则没有事务日志。 查询性能上：MyISAM 要优于 InnoDB，因为 InnoDB 在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而 MyISAM 可以直接定位到数据所在的内存地址，可以直接找到数据。 表结构文件上：MyISAM 的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而 InnoDB 的表数据文件为:.ibd和.frm(表结构定义)； MySQL 基础架构 这道题应该从 MySQL 架构来理解，我们可以把 MySQL 拆解成几个零件，如下图所示 大致上来说，MySQL 可以分为 Server层和 存储引擎层。 Server 层包括连接器、查询缓存、分析器、优化器、执行器，包括大多数 MySQL 中的核心功能，所有跨存储引擎的功能也在这一层实现，包括 存储过程、触发器、视图等。 存储引擎层包括 MySQL 常见的存储引擎，包括 MyISAM、InnoDB 和 Memory 等，最常用的是 InnoDB，也是现在 MySQL 的默认存储引擎。存储引擎也可以在创建表的时候手动指定，比如下面 CREATE TABLE t (i INT) ENGINE = ; 然后我们就可以探讨 MySQL 的执行过程了 连接器 首先需要在 MySQL 客户端登陆才能使用，所以需要一个连接器来连接用户和 MySQL 数据库，我们一般是使用 mysql -u 用户名 -p 密码 来进行 MySQL 登陆，和服务端建立连接。在完成 TCP 握手 后，连接器会根据你输入的用户名和密码验证你的登录身份。如果用户名或者密码错误，MySQL 就会提示 Access denied for user，来结束执行。如果登录成功后，MySQL 会根据权限表中的记录来判定你的权限。 查询缓存 连接完成后，你就可以执行 SQL 语句了，这行逻辑就会来到第二步：查询缓存。 MySQL 在得到一个执行请求后，会首先去 查询缓存 中查找，是否执行过这条 SQL 语句，之前执行过的语句以及结果会以 key-value 对的形式，被直接放在内存中。key 是查询语句，value 是查询的结果。如果通过 key 能够查找到这条 SQL 语句，就直接返回 SQL 的执行结果。 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果就会被放入查询缓存中。可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，效率会很高。 但是查询缓存不建议使用 为什么呢？因为只要在 MySQL 中对某一张表执行了更新操作，那么所有的查询缓存就会失效，对于更新频繁的数据库来说，查询缓存的命中率很低。 分析器 如果没有命中查询，就开始执行真正的 SQL 语句。 首先，MySQL 会根据你写的 SQL 语句进行解析，分析器会先做 词法分析，你写的 SQL 就是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串是什么，代表什么。 然后进行 语法分析，根据词法分析的结果， 语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果 SQL 语句不正确，就会提示 You have an error in your SQL syntax 优化器 经过分析器的词法分析和语法分析后，你这条 SQL 就合法了，MySQL 就知道你要做什么了。但是在执行前，还需要进行优化器的处理，优化器会判断你使用了哪种索引，使用了何种连接，优化器的作用就是确定效率最高的执行方案。 执行器 MySQL 通过分析器知道了你的 SQL 语句是否合法，你想要做什么操作，通过优化器知道了该怎么做效率最高，然后就进入了执行阶段，开始执行这条 SQL 语句 在执行阶段，MySQL 首先会判断你有没有执行这条语句的权限，没有权限的话，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。对于有索引的表，执行的逻辑也差不多。 至此，MySQL 对于一条语句的执行过程也就完成了。 SQL 的执行顺序 我们在编写一个查询语句的时候 SELECT DISTINCT < select_list >

FROM

< left_table >

< join_type >

JOIN

< right_table >

ON

< join_condition >

WHERE

< where_condition >

GROUP BY

< group_by_list >

HAVING

< having_condition >

ORDER BY

< order_by_condition >

LIMIT

< limit_number >

Do you know the order in which it is executed? This question will give you an answer.

FROM connection

First of all, when you execute a query against a SELECT statement, you join the tables on both sides of the FROM keyword to form a Cartesian product, which results in a virtual table VT1 (virtual table).

"first of all, let's explain what the Cartesian product is. Now we have two sets A = {0jue 1}, B = {2Magne 3}, B = {2je 3}, then the result of the set A * B is A * B = {(0prit 2), (1m 2), (0pm 3), (1je 3), (0je 4)}; B * A = {(2je 0), {2jol 1}, {3Q 0}, {3pm 1}, {4pm 0}, (4pm 1)} The above results of A * B and B * A can be called the Cartesian product of the multiplication of two sets, and we can conclude that the multiplication of A set and B set contains the sum of the elements in set An and the elements in set B. that is, the number of An elements * the number of B elements.

Let's explain what a virtual table is.

"in MySQL, there are three types of tables, one is permanent tables, which are created to store data for a long time in the future. One is temporary tables, and there are also two types of temporary tables. One is that, like permanent tables, only temporary data is saved, but it can exist for a long time. The other is temporarily created, and the SQL statement will be deleted after the completion of execution. One is the virtual table, which is actually the view, and the data may come from the execution results of multiple tables.

ON filtering

Then the results of the FROM connection are filtered by ON, the VT2 is created, and the conditions that meet the record are stored in VT2.

JOIN connection

The third step, if it is OUTER JOIN (left join, right join), then this step will add external rows, if it is left join, add the left table of the ON filter condition, and if it is right join, add the right table to generate a new virtual table VT3.

WHERE filtering

The fourth step is to execute the WHERE filter to reference WHERE filtering on the virtual table produced in the previous step to generate the virtual table VT4.

The difference between WHERE and ON

If there are external columns, ON filters the associated table, and the main table (reserved table) returns all the columns

If no external columns are added, the effect of the two is the same

Application

WHERE should be used to filter the main table

For related tables, ON is used for conditional query before join, and WHERE is used for conditional query after join.

GROUP BY

According to the columns in the group by clause, the records in VT4 are grouped to produce the virtual machine table VT5. If group by is applied, then all subsequent steps can only get the columns of VT5 or aggregate functions (count, sum, avg, etc.).

HAVING

The word GROUP BY is followed by HAVING, which uses HAVING filtering to put eligible ones on VT6.

SELECT

In the seventh step, the SELECT statement is executed and the result in VT6 is brushed according to SELECT to generate VT7.

DISTINCT

In step 8, the records generated by TV7 are de-duplicated to generate VT8. In fact, if you apply the group by clause, then distinct is redundant, also because when grouping is to group the only values in the column into groups, and only return a row of records for each group, then all records will be different.

ORDER BY

Apply the order by clause. Sorts VT8 by order_by_condition, which returns a cursor instead of a virtual table. Sql is based on the theory of set, the set does not sort its rows in advance, it is just a logical collection of members, and the order of members does not matter.

The procedure for executing the SQL statement is as follows

What is a temporary table and when to delete it

What is a temporary watch? When MySQL executes SQL statements, it usually temporarily creates tables that store intermediate result sets. Temporary tables are only visible to the current connection, and when the connection is closed, temporary tables are deleted and all tablespaces are freed.

There are two kinds of temporary tables: one is memory temporary table and the other is disk temporary table. What's the difference? Memory temporary tables use the MEMORY storage engine, while temporary tables use the MyISAM storage engine.

"MEMORY storage engine: memory is a special type of storage engine in MySQL that uses content stored in content to create tables, and the data is all in memory. Each table based on the MEMORY storage engine actually corresponds to a disk file. The file name of the file is the same as the table name, and the type is frm. The data files are stored in memory, which is conducive to the rapid processing of data and improve the efficiency of the whole table. MEMORY is rarely used because it stores data in memory, and if an exception occurs in memory, it will affect the data. If you restart or shut down, all data will disappear. Therefore, MEMORY-based tables have a short life cycle and are generally one-time.

MySQL generates temporary tables in the following situations

Using UNION queries: there are two kinds of UNION, one is UNION and the other is UNION ALL, both of which are used for federated queries; the difference is that using UNION removes duplicate data from both tables, which is equivalent to distinct the result set. With UNION ALL, all rows are returned without being weighed. Using UNION queries produces temporary tables.

Use the TEMPTABLE algorithm or the view in the UNION query. The TEMPTABLE algorithm is an algorithm for creating a temporary table, which places the results in the temporary table, which means that MySQL has to create a temporary table first, then put the results into the temporary table, and then use the temporary table to query accordingly.

Temporary tables are also generated when the clauses of ORDER BY and GROUP BY are different.

When querying with DISTINCT and adding ORDER BY

When SQL uses the SQL_SMALL_RESULT option; if the query result is small, you can optimize it by adding SQL_SMALL_RESULT to generate temporary tables.

Subquery in FROM

In the Extra column where EXPLAIN looks at the results of the execution plan, if you use Using Temporary, you will use temporary tables.

Common index types of MySQL

An index is a data structure stored on a specific column in a table, and the index is created on the column. Moreover, the index is a data structure.

In MySQL, there are mainly the following indexes

Global index (FULLTEXT): global index. At present, only MyISAM engine supports global index. It appears to solve the problem of low efficiency of fuzzy query for text.

Hash index (HASH): the hash index is the data structure of the only key-value key-value pair used in MySQL, which is suitable for use as an index. HASH indexes have the advantage of one location, and do not need to look up node by node like a tree, but this kind of lookup is suitable for finding a single key, and the performance of HASH indexes is very low for range lookups.

B-Tree index: B means Balance, BTree is a balanced tree, it has many variants, the most common is B + Tree, which is widely used by MySQL.

R-Tree index: R-Tree is rarely used in MySQL and only supports the geometry data type. The only storage engines that support this type are MyISAM, BDb, InnoDb, NDb and Archive. Compared with B-Tree, R-Tree has the advantage of range search.

Differences and usage scenarios between varchar and char

There is no nvarchar data type in MySQL, so the difference between varchar and char is directly compared.

Char: represents a fixed-length string, when you enter less than the specified number, for example, the specified number is char (6), when you enter less than 6 characters, char will fill in the blank value after your last character. When you enter more than the specified maximum allowable length, MySQL will report an error

Varchar:varchar refers to character data with a variable length of n bytes and is not Unicode. The n value is a value between 1 and 8000. Storage size is the actual size.

"Unicode is a character coding scheme that sets a unified and unique binary code for each character in each language to achieve the requirements of cross-language and cross-platform text conversion and processing.

It is very convenient to use char to store fixed-length data, and the efficiency of char retrieval is high. No matter whether you store 10 bytes of data or not, you have to take up 10 bytes of space.

Longer data can be stored using varchar, but it is not as efficient as char.

What is inner connection, outer connection, cross connection, Cartesian product

There are three main ways to connect: external connection, inner link and cross connection.

External connection (OUTER JOIN): there are three types of external connection: left external connection (LEFT OUTER JOIN or LEFT JOIN), right external connection (RIGHT OUTER JOIN or RIGHT JOIN), full external connection (FULL OUTER JOIN or FULL JOIN)

Left outer join: also known as left join, this connection will show the data rows of the left table that do not meet the conditions, and the data rows that do not meet the conditions on the right will directly display NULL

Right outer join: also known as the right join, which is opposite to the left connection. This connection will show the data rows of the right table that do not meet the conditions, and the data rows of the left table that do not meet the conditions will directly display NULL.

MySQL does not support full external connection.

Inner join (INNER JOIN): combines the same fields in two tables to return records with matching associated fields.

Cartesian product (Cartesian product): I mentioned Cartesian product above. For convenience, let's list it again.

"now we have two sets A = {0rect 1}, B = {2recorder 3, 4}. Then, the result of the set A * B is A * B = {(0pen2), (1), (0), (1), (1), (4), (1)}; B * A = {(2) 0), {2) 1}, {3) 0}, {3)}, (4)} The above results of A * B and B * A can be called the Cartesian product of the multiplication of two sets, and we can conclude that the multiplication of A set and B set contains the sum of the elements in set An and the elements in set B. that is, the number of An elements * the number of B elements.

The original text of cross-join is Cross join, which is the realization of Cartesian product in SQL. The keyword CROSS JOIN is used in SQL to represent cross-join. In cross-join, any addition of a table field will have a great impact on the result.

SELECT * FROM t_Class a CROSS JOIN t_Student b WHERE a.classid=b.classid

Or without CROSS JOIN, you can directly use FROM to express the effect of cross-connection.

SELECT * FROM t_Class a, t_Student b WHERE a.classid=b.classid

If there are many fields in the table, it is not suitable to use cross-connection, and the efficiency of cross-connection is relatively poor.

Full join: full join is not supported in full join,MySQL, but other join queries can be used to simulate full join, and UNION and UNION ALL can be used to simulate it. For example

(select colum1,colum2...columN from tableA) union (select colum1,colum2...columN from tableB) or (select colum1,colum2...columN from tableA) union all (select colum1,colum2...columN from tableB)

Considerations for using UNION and UNION ALL

"when the number of columns taken out separately by the SQL connected through union must be the same, when using union, multiple equal rows will be merged. Because merging is time-consuming, union is generally not used for merging, but union all is usually used for merging.

Talking about the experience of SQL Optimization

No matter which condition is used in the query statement is equal to, less than, or greater than, the conditional query field on the left side of WHERE should not use functions or expressions.

Use the EXPLAIN command to optimize your SELECT query. For complex and inefficient sql statements, we usually use explain sql to analyze this sql statement, which is convenient for us to analyze and optimize.

When your SELECT query requires only one record, use LIMIT 1

Instead of using SELECT * directly, you should use the table fields that need to be queried, because when using EXPLAIN for analysis, SELECT * uses a full table scan, that is, type = all.

Set an ID property for each table

Avoid making NULL judgments on fields in WHERE sentences

Avoid using the! = or operator in WHERE

Use BETWEEN AND instead of IN

Create an index for the search field

Choose the right storage engine, InnoDB, MyISAM, MEMORY, etc.

Using LIKE abc% will not walk the index, but using LIKE abc% will.

For fields of enumerated types (that is, fields with fixed list values), it is recommended to use ENUM instead of VARCHAR, such as gender, week, type, category, etc.

Split large DELETE or INSERT statements

Select the appropriate field type, the selection criteria is as small as possible, as long as possible, and use integers as much as possible.

Field design uses NOT NULL as much as possible

Cut horizontally or split vertically

"horizontal split: to store data vertically by creating several tables with the same structure: put the fields that are often used together in a separate table, and there is an one-to-one correspondence between the split table records.

This is the end of the content of "what are the MySQL interview questions"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.