MYSQL 阿里的一个sql优化疑问

发布时间：2022-06-28 11:07:19 所属栏目：MySql教程来源：互联网

导读：今天研究了一天innodb事物，临近下班了同事田兴春告诉我有个阿里发出来的优化题，并且把建表和语句给我了，并且告诉我语句里面有隐式转换昨天群里也有人说这道题但是一直没空看，刚好这会没事就看了一下，整个脚本如下：点击(此处)折叠或打开数据准备：

　　今天研究了一天innodb事物，临近下班了同事田兴春告诉我有个阿里发出来的优化题，并且把建表和语句给我了，并且告诉我语句里面有隐式转换
　　昨天群里也有人说这道题但是一直没空看，刚好这会没事就看了一下，整个脚本如下：

　　点击(此处)折叠或打开

　　数据准备：

　　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'

　　二、优化原则问题
　　我们知道基本所有的语句执行算法逻辑都在MYSQL层次，INNODB只是负责将数据通过几种方式
　　(PAGE_CUR_G，PAGE_CUR_GE，PAGE_CUR_L，PAGE_CUR_LE)扫描出来，递送给MYSQL层次进行处理，这之间存在扫描拿到
　　innodb record-->innodb tuple-->mysql record的一个转换的过程，这个步骤大部分被标记为sending data过程
　　(update/delete为updating)，那么我们就有必要减少中间结果集的产生，来减少整个从innodb拿数据到MYSQL层的
　　整个数据量。这里以NJL的优化原则为列解释，因为这道题就是这个目的

　　1、减少NJL驱动结果集的数据
　　这事显而易见的，减少驱动次数自然就减少了数据在innodb和mysql之间的传递
　　2、被驱动表的索引唯一性要尽量好
　　这个问题稍微难理解一点，但是仔细想一下也没什么，如果被驱动表索引唯一性更好，那么通过索引回表的次数就少了，
　　这里可以通过rows和filter进行大概判断，大概是因为他们本来就不准。
　　曾经我们就有一个列子也是同事田兴春和我一起看的。一个被驱动表有两个链接条件，一个索引唯一性很差，而唯一性好的连接
　　列上没有索引，我们在唯一性好的列上建立了索引性能马上提升了。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

数据库中间件ProxySQL	zabbix 一次悲惨的分区
mysql如何实现数据备份	建立数据表主键外键