几个影响sql语句性能的例子
案例一:假设你现在维护了一个交易系统,其中交易记录表tradelog包含交易流水号(tradeid)、交易员id(operator)、交易时间(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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
假设,现在已经记录了从2016年初到2018年底的所有数据,运营部门有一个需求是,要统计发生在所有年份中7月份的交易记录总数。这个逻辑看上去并不复杂,你的SQL语句可能会这么写:
mysql> select count(*) from tradelog where month(t_modified)=7;
由于t_modified字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。
如果对字段做了函数计算,就用不上索引了,这是MySQL的规定。
可以再追问一句为什么?为什么条件是where t_modified='2018-7-1’的时候可以用上索引,而改成where month(t_modified)=7的时就不行了?
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜
索功能。需要注意的是,优化器并不是要放弃使用这个索引。在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引t_modified,优化器对比索引大小后发现,索引t_modified更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引t_modified。
Extra字段的Using index,表示的是使用了覆盖索引。也就是说,由于在t_modified字段加了month()函数操作,导致了全索引扫描。
按照下面这个写法,优化器就能按照我们预期的,用上t_modified索引的快速定位能力了。
mysql> select count(*) from tradelog where
‑> (t_modified >= '2016‑7‑1' and t_modified<'2016‑8‑1') or
‑> (t_modified >= '2017‑7‑1' and t_modified<'2017‑8‑1') or
‑> (t_modified >= '2018‑7‑1' and t_modified<'2018‑8‑1');
案例二:隐式类型转换
我们一起看一下这条SQL语句:
mysql> select * from tradelog where tradeid=110717;
交易编号tradeid这个字段上,本来就有索引,但是explain的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid的字段类型是varchar(32),而输入的参数却是整型,所以需要做类型转换。
数据类型转换的规则是什么
- 在MySQL中,字符串和数字做比较的话,是将字符串转换成数字。
为什么有数据类型转换,就要走全索引扫描。
以上sql语句相当于
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。
同样的例子比如:,字符集utf8mb4是utf8的超集,所以当这两个类型的字
符串在做比较的时候,MySQL内部的操作是,先把utf8字符串转成utf8mb4字符集,再做比较。这个设定很好理解,utf8mb4是utf8的超集。类似地,在程序设计语言里面,做自动类型转换的时候,为了避免数据在转换过程中由于截断导致数据错误,也都
是“按数据长度增加的方向”进行转换的。也就是说,实际上这个语句等同于下面这个写法:
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
CONVERT()函数,在这里的意思是把输入的字符串转成utf8mb4字符集。这就再次触发了我们上面说到的原则:对索引字段做函数操作,优化器会放弃走树搜索功能。
小结:
- 对于给不同年份中的月份加索引,我们要指定年份才能走索引,因为不同年份可能会有相同的月份,mysql优化器不知如何选择,不会走指定字段索引。
- 保持字段类型一致,比如字段和数字,mysql会自动的调用函数将字符串转化为数字,会走全表扫描。
- 保持字符集一致,charset=utf8,如果跨表查询,两张表的字符集应该相同,否则mysql内部还是会调用函数,因此会走全表扫描。
- 在每次代码升级的时候我们都应该调用explain来看一下sql语句是否按索引来走的。
- 调用函数就不会走我们已经建好的索引字段。
查一行的sql语句执行很慢
第一类 查询长时间不返回
mysql> select * from t where id=1;
执行show processlist命令,查看当前语句处于什么状态。
等释放
-
- 可以看到这个语句目前的状态是waiting for table metadata lock,表示有一个线程正在表t上请求或者持有MDL写锁,把select语句堵住了
等行锁
mysql> select * from t where id=1 lock in share mode;
由于访问id=1这个记录时要加读锁,如果这时候已经有一个事务在这行记录上持有一个写锁,我们的select语句就会被堵住。
显然,session A启动了事务,占有写锁,还不提交,是导致session B被堵住的原因。
第二类:查询慢
mysql> select * from t where c=50000 limit 1;
- 由于字段c上没有索引,这个语句只能走id主键顺序扫描,因此需要扫描5万行。
mysql> select * from t where id=1; # 假如这条语句慢查询日志返回的是800毫秒
select * from t where id=1 lock in share mode # 这条语句返回的查询时间是0.2毫秒
为什么结果相差会这么大呢?
- session A先用start transaction with consistent snapshot命令启动了一个事务,之
后session B才开始执行update 语句。 - session B执行完100万次update语句后,id=1这一行处于什么状态呢?
- session B更新完100万次,生成了100万个回滚日志(undo log)。
带lock in share mode的SQL语句,是当前读,因此会直接读到1000001这个结果,所以速度很快;而select * from t where id=1这个语句,是一致性读,因此需要从1000001开始,依次执行undo log,执行了100万次以后,才将1这个结果返回。所以返回查询时间很长