Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

10 | MySQL为什么有时候会选错索引? #19

Open
git-zjx opened this issue Jul 24, 2019 · 0 comments

Comments

@git-zjx
Copy link
Owner

commented Jul 24, 2019

优化器的逻辑

优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。
扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行数是怎么判断的?

MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。
可以使用 show index 方法,看到一个索引的基数

MySQL 是怎样得到索引的基数的呢?

使用采样统计的方法,因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了

采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:

  • 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
  • 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。

analyze table t 命令,可以用来重新统计索引信息

索引选择异常和处理

  1. 采用 force index 强行选择一个索引
  2. 修改语句,引导 MySQL 使用我们期望的索引
  3. 新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引

问题

  1. 如果某次写入使用了 change buffer 机制,之后主机异常重启,是否会丢失 change buffer 和数据?
    不会丢失,虽然是只更新内存,但是在事务提交的时候,我们把 change buffer 的操作也记录到 redo log 里了,所以崩溃恢复的时候,change buffer 也能找回来
  2. merge 的过程是否会把数据直接写回磁盘?
    merge 的执行流程是这样的:
  • 从磁盘读入数据页到内存(老版本的数据页);
  • 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个),依次应用,得到新版数据页;
  • 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
    到这里 merge 过程就结束了。这时候,数据页和内存中 change buffer 对应的磁盘位置都还没有修改,属于脏页,之后各自刷回自己的物理数据,就是另外一个过程了。
  1. 假如要查 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)
  2. 删除索引的时候能做到秒删,这个什么原理呢?
    标记删除

@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Jul 24, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
1 participant
You can’t perform that action at this time.