发现
分析
调优理论
特定语句的原理与优化
Percona Toolkit
foodie-dec项目慢SQL调优实战
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mSmJlKh2-1660280846548)(assets/image-20220811140331-hj3ae6i.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aB1GUupO-1660280846549)(assets/image-20220811140352-6bjsy76.png)]
设置合理的数据库性能参数
操作系统提供了各种资源使用策略,设置合理的配置,以便于数据库充分利用资源
MySQL官方测试数据库-employee-data V : https://dev.mysql.com/doc/index-other.html
github地址:https://github.com/datacharmer/test_db
Installation:
Download the repository
Change directory to the repository
Then run
mysql < employees.sql
If you want to install with two large partitioned tables, run
mysql < employees_partitioned.sql
Testing the installation
After installing, you can run one of the following
mysql -t < test_employees_md5.sql
# OR
mysql -t < test_employees_sha.sql
For example:
mysql -t < test_employees_md5.sql
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+----------------------------------+
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+----------------------------------+
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| employees | OK | ok |
| departments | OK | ok |
| dept_manager | OK | ok |
| dept_emp | OK | ok |
| titles | OK | ok |
| salaries | OK | ok |
+--------------+---------------+-----------+
# 进入test_db项目的根目录
Last login: Thu Aug 11 15:09:23 on ttys000
# 执行导入
❯ mysql -uroot -pAbc@123456 < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:18
# 测试是否成功
❯ mysql -uroot -pAbc@123456 -t < test_employees_md5.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+----------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+----------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| departments | OK | ok |
| dept_emp | OK | ok |
| dept_manager | OK | ok |
| employees | OK | ok |
| salaries | OK | ok |
| titles | OK | ok |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:12 |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC | OK |
| count | OK |
+---------+--------+
这里使用IDEA连接数据库,具体的步骤可以百度查看,这里不做说明