随笔分类 - mysql
关于mysql相关的数据库DDL 及DDM等随笔
摘要:一、表信息更新的基本逻辑 默认情况下,MySQL 会高效的从 系统表 mysql.index_stats 和 mysql.table_stats 中检索这些列的缓存值,而不是直接从存储引擎中获取统计信息。如果缓存的统计信息不可用或已过期,MySQL 将从存储引擎中检索最新的统计信息,并将其统计信息更
阅读全文
摘要:1、my.cnf设置了max_connections = 20000,但是查看最大连接数是41902、问题原因 /lib/systemd/system/mysqld.service LimitNOFILE=5000 3、解决办法打开mysql服务文件mysqld.service中的[Service]
阅读全文
摘要:redo log和binlog区别 redo log是属于innoDB层面,binlog属于MySQL Server层面的,这样在数据库用别的存储引擎时可以达到一致性的要求。 redo log是物理日志,记录该数据页更新的内容;binlog是逻辑日志,记录的是这个更新语句的原始逻辑 redo log
阅读全文
摘要:一、关于逻辑备份和物理备份 物理备份恢复速度比较快,占用空间比较大。 逻辑备份速度比较慢,占用空间比较小。逻辑备份的恢复成本高。 二、逻辑备份 逻辑备份是备份sql语句,在恢复的时候执行备份的sql语句实现数据库数据的重现。比如:mysqldump是采用SQL级别的备份机制,它将数据表导成SQL脚本
阅读全文
摘要:Rule Based Optimizer(RBO)基于规则Cost Based Optimizer(CBO)基于成本,或者讲统计信息ORACLE 提供了CBO、RBO两种SQL优化器。CBO在ORACLE7 引入,但在ORACLE8i 中才成熟。ORACLE已经明确声明在ORACLE9i之后的版本中
阅读全文
摘要:最近在做一个数据关联处理分析需求,涉及left表right表LEFT JOIN,发现LEFT JOIN后的结果条数比left表的记录多不少。 于是查看结果明细发现最终的结果,不少都是重复的。 最终确定是left 表和right表是一对多的关系,或者说right表有重复的记录也是一样的效果。 于在本地
阅读全文
摘要:insert into xxx on duplicate key update xxx=xxx语句,解决插入数据时产生的重复主键问题,前提是设置了正确的unique key。 相当于完成的操作: if not exists (select 。。。。。。) insert into 。。。。。。else
阅读全文
摘要:关于timestamp的两属性CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP,使用示例如下: CREATE TABLE `upload_table` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
阅读全文
摘要:直接上例子了,查询近一周TOP20的订单商品。 mysql> select (@ii:= @ii+1) as "序号", otmp.* from (select sku_id as "SKU_ID",count(*) as "数量" from order where order_time BETWE
阅读全文
摘要:30天前的日期 select DATE_SUB(CURDATE(), INTERVAL 30 DAY);当时的季度 select QUARTER(now());上一个季度 select QUARTER(DATE_SUB(now(),interval 1 QUARTER));6个月前的时间 selec
阅读全文
摘要:MySQL 的 case when 的语法有两种: 简单函数 CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END 搜索函数 CASE WHEN [expr] THEN [result1]…ELSE [default] END
阅读全文
摘要:1、查看当前数据库的连接情况 show full processlist; show processlist;2、查看所有用户的总连接数 show variables like '%max_connections%'; 3、查看每一个用户的最大连接数show variables like '%max
阅读全文
摘要:最近在优化慢SQL,现在通过explain查看执行计划有几列还是有些模糊,特此做一下笔记。 Explain命令主要应用在解决数据库性能上,大部分的性能问题可以通过此命令来简单的解决,方便查看SQL语句的执行效果,帮助选择更好的索引和优化查询语句。 其语法:explain select … from
阅读全文
摘要:最近有一个场景,在生产环境的一个库中,新增了一个字段。需要从另一个关联表中找到相当的字段回填。 影响数据数百万条。 首先,不能使用一条大的update语句来更新,这个锁太大,容易产生锁征用,造成死锁。 update B a set new_column=(SELECT other_col from
阅读全文
摘要:假定要查的数据库名称是“xxxx_mall” 1、查看数据库数据占用大小 select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema
阅读全文
摘要:mysql排查线上数据库问题,经常会用到 show processlist和show full processlist这两条命令 processlist命令的输出结果显示了有哪些线程在运行,不仅可以查看当前所有的连接数,还可以查看当前的连接状态帮助识别出有问题的查询语句等。 如果是root帐号,能看
阅读全文
摘要:最近遇到个场景,就是根据有序的列表用户ID,批量查询用户信息列表,并且保持结果排序不变。 一、语法: MySQL中的field()函数,可以用来对SQL中查询结果集进行指定顺序排序 ORDER BY FIELD(field,str1,str2,str3,str4……) DESC field为排序字段
阅读全文
摘要:最近才考虑数据库迁移,想起了之前做DTS踩过的那些坑。 DTS同步binlog,开始是使用binlog event + position方式,之后追加支持了GTID。 基于数据库迁移,比如从源A库迁移到源B库,包括但不限于数据库上云。 数据库迁移方案有两种场景: (1)、停机迁移方案【停服时间比较长
阅读全文
摘要:1、索引相关 查看表索引 show index from `user` 查看sql的执行计划 explain select * from where user 2、存储过程相关 查看存储过程 show procedure status 查看存储过程AAA的ddl show create proced
阅读全文
摘要:带参数的存储过程: 特别说明: 在游标中是不支持对形参的判断的,外部可以 查看所有的存储过程: show procedure status; 查看某个存储过程的创建schema: show create procedure yyyyy_zzzz.settlexxxxx_common; 调用操作: c
阅读全文