## software version  Mysql 8.0 

Optimizer choose index by Cost-Based Optimization
- Selectivity:
  。Selectivity is more better,if the lower the proportion of different values in the index column（customer_id is unique） 
  。Choose an index with fewer remaining rows after filtering.

- Index coverage
  。If an index can cover more columns in the WHERE or SELECT statements (reducing the need for a table lookup), its priority is increased.
  

## SQL

select
  id,
  receivables_rmb,
  fee_type_name
from
  customer_bill_fee_details
where
  bill_batch_number = '123456'
  and customer_id = 123456
  and id > 10000
order by
  id asc
limit
 1000

## SQL Execute the plan

 {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.64"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "table": {
        "table_name": "customer_bill_fee_details",
        "access_type": "ref",
        "possible_keys": [
          "index_customer_id",
          "index_bill_batch_number",
          "index_customer_id_receipt_time_is_gen",
          "index_customer_id_send_time_is_gen",
          "index_customer_id_is_send_is_gen",
          "index_customer_id_is_gen",
          "index_customer_id_created_time_is_gen",
          "fa_customer_bill_fee_details_customer_id_create_time_index",
          "index_cid_is_send_is_gen_operate_time"
        ],
        "key": "index_bill_batch_number",
        "used_key_parts": [
          "bill_batch_number"
        ],
        "key_length": "515",
        "ref": [
          "const"
        ],
        "rows_examined_per_scan": 1,
        "rows_produced_per_join": 0,
        "filtered": "5.00",
        "cost_info": {
          "read_cost": "0.54",
          "eval_cost": "0.01",
          "prefix_cost": "0.64",
          "data_read_per_join": "336"
        },
        "used_columns": [
          "id",
          "customer_id",
          "fee_type_name",
          "receivables_rmb",
          "bill_batch_number"
        ],
        "attached_condition": "(`integrated_dev`.`fa_customer_bill_fee_details`.`customer_id` = 24232)"
      }
    }
  }
}

Optimize comparisons： Single-column indexes vs. federated indexes
For example, consider the query: WHERE bill_batch_number = '123456' AND customer_id = 123456456.

Index Type                 Execution Path                                                                                      Performance Impact
Single-column Index A Index A → Table Access (回表) → Filter Condition B                                          Requires multiple table accesses, suitable for high selectivity scenarios
Single-column Index B Index B → Table Access → Filter Condition A                                                              Same as above
Composite Index (A,B) Directly locate via composite index (A,B), no filtering needed, can cover the query          Fastest, eliminates table access and secondary filtering
Composite Index (B,A) Equivalent to (A,B), but must follow the leftmost prefix rule                                Same principle, but index order should be adjusted according to condition order


How to use Single-column index?

Single-column query conditions dominate the business (e.g., 80% of queries use only bill_batch_number).
Not all query patterns can be predicted, so single-column flexibility must be maintained.


How to use combine index?

High-frequency execution of multi-condition combination queries.
The query performance requirements are strict, and table access (回表) or secondary filtering must be eliminated.


Therefore, at the beginning we can start with single-column indexes, and as query conditions become more complex later on, we will need to perform index maintenance by converting the single-column indexes into composite indexes