為應用選擇和創(chuàng )建最佳索引，加速數據讀取

背景信息

在數據庫使用過(guò)程中，由SQL問(wèn)題導致的數據庫故障層出不窮，其中索引問(wèn)題是SQL問(wèn)題中常見(jiàn)的一種，例如：無(wú)索引，隱式轉換，索引創(chuàng )建不合理。

• 無(wú)索引：使用沒(méi)有創(chuàng )建索引的SQL訪(fǎng)問(wèn)數據庫中的表時(shí)，系統會(huì )進(jìn)行全表掃描。如果表的數據量很大，則SQL執行效率會(huì )過(guò)慢，同時(shí)占用數據庫連接數，當達到數據庫的最大連接數限制時(shí)，新的應用請求將會(huì )被拒絕導致出錯。

• 隱式轉換：指SQL查詢(xún)條件中的傳入值與目標字段的數據類(lèi)型不一致導致索引無(wú)法使用，引發(fā)慢SQL堆積導致數據庫連接數超出限制。
  常見(jiàn)隱式轉換如：字段的表結構定義為字符類(lèi)型，但SQL傳入值為數字；或者是字段定義collation為區分大小寫(xiě)，在多表關(guān)聯(lián)的場(chǎng)景下，其表的關(guān)聯(lián)字段大小寫(xiě)敏感定義各不相同。

注意事項

• 使用like關(guān)鍵字時(shí)，前置%會(huì )導致索引失效。
• 使用null值會(huì )被自動(dòng)從索引中排除，索引一般不會(huì )建立在有空值的列上。
• 使用or關(guān)鍵字時(shí)，or左右字段如果存在一個(gè)沒(méi)有索引，有索引字段也會(huì )失效。
• 使用!=操作符時(shí)，將放棄使用索引。因為范圍不確定，使用索引效率不高，會(huì )被引擎自動(dòng)改為全表掃描。
• 不要在索引字段進(jìn)行運算。
• 在使用復合索引時(shí)，最左前綴原則，查詢(xún)時(shí)必須使用索引的第一個(gè)字段，否則索引失效；并且應盡量讓字段順序與索引順序一致。
• 避免隱式轉換，定義的數據類(lèi)型與傳入的數據類(lèi)型保持一致。

索引使用策略

• 在經(jīng)常查詢(xún)而不經(jīng)常增刪改操作的字段加索引。
• order by與group by后應直接使用字段，而且字段應該是索引字段。
• 一個(gè)表上的索引不應該超過(guò)6個(gè)。
• 索引字段的長(cháng)度應固定，且不宜過(guò)長(cháng)。
• 索引字段不宜有過(guò)多重復。
• 在過(guò)濾性高的字段上加索引。

無(wú)索引優(yōu)化案例1

1. 在數據庫中執行show create table 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

2. 執行explain select * from customers where cust_zip = '44444' limit 0,1 \G;查看目標SQL語(yǔ)句的執行計劃。

            id: 1
   select_type: SIMPLE
         table: customers
          type: ALL
   possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 505560
         Extra: Using where

   說(shuō)明：
   從執行計劃可以看到type為ALL，即全表掃描，每次執行需要掃描505560行數據，數據庫的性能消耗非常大。

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

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

            id: 1
   select_type: SIMPLE
         table: customers
          type: ref
   possible_keys: idx_cus
           key: idx_cus
       key_len: 31
           ref: const
          rows: 4555
         Extra: Using index condition

   說(shuō)明：
   此時(shí)type已變更為ref，即基于索引的等值查詢(xún)或者表間等值連接，掃描行數為4555行，大幅優(yōu)化了查詢(xún)速度。

無(wú)索引優(yōu)化案例2

1. 在數據庫中執行show create table 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

2. 執行explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;查看目標SQL語(yǔ)句的執行計劃。

            id: 1
   select_type: SIMPLE
         table: customers
          type: ALL
   possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 505560
         Extra: Using filesort

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

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

            id: 1
   select_type: SIMPLE
         table: customers
          type: ref
   possible_keys: idx_cu_zip_name
           key: idx_cu_zip_name
       key_len: 31
           ref: const
          rows: 4555
         Extra: Using where; Using index

隱式轉換優(yōu)化案例1

1. 在數據庫中執行show create table 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

2. 執行explain select * from customers where cust_zip = 44444 limit 0,1 \G;查看目標SQL語(yǔ)句的執行計劃。

          id: 1
   select_type: SIMPLE
        table: customers
         type: ALL
   possible_keys: idx_cus
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 505560
        Extra: Using where

3. 執行show warnings;查詢(xún)上一個(gè)語(yǔ)句執行后的警告信息。

   Warning： Cannot use range access on index 'idx_cus' due to type or collation conversion on field 'cust_zip'

   說(shuō)明：
   由于cust_zip字段為字符串類(lèi)型，而應用傳入的是數字，導致隱式轉換，無(wú)法使用索引。

4. 可通過(guò)如下兩種方案優(yōu)化：

   • 將cust_zip字段的數據類(lèi)型修改為數字類(lèi)型。
   • 將應用中傳入的數據類(lèi)型修改為字符串類(lèi)型。

隱式轉換優(yōu)化案例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'\G;查看目標SQL語(yǔ)句的執行計劃。

   *************************** 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)

   說(shuō)明：
   兩個(gè)表中，cust_id字段的字符集未保持一致，無(wú)法使用索引。

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

   說(shuō)明：
   執行該語(yǔ)句會(huì )同步修改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'\G;查看執行計劃。

   *************************** 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

   說(shuō)明：
   表字段的COLLATE一致后執行計劃成功使用了索引。