Recently, I was preparing for the interview and compiled some interview questions.

What are the three driver types for Java mysql?

// Load the database driver
Class.forName("com.mysql.jdbc.Driver");
new com.mysql.jdbc.Driver() ;// Create a driver object to load the database driver
System.setProperty("jdbc.driver"."com.mysql.jdbc.Driver");// The system property specifies the database driver
Copy the code

Mysql lock?

slightly

What is the difference between a clustered index and a non-clustered index?

Clustered indexes: InnoDB uses clustered indexes and myIsam uses non-clustered indexes

  1. A clustered index is an index created with a primary key
  2. Each table can have only one clustered index because the records in a table can only be stored in one physical order. The actual data pages can only be sorted by a B+ tree.
  3. The sorting of table records and indexes is always
  4. Clustered indexes store records that are physically contiguous.
  5. The primary key of a cluster index is inserted much slower than a flying cluster index.
  6. Cluster index is suitable for sorting, because the leaf node of cluster index itself is the index and data are put together in the same order, and the index sequence is the data sequence. The leaf node of the non-clustered index retains a pointer to the data. The index itself is sorted, but the data is not sorted, and the data query consumes additional IO.
  7. Updating the clustered index is expensive because InnoDB is forced to move every updated row to a new location.

Nonclustered index

  1. A leaf node of a non-clustered index is still an index node and retains a pointer to the data block.
  2. Clustered indexes are suitable for sorting, while non-clustered indexes are not suitable for sorting.
  3. A non-clustered index is logically continuous, while a clustered index is physically continuous.

Why is clustering index fast?

Using a clustered index to find the row with the first value ensures that subsequent index rows are in adjacent physical locations.

What are the considerations for building a cluster index?

Do not include frequently modified columns in the clustered index. Because after the code value is changed, the data row must be moved to a new location, the index will be rearranged, resulting in a large waste of resources.

Innodb primary key generation rule (cluster index generation rule)?

User-defined primary keys are preferred. No primary key uses the first unique key as the primary key. There is no unique key to add an implicit row_ID hidden column to the table as the primary key.

What is the maximum number of non-clustered indexes?

249 non-clustered indexes require a lot of hard disk space and memory.

What is the difference between Btree and Hash index?

  1. The btree index uses multiple halve lookup to find the corresponding data block.
  2. Hash index Uses the hash function to calculate the hash value and find the corresponding data in the table.
  3. A hash index is usually more efficient than a Btree.
  4. Hash does not support fuzzy queries, range queries, and left-most matching rules for joint indexes, which are supported by Btrees.

When to build an index

  1. Fields that require sorting, querying, grouping, and joining queries are good candidates for indexing.
  2. If there are many indexes, the data update table will be slower. Try to use fields with large proportion of field values that do not duplicate as indexes. Joint indexes are more efficient than multiple independent indexes.
  3. Frequent query creates index, frequent update does not create index.
  4. When data is added or deleted, the index is dynamically maintained, reducing the data maintenance speed.

Why B+ trees and not B trees, red black trees, B- trees

  1. B + tree non-leaf nodes only store key value information to reduce the height of the B tree.
  2. The red black tree and the B tree are obviously much taller, much less efficient than the B+ tree.
  3. The disadvantage of B trees is that they take up more space because the keys are repeated. However, the spatial disadvantage is acceptable compared to the performance advantage, so B+ trees are more widely used in databases than B trees.
When does an index fail?
  1. The condition is that if or is to take effect, each field of OR must be indexed.
  2. Like begins with the wildcard %
  3. If the column is a string, the query condition must be quoted, otherwise the data type conversion will not use the index.
  4. Indexed columns in WHERE use functions or operations.

Transaction characteristics of the database?

A. ACID B. atomicity C. consistency D. isolation

How are database transactions implemented?

  1. Implemented through pre-write logging, the redo and undo mechanisms are the basis for database transactions.
  2. Redo logs are used to repeat the flush process in case of power outages/database crashes. Redo logs are used to flush data to the database, ensuring transaction persistence.
  3. Undo log is to cancel the operation on the database when the transaction fails, ensuring the atomicity of the database.

What is the isolation level of the database?

  1. Uncommitted read: Dirty read, unrepeatable read, phantom read allowed. Dirty read: It is possible to read uncommitted transaction changes in other sessions
  2. Unrepeatable read (committed read) : Unrepeatable read – unreal read. Unrepeatable read: A can only read the transaction committed by B, but before A finishes, B updates the implicit commit and THEN A reads the transaction again. The unrepeatable read occurs. Only committed data can be read. Most databases, such as Oracle, default to this level (no repeat reads).
  3. Repeatable read: Illusion is allowed. Phantom read: the transaction is enabled and the UPDATE operation of other transaction A is not allowed to read the transaction committed by B. However, B inserts A new row into the table, and then A reads an extra row, resulting in phantom read. Repeatable. Queries within the same transaction are consistent at the start of the transaction, InnoDB default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but phantom reads still exist.
  4. Serialization.

Innodb transaction isolation level

What is the propagation level of Spring transactions?

Spring transaction propagation behavior

Mysql > deep pagination

  1. The join optimization
- the original SQL
SELECT * from dc_base_oms_sku limit 5000000.100;

- optimize SQL
SELECT * from dc_base_oms_sku t1 
INNER JOIN (SELECT id from dc_base_oms_sku limit 5000000.100) t2 on t1.id =t2.id;
Copy the code
  1. The principle of sub-query optimization is the same as above
  2. Bookmarking, which records the identity of the previous page, reducing the limit of where col > ‘XX’ scan lines requires continuous increment (snowflake algorithm), and businesses can accommodate this concept of continuity.
- the original SQL
SELECT * from dc_base_oms_sku limit 5000000.100;

- optimize SQL
SELECT * from dc_base_oms_sku t1  where id  > 1000000 limit 100;
Copy the code

Database query process?

Client – Connector – Query cache – Analyzer – Optimizer – Executor – Engine

What is done at each step of the database query?

  1. Connector: TCP handshake, authentication, showProcessList,wait_timeout Control duration Default 8 hours. As long links continue to open, memory increases.
  2. Database query cache: nothing is easy to fail 8.0 after the cancellation.
  3. Mysql parser: Analyzes lexical errors
  4. Mysql optimizer: determine which index to use, and optimize the order of execution: query first or associative query first, there are many options, and it is up to the optimizer to decide which one to use.
select SQL_NO_CACHE * from B
Copy the code

What’s the difference between B tree and B+ tree, B tree and AVL tree?

  • Avl tree updates and deletes are logN time complexity and then higher
  • A B + tree is an upgraded version of a B tree. The non-leaf nodes of a B tree are redundant, and Pointers are connected between leaf nodes. Range lookups can be made more efficient (there is a pointer to the next leaf node).

How many elements can be stored in a b+ tree node?

A page or multiple of a page, the reason is not wasted. Read one page even if it takes more than one page.

The concept of the page

  1. The basic storage structure of mysql is pages.
  2. One page 16 KB
    • file header 38byte
    • page header 56byte
    • Infimum + supermum 26 bytes Stores records
    • User Records is not sure to store records
    • Free Space is not sure
    • Page Directory is uncertain
    • file tailer 8byte
  3. Individual data pages can form a two-way list.
  4. The records in each data page can form a one-way linked list.
  5. Each data page will generate a page directory for the records stored in its edge. When searching for a record through the primary key, dichotomy can be used to quickly locate the corresponding slot in the page directory, and then traverse the records in the corresponding group of the slot to quickly find the specified record
  6. Use other columns (non-primary keys) as search criteria: Each record in a single-linked list can only be traversed starting with the smallest record.

Extended back table, index overwrite.

Database backup commands

Mysqldump to SQL file, or navicat operation