随笔分类 - 数据库
摘要：grant之后要跟着flush privileges吗？ grant 语句会同时修改数据表和内存，判断权限使用的是内存数据。规范地使用grant和revoke语句，不需要随后加上flush privileges语句的。 flush privileges 其实就是清空了内存中的权限，然后重新从权限表加
阅读全文
摘要：怎么最快地复制一张表？ mysqldump mysqldump -hport -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900
阅读全文
摘要：39 | 自增主键为什么不是连续的？ 自增主键保存在哪里？ 自增主键并不保存在表结构定义的frm文件中。 MyISAM 引擎自增值保存在数据文件中 MySQL 8.0之前，自增值保存在内存中，重启后设置为max(id) + 1; MySQL 8.0之后，自增长值保存在了redo log中，重启的时候
阅读全文
摘要：35 | join语句怎么优化？ MRR Multi-Range Read 优化，指的是尽量使用顺序读盘。 因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。 怎么是顺序读 根据索引将数据放入read_rnd_
阅读全文
摘要：select * from t1 join t2 on t1.c = t1.c 这样的语句，mysql优化器会选择t_a或t_b作为驱动表, 使用straight_join来指定 左边的表为驱动表。如果字段c上有建立索引，MySQL会使用 Index Nested-Loop Join 策略处理joi
阅读全文
摘要：我查这么多数据，会不会把数据库内存打爆？ 肯定的说，不会。因为MySQL是边读边发的。 InnoDB 的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表 t 的主键索引。这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。那么，这个“结果集”存在哪
阅读全文
摘要：这些“kill 不掉”的情况，其实是因为发送 kill 命令的客户端，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。而被 kill 的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的。 kill 命令 MySQL 提供了 kil
阅读全文
摘要：如此处理各种情况下的误删数据 delete 语句删除 用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据恢复回来。Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row
阅读全文
摘要：读写分离有哪些坑？ 读写分离存在的问题，主要是从库不可避免存在同步延迟，导致客户端在从库读取到旧数据。 读写分离架构 读写分离主要目的时分摊主库的压力。 上面的结构是client主动选择后端数据库。 还有一种结构是带Proxy的读写分离架构 客户端直连和带proxy读写分离架构的优缺点 客户端直连结
阅读全文
摘要：主库出问题了，从库怎么办？ 备库：同步主库的binlog，当主库出问题时，备库切换为主库。一般不提供读服务。 从库：同步主库的binlog，只对外提供读服务。 一主多从主备切换 方法 基于位点的主备切换 首先我们知道，设置从库时的命令 CHANGE MASTER TO MASTER_HOST=$ho
阅读全文
摘要：
主备切换 主备流程图 主库的后台线程通过长连接将binlog日志同步到从库，从库的io_thread接受binlog写入从库的redo log ，从库的sql_thread线程重放redo log 到数据库。 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名
阅读全文
主备切换 主备流程图 主库的后台线程通过长连接将binlog日志同步到从库，从库的io_thread接受binlog写入从库的redo log ，从库的sql_thread线程重放redo log 到数据库。 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名
阅读全文
摘要：慢SQL治理主要从三个方面进行优化 从数据表的定义开始优化 从索引设计开始优化 从查询开始优化 优化表设计 表字段长度尽量紧凑，字段尽量不冗余 字段类型尽量考虑用简单的类型 字段尽量有默认值，且默认值尽量不要用null 优化索引 设计索引的时候，尽量让查询用到索引，并且减少回表次数。 业务查询频率高
阅读全文
摘要：binlog的写入机制 每个线程都会先将日志写入到binglog cache， 事务提交的时候，再把binlog cache写到binlog文件中。所有线程都写一个binlog日志。 binlog_cache_size 用于控制单个线程内binlog cache 所占内存的大小，如果超过了这个参数规
阅读全文
摘要：短连接风暴 正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。 怎么解决短连接引起数据库连接数暴涨的问题？ 先处理掉那些占着连接但是不工作的线程。怎么做呢？show processlist, 踢掉显示为sleep的线程，但是这样也会误杀执行事务的线程，使用命令
阅读全文
摘要：幻读指的是事务中读取到的记录数量不一致，只有RR可重复读事务隔离级别才存在幻读。 并且幻读需要当前读才会出现。 幻读有什么问题？ 造成数据不一致的问题。尤其时binlog 格式为statement。先开始但后提交的事务会将数据覆盖更新了 如何解决幻读？ MVCC版本控制 + 间隙锁。间隙锁和间隙锁不
阅读全文
摘要：众所周知，MySQL索引是以B+树存储的，而且是否使用索引是由引擎决定的，当MySQL觉得走索引花费开销大时，反而会放弃走索引。 总结一下，哪些情况下会使索引失效： 对where条件中的字段进行了函数操作，比如month(t_modified)=7; 隐式类型转换，比如where id_card=1
阅读全文
摘要：事务 // 查询当前数据库执行时间在10秒以上的事务 SELECT t.trx_mysql_thread_id ,t.trx_state ,t.trx_tables_in_use ,t.trx_tables_locked ,t.trx_query ,t.trx_rows_locked ,t.trx_
阅读全文
摘要：主备延迟 怎么在备库查看主备延迟 在备库执行show slave status, 结果的seconds_behind_master 就是当前备库延迟了多少秒。 主备延迟的原因 从库的机器配置比较差 备库执行了统计类型的SQL，导致压力大 主库正在执行大事务。如果一个主库上的语句执行 10 分钟，那这
阅读全文
摘要：我们知道MySQL 的innodb引擎使用redo log 实现crash safe， 并且更新数据时，先更新内存，然后写完redo log，然后后台线程将redo log 异步刷入磁盘。 内存与磁盘的数据页不一致，这样的数据页叫做脏页，mysql偶尔查询慢，可能就是在刷脏页到磁盘，刷脏页有几种情况
阅读全文
摘要：创建完整索引，会占用比较多的空间 创建前缀索引 比如需要给用户表的name字段增加索引，取前6字符作为前缀索引，命令是 alter table user add index idx_name name(6); 但千万注意，此时创建的name索引无法和主建id组成覆盖索引， select name,
阅读全文
