As a database knowledge point, no matter what language will definitely ask, when asked about MySql must be hard, for everyone to prepare 50 questions related to MySql interview, first understand these, I continue to prepare behind. As long as you follow me, I will keep updating.

What is a database?

A database is “a warehouse that organizes, stores, and manages data according to its data structure.” It is an organized, shareable, uniformly managed collection of large amounts of data stored in a computer for a long time.

2. How do I view the syntax of an operation?

For example, look at the syntax for creating a table:

mysql> ? create table
Name: 'CREATE TABLE'Description: Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]  [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expression CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_nameCopy the code

3. What are the storage engines of MySql?

MyISAM, InnoDB, BDB, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED.

InnoDB and BDB provide transaction safe tables, other storage engines are non-transaction safe tables.

4. What are the two common storage engines?

1.Myisam is the default storage engine of Mysql. When create creates a new table, Myisam is used by default if the storage engine of the new table is not specified.

Each MyISAM is stored as three files on disk. The file names are the same as the table names, with.frm (storing table definitions),.myd (MYData), and.myi (MYIndex) extensions.

Data files and index files can be placed in different directories, evenly distributed IO, for faster speeds.

2.InnoDB storage engine provides transaction security with commit, rollback and crash recovery capabilities. But compared to Myisam’s storage engine, InnoDB writes less efficiently and takes up more disk space to retain data and indexes.

6. Can the engine be set for tables? How do I set it?

Yes, ENGINE= XXX sets the ENGINE.

Code examples:

create table person(
   id int primary key auto_increment,
   username varchar(32)
) ENGINE=InnoDB
Copy the code

6. Select an appropriate storage engine.

Selection criteria: Select an appropriate storage engine based on application characteristics. For complex application systems, you can combine multiple storage engines based on actual conditions. The following are the applicable environments for common storage engines:

  1. MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in Web, data warehousing, and other application environments.

  2. InnoDB: For transaction processing applications with numerous features including ACID transaction support.

  3. Memory: Keeps all data in RAM, providing extremely fast access in environments where references and other similar data need to be found quickly.

  4. Merge: Allows a MySQL DBA or developer to logically combine a series of equivalent MyISAM tables and reference them as one object. It is ideal for VLDB environments such as data warehousing.

7. Select the appropriate data type

Prerequisite: Use a suitable storage engine.

Selection principle: Determine how to select an appropriate data type based on the selected storage engine. The following selection methods are classified by storage engine:

  1. MyISAM data storage engine and data columns

    MyISAM data table, it is best to use fixed length data columns instead of variable length data columns.

  2. MEMORY Stores engines and data columns

    MEMORY tables are currently stored in fixed-length rows, so it doesn’t matter whether a CHAR or VARCHAR column is used. Both are handled as CHAR types.

  3. InnoDB stores engines and data columns

    The VARCHAR type is recommended

    For InnoDB tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all rows use a header pointer to the value of the data column), so it is not necessarily easier to use a fixed-length CHAR column per se than a variable-length VARCHAR column. Thus, the primary performance factor is the amount of storage used for data execution. Since CHAR takes up more space on average than VARCHAR, it is good to use VARCHAR to minimize the total amount of storage and disk I/O that need to be processed for rows of data.

8.char & varchar

There are different ways of saving and retrieving. They also differ in terms of maximum length and whether trailing Spaces are reserved. There is no case conversion during storage or retrieval.

9. Mysql character set

Mysql server can support multiple character sets (you can use the show character set command to view all the character sets supported by mysql). Different character sets can be specified on the same server, in the same database, or even for different fields of the same table.

The CHARACTER set of mysql includes CHARACTER and COLLATION.

10. How to select a character set?

It is recommended to use a small character set as long as it can fully meet the requirements of the application. A smaller character set means space savings, fewer bytes transferred over the network, and an indirect improvement in system performance due to the smaller storage space.

There are many character sets that can hold Chinese characters, such as UTF8, GB2312, GBK, latin1, and so on, but gb2312 and GBK are commonly used. Because gb2312 is smaller than GBK and some out-of-your-way words (such as: distill) cannot be preserved, you must weigh the likelihood and impact of such out-of-your-way words in application when selecting a character set; GBK is the best choice if you cannot make a firm decision as to whether you will fall out with them or not.

What is an index?

In relational databases, an index is a single, physical storage structure that sorts the values of one or more columns in a database table. It is a set of values of one or more columns in a table and the corresponding logical pointer list to the data page in the table that physically identifies these values. Index function is equivalent to a book catalog, you can quickly find the required content according to the page number in the catalog.

12. Index design principles?

  1. The index column to search for is not necessarily the column to select. The best columns for an index are those that appear in the WHERE clause, or specified in the join clause, rather than in the selection list after the SELECT keyword.

  2. Use unique indexes. Consider the distribution of values in a column. Indexes work best for columns with unique values, and worst for columns with multiple duplicate values.

  3. Use short indexes. If you index a column, you should specify a prefix length, whenever possible. For example, if you have a CHAR(200) column, do not index the entire column if multiple values are unique within the first 10 or 20 characters.

  4. Use the left-most prefix. When you create an index with n columns, you actually create n indexes available to MySQL. A multi-column index can function as several indexes because rows can be matched using the leftmost set of columns in the index. Such a set of columns is called the left-most prefix. (This is different from indexing a column prefix, which uses the n prefix as the index value.)

  5. Don’t over-index. Each additional index takes up additional disk space and reduces write performance, as we’ve already covered. When the contents of a table are changed, the indexes must be updated, and sometimes refactored, so the more indexes, the longer it takes.

    Having an index that is rarely or never used can unnecessarily slow down table changes. In addition, MySQL takes into account individual indexes when generating an execution plan, which can take time.

    Creating redundant indexes adds more work to query optimization. Too many indexes can also prevent MySQL from choosing the best index to use. Keeping only the required indexes facilitates query optimization. If you want to add an index to an indexed table, you should consider whether the index you want to add is the leftmost index of an existing multi-column index.

  6. Consider the type of comparison performed on the column. Indexes can be used for “<“, “< =”, “=”, “> =”, “>”, and BETWEEN operations. Indexes are also used for LIKE operations when the schema has an immediate quantity prefix. If a column is only used for other types of operations (such as STRCMP()), there is no value in indexing it.

13. What indexes does MySql have?

  • Data structure perspective
  1. BTREE
  2. HASH
  3. FULLTEXT
  4. R-Tree
  • Physical Storage Angle

Clustered Index

2. Non-clustered index

  • Logically
  1. Plain index: accelerates queries only
  2. Unique index: accelerated query + column value unique (can have NULL)
  3. Primary key index: accelerated query + unique column value (no NULL) + only one in the table
  4. Combined index: An index composed of multiple column values that is specifically used for combined searches and is more efficient than index merges
  5. Full-text index: segmentation of text content, search

14. The underlying implementation principles of Hash index and B+ tree index?

The underlying hash index is a hash table. During query, you can call the hash function once to obtain the corresponding key value, and then query back to the table to obtain the actual data.

The bottom realization principle of B+ tree is multi-path balanced search tree. For each query, the root node is the starting point, and the key value can be obtained when the leaf node is queried, and then the query is judged whether it needs to be queried back to the table.

The difference between:

A hash index

1: Hash index equivalency query is faster (in general) but range query is not possible. After the hash function is used to create indexes in the hash index, the index order cannot be the same as the original order, and range query cannot be supported.

2: The hash index does not support fuzzy query and left-most prefix matching of multi-column indexes. Because the hash function is unpredictable, the indexes of eg:AAAA and AAAAB have no correlation.

3: The hash index cannot avoid data query back to the table at any time.

4. Although hash index is fast in equivalent value query, it is unstable and unpredictable in performance. When a certain key value has a large number of repetitions, hash collisions occur, and the query efficiency may be very poor.

5: Hash index sorting is not supported because the hash function is unpredictable.

B + tree

1: All nodes of a B+ tree follow the rules (the left node is smaller than the parent node, the right node is larger than the parent node, and the same is true for multi-fork trees).

2: when certain conditions (cluster index, overwrite index, etc.) are met, the query can be completed only through the index. There is no need to query back to the table.

3: The query efficiency is relatively stable. All queries are from the root node to the leaf node, and the height of the tree is relatively low.

conclusion

In most cases, choosing B+ tree indexes directly can achieve stable and good query speed without using Hash indexes. Link: blog.csdn.net/qq_44590469…

15. Must non-clustered indexes be queried back into the table?

Not necessarily. This involves whether all the fields required by the query match the index. If all the fields match the index, then there is no need to perform the query back to the table.

Select age from employee where age < 20; select age from employee where age < 20; select age from employee where age < 20;

16. How do I query the last row?

select * from table_name order by id desc limit 1;
Copy the code

17.MySQL self-added ids are not consecutive.

  • Unique key conflict
  • Transaction rollback
  • Apply for the policy of adding an ID in batches

SQL injection problem?

Cause: The parameters imported by the user are injected into the SQL syntax, which destroys the original SQL structure semantics and achieves attack effect.

19. What is the 3NF paradigm?

  • 1NF means that any attribute in the database table is atomic and cannot be decomposed
  • 2NF is a uniqueness constraint on records that requires records to have a unique identity, that is, the uniqueness of the entity
  • 3NF is a constraint on field redundancy, that is, no field can be derived from any other field, and it requires that fields have no redundancy

20. NULL and NULL string judgments?

A NULL value is no value, it’s not an empty string. If you specify ”(two single quotes, no characters between them), this is allowed in the NOT NULL column. An empty string is a valid value, it is not null.

To check for NULL, use IS NULL or IS NOT NULL.

21. What is a transaction?

It can be used to maintain database integrity by ensuring that batch MySQL operations are either performed completely or not at all.

22. Transaction 4 features?

A transaction must satisfy four conditions (ACID) :

  • ** Atomicity: ** The smallest unit of execution in which all operations in a transaction either complete or not complete.
  • ** Consistency: ** the transaction is consistent before and after execution.
  • ** Isolation: the ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently.
  • ** Durability: ** Modifications to data are permanent after transactions complete and will not be lost even if system failures.

23. What are the transaction isolation levels?

  • READ_UNCOMMITTED this is the lowest isolation level for a transaction and allows another transaction to see the uncommitted data of that transaction. Solve the first type of missing update problem, but there will be dirty read, unrepeatable read, the second type of missing update problem, unreal read.
  • READ_COMMITTED ensures that data modified by one transaction can be read by another transaction only after it is committed. That is, another transaction cannot read data that is not committed by the same transaction. Solve the first type of lost update and dirty read problem, but there will be unrepeatable read, the second type of lost update problem, unreal read problem
  • REPEATABLE_READ guarantees that the data obtained twice before and after a transaction is consistent under the same condition (note: it is a transaction, can be understood as the data between transactions does not affect each other). It solves the problem of the first type of lost update, dirty read, unrepeatable read, and the second type of lost update, but it can produce magic read.
  • SERIALIZABLE transactions are executed in serial mode to solve dirty read, unrepeatable read and phantom read. But it’s very inefficient, so it’s usually not used in practice.

InnoDB default transaction isolation level? How do I view the current isolation level

REPEATABLE READ

To view:

mysql> select @@global.tx_isolation; + -- -- -- -- -- -- + | @ @ global. Tx_isolation | + -- -- -- -- -- -- + | the REPEATABLE - READ | + -- -- -- -- -- - + 1 row in the set, 1 warning (0.01 SEC)Copy the code

What is a lock?

Database locks are used to support concurrent access to shared resources to ensure data integrity and consistency. In this way, data will not be damaged when accessing the database in high concurrency.

26. The deadlock?

It refers to the phenomenon of two or more processes waiting for each other because they compete for shared resources.

27. How do I handle deadlocks?

  • Set the timeout period. Automatically released after timeout.

  • Initiate deadlock detection to actively roll back one of the transactions and let the other transactions continue.

28. How do I create a user? Authorization?

Create a user:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
Copy the code

Authorization:

GRANT privileges ON databasename.tablename TO 'username'@'host';
Copy the code
  • Username: indicates the username
  • Host: indicates the IP address of the host that can be logged in to. Local users are represented by localhost, and any remote host is represented by the wildcard %.
  • Password: specifies the password for logging in to the server. If the password can be left blank, no password is required
  • Databasename: indicates the databasename.
  • Tablename: indicates the tablename. * represents all tables.

29. How do I view the table structure?

​ desc table_name;

mysql> desc zipkin_spans; + + - - - - - - - - - - - + - + - + - + - + | Field | Type | Null | Key | Default | Extra | + + - - - - - - - - - - - + - + - + - + - + | trace_id_high | bigint(20) | NO | PRI | 0 | | | trace_id | bigint(20) | NO | PRI | NULL | | | id | bigint(20) | NO | PRI  | NULL | | | name | varchar(255) | NO | MUL | NULL | | | parent_id | bigint(20) | YES | | NULL | | | debug | bit(1) | YES | | NULL | | | start_ts | bigint(20) | YES | MUL | NULL | | | duration | bigint(20) | YES | | NULL | | + + - - - - - - - - - - - + - + - + - + - + 8 rows in the set (0.01 SEC)Copy the code

Mysql > alter table drop table The difference between them?

1. Delete: deletes only table data, supports conditional filtering, and supports rollback. Log. So it’s slower.

delete from table_name;
Copy the code

2. Truncate: Only all data is deleted. Conditional filtering and rollback are not supported. No log is recorded, which is more efficient than DELETE.

truncate table table_name;
Copy the code

3. Drop: Deletes the table data and the table structure. Free up all the space occupied by the table. The deletion efficiency is the highest.

drop table table_name;
Copy the code

31. Do you like to go to the index?

Xxx% goes to index, % Xxx does not go to index.

32. What is a callback table?

After the primary key index is found in the normal index, the primary key index is used to locate the record. This is equivalent to saying that a non-primary key index needs to go one more index tree.

33. How do I avoid returning to the table?

The index overwrites the field being queried.

34. What is index coverage?

If an index contains (or overwrites) the values of all the fields that need to be queried, it is called a ‘overwrite index’.

35. Pros and cons of views?

advantages

Simplicity, data is what you see is what you get

Security, users can only query or modify the data they can see

Logical independence, which can be shielded from real table structure changes

disadvantages

Performance is relatively poor, and simple queries can become slightly more complex

It is not easy to modify, and the special change is that the aggregation view is complex and basically cannot be modified

36. What is the difference between a primary key and a unique index?

Essential difference, primary key is a constraint, unique index is an index.

Primary keys cannot be empty (non-empty + unique), and unique indexes can be empty.

A primary key can be a foreign key of another table, but a unique index cannot.

A table can have only one primary key and multiple unique indexes.

Can create a federated primary key or federated unique index.

Primary key -> clustered index, unique index -> non-clustered index.

37. How do I randomly obtain a record?

SELECT * FROM table_name ORDER BY rand(a)LIMIT 1; 
Copy the code

38. Value types in Mysql?

39. What indexes does the current table have?

show index from table_name;
Copy the code

40. What happens when an index does not take effect?

  • Use is not equal to query
  • A NULL value
  • Columns are involved in mathematical operations or functions
  • The left side of the string like is the wildcard. Such as XXX %
  • Mysql does not use indexes when parsing full table scans faster than using indexes.
  • When using a federated index, the first condition is a range query, and the second condition cannot use the index even if it complies with the left-most prefix rule.

41. MVVC?

MVCC stands for multi-version concurrency Control system. InnoDB’s MVCC is implemented by storing two hidden columns at the end of each row. One holds the time when the row was created and the other holds the time when the row expired (deleted). Of course, the stored time is not the real time, but the system version number. Each time a new transaction is started, the system version number is automatically added. The system version number at the start time of the transaction is used as the version number of the transaction. The version number of each row is queried for comparison.

42. SQL statement execution flow?

The client connects to the database and authenticates.

Obtain the current user permission.

When you query, you go to the cache first to see if there is a return.

If not, the parser performs lexical analysis on the SQL.

The optimizer “optimizes” the SQL as it sees fit.

The executor is responsible for executing the SQL statement.

Finally, the data is returned to the client.

43. How do I obtain the SELECT statement execution plan?

explain sql;

44. What is in the Explain column? Meaning?

A, id

Serial number in SQL query.

The larger the id column number, the better the execution. If the id column number is the same, the execution is performed from the top down.

Second, the select_type

Third, the table

Show which table this row is about. This may not be an actual table name. Can be the following values:


  • : result of referencing ids M and N UNION.
    ,n>
  • : references the table derived from the result with id N. A derived table can be a result set, such as the result derived FROM a FROM neutron query.
  • : references the table materialized from the subquery result with id N. That is, a temporary table is generated to hold the results of the subquery.

Four, the type of

This is one of the most important fields that shows what type the query is using. The connection types, from best to worst, are:

System, const, eq_ref, ref, fulltext, ref_or_NULL, index_merge, unique_subquery, index_subquery, range, index, ALL

1, the system

A table with only one row of data or an empty table is a special case of const type. And only for MyISAM and Memory tables. In an Innodb engine table, the type column is usually all or index in this case

2, const

At most one row of records matches. The join type is const when all fields of the union primary key or unique index are compared to constant values. Other databases are also called unique index scans

3, eq_ref

When a multi-table JOIN occurs, only one row can be found in the current table for each row from the previous table. This is probably the best type besides system and const. This type is used when all fields of a primary key or unique non-null index are used as join joins.

Eq_ref can be used for indexed columns that use the ‘=’ operator for comparison. The value can be a constant or an expression that uses a column of a table that was read before this table.

The difference from ref below is that it uses a unique index, that is, a primary key or a unique index, whereas REF uses a non-unique index or a normal index. Eq_ref can only find one row, whereas ref can find multiple rows.

4, ref

For each row from the previous table, multiple rows can be matched in the index of this table. Use the ref type when the join applies only to the leftmost prefix of the index or when the index is not a primary key or unique index (that is, the join can match multiple row records).

Ref can be used for indexed columns that are compared using the ‘=’ or ‘<=>’ operators.

5、 fulltext

This type is used when using full-text indexes. Note that full-text indexes are of high priority. If both full-text indexes and normal indexes exist, mysql will use full-text indexes regardless of the cost

6, ref_or_null

Similar to the ref type, but with the addition of null comparisons. Not much actually.

7, index_merge

Ref_or_null indicates that the query uses more than two indexes, and finally selects the intersection or union. The common and, or conditions use different indexes. The official sort is after ref_OR_NULL, but in practice, the performance may be worse than range most of the time because multiple indexes are read

8 unique_subquery.

Used for in subquery in where, subquery returns a unique value that does not repeat the value, can completely replace the subquery, more efficient. This type replaces the following form of IN subquery ref: value IN (SELECT primary_key FROM single_table WHERE some_expr)

9 index_subquery.

This join type is similar to unique_subquery. Applies to non-unique indexes and can return duplicate values.

10 and the range

Index range queries, common use =, < >, >, > =, <, < =, IS NULL, the < = >, BETWEEN, IN () or the like operator IN the query.

11, the index

Index full table scan, sweep the index from top to bottom. There are two cases: one is if the query uses an overwrite index, then it only needs to scan the index to get the data, which is faster than a full table scan because the index is usually smaller than the data table and avoids a second query. Using index is displayed in extra. Otherwise, if a full table scan is performed on an index, there is no Using index prompt.

12, all

Full table scan has the worst performance.

Fifth, possible_keys

The indexes that might be used by the query are listed here.

Sixth, the Key

The key column shows the actual key (index) used by MySQL.

To FORCE MySQL to USE or IGNORE the possible_keys column INDEX, you can USE FORCE INDEX, USE INDEX, or IGNORE INDEX.

If select_Type is index_merge, more than two indexes may appear, and only one of other select_types may appear.

Seven, key_len

Represents the number of bytes used in the index.

Key_len only calculates the length of the index used by the WHERE condition, and sorting and grouping do not count to key_len if indexes are used.

Without sacrificing accuracy, the shorter the length, the better.

Eight, ref

Represents the join match criteria for the above table, that is, which columns or constants are used to find values on indexed columns.

Nine, rows

Rows is also an important field. This is mysql’s estimate of the number of rows that need to be scanned (not an exact value).

Ten, Extra

This column contains the details of how MySQL resolves queries in the following cases:

  • Using WHERE: Column data is returned from a table that only uses the information in the index without reading the actual action. This occurs when all the requested columns of the table are part of the same index, indicating that the mysql server will filter the rows after the storage engine retrits them.
  • Using temporary: indicates that MySQL needs to use temporary tables to store result sets. This is common for sorting and grouping queries.
  • Using filesort: a sort operation in MySQL that cannot be done Using an index is called “filesort”.
  • Using join buffer: The change emphasizes that no index is used when retrieving join conditions and that the join buffer is needed to store intermediate results. If this value is present, it should be noted that indexes may be added to improve performance depending on the query.
  • Impossible WHERE: This value emphasizes that the WHERE statement will result in no eligible rows.
  • Select Tables Optimized Away: This value means that the optimizer may return only one row from the aggregate function result by using the index alone.

Link: www.jianshu.com/p/8fab76bbf…

45. How many index columns can be created in MySql?

16

46. Why is it best to create a primary key?

Primary keys ensure the uniqueness of data rows in the entire table. You are advised to add a self-growing ID column as the primary key even if the table does not have a primary key. After setting the primary key, it is possible to make subsequent deletions faster and ensure the safety of the operation data range.

47.Why is not NULL recommended for a field?

MySQL官网这样介绍:

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

Null values take up more bytes and cause a lot of mismatches in your program.

48.What do varchar(10) and int(10) stand for

Varchar 10 represents the requested space length, is also the maximum length of data can be stored, while int 10 only represents the displayed length, less than 10 bits are filled with zeros. That is,int(1) and int(10) can store the same number of digits and occupy the same amount of space, except that they are displayed by length.

49. What is a view? Comparison of ordinary table advantages?

A View is a virtual table that is essentially transparent to the user of the View. The view does not actually exist in the database; the row and column data comes from the tables used in the query that defines the view and is generated dynamically when the view is used.

The main advantages of views over regular tables include the following.

  • Simple: Users of a view need not care about the structure, association conditions, and filtering conditions of the corresponding table.

    A result set that is already a filtered compound condition to the user.

  • Security: Users using views can access only the result sets they are allowed to query; permission management on tables does not

    Restrict to a row or column, but this can be done simply through views.

  • Data independence: Once the structure of the view is determined, the user can be shielded from changes in the table structure and source tables are added

    Columns have no effect on views; If the source table changes the column name, it can be resolved by modifying the view without affecting visitors.

50. How is count(*) implemented in different engines?

MyISAM: Saves the total number of rows in a table on disk. Count (*) returns this number directly.

InnoDB: cumbersome. When it executes count(*), it reads data line by line from the engine and accumulates the count.

Reference:

  • MySQL in Simple Form
  • High Performance MySql
  • Inside MySQL Technology (5th Edition)
  • MySQL Must Know must Know
  • Geek time: MySQL Combat 45 lecture
  • Baidu encyclopedia

New blogger for three, at the same time pay attention to my public account [Java small coffee show] reply interview can be a white whone “Java full level engineering lion interview questions. PDF” continue to update, adhere to ✊✊✊