mysql - 如何确定在 mysql 中添加索引的最佳位置？

Question

我已经尝试进行一些搜索，但我很难理解这一点。

一旦我解释了一个查询，我如何查看信息并确定添加索引以加速查询的最佳位置。

例如我有这个查询:

    SELECT a.app_id, DATE_FORMAT(app_datetime, '%c/%d/%y %H:%i') as app_datetime, app_language
        , if((select count(w.app_id) from li_app_cnc as w where w.app_id = a.app_id) > 0 ,         concat('CNC: ',(select cnc_note from li_app_cnc as w where w.app_id = a.app_id)), 
            if((select count(x.app_id) from li_app_dnc as x where x.app_id = a.app_id) > 0, concat('DNC: ', (select cancel_note from li_app_dnc as x where x.app_id = a.app_id)), 
                if((select count(y.app_id) from li_app_canceled as y where y.app_id = a.app_id) > 0, concat('Canceled: ', (select cancel_note from li_app_canceled as y where y.app_id = a.app_id)),
                    concat(h.emp_firstname, ' ', h.emp_lastname)))) as int_id, app_notes, app_facility, app_department
        , app_requesting_person, app_service_provider
        , cast(AES_DECRYPT(les_name, '$privatekey') as char) as les_name
        , les_dob, cast(AES_DECRYPT(les_medicaid_id, '$privatekey') as char) as les_medicaid_id
        , billing_total_time, billing_workorder_received, billing_admin_fee
        , billing_notes, created_by, created_on, modified_by, modified_on, wo_entered_by
        , t.cancel_code, t.cancel_note
        , u.cnc_code, u.cnc_note
        , v.cancel_code as dnc_code, v.cancel_note as dnc_note
        FROM li_appointments.li_appointments as a
        left Join orangehrm_li.hs_hr_employee as h on a.terp_id = h.employee_id
        left Join li_appointments.li_app_canceled as t on t.app_id = a.app_id
        left Join li_appointments.li_app_cnc as u on u.app_id = a.app_id
        left Join li_appointments.li_app_dnc as v on v.app_id = a.app_id
        where (app_client_id in (select account_number from li_appointments.li_client_access 
            where id = $userid) or created_by = '$username') 
            and date(app_datetime) = date(now())
            and a.app_id  not in (select f.app_id from li_app_dnc as f)

大约需要 14 秒

解释为:

   1    PRIMARY                 a   ALL                 37539   Using where
   1    PRIMARY                 h   ALL                 1036    
   1    PRIMARY                 t   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
   1    PRIMARY                 u   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
   1    PRIMARY                 v   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
   9    DEPENDENT SUBQUERY  f   unique_subquery PRIMARY PRIMARY 4   func    1   Using index
   8    DEPENDENT SUBQUERY  li_client_access    ALL                        72   Using where
   7    DEPENDENT SUBQUERY  y   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
   6    DEPENDENT SUBQUERY  y   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   Using index
   5    DEPENDENT SUBQUERY  x   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
   4    DEPENDENT SUBQUERY  x   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   Using index
   3    DEPENDENT SUBQUERY  w   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   
   2    DEPENDENT SUBQUERY  w   eq_ref  PRIMARY PRIMARY 4   li_appointments.a.app_id    1   Using index

Answer 1

一些经验法则:

1. 索引您将在其上定义WHERE 条件的所有字段。
2. 索引您将在其上定义关系的所有字段。
3. 索引您将定义分组标准的所有字段。
4. 避免索引表中的所有内容。在创建索引之前考虑一下。

(虽然这取决于你的具体需求，但我个人避免在浮点列上创建索引)

显然，上述规则意味着您必须索引所有字段作为主键或外键(如果您的表是规范化的，您必须已经创建了适当的主键；如果您的表未标准化，然后标准化)。