【 Preparation for Spring Recruitment series 】 Over the years about MySQL high frequency interview questions

• Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.
• Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
• Page lock: the overhead and lock time are between table lock and row lock. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

There are five types of tables:

• MyISAM
• Heap
• Merge
• INNODB
• ISAM 

MyISAM:

• Transactions are not supported, but each query is atomic;
• Supports table-level locking, that is, each operation locks the entire table.
• The total number of rows stored in the table;
• A MYISAM table has three files: index file, table structure file, data file;
• The data domain of the index file stores Pointers to the data file. Secondary indexes are basically the same as primary indexes, but secondary indexes are not guaranteed to be unique.

InnoDb:

• Acid-supported transactions, which support four isolation levels of transactions;
• Row-level locking and foreign key constraints are supported, so write concurrency is supported.

Total number of rows not stored:

An InnoDb engine is stored in one file space (shared table space, table size is not controlled by the operating system, a table may be distributed in multiple files), may be multiple (set to independent table empty, table size is limited by the operating system file size, generally 2G), by the operating system file size limit.

A primary key index uses a clustered index (the index’s data domain stores the data file itself), and a secondary index’s data domain stores the value of the primary key. Therefore, to search data from the secondary index, you need to find the primary key through the secondary index and then access the secondary index. It is best to use auto-increment primary keys to prevent large file adjustments when inserting data to maintain the B+ tree structure.

The four isolation levels defined by the SQL standard are:

1. Read uncommited: Uncommitted data is read
2. Read COMMITTED: Indicates dirty reads and cannot be committed
3. Repeatable read: repeatable
4. Serializable: Serial things

1. The CHAR and VARCHAR types differ in storage and retrieval
2. When CHAR values are stored, they are filled with Spaces to a specific length. Trailing Spaces are removed when retrieving CHAR values.

• Each row of a table is uniquely identified by a primary key, and a table has only one primary key.
• Primary keys are also candidates. By convention, candidate keys can be specified as primary keys and can be used for any foreign key reference.

All fields on MyISAM Static have fixed widths. Dynamic MyISAM tables will have fields like TEXT, BLOB, and so on to accommodate data types of different lengths.

The timestamp field gets the current timestamp whenever the row is changed.

It stops incrementing, and any further inserts will generate an error because the key has already been used.

LAST_INSERT_ID returns the last value assigned by Auto_increment and does not specify the table name.

Indexes are defined for tables in the following way:

SHOW INDEX FROM <tablename>;Copy the code

% corresponds to zero or more characters, and _ is just one character in a LIKE statement.

• UNIX_TIMESTAMP is a command that converts MySQL timestamp to Unix timestamp
• FROM_UNIXTIME is the command to convert a Unix timestamp to a MySQL timestamp

Use the =, <>, <=, <, > =, >, <<, >, <=>, AND, OR, OR LIKE operators in column comparisons in SELECT statements.

• A BLOB is a binary object that can hold a variable amount of data. TEXT is a case insensitive BLOB.
• The only difference between BLOB and TEXT types is that BLOB values are case-sensitive when sorting and comparing, while TEXT values are case-insensitive.

MySQL_fetch_array differs from MySQL_fetch_object:

• MySQL_fetch_array () – Returns the result row as an associative array or as a regular array from the database.
• MySQL_fetch_object – Returns the result row as an object from the database.

Each MyISAM table is stored on disk in three formats:

• ·. FRM file storage table definition
• · Data files have the extension “.myd “(MYData)
• Index files have the.myi (MYIndex) extension

• DISTINCT is converted to GROUP BY on all columns and is used in combination with the ORDER BY clause.
• SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

In MySQL, query to display the first 50 lines using the following code:

• SELECT*FROM
• LIMIT 0, 50;

• The NOW () command is used to display the current year, month, date, hour, minute, and second.
• CURRENT_DATE () displays only the current year, month, and date.

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT 

CONCAT(A, B) – Concatenate two string values to create A single string output. Usually used to combine two

Or multiple fields combined into a single field.

2, FORMAT(X, D)- FORMAT the valid numbers from X to D.

3. CURRDATE(), CURRTIME()- Returns the current date or time.

4. NOW () – Returns the current date and time as a value.

5, MONTH (), DAY (), YEAR (), WEEK (), WEEKDAY () – from the date

Value to extract the given data.

6. HOUR (), MINUTE (), SECOND () – Extract the given data from the time value.

7. DATEDIFF (A, B) — Determine the difference between two dates, usually used to calculate age

8. SUBTIMES (A, B) — Determine the difference between two times.

By default, MySQL is in autoCOMMIT mode. All database updates are committed immediately, so MySQL does not support transactions by default.

SETAUTOCOMMIT=0 allows MySQL to use non-autoCOMMIT mode. In non-autocommit mode, you must COMMIT your changes using COMMIT, or ROLLBACK your changes with ROLLBACK.

The NUMERIC and DECIMAL types are implemented by MySQL as the same type, which is allowed by the SQL92 standard. They are used to hold values whose precise accuracy is extremely important, such as data relating to money. Precision and size can be (and usually are) specified when declaring a class to be one of these types.

Such as:

Salary is a DECIMAL (9, 2)Copy the code

In this example, 9(precision) represents the total number of decimal places that will be used to store values, and 2(scale) represents the number of decimal places that will be used to store values.

So, in this case, the range of values that can be stored in the SALARY column is from -9999999.99 to 9999999.99.

The MySQL server controls user access to the database through the permission table, which is stored in the MySQL database and initialized by the MySQL_install_db script. These permission tables are user, DB, table_priv, columns_priv, and host.

The string type is:

1. Well-designed database structure allows partial data redundancy, avoids join query as far as possible, and improves efficiency.
2. Select the appropriate table field data type and storage engine, and add indexes as appropriate.
3. MySQL library master separated from read and write.
4. Find regular table, reduce the amount of data in a single table to improve the query speed.
5. Add caching mechanisms such as memcached, APC, etc.
6. Pages that do not change often, generate static pages.
7. Write efficient SQL. SELECT field_1,field_2, fieldd_3 FROM TABLE

1. Reading and writing separation
2. Subsection locking
3. Reduces the duration of lock holding
4. Multiple threads try to fetch resources in the same order

The granularity of the lock should not be too refined, otherwise there may be too many times of locking and releasing threads, but the efficiency is not as good as adding a large lock at a time.

B+ tree, optimized B+ tree

InnoDB recommends using the default primary key increment as the primary index for most tables by adding a pointer to the next leaf.

1. LIKE statements beginning with “%”, fuzzy match
2. The index is not used before OR after the OR statement
3. Implicit conversion of data type (vARCHar may be automatically converted to int if not quoted)

It is best to optimize in the following order:

1. SQL statement and index optimization
2. Optimization of database table structure
3. Optimization of system configuration
4. Hardware optimization

Select the most applicable field attributes, minimize the width of the defined field, and set the field as null as possible. For example, ‘province’ and ‘gender’ are best used in ENUM

• Use joins instead
• Instead of manually created temporary tables, use unions
• Transaction processing
• Lock tables and optimize transaction processing
• Apply foreign key, optimize lock table
• indexing
• Optimized query statement

Indexes are special files (indexes on InnoDB tables are part of the table space) that contain Pointers to all the records in the table.

The only job of a plain INDEX (an INDEX defined by the KEY or INDEX keyword) is to speed up access to data.

Normal indexes allow indexed data columns to contain duplicate values. If you can determine that a column will contain only values that differ from each other, you should define it as a UNIQUE index when creating an index for that column using the keyword UNIQUE. In other words, a unique index guarantees the uniqueness of the data record.

A PRIMARY KEY is a special unique index. Only one PRIMARY KEY can be defined in a table. A PRIMARY KEY is used to uniquely identify a record.

An INDEX can cover multiple data columns, such as the INDEX(columnA, columnB) INDEX, which is a federated INDEX.

Indexes can greatly improve the speed of querying data, but slow down the speed of inserting, deleting, and updating tables because of the need to manipulate index files while performing these writes.

A transaction is an ordered set of database operations as a unit. The transaction is considered successful if all operations in the group succeed, even if only one operation fails. If all operations are complete, the transaction commits and its changes apply to all other database processes. If an operation fails, the transaction is rolled back and the effects of all operations on the transaction are cancelled.

Transaction features:

• Atomicity: Indivisibility, where all transactions are executed or none are executed.
• Consistency or seriability. The execution of the transaction causes the database to transition from one correct state to another
• Isolation. Any changes made to data by that transaction are not allowed to be made available to any other transaction until the transaction has committed correctly,
• Persistence. Once a transaction is committed correctly, its results are permanently stored in the database, even if other failures occur after the transaction is committed.

Or to put it this way:

A transaction is a group of SQL statements that are bound together as a logical unit of work. If any statement fails, the entire operation fails, and the operation is then rolled back to its previous state or has a node on it. Transactions can be used to ensure that either they are executed or they are not. To consider groups of statements as transactions, you need to pass the ACID test, which is atomicity, consistency, isolation, and persistence.

The cause of SQL injection: During the process of program development, the client can submit some SQL statements through the global variables POST and GET for normal execution without paying attention to the standard writing of SQL statements and filtering of special characters.

Ways to prevent SQL injection:

Enable the magic_QUOtes_GPC and magic_QUOtes_Runtime Settings in the configuration file

Addslashes is used for SQL statement conversion when executing SQL statements

Do not omit double and single quotation marks when writing Sql statements.

Improve the database table and field naming skills, some important fields according to the characteristics of the program named, not easy to guess.

Field type priority: Integer > Date,time>enum,char> VARCHar >blob,text takes precedence over numeric type, date or binary type, and string type

Datatime:The date format is YYYY-MM-DD HH:MM:SS and occupies 8 bytes of storage space. The datatime type is independent of the time zone

Timestamp:It is stored in timestamp format and occupies 4 bytes. The display ranges from 1970-1-1 to 2038-1-19 depending on the specified time zone. By default, the value of timestamp column can be automatically modified when the data of the first column is modified

Date:The number of bytes used is less than the string.datatime.int, which takes only 3 bytes, stores the date and month, and can be calculated using the date and time function

TimeNote: Do not use string to store date_time data (usually takes up less storage space than string, you can use the date_filter function) using int to store date_time is not as good as using timestamp

Fast access to specific information in a data table to speed up retrieval create unique indexes to ensure the uniqueness of each row of data in a database table.

Accelerated tables and joins between tables can significantly reduce grouping and sorting time in queries when grouping and sorting clauses are used for data retrieval

Negative effects:

Creating and maintaining indexes takes time, which increases with the volume of data; Indexes take up physical space. Not only tables take up data space, but each index takes up physical space. Indexes are maintained dynamically when tables are added, deleted, or modified, which slows down data maintenance.

Build indexes on the most frequently used fields to narrow the query. Build indexes on frequently used fields that need to be sorted

Indexes are not appropriate for columns that are rarely involved in a query or that have a lot of duplicate values. For some special data types, such as text fields, indexes are not appropriate

Cross join: A cross join, also called a Cartesian product, matches all the records in one table with all the records in another table without using any criteria.

The inner join is a cross-join with only conditions. Records that meet the conditions are screened out according to a certain condition. Records that do not meet the conditions will not appear in the result set, that is, the inner join only connects the matched rows.

The result set of an outer join contains not only the rows that meet the join conditions, but also all data rows in the left table, right table or two tables. These three cases are called left outer join, right outer join, and full outer join in turn.

Left outer join, also known as left join, the left table is the main table, all records in the left table will appear in the result set, for those records in the right table do not match, still display, the corresponding field values of the right are filled with NULL. Right outer join, also known as right join, the right table is the main table, and all records in the right table will appear in the result set. Left join and right join are interchangeable, MySQL does not currently support full external join.

A transaction is a sequence of database operations defined by users. These operations either do all or do not do all, which is an inseparable unit of work. Transaction rollback refers to the cancellation of the update operation to the database that has been completed by the transaction.

When you want to modify two different tables in the database at the same time, if they are not a transaction, when the first table is modified, there may be an exception in the process of modifying the second table and it cannot be modified. At this time, only the second table is still in the state before the modification, and the first table has been modified. When you set them as a transaction, when the first table is modified and the second table fails to be modified, both the first table and the second table return to the unmodified state. This is called transaction rollback

SQL language includes data definition (DDL), data manipulation (DML), data control (DCL) and data query (DQL).

• Data definitions: Create Table,Alter Table,Drop Table, Craete/Drop Index, etc
• Data manipulation: Select, INSERT,update,delete,
• Data control: Grant, REVOKE
• Data query: select

Data Integrity refers to the Accuracy and Reliability of Data.

Entity integrity: Specifies that each row of a table is a unique entity in the table.

2. Domain integrity: it means that the columns in the table must meet certain data type constraints, including value range and accuracy.

3. Referential integrity: it means that the data of the primary and external keywords of two tables should be consistent, which ensures the consistency of data between tables and prevents data loss or meaningless data from spreading in the database.

4. User-defined integrity: Different relational database systems require special constraints depending on the context in which they are used. User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that a specific application must meet.

Table related constraints include column constraints (NOT NULL) and table constraints (PRIMARY KEY, Foreign KEY, check, and UNIQUE).

A: A database is a shared resource that is used by multiple users. When multiple users concurrently access data, multiple transactions simultaneously access the same data in a database. Uncontrolled concurrent operations can read and store incorrect data, damaging the consistency of the database.

Locking is a very important technology to realize database concurrency control. A transaction makes a request to the system to lock a data object before operating on it. After the lock is locked, the transaction has some control over the data object, and no other transaction can update the data object until the transaction releases the lock.

A: A view is a virtual table that has the same functions as a physical table. You can add, modify, query, and manipulate views, which are usually rows or subsets of columns in one or more tables. Changes to the view do not affect the base table. It makes it easier for us to get data compared to multi-table queries.

Cursor:The result set of the query is effectively processed as a unit. Cursors can be fixed to specific rows in the cell, retrieving one or more rows from the current line of the result set. You can modify the current row of the result set. Cursors are not generally used, but they are important when you need to process data item by item.

A: A stored procedure is a precompiled SQL statement that has the advantage of allowing modular design, meaning that it is created once and can be called many times later in the program. If an operation requires multiple SQL executions, using stored procedures is faster than simply executing SQL statements. A stored procedure can be invoked with a command object.

A:

Advantages and disadvantages of formal design:

Advantages:

Data redundancy can be reduced as far as possible, making the update fast and small

Disadvantages:

Antiformalization:

Disadvantages:Data redundancy and data anomalies, data modification requires more cost

A: A base table is a table that stands on its own. In SQL, a relationship corresponds to a table. A view is a table exported from one or more base tables. The view itself is not stored independently in the database and is a virtual table

A:

Answer: NULL this value stands for UNKNOWN: it does not stand for “” (empty string). Any comparison of the NULL value produces a NULL value. You cannot compare any value to a NULL value and logically expect an answer. Use IS NULL for NULL determination

Differences between primary keys, foreign keys, and indexes

• Definition: Primary key – Uniquely identifies a record, cannot be duplicated, and cannot be empty
• Foreign key – THE foreign key of a table is the primary key of another table. Foreign keys can have duplicate or null values
• Index – This field has no duplicate value, but can have a null value

Function:

• Primary key – used to ensure data integrity
• Foreign key – used to establish relationships with other tables
• Indexing – is to speed up query sorting

The number:

• Primary key – There can be only one primary key
• Foreign keys – A table can have more than one foreign key
• Indexes – A table can have multiple unique indexes

A: Check limits, which are defined in the database table to limit the values entered for that column.

Triggers can also be used to limit the values that fields in a database table can accept, but this approach requires triggers to be defined in the table, which can affect performance in some cases.

1. Joins between Where tables must precede other Where conditions, and conditions that filter out the maximum number of records must precede the end of the Where clause. HAVING finally.
2. Replace IN with EXISTS and NOT EXISTS with NOT IN.
3. Avoid calculations on indexed columns
4. Avoid using IS NULL and IS NOT NULL on index columns
5. Queries should be optimized to avoid full table scans, and indexes should be considered on where and order by columns first.
6. Try to avoid null values for fields in the WHERE clause, as this will cause the engine to abandon the index for a full table scan
7. Expression operations on fields in the WHERE clause should be avoided as much as possible, which can cause the engine to abandon indexes for a full table scan