Search the whole network, sort out the MySQL interview questions, and attach the answers.

I have compiled all Java-related interview questions and answers into PDF, and bookmarked the table of contents, which is very convenient to read
questions and answers PDF download : https://www.hicxy.com/?p=2645
questions and answers PDF download : https://www.hicxy.com/?p=2645
questions and answers PDF download : https://www.hicxy.com/?p=2645

1. When the column value is NULL, will the query use the index?

Columns with NULL values are also indexed in MySQL. Of course, if you plan to index a column, try to avoid setting it as nullable. MySQL is difficult to optimize queries that reference nullable columns, and it will make indexes, index statistics, and values more complicated.

2. Talk about the three paradigms

Form (1NF): The fields in the 1616ea53d4f7a1 database table are all single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real, character, logical, date, etc.
Form (2NF): 1616ea53d4f7c8 database table does not have partial functional dependence of non-key fields on any candidate key field (partial functional dependence refers to the existence of certain fields in the combined key to determine non-key fields ), that is, all non-key fields are completely dependent on any set of candidate keywords.
(3NF): is based on the second normal form. If there is no transfer function dependency of a non-key field on any candidate key field in the data table, it conforms to the third normal form. The so-called transfer function dependency means that if there is a decision relationship of "A → B → C", then the transfer function of C depends on A. Therefore, a database table that satisfies the third normal form should not have the following dependencies: key field → non-key field x → non-key field y

3. Features of innodb engine

Insert buffer
Double write
Adaptive hash index (ahi)
Read ahead

4. As mentioned above, the B+ tree does not need to go back to the table to query data when it meets the clustered index and the covering index. What is a clustered index?

In the B+ tree index, the leaf node may store the current key value, or it may store the current key value and the entire row of data. This is the clustered index and the non-clustered index. In InnoDB, only the primary key index is For a clustered index, if there is no primary key, a unique key is selected to build a clustered index. If there is no unique key, a key is implicitly generated to build a clustered index.

When the query uses a clustered index, the entire row of data can be obtained at the corresponding leaf node, so there is no need to perform the back-to-table query again.

5. Does your database support emoji expressions, if not, how to operate?

If it is utf8 character set, you need to upgrade to utf8_mb4 to support

For the following questions, you can first think about it for yourself.
In addition, I have sorted out all Java-related interview questions and answers for your reference
questions and answers PDF download : https://www.hicxy.com/?p=2645
questions and answers PDF download : https://www.hicxy.com/?p=2645
questions and answers PDF download : https://www.hicxy.com/?p=2645

6. Index Classification

7. How to solve these problems? Do you understand the MySQL transaction isolation level?

8. A 600 million table a, a 300 million table b, through the external tid association, how can you query the 200 data records in the 50,000th to the 50200th meeting the conditions in the fastest way?

9. MyISAM index implementation?

10. Scenarios for indexing

11. What are the application scenarios of MyISAM storage engine?

12. What if there are multiple transactions in progress at the same time?

13. What is MySQL?

14. What is an index? What are the effects and advantages and disadvantages?

15. Do you know MySQL locks?

16. What are the advantages of using MySQL views?

17. What is myisamchk used for?

18.So under what circumstances will it happen that an index is created for the column but is not used in the query?

19. If you want to store a user's password hash, what field should be used to store it?

20. MySQL replication basic principle process

21. What are the advantages of relational databases?

22. Explain performance analysis

23. Notes on MySQL Index

24. MySQL replication principle and process

25. What are the classifications of SQL statements?

26. What kinds of locks are there in MySQL?

27. The impact of indexes on performance:

28. Internal connections are divided into three categories

29. Types of Indexes

30. FULL JOIN

31. There are several input formats for MySQL binlog? What are the differences?

32. Storage period

33. How to build an index for the following three sql, how to build only one?

34. Syntax for creating an index

35. The difference between varchar and char in MySQL and the meaning of 50 in varchar(50)

36. Locking Mechanism and InnoDB Locking Algorithm

37. What problems do concurrent transactions bring?

38. How to deal with oversized paging?

39. What is a stored procedure?

40. What are the four major features of the InnoDB engine?

41. What is the difference between delete, drop and truncate in the database delete operation?

42. Trigger usage scenarios

43. Briefly describe the difference between indexes, primary keys, unique indexes, and joint indexes in MySQL. What are the effects on database performance?

44. Can the use of index query definitely improve the performance of the query? why

45. Some methods of SQL statement optimization?

46. Which engine does SELECT COUNT(*) perform faster?

47. What do varchar(10) and int(10) mean?

48. The difference between CHAR and VARCHAR?

49. What is SQL

50. Lock optimization strategy

51. MySQL Transaction Processing

52. Outer join (LEFT JOIN/RIGHT JOIN)

53. How to optimize SQL

54. What kind of locks does MySQL have? Isn't it a bit hindering concurrency efficiency to lock like the above?

55. What are the reasons for SQL injection vulnerabilities? How to prevent it?

56. Use auto-increment ID or UUID for primary key?

57. The difference between indexes

58. MySQL lock mechanism

59. B-Tree and B+Tree

60. What MySQL index related optimizations have been done

61. What is a stored procedure? What are the advantages and disadvantages?

62. What is a MySql view?

63. If a table has a column defined as TIMESTAMP, what will happen?

64. What field type is good for recording currency in MySQL

65. What is a transaction?

66. Have you ever been concerned about the time-consuming SQL in the business system? Have you ever counted slow queries? How have you optimized slow queries?

67. Advantages and disadvantages of indexes

68. Storage engine commonly used commands

69. What is the application scenario of InnoDB storage engine?

70. MySQL index creation principles

71. Which isolation level does Innodb use?

72. Will non-clustered indexes always return to table queries?

73. Why use B+ tree as index instead of hash table?

74. The difference between varchar and char in MySQL and the meaning of 50 in varchar(50)

75. What are the types of MySQL storage engines?

76. What are the parts of MySQL and what are they used for?

77. What are the characteristics of the MyISAM storage engine?

78. Briefly talk about the difference between drop, delete and truncate

79. Is unique index faster than normal index and why

80. What is a relational database?

81. Why is the field required to be defined as not null?

82. How to get the current date in MySQL?

83. What is ACID? Can you elaborate on it?

84. The difference between MyISAM index and InnoDB index?

85. What is the difference or pros and cons between Hash index and B+ tree?

86. Will the following statement apply an index: SELECT FROM users WHERE YEAR(adddate) <2007;

87. Please explain the difference between InnoDB and MyISAM

88. Has the created index been used? Or how can I know the reason why this statement runs slowly?

89. Overview of transaction rollback mechanism in Myql

90. When building an index, what factors need to be considered?

91. Innodb transaction and log implementation

92. What is the difference between varchar and char in MySQL.

93. What are the optimistic and pessimistic locking of the database?

94. Horizontal sub-table and vertical sub-table, can you cite an example that suits them?

95. Methods of optimizing the database

96. The MySQL database is used as the storage of the publishing system, with an increment of more than 50,000 entries per day. It is expected to operate and maintain for three years. How to optimize?

97. Briefly describe the difference between indexes, primary keys, unique indexes, and joint indexes in MySQL, and how they affect the performance of the database (from both read and write)

98. Please explain the difference between varchar and text

99. What is the difference between InnoDB and MyISAM?

100. What data types does MySQL have?

101. What is a view? And what are the usage scenarios of the view?

102. What are super keys, candidate keys, primary keys, and foreign keys?

103. Does int(10) and bigint(10) can store the same data size?

104. Explain the difference between MySQL outer connection, inner connection and self connection

105. Common Operations of MySQL

106. Principle of MySQL Index

107. What is the difference between FLOAT and DOUBLE?

108. What kind of data structure is an index?

109. The basis of indexing

110. How does MySQL ensure data consistency during replication and reduce data synchronization delay?

111. Under what circumstances the index is set but cannot be used

112. What should I pay attention to when creating a MySQL joint index?

113. How many UTF8 encoded Chinese characters can be stored in VARCHAR(50)?

114. Why try to set a primary key?

115. What is the default port number of the MySql service?

116. What is a joint index? Why do you need to pay attention to the order in the joint index?

117. How can I see all the indexes defined for the table?

118. What storage engines does MySQL support?

119. Stored procedures and functions seem to be similar, what is the difference between them?