Chapter 1 pretending to be a White Boy — Rediscovering MySQL

1.1 Client/Server Architecture of MySQL

MySQL:

  1. Start the MySQL server program.
  2. Start the MySQL client program and connect to the server program.
  3. In the client program input some command statements as a request sent to the server program, the server program receives these requests, according to the content of the request to operate specific data and return to the client operation results.

1.2 installing MySQL

Executable files in the 1.2.1 bin directory

1.3 Starting the MySQL server Program

1.4 Starting the MySQL client program

1.5 Process of connecting the client to the server

1.5.1 TCP/IP

On a network, each computer has a unique IP address. If a process needs to use TCP for network communication, it can apply for a port number from the operating system. The port number ranges from 0 to 65535. In this way, other processes on the network can use the IP address + port number to connect to this process. In this way, processes can communicate with each other over the network.

1.5.2 Naming Pipes and Shared Memory

Named pipes and shared memory are two ways of interprocess communication in Windows operating system.

1.5.3 UNIX Domain sockets

1.6 The server processes client requests

1.6.1 Connection Management

MySQL server allocates one thread for each client that connects to the server, but too many threads can seriously affect system performance, so we also need to limit the number of clients that can connect to the server at the same time.

1.6.2 Analysis and Optimization

16.2.1 Querying the Cache

The MySQL server program caches the query request it just processed along with the result, and if the same request comes in next time, it looks up the result directly from the cache.

MySQL’s caching system monitors each table involved, and any cached queries that use the table are invalidated and removed from the cache as soon as the structure or data of the table is changed.

Tip:

As of MySQL 5.7.20, query caching is not recommended and was removed in MySQL 8.0.

16.2.2 Syntax Analysis

Since the client program sends a request just a piece of text, the MySQL server program first parses the text.

16.2.3 Query Optimization

The MySQL optimizer makes some optimizations to our statements. We can use the EXPLAIN statement to see the execution plan of a particular statement.

1.6.3 Storage Engines

The MySQL server encapsulates the data storage and extraction operations into a module called the storage engine.

1.7 Common Storage Engines

1.8 Operations on storage Engines

1.8.1 Viewing storage Engines Supported by the Current server Program

SHOW ENGINES;
Copy the code

1.8.2 Setting the Storage Engine for a Table

We can set up different storage engines for different tables.

1.8.2.1 Specifying a Storage Engine when Creating a table
CREATE TABLE engine_demo_table ( i INT ) ENGINE = MyISAM;
Copy the code
1.8.2.2 Modifying the Storage Engine of a Table
ALTER TABLE engine_demo_table ENGINE = INNODB;
Copy the code