背景介绍
运维反馈生产环境定时任务管理界面查询速度太慢，经过定位发现，是SQL查询速度太慢导致的，经过定位发现出有以下SQL数据查询过慢
SELECT t.id, t.job_group, t.job_id, t.executor_address, t.executor_handler, t.executor_param, t.executor_sharding_param, t.executor_fail_retry_count, t.trigger_time, t.trigger_code, t.trigger_msg, t.handle_time, t.handle_code, t.handle_msg, t.alarm_status, jgroup.app_name
FROM job_log AS t LEFT join job_group AS jgroup on t.job_group = jgroup.id WHERE t.is_deleted != 1 ORDER BY t.trigger_time DESC LIMIT 10 offset 0;
SELECT id FROM job_log WHERE ( trigger_code not in (0, 200) or handle_code = 500 ) AND `alarm_status` = 0 AND is_deleted != 1 ORDER BY id ASC LIMIT 1000;
SELECT t.id FROM job_log AS t WHERE t.handle_code = 0 and t.is_deleted != 1 and t.trigger_code = 200 and t.trigger_time <= TIMESTAMP '2023-02-07 15:00:00' and t.executor_address not in (SELECT t2.registry_value FROM job_registry AS t2 where t2.is_deleted != 1)；
SELECT count(1) FROM job_log;
数据库表当时的索引只有job_log表有两个索引，分别是
CREATE INDEX I_trigger_time ON job_log (trigger_time);
CREATE INDEX I_handle_code ON job_log (handle_code);
当时job_log的表中的数据量大概是1200W左右
问题现象
由于没办法在生产环境执行，lab环境的数据量比生产环境还多，所以我就在lab环境使用上面的SQL进行查询，发现以上四个SQL在lab环境的执行时间分别是8.58秒、5.54秒、6.64秒、4.97秒。
分析思路
由于之前一直用的是MySQL或者Oracle, Google Spanner从来没用过，还好Google有提供自带SQL查询分析，我们接下来一个个分析，以下是第一个SQL的分析图
我们可以看到，主要的耗时时间是在Table scan、Merge distribute union和Sort limit，其中的Table scan和Sort limit很好理解，就是全表扫描和排序过滤，那么Merge distribute union又是什么意思？
Merge distribute union:由于Google Spanner是一个分布式数据，所有的数据会存放在多个远程服务器上，当使用Order By和Limit的时候，需要从多个远程服务器上取出数据，然后再合并排序。
要解决慢SQL的问题，就要把Table scan、Merge distribute union和Sort limit问题解决，首先是Table scan，job_log表中已经有了trigger_time索引了，为什么还会走全表扫描，按照MySQL的逻辑，不是应该走trigger_time索引，从右到左扫描，然后再回主表过滤返回么？难道Google Spanner索引组织方式和M有SQL的不一样？
带着这个疑问我去看了下Google Spanner的官方文档，通过文档，我们发现，Spanner的索引是有顺序结构的，默认创建的是升序的索引，可以通过DESC来创建倒序顺序，由于我们查询的SQL是Order By trigger_time DESC，而且我们的Where条件中也没有trigger_time，所以Spanner就走了Table scan
那我们就创建一个倒序的trigger_time索引，然后再执行查询，看看效果，发现查了两分钟都没有查询出结果，后来发现是因为表连接用的是LEFT JOIN，改为JOIN后的SQL分析图
我们可以发现Table scan变成了Index scan，说明走了索引, 是不是以为大功告成了？然而并没有，因为是查询页面，所以会有很多查询条件，这时候我们把这个SQL改下，加个查询条件t.job_id=100000,这个job_id是不存在的，这时候再去查询发现需要79秒，比全表扫描还要慢，看SQL分析图
我们发现由于在trigger_time里没有job_id，所以Spanner需要回主表去判断记录是否符合，而我们查询的job_id是没有job_log，所以需要回表1200多W次，导致查询速度比全表扫描还慢，这个时候，如果是MySQL的话，就需要增加job_id的索引或者创建个联合索引，但是由于查询的条件比较多，这样创建索引会导致索引的数量太大，占用空间太多，后来发现Spanner创建索引的时候，通过STORING关键字支持带指定的列进行数据过滤，写法是
CREATE INDEX idx_trigger_time ON job_log(trigger_time DESC) STORING (job_group, job_id, handle_code, trigger_code);
最后我们执行下SQL，通过SQL分析图，可以看到不会在一条条的回表查询
你以为就结束了么？不不不，其实还有个问题，因为前端页面是分页查询，如果页数太多了，上面的SQL会很慢，比如我们设置limit为10，offset为100000
SELECT t.id, t.job_group, t.job_id, t.executor_address, t.executor_handler, t.executor_param, t.executor_sharding_param, t.executor_fail_retry_count, t.trigger_time, t.trigger_code, t.trigger_msg, t.handle_time, t.handle_code, t.handle_msg, t.alarm_status, jgroup.app_name
FROM job_log AS t join job_group AS jgroup on t.job_group = jgroup.id WHERE t.is_deleted != 1 and t.trigger_time <= TIMESTAMP '2023-04-17 15:00:00' ORDER BY t.trigger_time DESC LIMIT 10 offset 100000;
执行改SQL，Spanner需要7.46秒才能返回数据，我们可以通过SQL分析图来看Spanner是怎么查询的
我们可以看到，Index scan和Table scan数量一模一样，所以可以发现Spanner是先回表在去过滤数据的，这是因为SQL的执行顺序就是FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT，但是我们更希望是先执行Limit再去回表，因为这样就能减少回表的次数
我们就需要把SQL改成这样
SELECT t.id, t.job_group, t.job_id, t.executor_address, t.executor_handler, t.executor_param, t.executor_sharding_param, t.executor_fail_retry_count, t.trigger_time, t.trigger_code, t.trigger_msg, t.handle_time, t.handle_code, t.handle_msg, t.alarm_status, jgroup.app_name
FROM job_log AS t join (SELECT t.id FROM job_log AS t where t.is_deleted != 1 and t.trigger_time <= TIMESTAMP '2023-04-17 15:00:00' ORDER BY t.trigger_time DESC LIMIT 10 offset 100000) AS log on t.id = log.id join job_group AS jgroup on t.job_group = jgroup.id ORDER BY t.trigger_time DESC;
我们再次执行优化后的SQL，发现现在只需要115.35毫秒，以下是SQL的分析图
通过SQL分析图，我们可以看到通过只查询先limit，再回表可以进一步提高SQL的性能，从8.58秒到115.35毫秒，可以提升74倍的效率，足见一个好的SQL的性能有多么重要
解决方案
最终，我们通过创建倒序索引+STORING多余的where条件的列来优化SQL，对于深分页查询，我们通过子查询的方式来做优化，其他的SQL也可以根据上面的分析逻辑来进行优化。
只有统计数据的SQL，因为我们定时任务的查询页面，在进入页面时，默认是可以没有任何条件的，所以count没办法优化，当时想的就是要不去掉count，要不默认只查最近一个星期的数据，由于如果去掉count，前端页面就没办法根据count来分页的，前端就要把分页改成只有上一页和下一页，改动较大，所以选择了第二种方式
总结
通过上面的分析，我们知道了Spanner和Mysql的一些不同，比如Spanner索引需要指定顺序，默认是倒序的，Spanner的索引可以带额外的列来过滤数据，减少回表的次数，还有一些相同点，比如Spanner的索引也是最左匹配原则
所以我们在将数据从一个数据库迁移到另一个数据库的时候，最好是要先熟悉被迁移的数据库，除了表结构之外，还需要特别注意索引的区别，因为创建索引的DDL语句都是通用的，但是每个数据库的索引组织方式都是不一样的，在这个情况下，直接迁移，虽然程序不会出错，却很容易出现SQL查询慢的问题。
在分析SQL慢查询的时候，首先要搞清楚数据库的索引组织方式，索引的一些类型，比如索引是不是区分顺序？索引能否带一些列来进行过滤？然后通过数据查询分析(基本上所有的数据库都会有)来分析为什么查询速度会这么慢
参考文档
- https://cloud.google.com/spanner/docs/secondary-indexes?hl=zh-cn#add-index
- https://cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language#create-index