随笔分类 - Sql
摘要：需要复制的数据较少的场合，可以控制源表扫描行数且加锁范围很小的情况下，直接用 insert...select 语句即可实现少量数据的复制。 数据量较大的情况，为了避免源表加读锁，需要先将数据放入外部文件，再写入目标表。 mysqldump 导出 mysqldump -h$host -P$port -
阅读全文
摘要：前面了解了 WAL 机制知道，只要 redo log 和 binlog 持久化到磁盘，就能确保 Mysql 重启后，数据可以恢复。 binlog 写入机制 事务执行过程中，日志先写入 binlog cache，事务提交时，再把 binlog cache 写入 binlog 文件中，并清空 cache
阅读全文
摘要：在实际开发过程中，业务高峰期常遇到 Mysql 响应变慢。为了不影响业务，要在短时间内临时提升性能。 短链接 原因分析 短链接是连接数据库后，执行很少的 SQL 后就断开，下次需要的时候再重新连接。这种情况很容易出现连接突然暴涨的情况 Mysql 建立连接时除了网络连接三次握手外，还要权限判断和获取
阅读全文
摘要：sql 性能优化，是代码编写过程中必定要考虑的内容，弄懂性能背后的逻辑，起到事半功倍的效果。 今天学习几种常见的简单优化场景。 条件字段中调用函数 以公司的打卡信息表为例，表中存放了公司近三年的打卡记录，add_time 字段有索引。 查询自己每年 7 月份的打卡情况， select count(*
阅读全文
摘要：误删数据是数据库操作过程中不可避免会遇到的问题。 误删分为几种，误删行、误删库/表、误删整个实例。 遇到问题就要分析原因，并对症下药解决问题。 误删行 使用 delete 语句误删数据行。 此时可以用 Flashback 工具通过闪回恢复数据。 原理是修改 binlog 内容，拿回原库重放，前提是确
阅读全文
摘要：Mysql 中有很多自增 ID 定义了自增 ID 的长度后，就有了最大值，就有可能被用完 表定义自增主键 ID 通过上一篇 Mysql 系列 | 自增 ID 很好理解，主键达到最大值，再申请 ID 时得到的还是原来的自增值，然后主键冲突，数据插入失败。 InnoDB 系统自增 row_id 如果表中
阅读全文
摘要：自增主键在每张表中都会存在，即使没有定义也会自动生成。 自增 ID 除了我们常说的表 ID 外，还有 row_id、thread_id、table_id 等。 下面只考虑每张表的 ID。 存在哪里 MyISAM 引擎，存在数据文件中 InnoDB 引擎， Mysql5.7 前存在内存中，没有持久化。
阅读全文
摘要：上一篇中说了 join 语句的执行过程，了解了深层逻辑，则优化方案呼之欲出。 Multi-Range Read（MRR） 优化 select * from t1 where a>=1 and a<=100; 回表时，根据 ID 去主键索引 B+ 树上查到一行数据。 当回表时 ID 是随机的，就会出现
阅读全文
摘要：join 也是平常开发中经常用到的，本篇了解一下 join 的执行过程。 t1、t2 两张表中有 a、b 字段，a 为索引。 ti 有 100 条数据，t2 有 1000 条数据。 Index Nested-Loop Join（索引嵌套查询） select * from t1 straight_jo
阅读全文
摘要：存储过程是实现特定功能的语句集合，编译后存在数据库中。 通过存储过程的名字，并给定参数来调用。 创建 create procedure 过程名([IN|OUT|INOUT 参数名 数据类型]) begin ; end; 通常在创建之前先进行删除操作,drop procedure if exists
阅读全文
摘要：上一篇 Mysql 系列 | order by 中介绍了全字段排序、rowid 排序以及借助索引优化排序。 在更复杂的 SQL 中，order by 还会有其他排序方式。 场景 在英语学习 APP 首页，每次打开会随机显示三个英语单词。也就需要每次从表中随机查出三条数据。 表数据越多，随机查询会变得
阅读全文
摘要：排序很常见，也很消耗资源，怎么尽可能地降低成本，提高效率！ 场景 在市民系统的市民信息表中，查询“杭州”的市民信息，并按照姓名排序返回前 1000 人的姓名、年龄 城市字段 city 加索引，避免全表扫描 SQL 语句，select city,name,age from t where city='
阅读全文
摘要：项目中常常需要统计表数据总数，select count(*) from t;。 在 InnoDB 搜索引擎中，count(*) 需要把数据都读到内存中然后计数，数据很多时会很慢。 count 字段或者 ID，会不会好一些呢！实际上在 Mysql 中，count(字段) < count(主键id) <
阅读全文
摘要：系统中经常会使用到邮箱登录 要验证登录信息常常要根据邮箱查询用户信息，select * from user where email = 'xxx' 怎么给 email 字段创建索引呢？ 创建完整索引 /* email 整个字段作为索引 */ alter table user add index in
阅读全文
摘要：默认情况下，SQL 语句中不会选择索引，都是由优化器自动选择。 优化器为了得到最优方案，会考虑扫描行数、是否使用临时表、是否排序等因素。 但有时候，因为种种原因，优化器选择的并不是最佳索引。 索引选择异常处理方案 analyze table 有时候 Mysql 不能准确判断数据的扫描行数，这时可以用
阅读全文
摘要：创建合适的索引，尽量少地访问数据库资源，是数据库结构设计需要考虑的内容。 场景 在一个市民信息表上，身份证号码是市民的唯一标识。 Mysql 系列 | 索引（唯一索引 or 普通索引）中讨论了给身份证号建立索引，即可根据身份证号找到对应的市民详细信息。 如果现在有一个高频请求，要根据身份证号查询姓名
阅读全文
摘要：EXPLAIN 用来模拟优化器执行 SQL 语句的执行计划。从而找到 SQL 优化方案。 EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。 EXPLAIN 不会真正执行 SQL 语句，只返回执行计划。 本篇使用 Mysql 8.0.12 EX
阅读全文
摘要：索引时数据库优化最常考虑的内容之一，用对索引可以大大提高查询效率。 场景 一个市民系统，每个人都有唯一的身份证号码， 业务代码已经保证了不会有两个重复的身份证号， 系统要按照身份证号查询姓名， select name from CUser where id_card = 'xxxxxxxyyyyyy
阅读全文
摘要：数据并发访问时，用锁来实现对数据访问权限的控制。 本文还是以 InnoDB 为默认存储引擎。 Mysql 中的锁 全局锁 对整个数据库实例加锁 全局读锁，让整个库处于只读状态 加锁命令，Flush tables with read lock (FTWRL)，其他线程的语句会被阻塞，数据增删改、建表修
阅读全文
摘要：索引就相当于一本书的目录，有了这个目录能更快查到自己想要的内容。大大提高数据查询效率。 Mysql 中索引在存储引擎层实现。 数据库中的索引 索引模型 哈希表 以键值对（key-value）存储数据 多个 key 经过哈希函数可能会计算出同样的值，相同 key 对应一个链表循环取值。 key 值不递
阅读全文
