阿里云慢SQL优化挑战大赛分析

【背景】

阿里云慢SQL优化挑战赛：
https://yq.aliyun.com/articles/136363?spm=5176.100240.searchblog.32.oYlhtr

【考点分析】

本次慢SQL优化挑战赛的题目全部来自于生产案例，将众多考察点揉合到一条SQL中，主要考虑了以下方面：

• 表设计：考察字符和数字字段定义，字符集大小写校验，时间字段存储。
• 驱动表：考察多表join时候最优的连接顺序。
• 索引优化：考察索引消除排序以，索引隐式转换，覆盖索引避免回表的问题。
• 执行计划：使用explain extended获取SQL执行计划中的异常点。

备注：

• 可以修改字段定义，可以修改SQL写法，可以添加创建索引，不得删除添加字段。
• 基础数据统一由阿里云来提供，测试环境可以是在RDS中或者自建的数据库中。
• 数据库版本：MySQL 5.6，数据库要求关闭query_cache。
• 最终的执行时间以阿里云RDS上执行时间为准。

【问题分析】

表结构

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) DEFAULT NULL,
  `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32744 DEFAULT CHARSET=utf8;
CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_name` varchar(100) DEFAULT NULL,
  `user_id` varchar(50) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `sales` bigint(20) DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32744 DEFAULT CHARSET=utf8;
CREATE TABLE `c` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) DEFAULT NULL,
  `order_id` varchar(100) DEFAULT NULL,
  `state` bigint(20) DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=458731 DEFAULT CHARSET=utf8;

待优化SQL：

select a.seller_id,a.seller_name,b.user_name,c.state 
from a,b,c where a.seller_name=b.seller_name  and b.user_id=c.user_id and c.user_id=17  and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE) AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE)  
order  by  a.gmt_create；

原始SQL每次执行需要190ms以上，所以我们开始进行优化。
1.优化的第一步就是分析SQL的执行计划：

2.上图执行计划中全部是type=ALL的全表扫描，需要创建合适的索引来避免全表扫描。同时注意到执行计划的表连接顺序是小表驱动大表：A->B->C，符合MySQL 优化器NLP的算法。所以我们在选择驱动表的时候，需要注意将小表作为驱动表。所以接下来就需要选择到底是那张表作为驱动表。

A表：
mysql> select count(*) from a where  a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
B表：
mysql> select count(*) from b;
+----------+
| count(*) |
+----------+
|    16385 |
+----------+
C表：
mysql> select count(*) from c where c.user_id=17;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

可以看到A，B，C三张表中，A和C表根据条件过滤都只有1条记录，B由于没有直接的筛选条件，所以按照原始记录进行计算。同时我们看到SQL的最后还需要按照gmt_create时间字段进行排序，所以我们优先考虑以A表作为驱动表来进行优化：A->B->C的顺序。

A表上创建索引：
Alter table a add index ind_a_gmt_create(gmt_create);
如果回表取数据量较大，可以考虑将关联字段和查询字段冗余到索引中
Alter table a add index ind_a_gmt_create(gmt_create,seller_name,seller_id);

B表上创建索引：
Alter table b add index ind_b_seller_name(seller_name);
如果回表取数据量较大，可以考虑将关联字段和查询字段冗余到索引中
Alter table b add index ind_b_seller_name1(seller_name,user_name,user_id);

C表创建索引：
Alter table c add index ind_c_user_id(user_id);
如果回表取数据量较大，可以考虑将关联字段和查询字段冗余到索引中
Alter table c add index ind_c_user_id(user_id,state);

再次查看执行计划：

添加完索引之后我们在看执行计划，发现A，B并没有按照我们的预设走索引，同时C表即使走了索引，但是Rows项还是很大，这到底是什么原因？
3.现在要分析为什么我们添加的索引没有走上，可以使用explain extended 的方式去查看执行计划更加详细的部分，比如索引为什么不能够使用。

从上面的warnings信息可以看到索引不能够使用的原因式因为字段类型或者校验规则出现了不一致的情况，所以我们回到上述题目中表结构的定义，发现了问题的根源：

a表：gmt_create使用了varchar来存储，在5.6支持更高时间精度后，将会发生隐式转换。

b表：a表和b表的seller_name字段在COLLATE定义上不一致，也会导致隐式转换。

c表：b表和c表的user_id字段都定义为了varchar，但是SQL传入为数字，也会导致隐式转换。

所以表结构定义该改为：

alter table  a modify column  gmt_create datetime;  
alter table  a modify column  seller_name varchar(100) ;
alter table  c modify column user_id  bigint;

经过上面的优化，执行时间基本在2ms左右，在返回数据量大的情况下，可以将查询关联字段冗余到索引中去避免回表访问。

4.在候选答案中发现了还有一种方法就是使用C->B->A的方式，也就是：

alter table c add index ind_c_user_id(user_id);
alter table  c modify column user_id  bigint;
alter table b add index ind_user_id(user_id);
alter table b modify column user_id bigint;
alter table  a modify column  seller_name varchar(100) ;
alter table a add index ind_a_seller_name(seller_name);

这种驱动表的顺序在本次数据环境下的性能也是很好的，但是执行计划会有排序，这样在大数据量排序的情况下性能会变得很差，所以建议还是需要消除排序。

最后看到后多候答案修改gmt_create字段为date类型，导致最终表的数据被截断，最终查询结果无法正常显示，非常可惜。

【案例扩展】

在这些年的工作之中，由于SQL问题导致的数据库故障层出不穷。于是将过去工作中遇到的SQL问题总结归类，还原问题原貌，给出分析和解决问题的思路，帮助用户在使用数据库的过程中能够少走一些弯路。

一、索引篇

索引问题是SQL问题中出现频率最高的，常见的索引问题包括：无索引，隐式转换，。上述所举的案例中就是因为访问表中的SQL无索引导致全表扫描，扫描大量的数据，应用请求变慢占用数据库连接，连接堆积很快达到数据库的最大连接数设置，新的应用请求将会被拒绝导致故障发生。隐式转换是指SQL查询条件中的传入值与对应字段的数据定义不一致导致索引无法使用。常见隐士转换如字段的表结构定义为字符类型，但SQL传入值为数字；或者是字段定义collation为区分大小写，在多表关联的场景下另外的关联字段却不区分大小写。隐式转换会导致索引无法使用，进而出现上述慢SQL堆积数据库连接数跑满的情况。

1.无索引案例：

表结构：

CREATE TABLE `user` (
……
mo bigint NOT NULL DEFAULT '' ,
KEY ind_mo (mo) 
……
) ENGINE=InnoDB;

慢SQL

SELECT uid FROM `user` WHERE mo=1377255 LIMIT 0,1

执行计划

mysql> explain  SELECT uid FROM `user` WHERE mo=1377255 LIMIT 0,1;
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
         rows: 707250
        Extra: Using where

从上面的SQL看到执行计划中ALL，代表了这条SQL执行计划是全表扫描，每次执行需要扫描707250行数据，这是非常消耗性能的，该如何进行优化？添加索引。

验证mo字段的过滤性

mysql> select count(*) from user where mo=1377255;
|   0    |

添加索引

mysql> alter table user add index ind_mo(mo);
mysql>SELECT uid FROM `user` WHERE mo=1377255 LIMIT 0,1;
Empty set (0.05 sec)

执行计划

mysql> explain  SELECT uid FROM `user` WHERE mo=1377255 LIMIT 0,1\G;
*************************** 1. row ***************************
                    id: 1
           select_type: SIMPLE
                 table: user
                  type: index
         possible_keys: ind_mo
                   key: ind_mo
                  rows: 1
                 Extra: Using where; Using index

2.隐式转换案例一：

表结构

CREATE TABLE `user` (
  ……
mo char(11) NOT NULL DEFAULT '' ,
KEY ind_mo (mo)
……
) ENGINE=InnoDB;

执行计划

mysql> explain extended select uid from`user` where mo=1377255 limit 0,1;
mysql> show warnings;
Warning1：Cannot use  index 'ind_mo' due to type or collation conversion on field 'mo'                                                                        
Note：select `user`.`uid` AS `uid` from `user` where (`user`.`mo` = 1377255) limit 0,1

如何解决：

mysql> explain   SELECT uid FROM `user` WHERE mo='1377255' LIMIT 0,1\G;
*************************** 1. row ***************************
                   id: 1
          select_type: SIMPLE
                 type: ref
        possible_keys: ind_mo
                  key: ind_mo
                 rows: 1
                Extra: Using where; Using index

上述案例中由于表结构定义mo字段后字符串数据类型，而应用传入的则是数字，进而导致了隐式转换，索引无法使用，所以有两种方案：
第一，将表结构mo修改为数字数据类型。
第二，修改应用将应用中传入的字符类型改为数据类型。

3.隐式转换案例二：

CREATE TABLE `test_date` (
    `id` int(11) DEFAULT NULL,
    `gmt_create` varchar(100) DEFAULT NULL,
    KEY `ind_gmt_create` (`gmt_create`)
) ENGINE=InnoDB AUTO_INCREMENT=524272;

5.5版本执行计划

mysql> explain  select * from test_date where gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 1 MINUTE) AND   DATE_ADD(NOW(), INTERVAL 15 MINUTE) ;
+----+-------------+-----------+-------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys  | key | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+----------------+----------------+---------+------+------+-------------+
|1|SIMPLE| test_date |range| ind_gmt_create|ind_gmt_create|303| NULL | 1 | Using where |

5.6版本执行计划

mysql> explain select * from test_date where gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 1 MINUTE) AND   DATE_ADD(NOW(), INTERVAL 15 MINUTE) ; 
+----+-------------+-----------+------+----------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys  | key  | key_len | ref | rows | Extra|
+----+-------------+-----------+------+----------------+------+---------+------+---------+-------------+
| 1 | SIMPLE| test_date | ALL | ind_gmt_create | NULL | NULL | NULL | 2849555 | Using where |
+----+-------------+-----------+------+----------------+------+---------+------+---------+-------------+
|Warning|Cannot use range access on index 'ind_gmt_create' due to type on field 'gmt_create' 

上述案例是用户在5.5版本升级到5.6版本后出现的隐式转换，导致数据库cpu压力100%，所以我们在定义时间字段的时候一定要采用时间类型的数据类型。

4.隐式转换案例三：

表结构

CREATE TABLE `t1` (
  `c1` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  KEY `ind_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
CREATE TABLE `t2` (
  `c1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  KEY `ind_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

执行计划

mysql> explain     select t1.* from  t2 left  join  t1 on t1.c1=t2.c1 where t2.c2='b';
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys |key| key_len | ref   | rows   | Extra    |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
| 1 | SIMPLE | t2 | ref  | ind_c2 | ind_c2 | 303     | const |    258 | Using wher |
| 1 |SIMPLE  |t1  |ALL   | NULL   | NULL   | NULL    | NULL  | 402250 |    |

修改COLLATE

mysql> alter table t1 modify column c1 varchar(100) COLLATE utf8_bin ;                
Query OK, 401920 rows affected (2.79 sec)
Records: 401920  Duplicates: 0  Warnings: 0

执行计划

mysql> explain   select t1.* from  t2 left  join  t1 on t1.c1=t2.c1 where t2.c2='b';
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref | rows  | Extra       |
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
|  1 | SIMPLE| t2| ref | ind_c2| ind_c2 | 303     | const   |    258 | Using where |
|  1 |SIMPLE| t1|ref| ind_c1  | ind_c1  | 303     | test.t2.c1 | 33527 |             |
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+

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

5.两个索引的常见误区：
误区一：对查询条件的每个字段建立单列索引，例如查询条件为：A=？and B=？and C=？
在表上创建了3个单列查询条件的索引ind_A(A)，ind_B(B)，ind_C(C)，应该根据条件的过滤性，创建适当的单列索引或者组合索引。
误区二：对查询的所有字段建立组合索引，例如查询条件为select A,B,C,D,E,Ffrom T where G=？
在表上创建了ind_A_B_C_D_E_F_G(A,B,C,D,E,F,G)；

6.最佳实践

那如何避免无索引及发生了隐式转换呢？主要有以下四个途径：
1） 在使用索引时，我们可以通过explain+extended查看SQL的执行计划，判断是否使用了索引以及发生了隐式转换。
2） 由于常见的隐式转换是由字段数据类型以及collation定义不当导致，因此我们在设计开发阶段，要避免数据库字段定义，避免出现隐式转换。
3） 由于MySQL不支持函数索引，在开发时要避免在查询条件加入函数，例如date(gmt_create)。
4） 所有上线的SQL都要经过严格的审核，创建合适的索引。

二、SQL改写篇

SQL优化在这里总结了三类常见的，包括分页优化、子查询优化

1.分页优化：

表结构

CREATE TABLE ` buyer ` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
……
  PRIMARY KEY (`id`) 
KEY ind_seller (sellerid)
) ENGINE=InnoDB;

SQL语句

select * from buyer where sellerid=100 limit 100000，5000

这条语句是普通的Limit M、N的翻页写法，在越往后翻页的过程中速度越慢，因为MySQL会读取表M+N条数据，M越大，性能越差。
我们通过采用高效的Limit写法，可以将上述语句改写成：

select t1.* from buyer t1,
(select id from buyer sellerid=100 limit 100000，5000) t2
where t1.id=t2.id;

从而避免分页查询给数据库带来性能影响。需要注意一点是，这里需要在t表的sellerid字段上创建索引，id为表的主键。

2.子查询优化：

典型子查询

SELECT first_name
  FROM employees
WHERE emp_no IN
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000);

MySQL的处理逻辑是遍历employees表中的每一条记录，代入到子查询中中去
改写子查询

SELECT first_name
FROM employees emp,
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
WHERE emp.emp_no = sal.emp_no;

3.最佳实践：

采用高效的 Limit 写法，避免分页查询给数据库带来性能影响；
子查询在5.1，5.5版本中都存在较大风险，将子查询改为关联，使用Mysql 5.6的版本，可以避免麻烦的子查询改写；
另外避免用 SELECT * 查询所有字段数据，只查询需要的字段数据。

三、参数优化篇

数据库中的参数配置对SQL执行速度快慢也有非常大的影响，常见的三个参数包括innodb_buffer_pool_size，tmp_table_size,query_cache_size,table_cache：

1.innodb_buffer_pool_size

作用：定义了innodb引擎缓冲池的大小，该缓冲池主要缓存了索引以及数据，如果SQL查询的数据在缓冲池中已经缓存，那就不需要从磁盘中读取，性能会得到快速的提升。笔者曾经看到有生产系统使用了mysql的默认配置128MB，导致数据库磁盘的使用率达到了100%。
建议：通常配置主机内存的70%~80%之间。

2.tmp_table_size

作用：该参数用于决定内部内存临时表的最大值，每个线程都要分配（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值），如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。
现象：如果复杂的SQL语句中包含了group by/distinct等不能通过索引进行优化而使用了临时表，则会导致SQL执行时间加长。
建议：如果应用中有很多group by/distinct等语句，同时数据库有足够的内存，可以增大tmp_table_size(max_heap_table_size)的值，以此来提升查询性能。

3.table_open_cache

作用：table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

现象：通常在设置table_open_cache参数的时候，在业务的高峰时期，检查open_Tables的值，如果open_Tables的值与table_open_cache的值相等，并且opened_tales的值在不断的增加，这个时候就需要对table_open_cache的值增加了，这个时候线程的状态：Opening tables 。

mysql> show profiles;
+———-+————+—————–+
| Query_ID | Duration   | Query           |
+———-+————+—————–+
|        1 | 0.09211525 | select * from d |
|        2 | 0.03659925 | select * from d |
|        3 | 0.22665400 | select * from d |
|        4 | 0.11063350 | select * from d |
|        5 | 0.06929725 | select * from d |
|        6 | 0.09054975 | select * from d |
|        7 | 0.15971375 | select * from d |
|        8 | 0.12960625 | select * from d |
|        9 | 0.22713975 | select * from d |
|       10 | 0.00124025 | select * from d |
+———-+————+—————–+
mysql> show profile cpu for query 4;
+———————-+———-+———-+————+
| Status               | Duration | CPU_user | CPU_system |
+———————-+———-+———-+————+
| starting             | 0.000198 | 0.001000 |   0.000000 |
| checking permissions | 0.000053 | 0.000000 |   0.000000 |
| Opening tables       | 0.000454 | 0.000999 |   0.001000 |
| init                 | 0.000059 | 0.000000 |   0.000999 |
| System lock          | 0.000055 | 0.000000 |   0.000000 |
| optimizing           | 0.000053 | 0.000000 |   0.000000 |
| statistics           | 0.000056 | 0.000000 |   0.000000 |
| preparing            | 0.000056 | 0.000000 |   0.000000 |
| executing            | 0.000052 | 0.001000 |   0.000000 |
| Sending data         | 0.000072 | 0.000000 |   0.000000 |
| end                  | 0.000053 | 0.000000 |   0.000000 |
| query end            | 0.000056 | 0.000000 |   0.000000 |
| closing tables       | 0.000056 | 0.000000 |   0.000000 |
| freeing items        | 0.000076 | 0.000000 |   0.000000 |
| cleaning up          | 0.000056 | 0.000000 |   0.000000 |

通过这个profile可以看到执行时间主要花费在Opening tables，这个时候问题就比较清楚了，我们看一下table_open_cache这个参数的值是否较小，结果这个RDS的table_open_cache只有100，而这个RDS却创建了上万张的表，进而导致了每次访问新的表的时候不得不重新打开，所以只需把table_open_cache调大即可解决目前的问题。

4.query_cache_size

作用：该参数用于控制MySQL query cache的内存大小；如果MySQL开启query cache，再执行每一个query的时候会先锁住query cache，然后判断是否存在query cache中，如果存在直接返回结果，如果不存在，则再进行引擎查询等操作；同时insert、update和delete这样的操作都会将query cahce失效掉，这种失效还包括结构或者索引的任何变化，cache失效的维护代价较高，会给MySQL带来较大的压力，所以当我们的数据库不是那么频繁的更新的时候，query cache是个好东西，但是如果反过来，写入非常频繁，并集中在某几张表上的时候，那么query cache lock的锁机制会造成很频繁的锁冲突，对于这一张表的写和读会互相等待query cache lock解锁，导致select的查询效率下降。
现象：数据库中有大量的连接状态为checking query cache for query、Waiting for query cache lock、storing result in query cache；
建议：RDS默认是关闭query cache功能的，如果您的实例打开了query cache，当出现上述情况后可以关闭query cache；当然有些情况也可以打开query cache，比如：巧用query cache解决数据库性能问题。

四、优化器篇

优化器根据统计信息以及优化器参数计算出SQL的执行计划，所以统计信息和优化算法决定着执行计划的优劣。常见优化器导致SQL执行出现缓慢的情况包括两种，统计信息不准确导致索引走错出现性能下降；数据库版本升级导致优化器参数发生变化，进而导致执行计划发生变化，性能可能变差。

1.优化器参数

数据库从5.5升级到5.6，一条sql在 5.5执行只需要零点几秒，而在5.6上需要10多秒。通过对比5.5版本和5.6版本的执行计划，发现5.6版本中的执行计划多出了block_nested_loop。
1) 5.5的优化器策略：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

2) 5.6的优化器策略：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,block_nested_loop=on…..

mysql> explain SELECT *
    ->   FROM t1 this_  
    ->   LEFT OUTER JOIN t2 item2_ ON this_.itemId = gameitem2_.id
    ->   LEFT OUTER JOIN t3 group3_ ON gameitem2_.groupId =gamegroup3_.id
            …………….
    ->   LEFT OUTER JOIN t8 leagueitem10_ ON leagueinfo7_.itemId =leagueitem10_.id
    ->  ORDER BY this_.id ASC LIMIT 20;
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------+
| id | select_type| table| type|possible_keys | key| key_len | ref   | rows   | Extra  |
+----+----------+---------------+--------+---------------+---------+---------+--------------+-----------------+
| 1 | SIMPLE| this_| ALL|NULL|NULL|NULL|NULL|257312|Using temporary; Using filesort |
| 1 |SIMPLE|item2_|eq_ref|PRIMARY|PRIMARY|4|this_.itemId|1|NULL                
| 1 | SIMPLE| group3_| ALL | PRIMARY| NULL| NULL| NULL | 6 |Using where; Using join buffer (Block Nested Loop) 

通过设置优化器参数，将block_nested_loop关闭，然后再分析执行计划走正确。

mysql>  set optimizer_switch='....block_nested_loop=off....';
 
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+------+-------+
| id | select_type | table |type | possible_keys | key     | key_len | ref | rows | Extra |
+----+---------------+---------------+--------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPL | this| index   | NULL  | PRIMARY | 4   | NULL |   20 | NULL  |
|  1 |SIMPLE| item2_| eq_ref | PRIMARY| PRIMARY | 4 | this_.itemId |  1 | NULL  |
|  1 |SIMPLE | group3_ | eq_ref | PRIMARY | PRIMARY | 4 | item2_.groupId |    1 | NULL |

所以在升级数据库版本的时候一定要做好功能和性能测试。

2.统计信息

CREATE TABLE `t1` (
  `c1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  KEY `ind_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> explain  select * from t1  where c1='m'; 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE| t1    | ALL  | NULL | NULL | NULL    | NULL | 804273 | Using where |

可以看到即使表中有c1字段的索引，但是SQL并没有走ind_c1这个索引，哪到底是什么原因导致的，我们首先来看一下c1字段的过滤性。

mysql> show index from t1;  
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------
| t1 |  1 | ind_c1   |   1 | c1  | A  |  0 |   NULL | NULL | YES  | BTREE   |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------

可以看到索引ind_c1的统计信息并没有更新，我们重新收集一下这个表的统计信息。

mysql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+

mysql> show index from t1;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------
| t1 | 1 | ind_c1|  1 | c1 | A   |  18 |   NULL | NULL   | YES  | BTREE      |         |

mysql> explain  select * from t1 where c1='m';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE| t1    | ref  | ind_c1| ind_c1 | 303     | const |    1 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

可以看到在重新收集统计信息后，ind_c1索引可以被使用到。

五．小结

SQL的优化是DBA／开发日常工作中不可缺少的一部分，记得在学生时期，曾经在ITPUB上看到一篇帖子，当时楼主在介绍SQL优化的时候，用一个公式来讲解他在做sql优化的时候遵循的原则：

   T=S/V(T代表时间，S代表路程，V代表速度)

S指SQL所需访问的资源总量，V指SQL单位时间所能访问的资源量，T自然就是SQL执行所需时间了;我们为了获得SQL最快的执行时间，可以根据公式定义上去反推：

• 在S不变的情况下，我们可以提升V来降低T：通过适当的索引调整，我们可以将大量的速度较慢的随机IO转换为速度较快的顺序IO；通过提升服务器的内存，使得将更多的数据放到内存中，会比数据放到磁盘上会得到明显的速度提升；采用电子存储介质进行数据存储和读取的SSD，突破了传统机械硬盘的性能瓶颈，使其拥有极高的存储性能;在提升V上我们可以采用较高配置的硬件来完成速度的提升；
• 在V不变的情况下，我们可以减小S来降低T：这是SQL优化中非常核心的一个环节，在减小S环节上，DBA可以做的可以有很多，通常可以在查询条件中建立适当的索引，来避免全表扫描；有时候可以改写SQl，添加一些适当的提示符，来改变SQL的执行计划，使SQL以最少的扫描路径完成查询；当这些方法都使用完了之后，你是否还有其他方案来优化？在阿里系的DBA职位描述中有条就是要求DBA需要深入的了解业务，当DBA深入的了解业务之后，这个时候能站在业务上，又站DB角度上考虑，这个时候在去做优化，有时候能达到事半功倍的效果。