问题
查询列表，耗时10s
原因
缺索引
解决方法
字段1-缺索引
新增索引
字段2-日期字段：测试环境有索引，生产环境没有
新增索引
怎么查找原因？
字段1
检查查询字段，有没有索引
没有，就新增
字段2
测试环境有索引，所以一开始没想到生产没有索引
后面通过explain执行计划分析sql性能
1000万记录
没有使用日期过滤数据，是1000万记录，耗时10s
Plan Hash Value : 1488340459
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 111518 | 00:22:19 |
| 1 | SORT AGGREGATE | | 1 | 64 | | |
| * 2 | HASH JOIN RIGHT OUTER | | 7943655 | 508393920 | 111518 | 00:22:19 |
| 3 | INDEX FAST FULL SCAN | PK_SYS_SOFT_CONF | 4618 | 23090 | 4 | 00:00:01 |
| * 4 | HASH JOIN RIGHT OUTER | | 7943655 | 468675645 | 111489 | 00:22:18 |
| 5 | INDEX FAST FULL SCAN | PK_SYS_VENDER | 1210 | 6050 | 3 | 00:00:01 |
| * 6 | HASH JOIN RIGHT OUTER | | 7943655 | 428957370 | 111461 | 00:22:18 |
| 7 | INDEX FAST FULL SCAN | PK_SYS_VENDER | 1210 | 6050 | 3 | 00:00:01 |
| * 8 | HASH JOIN RIGHT OUTER | | 7943655 | 389239095 | 111433 | 00:22:18 |
| 9 | INDEX FAST FULL SCAN | SYS_C0030078 | 938 | 4690 | 3 | 00:00:01 |
| * 10 | HASH JOIN RIGHT OUTER | | 7943655 | 349520820 | 111405 | 00:22:17 |
| 11 | INDEX FULL SCAN | PK_SYS_PDT_TYPE | 382 | 1910 | 1 | 00:00:01 |
| * 12 | HASH JOIN RIGHT OUTER | | 7943655 | 309802545 | 111379 | 00:22:17 |
| 13 | INDEX FAST FULL SCAN | PK_SYS_USER | 4912962 | 34390734 | 6875 | 00:01:23 |
| * 14 | TABLE ACCESS FULL | SYS_PRODUCT | 7943655 | 254196960 | 83496 | 00:16:42 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("SC"."SOFT_CONF_ID"(+)="P"."SOFT_CONF_ID")
* 4 - access("V"."VENDER_ID"(+)="P"."VENDER_ID")
* 6 - access("F"."VENDER_ID"(+)="P"."FILIALE_ID")
* 8 - access("C"."CUSTOMER_NUMBER_ID"(+)="P"."CUSTOMER_NUMBER")
* 10 - access("PT"."PDT_TYPE_ID"(+)="P"."PDT_TYPE_ID")
* 12 - access("SU"."USER_ID"(+)="P"."USER_ID")
* 14 - filter("P"."PRODUCT_PART"=0)
10万记录
使用日期字段过滤，查询结果只有10万记录，但是仍然很慢，要5s——后面发现是日期字段在生产环境没有加索引，因为explain计划结果显示全表扫描
Plan Hash Value : 4287832562
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 83548 | 00:16:43 |
| 1 | SORT AGGREGATE | | 1 | 70 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 70 | 83548 | 00:16:43 |
| 3 | NESTED LOOPS OUTER | | 1 | 63 | 83547 | 00:16:43 |
| 4 | NESTED LOOPS OUTER | | 1 | 58 | 83547 | 00:16:43 |
| 5 | NESTED LOOPS OUTER | | 1 | 53 | 83547 | 00:16:43 |
| 6 | NESTED LOOPS OUTER | | 1 | 48 | 83547 | 00:16:43 |
| 7 | NESTED LOOPS OUTER | | 1 | 43 | 83547 | 00:16:43 |
| * 8 | TABLE ACCESS FULL | SYS_PRODUCT | 1 | 38 | 83547 | 00:16:43 |
| * 9 | INDEX UNIQUE SCAN | PK_SYS_PDT_TYPE | 1 | 5 | 0 | 00:00:01 |
| * 10 | INDEX UNIQUE SCAN | SYS_C0030078 | 1 | 5 | 0 | 00:00:01 |
| * 11 | INDEX UNIQUE SCAN | PK_SYS_VENDER | 1 | 5 | 0 | 00:00:01 |
| * 12 | INDEX UNIQUE SCAN | PK_SYS_VENDER | 1 | 5 | 0 | 00:00:01 |
| * 13 | INDEX UNIQUE SCAN | PK_SYS_SOFT_CONF | 1 | 5 | 0 | 00:00:01 |
| * 14 | INDEX UNIQUE SCAN | PK_SYS_USER | 1 | 7 | 1 | 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 8 - filter("P"."REG_TIME">=ADD_MONTHS(SYSDATE@!,-1) AND "P"."PRODUCT_PART"=0)
* 9 - access("PT"."PDT_TYPE_ID"(+)="P"."PDT_TYPE_ID")
* 10 - access("C"."CUSTOMER_NUMBER_ID"(+)="P"."CUSTOMER_NUMBER")
* 11 - access("F"."VENDER_ID"(+)="P"."FILIALE_ID")
* 12 - access("V"."VENDER_ID"(+)="P"."VENDER_ID")
* 13 - access("SC"."SOFT_CONF_ID"(+)="P"."SOFT_CONF_ID")
* 14 - access("SU"."USER_ID"(+)="P"."USER_ID")
优化效果
字段1
新增索引之后，速度快了一两秒
字段2-日期字段
新增索引之后，查询最近一个月数据-10万记录，之前要5s，现在不到1s
继续优化-自定义count
mybatis pagehelper默认会创建count sql，但是速度很慢，全表查询耗时10s以上
怎么优化？自定义count。并且去掉所有左连接。耗时6 7s
继续优化-默认只查询最近一个月数据
速度降到1s以内
200到500ms
耗时
单表统计1000万记录耗时2~3s
-- 统计sys_product
select count(*)
from sys_product;
800万记录
耗时2 3s
总结
查询列表，不要查询所有数据，默认只查近期的，否则卡死
光sql耗时就2 3s
实际浏览器看到的耗时6 7s
分页查询很快 0.1s
分页查询本身其实很快，ms级别
主要是count慢，千万数据，耗时秒级别，因为是全表扫描
SELECT
*
FROM
(
SELECT
TMP_PAGE.*,
ROWNUM PAGEHELPER_ROW_ID
FROM
(
select
p.product_id,
p.serial_no,
p.product_no,
p.sale_time,
p.vender_id,
p.filiale_id,
p.reg_time,
p.create_time,
p.update_time,
from
sys_product p
left join sys_user su on su.user_id = p.user_id
left join sys_pdt_type pt on pt.pdt_type_id = p.pdt_type_id
left join sys_soft_conf sc on sc.soft_conf_id = p.soft_conf_id
WHERE
p.product_part = 0
order by
p.product_id
) TMP_PAGE
WHERE
ROWNUM <= 10
)
WHERE
PAGEHELPER_ROW_ID > 0
最近一个月 几百ms
200~500ms
