本文主要是介绍mysql为啥选错索引_dnf助手_MySQL 选错索引的缘故原由？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
使用Flutter开发的抖音国际版pubg辅助
MySQL 中，可以为某张表指定多个索引，但在语句详细执行时，选用哪个索引是由 MySQL 中执行器确定的。那么执行器选择索引的原则是什么，以及会不会泛起选错索引的情形呢？
先看这样一个例子：
建立表 Y，设置两个通俗索引, 建立一个存储历程用于插入数据。
MySQL: 5.7.27, 隔离级别: RR
CREATE TABLE `Y` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB；
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into Y (`a`,`b`) values(i, i);
set i=i 1;
end while;
end;;
delimiter ;
call idata();
查看如下事务：
Session A
Session B
start transaction with consistent snapshot;
delete from t;
call idata();
explain select * from Y where a between 10000 and 20000;
explain select * from Y force index(a) where a between 10000 and 20000;
commit;
若是单独执行 Session B 中 select * from Y where a between 10000 and 20000;，毫无疑问会选择 a 这个索引。
但若是安装 Session A，Session B 的顺序执行，发现索引的选择如下：
可以发现，在 Session B 的场景下，执行器却没有选择 a 所在的索引，而是选择基于主键索引的全表扫描。
set long_query_time=0;
将慢查询日志打开，并将阙值设为 0. 在纪录的日志中，
可以发现 MySQL 并没有选择 a 所在的索引，同时花费了更长的时间。
这样看，MySQL 的优化器不一定每次都能选择合适的索引。想要明白泛起该征象的缘故原由，就要从优化器的选择逻辑提及。
优化器
MySQL 中优化器的目的就是找到一个最优的执行方案，从而用最小的价值去执行语句。
优化器在选择索引时，主要会思量如下的因素：
扫描的行数：扫描的行数越少，就证实接见磁盘数据的次数越少，消耗的 CPU 资源就越少。
有没有涉及到暂且表
排序
关于扫描行数的确定
盘算索引的基数
MySQL 在执行语句前，实在并不能准确的盘算出扫描的行数，而是通过数学统计信息来估算纪录数。这个统计信息被称为索引的“区分度”，在索引上差别的值越多，区分度就越高。在一个索引上差别值的个数，称为“基数”。基数越大，索引的区分度越好。
这里的 Cardinality 就是索引的基数，但基数并不是完全准确的。MySQL 是在获取基数时，现实上是接纳采样统计的方式。
盘算时，会选择 N 个数据页，并统计这些页面上的差别值，获得一个平均值，然后乘以该索引的页面数，然后获得的就是索引的基数。
在 MySQL 中，有两种存储索引的方式，可通过设置 innodb_stats_persistent 来切换：
on 时：示意统计信息会持久化存储，默认 N 为 20，M 为 10.
off 时，统计信息仅会存储在内存中，默认 N 为 8，M 为 16.
由于表中数据是不停转变的，以是当更新的值跨越 1/M 时，会自动触发索引统计。
但需要注重的是，由于是采样统计，以是基数的值不是准确的。
预估扫描行数的错误,和平精英免费透视辅助,
之前看到，执行 Select * from Y where a between 10000 and 20000 预估的行数是 100015，这个是能明白的，由于走的是全表扫描。
之后执行 select * from Y force index(a) where a between 10000 and 20000 预估的行数是 37116，这个就不能明白了，理想的情形下应该是 10001 行 (需要遍历到 20001)。
而且更奇怪的是，虽然 37116 行的预估行数不太合理，但也远小于全表扫描的 100015，为什么优化器照样选择全表扫描呢？
首先先看第二个问题，选择 100015 的缘故原由是由于若是使用索引 a 的话，除了需要在 a 索引扫描外，还需要回表，主键索引上的查询价值，优化器也需要算进去，以是选择了全表扫描。
这时再看第一个问题，为什么没有获得准确的行数。这个就和一致性视图有关了，首先 Session A 中，开启了一致性视图，并没有提交。之后的 Session 清空了 Y 表后，又重新建立了相同的数据，这时每行数据都有两个版本，旧版本是 delete 前的数据，新版本是符号为删除的数据。以是索引 a 上的数据实在有两份。也就造成了行数的预估错误。
mysql 是通过符号删除的方式来删除纪录的，并不是在索引和数据文件中真正的删除。而且由于一致性读的保证，不能删除 delete 的空间，再加上 insert 的空间。导致统计信息有误。
选用错误索引的解决办法
对于行数预估错误的情形， 可接纳如下的方式：
若是遇到 EXPLAIN 和预估的行数，数值相差较大时，可以通过analyze table 来重新统计索引信息。
直接通过 force index 强制指定需要使用的索引，不让优化器举行判断。但使用 force 也可能带来一些问题：
迁徙数据库时，语法不支持
不容易调换而且不太利便，由于选错索引的情形一样平常不会经常发生，在生产环境泛起问题后，才需要改代码，但还需要重新举行上线测试，部署。
优化 SQL 语句，指导优化器使用准确的索引
再看一个类似的例子：
先来看一下这句 SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;
在执行这句话时，可以选索引 a，也可以选索引 b. 我们知道，每个索引对应了一颗B 树。这里由于取得是 a 和 b 的交集，若是选用索引 a 的话，需要遍历 1 - 10001 行。选用索引 b 需要遍历 50000 - 100001 行。理论上来说，应该选择 a 作为索引，可以优化器又偏偏选择了 b 作为索引。
这里选择 b 作为索引的缘故原由，是由于优化器看到了后面的 order by 语句，由于要排序，而 B 树自己就是有序的，省去了排序的历程，以是选择了 b 作为索引。
但从现实的执行时间来看，索引 a 执行时间更短，以是这里 MySQL 又选择了错误的索引。
我们可以将上述语句中 order by b limit 改为 order by b,a limit 1 这时由于 a，b 索引都要排序，扫描的行数就成为执行器主要参考的条件，指导选择准确的索引。
这样做的条件一定要保证执行的逻辑效果是一致的，比如在 limit 1 的情形下，order by b,a 和 order by b 的效果一致，若是换成 limit 100 就不一定了。
另有一种改法，select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
现在可以看到，优化器选择了合适的索引。缘故原由在于 limit 100 让优化器以为，使用索引 b 的价值较高，进而选择索引 a. 实在就是通过 limit 100 诱导优化器做出选择。
调整索引
能否找到更优，更合适的索引，或者行使索引的原则，删除一些不必要的索引。
总结
现在我们知道，MySQL 在选择索引时，是会泛起错误的情形的。优化器选择索引的原则主要有三个，扫描的行数，是否存在暂且表，以及排序。行数的扫描，主要和基数有关，而基数的统计则是通过统计抽样决议的，进而预估的行数可能会是不准确的。
在遇到扫描的行数不准确时，可以通过 analyze table 来重新统计表的信息，通过 force index 强制指定索引，或通过手动改变 sql 的语义，诱导优化器做出准确的选择。,dnf卡盟MySQL索引及优化(1)存储引擎和底层数据结构
这篇关于mysql为啥选错索引_dnf助手_MySQL 选错索引的缘故原由？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
