说说 SQL 优化之道

小灰灰 2020-05-01 19:36 883阅读 0赞

# 说说 SQL 优化之道

## 一些常见的 SQL 实践

- 负向条件查询不能使用索引

```mysql
select from order where status!=0 and status!=1
```

```mysql
not in/not exists # 都不是好习惯
```

可以优化为 `in` 查询：

```mysql
select from order where status in(2,3)
```

- 前导模糊查询不能使用索引

```mysql
select from order where desc like '%XX'
```

而非前导模糊查询则可以：

```mysql
select from order where desc like 'XX%'
```

- 数据区分度不大的字段不宜使用索引

```mysql
select from user where sex=1
```

原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。

经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。

- 在属性上进行计算不能命中索引

```mysql
select from order where YEAR(date) < = '2017'
```

即使date上建立了索引，也会全表扫描，可优化为值计算：

```mysql
select from order where date < = CURDATE()
```

或者：

```mysql
select from order where date < = '2017-01-01'
```

## 并非周知的 SQL 实践

- 如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心

```mysql
select from user where uid=?
select from user where login_name=?
```

原因：`B-Tree` 索引的时间复杂度是 `O(log(n))`；`Hash` 索引的时间复杂度是 `O(1)`

- 允许为 `null` 的列，查询有潜在大坑

单列索引不存 `null` 值，复合索引不存全为 `null` 的值，如果列允许为 `null`，可能会得到“不符合预期”的结果集

```mysql
select from user where name != 'shenjian'
```

如果 `name` 允许为 `null`，索引不存储 `null` 值，结果集中不会包含这些记录。所以，请使用 `not null` 约束以及默认值。

- 复合索引最左前缀，并不是值 SQL 语句的 `where` 顺序要和复合索引一致

用户中心建立了(login_name, passwd)的复合索引

```mysql
select from user where login_name=? and passwd=?
select from user where passwd=? and login_name=?
```

都能够命中索引

```mysql
select from user where login_name=?
```

也能命中索引，满足复合索引最左前缀

```mysql
select from user where passwd=?
```

不能命中索引，不满足复合索引最左前缀

- 使用 `ENUM` 而不是字符串

`ENUM` 保存的是 `TINYINT`，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串，字符串空间又大，效率又低。

## 小众但有用的 SQL 实践

- 如果明确知道只有一条结果返回，`limit 1` 能够提高效率

```mysql
select from user where login_name=?
```

可以优化为：

```mysql
select from user where login_name=? limit 1
```

原因：你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动

- 把计算放到业务层而不是数据库层，除了节省数据的 CPU，还有意想不到的查询缓存优化效果

```mysql
select from order where date < = CURDATE()
```

这不是一个好的SQL实践，应该优化为：

```mysql
$curDate = date('Y-m-d');
$res = mysqlquery('select from order where date < = $curDate');
```

原因：释放了数据库的 CPU，多次调用，传入的SQL相同，才可以利用查询缓存

- 强制类型转换会全表扫描

```mysql
select from user where phone=13800001234
```

你以为会命中 phone 索引么？大错特错了，这个语句究竟要怎么改？

末了，再加一条，不要使用 `select *（潜台词，文章的 SQL 都不合格 ==）`，只返回需要的列，能够大大的节省数据传输量，与数据库的内存使用量哟。

发表评论取消回复

表情：

评论列表（有 0 条评论，883人围观）

还没有评论，来说两句吧...

相关阅读

相关 SQL代码整理之道

、在工作中，见到好多人在写SQL代码时，都是一长串字符打天下，很难看到其结构，也就读懂其句意。今天我把我的一点习惯性经验总结下，不全面，就当是随笔经验吧。二、主要是三...

我就是我/ 2024年04月18日 22:30/ 0 赞/ 25 阅读

相关金融系统性能优化之道

系统设计得再好，如不能及时完成业务处理也不行。为什么不同业务有不同优化需求，以及常见的优化方式和问题有哪些。 1 分析 “快”在不同的环境下有不同的定义：对互

阳光穿透心脏的1/2处/ 2024年03月30日 13:26/ 0 赞/ 67 阅读

相关 MySQL 之 SQL优化

没时间的可以直接跳到最后的总结文章目录 1 插入数据 1.1 insert 1.2 大批量插入数据

野性酷女/ 2024年03月26日 22:15/ 0 赞/ 98 阅读

相关 SQL优化之道：提升日常工作效率的技巧

SQL优化之道：提升日常工作效率的技巧文章目录 SQL优化之道：提升日常工作效率的技巧引言分析查询性能问题

亦凉/ 2024年03月24日 21:17/ 0 赞/ 47 阅读

相关 SQL之SQL优化

文章目录一、插入数据优化 insert优化大批量插入数据二、主键优化数据组织方式 ·页分裂

冷不防/ 2023年10月09日 17:19/ 0 赞/ 9 阅读

相关 MySQL之SQL优化

1. SQL优化的步骤 1. 发现有性能问题的SQL； 2. 分析SQL执行计划； 3. 优化索引； 4. 改写SQL； 2. 发现有性能问题的SQL

青旅半醒/ 2023年07月18日 12:52/ 0 赞/ 3 阅读

相关 Sql优化之道：一，总览思维导图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhp

缺乏、安全感/ 2022年12月04日 01:26/ 0 赞/ 178 阅读

相关海量之道之弱联网优化

前言【弱联网优化】作为海量之道2.0系列课题的基础方法论之一，望文生义，想必定是卖弄移动网络信号不佳时如何优化的奇技淫巧。恭喜你，感觉靠谱，不过我们还想多聊一点其它话题

﹏ヽ暗。殇╰゛Y/ 2022年05月24日 14:20/ 0 赞/ 183 阅读

相关性能优化之道

性能优化之道，绝大部分在于代码也。软件开发有好几个层次：架构、设计、实现。架构是最高层面，设计次之，架构和设计不合适，性能再怎么调，总体也好不到哪里去。但对于一般程序员

拼搏现实的明天。/ 2022年02月28日 11:28/ 0 赞/ 207 阅读

相关说说 SQL 优化之道

说说 SQL 优化之道一些常见的 SQL 实践 - 负向条件查询不能使用索引

小灰灰/ 2020年05月01日 19:36/ 0 赞/ 884 阅读