preface

Every programmer has a big factory dream, I am no exception, last year interview Ali, was unexpectedly asked by MySQL, many relevant questions did not answer, only 2 face cool. A lot of preparation for the interview, collect a lot of questions about MySQL interview, you can have a good understanding!

What are the features of MySQL?

  • Written in C and C++, and using a variety of compilers for testing, to ensure the portability of source code
  • Support AIX, FreeBSD, HP-UX, Linux, MacOS, Novell Netware, OpenBSD, OS/2 Wrap, Solaris, Windows and other operating systems
  • Provides apis for a variety of programming languages. These languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.
  • Support multithreading, make full use of CPU resources
  • SQL query algorithm, effectively improve the query speed
  • It can not only be used as a single application in the client server network environment, but also be embedded in other software as a library to provide multi-language support. Common codes such as GB 2312 in Chinese, BIG5, Shift_JIS in Japanese, etc., can be used as data table names and data column names
  • Provides TCP/IP, ODBC, JDBC, and other database connections
  • Provides management tools for managing, checking, and optimizing database operations
  • Can handle large databases with tens of millions of records

Enter the world of MySQL

1. 21 best practices for MySQL performance optimization

  • Optimize your query for query caching

  • EXPLAIN your SELECT query

  • LIMIT 1 is used when there is only one row of data

  • Build indexes for search fields

  • Use a similar type of example when joining a table and index it

  • Don’t ORDER BY RAND()

  • Avoid SELECT *

  • Always set an ID for each table

  • Use ENUM instead of VARCHAR

  • Get advice from PROCEDURE ANALYSE()

  • Use NOT NULL whenever possible

  • Prepared Statements

  • Unbuffered query

  • Save the IP address as an UNSIGNED INT

  • Fixed length tables are faster

  • Vertical segmentation

  • Split large DELETE or INSERT statements

  • The smaller the column, the faster it will be

  • Select the correct storage engine

  • Select the correct storage engine

  • Beware of permalink

MySQL interview questions summary

Before the item inside have to do the summary, the detailed content of the interview questions together a document, this article is aimed at MySQL series, so the following only shows his first interview when ali was easing the bit in some MySQL problems asked, here is ali met MySQL interview this year, need answers parsing friend can focus on the public number: kirin bug.

  • Explain the concept and key features of a relational database.
  • What are the typical products, features and application scenarios of relational databases?
  • Please describe the categories of SQL statements and corresponding representative keywords in detail.
  • What is MySQL multi-instance and how to configure MySQL multi-instance?
  • How to strengthen MySQL security, please give feasible specific measures?
  • A drop database SQL statement was executed by mistake.
  • This section describes the principle of MySQL primary/secondary replication and the procedure for configuring primary/secondary replication.
  • 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 many questions can you answer?

  • Select * from Mysql where ID = 15; select * from Mysql where ID = 15; select * from Mysql where ID = 15;

  • What are the technical features of Mysql?

  • What is the Heap table?

  • What is the default port of the Mysql server?

  • What are Mysql’s advantages over Oracle?

  • How do you distinguish a FLOAT from a DOUBLE?

  • Distinguish CHAR_LENGTH from LENGTH?

  • Please briefly describe the four transaction isolation levels supported by InnoDB in Mysql and the differences between them.

  • What is the use of ENUM in Mysql?

  • How do I define REGEXP?

  • The difference between CHAR and VARCHAR?

  • What can be the string type of a column?

  • How to obtain the current version of Mysql?

  • What storage engine is used in Mysql?

  • What is the Mysql driver?

  • What does TIMESTAMP do on the UPDATE CURRENT_TIMESTAMP data type?

  • What’s the difference between primary keys and candidate keys?

  • How to use Unix shell to log in to Mysql?

  • What is Myisamchk used for?

  • MYSQL database server performance analysis method commands are available.

  • How do I control the maximum size of the HEAP table?

  • What is the difference between MyISAM Static and MyISAM Dynamic?

  • What is a Federated table?

  • What happens if a table has a column defined as TIMESTAMP?

  • What happens if the maximum value in the table is reached when the column is set to AUTO INCREMENT?

  • How do I find out which automatic increment was allocated on the last insert?

  • How do you see all the indexes defined for the table?

  • What do % and _ mean in the LIKE declaration?

  • How to convert between Unix and Mysql timestamps?

  • What is the column comparison operator?

  • How do we get the number of rows affected by the query?

  • Is Mysql query case sensitive?

  • What is the difference between a LIKE and a REGEXP operation?

  • What’s the difference between BLOB and TEXT?

  • What is the difference between mysql_fetch_array and mysql_fetch_object?

  • How do we run batch mode in mysql?

  • Where will the MyISAM table be stored and its storage format provided?

  • What are the different tables in Mysql?

  • What is ISAM?

  • What is InnoDB?

  • How does Mysql optimize DISTINCT?

  • How to enter characters as hexadecimal numbers?

  • How do I display the first 50 lines?

  • How many columns can be used to create an index?

  • What’s the difference between NOW () and CURRENT_DATE ()?

  • What objects can be created using the CREATE statement?

  • How many TRIGGERS are allowed in the Mysql table?

  • What is a nonstandard string type?

  • What are generic SQL functions?

  • Explain access control lists

  • Does MYSQL support transactions?

  • What is the best field type in mysql to record currency

  • When are MYSQL tables vulnerable to corruption?

  • Mysql > select * from ‘mysql’ where ‘privileges’ are stored.

  • What types of locks are available in Mysql?

Data types in MySQL

MySQL > select * from ‘MySQL’

Storage procedures & custom functions in detail

What is an index?

MySQL > select * from ‘MySQL’

How to use MySQL to implement distributed locks

The goal of the Mysql series is to master all the skills needed for advanced development from beginning to end.

Distributed lock functionality

1. Distributed lock users reside on different machines. After the lock is successfully obtained, users can perform operations on shared resources.

2. Locks have a reentrant function: that is, a user can acquire a lock more than once.

3. Lock acquisition has the function of timeout: that is, attempts to obtain the lock within the specified time exceed the timeout period. If the lock is not obtained successfully, the system returns that the lock acquisition failed.

4. Automatic fault tolerance, such as: A machine to obtain the lock lock1, before releasing the lock lock1, A machine hung, leading to lock lock1 not released, the result will be lock1 has been A machine occupied, in this case, distributed lock to be able to automatically solve, small series here also corresponding to sort out A MySQL optimization 500 pages of learning notes, to share with you. You can add a timeout period when holding a lock. If the timeout period is exceeded, other machines will have a chance to acquire the lock.

The last

Space is limited, other content will not be shown here one by one, welcome everyone to exchange, like the article remember to pay attention to me like yo, thank you for your support!