list数据获取原理
- sql查询时仅作id查询（主模型+relation关联模型 的数据ID），获取所有id后通过super_mget_dict缓存方式获取id对应的数据对象，如果id不命中，还是会继续穿透查询数据库，当查询大量数据时，对redis和数据库造成的压力较大，一般来讲大量数据获取时，特殊情况下，仅获取id关键属性，加参数only_id控制。
- list取数时默认会返回分页的list数据以及数据总量count
以Employee取数做sql分析
参数
{
"model": "Employee"
}
未做任何优化
sql输出
[
由此可见，count取数耗时(232.424)与list相近，多数情况下count值作用不大，可以优化掉，使用only_list
"[232.424]SELECT count(*) AS count_1 FROM (SELECT employee.id AS id, jobinfomation_1.id AS `job_info.id`, department_history_1.id AS `position.id`, department_history_2.id AS `department.id`, department_history_3.id AS `unit.id`, job_step_1.id AS `job_step.id`, job_step_type_1.id AS `position_sequence.id` FROM employee INNER JOIN jobinfomation AS jobinfomation_1 ON jobinfomation_1.employee_id = employee.id AND jobinfomation_1.begin_date <= '2024-11-19' AND jobinfomation_1.end_date > '2024-11-19' AND jobinfomation_1.position_type = 1 AND jobinfomation_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_1 ON jobinfomation_1.position_id = department_history_1.origin_id AND department_history_1.begin_date <= '2024-11-19' AND department_history_1.end_date > '2024-11-19' AND department_history_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_2 ON department_history_1.parent_id = department_history_2.origin_id AND department_history_2.begin_date <= '2024-11-19' AND department_history_2.end_date > '2024-11-19' AND department_history_2.company_id = 190 LEFT OUTER JOIN department_history AS department_history_3 ON department_history_2.subordinate_unit_id = department_history_3.origin_id AND department_history_3.begin_date <= '2024-11-19' AND department_history_3.end_date > '2024-11-19' AND department_history_3.company_id = 190 LEFT OUTER JOIN job_step AS job_step_1 ON jobinfomation_1.job_step_id = job_step_1.id AND job_step_1.company_id = 190 LEFT OUTER JOIN job_step_type AS job_step_type_1 ON job_step_1.step_type_id = job_step_type_1.id AND job_step_type_1.company_id = 190 INNER JOIN (SELECT department_hierarchy.department_id AS id FROM department_hierarchy WHERE department_hierarchy.l0_id = 13742866 AND department_hierarchy.company_id = 190 AND department_hierarchy.begin_date <= '2024-11-19 00:00:00' AND department_hierarchy.end_date > '2024-11-19 00:00:00' AND department_hierarchy.enabled IN (1, 0)) AS anon_2 ON anon_2.id = department_history_2.origin_id WHERE employee.company_id = 190) AS anon_1",
以下为list标准取数过程：
此处获取主模型以及关联模型的ID，下一步通过super_mget_dict获取到实际数据，虽然此方法为缓存方法，但是大量数据时，redis速度也会下降，性能降低
"[221.869]SELECT employee.id AS id, jobinfomation_1.id AS `job_info.id`, department_history_1.id AS `position.id`, department_history_2.id AS `department.id`, department_history_3.id AS `unit.id`, job_step_1.id AS `job_step.id`, job_step_type_1.id AS `position_sequence.id` FROM employee INNER JOIN jobinfomation AS jobinfomation_1 ON jobinfomation_1.employee_id = employee.id AND jobinfomation_1.begin_date <= '2024-11-19' AND jobinfomation_1.end_date > '2024-11-19' AND jobinfomation_1.position_type = 1 AND jobinfomation_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_1 ON jobinfomation_1.position_id = department_history_1.origin_id AND department_history_1.begin_date <= '2024-11-19' AND department_history_1.end_date > '2024-11-19' AND department_history_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_2 ON department_history_1.parent_id = department_history_2.origin_id AND department_history_2.begin_date <= '2024-11-19' AND department_history_2.end_date > '2024-11-19' AND department_history_2.company_id = 190 LEFT OUTER JOIN department_history AS department_history_3 ON department_history_2.subordinate_unit_id = department_history_3.origin_id AND department_history_3.begin_date <= '2024-11-19' AND department_history_3.end_date > '2024-11-19' AND department_history_3.company_id = 190 LEFT OUTER JOIN job_step AS job_step_1 ON jobinfomation_1.job_step_id = job_step_1.id AND job_step_1.company_id = 190 LEFT OUTER JOIN job_step_type AS job_step_type_1 ON job_step_1.step_type_id = job_step_type_1.id AND job_step_type_1.company_id = 190 INNER JOIN (SELECT department_hierarchy.department_id AS id FROM department_hierarchy WHERE department_hierarchy.l0_id = 13742866 AND department_hierarchy.company_id = 190 AND department_hierarchy.begin_date <= '2024-11-19 00:00:00' AND department_hierarchy.end_date > '2024-11-19 00:00:00' AND department_hierarchy.enabled IN (1, 0)) AS anon_1 ON anon_1.id = department_history_2.origin_id WHERE employee.company_id = 190 ORDER BY employee.number ASC, employee.orderno ASC LIMIT 0, 20",
此处为super_mget_dict方法，即获取完整数据sql，这句sql再命中缓存情况下会没有，但是缓存不存在时会直接查询数据库，优化此处sql可以使用only_id，仅使用查询ID，不再获取所有info信息
"[2.881]SELECT employee.id AS employee_id, employee.operate_time AS employee_operate_time, employee.operator_id AS employee_operator_id, employee.company_id AS employee_company_id, employee.name AS employee_name, employee.number AS employee_number, employee.photo AS employee_photo, employee.life_photo AS employee_life_photo, employee.sign_photo AS employee_sign_photo, employee.photo_state AS employee_photo_state, employee.birthday AS employee_birthday, employee.lunar_calendar_birthday AS employee_lunar_calendar_birthday, employee.gender AS employee_gender, employee.blood_type AS employee_blood_type, employee.adapter_id AS employee_adapter_id, employee.enabled AS employee_enabled, employee.native_place AS employee_native_place, employee.married AS employee_married, employee.nation AS employee_nation, employee.nationality AS employee_nationality, employee.political_status_id AS employee_political_status_id, employee.residence_place AS employee_residence_place, employee.residence_type_id AS employee_residence_type_id, employee.orderno AS employee_orderno, employee.birth_place AS employee_birth_place, employee.id_type AS employee_id_type, employee.identity_card AS employee_identity_card, employee.identity_card_index AS employee_identity_card_index, employee.health AS employee_health, employee.specialty_hcm AS employee_specialty_hcm, employee.entry_source AS employee_entry_source, employee.record_id AS employee_record_id, employee.dismission_reason AS employee_dismission_reason, employee.dismission_type AS employee_dismission_type, employee.job_grade_id AS employee_job_grade_id, employee.job_id AS employee_job_id, employee.job_level_id AS employee_job_level_id, employee.job_level_adjust_date AS employee_job_level_adjust_date, employee.group_time AS employee_group_time, employee.take_work_time AS employee_take_work_time, employee.working_years_adjust AS employee_working_years_adjust, employee.company_age_adjust AS employee_company_age_adjust, employee.entry_date AS employee_entry_date, employee.early_retirement_date AS employee_early_retirement_date, employee.retirement_date AS employee_retirement_date, employee.plan_retirement_date AS employee_plan_retirement_date, employee.correction_date AS employee_correction_date, employee.quit_date AS employee_quit_date, employee.employee_category_id AS employee_employee_category_id, employee.position_status_id AS employee_position_status_id, employee.mobile AS employee_mobile, employee.mail AS employee_mail, employee.security_level AS employee_security_level, employee.search_string AS employee_search_string, employee.is_demo AS employee_is_demo, employee.administrator_type AS employee_administrator_type, employee.position_type AS employee_position_type, employee.job_level AS employee_job_level, employee.identity_type AS employee_identity_type, employee.identity_id AS employee_identity_id, employee.archives_organization AS employee_archives_organization, employee.archives_status_id AS employee_archives_status_id, employee.archives_number AS employee_archives_number, employee.statistics_category_id AS employee_statistics_category_id, employee.constellation AS employee_constellation, employee.present_address AS employee_present_address, employee.emergency_contact_person AS employee_emergency_contact_person, employee.emergency_contact_person_phone AS employee_emergency_contact_person_phone, employee.emergency_contact_person_two AS employee_emergency_contact_person_two, employee.emergency_contact_person_two_phone AS employee_emergency_contact_person_two_phone, employee.modify_time AS employee_modify_time, employee.create_time AS employee_create_time, employee.creator_id AS employee_creator_id, employee.updater_id AS employee_updater_id, employee.outer_info AS employee_outer_info, employee.soft_delete AS employee_soft_delete, employee.soft_delete_date AS employee_soft_delete_date, employee.info_integrity AS employee_info_integrity, employee.economic_work_years AS employee_economic_work_years, employee.finance_job_years AS employee_finance_job_years, employee.bank_job_years AS employee_bank_job_years, employee.has_relatives AS employee_has_relatives, employee.enter_type AS employee_enter_type, employee.description AS employee_description, employee.flex_data AS employee_flex_data, employee.c_field1 AS employee_c_field1, employee.c_field2 AS employee_c_field2, employee.c_field3 AS employee_c_field3, employee.c_field4 AS employee_c_field4, employee.c_field5 AS employee_c_field5, employee.c_field6 AS employee_c_field6, employee.c_field7 AS employee_c_field7, employee.c_field8 AS employee_c_field8, employee.c_field9 AS employee_c_field9, employee.c_field10 AS employee_c_field10, employee.c_field11 AS employee_c_field11, employee.c_field12 AS employee_c_field12, employee.c_field13 AS employee_c_field13, employee.c_field14 AS employee_c_field14, employee.c_field15 AS employee_c_field15, employee.c_field16 AS employee_c_field16, employee.c_field17 AS employee_c_field17, employee.c_field18 AS employee_c_field18, employee.c_field19 AS employee_c_field19, employee.c_field20 AS employee_c_field20, employee.c_field21 AS employee_c_field21, employee.c_field22 AS employee_c_field22, employee.c_field23 AS employee_c_field23, employee.c_field24 AS employee_c_field24, employee.c_field25 AS employee_c_field25, employee.c_field26 AS employee_c_field26, employee.c_field27 AS employee_c_field27, employee.c_field28 AS employee_c_field28, employee.c_field29 AS employee_c_field29, employee.c_field30 AS employee_c_field30, employee.c_field31 AS employee_c_field31, employee.c_field32 AS employee_c_field32, employee.c_field33 AS employee_c_field33, employee.c_field34 AS employee_c_field34, employee.c_field35 AS employee_c_field35, employee.data_object1_id AS employee_data_object1_id, employee.data_object2_id AS employee_data_object2_id, employee.data_object3_id AS employee_data_object3_id, employee.data_object4_id AS employee_data_object4_id FROM employee WHERE employee.id IN (1833608, 2259465, 1833627, 1829794, 2232494, 2232496, 2232497, 1843506, 2249271, 1843264, 1834054, 1833931, 1833675, 1835350, 2015586, 2444518, 1834734, 1844340, 2444539, 2444541)"
]
参数
{
"model": "Employee",
"extra_property": {
"only_list": true
}
}
优化only_list，减少count取数
sql分析
由下列sql分析对比可以看出，减少了count那一句sql，耗时基本可以减少为原来1/2
[
"[300.801]SELECT employee.id AS id, jobinfomation_1.id AS `job_info.id`, department_history_1.id AS `position.id`, department_history_2.id AS `department.id`, department_history_3.id AS `unit.id`, job_step_1.id AS `job_step.id`, job_step_type_1.id AS `position_sequence.id` FROM employee INNER JOIN jobinfomation AS jobinfomation_1 ON jobinfomation_1.employee_id = employee.id AND jobinfomation_1.begin_date <= '2024-11-19' AND jobinfomation_1.end_date > '2024-11-19' AND jobinfomation_1.position_type = 1 AND jobinfomation_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_1 ON jobinfomation_1.position_id = department_history_1.origin_id AND department_history_1.begin_date <= '2024-11-19' AND department_history_1.end_date > '2024-11-19' AND department_history_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_2 ON department_history_1.parent_id = department_history_2.origin_id AND department_history_2.begin_date <= '2024-11-19' AND department_history_2.end_date > '2024-11-19' AND department_history_2.company_id = 190 LEFT OUTER JOIN department_history AS department_history_3 ON department_history_2.subordinate_unit_id = department_history_3.origin_id AND department_history_3.begin_date <= '2024-11-19' AND department_history_3.end_date > '2024-11-19' AND department_history_3.company_id = 190 LEFT OUTER JOIN job_step AS job_step_1 ON jobinfomation_1.job_step_id = job_step_1.id AND job_step_1.company_id = 190 LEFT OUTER JOIN job_step_type AS job_step_type_1 ON job_step_1.step_type_id = job_step_type_1.id AND job_step_type_1.company_id = 190 INNER JOIN (SELECT department_hierarchy.department_id AS id FROM department_hierarchy WHERE department_hierarchy.l0_id = 13742866 AND department_hierarchy.company_id = 190 AND department_hierarchy.begin_date <= '2024-11-19 00:00:00' AND department_hierarchy.end_date > '2024-11-19 00:00:00' AND department_hierarchy.enabled IN (1, 0)) AS anon_1 ON anon_1.id = department_history_2.origin_id WHERE employee.company_id = 190 ORDER BY employee.number ASC, employee.orderno ASC LIMIT 0, 20",
"[2.637]SELECT employee.id AS employee_id, employee.operate_time AS employee_operate_time, employee.operator_id AS employee_operator_id, employee.company_id AS employee_company_id, employee.name AS employee_name, employee.number AS employee_number, employee.photo AS employee_photo, employee.life_photo AS employee_life_photo, employee.sign_photo AS employee_sign_photo, employee.photo_state AS employee_photo_state, employee.birthday AS employee_birthday, employee.lunar_calendar_birthday AS employee_lunar_calendar_birthday, employee.gender AS employee_gender, employee.blood_type AS employee_blood_type, employee.adapter_id AS employee_adapter_id, employee.enabled AS employee_enabled, employee.native_place AS employee_native_place, employee.married AS employee_married, employee.nation AS employee_nation, employee.nationality AS employee_nationality, employee.political_status_id AS employee_political_status_id, employee.residence_place AS employee_residence_place, employee.residence_type_id AS employee_residence_type_id, employee.orderno AS employee_orderno, employee.birth_place AS employee_birth_place, employee.id_type AS employee_id_type, employee.identity_card AS employee_identity_card, employee.identity_card_index AS employee_identity_card_index, employee.health AS employee_health, employee.specialty_hcm AS employee_specialty_hcm, employee.entry_source AS employee_entry_source, employee.record_id AS employee_record_id, employee.dismission_reason AS employee_dismission_reason, employee.dismission_type AS employee_dismission_type, employee.job_grade_id AS employee_job_grade_id, employee.job_id AS employee_job_id, employee.job_level_id AS employee_job_level_id, employee.job_level_adjust_date AS employee_job_level_adjust_date, employee.group_time AS employee_group_time, employee.take_work_time AS employee_take_work_time, employee.working_years_adjust AS employee_working_years_adjust, employee.company_age_adjust AS employee_company_age_adjust, employee.entry_date AS employee_entry_date, employee.early_retirement_date AS employee_early_retirement_date, employee.retirement_date AS employee_retirement_date, employee.plan_retirement_date AS employee_plan_retirement_date, employee.correction_date AS employee_correction_date, employee.quit_date AS employee_quit_date, employee.employee_category_id AS employee_employee_category_id, employee.position_status_id AS employee_position_status_id, employee.mobile AS employee_mobile, employee.mail AS employee_mail, employee.security_level AS employee_security_level, employee.search_string AS employee_search_string, employee.is_demo AS employee_is_demo, employee.administrator_type AS employee_administrator_type, employee.position_type AS employee_position_type, employee.job_level AS employee_job_level, employee.identity_type AS employee_identity_type, employee.identity_id AS employee_identity_id, employee.archives_organization AS employee_archives_organization, employee.archives_status_id AS employee_archives_status_id, employee.archives_number AS employee_archives_number, employee.statistics_category_id AS employee_statistics_category_id, employee.constellation AS employee_constellation, employee.present_address AS employee_present_address, employee.emergency_contact_person AS employee_emergency_contact_person, employee.emergency_contact_person_phone AS employee_emergency_contact_person_phone, employee.emergency_contact_person_two AS employee_emergency_contact_person_two, employee.emergency_contact_person_two_phone AS employee_emergency_contact_person_two_phone, employee.modify_time AS employee_modify_time, employee.create_time AS employee_create_time, employee.creator_id AS employee_creator_id, employee.updater_id AS employee_updater_id, employee.outer_info AS employee_outer_info, employee.soft_delete AS employee_soft_delete, employee.soft_delete_date AS employee_soft_delete_date, employee.info_integrity AS employee_info_integrity, employee.economic_work_years AS employee_economic_work_years, employee.finance_job_years AS employee_finance_job_years, employee.bank_job_years AS employee_bank_job_years, employee.has_relatives AS employee_has_relatives, employee.enter_type AS employee_enter_type, employee.description AS employee_description, employee.flex_data AS employee_flex_data, employee.c_field1 AS employee_c_field1, employee.c_field2 AS employee_c_field2, employee.c_field3 AS employee_c_field3, employee.c_field4 AS employee_c_field4, employee.c_field5 AS employee_c_field5, employee.c_field6 AS employee_c_field6, employee.c_field7 AS employee_c_field7, employee.c_field8 AS employee_c_field8, employee.c_field9 AS employee_c_field9, employee.c_field10 AS employee_c_field10, employee.c_field11 AS employee_c_field11, employee.c_field12 AS employee_c_field12, employee.c_field13 AS employee_c_field13, employee.c_field14 AS employee_c_field14, employee.c_field15 AS employee_c_field15, employee.c_field16 AS employee_c_field16, employee.c_field17 AS employee_c_field17, employee.c_field18 AS employee_c_field18, employee.c_field19 AS employee_c_field19, employee.c_field20 AS employee_c_field20, employee.c_field21 AS employee_c_field21, employee.c_field22 AS employee_c_field22, employee.c_field23 AS employee_c_field23, employee.c_field24 AS employee_c_field24, employee.c_field25 AS employee_c_field25, employee.c_field26 AS employee_c_field26, employee.c_field27 AS employee_c_field27, employee.c_field28 AS employee_c_field28, employee.c_field29 AS employee_c_field29, employee.c_field30 AS employee_c_field30, employee.c_field31 AS employee_c_field31, employee.c_field32 AS employee_c_field32, employee.c_field33 AS employee_c_field33, employee.c_field34 AS employee_c_field34, employee.c_field35 AS employee_c_field35, employee.data_object1_id AS employee_data_object1_id, employee.data_object2_id AS employee_data_object2_id, employee.data_object3_id AS employee_data_object3_id, employee.data_object4_id AS employee_data_object4_id FROM employee WHERE employee.id IN (1833608, 2259465, 1833627, 1829794, 2232494, 2232496, 2232497, 1843506, 2249271, 1843264, 1834054, 1833931, 1833675, 1835350, 2015586, 2444518, 1834734, 1844340, 2444539, 2444541)"
]
参数
{
"model": "Employee",
"extra_property": {
"only_id": true,
"only_list": true
}
}
优化only_list、only_id，表示仅获取数据ID
sql分析
对比看出减少获取完整info的sql，仅仅只有查询ID的sql
[
"[303.212]SELECT employee.id AS id, jobinfomation_1.id AS `job_info.id`, department_history_1.id AS `position.id`, department_history_2.id AS `department.id`, department_history_3.id AS `unit.id`, job_step_1.id AS `job_step.id`, job_step_type_1.id AS `position_sequence.id` FROM employee INNER JOIN jobinfomation AS jobinfomation_1 ON jobinfomation_1.employee_id = employee.id AND jobinfomation_1.begin_date <= '2024-11-19' AND jobinfomation_1.end_date > '2024-11-19' AND jobinfomation_1.position_type = 1 AND jobinfomation_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_1 ON jobinfomation_1.position_id = department_history_1.origin_id AND department_history_1.begin_date <= '2024-11-19' AND department_history_1.end_date > '2024-11-19' AND department_history_1.company_id = 190 LEFT OUTER JOIN department_history AS department_history_2 ON department_history_1.parent_id = department_history_2.origin_id AND department_history_2.begin_date <= '2024-11-19' AND department_history_2.end_date > '2024-11-19' AND department_history_2.company_id = 190 LEFT OUTER JOIN department_history AS department_history_3 ON department_history_2.subordinate_unit_id = department_history_3.origin_id AND department_history_3.begin_date <= '2024-11-19' AND department_history_3.end_date > '2024-11-19' AND department_history_3.company_id = 190 LEFT OUTER JOIN job_step AS job_step_1 ON jobinfomation_1.job_step_id = job_step_1.id AND job_step_1.company_id = 190 LEFT OUTER JOIN job_step_type AS job_step_type_1 ON job_step_1.step_type_id = job_step_type_1.id AND job_step_type_1.company_id = 190 INNER JOIN (SELECT department_hierarchy.department_id AS id FROM department_hierarchy WHERE department_hierarchy.l0_id = 13742866 AND department_hierarchy.company_id = 190 AND department_hierarchy.begin_date <= '2024-11-19 00:00:00' AND department_hierarchy.end_date > '2024-11-19 00:00:00' AND department_hierarchy.enabled IN (1, 0)) AS anon_1 ON anon_1.id = department_history_2.origin_id WHERE employee.company_id = 190 ORDER BY employee.number ASC, employee.orderno ASC LIMIT 0, 20"
]
参数
{
"model": "Employee",
"extra_property": {
"only_id": true,
"only_list": true,
"ignore_meta": true
}
}
极致优化，去掉关联，仅查询模型ID
sql分析
sql仅仅存在主模型，查询仅为主模型ID
[
"[0.9]SELECT employee.id AS id FROM employee WHERE employee.company_id = 190 LIMIT 0, 20"
]