今天前同事问了一个sql语法优化问题,后来查资料的过程中,发现了一个prepare statement 这玩意。使用并测试了下性能,感觉未来能用上。记录下。
当时我在解决一个大表,分页问题。目前的分页sql一般有这几种(原文):
--方法1: 直接使用数据库提供的SQL语句
---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N
---适应场景: 适用于数据量较少的情况(元组百/千级)
---原因/缺点: 全表扫描,速度会很慢。
---方法2: 基于索引再排序
---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
---适应场景: 适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一所以,使得ORDERBY操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)
---原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC,期待...).
---方法3: 基于索引使用prepare(第一个问号表示pageNum,第二个?表示每页元组数)
---语句样式: MySQL中,可用如下方法: PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (?* ?) ORDER BY id_pk ASC LIMIT M
---适应场景: 大数据量
---原因: 索引扫描,速度会很快. prepare语句又比一般的查询语句快一点。
那么为啥prepare会快一点呢?先不急,我们先了解下,sql执行的过程,mysql服务器都做了哪些事?
SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。词法分析->语法分析这两个阶段我们称之为硬解析。
词法分析识别sql中每个词,语法分析解析SQL语句是否符合sql语法,并得到一棵语法树(Lex)。对于只是参数不同,其他均相同的sql,它们执行时间不同但硬解析的时间是相同的。
Prepare的出现就是为了优化硬解析的问题。
本来执行两条sql查询:
select * from a where id>1
select * from a where id>10
按执行顺序:词法分析->语法分析->语义分析->执行计划优化->执行 。执行两遍。
如果使用Prepare,节约硬解析时间。那么同样执行上面的sql
prepare first_ prepare from 'select * from a where id>(?)';
其中?可以传入不同参数
按执行顺序:第一次:词法分析->语法分析->语义分析->执行计划优化->执行 。
第二次:语义分析->执行计划优化->执行 。
节约了一次硬解析时间。
Prepare在execute阶段可以节省硬解析的时间。如果sql只执行一次,且以prepare的方式执行,那么sql执行需两次与服务器交互(Prepare和execute), 而以普通(非prepare)方式,只需要一次交互。这样使用prepare带来额外的网络开销,可能得不偿失。我们再来看同一sql执行多次的情况,比如以prepare方式执行10次,那么只需要一次硬解析。这时候 额外的网络开销就显得微乎其微了。因此prepare适用于频繁执行的SQL。
除了适用月需要频繁执行的SQL,还有一点也是需要注意的。
sql越简单硬解析的时间就越少,prepare的提升就越少。
综上,prepare 语法适用频繁执行且复杂的SQL。