It’s already June. In order to go to a good company in September, even a big company like Ali and Byte, I’m going to prepare the back test questions. Starting today, I will check in once a day.
What kind of locks are there in MySQL?
- Table-level locks: low overhead and fast locking; no deadlocks; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency.
- Row-level lock: high overhead, slow lock; deadlock will occur; the smallest locking granularity, the lowest probability of lock conflicts, and the highest degree of concurrency.
- Page locks: The overhead and lock time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the degree of concurrency is average.
What are the different tables in MySQL?
There are 5 types of tables:
Briefly describe the difference between MyISAM and InnoDB in the MySQL database
- Does not support transactions, but each query is atomic; supports table-level locks, that is, each operation locks the entire table; stores the total number of rows in the table;
- A MYISAM table has three files: index file, table structure file, data file;
- With a nonclustered index, the data field of the index file stores a pointer to the data file. The auxiliary index is basically the same as the main index, but the uniqueness of the auxiliary index is not guaranteed.
- Supports ACID transactions, supports four isolation levels of transactions; supports row-level locks and foreign key constraints: therefore can support write concurrency; does not store the total number of rows:
- An InnoDb engine is stored in a file space (shared table space, table size is not controlled by the operating system,
- A table may be distributed in multiple files), or multiple (set to independent table space, the table size is limited by the operating system file size, generally 2G), and is limited by the operating system file size;
- The primary key index uses a clustered index (the data field of the index stores the data file itself), and the data field of the secondary index stores the value of the primary key; therefore, to find data from the secondary index, you need to find the primary key value through the secondary index first, and then access the secondary index; preferably Use the auto-increment primary key to prevent large adjustments to the file in order to maintain the B+ tree structure when inserting data.
The names of the four transaction isolation levels supported by InnoDB in MySQL
- Read uncommitted (RU): When a transaction is not committed, the changes it makes can be seen by other transactions.
- Read Commit (RC): After a transaction is committed, the changes it makes will be seen by other transactions.
- Repeatable read (RR):. During the execution of a transaction to see the data, you always see with this transaction start data is consistent when the
course in under Repeatable Read isolation level, uncommitted change but also on other matters Invisible.
- Serialization (S): For the same row of records, both read and write will be locked. When there is a read-write lock conflict, the transaction that is accessed later must wait
for the completion of the previous transaction before it can continue.
What is the difference between CHAR and VARCHAR?
1. CHAR and VARCHAR types are different in storage and retrieval.
2. The length of the CHAR column is fixed to the length declared when the table is created. The length value range is 1 to 255. When CHAR values are stored, they are filled with spaces to a specific length , It is necessary to remove trailing spaces when retrieving CHAR values.
What is the difference between primary key and candidate key?
Each row of the table is uniquely identified by the primary key, and a table has only one primary key.
The primary key is also a candidate key. By convention, candidate keys can be designated as primary keys and can be used for any foreign key references.
Briefly describe the difference between indexes, primary keys, unique indexes, and joint indexes in MySQL, and how they affect the performance of the database (from both read and write)
- Indexes are a special kind of files (indexes on InnoDB data tables are an integral part of the table space), they contain reference pointers to all records in the data table.
- The only task of an ordinary index (an index defined by the keyword KEY or INDEX) is to speed up data access.
- Ordinary indexes allow the indexed data column to contain duplicate values. If you can be sure that a data column will only contain values that are different from each other, you should use the keyword
UNIQUE to define it as a unique index when creating an index for this data column . In other words, the unique index can guarantee the uniqueness of the data record.
- The primary key is a special unique index. Only one primary key index can be defined in a table. The primary key is used to uniquely identify a record and is created using the keyword PRIMARY KEY.
- An index can cover multiple data columns, such as an INDEX (columnA, columnB) index, which is a joint index.
- Indexes can greatly increase the speed of data query, but will reduce the speed of inserting, deleting, and updating tables, because when performing these write operations, the index file is also operated.
Ways to optimize the database
1. Select the most applicable field attribute, reduce the width of the defined field as much as possible, and try to set the field as NOTNULL, such as'Province' and'Gender', it is best to use ENUM
2. Use JOIN to replace subqueries
3. Apply union ( UNION) instead of manually created temporary tables
4, transaction processing
5, locking tables, optimizing transaction processing
6, applying foreign keys, optimizing locking tables
7, creating indexes
8, optimizing query statements
What field type is good for recording currency in MySQL
The NUMERIC and DECIMAL types are implemented by MySQL as the same type, which is allowed in the SQL92 standard. They are used to store values whose accuracy is extremely important, such as data related to money. When declaring a class to be one of these types, the precision and scale can be (and usually are) specified.
In this example, 9 (precision) represents the total number of decimal places that will be used to store the value, and 2 (scale) represents the number of decimal places that will be used to store
the value. Therefore, in this case, the range of values that can be stored in the salary column is from -9999999.99 to
What is the difference between NOW() and CURRENT_DATE()?
The NOW() command is used to display the current year, month, date, hour, minute, and second. CURRENT_DATE() only displays the current year, month and date.
What are the parts of MySQL and what are they used for?
connector: management connection, permission verification.
Analyzer: lexical analysis, grammatical analysis.
Optimizer: execution plan generation, index selection.
Executor: operation storage engine, return execution results.
- Storage engine: Store data and provide read and write interfaces.
Briefly talk about the database paradigm
- First Normal Form: Attributes cannot be divided.
- Second paradigm: On the basis of a paradigm, each instance or row in the database table must be uniquely distinguishable. It is usually necessary to add a column to the table to store the unique identification of each instance.
This unique attribute column is It is called the primary key or primary key.
- The third normal form: On the basis of the second normal form, it is required that a database table does not contain the non-primary key information already contained in other tables. So the third normal form has the following characteristics: 1).
Each column has only one value. 2 ). Each row can be distinguished. 3). Each table does not contain non-primary key information that other tables already contain.