Dig into the basics of mysql

1. Each client connection will be assigned a thread belonging to it from the server process. All queries for that connection are processed by that thread.

2. The server caches threads. Therefore, threads are not created or destroyed for each new connection.

3. When initiating a connection to the MySQL server, the server will verify username, host, password. Once a connection is made, the server detects its permissions.

4. The MySQL query cache will only hold the SELECT statements and the corresponding results. The query cache is queried before the query is parsed, and the result is returned if the query cache can find the corresponding result.

5. The data folder of MySQL will create a folder with a corresponding name according to each database. For each table, there are three files with different suffixes:.frm,.MYD,.MYI. The.frm file is used to store the table definition. .MYD (mysql data) stores data, while.MYI (mysql index) stores indexes.

6. Storage type selection for IP. Don't use varchar(15) anymore, use INT UNSIGNED instead. It is also best to use ip2long() and long2ip(), PHP's built-in functions, rather than INET_ATON() and INET_NTOA(). As much as possible, leave things like computation and transformation to the program. Storing for INT not only saves space, but also facilitates queries. For example, if I want to query all the IP of a certain IP segment, varchar cannot be done.

7. B+Tree index saves data in the same order as the fields in the table. InnoDB will automatically create an in-memory index for a number of frequently accessed index values in memory to speed things up.

8. Index invalidation is caused by the following queries:

SELECT name FROM user WHERE id+1=4;    // mysql It's not going to be analyzed from the calculation id It's indexed 
SELECT name FROM user WHERE TO_DAYS(birth) > 20;    // mysql The index is birth Per se, not TO_DAYS() The transformed data 

1. Copy the table structure
CREATE TABLE b LIKE a;

2. Change the storage engine
ALTER TABLE a ENGINE=InnoDB;

3. Copy table data
INSERT INTO b SELECT * FROM a;

4. Get table information
SHOW TABLE STATUS LIKE '%XXX%'; // get table information whose table name matches LIKE.
SHOW TABLE STATUS FROM 'database name'; // gets information about all the tables in the database

5. Clear the binary log
RESET MASTER;

6. Return X characters before a field
SELECT LEFT(name, 3) AS pre_name FROM user;

7. Add a prefix index to a field
ALERT TABLE xxx ADD KEY (name(3));

8. Avoid reading unnecessary rows and use indexes to override queries
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); // where actor has an index