create table a (id int auto_increment,seller_id bigint,seller_name varchar(100) collate utf8_bin ,gmt_create varchar(30),primary key(id));
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');
待优化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 先说明这个优化题目主要考察下面5点： 1、BNL和NJL的区别 2、NJL的实现 3、DBA对于数据分布的观察 4、隐式转换索引不能使用 5、比较字符集不同索引不能使用
一、我们先来分别描述 1、BNL和NJL的区别 这个区别参考我的文章 http://blog.itpub.net/7728585/viewspace-2129502/ (从顺序随机I/O原理来讨论MYSQL MRR NLJ BNL BKA ) 简单的说BNL一般用于TYPE=INDEX以及TYPE=ALL的情况，因为被驱动表连接条件没有索引，而需要join buffer 将驱动表中待连接的 数据取出来(物理/逻辑 读取)，放到join buffer，主要目的在于减少被驱动表的驱动次数，从而提高效率，因为没有索引的情况 被驱动表扫描一次实在太慢了，这里的B就是BLOCK的意思. 而NJL一般用于被驱动表连接条件有索引的情况，通过索引上的ref或者eq_ref(取决于索引是否唯一)就理所当然的快很多很多，这个时候join buffer是不会 使用的，它只需要读取一条数据(物理/逻辑 读取)来驱动一次驱动表，因为驱动表连接条件有索引，自然就快了(索引定位回表) 2、NJL的实现 同样可以参考上面的文章，上面也大概说了一下，就不在废话了 3、DBA对于数据分布的观察 这一点是人为可以达到的，简单的说比如一个表有100条数据 99条为no=1 1条为no=2，那么我们 需要对这个有所警觉，如果这个表示用作驱动表那么no=2的时候效果要远远好于no=1。这道题也有 这个因素 明显and c.user_id='17' 只有一条数据 4、隐式转换索引不能使用 这个不管是MYSQL还是ORACLE都有的问题， ORACLE会显示给出来to_char(id)='1'之类的 MYSQL中会有如下类似的警告 | Warning | 1739 | Cannot use ref access on index 'user_id' due to type or collation conversion on field 'user_id' | | Warning | 1739 | Cannot use range access on index 'user_id' due to type or collation conversion on field 'user_id'
比如这里的 c.user_id=17 而 user_id 是varchar类型不是int类型 又比如这里的 a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) 这里 gmt_create varchar(30) 居然也是varchar 擦！！ 5、比较字符集不同索引使用异常 这个关于字符串的比较问题我已经在文章里面有所描述 http://blog.itpub.net/7728585/viewspace-2141914/ 简单的说这里 a.seller_name=b.seller_name a.seller_name 比较字符集是utf8_bin 区分大小写 而 b.seller_name 是不区分大小写的这是默认的。 他们之间做join必然被驱动表用不到索引使用异常。(innodb 可以icp) 也会有类似如下的警告： Cannot use ref access on index 'seller_name' due to type or collation conversion on field 'seller_name'
