先来给 MySQL 中的 sql 定义个量变
慢 SQL 对数据库的影响,是一个量变到质变的过程,对“量”的把握,就很重要
一台单机 MySQL 数据库,大致处理能力的极限是,每秒一万条左右的简单 SQL,这里的“简单 SQL”,指的是类似于主键查询这种不需要遍历很多条记录的 SQL,这里的一万 TPS 是中位数的经验值,具体还是要看咋们的服务器是否给力。
一般一台 MySQL 服务器,平均每秒钟执行的 SQL 数量在几百左右,就已经是非常繁忙了,即使看起来 CPU 利用率和磁盘繁忙程度没那么高,你也需要考虑给数据库“减负”了。
遍历行数在千万左右,是 MySQL 查询的一个坎儿。MySQL 中单个表数据量,也要尽量控制在一千万条以下,最多不要超过二三千万这个量级。
对一个千万级别的表执行查询,加上几个 WHERE 条件过滤一下,符合条件的数据最多可能在几十万或者百万量级,这还可以接受。但如果再和其他的表做一个联合查询,遍历的数据量很可能就超过千万级别了。所以,每个表的数据量最好小于千万级别。
所以我们在做一些平台级别的产品时,普遍量都比较大,再加上微服务架构的拆服务重构问题,都不建议直接用join进行连表查询,在阿里的JAVA某公开手册中是有明确规定禁止使用join连表的,个人也不建议疯狂的秀sql语句,一个查询写了一堆嵌套的sql,这也不便于后人维护和优化。
如果数据库中的数据量就是很多,而且查询业务逻辑就需要遍历大量数据怎么办?
很简单,加上 “必要的且合理的” 索引即可。
为什么这里是 “必要的且合理的” ,这里就要说到索引的原理和使用方法及其优化方案,篇幅问题,我会在另外文章再来记录。
如何查看自己写的sql性能的可能性
大部分就是查看sql是否命中索引以及命中索引的类型和filter
逻辑不是很复杂的单表查询,我们可能还可以分析出来,查询会使用哪个索引。但如果是比较复杂的多表联合查询,我们单看 SQL 语句本身,就很难分析出查询到底会命中哪些索引,会遍历多少行数据。MySQL 和大部分数据库,都提供一个帮助我们分析查询功能:执行计划。
分析 SQL 执行计划:
只要在你的 SQL 语句前面加上 EXPLAIN 关键字,然后执行这个查询语句就可以了。
举个栗子,有一个用户表,包含用户 ID、姓名、部门编号和状态这几个字段:
我们现在按照 部门代号以 00028 开头的所有人。
下面这两个 SQL,他们的查询结果是一样的,都满足要求,但是,哪个查询性能更好呢?
SELECT * FROM user WHERE left(department_code, 5) = '00028';
SELECT * FROM user WHERE department_code LIKE '00028%';
所以来看看分析计划吧:
执行:
explain SELECT * FROM user WHERE left(department_code, 5) = ‘00028’;
如上图中看见(可能需要你科学上网才能看见图片吧):
Left 是Mysql自由函数,type显示ALL,没有命中索引走的是全表查询,跑了4534行,最垃圾的效果。
而Like 虽然type是range 走的范围索引类型,但别人至少命中索引了,rows显示只跑了8行搞定。
通过对比这两个 SQL 的执行计划,就可以看出来,第二个 SQL 虽然使用了普遍认为低效的 LIKE 查询条件,但是仍然可以用到索引的范围查找,遍历数据的行数远远少于第一个 SQL,查询性能更好。
explain sql 出来的字段表示具体含义
这里主要注意,title中的type字段含义:
type 表示显示联结类型,显示查询使用了何种类型有如下:
下面有好到坏的排序:
- system
- const
- eq_ref
- ref
- fulltext
- ref_or_null
- index_merge
- unique_subquery
- index_subquery
- range
- index
- ALL
这里就不再多参数了,可以去官网和一些分享文档中自行查看即可:
参考:
总结:
在写db层sql语句时,你应该有意识的去扫一下是否用到了索引,大致预估下能跑多少行。
尽量针对业务写出效率较高的查询语句。比如:
where 最左原则的过滤字段顺序、
where 中填入的值是否已经代码转化好了对应表的数据类型(如果让数据库给你转化的话就可能不会迟到索引,即便该字段你加了index)
order by 字段是否有必要,是否走的内存排序还是临时表排序、
select 是否可以走个覆盖索引、