异常500,程序异常,Exception
当前位置:Exception500 » MySQL » 第十二部分:MySQL数据库SQL优化案例

第十二部分:MySQL数据库SQL优化案例

来源:exception500.com    发布时间:2020-05-15 16-05-26    浏览次数:52次

###第十二部分: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;



[关键词: 阿里云SQL优化 ]

软件开发 程序错误 异常 500错误 Exception Copyright© 2019-2020  Exception500 版权所有  【蜀ICP备15020376号-9】  网站地图