###第十二部分：MySQL数据库SQL优化案例
#https://yq.aliyun.com/articles/183749?spm=a2c4e.11154000.rtdmain.1.97ed46c9TynVUK
#本次慢SQL优化挑战赛的题目全部来自于生产案例，将众多考察点揉合到一条SQL中，主要考虑了以下方面：
表设计：考察字符和数字字段定义，字符集大小写校验，时间字段存储。
驱动表：考察多表join时候最优的连接顺序。
索引优化：考察索引消除排序以，索引隐式转换，覆盖索引避免回表的问题。
执行计划：使用explain extended获取SQL执行计划中的异常点。
#备注：
可以修改字段定义，可以修改SQL写法，可以添加创建索引，不得删除添加字段。
基础数据统一由阿里云来提供，测试环境可以是在RDS中或者自建的数据库中。
数据库版本：MySQL 5.6，数据库要求关闭query_cache。
最终的执行时间以阿里云RDS上执行时间为准。
#关闭查询缓存
SET GLOBAL query_cache_size = 0; SET GLOBAL query_cache_type = 0;
#初始化表结构
DROP TABLE a; DROP TABLE b; DROP TABLE c; 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;
#初始化数据
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100000,'uniqla','2017-01-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100001,'uniqlb','2017-02-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100002,'uniqlc','2017-03-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100003,'uniqld','2017-04-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100004,'uniqle','2017-05-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100005,'uniqlf','2017-06-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100006,'uniqlg','2017-07-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100007,'uniqlh','2017-08-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100008,'uniqli','2017-09-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100009,'uniqlj','2017-10-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100010,'uniqlk','2017-11-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100011,'uniqll','2017-12-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100012,'uniqlm','2018-01-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100013,'uniqln','2018-02-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100014,'uniqlo','2018-03-01');
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100015,'uniqlp','2018-04-01');
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) SELECT seller_id,seller_name,gmt_create FROM a;
INSERT INTO a (seller_id,seller_name,gmt_create) VALUES (100016,'uniqlq',NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqla','1','a',1,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlb','2','b',3,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlc','3','c',1,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqld','4','d',4,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqle','5','e',5,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlf','6','f',1,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlg','7','g',7,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlh','8','h',1,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqli','9','i',1,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlj','10','j',15,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlk','11','k',61,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqll','12','l',31,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlm','13','m',134,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqln','14','n',1455,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlo','15','o',166,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('niqlp','16','p',15,NOW());
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) SELECT seller_name,user_id,user_name,sales,gmt_create FROM b;
INSERT INTO b (seller_name,user_id,user_name,sales,gmt_create) VALUES ('uniqlq','17','s',109,NOW());
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 21,1,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 22,2,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 33,3,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 43,4,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 54,5,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 65,6,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 75,7,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 85,8,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 95,8,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 100,8,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 150,8,0 ,NOW() );
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) SELECT user_id,order_id,state,gmt_create FROM c;
INSERT INTO c (user_id,order_id,state,gmt_create) VALUES( 17,8,0 ,NOW() );
#待优化的SQL
EXPLAIN
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;
#上图执行计划中全部是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表：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;
