某、人 置顶2018-12-05今天这个问题不是特别明白为什么。session A开启了一致性读,session B delete或者insert,之前记录都已经放进了undo了。二级索引的记录也写进了redo和change buffer,应该说删除了索引页也不影响session A的重复读。估计是开启了一致性读之后,在这个事务执行期间,不能释放空间,导致统计信息变大。还是需要老师解释下具体的细节
今天有两个问题,想请教下老师
1.我的理解是由于B是查找(50000,100000),由于B+树有序,通过二分查找找到b=50000的值,从50000往右扫描,一条一条回表查数据,在执行器上做where a(1,1000)的筛选,然后做判断是否够不够limit的数,够就结束循环。由于这里b(50000,100000)必然不存在a(1,1000),所以需要扫描5W行左右.但是如果把a改为(50001,51000),扫描行数没有变。那么是因为优化器给的扫描行数有问题还是执行器没有结束循环？为什么不结束循环?
(好像rows能直观展示limit起作用,必须在执行器上过滤数据,不能在索引上过滤数据,不知道为什么这样设计)
2.假设b上数据是会有很多重复的数据,b的最大值也存在多行重复
select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b desc limit 1;
这里倒序去扫描b索引树,选取的是b值最大,id值为一个固定值(既不最大也不最小)
select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b desc limit 1;
由于这里选取的是a索引,排序不能用到索引,只能用优化排序.选取的是b值最大,id值最小那一行
这就是典型的两条相同的sql,但是索引选择的不同,出现的数据不一致。
所以如果是order by b,a就可以避免这种情况的引起的不一致,也可以避免堆排序造成的不一致
但是如果是asc没有出现这种情况。这里出现不一致,应该还不是由于堆排序造成的。这是什么原因造成的？展开作者回复: 1. 好问题，而且你做了个不错的对照实验。是的，加了limit 1 能减少扫描多少行，其实优化器也不确定，【得执行才知道】，所以显示的时候还是按照“最多可能扫多少行”来显示。
2. 你这个例子里，如果确实是按照b扫描了，应该肯定是ID最大值呀，除非ID最大的那个记录，a条件不满足。但是一定是“满足a条件里面最大的那个ID的”，你再验证下。
而如果是用了a, 那就有临时表排序，临时表排序有三种算法，还分内存还是磁盘临时表… 这里展开不了了，后面《order by是怎么工作的》这篇会讲。 31
路过 置顶2018-12-05老师，关于本章中的“基数”（cardinality）问题。既然已经为列a创建了索引，即有专门的数据页存放索引。遍历索引是很快的，从而得到“基数”的值应该很快呀。为何要到原始的数据页中，找N页，统计上面不同的值呢？有点多此一举啊。如果这样操作，会导致信息不准确，比如本来一个页中有50条数据，后来其中20条数据被删除了，空间没有被释放，这导致统计的信息就发生偏差。基数信息就更不准确了。
从原始页中计算“基数”，是不是考虑到索引页中的数据具有滞后性，即更新了表中数据，要过一会才更新索引页？
请老师指正，谢谢！作者回复: 啊，误会了，确实是哪个索引的基数就是在哪个索引树上拿的。
你的理解是对的，我文中也是这个意思哦😓 12
bowenz 置顶2018-12-05在5.7.21 percona 版本实验，未出现案例1的情况 。
dev02> select @@global.tx_isolation,@@tx_isolation,version(),"session A";
+-----------------------+-----------------+---------------+-----------+
| @@global.tx_isolation | @@tx_isolation | version() | session A |
+-----------------------+-----------------+---------------+-----------+
| REPEATABLE-READ | REPEATABLE-READ | 5.7.21-20-log | session A |
+-----------------------+-----------------+---------------+-----------+
dev02> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
dev02> commit;
Query OK, 0 rows affected (0.00 sec)
dev02> select now() ;
+---------------------+
| now() |
+---------------------+
| 2018-12-04 22:03:48 |
+---------------------+
1 row in set (0.00 sec)
dev02> select @@global.tx_isolation,@@tx_isolation,version(),"session B";
+-----------------------+-----------------+---------------+-----------+
| @@global.tx_isolation | @@tx_isolation | version() | session B |
+-----------------------+-----------------+---------------+-----------+
| REPEATABLE-READ | REPEATABLE-READ | 5.7.21-20-log | session B |
+-----------------------+-----------------+---------------+-----------+
1 row in set, 2 warnings (0.00 sec)
dev02> delete from t;
Query OK, 100000 rows affected (0.51 sec)
dev02> call idata();
Query OK, 1 row affected (2 min 38.34 sec)
dev02> select now();
+---------------------+
| now() |
+---------------------+
| 2018-12-04 22:03:58 |
+---------------------+
1 row in set (0.00 sec)
dev02> explain select * from t where a between 10000 and 20000;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | t | NULL | range | a | a | 5 | NULL | 10001 | 100.00 | Using index condition |展开作者回复: Session A提交早了… 从上到下按照时间顺序执行哈
7- 2018-12-06趁着答案公布之前的最后时间,再来尝试性答一下这个题
1.为什么没有session A,session B扫描的行数是1W
由于mysql是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除。
如果delete和insert中间的间隔相对较小,purge线程还没有来得及清理该记录。
如果主键相同的情况下,新插入的insert会沿用之前删除的delete的记录的空间。
由于相同的数据量以及表大小,所以导致了统计信息没有变化
2.为什么开启了session A,session B扫描行数变成3W
由于session A开启了一致性读,目的为了保证session A的可重复读,insert只能
另起炉灶,不能占用delete的空间。所以出现的情况就是delete虽然删除了,但是
未释放空间,insert又增加了空间。导致统计信息有误展开作者回复: 👍🏿
4 108 
2018-12-12我试了几遍 也是没有复现选错索引
A会话
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.01 sec)
mysql> select @@version
-> ;
+------------+
| @@version |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)
mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
B会话
mysql> delete from t;
Query OK, 100000 rows affected (2.58 sec)
mysql> call idata();
Query OK, 1 row affected (24.32 sec)
mysql> explain select * from t where a between 10000 and 20000;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | t | NULL | range | a | a | 5 | NULL | 10001 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
为啥呢？展开 3 32
2018-12-05现学现用 今天有个500万的表 分页查询特别慢。
select * from table where create_time and create_time>=时间戳 and create_time<=时间戳
and subtype='xx' and type='xx' and company_id =x order by create_time limited 90,30 ;
已经建立了组合索引 union_index包括字段 create_time subtype type company_id
但是 explain 发现竟然走了create_time 的索引
语句里加了一个use index(union_index) ，立马好了
真正的解决了客户的实际问题啊。 感谢老师展开作者回复: 👍🏿
而且发评论的时候还做了很细致地脱敏，赞 5 19
2018-12-05公司测试机器IO性能太差，插十万条要27分钟，做这个文章的实验要1个小时以上作者回复: ………
不会吧，插入10万条27分钟… 你把innodb_flush_log_at_trx_commit 和 sync_binlog都设置成0试试 3 16
2018-12-05问题的思考：
我理解 session A 开启的事务对 session B的delete操作后的索引数据的统计时效产生了影响，因为需要保证事务A的重复读，在数据页没有实际删除，而索引的统计选择了N个数据页，这部分数据页不收到前台事务的影响，所以整体统计值会变大，直接影响了索引选择的准确性；作者回复: 👍🏿
2 16
2018-12-09把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
1.
mysql如何判断一个查询的扫描行数?
2.
索引基数如何计算? 通过哪个参数可以设置索引统计的存储方式?
3.
可以重新统计索引信息的命令是什么?
4.
如何定位索引选择异常这样的问题?
5.
索引选择异常的问题可以有哪几种处理方式?展开 15
2019-03-161、sessionA：start transaction with consistent snapshot;
2、sessionB：delete from t;
3、sessionB：call idata();
4、sessionB：explain select * from t where a between 10000 and 20000;
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 10000 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
5.6.43没有复现 案例1的情况展开 1 9
2018-12-05图十下面第二段
现在 limit b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。
应该是order by b,a吧
另外有个问题请教林老师，根据经验大表增加索引的时候比较慢，这个是理解的，但是删除索引的时候能做到秒删，这个什么原理呢？作者回复: 是，已经修改了，谢谢。
删除的时候是标记删除，所以很快。
建索引是要扫描数据和真正生成索引树，是会慢些 9
2019-02-01假如要查 A in () AND B in (), 怎么建索引?作者回复: 好问题
where A in (a,b,c) AND B in (x,y,z)
会转成
(A=a and B=x) or (A=a and B=y) or (A=a and B=z) or
(A=b and B=x) or (A=b and B=y) or (A=b and B=z) or
(A=c and B=x) or (A=c and B=y) or (A=c and B=z) 4 8- 2018-12-05谢谢老师的回答.
1.确实有时候感觉虽然显示了扫描行数比较多,但是limit应该是起了作用的,从执行时间就能看出来。
2.失误,确实是应该满足a条件id最大的。
临时表排序之前了解过一点,1.常规排序。2.优化排序 3.优先队列排序(堆排序)
由于选取的字段没超过max_length_for_sort_data,所以选取的是优化排序,只需要进行一次I/O。 7 
2019-02-26谢谢老师的解答，我之前一直以为这个操作也是在存储层进行的。
那执行器调用存储层的接口是不是只能获取到最原始的数据，后续的加工，比如order，join和group操作也都是在执行器里进行的吗？对应的buffer和内存临时表也都是server层的东西？作者回复: 是的，你提到的这些，都在server层
很早之前连过滤数据都在server层，后来有了index condition pushdown下推了一点到引擎层 6
2018-12-24按照顺序执行，没能复现选错索引 版本5.6.33。
Server version: 5.6.33 MySQL Community Server (GPL)
# Time: 181224 11:32:26
# User@Host: root[root] @ localhost [] Id: 20852
# Query_time: 0.012308 Lock_time: 0.000124 Rows_sent: 10001 Rows_examined: 10001
SET timestamp=1545622346;
select * from t where a between 10000 and 20000;
# Time: 181224 11:32:32
# User@Host: root[root] @ localhost [] Id: 20852
# Query_time: 0.351406 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 100000
SET timestamp=1545622352;
delete from t;
# Time: 181224 11:32:37
# User@Host: root[root] @ localhost [] Id: 20852
# Query_time: 3.234390 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1545622357;
call idata();
# Time: 181224 11:32:40
# User@Host: root[root] @ localhost [] Id: 20852
# Query_time: 0.014459 Lock_time: 0.000160 Rows_sent: 10001 Rows_examined: 10001
SET timestamp=1545622360;
select * from t where a between 10000 and 20000;展开 6
2018-12-17老师，你好。
我按照文档中的例子。没有复现选择错索引呢？
select @@global.tx_isolation,@@tx_isolation,version(),"session A";
start transaction with consistent snapshot;
select now() ;
select @@global.tx_isolation,@@tx_isolation,version(),"session B";
delete from tt;
call idata();
select now();
explain select * from tt where a between 10000 and 20000;
select @@global.tx_isolation,@@tx_isolation,version(),"session A";
commit;展开 5
2018-12-11通过这一讲的学习，阅读完本文，其实核心点就是优化器的索引选择，从而牵引出索引统计的更新机制，以及通过Analyze tabe来进行分析索引。当然有时避免不了可以选择简单粗暴的用Force index来进行索引选择。
下半部分老师通过实践上的问题给出了索引选择上的方案，一、通过Force index强行选择一个索引。二、通过修改SQL语句来修改索引，三、通过提供一个更合适的索引来给优化器做出选择。
学习到这里，我想写通过认真阅读老师的每一篇文章，至少我现在开始思考老师每篇文章真正的出发点是什么，然后通过老师的每一步细节来搞懂技术上的问题，不敢说有多大进步，至少我相信我在不久之后可以做数据库技术这件事，感谢和老师一同进步成长。 5- 2018-12-17使用navicate for mysql .
开启了2个查询编辑器。
在1 编辑器中 开启长事务：start TRANSACTION with CONSISTENT SNAPSHOT ;
然后到2编辑器中， 进行了
delete from t ;
call idata();
explain select *from t where a BETWEEN 1000 and 2000 ;
依然用了 a索引。
包括下一篇的例子，我也看了。
也是按照那个方式模拟的，差别就是我用的navicat ，为什么就无法重现呢。。。。展开 1 4 
2018-12-08老师，原文中：在这个例子里，我们用 limit 100 让优化器意识到，使用b索引代价是很高的。
问题：为什么limit 100时候，使用b索引代价高呢？和limit 1相比，赶紧没有什么质的变化啊作者回复: 其实这篇主要就是说优化器的bug😄 bug嘛就没什么道理😓
4
2018-12-05merge那段的解释明白了change buffer操作逻辑。即change buffer变化与数据块变化是分开的，最初redo中记录的只是change buffer的变更，因为还未应用到数据块上。而merge后redo记录的是数据块、change buffer的变更。
是这样吧？😄作者回复: 准确。
悄悄地我涨了一点信心😄 2 4