Einstein said, “Patience and perseverance always pay off.” I have always taken that as my motto, and it has come true for me this year.
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. Did a lot of preparation for the interview, collected a lot of questions about MySQL interview
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
Step into 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
2, MySQL interview question summary
The previous Ali interview questions have been summarized, the specific interview questions have been organized into a document, this article is for MySQL series, so the following only show my first interview ali was asked some MySQL problems, the following is this year’s interview Ali encountered MySQL questions.
2.1. Documents and materials of other topics (including answers) can be obtained by private message.
- 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?
2.2. How many questions can you answer in MySQL55?
- 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?
3. MySQL performance tuning and architecture design
- Basic article:
- Introduction to MySQL
- MySQL Architecture Composition
- Introduction to MySQL Storage engine
- MySQL Security Management
- MySQL backup and restore
- Performance Optimization:
- Factors that affect MySQLServer performance
- MySQL 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
- Extensible design for MySQLReplication
- Scalable design for data sharding
- Scalable design of Cache and Search..
- MySQLCluster
- High availability design ideas and schemes
- MySQL monitoring in a highly available design
MySQL architecture and history
Compared to other database systems, MySQL is a bit different in that its architecture can be used in many different scenarios and work well, but it also brings about the difficulty of selecting points. MySQL is not perfect, but it is flexible enough to adapt to demanding environments, such as Web applications. At the same time, MySQL can be embedded in applications and support data warehousing, content indexing and deployment software, highly available redundant systems, online transaction processing systems (OLTP) and many other application types.
【 architecture book 】 you can get the most complete MySQL practice document for free
Server Performance Profiling
In our technical consulting career, the most frequently encountered three performance related service request is: how to confirm whether the server has reached the best performance, find out why a statement execution quickly enough, and the diagnosis is described by the user into a “pause” and “accumulation” or “stuck” some of the intermittent troubleshooting. This chapter will mainly answer these three questions. We will provide tools and techniques to optimize the performance of a machine, optimize the execution speed of a single statement, and diagnose or solve hard-to-observe problems that are often difficult for users to know the root cause of, and sometimes even difficult to detect.
Query performance optimization
The previous section described how to design the optimal library table structure and how to build the best indexes, which are essential for high performance. But that’s not enough — you need to design queries properly. If the query is poorly written, even the most well-structured and well-indexed library tables will not achieve high performance.
MySQL Advanced Features
MySQL has introduced many advanced features since versions 5.0 and 5.1, such as partitions, triggers, and more, which may be familiar to users with other relational database backgrounds. These new features attracted many users to MySQL. However, the performance of these features is not known until users actually use them. Instead of just a reference manual or handout, we’ll show you how these features behave in the real world. Data on.
You can get the most complete MySQL combat document for free
Optimizing server Settings
Here we explain the process of creating a solid configuration file for MySQL server. It’s a very convoluted process, with a lot of interesting concerns and interesting ideas. It’s important to focus on these points, because the fastest way to create a good configuration isn’t to start by learning configuration items, asking which configuration items should be set how or changed, or examining server behavior and asking which configuration items improve performance.
It’s a good place to start by understanding the MySQL kernel and behavior. You can then use this knowledge to guide the configuration of MySQL. Finally, you can compare the desired configuration to the current one and correct important and valuable differences.
After forwarding + attention
You can get the most complete MySQL combat document for free
copy
MySQL’s built-in replication capabilities are the foundation for building large-scale, high-performance applications based on MySQL that use a so-called “scale-out” architecture. Data synchronization can be done by configuring one or more standby generation 1s for the server. Replication is not only good for building high-performance applications, it is also the foundation for high availability, scalability, disaster recovery, backup, and data warehousing. In fact, scalability and high availability are often related topics, which we’ll cover in more detail in the next three chapters.
Forward + follow
You can get the most complete MySQL combat document for free
Extensible MySQL
Here we show how to build a mysql-based application that is fast, efficient, and economical as it gets bigger and bigger. What extensibility recommendations are relevant to applications that only work on — a platform or a small number of servers? Most people will never maintain very large systems, and often cannot emulate the strategies used by major companies. This chapter covers this series of strategies. We have built or helped build many applications, ranging from single or small servers to applications with thousands of servers. Choosing the right strategy can save a lot of time and money. MySQL is often criticized for being hard to scale, and in some cases this is true, but if you choose the right architecture and implement it well, you can scale MySQL very well. But extensibility is not – a well-understood topic, so let’s clear it up – something that can be confusing.
Forward + follow
Get the most complete MySQL document for free
The cloud of MySQL
Application layer optimization
If you spend too much time trying to improve MySQL’s performance, it’s easy to focus on MySQL itself and lose sight of the user experience. In retrospect, you might realize that MySQL was probably optimized enough to make a small contribution to the response time users saw, and that it was time to focus on other parts. This is a good point, especially for DBAs, and the right thing to do. But if it’s not MySQL, what’s causing the problem? Using the techniques mentioned in Chapter 3, measurements give quick and accurate answers. It is generally not difficult to find the source of a problem if you can follow the logic of the application from beginning to end. Sometimes, even though the problem is with MySQL., it can be easily solved in another part of the system.
Backup and Restore
If you don’t plan your backups ahead of time, you may find yourself missing some of your best options. For example, after the server has been configured, it occurs to you that you should use LVM so that you can take a snapshot of the file system — but by then it’s too late. When configuring system parameters for backup, you may not notice that some system configurations have a significant impact on performance. If you don’t plan on doing regular recovery drills, when you do need to recover, you’ll find it’s not going so well.
MySQL User Tools
The MySQL server distribution does not include tools for many common tasks, such as monitoring servers or comparing data between different servers. Fortunately, the commercial edition of Oracle provides a number of extensions, as do MySQL’s active open source community and third-party companies, to reduce the need to reinvent the wheel yourself.
Total directory
Forward + follow
Get the most complete MySQL document for free
