孤独求学人

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) 浏览(770)

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) 浏览(984)

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) 浏览(927)

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) 浏览(6839)

MYSQL SQL 使用binary导致索引失效的问题分析与总结

2024-7-24 杜世伟 Mysql

在 MySQL 中，是否使用索引取决于查询的具体情况。以下是几个关键点，决定查询是否使用索引：

1. 查询条件：如果查询条件中的列有索引，并且查询能够利用这些索引，那么 MySQL 会尝试使用索引。例如，`WHERE` 子句中的条件列有索引。
2. 查询类型：对于一些特定类型的查询，如 `SELECT`、`UPDATE` 或 `DELETE`，MySQL 会尝试使用索引来优化查询性能。
3. 索引类型：
   - B-Tree 索引：默认情况下，MySQL 使用 B-Tree 索引。对于等值查询、范围查询等都能有效利用。
   - 全文索引：用于全文搜索。
   - 哈希索引：仅适用于 Memory 引擎，用于等值查询。
4. 数据类型：索引的使用也取决于列的数据类型和索引类型是否匹配。例如，`BINARY` 数据类型的列如果有 B-Tree 索引，在等值查询时可以有效使用。
5. 查询优化器：MySQL 查询优化器决定是否使用索引。它会根据查询的成本估算（例如全表扫描 vs. 使用索引扫描）来决定是否使用索引。你可以使用 `EXPLAIN` 语句查看查询的执行计划，确定 MySQL 是否使用了索引。

阅读全文>>

评论(0) 浏览(6233)

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) 浏览(10137)

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) 浏览(10084)

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) 浏览(7447)

mysql 备份指定前缀的数据库信息

2016-3-16 杜世伟 Mysql

mysql_dump.py 备份指定前缀的数据库信息,备份的文件支持gzip压缩！生成的文件名字指定了的DB名称加上导出数据的时间!

(1)运行脚本效果如下:
  请输入连接数据库的账号:root
  请输入连接数据库的密码:
  请输入连接数据库的地址(默认:127.0.0.1):127.0.0.1
  请输入导出数据库的前缀:test
  请输入连接数据库的端口号(默认:3306):3306
  请输入备份数据库的路径(默认:/home/,记得添加最后的斜线):/home/dbback/
  备份【test】数据成功！！
  备份【test001】数据成功！！
  备份【test02】数据成功！！
  备份【test07】数据成功！！

阅读全文>>

标签: linux mysql mysqldump

评论(0) 浏览(10445)

mysql explain 性能优化神器

2016-1-18 杜世伟 Mysql

阅读全文>>

标签: mysq explain

评论(0) 浏览(12228)