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

This article introduces the relevant knowledge of "what are the database interview questions that must be mastered?" in the operation of actual cases, 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!

First, why the use of data indexes can improve efficiency

The storage of the data index is orderly.

In an orderly case, there is no need to traverse index records to query a data through an index

In extreme cases, the query efficiency of data index is dichotomy query efficiency, which is close to log2 (N).

Second, the difference between B + tree index and hash index

The B + tree is a balanced multi-fork tree. The height difference from the root node to each leaf node is less than 1, and the pointers of the nodes at the same level are linked to each other, which is orderly, as shown in the following figure:

Hash indexing uses a certain hash algorithm to convert the key value into a new hash value. It does not need to be searched step by step from the root node to the leaf node like the B+ tree, but only needs one hash algorithm, which is unordered, as shown in the following figure:

Third, the advantages of hash indexing:

Equivalent query, hash indexing has an absolute advantage (the premise is: there are not a large number of repeated key values, if a large number of repeated key values, the efficiency of hash indexing is very low, because of the so-called hash collision problem.

4. Scenarios where hash indexing is not applicable:

Range query is not supported

Index completion sorting is not supported

Leftmost prefix matching rules for federated indexes are not supported

5. What is table partition?

Table partitioning refers to the decomposition of a table in a database into smaller, manageable parts according to certain rules. Logically, there is only one table, but the underlying layer is made up of multiple physical partitions

6. what is the difference between a table partition and a sub-table?

Sub-table: refers to the decomposition of a table into several different tables by certain rules. For example, record the user's order into multiple tables according to time.

The difference between a partition and a partition is that a partition logically has only one table, while a sub-table breaks down a table into multiple tables.

What are the benefits of table partitioning?

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Store more data. The data of the partition table can be distributed over different physical devices, thus making efficient use of multiple hardware devices. Can store more data than a single disk or file system

Optimize E inquiry. When partition conditions are included in where statements, only one or more partition tables can be scanned to improve query efficiency; when sum and count statements are involved, they can also be processed in parallel on multiple partitions to summarize the results.

Partition tables are easier to maintain. For example, if you want to delete a large amount of data in bulk, you can clear the entire partition.

Avoid some special bottlenecks, such as mutually exclusive access to a single index of InnoDB, ext3 asking prices for your system's inode lock competition, etc.

8. In MVCC concurrency control, read operations can be divided into two categories:

Snapshot read (snapshot read): read the visible version of the record (possibly the historical version) without locking (shared read lock s lock is not added, so it does not block the writing of other transactions)

Current currentread: the latest version of the record is read, and the record returned by the current read will be locked to ensure that other transactions will not modify the record concurrently

IX. Advantages of row-level locking:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

There are only a few lock conflicts when accessing different rows in many threads.

There are only a small number of changes on rollback

You can lock a single row for a long time.

10. Disadvantages of row-level locking:

Takes up more memory than page-or table-level locking. When used in most tables, locking is slower than page-level or table-level locking because you have to acquire more locks. If you often GROUP BY most of the data or have to scan the entire table frequently, it is significantly slower than other locks. With high-level locking, you can also easily adjust the application by supporting different types of locking because the cost of locking is less than row-level locking.

11. MySQL optimization

Open the query cache and optimize the query

Explain your select queries, which can help you analyze performance bottlenecks in your queries or table structures. EXPLAIN's query results will also tell you how your index primary key is used and how your data tables are searched and sorted.

When using limit 1 when only one row of data is used, the MySQL database engine stops searching after finding a piece of data, rather than continuing to look back for the next piece of data that matches the record.

Index the search field

Use ENUM instead of VARCHAR

Prepared StatementsPrepared Statements, much like a stored procedure, is a collection of SQL statements that run in the background, and we can start from using the

Prepared statements gains a lot of benefits, whether it's performance or security issues.

Prepared Statements can check some variables you bind to protect your program from "SQL injection" attacks.

Vertical subtable

Choose the right storage engine

The difference between key and index

Key is the physical structure of the database, which contains two layers of meaning and function, one is constraint (emphasis on constraint and standardizing the structural integrity of database), and the other is index (for auxiliary query). Including primary key, unique key, foreign key, etc.

Index is the physical structure of the database, it is only auxiliary query, when it is created, it is stored in another table space (innodb table space in mysql) as a directory-like structure. If the index wants to be classified, it can be divided into prefix index, full-text index, etc.

What are the differences between MyISAM and InnoDB in Mysql?

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

InnoDB supports transactions, but MyISAM does not

InnoDB supports foreign keys, but MylSAM does not. Converting an InnoDB table containing foreign keys to MYISAM will fail

InnoDB is a clustered index, data files are tied to the index, there must be a primary key, through the primary key index efficiency.

InnoDB does not save the specific number of rows of the table, and a full table scan is required when performing select count (*) from table.

Innodb does not support full-text indexing, while MyISAM supports full-text indexing. MyISAM is more efficient in query.

Matters needing attention in the creation of database tables

1. The rationality of field name and field configuration

Remove fields that are not closely related; 1 the naming of fields should have rules and corresponding meanings (not part of English, part of Pinyin, and fields with unknown meanings such as a.b.c)

Try not to use abbreviations for field naming (most abbreviations do not specify the meaning of the field)

Fields should not be mixed in uppercase and lowercase (if you want to be readable, multiple English words can be connected by underlining)

Do not use reserved words or keywords for field names

Maintain consistency of field names and types

Choose the number type carefully; leave plenty of room for the text field

2. Treatment of special fields of the system and suggestions after completion.

Add delete tags (e.g. operator, delete time)

Establish a version mechanism

3. Rational configuration of table structure

The treatment of polymorphic fields is whether there are fields in the table that can be broken down into smaller independent parts (for example, people can be divided into men and women).

The processing of multi-valued fields can divide the table into three tables, which makes the retrieval and sorting more conditioned, and ensures the integrity of the data!

4. Other suggestions

For big data fields, separate tables are stored to affect performance (for example, introduction fields)

Use varchar class type instead of char, because varchar dynamically allocates length, and char specifies a fixed length; creating a primary key for a table has a certain impact on query and index definitions for tables without primary keys

To avoid table fields running as null, it is recommended to set the default value (for example: set the default value of int type to 0) for efficiency on index query; 1 to establish an index, it is best to build on only-and non-empty fields, too many indexes will have a certain impact on later insertion and update (consider the actual situation to create)

This is the end of the content of "what are the database interview questions that must be mastered"? 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.