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

在工作之中，由于SQL问题导致的数据库故障层出不穷，索引问题是SQL问题中常见的一种，例如：无索引，隐式转换，索引创建不合理。
使用没有创建索引的SQL访问数据库中的表时，系统会进行全表扫描。如果表的数据量很大，则扫描大量的数据，执行效率过慢，占用数据库连接，连接数堆积很快达到数据库的最大连接数设置，新的应用请求将会被拒绝导致故障发生。
隐式转换是指SQL查询条件中的传入值与对应字段的数据定义不一致导致索引无法使用。常见隐式转换如字段的表结构定义为字符类型，但SQL传入值为数字；或者是字段定义collation为区分大小写，在多表关联的场景下，其表的关联字段大小写敏感定义各不相同。隐式转换会导致索引无法使用，进而出现上述慢SQL堆积数据库连接数跑满的情况。

索引使用策略及优化



创建索引

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

使用索引注意事项

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

无索引案例



无索引案例一

1. 
   查看表结构。 mysql> 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

执行语句。

1. mysql> select * from customers where cust_zip = '44444' limit 0,1 \G;

执行计划。

1. mysql> explain select * from customers where cust_zip = '44444' limit 0,1 \G;

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

执行计划看到type为ALL，是全表扫描，每次执行需要扫描505560行数据，这是非常消耗性能的，那么下面将介绍优化方式。
添加索引。

1. mysql> alter table customers add index idx_cus(cust_zip);

执行计划。

1. mysql> explain select * from customers where cust_zip = '44444' limit 0,1 \G;

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，基于索引的等值查询，或者表间等值连接。

无索引案例二

1. 
   表结构同上案例相同，执行语句。 mysql> select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;

执行计划。

1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;

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

添加索引。

1. mysql> alter table customers add index idx_cu_zip_name(cust_zip,cust_name);

执行计划。

1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;

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

order by使用字段，而且字段应该是索引字段。

隐式转换案例



隐式转换案例一

1. mysql> explain select * from customers where cust_zip = 44444 limit 0,1 \G;

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

1. mysql> show warnings;
2. Warning： Cannot use range access on index 'idx_cus' due to type or collation conversion on field 'cust_zip'

上述案例中由于表结构定义cust_zip字段是字符串数据类型，而应用传入的是数字，导致了隐式转换，无法使用索引。

解决方案：

1. 将cust_zip字段修改为数字数据类型。
2. 将应用中传入的字符类型改为数据类型。

隐式转换案例二

1. 
   查看表结构。 mysql> show create table customers1;

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
6. mysql> show create table customers2;
7. CREATE TABLE `customers2` (
8.    `cust_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
9.    `cust_name` char(50) NOT NULL,
10.    KEY `idx_cu_id` (`cust_id`)
11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

执行语句。

1. mysql> select customers1.* from  customers2 left  join  customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';

执行计划。

1. mysql> explain select customers1.* from  customers2 left  join  customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x'\G;

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)

修改COLLATE。

1. mysql> alter table customers1 modify column cust_id varchar(10) COLLATE utf8_bin ;

执行计划。

1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;

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

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

字段的COLLATE一致后执行计划使用到了索引，所以一定要注意表字段的collate属性的定义保持一致。

总结


在使用索引时，我们可以通过explain查看SQL的执行计划，判断是否使用了索引以及发生了隐式转换，创建合适的索引。索引太复杂，创建需谨慎。