1.27 MySQL Tuning

1. Overall Approach

1. Record slow SQL through slow query logs / monitoring / Druid.
   • MySQL Tuning.md
2. Analyze with explain.
   • Index invalidation.
   • Too many tables in join queries.
   • Low server parameter configuration.
3. Add indexes.
   • MySQL Indexes.md
   • Pay attention to scenarios where indexes cannot be used.
4. Modify SQL statements.
   • Use join or exists depending on the situation.
   • Do not use select *.

Database Optimization.md

2. Single-Server MySQL Bottleneck

3 million rows, 2,000 concurrent requests.

3. Slow Query Log

https://mariadb.com/kb/en/library/documentation/mariadb-administration/server-monitoring-logs/slow-query-log/slow-query-log-overview/ https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

3.1. What It Is

Record statements whose query time exceeds a certain threshold in the log.

show variables like '%slow_query_log%';

+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| slow_query_log      | OFF                    |
| slow_query_log_file | <example>-slow.log     |
+---------------------+------------------------+

show variables like '%long_query_time%';

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

3.2. How to Enable It

set global slow_query_log=1;
set global long_query_time=3;

You need to reconnect to the database before the effect becomes visible.

3.3. How to View It

• View the log directly.

tail -f /var/lib/mysql/<example>-slow.log -n 200

• Use the mysqldumpslow command.

4. explain

• MySQL explain.md

5. Show Profile

5.1. What It Is

show variables like 'profiling'

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+

5.2. Enable It

set profiling=on;

5.3. View Results

show profiles;

5.4. Analyze

show profile cpu,block io for query 3;