MYSQL实战45讲

发表于 | 分类于 | 次

MYSQL实战45讲

第1讲 一条SQL查询语句是如何执行的？

1.Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务

功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在

这一层实现，比如存储过程、触发器、视图等。

2.而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、

Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了

默认存储引擎。

第2讲 日志系统：一条SQL更新语句是如何执行的？

WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，redo log（重做日志）和 binlog（归档日志）。如果接触MySQL，那这两个词肯定是绕不过 的，我后面的内容里也会不断地和你强调。

Redo log和binlog的两阶段提交

redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

第3讲 事务隔离：为什么你改了我还看不见？

查询长事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

第4讲 深入浅出索引（上）

InnoDB采用的B+树结构，以及为什么 InnoDB要这么选择。

B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

第5讲 深入浅出索引（下）

覆盖索引、最左前缀原则、索引下推

第6讲 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

全局锁主要用在逻辑备份过程中。对于全部是InnoDB引擎的库，我建议你选择使用–single-

transaction参数，对应用会更友好。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock

tables这样的语句，你需要追查一下。

要么是你的系统现在还在用MyISAM这类不支持事务的引擎，那要安排升级换引擎；

要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把lock

tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

第7讲 行锁功过：怎么减少行锁对性能的影响？

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

第8讲 事务到底是隔离的还是不隔离的？

InnoDB的行数据有多个版本，每个数据版本有自己的rowtrx_id，每个事务或者语句有自己的一 致性视图。普通查询语句是一致性读，一致性读会根据rowtrx_id和一致性视图确定数据版本的 可见性。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；

对于读提交，查询只承认在语句启动前就已经提交完成的数据；

而当前读，总是读取已经提交完成的最新版本。

第9讲 普通索引和唯一索引，应该怎么选择？

普通索引会使用Change buffer，唯一索引不会。

InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

innodb_change_buffer_max_size来动态设置

第10讲 MYSQL为什么回选错索引？

analyze table t 命令，可以用来重新统计索引信息

第11讲 怎么给字符串字段加索引？

1
2
3
4
5
6
7
8
9
10
select 
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;

创建前缀索引的区分度损失比例。比如5%，则选择 >= L * 95 %

注意：使用前缀索引就用不上覆盖索引对查询性能的优化

第12讲 为什么我的MYSQL会抖一下？

可能是在进行flush操作，刷脏页

innodb_io_capacity参数，它会告诉InnoDB你的磁盘能力。这个值我建议你设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试。

1
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -n

InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。

合理地设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75%.

1
2
3
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'; 
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

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

数据删除流程导致表数据页空洞

重建表

推荐用GitHub开源的gh-ost来做。

第14讲 count(*)这么慢，我该怎么办？

count(字段)<count(主键id)<count(1)≈count()，所以我建议你，尽量使用count(\)

第15讲 答疑文章（一）日志和索引相关问题

A和B相互关注的问题

比较A和B的大小，如果A<B

1
2
3
4
5
6
7
begin; 
/*启动事务*/ 
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B; 
/*代码中判断返回的 relation_ship， 如果是1，事务结束，执行 commit 如果是3，则执行下面这两个语句： */ 
insert ignore into friend(friend_1_id, friend_2_id) values(A,B); 
commit;

如果A>B

1
2
3
4
5
6
7
begin; 
/*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A; 
/*代码中判断返回的 relation_ship， 如果是2，事务结束，执行 commit 如果是3，则执行下面这两个语句： */
insert ignore into friend(friend_1_id, friend_2_id) values(B,A); 
commit;

第16讲 “order by”是怎么工作的？

确定一个排序语句是否使用了临时文件。

1
2
3
4
5
6
7
8
9
10
11
12
/* 打开optimizer_trace，只对本线程有效 */ 
SET optimizer_trace='enabled=on';
/* @a保存Innodb_rows_read的初始值 */ 
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */ 
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */ 
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G 
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read'; 
/* 计算Innodb_rows_read差值 */
select @b-@a;

MYSQL优先全字段排序> rowid排序

第17讲 “order by”是怎么工作的？

内存临时表

order by rand()使用了内存临时表，内存临时表排序的时候使用的是rowid排序方法。

归并排序 => 结果全部有序

磁盘临时表

优先队列排序算法 => 小根堆

随机排序算法

第18讲 为什么这些SQL语句逻辑相同，性能却差异巨大？

1
2
3
4
5
6
7
8
9
CREATE TABLE `tradelog`(
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`),
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

第19讲 为什么我只查一行的语句，也执行这么慢？

第一类：查询长时间不返回

​ 等MDL锁

​ 通过查询sys.schema_table_lock_waits这张表，直接找出造成阻塞的process id。

​ 等flush

​ 等行锁

​ select * from t sys.innodb_lock_waits where locked_table='test'.'t'\G

第20讲 幻读是什么，幻读有什么问题？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)

第21讲 为什么我只改一行的语句，锁这么多？

两个原则、两个优化、一个bug

原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

第22讲 MYSQL有哪些“饮鸩止渴”提高性能的方法？

慢查询

存储过程实现，语句改写规则

1
2
3
insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1"); 

call query_rewrite.flush_rewrite_rules();

第23讲 MySQL是怎么保证数据不丢的？

只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复。

第24讲 MySQL是怎么保证主备一致的？

mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，

否则就用statement格式。

第25讲 MySQL是怎么保证高可用的？(浅了解)

主备延迟：机器差、备库压力大、大事务、备库的并行复制能力

数据库运维系统都有备库延迟监控，其实就是在备库上执行 showslave status，采集seconds_behind_master的值

第26讲 备库为什么会延迟好几个小时？(浅了解)

从这些分析中，你也会发现大事务不仅会影响到主库，也是造成备库复制延迟的主要原因之一。

因此，在平时的开发工作中，我建议你尽量减少大事务操作，把大事务拆成小事务。

第27讲 主库出问题了从库怎么办？(浅了解)

如果你使用的MySQL版本支持GTID的话，我都建议你尽量使用GTID模式来做一主多从的切换。

第28讲 读写分离有哪些坑？

在实际应用中，这几个方案是可以混合使用的。

比如，先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期

读；然后，对于不能接受过期读的语句，再使用等GTID或等位点的方案。

第29讲 如何判断一个数据库是不是出问题了？

select 1这样的方法是不是已经被淘汰了呢，但实际上使用非常广泛的MHA（MasterHigh Availability），默认使用的就是这个方法。

其实，每个改进的方案，都会增加额外损耗，并不能用“对错”做直接判断，需要你根据业务实际情况去做权衡。

我个人比较倾向的方案，是优先考虑update系统表，然后再配合增加检测performance_schema 的信息。

第30讲 答疑文章（二）：用动态的观点看加锁

介绍了怎么看showengine innodb status输出结果中的事务信息和死锁信息。

第31讲 误删数据后除了跑路，还能怎么办？

删行

使用delete语句误删了数据行，可以用Flashback工具通过Flashback把数据恢复回来。

Flashback恢复数据的原理，是修改binlog的内容，拿回原库重放。而能够使用这个方案的前提

是，需要确保binlog_format=row和 binlog_row_image=FULL。

删库

假如有人中午12点误删了一个库，恢复数据的流程如下：

\1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天0点；

\2. 用备份恢复出一个临时库；

\3. 从日志备份里面，取出凌晨0点之后的日志；

\4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

预防误删库/表

账号分离、必须先对表做改名操作

第32讲 为什么还有kill不掉的语句？

客户端连接 -A关闭自动补全

我们感知的慢，不是服务端慢，而是客户端慢。

–quick参数的意思，是让客户端变得更快。

第33讲 我查这么多数据会不会把数据库内存打爆？

MYSQL是边读边发数据给客户端。

“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。

执行showengine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率。

InnoDB内存管理用的是最近最少使用 (Least RecentlyUsed, LRU)算法，这个算法的核心就是淘汰最久未使用的数据。

LRU算法的操作逻辑：

\1. 扫描过程中，需要新插入的数据页，都被放到old区域;

\2. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；

\3. 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是young区域），很快就会被淘汰出去。

这个策略最大的收益，就是扫描这个大表的过程中，虽然也用到了Buffer Pool，但是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率

第34讲 到底可不可以使用join？

这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“IndexNested-Loop Join”

简称NLJ。前提是“可以使用被驱动表的索引”。

“Block Nested-Loop Join”，特点为被驱动表上没有可用的索引。

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

什么是小表？

过滤条件后select的数据条数少的。

第35讲 join语句怎么优化？

在这些优化方法中：

\1. BKA优化是MySQL已经内置支持的，建议你默认使用；

\2. BNL算法效率低，建议你都尽量转成BKA算法。优化的方向就是给被驱动表的关联字段加上索引；

\3. 基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的；4. MySQL目前的版本还不支持hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

哈希join 在业务端实现。

第36讲 为什么临时表可以重名？

临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。

第37讲 什么时候会使用内部临时表？

了group by的几种实现算法，从中可以总结一些使用的指导原则：

\1. 如果对group by语句的结果没有排序要求，要在语句后面加 order bynull；

\2. 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary和 Using filesort；

\3. 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

\4. 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

第38讲 都说InnoDB好那还要不要使用Memory引擎？

如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双M架构，还可能导致主库的内存表数据被删掉。

就是内存临时表。内存表支持hash索引，这个特性利用起来，对复杂查询的加速效果还是很不错的。

主库暂时不能修改引擎，那么就把备库的内存表引擎先都改成InnoDB。

第39讲 自增主键为什么不是连续的？

在MyISAM引擎里面，自增值是被写在数据文件上的。而在InnoDB中，自增值是被记录在内存的。MySQL直到8.0版本，才给InnoDB表的自增值加上了持久化的能力，确保重启前后一个表的自增值不变。

第40讲 insert语句的锁为什么这么多？

insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的next-key lock(S锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

第41讲 怎么最快地复制一张表？

物理拷贝

mysqldump

select …into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就

是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

第42讲 grant之后要跟着flush privileges吗？

grant语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用grant和revoke语句，是不需要随后加上flush privileges语句的。

flush privileges语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用DML语句操作系统权限表导致的，所以

我们尽量不要使用这类语句。

第43讲 要不要使用分区表？

有两个问题需要注意：

\1. 分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。

\2. 分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的12个新分区创建上即可。对于没有数据的历史分区，要及时的drop掉。

第44讲 答疑文章（三）：说一说这些好问题

第45讲 自增id用完怎么办？