No matter what kind of service, its optimization is nothing more than from two aspects, the first is the optimization of hardware, the second is the optimization of the system and the service itself.
1, Query the number of times to connect to the MySQL server

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 3     |
+---------------+-------+
1 row in set (0.01 sec)

2. Query the running time of MySQL server

mysql> show status like 'uptime';           #In seconds
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 158   |
+---------------+-------+
1 row in set (0.00 sec)

3. Number of query operations

mysql> show status like 'com_select';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

4. Number of insert operations

mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 2     |
+---------------+-------+
1 row in set (0.00 sec)

5. Number of update operations

mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 3     |
+---------------+-------+
1 row in set (0.00 sec)

6. Number of delete operations

mysql> show status like 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 1    |
+---------------+-------+
1 row in set (0.00 sec)

7. Number of slow queries to query MySQL server

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 15     |
+---------------+-------+
1 row in set (0.00 sec)

2, Analyze SQL statements
1. Analyze with explain keyword

mysql> explain select * from user\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user          #Table name
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL             #Which column or constant is used with the index to query records
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

2. Using index to improve query efficiency

mysql> explain select * from stu_info where s_id=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3                        #You need to query three rows to find (there are only three rows of data in this table)
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> create index index_1 on stu_info(s_id);         # Create index

mysql> explain select * from stu_info where s_id=3\G      # Query again
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu_info
   partitions: NULL
         type: ref
possible_keys: index_1          #Which index name is used
          key: index_1
      key_len: 5
          ref: const
         rows: 1                #After the index is created, query 1 line to find that
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

For using indexes, refer to: Index type of MySQL

3, profiling analysis query
Through slow log query, we can know which SQL statements are inefficient to execute. Through explain, we can know the specific execution of SQL statements, index usage, etc. and we can also view the execution status with the show command. If you think the explain information is not detailed enough, you can get more accurate information of system resources consumed by SQL execution through the profiling command. Profiling is off by default. You can view it through the following statement:
1. Check whether profiling is on

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |              # OFF indicates not on
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |                      # 0 means not on
+-------------+
1 row in set, 1 warning (0.00 sec)

2. Open profiling

mysql> set profiling=1;              # open
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;       # Check whether it is on
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

3. Execute the SQL statement to test

mysql> select * from stu_info;
+------+--------+--------+
| s_id | s_name | s_into |
+------+--------+--------+
|    1 | 2      | 3      |
|    3 | 4      | 5      |
|    2 | 4      | 5      |
+------+--------+--------+
3 rows in set (0.00 sec)

4. View the ID corresponding to the SQL statement and analyze it

mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.00016900 | select @@profiling     |
|        2 | 0.00018425 | select * from bank     |
|        3 | 0.00018475 | select * from stu_info |
+----------+------------+------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 3;           #Detailed analysis of query sql statement
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000058 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000022 |
| init                 | 0.000015 |
| System lock          | 0.000006 |
| optimizing           | 0.000002 |
| statistics           | 0.000008 |
| preparing            | 0.000007 |
| executing            | 0.000001 |
| Sending data         | 0.000036 |
| end                  | 0.000002 |
| query end            | 0.000004 |
| closing tables       | 0.000004 |
| freeing items        | 0.000008 |
| cleaning up          | 0.000008 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

In the return result of the above command, status is the state in profile, and duration is the time-consuming in status. Therefore, we focus on which state is the most time-consuming, which state can be optimized, and of course, we can also view more information, such as CPU. The syntax is as follows:

mysql> show profile block io for query 3\G
mysql> show profile all for query 3\G

In addition to the above block io and all, it can also be replaced by cpu (display user cpu time, system cpu time), ipc (display send and receive related overhead information), page faults (display page error related overhead information), swaps (display exchange times related overhead information).
Note: after the test, remember to turn off the debugging function to avoid affecting the normal use of the database.
Note: after the test, remember to turn off the debugging function to avoid affecting the normal use of the database.
Note: after the test, remember to turn off the debugging function to avoid affecting the normal use of the database.
4, Optimize database table structure

Analysis table, checklist and optimization table
The so-called analysis table is to analyze the distribution of keywords. The check table is to check whether there are errors. The optimization table is a waste of space caused by deletion or update.
1. Analysis table
An analysis table can analyze one or more tables at a time. It can only be read during the analysis, and cannot be inserted or updated. The syntax of the analysis table is as follows:

mysql> analyze table stu_info;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| mysql.stu_info | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (0.01 sec)

Explanation for the above returned results: Table is the Table name, what is the operation performed by op, msg_type information level (status is normal, info is information, note note, warning warning, error), msg_text is the display message.
2. Checklist
Check whether there are errors, keyword statistics, check whether there are errors in the view Check table table name option ={quick |fast | medium|extended |changed} Quick does not scan the rows, does not check the error connection Fast only checks the table Medium scan rows that have not been closed correctly to verify that the deleted connections are valid, and can also calculate the keyword checksums of each row. Extended performs a comprehensive keyword search for all the keywords in each row. Changed only checks the tables that have been changed since the last check and those that have not been closed correctly. Option is only valid for myisam and invalid for innodb tables. It will add a read-only lock to the tables during execution.

mysql> check table stu_info;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| mysql.stu_info | check | status   | OK       |
+----------------+-------+----------+----------+
1 row in set (0.00 sec)

3. Optimization table
To eliminate the space waste caused by deletion or update, the command syntax format is: Optimize [local |no_write_to_binlog] table tb1_name … . both the optimized tables of myisam and innodb are valid, but only the varchar\text\blob number type in the table can be optimized, and the read-only lock is applied during execution.

mysql> optimize table stu_info\G
*************************** 1. row ***************************
   Table: mysql.stu_info
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: mysql.stu_info
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.04 sec)