全部產品
Search

搜尋本產品

    ApsaraDB RDS：為應用選擇和建立最佳索引,加速資料讀取

    文件中心

    ApsaraDB RDS：為應用選擇和建立最佳索引,加速資料讀取

    更新時間：Jul 03, 2025

    本文介紹如何建立最佳索引，提高資料庫的讀取效能。

    背景資訊

    在資料庫使用過程中，由SQL問題導致的資料庫故障層出不窮，其中索引問題是SQL問題中常見的一種，例如：無索引，隱式轉換，索引建立不合理。

    • 無索引：使用沒有建立索引的SQL訪問資料庫中的表時，系統會進行全表掃描。如果表的資料量很大，則SQL執行效率會非常慢，同時會佔用資料庫連接數，當達到資料庫的最大串連數限制時，新的應用請求將會被拒絕導致出錯。

    • 隱式轉換：指SQL查詢條件中傳入的值與目標欄位的資料類型不一致導致索引無法使用，引發慢SQL堆積導致資料庫連接數超出限制。

    注意事項

    • 使用like關鍵字時，前置%會導致索引失效。

    • 使用null值會被自動從索引中排除，索引一般不會建立在有空值的列上。

    • 使用or關鍵字時，如果or左右的欄位有一個沒有索引，則有索引欄位也會失效。

    • 使用!=操作符時，將放棄使用索引。因為範圍不確定，使用索引效率不高，會被引擎自動改為全表掃描。

    • 不要在索引欄位進行運算。

    • 在使用複合索引時需遵循最左首碼原則，查詢時必須使用索引的第一個欄位，否則索引失效。欄位順序應盡量與索引順序一致。

    • 避免隱式轉換，定義的資料類型與傳入的資料類型需保持一致。

    索引使用原則

    • 在經常查詢而不經常增刪改操作的欄位加索引。

    • order by與group by後應直接使用欄位，而且欄位應該是索引欄位。

    • 一個表上的索引不應該超過6個。

    • 索引欄位的長度應固定，且不宜過長。

    • 索引欄位不宜有過多的重複。

    • 在過濾性高的欄位上加索引。

    無索引最佳化案例1

    1. 在資料庫中執行show create table customers;查看錶結構。

      說明

      customers表中有20條資料。

       CREATE TABLE `customers` (
   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
   `cust_name` char(50) NOT NULL,
   `cust_address` char(50) DEFAULT NULL,
   `cust_city` char(50) DEFAULT NULL,
   `cust_state` char(5) DEFAULT NULL,
   `cust_zip` char(10) DEFAULT NULL,
   `cust_country` char(50) DEFAULT NULL,
   `cust_contact` char(50) DEFAULT NULL,
   `cust_email` char(255) DEFAULT NULL,
   PRIMARY KEY (`cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8;

    2. 執行explain select * from customers where cust_zip = '44444' limit 0,1;查看目標SQL語句的執行計畫。

                  id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 10.00
        Extra: Using where
      說明

      從執行計畫可以看到type為ALL，即全表掃描，每次執行需要掃描20行資料，資料庫的效能消耗非常大。

    3. 執行alter table customers add index idx_cus(cust_zip);添加索引。

    4. 重新執行explain select * from customers where cust_zip = '44444' limit 0,1;查看執行計畫。

                 id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ref
possible_keys: idx_cus
          key: idx_cus
      key_len: 31
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
      說明

      此時type已變更為ref，即基於索引的等值查詢或者表間等值串連，掃描行數為1行，大幅最佳化了查詢速度。

    無索引最佳化案例2

    1. 執行drop table if exists customers;刪除之前建立的表customers。

    2. 在資料庫中執行如下語句，建立測試表customers。

       CREATE TABLE `customers` (
   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
   `cust_name` char(50) NOT NULL,
   `cust_address` char(50) DEFAULT NULL,
   `cust_city` char(50) DEFAULT NULL,
   `cust_state` char(5) DEFAULT NULL,
   `cust_zip` char(10) DEFAULT NULL,
   `cust_country` char(50) DEFAULT NULL,
   `cust_contact` char(50) DEFAULT NULL,
   `cust_email` char(255) DEFAULT NULL,
   PRIMARY KEY (`cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8;

    3. 在customers表中插入20條資料。

      INSERT INTO customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES
('John Smith', '123 Main St', 'New York', 'NY', '10001', 'USA', 'John Doe', 'john@example.com'),
('Jane Doe', '456 Elm St', 'Los Angeles', 'CA', '90001', 'USA', 'Jane Smith', 'jane@example.com'),
('Bob Johnson', '789 Oak St', 'Chicago', 'IL', '60601', 'USA', 'Bob Smith', 'bob@example.com'),
('Alice Brown', '987 Maple Ave', 'Houston', 'TX', '77001', 'USA', 'Alice Smith', 'alice@example.com'),
('Michael Davis', '654 Pine St', 'San Francisco', 'CA', '94101', 'USA', 'Michael Smith', 'michael@example.com'),
('Sarah Wilson', '321 Cedar St', 'Seattle', 'WA', '98101', 'USA', 'Sarah Smith', 'sarah@example.com'),
('David Lee', '876 Birch St', 'Boston', 'MA', '02101', 'USA', 'David Smith', 'david@example.com'),
('Karen Taylor', '543 Willow St', 'Miami', 'FL', '33101', 'USA', 'Karen Smith', 'karen@example.com'),
('Steven Miller', '210 Oak St', 'Denver', 'CO', '80201', 'USA', 'Steven Smith', 'steven@example.com'),
('Lisa Anderson', '876 Elm St', 'Atlanta', 'GA', '30301', 'USA', 'Lisa Smith', 'lisa@example.com'),
('Matthew Wilson', '567 Pine St', 'Dallas', 'TX', '75201', 'USA', 'Matthew Smith', 'matthew@example.com'),
('Emily Johnson', '654 Cedar St', 'Phoenix', 'AZ', '85001', 'USA', 'Emily Smith', 'emily@example.com'),
('James Davis', '321 Birch St', 'San Diego', 'CA', '92101', 'USA', 'James Smith', 'james@example.com'),
('Olivia Brown', '987 Willow St', 'Portland', 'OR', '97201', 'USA', 'Olivia Smith', 'olivia@example.com'),
('Daniel Wilson', '543 Oak St', 'Las Vegas', 'NV', '89101', 'USA', 'Daniel Smith', 'daniel@example.com'),
('Emma Taylor', '210 Elm St', 'Philadelphia', 'PA', '19101', 'USA', 'Emma Smith', 'emma@example.com'),
('Christopher Miller', '876 Maple Ave', 'Austin', 'TX', '78701', 'USA', 'Christopher Smith', 'christopher@example.com'),
('Sophia Anderson', '567 Pine St', 'San Antonio', 'TX', '78201', 'USA', 'Sophia Smith', 'sophia@example.com'),
('Jacob Wilson', '654 Cedar St', 'Nashville', 'TN', '37201', 'USA', 'Jacob Smith', 'jacob@example.com'),
('Ava Johnson', '321 Birch St', 'Charlotte', 'NC', '28201', 'USA', 'Ava Smith', 'ava@example.com');

    4. 執行explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;查看目標SQL語句的執行計畫。

                 id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 10.00
        Extra: Using where; Using filesort

    5. 執行alter table customers add index idx_cu_zip_name(cust_zip,cust_name);添加索引。

    6. 重新執行explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;查看執行計畫。

                 id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ref
possible_keys: idx_cu_zip_name
          key: idx_cu_zip_name
      key_len: 31
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

    隱式轉換最佳化案例1

    1. 執行drop table if exists customers;刪除之前建立的表customers。

    2. 在資料庫中執行如下語句，建立測試表customers。

       CREATE TABLE `customers` (
   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
   `cust_name` char(50) NOT NULL,
   `cust_address` char(50) DEFAULT NULL,
   `cust_city` char(50) DEFAULT NULL,
   `cust_state` char(5) DEFAULT NULL,
   `cust_zip` char(10) DEFAULT NULL,
   `cust_country` char(50) DEFAULT NULL,
   `cust_contact` char(50) DEFAULT NULL,
   `cust_email` char(255) DEFAULT NULL,
   PRIMARY KEY (`cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8;

    3. 在customers表中插入20條資料。

      INSERT INTO customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES
('John Smith', '123 Main St', 'New York', 'NY', '10001', 'USA', 'John Doe', 'john@example.com'),
('Jane Doe', '456 Elm St', 'Los Angeles', 'CA', '90001', 'USA', 'Jane Smith', 'jane@example.com'),
('Bob Johnson', '789 Oak St', 'Chicago', 'IL', '60601', 'USA', 'Bob Smith', 'bob@example.com'),
('Alice Brown', '987 Maple Ave', 'Houston', 'TX', '77001', 'USA', 'Alice Smith', 'alice@example.com'),
('Michael Davis', '654 Pine St', 'San Francisco', 'CA', '94101', 'USA', 'Michael Smith', 'michael@example.com'),
('Sarah Wilson', '321 Cedar St', 'Seattle', 'WA', '98101', 'USA', 'Sarah Smith', 'sarah@example.com'),
('David Lee', '876 Birch St', 'Boston', 'MA', '02101', 'USA', 'David Smith', 'david@example.com'),
('Karen Taylor', '543 Willow St', 'Miami', 'FL', '33101', 'USA', 'Karen Smith', 'karen@example.com'),
('Steven Miller', '210 Oak St', 'Denver', 'CO', '80201', 'USA', 'Steven Smith', 'steven@example.com'),
('Lisa Anderson', '876 Elm St', 'Atlanta', 'GA', '30301', 'USA', 'Lisa Smith', 'lisa@example.com'),
('Matthew Wilson', '567 Pine St', 'Dallas', 'TX', '75201', 'USA', 'Matthew Smith', 'matthew@example.com'),
('Emily Johnson', '654 Cedar St', 'Phoenix', 'AZ', '85001', 'USA', 'Emily Smith', 'emily@example.com'),
('James Davis', '321 Birch St', 'San Diego', 'CA', '92101', 'USA', 'James Smith', 'james@example.com'),
('Olivia Brown', '987 Willow St', 'Portland', 'OR', '97201', 'USA', 'Olivia Smith', 'olivia@example.com'),
('Daniel Wilson', '543 Oak St', 'Las Vegas', 'NV', '89101', 'USA', 'Daniel Smith', 'daniel@example.com'),
('Emma Taylor', '210 Elm St', 'Philadelphia', 'PA', '19101', 'USA', 'Emma Smith', 'emma@example.com'),
('Christopher Miller', '876 Maple Ave', 'Austin', 'TX', '78701', 'USA', 'Christopher Smith', 'christopher@example.com'),
('Sophia Anderson', '567 Pine St', 'San Antonio', 'TX', '78201', 'USA', 'Sophia Smith', 'sophia@example.com'),
('Jacob Wilson', '654 Cedar St', 'Nashville', 'TN', '37201', 'USA', 'Jacob Smith', 'jacob@example.com'),
('Ava Johnson', '321 Birch St', 'Charlotte', 'NC', '28201', 'USA', 'Ava Smith', 'ava@example.com');

    4. 執行explain select * from customers where cust_zip = 44444 limit 0,1;查看目標SQL語句的執行計畫。

                 id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 10.00
        Extra: Using where

    隱式轉換最佳化案例2

    1. 在資料庫中執行show create table customers1;和show create table customers2;查看錶結構。

       CREATE TABLE `customers1` (
   `cust_id` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
   `cust_name` char(50) NOT NULL,
   KEY `idx_cu_id` (`cust_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      CREATE TABLE `customers2` (
   `cust_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
   `cust_name` char(50) NOT NULL,
   KEY `idx_cu_id` (`cust_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    2. 執行explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';查看目標SQL語句的執行計畫。

       *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers2
          type: ref
 possible_keys: idx_cu_id
           key: idx_cu_id
       key_len: 33
           ref: const
          rows: 1
          filtered: 100.00
         Extra: Using index
       *************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers1
          type: range
 possible_keys: idx_cu_id
           key: idx_cu_id
       key_len: 13
           ref: NULL
          rows: 1
          filtered: 100.00
         Extra: Using where; Using join buffer (hash join)
      說明

      兩個表中，cust_id欄位的字元集未保持一致，無法使用索引。

    3. 執行alter table customers1 modify column cust_id varchar(10) COLLATE utf8_bin;將customers1中cust_id欄位的字元集修改為utf8_bin，保證和customers2中的cust_id欄位一致。

      說明

      執行該語句會同步修改cust_id欄位的CHARACTER SET為utf8。

    4. 重新執行explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';查看執行計畫。

       *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers2
         partitions: NULL
          type: ref
 possible_keys: idx_cu_id
           key: idx_cu_id
       key_len: 33
           ref: const
          rows: 1
          filtered: 100.00
         Extra: Using index
       *************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers1
         partitions: NULL
          type: ref
 possible_keys: idx_cu_id
           key: idx_cu_id
       key_len: 33
           ref: const
          rows: 1
          filtered: 100.00
         Extra: NULL
      說明

      表欄位的COLLATE一致後執行計畫成功使用了索引。