preface

As a programmer, MySQL must not be unfamiliar, especially in the Internet industry, the use of MySQL is more. For job seekers, MySQL is a key question that will be asked in the interview. Many people dream of big factory, but fail because of MySQL. In fact, MySQL is not difficult, and today’s most comprehensive MySQL summary will help you to “fire” the big factory, so that you will not be confused by MySQL interview.

Note: About MySQL content, including interview questions, study notes, use documents and Xmind mind map several parts, need the full version of MySQL learning resources package, please forward + follow, get free way

 

01, MySQL interview questions set summary

1.1 MySQL Interview Questions (Basic part) :

  • Difference between DROP, TRUNCate, and DELETE
  • What are the database three paradigms?
  • What’s the difference between union and union all?
  • What is the difference between char, varchar2, varchar?
  • What are the merge queries?
  • SQL statement execution sequence
  • The meaning of the null
  • MySQL, SqlServer, oracle write character storage, string conversion time
  • Can the UPDATE statement modify the data in the result set?
  • B tree and B+ tree
  • Have you seen the index? Principles for indexing
  • The type of index, such as a primary key index
  • View the SQL execution plan
  • There are 100,000 pieces of data. Write SQL statements to query several pieces of data with the larger value of a certain field
  • The difference between subquery and associated query
  • MySQL InnoDB, Mysaim features?
  • The difference between optimistic lock and pessimistic lock?
  • What is the difference between row and table locks?
  • What is the database isolation level? What does it do?
  • Basic principles of MySQL primary/secondary synchronization.
  • How to optimize database performance (indexes, database tables, batch operations, paging algorithm, SSD upgrade, service optimization, and primary/secondary deployment)
  • When SQL does not use index (does not include, does not equal, function)
  • Which fields should be indexed (the most filtered fields)
  • MySQL, B+ index implementation, row lock implementation, SQL optimization
  • How to solve the problem of high concurrent inventory reduction
  • Several granularity of database transactions

1.2 MySQL Interview question:

  • Database three paradigm, according to the second kill scenario design data table
  • Primary/secondary replication of databases
  • How to solve deadlock
  • Mysql concurrency (by transaction, isolation level, lock)
  • What does a trigger do?
  • What is a stored procedure? With what?
  • What are the advantages and disadvantages of stored procedures?
  • The difference between stored procedures and functions
  • What does an index do? And what are its advantages and disadvantages?
  • What fields are suitable for indexing
  • What are the index types?
  • What is a transaction? What is a lock?
  • What is a view? What is a cursor?
  • Pros and cons of views
  • List several table join methods, what is the difference?
  • What’s the difference between a primary key and a foreign key?
  • In the database query statement speed is very slow, how to optimize?
  • What are the database three paradigms?
  • What is the difference between Varchar2 and VARCHar?
  • The difference between Oracle and Mysql?
  • The difference between order by and group by

1.3 MySQL Interview Questions (Advanced Section) :

  • Explain the concept and key features of a relational database.
  • What are the typical products, features and application scenarios of relational databases?
  • Explain the concept and key features of a non-relational database.
  • What are the typical products, features and application scenarios of non-relational databases?
  • Please describe the categories of SQL statements and corresponding representative keywords in detail.
  • Describe in detail the difference between char(4) and varchar(4).
  • How to authorize user Oldboy to access the database from 172.16.1.0/24.
  • What is MySQL multi-instance and how to configure MySQL multi-instance?
  • How to strengthen MySQL security, please give feasible specific measures?
  • What is the difference between delete and TRUNCate?
  • How to solve the problem of MySQL Sleep thread overload?
  • What does the sort_buffer_size parameter do? How do I take effect online?
  • How to clean MySQL binlog online?
  • What are the working modes of Binlog? What are the characteristics and how do enterprises choose?
  • A drop database SQL statement was executed by mistake.
  • Mysql > select * from ‘mysqldump’ where ‘a-b’ is used
  • This section describes the principle of MySQL primary/secondary replication and the procedure for configuring primary/secondary replication.
  • How to enable the binlog function of the slave library?
  • How does MySQL implement two-way master-slave replication and describe the application scenarios?
  • How does MySQL implement cascading synchronization and describe the application scenario?
  • How can I solve the primary/secondary replication failure of MySQL?
  • How do I monitor whether primary/secondary replication fails?
  • How to implement read/write separation in MySQL database?
  • How can I restore the production One Master Multiple Slave Database?

MySQL interview answer:

 

If you need the full version of MySql learning Resources package, please click here to get it for free

 

02, About the actual use of MySQL

2.1 21 best practices for MySQL Performance optimization

  1. Optimize your query for query caching
  2. EXPLAIN your SELECT query
  3. LIMIT 1 is used when there is only one row of data
  4. Build indexes for search fields
  5. Use a similar type of example when joining a table and index it
  6. Don’t ORDER BY RAND()
  7. Avoid SELECT *
  8. Always set an ID for each table
  9. Use ENUM instead of VARCHAR
  10. Get advice from PROCEDURE ANALYSE()
  11. Use NOT NULL whenever possible
  12. Prepared Statements
  13. Unbuffered query
  14. Save the IP address as an UNSIGNED INT
  15. Fixed length tables are faster
  16. Vertical segmentation
  17. Split large DELETE or INSERT statements
  18. The smaller the column, the faster it will be
  19. Select the correct storage engine
  20. Use an Object Relational Mapper
  21. Beware of permalink

 

2.2 MySQL Performance Tuning and Architecture Design — Full volume

  • Basic article:

MySQL storage engine, MySQL security management, MySQL backup and recovery

  • Performance Optimization:

Related factors affecting MySQLServer performance, MySQI database locking mechanism, MySQL database Query optimization, MySQL database Schema design performance optimization, MySQLServer performance optimization, common storage engine optimization

  • Architectural Design:

Basic principles of MySQL extensible design, MySQLReplication extensible design, data segmentation extensible design, use of C ache and Se AR CH extensible design, MySQLCluster, high availability design ideas and schemes, high availability design of MySQL monitoring

 

2.3 MySQL from entry to project practice

  • Chapter 1 Basic Knowledge

Hello MySQL, database exploration, the use of common MySQL management tools, MySQL database basic operation

  • Chapter 2 Core application

MySQL database view, MySQL data types and operators, MySQL functions, MySQL database query statement details, MySQL database data and index operations, stored procedures and storage functions, use MySQL trigger

  • Chapter 3 Core technology

MySQL database permission management and recovery, MySQL database replication, MySQL log management, using MySQL to build distributed applications, MySQL query cache, MySQL error code and message use

  • Chapter 4 Advanced Applications

MySQL database connection in C#, MySQL database connection in Java, MySQL database connection in PHP

  • The fifth project actual combat

Project actual combat planning stage — project development and planning, project actual combat entry stage — forum management system database development, project actual combat improvement stage — enterprise member management system database development, project actual combat advanced stage — news release system database development

 

03, About learning MySQL mind Map (Xmind)

MySQL optimization problem (Xmind)

Transaction, lock, SQL optimization principle, JION principle, execution plan and execution details, execution process, impact of table structure on performance, index

 

conclusion

In a word, learning is their own business, as a developer, MySQL is a must to master, if you have dachang dream, the foundation can not be too bad, if you have a lot of questions about MySQL, then this MySQL summary package must have!

If you need the full version of MySql learning Resources package, please click here to get it for free