Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to learn mysql". The content in the article is simple and clear, easy to learn and understand. Please follow the editor's train of thought to study and learn "how to learn mysql".

Characteristics of MySQL24 system

1. Written in C and C++, and tested with a variety of compilers to ensure the portability of the source code.

2. Support AIX, FreeBSD, HP-UX, Linux, Mac OS, NovellNetware, OpenBSD, OS/2 Wrap, Solaris, Windows and other operating systems.

3. API is provided for many programming languages. These programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby,.NET and Tcl.

4. Support multithreading and make full use of CPU resources.

5. The optimized SQL query algorithm can effectively improve the query speed.

6. It can be used not only as a separate application in the client server network environment, but also as a library and embedded in other software.

7. Provide multi-language support. Common codes such as GB 2312 and BIG5 in Chinese and Shift_JIS in Japanese can be used as data table names and data column names.

8. Provide a variety of database connections such as TCP/IP, ODBC and JDBC.

9. Provide management tools for managing, checking, and optimizing database operations.

10. Support large databases. It can handle large databases with tens of millions of records.

11. Multiple storage engines are supported.

12.MySQL is open source, so you don't have to pay extra.

13.MySQL uses the standard SQL data language form.

14.MySQL has good support for PHP, and PHP is a popular Web development language.

15.MySQL can be customized, using the GPL protocol, you can modify the source code to develop your own MySQL system.

16. Online DDL/ change capabilities, data architecture support for dynamic applications and developer flexibility (5.6 new)

17. Copy the global transaction identity to support self-healing clustering (added in 5.6)

18. Replication crash-free slaves to improve availability (add 5.6)

19. Copy multithreaded slaves to improve performance (added in 5.6)

20.3x faster performance (5.7 [7] New)

21. New optimizer (5.7 added)

twenty-two。 Native JSON support (5.7new)

23. Multi-source replication (5.7 New)

Spatial expansion of 24.GIS [8] (5.7New)

OK, after knowing this, let's next look at tuning, which is also some questions that are often asked during the interview, and with the development of the Internet and the increase in the amount of data, we do not want to introduce the big data system, so we can only work on the database, so do you think it is difficult to tune the database? In fact, it's really not difficult. I don't believe it. Look down there.

MySQL tuning mind map

Seeing this mind map, from top to bottom, I wonder if you have recalled how clear these aspects are in your knowledge system when reading this article.

OK, after reading this sentence, my focus is coming. When you recall these knowledge points, did you review and sort out your knowledge points, check and fill the gaps, and you also know the deficiencies in your knowledge points? is it possible to study pertinently?

Let's take sub-database and sub-table as an example to discuss.

Sub-library sub-table 1, horizontal sub-library

Concept: split the data in one library into multiple databases based on fields and in accordance with certain strategies (hash, range, etc.).

Results:

The structure of each library is the same

The data in each library is different and there is no intersection.

The union of all libraries is full data.

Scenario: the absolute concurrency of the system has come up, the sub-table is difficult to solve the problem fundamentally, and there is no obvious business attribution to divide the database vertically.

Analysis: with more libraries, the pressure on io and cpu can be alleviated exponentially.

2. Horizontal subtable

Concept: split the data in one table into multiple tables based on fields and in accordance with certain strategies (hash, range, etc.).

Results:

The structure of each table is the same

The data of each table is different and there is no intersection.

The union of all tables is full data.

Scenario: the absolute concurrency of the system has not come up, but there is too much data in a single table, which affects the efficiency of SQL and increases the burden of CPU, so that it becomes a bottleneck. Recommendation: an analysis of the principle of SQL query optimization

Analysis: the amount of data in the table is less, and the execution efficiency of a single SQL is high, which naturally reduces the burden of CPU.

3. Vertical sub-library

Concept: based on tables, different tables are split into different libraries according to different business ownership.

Results:

The structure of each library is different.

The data in each library is also different, and there is no intersection.

The union of all libraries is full data.

Scenario: the absolute concurrency of the system is up, and individual business modules can be abstracted.

Analysis: at this point, we can basically become service-oriented. For example, with the development of business, there are more and more common configuration tables, dictionary tables, and so on. At this time, these tables can be split into separate libraries, or even serviced. In addition, with the development of the business, a set of business model has been hatched, at this time, the relevant tables can be split into a separate library, or even service.

4. Vertical subtable

Concept: based on the field, the fields in the table are split into different tables (main table and extended table) according to the activity of the field.

Results:

Each table has a different structure.

The data of each table is also different. Generally speaking, the fields of each table have at least one column intersection, usually the primary key, which is used to associate the data.

The union of all tables is full data.

Scenario: the absolute concurrency of the system does not come up, the records of the table are not many, but there are many fields, and the hot data and non-hot data together, the storage space of single row data is larger. As a result, the number of data rows in the database cache is reduced, and a large number of random reads of IO are generated when querying, resulting in IO bottlenecks.

Analysis: list pages and details pages can be used to help understand. The splitting principle of vertical split tables is to put hot spot data (data that may be redundant and often queried together) together as primary tables and non-hot data together as extended tables. In this way, more hot data can be cached, thus reducing the number of random reads of IO. After disassembly, if you want to get all the data, you need to associate two tables to fetch the data.

But remember, never use join, because join not only puts a burden on CPU but also couples two tables (which must be on a database instance). The associated data should be written in the business Service layer, obtaining the master table and extended table data respectively, and then associating all the data with the associated fields.

All right, at this point, the operation of sub-database and sub-table is basically completed. I don't know if you have any feelings. If knowledge is just to look at these knowledge points, how long can you remember? Anyway, if I don't look at it for a long time, I forget it, but I'm not a pussy, because I have this picture, and it's more detailed than the one shown, so I can look at it when I need it, and I can just take it out. Even if it's only 10 minutes.

Thank you for your reading, the above is the content of "how to learn mysql". After the study of this article, I believe you have a deeper understanding of how to learn mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.