MySQL Interview review summary

three paradigms

Three-paradigm definition (paradigm and inverse paradigm)

1NF: Each data item is the smallest unit, indivisible, and only one data can be determined after the column is identified.

2NF: Each non-primary attribute is completely dependent on the candidate Code (the value of the attribute group can uniquely identify a tuple, but its subset is not possible). ?

3NF: Each non-primary property does not pass a code -dependent, or part-dependent code (main code = candidate code for multiple municipalities, from which one is chosen as the main code).

BCNF: The primary attribute (one of the attributes in the candidate code) cannot be either partially or pass-dependent on the code.

4NF : No multi-valued dependency.

Data Type

MySQL data type-Beginner tutorial  

Introduction to data types in MySQL

integer: m in int (m) is the data display width, floating point number, and fixed point count.

string:char(n)4.0 n for Byte,5.0 n for character ( utf-8=3zj,gbk=2zj)

Char fixed number of characters, space to fill, retrieval speed is fast.

varchar characters + 1 bytes (n<=255) or 2 bytes (n>255)

The number of text characters + 2 bytes; cannot have a default value; The index is to specify the number of characters before the text is stored

Blob binary mode storage

Storage Engine

The difference and connection of various storage engines (Storage data technology and policies, storage mechanisms, indexing techniques, locking levels, etc.)

Database Storage Engine Show Table status displays information about the tables

InnoDB vs . MyISAM ( starting from 5.7 InnoDB the storage engine becomes the default storage engine. )

lock mechanism: row-level lock, table-level lock

Transactional operations: Transaction security, not supported

InnoDB (1) Reliability requirements are relatively high, the requirements of transactions, (2) Table updates and queries are quite frequent, and the opportunity to lock the row is relatively large.

Data and indexes for each table after MySQL4.1 are stored in a file.

The InnoDB employs MVCC to support high concurrency and achieves four standard isolation levels. Its default level is REPEATABLE READ (repeatable read), row-level lock.

automated Disaster recovery. Unlike other storage engines,the InnoDB table can automatically recover from a disaster.

foreign KEY constraints. MySQL supports the foreign key storage engine only InnoDB.

support for auto-increment columns The Auto_increment property.

MyIsam (1) does a lot of count calculation, (2) Inserts infrequently, queries very frequently; (3) No transactions.

tables are stored in two files, data files ( MYD) and index file (MYI)

table-level lock, read = shared lock, write = exclusive lock.

suitable for select-intensive tables, insert intensive tables.

database ACID

of the database ACID

Introduction to Database Transactions

atomicity (atomicity) a transaction must be considered an indivisible minimum unit of work, and all operations in the entire transaction either commit successfully or all fail back, and it is not possible for a transaction to perform only a subset of the operations.

Consistency (consistency) The database is always transitioning from one consistent state to another consistent state.

Isolation (isolation) changes made by an office are not visible to other transactions until the final submission.

Persistence (durability) once a transaction commits, its modifications are not persisted to the database.

4 levels of isolation

A brief introduction to MVVC

Read uncommitted (UNCOMMITTED) Dirty Read : modifications in the transaction, even if they are not committed, are visible to other transactions.

Read COMMITTED (submit reading) non-repeatable read : Things any modifications made are not visible to other transactions from the beginning until the submission.

REPEATABLE READ (REPEATABLE READ): Phantom read: a transaction reads previously retrieved data in the same query condition, and other transactions insert new data that satisfies its query criteria. produces phantom rows.

SERIALIZABLE (serializable) enforcement of transaction serial execution

MVVC is a row-level locks variant of it in lock operation is avoided under normal reading conditions, and locks are added from certain circumstances. .

MySQL deadlock problem

MYsql Pessimistic lock summary and practice

MYsql optimistic locking summary and practice

SELECT ... LOCK in SHARE MODE SELECT ... For update: (the lock in SHARE MODE can easily cause a deadlock if a transaction is to UPDATE the same form)

Optimistic Lock: Lock failure, resulting in retrospective effect of efficiency.

Fetch Data Other threads do not modify the data when they are considered.

updated to determine whether to modify the data, version number mechanism or CAS Operations .

Pessimistic lock: Each fetch of data will be locked.

innodb_lock_wait_timeout Waiting for lock timeout rollback TRANSACTION: "Timeout Method"

The intuitive approach is that when two transactions are waiting for each other, when a wait time exceeds one of the set thresholds, one transaction is rolled back, and the other transaction can continue to execute. In InnoDB , the parameter innodb_lock_wait_timeout is used to set the time-out.

wait-for graph Algorithm for active deadlock detection: "Wait Graph method"

The InnoDB also provides the wait-for graph algorithm for active deadlock detection, wait-for graph whenever a locking request cannot be immediately satisfied and enters the wait The algorithm will be triggered.

Index

index (Storage engine quickly find a record of a data structure, the basic function of the index)

What is B-tree

The data structure and algorithm principle behind MySQL index

MySQL performance optimization-slow query analysis, optimized indexing and configuration

Index Type:

B-tree Whether the index is used by the order of indexed index columns.

Hash Index

cannot be used for sorting.

only all matches are supported.

only equivalent comparisons are supported.

There are many hash conflicts when the efficiency is not too high.

Spatial Data indexing (r-tree) queries data from all dimensions without a prefix query.

Full-text search finds keywords in text, similar to what a search engine does.

description of specific types:

Single-column index: null is not allowed

Normal Index null values are not allowed

Unique index

PRIMARY KEY Index It's important in the InnoDB engine.

Combination Engine: indexes created on multiple fields , follow the leftmost prefix principle when composite indexes .

a column in a query has a range query, and all columns to the right of it cannot use the query

Full-text index:

Spatial index:

Reference: the MySQL index , my MySQL learning experience (ix) index

MySQL Index detailed ( the number of disk I/O is generally used to evaluate the index structure. )

Disk Access Principle

Principle of locality and disk pre-reading

M- order B-tree

there are at least 2 subtrees of the root node.

each non-leaf node consists of a n-1 key and n pointers.

Branch nodes have at least M/2 subtree, with a maximum of M subtrees. (Outside the root node and leaf nodes)

all leaf nodes have the same depth, which equals the tree height H.

Each leaf node contains at least one key and two pointers, with a maximum of 2d-1 keys and 2d pointers.

B + Tree

The inner node does not store data and stores only key.

Leaf nodes do not store pointers.

MySQL Index Implementation

MyISAM index files and data files are detached , nonclustered indexes.

The InnoDB leaf node contains the complete data record, the clustered index. Aggregates according to the primary key.

EXPLAIN Field Introduction

Possible_keys: Displays the indexes that may be applied to this table.

Key: The actual index used.

Key_len: The length of the index used, the shorter the better.

Ref: Shows which column of the index is being used.

Rows:mysql the number of rows that must be retrieved to return the requested data.

Type: What kind was used. The best to worst connection types are system, const (constant), eq_ref, ref, range, index (full table scan), and all (full table scan).

View  

MySQL database view

MySQL-View algorithm

The simplest implementation of a view is to store the results of a SELECT statement in a temporary table. With performance issues, it is difficult for the optimizer to optimize queries on temporary tables.

Merging Algorithms :The SELECT statement is merged with the SELECT statement for the external query view and then executed.

Temporal table Algorithm : executes the view's select statement before executing the statement for the external query.

Views can improve performance in some cases and overlay them with other ways of boosting performance.

Views are not able to modify data across tables.

when creating a conditional view, add the "with CHECK OPTION" command.

Trigger

Trigger events for triggers , can be INSERT, UPDATE, or DELETE.

Trigger Time , can be Before or after.

Same trigger event with same table trigger time , only one trigger can be defined, only row-based triggering is supported.

the atomic nature of the trigger, InnoDB support transactions, MyISAM not supported.

Events

A timed task similar to Linux that executes a piece of SQL code at some time or at intervals.

Backup

data Backup (in layman 's Mysql 27 Chapter backup and recovery)

A comparison of full backups with incremental backups.

Make sure that MySQL turns on the Log-bin option, with Binlog,mysql to do a full recovery when necessary, or point-in-time recovery, or location-based recovery.

logical backup (to back up the data in the database as a text file, the backed up files can be checked See and edit. )

Physical Backup

Cold backup:the CP method for moving data files.

Restore: Move data files and use the Mysqlbinlog tool to recover all BINLOG since the backup.

Hot backup: (the table that will be backed up reads the lock and then the CP data file to the backup directory.) ）

myisam:mysqlhotcopy Tools.

Ibbackup is a hot backup tool for Innobase Corporation (www.innodb.com).

Recovery

Full recovery

Executes the backup as input.

Redo the log that was executed after the backup.

Incomplete recovery (skipping the error operation statement, then recovering The statement executed by the face to complete our recovery. )

A point-in-time operation. Skips the time that the failure occurred.

Location-based recovery. Locate the location number of the error statement and skip the position interval.

Log

error log: Records information about when Mysqld starts and stops, and when any critical errors occur during the operation of the server.

Binary files: recorded all the DDL (data definition Language) statements and DML (data manipulation language) statements, excluding data query statements. Statements are saved as "events," which describe the process of changing the data. (Delete the log periodically and turn it off by default).

Query log: all statements for the client are recorded in plain text format and can be read directly. (the log log records all database operations, and for frequently accessed systems, this logs have a large impact on system performance, which is recommended to close, by default).

Slow query log: The slow query log records the log of SQL statements that contain all the values that were set for the execution time over the parameter long_query_time (in seconds). (plain text format)mysql log file error log and slow query log detailed .

Summary of log files:

In the case of a system failure, it is recommended to review the error log first to help users quickly locate the cause of failure.

Log data changes, backup data, copy data, and so on, open the binary logs. This log is not logged by default and it is recommended that this log be opened with the--log-bin option.

if you want to log any operations that occur on the database, including SELECT, you need to open the query log with--log, this log is closed by default, it is generally recommended not to open this log, so as not to affect the overall performance of the system.

Review your system's performance issues, want to find a performance problem SQL statement, you need to open the slow query log with--log-slow-queries. For a large number of slow query logs, it is recommended to use the Mysqldumpslow tool for summary viewing.

MySQL Interview review summary