MySQL optimization notes

There is too much data, and nearly 10 million data leads to extremely slow database operation. Therefore, the imminent problem is database optimization.

I read some posts on the Internet and sorted the records here based on my own practices.

MySQL optimization involves the following aspects:
1. Hardware
For high-performance CPUs, add memory. (The current configuration is enough, so this optimization is not under consideration)

2. Operating System
It is said that MySQL runs faster in Linux than in windows. I have never tried it, but we use Linux.

3. SQL Server (set and query)
It mainly involves the configuration of my file, querying the cache, the maximum number of opened tables, the number of simultaneous connections, whether to resolve the server name, whether to resolve the IP address and server name, and so on. You can use show status to view MySQL running parameters for relevant settings.

4. Table Type Selection
Select a Database Engine Based on your needs. Generally, MyISAM or InnoDB is used.

5. Application Programming Interface
5.1 use continuous connections
5.2 do not query unnecessary columns in the application. It is best not to use select * from table where...
5.3 If you create an index for a column (affecting the speed) in the where clause, you are not recommended to create too many indexes and only create valid indexes. Do not create indexes on columns with high data repeatability, such as gender (male, female) fields.
5.4 use the most effective data type for each field in the table. For example, for a score field, if the valid range of the score is 0 to 100, do not use the int type. tinyint is enough.
5.5 use the correct and appropriate data type. Do not store numbers as strings.
5.6 if possible, the declared column is not null.
5.7 when creating an index on a long Char or vchar, do not create a full index. select an appropriate length.
5.8 when inserting data, try to insert multiple rows at a time.
5.9 connections are generally faster than subqueries.
5.10 If there are too many records that meet the query conditions, remember to use limit.
5.11 use less wildcards. Do not use wildcards at the beginning.
5.12 learn to use explain, show processlist, show status, show variables.

The above is a summary of the optimization and a lot of communication.