文章目录
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 杂坛]“!