网站管理助手 phpmyadmin,襄阳网站建设开发,10000ip网站怎么做,建网站要买服务器吗文章目录 1. 为何SQL语句逻辑相同#xff0c;性能却差异巨大1.1 性能差异大的SQL语句问题1.1.1 案例一:条件字段函数操作1.1.2 案例二:隐式类型转换1.1.3 案例三:隐式字符编码转换 2. 为何只查询一行的SQL执行很慢2.1 场景一:查询长时间不返回2.1.1 等MDL锁2.1.2 等 flush2.1.… 文章目录 1. 为何SQL语句逻辑相同性能却差异巨大1.1 性能差异大的SQL语句问题1.1.1 案例一:条件字段函数操作1.1.2 案例二:隐式类型转换1.1.3 案例三:隐式字符编码转换 2. 为何只查询一行的SQL执行很慢2.1 场景一:查询长时间不返回2.1.1 等MDL锁2.1.2 等 flush2.1.2 等行锁 2.1 场景二:查询慢 3. 幻读3.1 幻读是什么3.2 幻读带来的问题3.2 如何解决幻读 1. 为何SQL语句逻辑相同性能却差异巨大
1.1 性能差异大的SQL语句问题
1.1.1 案例一:条件字段函数操作
假设你维护了一个交易系统其中交易记录表 tradelog 包含交易流水号tradeid、交易员 idoperator、交易时间t_modified等字段。为了便于描述先忽略其他字段。这个表的建表语句如下
mysql 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)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;假设现在已经记录了从 2016 年初到 2018 年底的所有数据运营部门有一个需求是要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂SQL 语句可能会这么写
mysql select count(*) from tradelog where month(t_modified)7;虽然 t_modified 字段上有索引但在生产库中执行了这条语句但却发现执行了特别久才返回了结果。因为**MySQL 规定如果对字段做了函数计算就用不上索引 **
但是为何where t_modified2018-7-1’的时候可以用上索引而改成 where month(t_modified)7 的时候就不行了
下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值 如果 SQL 语句条件用的是 where t_modified2018-7-1’的话引擎就会按照上面绿色箭头的路线快速定位到 t_modified2018-7-1’需要的结果 实际上B 树提供的这个快速定位能力来源于同一层兄弟节点的有序性。
但是如果计算 month() 函数的话会看到传入 7 的时候在树的第一层就不知道该怎么办了。也就是说对索引字段做函数操作可能会破坏索引值的有序性因此优化器就决定放弃走树搜索功能。 需要注意的是优化器并不是要放弃使用这个索引。
在这里放弃了树搜索功能优化器可以选择遍历主键索引也可以选择遍历索引 t_modified优化器对比索引大小后发现索引 t_modified 更小遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。
使用 explain 命令查看一下这条 SQL 语句的执行结果 keyt_modified表示的是使用了 t_modified 这个索引这条语句扫描了整个索引的所有值Extra 字段的 Using index表示的是使用了覆盖索引。
由于加了 month() 函数操作MySQL 无法再使用索引快速定位功能而只能使用全索引扫描。
优化器偷懒行为 即使是对于不改变有序性的函数也不会考虑使用索引。 比如对于 select * from tradelog where id 1 10000 这个 SQL 语句这个加 1 操作并不会改变有序性但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以需要在写 SQL 语句的时候手动改写成 where id 10000 -1 才可以。 1.1.2 案例二:隐式类型转换
mysql select * from tradelog where tradeid110717;交易编号 tradeid 这个字段上本来就有索引但是 explain 的结果却显示这条语句需要走全表扫描。你可能也发现了tradeid 的字段类型是 varchar(32)而输入的参数却是整型所以需要做类型转换。
数据类型转换的规则是什么 MySQL 里的转换规则在 MySQL 中字符串和数字做比较的话是将字符串转换成数字。 因此上述sql就相当于:
mysql select * from tradelog where CAST(tradid AS signed int) 110717;为什么有数据类型转换就需要走全索引扫描 对索引字段做函数操作优化器会放弃走树搜索功能。 1.1.3 案例三:隐式字符编码转换
假设系统里还有另外一个表 trade_detail用于记录交易的操作细节。为了便于量化分析和复现我往交易日志表 tradelog 和交易详情表 trade_detail 这两个表里插入一些数据。
mysql CREATE TABLE trade_detail (id int(11) NOT NULL,tradeid varchar(32) DEFAULT NULL,trade_step int(11) DEFAULT NULL, /*操作步骤*/step_info varchar(32) DEFAULT NULL, /*步骤信息*/PRIMARY KEY (id),KEY tradeid (tradeid)
) ENGINEInnoDB DEFAULT CHARSETutf8;insert into tradelog values(1, aaaaaaaa, 1000, now());
insert into tradelog values(2, aaaaaaab, 1000, now());
insert into tradelog values(3, aaaaaaac, 1000, now());insert into trade_detail values(1, aaaaaaaa, 1, add);
insert into trade_detail values(2, aaaaaaaa, 2, update);
insert into trade_detail values(3, aaaaaaaa, 3, commit);
insert into trade_detail values(4, aaaaaaab, 1, add);
insert into trade_detail values(5, aaaaaaab, 2, update);
insert into trade_detail values(6, aaaaaaab, 3, update again);
insert into trade_detail values(7, aaaaaaab, 4, commit);
insert into trade_detail values(8, aaaaaaac, 1, add);
insert into trade_detail values(9, aaaaaaac, 2, update);
insert into trade_detail values(10, aaaaaaac, 3, update again);
insert into trade_detail values(11, aaaaaaac, 4, commit);如果要查询 id2 的交易的所有操作步骤信息SQL 语句可以这么写
mysql select d.* from tradelog l, trade_detail d where d.tradeidl.tradeid and l.id2; /*语句Q1*/结果
第一行显示优化器会先在交易记录表 tradelog 上查到 id2 的行这个步骤用上了主键索引rows1 表示只扫描一行第二行 keyNULL表示没有用上交易详情表 trade_detail 上的 tradeid 索引进行了全表扫描。
这个执行计划里是从 tradelog 表中取 tradeid 字段再去 trade_detail 表里查询匹配字段。因此我们把 tradelog 称为驱动表把 trade_detail 称为被驱动表把 tradeid 称为关联字段。
explain 结果表示的执行流程 图中
第 1 步是根据 id 在 tradelog 表里找到 L2 这一行第 2 步是从 L2 中取出 tradeid 字段的值第 3 步是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里面第二行的 keyNULL 表示的就是这个过程是通过遍历主键索引的方式一个一个地判断 tradeid 的值是否匹配。
第 3 步不符合我们的预期。因为表 trade_detail 里 tradeid 字段上是有索引的本来是希望通过使用 tradeid 索引能够快速定位到等值的行。但这里并没有。
原因是因为:这两个表的字符集不同一个是 utf8一个是 utf8mb4所以做表连接查询的时候用不上关联字段的索引。
字符集 utf8mb4 是 utf8 的超集所以当这两个类型的字符串在做比较的时候MySQL 内部的操作是先把 utf8 字符串转成 utf8mb4 字符集再做比较。
所以第3步执行相当于以下语句:
select * from trade_detail where CONVERT(traideid USING utf8mb4)$L2.tradeid.value; 其中$L2.tradeid.value 的字符集是 utf8mb4。 CONVERT() 函数在这里的意思是把输入的字符串转成 utf8mb4 字符集。
对索引字段做函数操作优化器会放弃走树搜索功能。
对比验证 执行一下语句:
mysqlselect l.operator from tradelog l , trade_detail d where d.tradeidl.tradeid and d.id4;这个语句里 trade_detail 表成了驱动表但是 explain 结果的第二行显示这次的查询操作用上了被驱动表 tradelog 里的索引 (tradeid)扫描行数是 1。
此时第3步执行相当于以下语句:
select operator from tradelog where traideid CONVERT($R4.tradeid.value USING utf8mb4); CONVERT 函数是加在输入参数上的这样就可以用上被驱动表的 traideid 索引。
SQL优化思路 如果要优化语句:
select d.* from tradelog l, trade_detail d where d.tradeidl.tradeid and l.id2;比较常见的优化方法是把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4这样就没有字符集转换的问题了。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;修改 SQL 语句
mysql select d.* from tradelog l , trade_detail d where d.tradeidCONVERT(l.tradeid USING utf8) and l.id2; 2. 为何只查询一行的SQL执行很慢
构造一个表基于这个表来说明今天的问题。这个表有两个字段 id 和 c并且在里面插入了 10 万行记录。
mysql CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB;delimiter ;;
create procedure idata()
begindeclare i int;set i1;while(i100000) doinsert into t values(i,i);set ii1;end while;
end;;
delimiter ;call idata();2.1 场景一:查询长时间不返回
在表 t 执行下面的 SQL 语句
mysql select * from t where id1;查询结果长时间不返回。一般碰到这种情况的话大概率是表 t 被锁住了。
分析原因的时候一般都是首先执行一下show processlist 命令看看当前语句处于什么状态。
2.1.1 等MDL锁
使用 show processlist 命令查看 Waiting for table metadata lock 的示意图 出现这个状态表示的是现在有一个线程正在表 t 上请求或者持有 MDL 写锁把 select 语句堵住了。
这类问题的处理方式就是找到谁持有 MDL 写锁然后把它 kill 掉。
通过查询 sys.schema_table_lock_waits 这张表我们就可以直接找出造成阻塞的 process id把这个连接用 kill 命令断开即可MySQL 启动时需要设置 performance_schemaon相比于设置为 off 会有 10% 左右的性能损失)
2.1.2 等 flush
这是另外一种查询被堵住的情况。 在表 t 上执行下面的 SQL 语句
mysql select * from information_schema.processlist where id1;这个状态表示的是现在有一个线程正要对表 t 做 flush 操作。
MySQL 里面对表做 flush 操作的用法一般有以下两个
flush tables t with read lock;flush tables with read lock;这两个 flush 语句如果指定表 t 的话代表的是只关闭表 t 如果没有指定具体的表名则表示关闭 MySQL 里所有打开的表。 出现 Waiting for table flush 状态的可能情况是有一个 flush tables 命令被别的语句堵住了然后它又堵住了select 语句。
2.1.2 等行锁
mysql select * from t where id1 lock in share mode; 由于访问 id1 这个记录时要加读锁如果这时候已经有一个事务在这行记录上持有一个写锁我们的 select 语句就会被堵住。
查出是谁占着这个写锁。如果用的是 MySQL 5.7 版本可以通过 sys.innodb_lock_waits 表查到。
mysql select * from t sys.innodb_lock_waits where locked_tabletest.t\G可以看到这个信息很全4 号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式就是 KILL QUERY 4 或 KILL 4。
不过这里不应该显示“KILL QUERY 4”。这个命令表示停止 4 号线程当前正在执行的语句而这个方法其实是没有用的。因为占有行锁的是 update 语句这个语句已经是之前执行完成了的现在执行 KILL QUERY无法让这个事务去掉 id1 上的行锁。实际上KILL 4 才有效也就是说直接断开这个连接。这里隐含的一个逻辑就是连接被断开的时候会自动回滚这个连接里面正在执行的线程也就释放了 id1 上的行锁。
2.1 场景二:查询慢
mysql select * from t where c50000 limit 1;由于字段 c 上没有索引这个语句只能走 id 主键顺序扫描因此需要扫描 5 万行。
接下来再看一个只扫描一行但是执行很慢的语句。
mysql select * from t where id1通过slow log看到虽然扫描行数是 1但执行时间却长达 800 毫秒这些时间都花在哪里了
再执行select * from t where id1 lock in share mode执行时扫描行数也是 1 行执行时间是 0.2 毫秒。按理说 lock in share mode 还要加锁时间应该更长才对啊。 第一个语句的查询结果里 c1带 lock in share mode 的语句返回的是 c1000001。
复现 session A 先用 start transaction with consistent snapshot 命令启动了一个事务之后 session B 才开始执行 update 语句。session B 执行完 100 万次 update 语句后id1 这一行状态如下 session B 更新完 100 万次生成了 100 万个回滚日志 (undo log)。
带 lock in share mode 的 SQL 语句是当前读因此会直接读到 1000001 这个结果所以速度很快
而 select * from t where id1 这个语句是一致性读因此需要从 1000001 开始依次执行 undo log执行了 100 万次以后才将 1 这个结果返回。
注意undo log 里记录的其实是“把 2 改成 1”“把 3 改成 2”这样的操作逻辑画成减 1 的目的是方便看图。 思考 加锁读的时候用的是这个语句select * from t where id1 lock in share mode。由于 id 上有索引所以可以直接定位到 id1 这一行因此读锁也是只加在了这一行上。
如果是下面的 SQL 语句
begin;
select * from t where c5 for update;
commit;这个语句序列是怎么加锁的呢加的锁又是什么时候释放呢 3. 幻读
建表和初始化语句如下
CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,d int(11) DEFAULT NULL,PRIMARY KEY (id),KEY c (c)
) ENGINEInnoDB;insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);表除了主键 id 外还有一个索引 c初始化语句在表中插入了 6 行数据。
下面的语句序列是怎么加锁的加的锁又是什么时候释放的呢
begin;
select * from t where d5 for update;
commit;这个语句会命中 d5 的这一行对应的主键 id5因此在 select 语句执行完成后id5 这一行会加一个写锁而且由于两阶段锁协议这个写锁会在执行 commit 语句的时候释放。 由于字段 d 上没有索引因此这条查询语句会做全表扫描。那么其他被扫描到的但是不满足条件的 5 行记录上会不会被加锁呢 3.1 幻读是什么
如果只在 id5 这一行加锁而其他行的不加锁的话假设以下场景: session A 里执行了三次查询分别是 Q1、Q2 和 Q3。 它们的 SQL 语句相同都是 select * from t where d5 for update。查所有 d5 的行而且使用的是当前读并且加上写锁。
这三条 SQL 语句返回结果:
Q1 只返回 id5 这一行在 T2 时刻session B 把 id0 这一行的 d 值改成了 5因此 T3 时刻 Q2 查出来的是 id0 和 id5 这两行在 T4 时刻session C 又插入一行1,1,5因此 T5 时刻 Q3 查出来的是 id0、id1 和 id5 的这三行。
其中Q3 读到 id1 这一行的现象被称为“幻读”。 幻读指的是一个事务在前后两次查询同一个范围的时候后一次查询看到了前一次查询没有看到的行。
“幻读”说明
在可重复读隔离级别下普通的查询是快照读是不会看到别的事务插入的数据的。因此幻读在“当前读”下才会出现。上面 session B 的修改结果被 session A 之后的 select 语句用“当前读”看到不能称为幻读。幻读仅专指“新插入的行”。
这三个查询都是加了 for update都是当前读。而当前读的规则就是要能读到所有已经提交的记录的最新值
3.2 幻读带来的问题
首先是语义上的语义被破坏了。破坏了加锁的申明数据一致性的问题。锁的设计是为了保证数据的一致性。这个一致性不止是数据库内部数据状态在此刻的一致性还包含了数据和日志在逻辑上的一致性
即使把所有的记录都加上锁还是阻止不了新插入的记录
3.2 如何解决幻读
为了解决幻读问题InnoDB 只好引入新的锁也就是间隙锁 (Gap Lock)。
间隙锁锁的就是两个值之间的空隙。开头的表 t初始化插入了 6 个记录这就产生了 7 个间隙。 执行 select * from t where d5 for update 的时候就不止是给数据库中已有的 6 个记录加上了行锁还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。
间隙锁跟之前碰到过的锁都不太一样。 比如行锁分成读锁和写锁。 跟行锁有冲突关系的是“另外一个行锁”。
但是间隙锁不一样跟间隙锁存在冲突关系的是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
间隙锁和行锁合称 next-key lock每个 next-key lock 是前开后闭区间。 也就是说表 t 初始化以后如果用 select * from t for update 要把整个表所有记录锁起来就形成了 7 个 next-key lock分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, supremum]。
间隙锁和 next-key lock 的引入帮我们解决了幻读的问题但同时也带来了一些“困扰”。 业务逻辑这样的任意锁住一行如果这一行不存在的话就插入如果存在这一行就更新它的数据代码如下
begin;
select * from t where idN for update;/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set dN set idN;commit;这个逻辑一旦有并发就会碰到死锁。
用两个 session 来模拟并发并假设 N9。 按语句执行顺序来分析一下
session A 执行 select … for update 语句由于 id9 这一行并不存在因此会加上间隙锁 (5,10);session B 执行 select … for update 语句同样会加上间隙锁 (5,10)间隙锁之间不会冲突因此这个语句可以执行成功session B 试图插入一行 (9,9,9)被 session A 的间隙锁挡住了只好进入等待session A 试图插入一行 (9,9,9)被 session B 的间隙锁挡住了。
至此两个 session 进入互相等待状态形成死锁。
间隙锁的引入可能会导致同样的语句锁住更大的范围这其实是影响了并发度的。 间隙锁是在可重复读隔离级别下才会生效的。如果把隔离级别设置为读提交的话就没有间隙锁了。
来自林晓斌 《MySql实战45讲》
