中國站
幫助中心 > 數(shù)據庫 > 云數(shù)據庫MySQL > 最佳實踐 > 為應用選擇和創(chuàng)建最佳索引，加速數(shù)據讀取

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

背景信息

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

  • 無索引：使用沒有創(chuàng)建索引的SQL訪問數(shù)據庫中的表時，系統(tǒng)會進行全表掃描。如果表的數(shù)據量很大，則SQL執(zhí)行效率會過慢，同時占用數(shù)據庫連接數(shù)，當達到數(shù)據庫的最大連接數(shù)限制時，新的應用請求將會被拒絕導致出錯。

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

注意事項

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

索引使用策略

  • 在經常查詢而不經常增刪改操作的字段加索引。
  • order by與group by后應直接使用字段，而且字段應該是索引字段。
  • 一個表上的索引不應該超過6個。
  • 索引字段的長度應固定，且不宜過長。
  • 索引字段不宜有過多重復。
  • 在過濾性高的字段上加索引。

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

  1. 在數(shù)據庫中執(zhí)行show create table customers;查看表結構。

    1. CREATE TABLE `customers` (
    2. `cust_id` int(11) NOT NULL AUTO_INCREMENT,
    3. `cust_name` char(50) NOT NULL,
    4. `cust_address` char(50) DEFAULT NULL,
    5. `cust_city` char(50) DEFAULT NULL,
    6. `cust_state` char(5) DEFAULT NULL,
    7. `cust_zip` char(10) DEFAULT NULL,
    8. `cust_country` char(50) DEFAULT NULL,
    9. `cust_contact` char(50) DEFAULT NULL,
    10. `cust_email` char(255) DEFAULT NULL,
    11. PRIMARY KEY (`cust_id`),
    12. ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8

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

    1.          id: 1
    2. select_type: SIMPLE
    3.       table: customers
    4.        type: ALL
    5. possible_keys: NULL
    6.         key: NULL
    7.     key_len: NULL
    8.         ref: NULL
    9.        rows: 505560
    10.       Extra: Using where

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

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

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

    1.          id: 1
    2. select_type: SIMPLE
    3.       table: customers
    4.        type: ref
    5. possible_keys: idx_cus
    6.         key: idx_cus
    7.     key_len: 31
    8.         ref: const
    9.        rows: 4555
    10.       Extra: Using index condition

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

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

  1. 在數(shù)據庫中執(zhí)行show create table customers;查看表結構。

    1. CREATE TABLE `customers` (
    2. `cust_id` int(11) NOT NULL AUTO_INCREMENT,
    3. `cust_name` char(50) NOT NULL,
    4. `cust_address` char(50) DEFAULT NULL,
    5. `cust_city` char(50) DEFAULT NULL,
    6. `cust_state` char(5) DEFAULT NULL,
    7. `cust_zip` char(10) DEFAULT NULL,
    8. `cust_country` char(50) DEFAULT NULL,
    9. `cust_contact` char(50) DEFAULT NULL,
    10. `cust_email` char(255) DEFAULT NULL,
    11. PRIMARY KEY (`cust_id`),
    12. ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8

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

    1.          id: 1
    2. select_type: SIMPLE
    3.       table: customers
    4.        type: ALL
    5. possible_keys: NULL
    6.         key: NULL
    7.     key_len: NULL
    8.         ref: NULL
    9.        rows: 505560
    10.       Extra: Using filesort

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

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

    1.          id: 1
    2. select_type: SIMPLE
    3.       table: customers
    4.        type: ref
    5. possible_keys: idx_cu_zip_name
    6.         key: idx_cu_zip_name
    7.     key_len: 31
    8.         ref: const
    9.        rows: 4555
    10.       Extra: Using where; Using index

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

  1. 在數(shù)據庫中執(zhí)行show create table customers;查看表結構。

    1. CREATE TABLE `customers` (
    2. `cust_id` int(11) NOT NULL AUTO_INCREMENT,
    3. `cust_name` char(50) NOT NULL,
    4. `cust_address` char(50) DEFAULT NULL,
    5. `cust_city` char(50) DEFAULT NULL,
    6. `cust_state` char(5) DEFAULT NULL,
    7. `cust_zip` char(10) DEFAULT NULL,
    8. `cust_country` char(50) DEFAULT NULL,
    9. `cust_contact` char(50) DEFAULT NULL,
    10. `cust_email` char(255) DEFAULT NULL,
    11. PRIMARY KEY (`cust_id`),
    12. ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8

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

    1.        id: 1
    2. select_type: SIMPLE
    3.      table: customers
    4.       type: ALL
    5. possible_keys: idx_cus
    6.        key: NULL
    7.    key_len: NULL
    8.        ref: NULL
    9.       rows: 505560
    10.      Extra: Using where

  3. 執(zhí)行show warnings;查詢上一個語句執(zhí)行后的警告信息。

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

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

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

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

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

  1. 在數(shù)據庫中執(zhí)行show create table customers1;和show create table customers2;查看表結構。

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

  2. 執(zhí)行explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x'\G;查看目標SQL語句的執(zhí)行計劃。

    1. *************************** 1. row ***************************
    2.          id: 1
    3. select_type: SIMPLE
    4.       table: customers2
    5.        type: ref
    6. possible_keys: idx_cu_id
    7.         key: idx_cu_id
    8.     key_len: 33
    9.         ref: const
    10.        rows: 1
    11.       Extra: Using where; Using index
    1. *************************** 2. row ***************************
    2.          id: 1
    3. select_type: SIMPLE
    4.       table: customers1
    5.        type: ALL
    6. possible_keys: NULL
    7.         key: NULL
    8.     key_len: NULL
    9.         ref: NULL
    10.        rows: 1
    11.       Extra: Using where; Using join buffer (Block Nested Loop)

    說明：
    兩個表中，cust_id字段的字符集未保持一致，無法使用索引。

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

    說明：
    執(zhí)行該語句會同步修改cust_id字段的CHARACTER SET為utf8。

  4. 重新執(zhí)行explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x'\G;查看執(zhí)行計劃。

    1. *************************** 1. row ***************************
    2.          id: 1
    3. select_type: SIMPLE
    4.       table: customers2
    5.        type: ref
    6. possible_keys: idx_cu_id
    7.         key: idx_cu_id
    8.     key_len: 33
    9.         ref: const
    10.        rows: 1
    11.       Extra: Using where; Using index
    1. *************************** 2. row ***************************
    2.          id: 1
    3. select_type: SIMPLE
    4.       table: customers1
    5.        type: ref
    6. possible_keys: idx_cu_id
    7.         key: idx_cu_id
    8.     key_len: 33
    9.         ref: const
    10.        rows: 1
    11.       Extra: Using where

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