为什么数据库连接很消耗资源？

2024-12-19 杜世伟 Message

要更详细地分析为什么数据库连接消耗资源，我们可以从连接的建立过程、运行时的资源占用以及并发管理的挑战三个方面深入探讨。

1. 数据库连接的建立过程

建立数据库连接是一个复杂的过程，涉及多个步骤，每个步骤都会消耗资源。

1.1 客户端与服务器的网络通信

  • DNS解析：当客户端尝试连接数据库时，首先需要解析数据库服务器的地址（如果使用域名）。
  • TCP三次握手：客户端和服务器之间建立一个可靠的TCP连接，需要完成三次握手。这个过程需要一定的网络延迟，尤其在分布式环境中可能更为明显。
  • SSL/TLS握手（可选）：如果数据库连接使用加密（如SSL/TLS），双方需要协商安全协议、交换密钥，这个过程涉及密钥生成和加解密运算，消耗CPU资源。

1.2 数据库认证

  • 客户端发送凭据（如用户名和密码）到服务器。
  • 数据库服务器需要验证这些凭据，这可能涉及读取用户表、计算哈希、对比凭据等操作。对于复杂的认证（如LDAP、Kerberos），需要额外的网络调用和计算。

阅读全文>>

标签: mysql db 数据库 连接

评论(0) 浏览(509)

MySQL ORDER BY field1, field2 DESC 和 ORDER BY field1 DESC, field2 DESC 的区别

2024-12-18 杜世伟 Mysql

主要区别总结
ORDER BY field1, field2 DESC:
field1 升序排序（默认）。

当 field1 值相同时，按 field2 降序排序。


ORDER BY field1 DESC, field2 DESC:
field1 降序排序。
当 field1 值相同时，按 field2 降序排序。
排序方向的指定顺序会显著影响结果，因此在写 SQL 时，需要明确每个字段的排序优先级和方向。

阅读全文>>

标签: mysql desc order

评论(0) 浏览(857)

Innodb与Myisam引擎的区别与应用场景

2024-11-22 杜世伟 Message

InnoDB 和 MyISAM 是 MySQL 数据库中常用的两种存储引擎，它们的设计目标、功能特性和适用场景有显著差异。以下是它们的区别和应用场景：

主要区别

1. 事务支持

  • InnoDB: 支持事务 (Transaction)，提供 ACID 特性，可以通过 COMMIT 和 ROLLBACK 确保数据的一致性。
  • MyISAM: 不支持事务，适合不需要事务管理的场景。

2. 锁机制

  • InnoDB: 使用行级锁（Row-Level Locking），并发性能更高，适合高频读写操作的应用。
  • MyISAM: 使用表级锁（Table-Level Locking），在写操作时会锁定整个表，导致并发性能较差。

3. 外键支持

  • InnoDB: 支持外键约束，能维护数据的参照完整性。
  • MyISAM: 不支持外键约束，依赖应用层逻辑实现。

阅读全文>>

标签: mysql InnoDB myisam

评论(0) 浏览(714)

MySQL 给数据表增加一列，一定会锁表吗？

2024-11-21 杜世伟 Mysql

在 MySQL 中，给数据表增加一列（ALTER TABLE 操作）是否会锁表，取决于以下几个因素：

1. 存储引擎
InnoDB：这是 MySQL 默认的事务性存储引擎，从 MySQL 5.6 开始，InnoDB 支持在线 DDL（Online DDL），在某些情况下可以避免完全锁表。

MyISAM：这种存储引擎不支持在线 DDL，因此会对表进行完全锁定。


2. ALTER TABLE 的具体操作
不同的操作对锁表的影响不同：
新增列：简单的增加一个不带默认值或允许 NULL 的列，InnoDB 通常支持在线 DDL，可以避免锁表。
新增列并带有默认值：如果需要对现有数据进行填充（例如新增列设置了非空约束且有默认值），可能会触发对表中每一行的更新，此时通常会锁表。

新增列并影响索引：如果新增的列涉及索引或主键，也可能导致表锁。

阅读全文>>

标签: mysql InnoDB lock ddl

评论(0) 浏览(1047)

mysql binlog格式是有哪些，各自优点和缺点有哪些？

2024-10-18 杜世伟 Mysql

MySQL 的 binlog（二进制日志）用于记录对数据库进行的所有更改操作，主要用于数据恢复、主从复制和审计。binlog 有三种格式，每种格式在不同的场景下有各自的优点和缺点：

1. STATEMENT 格式

在 STATEMENT 格式下，MySQL 将每一条修改数据的 SQL 语句记录到 binlog 中，而不是记录具体的行级数据变化。换句话说，binlog 中记录的内容就是执行的 SQL 语句。

优点：

  • 占用空间小：相比其他格式，STATEMENT 格式只记录 SQL 语句，binlog 文件的大小通常较小。
  • 性能较好：因为它只记录 SQL 语句，而不是每一行数据的变化，生成和写入 binlog 的开销较小，尤其是当修改了大量数据时，binlog 的生成速度和磁盘 I/O 负担较轻。
  • 适合大多数简单的操作：对于简单的 INSERT、UPDATE、DELETE 语句，使用 STATEMENT 格式足够高效。

缺点：

  • 非确定性行为可能出错：如果 SQL 语句在执行时存在非确定性，如包含 NOW()、UUID()、RAND() 这样的函数，或者依赖于自定义的用户变量，STATEMENT 可能导致主从复制的不一致。
  • 复杂语句的重放风险：某些复杂的 SQL 语句（如 INSERT ... SELECT、触发器、存储过程等）在主从复制或数据恢复时可能会引发问题，因为这些语句的执行顺序和环境依赖于运行时的上下文。
  • 性能副作用：某些语句在主库上执行一次，可能会在从库上执行多次，增加了从库的负担。例如，涉及到大规模表的 UPDATE 和 DELETE 操作。

阅读全文>>

标签: mysql binlog STATEMENT row MIXED

评论(0) 浏览(934)

MySQL 中的 distinct 和 group by 哪个效率更高

2024-8-16 杜世伟 Mysql

在 MySQL 中，DISTINCT 和 GROUP BY 的效率取决于查询的上下文、数据的分布、索引的存在与否等因素。一般来说，两者的执行效率是非常接近的，因为 MySQL 通常会在内部使用相同的机制来执行这两种操作。

DISTINCT 和 GROUP BY 的区别

  • DISTINCT 用于去除查询结果中的重复行，保留唯一的记录。
  • GROUP BY 则用于将查询结果按照指定列进行分组，并通常与聚合函数（如 COUNT、SUM、AVG 等）一起使用。

执行计划

在某些情况下，MySQL 执行 DISTINCT 和 GROUP BY 语句时，会生成类似的执行计划。如果查询没有复杂的聚合操作，MySQL 通常会将这两者优化为类似的操作。

阅读全文>>

标签: mysql distinct group

评论(0) 浏览(6901)

ERROR 1010 (HY000): Error dropping database (can't rmdir './******', errno: 39)

2018-3-11 杜世伟 Mysql

今天操作数据库出现以下错误提示：

ERROR 1010 (HY000): Error dropping database (can't rmdir './******', errno: 39)

字面意思是说删除数据失败，不能删除目录./******

数据库版本信息:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.2    |
+-----------+
1 row in set (0.00 sec)

mysql> use 数据库名;
Database changed
mysql> show tables;
Empty set (0.00 sec)
发现数据库中已经没有表了，这个时候为什么drop database不成功呢？奇怪了......
通过以下命令查看数据库文件所在目录：
mysql> select @@datadir;
+--------------+

阅读全文>>

标签: mysql drop version

评论(0) 浏览(10153)

MyISAM几个容易忽视的配置选项

2017-7-19 杜世伟 Mysql

MyISAM在读操作占主导的情况下是很高效的。可一旦出现大量的读写并发，同InnoDB相比，MyISAM的效率就会直线下降，而且，MyISAM和InnoDB的数据存储方式也有显著不同：通常，在MyISAM里，新数据会被附加到数据文件的结尾，可如果时常做一些 UPDATE，DELETE操作之后，数据文件就不再是连续的，形象一点来说，就是数据文件里出现了很多洞洞，此时再插入新数据时，按缺省设置会先看这些洞洞的大小是否可以容纳下新数据，如果可以，则直接把新数据保存到洞洞里，反之，则把新数据保存到数据文件的结尾。之所以这样做是为了减少数据文件的大小，降低文件碎片的产生。但InnoDB里则不是这样，在InnoDB里，由于主键是cluster的，所以，数据文件始终是按照主键排序的，如果使用自增ID做主键，则新数据始终是位于数据文件的结尾。
了解了这些基础知识，下面说说MyISAM几个容易忽视的配置选项：
concurrent_insert：
通常来说，在MyISAM里读写操作是串行的，但当对同一个表进行查询和插入操作时，为了降低锁竞争的频率，根据concurrent_insert的设置，MyISAM是可以并行处理查询和插入的：

阅读全文>>

标签: linux mysql

评论(0) 浏览(10092)

MySQL大表优化方案

2016-8-3 杜世伟 架构

当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化：

单表优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

字段

  • 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED

  • VARCHAR的长度只分配真正需要的空间

  • 使用枚举或整数代替字符串类型

  • 尽量使用TIMESTAMP而非DATETIME，

  • 单表不要有太多字段，建议在20以内

  • 避免使用NULL字段，很难查询优化且占用额外索引空间

  • 用整型来存IP

索引

  • 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

阅读全文>>

标签: mysql

评论(0) 浏览(1611)

ERROR 1286 (42000): Unknown table engine 'InnoDB'

2016-6-14 杜世伟 Mysql 



今天在操作数据的的时候，突然提示以下错误：
ERROR 1286 (42000): Unknown table engine 'InnoDB'


mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.57    |
+-----------+
1 row in set (0.01 sec)

mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                   | Transactions | XA   | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| CSV        | YES     | CSV storage engine                                        | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+

4 rows in set (0.00 sec)
mysql> show variables like 'have_innodb%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | NO   |
+---------------+-------+
1 row in set (0.00 sec)
至此发现数据库不支持INNOD引擎！！！
mysql> show variables like '%dir%';

阅读全文>>

标签: mysql show enginx InnoDB

评论(0) 浏览(7454)

Powered by emlog 沪ICP备2023034538号-1