为应用选择和创建最佳索引，加速数据读取

背景信息

在数据库使用过程中，由SQL问题导致的数据库故障层出不穷，其中索引问题是SQL问题中常见的一种，例如：无索引，隐式转换，索引创建不合理。

• 无索引：使用没有创建索引的SQL访问数据库中的表时，系统会进行全表扫描。如果表的数据量很大，则SQL执行效率会过慢，同时占用数据库连接数，当达到数据库的最大连接数限制时，新的应用请求将会被拒绝导致出错。

• 隐式转换：指SQL查询条件中的传入值与目标字段的数据类型不一致导致索引无法使用，引发慢SQL堆积导致数据库连接数超出限制。
  常见隐式转换如：字段的表结构定义为字符类型，但SQL传入值为数字；或者是字段定义collation为区分大小写，在多表关联的场景下，其表的关联字段大小写敏感定义各不相同。

注意事项

• 使用like关键字时，前置%会导致索引失效。
• 使用null值会被自动从索引中排除，索引一般不会建立在有空值的列上。
• 使用or关键字时，or左右字段如果存在一个没有索引，有索引字段也会失效。
• 使用!=操作符时，将放弃使用索引。因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描。
• 不要在索引字段进行运算。
• 在使用复合索引时，最左前缀原则，查询时必须使用索引的第一个字段，否则索引失效；并且应尽量让字段顺序与索引顺序一致。
• 避免隐式转换，定义的数据类型与传入的数据类型保持一致。

索引使用策略

• 在经常查询而不经常增删改操作的字段加索引。
• order by与group by后应直接使用字段，而且字段应该是索引字段。
• 一个表上的索引不应该超过6个。
• 索引字段的长度应固定，且不宜过长。
• 索引字段不宜有过多重复。
• 在过滤性高的字段上加索引。

无索引优化案例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语句的执行计划。

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

   说明：
   从执行计划可以看到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

   说明：
   此时type已变更为ref，即基于索引的等值查询或者表间等值连接，扫描行数为4555行，大幅优化了查询速度。

无索引优化案例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语句的执行计划。

            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

隐式转换优化案例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语句的执行计划。

          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;查询上一个语句执行后的警告信息。

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

   说明：
   由于cust_zip字段为字符串类型，而应用传入的是数字，导致隐式转换，无法使用索引。

4. 可通过如下两种方案优化：

   • 将cust_zip字段的数据类型修改为数字类型。
   • 将应用中传入的数据类型修改为字符串类型。

隐式转换优化案例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语句的执行计划。

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

   说明：
   表字段的COLLATE一致后执行计划成功使用了索引。