MYSQL interview questions (1)

This article mainly shares MYSQL interview questions (1) with you, hoping to help you.

[Related topic recommendations: mysql interview questions (2020)]

1. How to design a high-concurrency system

① Optimization of database, including reasonable transaction isolation level, SQL statement optimization, index optimization

② Use cache to minimize the number of databasesIO

③ Distributed database, distributed cache

④ Server load balancing

2 . Lock optimization strategy

① Read and write separation

② Segmented locking

③ Reduce the lock holding time

④ Multiple threads try to obtain resources in the same order

, etc. These are not absolute principles and all depend on the situation. For example, the lock cannot be The granularity is too detailed, otherwise the thread may be locked and released too many times, which will not be as efficient as adding a large lock at a time. I talked with the interviewer for a long time about this part

3. The underlying implementation principles and optimization of the index

B+ tree, the optimized B+ tree

is mainly All leaf nodes have added pointers to the next leaf node, so InnoDB recommends using the default auto-incremented primary key as the primary index for most tables. . 4. Under what circumstances the index is set but cannot be used

① LIKE statement starting with "%", fuzzy matching ② No index is used before and after the OR statement

③ There is an implicit conversion of the data type (for example, varchar may be automatically converted to int type without single quotes)

5 . Optimization of SQL statements

How to handle order byalter try to merge multiple times into one

insert and delete also need to be merged

Wait

6

. How to optimize in practiceMySQL I answered the following four questions in order. In terms of effect, the first one has the greatest impact, and the subsequent ones have smaller and smaller effects.

① SQLOptimization of statements and indexes

② Optimization of database table structure

③ Optimization of system configuration

##④ Optimization of hardware

8. The main characteristics of sql injectionThere are many variants, simple attacks, and great harm

9. The main hazards of sql injection

Unexpected Authorize to operate the database data

Maliciously modify the webpage

Add system account or database user account privately

Trojan horse installed on the webpage

11. Methods of optimizing database

##Eight ways to optimize MySQL database (classic must-see) click to get

· Select the most applicable field attributes, minimize the defined field width, and try to set the field to NOTNULL. For example, 'province' and 'gender' are best suited to ENUM

· Use connection(JOIN) instead of subquery

· Apply union(UNION) to replace the manually created temporary table

· Transaction processing

· Lock table, optimize transaction processing

· Apply foreign keys and optimize locking tables

##·

Create indexes

·

Optimize query statement

19. Briefly describe mysql, The difference between index, primary key, unique index and joint index, and what impact it has on the performance of the database (from both reading and writing aspects) (Sina Technology Department)

Indexes are special files(The indexes on the InnoDB data table are an integral part of the table space). They contain reference pointers to all records in the data table.
The only task of a normal index (an index defined by the keyword KEY or INDEX) is to speed up access to data.
#Normal indexes allow indexed data columns to contain duplicate values. If you can determine that a certain 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, a unique index can ensure the uniqueness of data records.
#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, using keywords PRIMARY KEY to create.
The index can cover multiple data columns, such as the INDEX(columnA, columnB) index, which is a joint index.
Index can greatly improve the speed of data query, but it will reduce the speed of inserting, deleting, and updating the table, because when performing these write operations, To operate the index file.

20. What is a transaction in a database?

Transaction (transaction) is a group of units as a unit Orderly database operations. A transaction is considered successful if all operations in the group succeed, even if only one operation fails, the transaction is not successful. If all operations are completed, the transaction is committed and its modifications are applied to all other database processes. If an operation fails, the transaction is rolled back and the effects of all operations in the transaction are canceled. The four major characteristics of ACID are atomicity, isolation, consistency, and durability.

21. Do you understand XSS attacks? How to prevent it?

the behavior of.
Use the htmlspecialchars() function to filter the submitted content and materialize the special symbols in the string.

22. What causes SQL injection vulnerabilities? How to prevent it?

Cause of SQL injection: During the program development process, you did not pay attention to standard writing of SQL statements and filtering of special characters, which resulted in the client being able to submit some data through global variables POST and GET. The sql statement is executed normally.

How to prevent SQL injection:

Open the magic_quotes_gpc and magic_quotes_runtime settings

Use addslashes for sql statement conversion when executing sql statements

Sql statement writing Try not to omit double and single quotes.

Filter out some keywords in the sql statement: update, insert, delete, select, *.

Improve the naming skills of database tables and fields. Name some important fields according to the characteristics of the program and choose ones that are not easy to guess.

#Set register_globals to off in the Php configuration file and turn off global variable registration

Control error messages and do not browse Output error information on the server and write the error information to the log file .

25. For relational databases, indexing is a very important concept. Please answer a few questions about indexing:

a) What is the purpose of indexing?

Quickly access specific information in the data table and increase retrieval speed

Create unique A unique index ensures the uniqueness of each row of data in the database table.

Accelerate joins between tables

Use grouping and sorting clauses When performing data retrieval, the time of grouping and sorting in queries can be significantly reduced

b) What is the negative impact of indexes on the database system?

Negative impact:
Create index It takes time to maintain and maintain indexes, which increases with the increase in data volume; indexes need to occupy physical space, not only the table needs to occupy data space, but each index also needs to occupy physical space; when adding, deleting, or modifying the table , the index must also be maintained dynamically, which reduces the data maintenance speed.

#c) What are the principles for creating indexes for data tables?

#Create indexes on the most frequently used fields to narrow the scope of the query.

Create indexes on frequently used fields that need to be sorted

d) , Under what circumstances is it inappropriate to create an index?

#It is not appropriate to create an index for columns that are rarely involved in queries or columns with many duplicate values.

For some special data types, it is not appropriate to create indexes, such as text fields (text), etc.

26. Briefly describe the difference between MyISAM and InnoDB in MySQL database

The most important feature that distinguishes it from other databases is its plug-in table Storage engine. Remember: the storage engine is based on tables, not databases.

The difference between InnoDB and MyISAM:

InnoDB storage engine: Main For OLTP (Online Transaction Processing) applications, it is the first storage engine to fully support ACID transactions (BDB was the first storage engine to support transactions and has stopped development).

Features:

##· Row lock Design and support foreign keys, support transactions, support concurrency, lock granularity supports mvcc row-level lock;

MyISAM storage engine: is the storage engine officially provided by MySQL, mainly for OLAP (Online Analytical Processing, online analysis and processing) applications .
Features:

Does not support transactions, The lock granularity supports table-level locks for concurrent insertion, supports table locations and full-text indexes. The operation speed is fast,cannot read and write operations too frequently；

27. Explain the differences between MySQL outer joins, inner joins and self-joins

Let’s first talk about cross join: Cross join is also called Cartesian product. It means to directly combine all the records in one table with all the records in another table without using any conditions. Match one by one.

Inner join is a cross-join with only conditions, filtering out records that meet the conditions based on a certain condition , records that do not meet the conditions will not appear in the result set, that is, the inner join only connects matching rows.
Outer join The result set not only contains rows that meet the join conditions, but also All data rows
in the left table, right table or both tables, these three cases are called left outer join, right outer join, and Full outer join.

Left outer join, also called left join, the left table is the main table, all records in the left table will appear in the result set, for those in the right table There is no matching record in , it still needs to be displayed, and the corresponding field values ​​on the right are filled with NULL. Right outer join, also called right join, the right table is the main table, and all records in the right table will appear in the result set. Left joins and right joins are interchangeable, and MySQL currently does not support full outer joins.

28. Write the names of more than three MySQL database storage engines (tip: case-insensitive)

MyISAM, InnoDB, BDB (BerkeleyDB), Merge, Memory (Heap), Example, Federated,
##Archive, CSV, Blackhole, MaxDB and more than a dozen engines

33. Overview of transaction rollback mechanism in Myql

A transaction is a user-defined database operation Sequence, these operations are either done or not done at all, and are an indivisible unit of work. Transaction rollback refers to undoing the update operations to the database that have 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, the second table may be modified. An exception occurred during the process and the modification failed. At this time, only the second table is still in the unmodified state, while the first table has been modified. And when you set them as a transaction, when the first table is modified and the second table is modified abnormally and cannot be modified, the first table and the second table will return to the unmodified state. This is the so-called transaction rollback

#2. What parts does the SQL language include? What are the action keywords for each section?

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

Data definition: Create Table, Alter Table, Drop Table, Craete/Drop Index, etc.

Data manipulation: Select, insert, update, delete,

Data control: grant, revoke

Data query: select

3. What do integrity constraints include?

Answer: Data integrity(Data Integrity) refers to the accuracy and reliability of data.

is divided into the following four categories:

1) Entity integrity: stipulates that each row of the table is in the table is the only entity.

2) Domain integrity: It means that the columns in the table must meet certain data type constraints, which include value range, precision and other regulations.

3) Referential integrity: It means that the data of the primary key and foreign key of the two tables should be consistent, ensuring the consistency of the data between the tables and preventing the data from being corrupted. Missing or meaningless data proliferates in the database.

4) User-defined integrity: Different relational database systems often require some special constraints based on their application environments. User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that a specific application must meet.

Constraints related to the table: including column constraints (NOT NULL (non-null constraint)) and table Constraints (PRIMARY KEY, foreign key, check, UNIQUE).

4. What is a transaction? and its characteristics?

# Answer: Transaction: It is a series of database operations and the basic logical unit of database application.

Transaction characteristics:

(1) Atomicity: that is, not possible Splitability, either all transactions are executed or none are executed.

(2) Consistency or stringability. The execution of a transaction converts the database from one correct state to another correct state

(3) Isolation. Before the transaction is correctly committed, any changes to the data by the transaction are not allowed to be provided to any other transaction,

(4) Persistence. After a transaction is submitted correctly, its results will be permanently saved in the database. Even if there are other failures after the transaction is submitted, the processing results of the transaction will be saved.

Or understand it this way:

Transactions are bound together as a logical unit of work SQL statement grouping, if any statement operation fails, the entire operation will fail, and future operations will be rolled back to the state before the operation, or there will be a node on it. To ensure that something is either executed or not executed, transactions can be used. For a grouped statement to be considered a transaction, it needs to pass the ACIDtest, which is atomicity, consistency, isolation, and durability.

5. What is a lock?

Answer: The database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions simultaneously access the same data in the database. If concurrent operations are not controlled, incorrect data may be read and stored, destroying the consistency of the database.

Locking is a very important technology to achieve database concurrency control. Before a transaction operates on a data object, it first sends a request to the system to lock it. After locking, the transaction has certain control over the data object. Before the transaction releases the lock, other transactions cannot update the data object.

Basic lock types: locks include row-level locks and table-level locks

6. What is view? What is a cursor?

Answer: A view is a virtual table that has the same functions as a physical table. Views can be added, modified, checked, and operated. A view is usually a subset of rows or columns of one table or multiple tables. Modifications to the view do not affect the underlying tables. It makes it easier for us to obtain data than multi-table queries.

Cursor: It effectively processes the query result set as a unit. The cursor can be positioned on a specific row in the cell to retrieve one or more rows from the current row in the result set. You can make changes to the current row of the result set. Cursors are generally not used, but when data needs to be processed one by one, cursors are very important.

7. What is a stored procedure? What to call?

Answer: The stored procedure is a precompiled SQL statement. The advantage is that it allows a modular design, which means that it only needs to be created once and can be used later in the program. You can call it multiple times. If a certain operation requires multiple executions of SQL, using stored procedures is faster than executing simple SQL statements. Stored procedures can be called using a command object.

8. What is the role of index? And what are its advantages and disadvantages?

#Answer: An index is a special query table that the database search engine can use to speed up data retrieval. It is very similar to the table of contents of a book in real life. You can find the data you want without querying the entire book. Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down data entry and increases the size of the database.

9. How to understand the three paradigms in a popular way?

Answer: First normal form: 1NF is an atomicity constraint on attributes, which requires attributes to be atomic and cannot be decomposed;

Second Normal Form: 2NF is a unique constraint on records, requiring records to have unique identifiers, that is, the uniqueness of entities;

Third Normal Form: 3NF is a constraint on field redundancy, that is, any field cannot be derived from other fields. It requires that the fields are not redundant. .

Advantages and disadvantages of normalized design:

##Advantages:

Can reduce data redundancy as much as possible, so that updates are fast and the size is small

Disadvantages: It requires a lot of queries Association between tables reduces writing efficiency and increases reading efficiency, making it more difficult to perform index optimization

##Denormalization:

Advantages: It can reduce table associations and enable better index optimization

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

10. What is a basic table? What is a view?

# Answer: The basic table is a table that exists independently. In SQL, a relationship is Corresponds to a table. A view is a table derived from one or several base tables. The view itself is not stored independently in the database, but is a virtual table

11. Describe the advantages of views?

Answer: (1) Views can simplify user operations (2) Views enable users to view the same data from multiple perspectives; (3) Views are databases Provides a certain degree of logical independence; (4) Views can provide security protection for confidential data.

12. What does NULL mean?

Answer: The value NULL represents UNKNOWN: it does not represent "" (empty string). Any comparison against a NULL value will produce a NULL value. You cannot compare any value to a NULL value and logically expect to get an answer.

Use IS NULL for NULL judgment

13. What is the difference between primary key, foreign key and index?

The difference between primary key, foreign key and index

Definition:

Primary key--uniquely identifies a record, cannot be duplicated, and is not allowed to be empty

Foreign key--the foreign key of a table belongs to another table Primary keys and foreign keys can have duplicates and can be null values

Index--this field has no duplicate values, but can have a null value

## Function:

Primary key--Use To ensure data integrity

Foreign keys--used to establish connections with other tables

Indexes--are used to improve query sorting speed

Number:

Primary key--There can only be one primary key

Foreign key- -A table can have multiple foreign keys

Indexes--A table can have multiple unique indexes

14. What can you use to ensure that the fields in the table only accept values ​​in a specific range?

Answer: Check limit, it Defined in a database table to limit the value entered in this column.

Triggers can also be used to limit the values ​​that fields in database tables can accept, but this method requires triggers to be defined in the table, which may occur in Affects performance in some cases.

15. What are the methods for optimizing SQL statements? (Select a few)

(1) In the Where clause: the connection between where tables must be written before other Where conditions, those can filter out the largest The conditions for quantity records must be written at the end of the Where clause. HAVING is at the end.

（2）Replace IN with EXISTS and NOT IN with NOT EXISTS.

(3) Avoid using calculations on indexed columns

(4) Avoid Use IS NULL and IS NOT NULL

# (5) on the index column to optimize the query. You should try to avoid full table scans. You should first consider where and Create an index on the columns involved in order by.

（6）You should try to avoid null value judgment on fields in the where clause, otherwise the engine will give up using the index and perform a full table scan

(7) You should try to avoid performing expression operations on fields in the where clause, which will cause the engine to give up using the index and perform a full table scan

16. What is the difference between 'correlated subquery' and 'non-correlated subquery' in SQL statement?

Answer: Subquery: A query nested within other queries is called a query.

The subquery is also called the inner query, and the statement containing the subquery is called the outer query (also called the main query).

All subqueries can be divided into two categories, namely correlated subqueries and non-correlated subqueries

(1) Non-correlated subquery is a subquery that is independent of the outer query. The subquery is executed once in total, and the value is passed to the outer query after execution.

(2) The execution of the relevant subquery depends on the data of the external query. When the external query executes a row, the subquery is executed once.

Therefore, non-correlated subqueries are more efficient than correlated subqueries

17. char What is the difference between varchar and varchar?

Answer: It is a fixed-length type, while varchar is a variable-length type. The difference between them is:

In a char(M) type data column, each value occupies M bytes. If a length is less than M, MySQL will pad it with space characters on the right. (Padding space characters will be removed during the search operation.) In a varchar(M) type data column, each value only takes up just enough bytes plus one byte to record its length ( That is, the total length is L+1 bytes).

varchar is applicable to scenarios:

The maximum length of the string list is much larger than the average length 2. String Rarely updated, prone to storage fragmentation 3. Use multi-byte character sets to store strings

Char scenario:

Storage strings with approximate length (md5 value, ID card, mobile phone number) and relatively short length (because varchar requires extra space to record the length of the string), which is more suitable for characters that are frequently updated string, there will be no page splitting when updating, avoid storage fragmentation, and obtain better io performance

18. Mysql storage engine, the difference between myisam and innodb.

Answer: Simple expression:

MyISAM is a non-transactional storage engine; suitable for applications with frequent queries ; Table lock, no deadlock; suitable for small data, small concurrency

innodb is a storage engine that supports transactions; suitable for applications with a lot of insert and update operations; if the design is reasonable It is a row lock (the biggest difference lies in the level of the lock); suitable for big data and large concurrency.

19. What are the data table types?

Answer: MyISAM, InnoDB, HEAP, BOB, ARCHIVE, CSV, etc.

MyISAM: Mature, stable, easy to manage, and fast to read. Some functions do not support (transactions, etc.), table-level locks.

InnoDB: supports transactions, foreign keys and other features, and data row locking. It takes up a lot of space and does not support full-text indexing, etc.

20. The MySQL database is used as the storage of the publishing system. The increment of more than 50,000 items per day is expected to last three years. How to optimize it?

a. Design a well-designed database structure, allow partial data redundancy, and try to avoid join queries to improve efficiency.

b. Select the appropriate table field data type and storage engine, and add indexes appropriately.

c. Mysql library master-slave reading and writing separation.

d. Find regular tables and reduce the amount of data in a single table to improve query speed.

e. Add caching mechanisms, such as memcached, apc, etc.

f. For pages that do not change frequently, generate static pages.

g. Write efficient SQL. For example, SELECT * FROM TABEL is changed to SELECT field_1, field_2, field_3 FROM TABLE.

21. For a website with high traffic, what method do you use to solve the problem of statistics of page visits? ?

Answer: a. Confirm whether the server can support the current traffic.

b. Optimize database access.

c. Prohibit external access to links (hotlinking), such as hotlinking of pictures.

d. Control file download.

#e. Use different hosts to distribute traffic.

f. Use browsing statistics software to understand the number of visits and perform targeted optimization.

4. How to optimize SQL? (Students can understand the following explanation, and then express the general meaning according to their own understanding)

Answer:

（1）Choose the correct storage engine

Take MySQL as an example, including two storage engines MyISAM and InnoDB, each engine has pros and cons.

MyISAM is suitable for applications that require a large number of queries, but it is not very good for a large number of write operations. Even if you just need to update a field, the entire table will be locked, and other processes, even the reading process, cannot operate until the reading operation is completed. In addition, MyISAM is extremely fast for calculations such as SELECT COUNT(*).

The trend of InnoDB will be a very complex storage engine, and for some small applications, it will be slower than MyISAM. But it supports "row lock", so it will be better when there are many write operations. Moreover, it also supports more advanced applications, such as transactions.

(2) Optimize the data type of the field

Remember one principle, the smaller the column will be faster. If a table only has a few columns (such as dictionary table, configuration table), then we have no reason to use INT as the primary key. It will be more economical to use MEDIUMINT, SMALLINT or smaller TINYINT. If you don't need to keep track of time, it's much better to use DATE than DATETIME. Of course, you also need to leave enough room for expansion.

(3) Add an index to the search field

The index does not necessarily mean the primary key or Unique field. If there is a field in your table that you will always use for searching, it is best to index it. Unless the field you want to search is a large text field, then you should create a full-text index.

(4) Avoid using Select * The more data is read from the database, the slower the query will become. Moreover, if your database server and WEB server are two independent servers, this will also increase the load of network transmission. Even if you want to query all fields in the data table, try not to use the * wildcard character. Making good use of the built-in field exclusion definitions may bring more convenience.

(5) Use ENUM instead of VARCHAR

The ENUM type is very fast and compact. In fact, it holds a TINYINT, but it appears as a string. In this way, it becomes quite perfect to use this field to make some choice lists. For example, if the values ​​of fields such as gender, ethnicity, department, and status are limited and fixed, you should use ENUM instead of VARCHAR.

(6) Use NOT NULL as much as possible

Unless you have a very special reason to use NULL value, you should always keep your fields NOT NULL. NULL actually requires extra space, and your program will be more complex when you perform comparisons. Of course, this does not mean that you cannot use NULL. The reality is very complicated, and there will still be situations where you need to use NULL values.

(7) Fixed-length tables will be faster

If all fields in the table are "fixed length", the entire table will be considered "static" or "fixed-length". For example, there are no fields of the following types in the table: VARCHAR, TEXT, BLOB. As long as you include one of these fields, the table is no longer a "fixed-length static table" and the MySQL engine will process it in another way.

Fixed length tables will improve performance because MySQL will search faster because these fixed lengths make it easy to calculate the offset of the next data Quantity, so reading will naturally be fast. And if the field is not of fixed length, then every time you want to find the next one, the program needs to find the primary key.

Also, fixed-length tables are easier to cache and rebuild. However, the only side effect is that fixed-length fields waste some space, because fixed-length fields require so much space regardless of whether you use them or not.

##22, is in the table Select the appropriate data type for the field (physical design)

Field type priority: integer>date,time>enum,char>varchar>blob,text

Give priority to numeric types, followed by date or binary types, and finally string types. For data types of the same level, priority should be given to data that takes up less space. Type

23: Storage period

Datatime: YYYY-MM-DD HH:MM: SS format stores period time, accurate to seconds, occupying 8 bytes of storage space, datatime type has nothing to do with time zone

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

Date: (birthday) takes up less bytes than using string.datatime.int to store. Using date only requires 3 bytes to store the date and month. You can also use The date and time function performs calculations between dates

Time: stores the time part data

Note : Do not use the string type to store date and time data (usually takes up less storage space than strings, you can use date functions when performing search and filtering)

Using int to store date and time is not as good as using the timestamp type.

Related recommendations:

Some MySQL interview questions

The above is the detailed content of MYSQL interview questions (1). For more information, please follow other related articles on the PHP Chinese website!