文章目录
Mysql 数据库优化秘籍
“ SQL优化在提升系统性能中扮演着举足轻重的角色，已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注。如果是你，在这个问题上能吊打面试官还是会被吊打呢？”
调优原则 — 倒金字塔
数据库优化是个亘古存在的问题，在数据库角度可以分成 SQL/索引优化、表结构优化、系统配置优化、硬件优化，四层倒金字塔结构。
图片
更多细节请关注 公众号 “ [code 杂坛]“！
金字塔结构中各级优化成本逐级递增，效果逐级递减！“SQL&索引层“ 和 ”表结构层“ 与程序设计实现强相关，ROI[投资回报率]最高。
故，调优涉及通常更多的是在 “SQL&索引层“ 和 ”表结构层“ 做调整。
SQL 语法/执行顺序
理解SQL优化原理之前 ，首先要搞清楚SQL一些重要的顺序。
2.1 语法顺序
在业务实现中，良好的 SQL 语法书写习惯在代码易读和执行效率上至关重要。
- SELECT
- DISTINCT <select_list>
- FROM <left_table>
- <join_type> JOIN <right_table>
- ON <join_condition>
- WHERE <where_condition>
- GROUP BY <group_by_list>
- HAVING <having_condition>
- ORDER BY <order_by_condition>
10.LIMIT <limit_number>
2.2 执行顺序
理解 SQL 的执行顺序是深入 SQL 执行原理的基础，更是调优的切入口。
更多细节请关注 公众号 “ [code 杂坛]“！
FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN<join, left join, right join…>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>
SQL 优化策略
当我们数据量大的时候，我们的SQL执行效率往往不是那么可观，虽然结果也执行的出来SQL也是正确的，但是有的时候我们不得不更改原来的SQL以提升性能。
更多细节请关注 公众号 “ [code 杂坛]“！
就像排序算法一样，逻辑简单的冒泡也能实现功能，但是数据量大的时候执行效率就非常的差，而我们的快速排序可以出色的解决我们的需求。
我们需要熟悉和利用一些特殊的策略去提升SQL执行效率。
3.1 索引未生效/利用
- 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE username LIKE ‘%陈%’
优化方式：尽量在字段后面使用模糊查询。如下：
SELECT * FROM t WHERE username LIKE ‘陈%’
2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如下：
SELECT * FROM t WHERE id IN (2,3)
如果是子查询，可以用exists代替。
– 不走索引
select * from A where A.id in (select id from B);
– 走索引
select * from A where exists (select * from B where B.id = A.id
3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：
更多细节请关注 公众号 “ [code 杂坛]“！
SELECT * FROM t WHERE id = 1O or id = 3
优化方式：可以用union代替or。如下：
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3
4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE score IS NULL
优化方式：可以给字段添加默认值0，对0值进行判断。如下：
SELECT * FROM t WHERE score = 0
5. 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。可以将表达式、函数操作移动到等号右侧。如下：
– 全表扫描
SELECT * FROM T WHERE score/10 = 9
– 走索引
SELECT * FROM T WHERE score = 10*9
6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：
SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加and。
- 隐式类型转换造成不使用索引。如下，SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
select col1 from table where col_varchar=123;
3.2 Select级别调优
- 避免出现select *
首先，select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。
使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。
建议提出业务实际需要的列数，将指定列名以取代select *。
- 避免出现不确定结果的函数
更多细节请关注 公众号 “ [code 杂坛]“！
特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。
3.多表关联查询时，小表在前，大表在后。
在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
4.调整Where字句中的连接顺序
MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
3.3 Create级别调优
-
在表中建立索引，优先考虑where、order by使用到的字段。
-
尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。 -
查询数据量大的表 会造成查询缓慢。
更多细节请关注 公众号 “ [code 杂坛]“！
主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,*
FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050
4. 用varchar/nvarchar 代替 char/nchar
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
常用的策略还有 DML级别调优、数据库分库分表等，篇幅有限暂不赘述！
推荐阅读：
｜Kafka 高吞吐、高性能核心技术及最佳应用场景…
｜三行代码搞定 —— 反转链表…
｜百度信息流和搜索双引擎业务中的 KV 存储实践…
｜HTTPS 如何保证数据传输安全 —— TLS 协议…
| 五分钟搭建基于 Prometheus + Grafana 实时监控系统…
更多细节请关注 公众号 “ [code 杂坛]“！
扫一扫
11万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
