In the Internet industry, MySQL database is undoubtedly the most commonly used database, LAMP (Linux +Apache + MySQL + PHP) has even become a proprietary term, is also the preferred technical architecture of many small and medium-sized websites.
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
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.
Please forward it and add assistant VX: Yunduoa2019 after following it or scan the qr code below and follow the guidance of the assistant to obtain it by yourself
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.
Please forward it and add assistant VX: Yunduoa2019 after following it or scan the qr code below and follow the guidance of the assistant to obtain it by yourself
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.
Please forward it and add assistant VX: Yunduoa2019 after following it or scan the qr code below and follow the guidance of the assistant to obtain it by yourself
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.
Please forward it and add assistant VX: Yunduoa2019 after following it or scan the qr code below and follow the guidance of the assistant to obtain it by yourself
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.
Please forward it and add assistant VX: Yunduoa2019 after following it or scan the qr code below and follow the guidance of the assistant to obtain it by yourself
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
Please forward it and add assistant VX: Yunduoa2019 after following it or scan the qr code below and follow the guidance of the assistant to obtain it by yourself
