【内容或版权原因，部分内容未显示】

目录

笔记

  • 索引基数

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。注意这个基数并不准确。

  • MySQL 是怎样得到索引的基数的

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

  • 为什么优化器会选择错索引？

mysql优化器只是在大部分情况下会选择最优的索引和执行顺序，但还是存在一些微小的信息导致优化器误判最优选择。比如上面说的基数，比如文章中说的sessionA开启事务和sessionB的delete操作后，出现索引a基数统计出现偏差，为了A的重复读，在内存数据也并没有实际删除，而索引的统计选择了其中部分数据页进行基数统计，导致整体统计值偏大，最后影响了优化器对索引的选择。

  • 如何解决索引基数偏差

    analyze table t

可以用来重新统计索引信息，如果只是索引统计不准确，通过analyze可以解决很多问题。

  • 善用explain 分析

  • 索引选择异常处理

    • 强行选择索引 force index

      手动选择索引，但在考虑索引可能变更及平台迁移，我们可能不希望在代码里补充force index这样的代码

    • 优化语句，引导优化器使用期望的索引

      比如order by b limit 1，优化为 order by b,a limit 1,可以引导优化器判断a及b对应的最优索引，而不会仅仅只考虑b索引

    • 新建更合适的索引，或删除被误用的索引

原文

建议底部去阅读原文，还有丁奇老师的问答回复更是精华之处。

前面我们介绍过索引，你已经知道了在MySQL中一张表其实是可以支持多个索引的。但是，你写SQL语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由MySQL来确定的。

不知道你有没有碰到过这种情况，一条本来可以执行得很快的语句，却由于MySQL选错了索引，而导致执行速度变得很慢？

我们一起来看一个例子吧。

我们先建一个简单的表，表里有a、b两个字段，并分别建上索引：

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB；

然后，我们往表t中插入10万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000)。

我是用存储过程来插入数据的，这里我贴出来方便你复现：

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i

接下来，我们分析一条SQL语句：

mysql> select * from t where a between 10000 and 20000;

你一定会说，这个语句还用分析吗，很简单呀，a上有索引，肯定是要使用索引a的。

你说得没错，图1显示的就是使用explain命令看到的这条语句的执行情况。

图1 使用explain命令查看语句执行情况从图1看上去，这条查询语句的执行也确实符合预期，key这个字段值是’a’，表示优化器选择了索引a。

不过别急，这个案例不会这么简单。在我们已经准备好的包含了10万行数据的表上，我们再做如下操作。

图2 session A和session B的执行流程这里，session A的操作你已经很熟悉了，它就是开启了一个事务。随后，session B把数据都删除后，又调用了 idata这个存储过程，插入了10万行数据。

这时候，session B的查询语句select * from t where a between 10000 and 20000就不会再选择索引a了。我们可以通过慢查询日志（slow log）来查看一下具体的执行情况。

为了说明优化器选择的结果是否正确，我增加了一个对照，即：使用force index(a)来让优化器强制使用索引a（这部分内容，我还会在这篇文章的后半部分中提到）。

下面的三条SQL语句，就是这个实验过程。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where 
【内容或版权原因，部分内容未显示】...

页；

  • 写redo log。这个redo log包含了数据的变更和change buffer的变更。
    • 


    到这里merge过程就结束了。这时候，数据页和内存中change buffer对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。
    

    阅读原文
    

    

    本文收藏来自互联网，仅用于学习研究，著作权归原作者所有，如有侵权请联系删除
    

    markdown @tsingchan
    

    

    部分引用格式为收藏注解，比如本句就是注解，非作者原文。