mysql

MySql实战45讲笔记 --- 实践篇

Posted by Sutdown on February 18, 2025

普通索引和唯一索引

  • 普通索引：找到满足条件的第一个记录之后，会查找下一个记录，直到不满足要求。
  • 唯一索引：查找到第一个满足条件的记录后，就会停止继续检索。

两种索引的性能差距微乎其微。当需要读一条记录的时候，以页为单位，整体从磁盘读入内存。在InnoDB中，每个数据页的大小默认16KB，当找k=5的记录，它所在的数据页就都在内存里，对于普通索引，要多做一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算；如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

change buffer：当更新数据页时，如果数据页不在内存中，不影响数据一致性的情况下，innoDB会将该更新操作缓存在change buffer中，下次需要访问该数据页时，将其读入内存，执行change buffer中与这个页相关操作。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。唯一索引的更新就不能使用change buffer，因为唯一索引一定要读入内存看有没有冲突，普通索引则是会将更新记录在change buffer，减少了磁盘的随机访问，提升部分性能。

举个例子：

某个业务的库内存命中率突然从99%降低到了75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，这个业务有大量插入数据的操作，而某员工在前一天把其中的某个普通索引改成了唯一索引

change buffer使用较好的场景是写多读少，比如账单累，日志类。它在读时容易触发merge过程，频繁的读操作不会减少访问IO的次数，反而增加了change buffer的维护代价。

Q：change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？change buffer丢失可不是小事儿，再从磁盘读入数据可就没有了merge过程，就等于是数据丢失了。

A：虽然是只更新内存，但是在事务提交的时候，我们把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。

MySQL为什么有时候会选错索引

选择索引是优化器的工作。优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

既然是统计信息不对，那就修正。analyze table t 命令，可以用来重新统计索引信息。

解决方案：

  • 采用force index强行选择一个索引。
  • 修改语句，引导MySQL使用我们期望的索引。
  • 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

为什么会选错索引

如何给字符串字段加索引

  1. 直接创建完整索引，这样可能比较占用空间；
  2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

为什么Mysql会抖动

内存中会存储信息，在空闲时刷新到磁盘，占用适当资源，因此会抖动。

为什么表数据删掉一半，表文件大小不变

也就是删除了表但是没有回收空间，其中的原理需要从删除流程看起：删除其实是删除B+树的结点，回收空间过于的复杂，只会标记这个空间可以复用。

重建表则是处理掉类似于这种只标记而没有存放实际有效数据的空间，以达到收缩表的目的。重建也是重新建立一个新表遍历原来的表，具体流程如下：

  1. 建立一个临时文件，扫描表A主键的所有数据页；
  2. 用数据页中表A的记录生成B+树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
  5. 用临时文件替换表A的数据文件。

在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。所以当原表没有什么空洞时重建可能导致表变大。

count(*)慢的原因

  • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
  • 而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

count(字段)<count(主键id)<count(1)≈count(*)

日志和索引

两阶段提交：准备阶段（写入binlog）和提交阶段

binlog：记录数据库执行的增删改查，确保主从一致

崩溃恢复时的判断规则：

  1. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；
  2. 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整： a. 如果是，则提交事务； b. 否则，回滚事务。

order by执行过程

会存在sort buffer size，在其中利用相应字段为某一行排序

如果行的数值太大，则会只选择部分字段

如果行数太多，会向磁盘借一些临时空间

随机显示消息

mysql> select word from words order by rand() limit 3;
  1. 取得整个表的行数，并记为C。
  2. 取得 Y = floor(C * rand())。 floor函数在这里的作用，就是取整数部分。
  3. 再用limit Y,1 取得一行。

逻辑相似实践却相差大的操作

  • 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

查一行也很慢的情况

1 等MDL锁

现在有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了。

在sys.SCHEMA_TABLE_LOCK_WAITS中查找阻塞的process id，

select blocking_pid from sts.schema_table_lock_waits

2 等flush

有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句。

show processlist

3 查询慢

执行时间慢，上一步更新，需要查询大量的undo log

幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  2. 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

事务的隔离级别：

脏读，不可重复读，幻读，可重复读

锁

难点，待办

mysql提高性能的办法

1 处理占着连接不公正的线程

2 减少连接过程的消耗。比如重启数据库时 采用-SKIP-GRANT-tables，mysql会跳过所有的权限验证阶段，但是风险高。

慢查询可能产生的情况

  1. 索引没有设计好；
  2. SQL语句没写好；
  3. MySQL选错了索引。

MYSQL如何保证数据不丢失

WAL机制，redo log和bin log持久化写入磁盘，就能确保mysql异常重启后数据可以恢复。

binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

对于redo log，分为三个区域，内存中的redo log buffer, FS page cache（写到磁盘，但是还没有持久化），hard dist

  1. 一种是，redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，后台线程会主动写盘。
  2. 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。

mysql如何保证主备一致

  1. 在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。
  2. 在备库B上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。
  3. 主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。
  4. 备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。
  5. sql_thread读取中转日志，解析出日志里的命令，并执行。

主备延迟

1 备用库机器规格差

2 备库压力大

3 大事务

4 &&备库并行复制能力

一主多从：读写分离

读写分离的主要目标就是分摊主库的压力。图1中的结构是客户端（client）主动做负载均衡，这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说，由客户端来选择后端数据库进行查询。

还有一种架构是，在MySQL和客户端之间有一个中间代理层proxy，客户端只连接proxy， 由proxy根据请求类型和上下文决定请求的分发路由。

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。这种“在从库上会读到系统的一个过期状态”的现象，在这篇文章里，我们暂且称之为“过期读”。

  • 强制走主库方案；
  • sleep方案；
  • 判断主备无延迟方案；
  • 配合semi-sync方案；
  • 等主库位点方案；
  • 等GTID方案。

如何判断数据库的主库有没有出问题

select1判断：只能判断库的进程还在，不能说明主库没问题。虽然说等锁的线程不算在并发线程计数里，但如果它在真正地执行查询，就比如我们上面例子中前三个事务中的select sleep(100) from t，还是要算进并发线程的计数的。同时在执行的语句超过了设置的innodb_thread_concurrency的值，这时候系统其实已经不行了，但是通过select 1来检测系统，会认为系统还是正常的。

查表判断：为了能够检测InnoDB并发线程数过多导致的系统不可用情况，我们需要找一个访问InnoDB的场景。一般的做法是，在系统库（mysql库）里创建一个表，比如命名为health_check，里面只放一行数据，然后定期执行。但是不能检测出来以下这种情况

更新事务要写binlog，而一旦binlog所在磁盘的空间占用率达到100%，那么所有的更新语句和事务提交的commit语句就都会被堵住。但是，系统这时候还是可以正常读数据的。

更新判断：常见做法是放一个timestamp字段，用来表示最后一次执行检测的时间。

内部统计：可以通过MAX_TIMER的值来判断数据库是否出问题了。比如，你可以设定阈值，单次IO请求时间超过200毫秒属于异常

加锁

  • 原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。
  • 原则2：查找过程中访问到的对象才会加锁。
  • 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
  • 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
  • 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

误删数据

1 预处理：

  • 使用规范
  • 不太的开发人员权限不太

2 恢复方案

  • binlog日志恢复
  • 全量备份
  • 对重要数据的从库进行一个延迟处理，便于短时间快速恢复
  • 某个结点误删切换其它结点，后台恢复该结点数据

kill失效

在MySQL中有两个kill命令：一个是kill query +线程id，表示终止这个线程中正在执行的语句；一个是kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

kill并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了，可以开始“执行停止的逻辑了”。

其实，这跟Linux的kill命令类似，kill -N pid并不是让进程直接停止，而是给进程发一个信号，然后进程处理这个信号，进入终止逻辑。只是对于MySQL的kill命令来说，不需要传信号量参数，就只有“停止”这个命令。

  1. 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)；
  2. 给session B的执行线程发一个信号。

kill无效的第一类情况即：线程没有执行到判断线程状态的逻辑。跟这种情况相同的，还有由于IO压力过大，读写IO的函数一直无法返回，导致不能及时判断线程的状态。

另一类情况是，终止逻辑耗时较长。这时候，从show processlist结果上看也是Command=Killed，需要等到终止逻辑完成，语句才算真正完成。比较常见的场景有以下几种：

  1. 超大事务执行期间被kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
  2. 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待IO资源，导致耗时较长。
  3. DDL命令执行到最后阶段，如果被kill，需要删除中间过程的临时文件，也可能受IO资源影响耗时较久。
连接数据库慢的原因

每个客户端在和服务端建立连接的时候，需要做的事情就是TCP握手、用户校验、获取权限。但这几个操作，显然跟库里面表的个数无关。当使用默认参数连接的时候，MySQL客户端会提供一个本地库名和表名补全的功能。为了实现这个功能，客户端在连接成功后，需要多做一些操作。如果在连接命令中加上-A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了。

全表扫描的影响

，对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用mysql_store_result这个接口，直接把查询结果保存到本地内存。

由于MySQL采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集。所以，如果客户端读结果不及时，会堵住MySQL的查询过程，但是不会把内存打爆。

而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于InnoDB对LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。

当然，我们前面文章有说过，全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

join语句

第一个问题：能不能使用join语句？

  1. 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
  2. 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

第二个问题是：如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？

  1. 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
  2. 如果是Block Nested-Loop Join算法：
    • 在join_buffer_size足够大的时候，是一样的；
    • 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。

InnoDB和Memory引擎

InnoDB的主键索引：B+树，把数据放在主键索引上，其他索引上保存的是主键id。这种方式，我们称之为索引组织表（Index Organizied Table）

Memory引擎的数据和索引是分开的，把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）

因此，

  1. InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
  2. 当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
  3. 数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引；
  4. InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
  5. InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text字段，并且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

自增主键为什么不连续

  1. 避免回滚：在关键数据插入时，尽量避免事务回滚。

  2. 防止删除操作影响：通过逻辑删除（如设置状态字段 is_deleted = 1）替代物理删除。

  3. 禁用批量插入：减少批量插入时的失败风险。

  4. 设置自增 ID 策略：

    • 在主从复制中设置合理的 auto-increment-increment 和 auto-increment-offset。

    • 合理配置数据库重启后的 ID 分配策略。

  5. 使用 UUID 替代自增主键：

    • 在分布式系统中，使用 UUID 或 雪花算法（Snowflake） 生成全局唯一 ID，避免自增主键不连续的问题。

怎么最快复制一张表

1 逻辑导数据的方法，也就是将数据从表db1.t中读出来，生成文本，然后再写入目标表db2.t中

mysqldump方法：

使用mysqldump命令将数据导出成一组INSERT语句

导出CSV文件：

直接将结果导出成.csv文件

2 物理拷贝

MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能

  1. 执行 create table r like t，创建一个相同表结构的空表；
  2. 执行alter table r discard tablespace，这时候r.ibd文件会被删除；
  3. 执行flush table t for export，这时候db1目录下会生成一个t.cfg文件；
  4. 在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL进程要有读写权限）；
  5. 执行unlock tables，这时候t.cfg文件会被删除；
  6. 执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。

总结

  1. 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
    • 必须是全表拷贝，不能只拷贝部分数据；
    • 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
    • 由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。
  2. 用mysqldump生成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。
  3. 用select … into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

grant之后要跟着flush privileges吗？

flush privileges命令使赋权语句生效

grant命令的动作：

  1. 磁盘上，将mysql.user表里，用户’ua’@’%’这一行的所有表示权限的字段的值都修改为‘Y’；
  2. 内存里，从数组acl_users中找到这个用户对应的对象，将access值（权限位）修改为二进制的“全1”。

flush privileges命令会清空acl_users数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_users数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。

grant是即使更改表生效的，但是在比如删除用户这种不规范的操作则需要flush privileges刷新。

分区表的使用

  1. MySQL在第一次打开分区表的时候，需要访问所有的分区；
  2. 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
  3. 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

distinct和group by

如果只需要去重，不需要执行聚合函数，distinct 和group by哪种效率高一些呢？

相同。

  1. 创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引；
  2. 遍历表t，依次取数据插入临时表中：
    • 如果发现唯一键冲突，就跳过；
    • 否则插入成功；
  3. 遍历完成后，将临时表作为结果集返回给客户端。

自增ID用完怎么办

  1. 表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
  2. row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据。
  3. Xid只需要不在同一个binlog文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
  4. InnoDB的max_trx_id 递增值每次MySQL重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的bug，好在留给我们的时间还很充裕。
  5. thread_id是我们使用中最常见的，thread_id_counter定义的大小是4个字节，因此达到232-1后，它就会重置为0，然后继续增加。但是，你不会在show processlist里看到两个相同的thread_id。
CATALOG
    FEATURED TAGS
    algorithm C++ levelDB 操作系统 Blog go mysql coroutine 计算机网络 recreation
    FRIENDS