為應用選擇和建立最佳索引，加速資料讀取

注意事項

• 使用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 where; 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
            Extra: Using where; Using index

    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: customers1
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1
            Extra: Using where; Using join buffer (Block Nested Loop)

   說明

   兩個表中，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
             type: ref
    possible_keys: idx_cu_id
              key: idx_cu_id
          key_len: 33
              ref: const
             rows: 1
            Extra: Using where; Using index

    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: customers1
             type: ref
    possible_keys: idx_cu_id
              key: idx_cu_id
          key_len: 33
              ref: const
             rows: 1
            Extra: Using where

   說明

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