此文是我做为日记来用的,后面再规范和整理
1、索引太多会影响Insert、Update;
2、索引太少不利于性能调优;
3、数据结构分析、SQL拼写的分析如果太复杂,又不利于实际的开发工作;
故,是否可以这样:
1、第一步,确定某个表可能用到的、经常使用的查询条件,不建议加索引的字段后面会忽略
SELECT * FROM t_big_data aa
WHERE 1=1
AND aa.billingtime>='2021-08-01' AND aa.billingtime<'2021-08-02'
AND aa.stateId=1 -- 此字段不建议加索引,后面建索引时会忽略它
AND aa.patientid='360732199801031328'
AND aa.departmentid='0307'
AND aa.roomId='1001'
AND aa.doctorId='2398';
2、第二步,为该表创建常用查询使用的索引
-- billingtime业务发生时间,是连续数据,一般是范围查询,适合brin索引
CREATE INDEX idx_big_data_billingtime
ON big_data USING brin(billingtime);
-- 其他字段使用多字段联合索引,默认btree
CREATE INDEX idx_big_data_search
ON big_data(patientid, departmentid, roomId, doctorId);
3、最后,在代码和存储过程函数中,规范Where的书写,使用CASE WHEN对条件字段特殊处理,对查询语句中不做为条件的参数,程序将会赋“ALL”值。
如:
SELECT * FROM t_big_data aa
WHERE 1=1
AND aa.billingtime>=@begintime AND aa.billingtime<@endtime
AND aa.stateId=1
AND aa.patientid=CASE @patientId WHEN 'ALL' THEN aa.patientid ELSE @patientId END
AND aa.departmentid=CASE @departmentid WHEN 'ALL' THEN aa.departmentid ELSE @departmentid END
AND aa.roomId=CASE @roomId WHEN 'ALL' THEN aa.roomId ELSE @roomId END
AND aa.doctorId=CASE @doctorId WHEN 'ALL' THEN aa.doctorId ELSE @doctorId END;
程序对这些参数赋值后,变成实际的值,以第1步中的SQL为例,若本次查询无需对patientId字段进行判断,则可以对相应的@patientId变量赋“ALL”值,其他变量照常赋值,生成实际的SQL如下:
SELECT * FROM t_big_data aa
WHERE 1=1
AND aa.billingtime>='2021-08-01' AND aa.billingtime<'2021-08-02'
AND aa.stateId=1
-- @patientId传进来是ALL值,实际生成SQL语句就是这样的效果,
-- 则此句与 “AND aa.patientid=aa.patientid” 等效
AND aa.patientid=CASE 'ALL' WHEN 'ALL' THEN aa.patientid ELSE 'ALL' END
AND aa.departmentid=CASE '0307' WHEN 'ALL' THEN aa.departmentid ELSE '0307' END
AND aa.roomId=CASE '1001' WHEN 'ALL' THEN aa.roomId ELSE '1001' END
AND aa.doctorId=CASE '2398' WHEN 'ALL' THEN aa.doctorId ELSE '2398' END;
4、经过 EXPLAIN分析执行计划,这样做用到了相关的两个索引。
总之,我似乎找到了一种能在建索引时【偷懒】,又不影响数据库性能的方案。
最后是由此带来的问题
1、虽然这样做【节约】了索引,查询也用到了必要的索引,在测试库中的执行性能也没问题;
2、但是,对数据库底层逻辑的分析和猜想,上面这种写法增加了无必要的字段的判断,不确定这样做,对数据库会不会造成无意义的硬盘IO等问题。
PS:MySQL 5.7我也测试过,这种【Case When Then】的写法没有用到索引,不确定MySQL8怎么样。
先把这个【点子】记录下来,以后再做分析和测试。
希望数据库的大拿们能够帮我分析和指正。