数据库学习——数据库优化

数据库优化

需要考虑优化的场景

• 系统的吞吐量一般出现在数据库的访问速度
• 随着数据量的逐步增大，处理时间会相应变
• 数据存储在硬盘上，读写速度和内存不匹配

优化方案：

• 硬件优化
• 缓存优化
• 设计优化
• sql语句优化

sql性能分析profile

利用mysql中的profile可以**记录所有的SQL执行的详细信息**

开启profile

查看环境变量show variables like 'profiling';

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.03 sec)

-- 开启profile日志
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.01 sec)

使用profile

查看profile记录show profiles;

mysql> insert into tb_user values(1,'张三','123456');
Query OK, 1 row affected (0.01 sec)

-- 查看profile记录
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.01121450 | show variables like 'profiling'               |
|        2 | 0.00040400 | SELECT DATABASE()                             |
|        3 | 0.02113475 | show tables                                   |
|        4 | 0.00032625 | dsc tb_user                                   |
|        5 | 0.00444000 | desc tb_user                                  |
|        6 | 0.01282900 | insert into tb_user values(1,'????','123456') |
+----------+------------+-----------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

查看特定查询的统计信息

show profile for query 查询编号Query_Id可以查看执行该SQL经历了哪些步骤，以及每个步骤消耗的时间

mysql> show profile for query 4;
+---------------+----------+
| Status        | Duration |
+---------------+----------+
| starting      | 0.000238 |
| freeing items | 0.000074 |
| cleaning up   | 0.000015 |
+---------------+----------+
3 rows in set, 1 warning (0.00 sec)

慢查询日志

MySQL慢查询全名称为慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中执行sql语句的**响应时间超过阈值的sql语句**

• 具体环境中，运行时间超过long_query_time值的sql语句会被记录在慢查询日中。long_query_time的默认值为10，意思是记录运行时间为10秒以上的sql语句

• 默认情况下，MySQL并不会开启慢查询日志，需要手工设置这个参数。如果不是进行系统调优的需要时，不建议启动该参数。因为启动慢查询日志会带来一定的性能损耗

• 慢查询日志支持将日志信息记录在文件或者数据表。用于记录执行时间超过临界阈值的sql语句，可以快速定位应用中的慢查询，是sql语句优化的参考和基准

开启慢查询

配置项slow_query_log

查看慢查询是否开启show variables like 'slow_query_log;'，环境变量值为OFF表示关闭

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set, 1 warning (0.00 sec)

开启后在data下则会产生一个xxx-slow.log文件，其中存储慢查询日志

设置临界时间

MySQL默认慢查询的阈值为10秒，允许通过配置参数long_query_time进行修改，单位为秒

查看慢查询阈值： show variables like 'long_query_time';

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

修改默认阈值 set long_query_time=1; 将阈值修改为1s

执行一个长时间的查询操作 select sleep(5);

mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.01 sec)

具体的日志记录

D:\mysql-8.0.15-winx64\bin\mysqld, Version: 8.0.15 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2022-05-08T03:50:29.263909Z
# User@Host: root[root] @ localhost [::1]  Id:    16
# Query_time: 5.011263  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1651981824;
select sleep(5);
# Time: 2022-05-11T11:08:01.541478Z
# User@Host: root[root] @ localhost [::1]  Id:    63
# Query_time: 5.018711  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1652267276;
select sleep(5);

慢查询的相关配置参数

• slow_query_log是否开启慢查询日志，1开启0关闭

• slow_query_log_file设置慢查询日志的存储路径，可以不设置该参数，系统会默认一个缺省文件【主机名称-slow.log】

• long_query_time设置慢查询的阈值，默认为10秒钟，当查询时间多于设置的阈值时，可以记录日志

• log_queries_not_using_indexes设置没有使用索引的查询会被记录到查询日志中

• log_output设置日志存储方式，值为FILE表示采用文件的方式记录日志，默认值FILE；如果值为TABLE表示将慢查询日志记录存储在数据库

在命令行中执行 set global show_query_log=1 可以开启慢查询日志，但是仅仅只对当前数据库生效，如果MySQL重启后则会失效。如果需要**永久生效，就必须修改my.ini配置文件**

注意：实际上的慢查询的阈值设置需要考虑具体情况，一般从大到小逐步筛查，目的在于将最慢的SQL语句优化掉

设计优化

• 设计数据库时，应该充分考虑数据表和字段的设计以及存储引擎的选择

  1、尽量使用整型数表示字符串。例如存储IP地址：inet_aton(字符串)和inet_ntoa(数值)

  2、表的字段类型一般不采用enum和set类型，因为维护成本太高，可以采用关联表的方式来替代enum

  3、使用decimal可以对浮点数进行精确存放，但是存储空间会随着数值的增大而增大；一般建议使用固定空间，例如double，但是double会损失存储精度。

  4、尽可能使用not null约束，针对允许为null的字段可以考虑定义default。由于null值判断比较繁琐。例如不要使用 id int ，可以考虑使用 id int default 0

  5、字段注释需完整，最好可以见名知意

  6、一般建议单表的字段不易过多。一般20-30就是极限

  7、可以有预留字段。

  总之所有的设计过程就是在性能和需求之间平衡的结果

• 充分利用MySQL自身提供的功能，例如索引等

  需要记忆NF3和反范式

  需要记忆常见的索引失效情形和执行计划查询的命令 exaplain

• 横向扩展，引入MySQL集群、负载均衡和读写分离

• SQL语句优化

  1、使用limit对查询结果的记录进行限定

  2、避免使用 select * ，将需要查询的字段列表出来 【原因】

  3、使用join代替子查询

  4、拆分大的delete或者insert语句【delete和truncate table】

  5、可以通过开启慢查询定位应用中的执行较慢的sql语句

  6、一般不进行列计算。例如 select id from t_users where age+1=10; 但是针对列的计算操作会导致整表扫描，一般建议查询时尽可能将操作移动到等号的右边 select if from t_users where age=10-1

压力测试mysqlslap

安装mysql时，系统自带了一个压力测试工具mysqlslap，位于bin目录下

1、自动生成sql测试

2、并发测试，例如默认100个并发量

3、多轮测试，循环测试10次

语句优化步骤

1、分析使用慢查询日志，查找需要进行优化的SQL语句。

2、针对慢查询的常见优化方法

• 创建合理的索引，并使用explain查看执行计划，确认索引是否生效

• 优化数据库结构。在设计过程中需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等方面。

• 分解关联查询，可以将一个大的复杂查询分为多个小查询，可以对每个需要关联的表单独查询，然后将查询结果在应用程序中进行关联

• 典型的考试题：针对limit分页的优化查询 select * from t_users limit 1000000,10 ;

• 分析具体的SQL语句，例如选择数据量小的文件充当驱动表，将in子查询转换为exists子查询