Recently, while reviewing some notes related to MySQL database, I suddenly want to ask: why do I have to learn MySQL to learn Java?

To put it bluntly, no matter what kind of application (desktop application, web application, APP application, etc.), the server uses a relational database as its container for storing data, namely MySQL database. MySQL is among the most popular relational database management systems (RDBMS). And database technology is one of the skills every developer must have, no matter what language they are learning. The New Year’s day holiday of 2021 has passed, and a busy year has started again. Recently, ACCORDING to my review and summary, I specially give everyone benefits, and present my “exclusive new” MySQL advanced package, which is absolutely blood profit! Don’t believe it!

Note: If you want to learn more about MySQL, please download the original version.

Please note: Poke here – get code – dig ！！！！

As an advanced package, it is necessary to talk about the best PDF – the Path to the King of MySQL

Divided into: bronze, silver, gold, platinum, diamond, star yao, king and other 7 parts

Part 1 Stubborn Bronze (Chapters 1~8) : includes roughly the following

  • Introduction of MySQL

  • Major branch version

  • Database installation/startup/shutdown

  • Rights management

  • Memory pool structure of the MySQL database

  • The storage engine

  • The thread function

  • The refresh mechanism

  • Database file

  • Table management

  • Character set

  • Methods of statistical information and data fragmentation

  • A detailed reading of the index

  • Analysis of execution plans

  • Stress tests on display

  • Introduction to transactions

  • Isolation levels are explained in depth and are expanded with locks

Part 2 Order silver Chapter (Chapter 9) : generally includes the following contents

  • Logical backup

  • Raw File Backup

  • Mysqldump, select… Into Outfile, MyDumper and Xtrabackup

Part 3: Glorious Gold (chapters 10~11) : includes roughly the following

  • Principle of master-slave replication

  • Explain the replication parameters in detail

  • Comprehensive interpretation and practice of semi-synchronous replication, multi-source replication and GTID replication

  • This section describes the verification and replication management techniques for replication data consistency

  • And read all kinds of errors in master and slave replication

Part 4: Noble Platinum (chapters 12~15) : generally includes the following contents

  • Internet mainstream MySQL high availability cluster architecture

  • Analysis, maintenance management and practice of MHA/Keepalived/PXC principle

  • ProxySQL is a powerful MySQL intermediate proxy layer application

Part 5: Eternal Diamonds (chapters 16~17) : covers roughly the following

  • New features in MySQL 5.7

  • Through hardware, operating system, database, program design these four dimensions to comprehensively introduce MySQL database optimization

Part 6 Supreme Star Flare (chapters 18~19) : includes roughly the following

  • Learn to deploy Lepus

  • Monitor MySQL and version updates

Part 7: The Best Of Kings (Chapter 20) : MySQL Interview summary

Speaking of MySQL interview, I also specially searched for a database interview question said to be ali P8 level

This P8 level interview summary, from the basics, index, locking, logging, tuning and some open questions, WE can test their knowledge layer by layer, can not see the answer analysis

  • MySQL > MySQL

  • 1. Describe the internal process of executing a query in MySQL.

  • 2. Which node is displayed when MySQL displays “this column does not exist”?

  • 3. What are the advantages and disadvantages of MySQL query caching?

  • 4. How do I disable the query cache function of MySQL?

  • 5. What are the common engines of MySQL?

  • 6. Can MySQL set database engine for table level? How do I set it?

  • 7. What is the difference between InnoDB and MyISAM?

  • 8. What are InnoDB’s features?

  • 9. There are three data in a self-increment table. After two data are deleted, the database is restarted and another data is added.

  • MySQL > alter table select * from user where primary keys cannot be consecutively added.

  • 11. Can auto-increment primary keys in InnoDB be persisted?

  • 12. What are independent and shared table Spaces? What’s the difference?

  • 13. How to set an independent tablespace?

  • 14. How do I shrink a tablespace?

  • 15. Talk about the execution process of rebuilding the table?

  • 16. Where does the table structure information exist?

  • 17. What is an overwrite index?

  • 18. If you delete the primary key of an InnoDB table, is there no primary key?

  • 19. After executing an UPDATE statement, I went to hexdump to view the ibD file directly. Why didn’t I see any changes?

  • 20. What is the difference between memory tables and temporary tables?

  • 21. What are the problems associated with concurrent transactions?

  • 22. What are dirty reading and magic reading?

  • 23. Why does hallucination occur? What are the problems with illusory reading?

  • 24. How to avoid hallucinations?

  • 25. How to check whether there are idle connections in MySQL?

  • 26. What are the string types in MySQL?

  • 27. What is the difference between VARCHAR and CHAR? What are the applicable scenarios?

  • 28. Which data type should be used to store amounts in MySQL? Why is that?

  • 29. What is the meaning of limit 3,2?

  • 30. What is the difference between now() and current_date()?

  • 31. How to recalculate the total number of entries?

  • 32. What does lastinsertid() do? What are the characteristics?

  • 33. How many ways can I delete data from a table? What’s the difference?

  • 34. How many fuzzy queries are supported in MySQL? What’s the difference?

  • 35. Does MySQL support enumeration? How to do that? What is it used for?

  • 36. What is the difference between count(column) and count(*)?

  • 37. Which of the following is true about count?

  • 38. Why doesn’t InnoDB record the total number of entries and return it directly when querying?

  • 39. Can I use the number of rows in show table status as the total number of rows in the table? Why is that?

  • 40. Which OF the following SQL queries has the highest performance?

  • InnoDB and MyISAM select count(*) from t, which is more efficient? Why is that?

  • MySQL > optimize count(*) What optimizations have been made?

  • 43. In InnoDB engine count(*), count(1), count(primary key), count(field) which has the highest performance?

  • 44. What is the difference between inner join, left join, and right join in MySQL?

  • 45. What is a view? How do I create views?

  • 46. What are the advantages of views?

  • 47. How many concepts does a view have in MySQL? What do they mean?

  • 48. How to retrieve the data mistakenly deleted by DELETE?

  • 49. What is the principle of Flashback data recovery?

  • MySQL > select * from ‘MySQL’;

  • 1. What is an index?

  • 2. What are the advantages and disadvantages of indexes?

  • 3. What is wrong with the following SQL? How to optimize?

  • 4. Why does MySQL officially recommend using auto-increment primary keys as primary keys for tables?

  • 5. What are the advantages and disadvantages of autoincrement primary keys?

  • 6. How many types of indexes are there? How are they created?

  • 7. What is the difference between a primary index and a unique index?

  • 8. In InnDB primary key index why is higher than ordinary index query performance?

  • 9. What is a back table query?

  • 10. How do I query all indexes of a table?

  • 11. How many index columns can MySQL create?

  • 12. Which of the following like queries will use the index option? Why is that?

  • Select * from ‘like % ABC’ where ‘ABC’ = ‘ABC’;

  • 14. What should be noticed about MySQL federated index?

  • 15. What is the function of a joint index?

  • 16. What is the leftmost matching principle? What are its effective principles?

  • 17. Will the index be used when the column value is NULL?

  • 18. Does the following statement go through the index?

  • 19. Can you create an index for the first six digits of the mobile phone number? How do I create it?

  • 20. What is a prefix index?

  • 21. Why prefix indexes?

  • 22. When is prefix indexing appropriate?

  • 23. What is a page?

  • 24. What are the common storage algorithms for indexes?

  • 25. Why InnoDB uses B+ trees instead of B trees, Hash, red black or binary trees?

  • 26. Why InnoDB uses B+ trees to store indexes?

  • 27. Which has better performance, unique index or plain index?

  • 28. What are the factors that influence the optimizer’s selection of query indexes?

  • 29. How does MySQL determine the number of rows scanned by the index?

  • 30. How does MySQL get index cardinality? Is it accurate?

  • 31. How to specify an index in MySQL?

  • MySQL > select * from ‘MySQL’;

  • 33. What’s wrong with the following OR query? How to optimize?

  • 34. How can the following queries be optimized?

  • 35. Does MySQL select an index incorrectly?

  • 36. How to solve the MySQL index error?

  • 37. How to optimize the index of id card?

  • MySQL lock problem

  • What is a lock? How many types of locks are provided in MySQL?

  • 2. What is a deadlock?

  • 3. What are the common deadlock cases?

  • 4. How to handle deadlocks?

  • 5. How do I check deadlocks?

  • 6. How to avoid deadlocks?

  • 7. How does InnoDB handle deadlocks by default?

  • 8. How do I enable deadlock detection?

  • 9. What is global locking? What are its application scenarios?

  • 10. What is shared lock?

  • 11. What is an exclusive lock?

  • 12. What are the problems caused by using global locks?

  • 13. What can I do if the entire database cannot be inserted during logical backup?

  • 14. How do I set the database to a global read-only lock?

  • 15. Besides FTWRL, what other methods can be used to make the database read only?

  • FTWRL = set global readOnly =true

  • 17. How to implement table lock?

  • 18. What’s the difference between a pessimistic lock and an optimistic lock?

  • 19. What are the advantages and disadvantages of optimistic locking?

  • 20. How many locking algorithms does InnoDB storage engine have?

  • 21. How does InnoDB implement row locking?

  • 22. What advice do you have for optimizing locks?

  • MySQL > update MySQL

  • 1. What important log files does MySQL have?

  • What is the difference between redo log and binlog?

  • 3. What is crash-safe?

  • 4. What are dirty and clean pages?

  • 5. What can cause MySQL to flush dirty pages?

  • 6. What may cause the MySQL database to flush dirty pages slowly?

  • 7. How to control MySQL to refresh only the current dirty page?

  • 8. What problems does WAL technology solve in MySQL?

  • 9. Why do I sometimes feel that MySQL occasionally freezes?

  • 10. How are redo logs and binlogs related?

  • 11. How does MySQL know that binlog is complete?

  • MySQL > alter database redo log;

  • MySQL > alter database redo log;

  • 14. Why is the binlog cache maintained by each thread and the redo log buffer globally shared?

  • 15. If crash or redo log is lost during transaction execution, the primary and secondary data is inconsistent.

  • 16. What mechanism is used in MySQL to optimize the IO consumption of random reads/writes to disks?

  • 17. What is wrong with the following statement?

  • Which of the following statements is true?

  • 19. Is there any way to restore MySQL data to a specified point in time? How to recover?

  • MySQL commands and built-in functions

  • 21. How to connect to the MySQL database using the command line?

  • Mysql -h 127.0.0.1 -uroot -p3307 -p3307 mysql -h 127.0.0.1 -uroot -p3307 -p3307

  • 23. How do I create a user? And authorize the user, right?

  • 24. How do I change the MySQL password?

  • 25. How to create a database using SQL and set the encoding format of the database?

  • 26. How to modify the encoding format of databases and tables?

  • 27. How to create a table using SQL?

  • 28. How to view table structure information in MySQL command line?

  • 29. How to use SQL to view a known table building script?

  • 30. How to update table structure with SQL statement?

  • 31. What are the deletion methods for MySQL? What’s the difference?

  • 32. How do I start and stop the MySQL service?

  • 33. How do I query the version of the current MySQL installation?

  • 34. How do I view the storage engine of a table?

  • 35. How do I view the number of times of adding, deleting, modifying and searching in the current database?

  • 36. How do I query the number of thread connections?

  • 37. How to check the maximum number of MySQL connections? Can you change it? How to modify?

  • 38. What’s the difference between CHAR_LENGTH and LENGTH?

  • 39. What are the uses of UNION and UNION ALL? What’s the difference?

  • 40. Which of the following statements about WHERE and HAVING is true?

  • 41. What is the difference between NULL and NULL?

  • 42. What are the common functions of MySQL?

  • MySQL optimization problem

  • 1. What are the performance indicators of MySQL? How do you get these indicators?

  • 2. What is slow query?

  • 3. How can I enable slow log Query?

  • 4. How to locate slow query?

  • 5. What are the optimization methods of MySQL?

  • 6. What are the common read/write separation schemes in MySQL?

  • 7. Introduce the function and execution process of Sharding-JDBC?

  • 8. What is MySQL multi-instance? How to configure MySQL multi-instance?

  • 9. How to ensure that there is no delay in standby inventory?

  • Finally, some open questions

  • 1. How to optimize paging query with a super large table?

  • 2. What are the risks of modifying table structure online?

  • 3. What is the possible cause that the query is not returned for a long time? How should it be handled?

  • 4. What are the causes of primary/secondary delay in MySQL?

  • 5. How to prevent data from being deleted by mistake?

  • 6. How to handle CPU surge of MySQL server?

  • 7. What is the possible cause of the abnormal MySQL restart? How to solve it?

  • 8. How to implement a high concurrency system?

The above involves so much knowledge, you can hand-draw a MySQL knowledge architecture corpus brain map to comb

I have a habit of summarizing after learning, and I will hand-draw a brain map to facilitate my later knowledge sorting and recall. The following is my hand-drawn “MySQL Knowledge Brain map”. If you don’t want to spend this time by yourself, after all, hand-drawing is also very troublesome, you can download xiaobian XMIND source file!

Please note: Poke here – get code – dig ！！！！

Finally, there is no such thing as a handwritten note from some big cow — MySQL advanced knowledge notes

Hee hee, accidentally whoring… The content is too much, I will not elaborate on the explanation, made a full details of the table of contents, as shown in the picture below, contains the content at a glance…

MySQL Advanced Knowledge Notes – Full details table of contents

MySQL Advanced Knowledge Notes – Full details table of contents

Part B:

In addition, there is also a PDF book for you to study -MySQL Technology Essentials: Architecture, Advanced Features, Performance optimization and Clustering

It mainly includes the introduction of MySQL construction, MySQL limited and safe MySQL backup and restore, MySQL advanced features, MySQL locking mechanism, using MySQL Workbench to manage the database, SQL performance optimization, MySQL server performance optimization, MySQL Performance monitoring, SQL Repli ation MySQL Cluster Deployment Enterprise MySQL high availability architecture At the same time, it also provides all examples of source code readers can view and call directly.

A summary of

PDF, MySQL interview (basic – advanced), MySQL knowledge brain map, writing MySQL advanced knowledge. PDF, MySQL technical highlights: architecture, advanced features, performance optimization and cluster practice. PDF, MySQL technical highlights: architecture, advanced features, performance optimization and cluster practice. . From a bronze to a king, you have to work hard. Just like you go to the canyon every day to shoot pesticides, the road to promotion is not easy. However, I think my promotion package, should still be a little help to you, at least you can choose the material is not confused, waste of time.

Approved to learn friends attention, all original download, please note: stamp this – get code – nuggets ！！！！