Database storage engine 2, InoDB (B+ Tree) 2, TokuDB (Fractal Tree- Nodes with data) 3, MyIASM 4, Memory 5, What is the database engine 2nd NF- Each table describes only one thing. 3rd NF- There are no transitive dependencies on non-primary key columns What is a view? 17. What is an inner join, left outer join, right outer join? 18. What are the problems caused by concurrent transactions? What are the transaction isolation levels? What is the default isolation level for MySQL? How to optimize the large table? SQL > select * from user where id is primary key; select * from user where id is primary key; select * from user where id is primary key; select * from user where id is primary key; What are the different tables in MySQL? InnoDB supports four transaction isolation levels in MySQL, and what are the differences between each level? What is the difference between a CHAR and a VARCHAR? What is the difference between a primary key and a candidate key? 33. What is Myisamchk used for? 34, What is the difference between MyISAM Static and MyISAM Dynamic? 35. What happens if a table has a column defined as TIMESTAMP? 36, How do you see all the indexes defined for the table? 37, What do you mean by % and in the LIKE declaration? What is the column comparison operator? What’s the difference between a BLOB and a TEXT? What is the difference between MySQL_fetch_array and MySQL_fetch_object 41. Where will MyISAM tables be stored and their storage format provided? MySQL: How to optimize DISTINCT? 43, How to display the first 50 lines? 44, How many columns can be used to create an index? 45, What’s the difference between NOW () and CURRENT_DATE ()? What is a nonstandard string type? 47, What are generic SQL functions? 48, Does MySQL support transactions? MySQL > select * from ‘MySQL’ where MySQL > select * from ‘MySQL’ where MySQL > select * from ‘MySQL’ 51, What can be the string type of a column? MySQL database as the storage of the release system, more than 50,000 increments a day, is expected to operate and maintain for three years, how to optimize? 53, lock the optimization strategy of 54, the underlying implementation principle of the index and optimization of 55, what circumstances set up index | but cannot use 56, 57, optimization of practice, how to optimize the MySQL database MySQL method 58, simple description, index, primary key, unique index, joint index difference, What is the impact on database performance (both in terms of reading and writing) 60, The cause of SQL injection vulnerability? How to prevent it? Index is a very important concept for relational databases. SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL What are the operational keys for each section 67. What are integrity constraints? What is a lock? What is a view? What is a cursor? 70. What is a stored procedure? With what? 71. How to understand the three paradigms popularly? What is a basic table? What is a view? 73. What are the advantages of trial views? What is the difference between a primary key, a foreign key and an index? 76. What can you use to ensure that fields in a table accept only values in a specified range? What are the methods of SQL statement optimization? What is optimistic lock 79, what is pessimistic lock 80, what is timestamp 81, what is row-level lock 82, what is table-level lock 83, what is page-level lock

MySQL interview questions

1. Database storage engine

Database storage engine is the bottom software organization of database. Database management system (DBMS) uses data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and other functions. Different storage engines can be used to obtain specific functions. Many different database management systems now support many different data engines. Storage engines include: 1. MyIsam, 2. InnoDB, 3. Memory, 4. Archive, 5.

2. What are the database engines

Mysql common engines include MYISAM, Innodb, Memory, MERGE

  1. MYISAM: full table lock, has a high execution speed, does not support transactions, does not support foreign keys, poor concurrency performance, relatively small footprint, transaction integrity is not required, select, INSERT based applications can basically use this engine
  2. Innodb: Row-level locking, provides transaction security with commit, rollback and crash recovery capabilities, supports automatic column growth, supports foreign key constraints, high concurrency, takes up 2.5 times as much space as MYISAM, relatively poor processing efficiency
  3. Memory: A full table lock, which is stored in the content, is fast, but occupies Memory space proportional to the amount of data, and the data will be lost when mysql restarts. The default HASH index is used, which is very efficient in searching, but not suitable for precise searching. It is mainly used for code tables whose contents do not change frequently
  4. MERGE: Is a combination of MYISAM tables

3. Differences between InnoDB and MyISAM

  1. InnoDB supports transactions, MyISAM does not support, for InnoDB each SQL language encapsulated as a transaction, automatic commit, this will affect the speed, so it is best to put multiple SQL languages between begin and commit, constitute a transaction;
  2. InnoDB supports foreign keys, while MyISAM does not. Converting an InnoDB table with foreign keys to MYISAM will fail;
  3. InnoDB is a clustered index, data files are tied to the index, must have a primary key, through the primary key index efficiency. But secondary indexes require two queries, first to the primary key and then to the data through the primary key. Therefore, the primary key should not be too large, because if the primary key is too large, the other indexes will be too large. While MylSAM is a non-clustered index, the data file is separated and the index holds the pointer to the data file. Primary key line | | and auxiliary index are independent of each other.
  4. InnoDB does not store the exact number of rows in a table. Select count(*) from table requires a full table scan. MylSAM uses a variable to store the number of rows in the entire table. When executing the above statement, you only need to read the variable, which is fast.
  5. Innodb does not support full-text indexing, while MyISAM supports full-text indexing. MyISAM has higher query efficiency

4, index,

An Index is a data structure that helps MySQL retrieve data efficiently. Common query algorithm, sequential search, binary search = fork sort tree search hash hash method partition search, balanced multi-path search tree B tree (B-tree), index is to the database table in one or more columns of the value of the sorted structure, the establishment of an index helps to quickly obtain information.

You can also think of an index as a way to speed up the retrieval of data in a table. The index of a database is similar to the index of a book. In books, indexes allow users to quickly find the information they need without having to go through the entire book. In databases, indexes also allow database programs to quickly find data in tables without having to scan the entire database

Mysql has 4 different indexes:

  • PRIMARY key index (PRIMARY)
  • UNIQUE index
  • Normal INDEX
  • FULLTEXT index

More indexes are not always better. Creating indexes also costs resources – it increases the storage space of the database, and it takes more time to maintain indexes during inserts and deletes

  • Indexes speed up database retrieval
  • Indexes slow down maintenance tasks such as inserts, deletes, and changes
  • Unique indexes ensure the uniqueness of each row of data
  • By using indexes, you can use optimization hiders during queries to improve system performance
  • Indexes take up both physical and data space

5. What are the common indexing principles

  1. Select a unique index. The value of a unique index is unique, so that a record can be identified more quickly through the index.
  2. Index fields that often require sorting, grouping, and union operations.
  3. Indexes fields that are commonly used as query criteria.
  4. Limit the number of indexes: The more indexes there are, the more time it takes to update the table. Try to use indexes with a small amount of data
  5. If the index value is long, the speed of the query will be affected. Use prefixes whenever possible
  6. If the value of an index field is long, it is best to use the prefix of the value to index it.
  7. Delete indexes that are no longer used or rarely used
  8. The left-most prefix matching rule, very important rule.
  9. The formula for selecting columns with as much distinction as possible for index distinction is the proportion of fields that are not duplicated
  10. Index columns cannot participate in the calculation. Keep the columns “clean” : queries with functions do not participate in the index.
  11. Expand indexes as much as possible, do not create new ones

6. What are the three paradigms of database

First normal form: columns cannot be separated second normal form: rows can only be distinguished, primary key constraint Third normal form: non-primary attribute of a table cannot depend on non-primary attribute foreign key constraint of other tables and the three paradigms are level by level. Second normal form is based on the first normal form, and third normal form is based on the first and second normal form.

Since the above interview questions have been compiled into a book, our small assistant VX: Xuanwo008 has been added to our products to provide free Java interview questions summary, mind map and a 400-page PDF exclusive Java interview manual!

These are some of the things that the interviewer should ask during the interview. These include basics, Java collections, JVMS, multi-threaded concurrency, Spring principles, microservices, Netty and RPC, Kafka, diaries, design patterns, Java algorithms, databases, Zookeeper, distributed caching, data structures, and more. Add a small assistant VX: Xuanwo008 is free

Collection method:

Add a small assistant VX: Xuanwo008 is free