In-depth explanation of basic mysql knowledge

1. Each client connection is divided into a thread belonging to the server process. The corresponding queries of the connection are all processed by this thread.

2. The Server caches the thread. Therefore, no threads are created or destroyed for each new connection.

3. When you initiate a connection to the MySQL server, the server will verify the username, host, and password. Once connected, the server detects its permissions.

4. the MySQL query cache only saves the SELECT statement and corresponding results. You will be asked about the query cache before parsing the query. If the query cache can find the corresponding results, the results will be directly returned.

5. The data folder of MySQL creates a folder with the corresponding name based on each database. Each table corresponds to three files with different suffixes:. frm,. MYD,. MYI. The. frm suffix file is used to store table definitions .. MYD (mysql data) stores data, while. MYI (mysql index) Stores indexes.

6. Select an IP Storage type. Do not use varchar (15) any more. Use int unsigned. It is also best to use the built-in PHP functions ip2long () and long2ip () for processing, instead of using the MySQL functions INET_ATON () and INET_NTOA (). Do your best to deliver computing, conversion, and other things to the program. Storage of INT not only saves space, but also facilitates query. For example, if I want to query all the IP addresses of an IP segment, varchar cannot be used.

7. The order in which B + Tree indexes store data is the same as that in the field at table creation. InnoDB automatically creates a memory index for frequently accessed index values in the memory to speed up.

8. The following query will cause index failure:

Copy codeThe Code is as follows: SELECT name FROM user WHERE id + 1 = 4; // mysql does not analyze FROM the computation that the id is indexed.
SELECT name FROM user WHERE TO_DAYS (birth)> 20; // mysql indexes the birth itself rather than the data converted after TO_DAYS ().

1. Copy the table structure
Create table B LIKE;

2. Change the storage engine
Alter table a ENGINE = InnoDB;

3. Copy table data
Insert into B SELECT * FROM;

4. Get table information
Show table status like '% XXX %'; // obtain information about tables whose names match the LIKE condition.
Show table status from 'database name'; // obtain information about all tables in the database

5. Clear binary logs
Reset master;

6. returns the first X characters of a field.
Select left (name, 3) AS pre_name FROM user;

7. Add a prefix index for a field
Alert table xxx add key (name (3 ));

8. Avoid reading unnecessary rows and overwrite the query using indexes.
SELECT * from join (SELECT prod_id FROM products WHERE actor = 'Sean CARREY 'AND
Title LIKE '% APOLLO %') AS t ON (t. prod_id = products. prod_id); // The actor has an index