Palai

Encounter#

You can first bring up your task manager (I'm using Windows, shortcut key CTRL + SHIFT + ESC) -> the first column, the first one is the process.

What is a process?#

A process is the basic unit of resource allocation and an execution unit of the operating system. There are many programs on our computer, such as WeChat, QQ, Netease Cloud Music, etc. Each running program can be called a process. Each process has its own independent memory space, code, and data; each process has a unique process ID. If you have started the MySQL service on your computer, you can find the process ID of MySQL in the process -> mysqld. As shown in the figure

(I have two because I have installed two versions of MySQL, one is 5.7 and the other is 8.0)

Connecting to MySQL#

Press win+r and type "cmd" to open the command prompt, then enter:

mysql -hlocalhost -u root -p

-hlocalhost can be omitted if it is on your own computer. There can be a space after -u, but there should not be one after -p. The above command allows you to enter the password implicitly without it being visible to others. After pressing enter, you can enter the password.

Inter-process communication methods?#

I'll use some humorous words to express it, if you want to see something more formal, you can look below.

1. Pipe: Imagine two processes passing secret messages through a straw. The pipe is unidirectional, and data can only flow from one end to the other, just like juice in a straw.

2. Named Pipe: This is an upgraded version of the pipe, it has a name! This way, different processes can find it by name and communicate bidirectionally, just like two people using a walkie-talkie.

3. Signal: A clapping game between processes. One process sends a signal to another process, telling it what event has occurred, just like clapping hands to say "you've been caught".

4. Message Queue: The post office between processes. A process puts a message into the queue, and another process takes the message out of the queue. It's like sending a letter, but this post office is very efficient.

5. Shared Memory: Processes share a block of memory, like a public blackboard where everyone can write and erase. This method is very fast, but you need to be careful to avoid confusion when writing.

6. Socket: The telephone between processes. Sockets allow communication between different processes, even different computers, just like making a phone call. As long as you know the other party's phone number (IP address and port number), you can start talking.

Pipe: Pipe is a half-duplex communication method, where data can only flow in one direction, and it can only be used between processes with a parent-child relationship. The parent-child relationship of processes usually refers to the relationship between a process and its child processes.

Named Pipe (FIFO): Named pipe is also a half-duplex communication method, but it allows communication between processes without a parent-child relationship.

Message Queue: Message queue is a linked list of messages stored in the kernel and identified by a message queue identifier. Message queues overcome the disadvantages of signal transmission with limited information, pipes can only carry unformatted byte streams, and limited buffer size.

Shared Memory: Shared memory is a mapping of a segment of memory that can be accessed by other processes. This shared memory is created by one process, but multiple processes can access it. Shared memory is the fastest IPC method and is specifically designed to overcome the low efficiency of other inter-process communication methods. It is often used in conjunction with other communication mechanisms, such as semaphores, to achieve process synchronization and communication.

Socket: Socket is a communication mechanism between processes, different from other communication mechanisms, it can be used for communication between different hosts.

Signal: Signal is a more complex communication method used to notify the receiving process of an event that has occurred.

What communication methods does MySQL support between processes?#

• Named pipe and shared memory
• Unix domain socket file
• TCP/IP: Communication is done through IP + port number, essentially belonging to "Socket" communication.

Processes and port numbers are two different concepts. Port numbers are used to identify specific processes in network communication. This means that not every process has a port number, but only those processes that need to communicate will use port numbers. Port numbers can be pre-allocated, such as port 80 for HTTP communication and port 443 for HTTPS. When our MySQL service starts, it will default to requesting port number 3306.

MySQL storage engines#

Let's talk about storage engines first, because data is stored in storage engines.
Here, I will only talk about two commonly used ones: InnoDB and MyISAM.

InnoDB:

1. Transaction support: InnoDB provides commit and rollback functionality to ensure data integrity and consistency.
2. Row-level locking: InnoDB can lock the modified individual rows of data during concurrent operations, improving concurrency performance.
3. Foreign key support: InnoDB supports referential integrity constraints, which helps maintain the relational integrity of the database.
4. MVCC (Multi-Version Concurrency Control) support: This helps improve read performance in high-concurrency scenarios.

MyISAM:

1. No transaction support: MyISAM does not provide transaction support, so it is not suitable for scenarios that require transaction processing.
2. Table-level locking: MyISAM locks the entire table when performing write operations, which may result in lower concurrency performance.
3. No foreign key support: MyISAM does not support referential integrity constraints.
4. Smaller storage space: MyISAM usually occupies less disk space because it does not need to store transaction and row-level locking-related information.

Differences between InnoDB and MyISAM:
Similarities:

1. Both are storage engines of MySQL used for storing and managing data.

Differences:

1. Transaction support: InnoDB supports transactions, while MyISAM does not.
2. Locking mechanism: InnoDB uses row-level locking, while MyISAM uses table-level locking.
3. Foreign key support: InnoDB supports foreign keys, while MyISAM does not.
4. Storage space: InnoDB usually occupies more disk space, while MyISAM occupies less.
5. Concurrency performance: In high-concurrency scenarios, InnoDB usually performs better because it supports row-level locking and MVCC.

Choose different storage engines based on different scenarios. If you need transaction support and high concurrency performance, InnoDB is a better choice. If you don't need transaction support and have higher requirements for storage space, you can consider using MyISAM.

One last point I want to mention is:

In InnoDB, data is index and index is data, while in MyISAM, index is index and data is data.

A brief explanation:

1. InnoDB: "Data is index and index is data": InnoDB uses clustered indexes to store data. In a clustered index, data rows are tightly integrated with the primary key index and stored in the order of the primary key. This means that data and index are actually one, and cannot be separated. When we query data, we can directly find the corresponding data row through the primary key index without any additional data lookup process.

2. MyISAM: "Index is index and data is data": MyISAM uses non-clustered indexes to store data. In a non-clustered index, data rows and indexes are stored separately. The index only contains index key values and pointers to the actual data rows. When we query data, we first need to search in the index, and then use the pointer to find the actual data. This process is called "lookup".

To summarize, in InnoDB, data and index are tightly integrated, and there is no need for additional data lookup during queries. In MyISAM, data and index are stored separately, and a lookup process is required to find the actual data through the index pointer. This is the meaning of "InnoDB: data is index and index is data; MyISAM: index is index and data is data".