Interviewer: Do you know the difference between MyISAM and InnoDB in MySQL database?

2022-06-06 0 By

Do you know the difference between MyISAM and InnoDB in MySQL database?As a software developer, you are more or less familiar with mysql database.Zhang Gong is a programmer with more than 3 years of development experience, mainly engaged in Java back-end development during these 3 years. Once, he applied for a Java development position in a software company in Guangzhou. The interviewer asked him this question: Do you know the difference between MyISAM and InnoDB in MySQL database?The interviewer asked him this question is not casually asked, the interview is to see Zhang Gong’s resume write familiar with mysql to ask questions.In fact, For mysql, Zhang Gong usually just use mysql to build a table, and then write data to the data table, look up the data such simple operations, but did not understand the principle of building a table.For this question, Zhang Gong did not know what the difference was, so he replied directly, “I don’t know”.”You have been working for 3 years, how come you don’t know the difference between MyISAM and InnoDB?”By the interviewer so said, Zhang Gong are some embarrassed.I blame myself for only knowing how to complete the task without understanding its principle.From Zhang Gong’s interview, also gave us a wake-up call. For some technical knowledge points, if you do not master them, it is recommended that you do not write familiar with them on your resume. If you really want to write, it is recommended that you use such words to describe them.Avoid being unable to answer the interviewer’s question and revealing your weakness.So what’s the difference between MyISAM and InnoDB in mysql database?Let’s start by looking at what engine we are using for our own database.We can use the following command to check: SHOW ENGINES;InnoDB’s Support column is DEFAULT, which means InnoDB is the DEFAULT database engine on the database server you are using.However, MySQL has good compatibility with multiple engines. Different databases on a database server can use different data engines, and even multiple tables in a database can use different engines.What’s the difference between MyISAM and InnoDB?MyISAM does not support transactions, so each query is atomic;Supports table-level locking, that is, each operation locks the entire table.The total number of rows stored in the table;A MYISAM table has three files: index file, table structure file, data file;Non-clustered indexes are used, in which index file data domains store Pointers to data files.InnoDb supports ACID transactions;Support row-level locking and foreign key constraints;Does not store the total number of rows;An InnoDb engine is stored in a single file space (shared table space, table size is not controlled by the operating system, a table may be distributed in multiple files) or multiple, depending on the size of the operating system file;A primary key index uses a clustered index (the index’s data domain stores the data file itself), and a secondary index’s data domain stores the value of the primary key.Therefore, to search data from the secondary index, you need to find the primary key through the secondary index and then access the secondary index.Use autoincrement primary keys to prevent large file adjustments when inserting data to maintain the B+ tree structure.In conclusion, as a programmer, when using some knowledge points, IT is suggested to understand the principle, rather than stay at the level of only knowing how to use it. It is necessary to understand the difference between InnoDB and MyISAM in use. If you are interested, it is necessary to further study why there are differences, which requires to understand the underlying implementation principle.And some knowledge of B+ trees.If you have a clear overview of the rationale behind it, you’ll feel more comfortable communicating with the interviewer, which will help you score points and increase your chances of getting hired.Due to the limited knowledge and level of the author, mistakes and omissions in the article are unavoidable, if there are deficiencies, welcome to exchange.