几个影响sql性能语句的例子

几个影响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字符集。这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能。
小结：
1. 对于给不同年份中的月份加索引，我们要指定年份才能走索引，因为不同年份可能会有相同的月份，mysql优化器不知如何选择，不会走指定字段索引。
2. 保持字段类型一致，比如字段和数字，mysql会自动的调用函数将字符串转化为数字，会走全表扫描。
3. 保持字符集一致，charset=utf8，如果跨表查询，两张表的字符集应该相同，否则mysql内部还是会调用函数，因此会走全表扫描。
4. 在每次代码升级的时候我们都应该调用explain来看一下sql语句是否按索引来走的。
5. 调用函数就不会走我们已经建好的索引字段。

查一行的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这个结果返回。所以返回查询时间很长

posted @ 2019-06-29 10:39 Jimmyhe 阅读(2041) 评论(0) 编辑收藏举报

刷新页面返回顶部

To Be survivor

是日，关山难越。他日，一览众山。

几个影响sql性能语句的例子

几个影响sql语句性能的例子

查一行的sql语句执行很慢

公告