MySQL - 数据库选错索引怎么办 - 颇忒脱的技术博客

在基础架构里提到在执行查询时优化器负责选择使用哪个索引。

实验1

建表：

1
2
3
4
5
6
7
8


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；

插入数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

用explain`来观察MySQL会选择哪个索引：

1

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

结果表明MySQL会选择索引a，并且预计扫描10001行，为什么是10001行而不是10000行？这是因为在扫描的时候要扫描到第一个不满足条件的数据为止，因此会多扫一行。

选错索引的逻辑

优化器选择索引考虑的因素：

cardinality（基数），基数代表区分度，基数越大区分度则越大，不同值越多则区分度越大，区分度大的索引被选择的概率大

1

mysql> show index from t;

基数的值并非精确值而是一个估算值，InnoDB选取N个数据页统计不同值，计算基数平均值。当更新的行超过1/M时，重新计算基数。可以用innodb_stats_persistent来控制这个统计信息存在哪里。

预估执行该语句本身会扫描多少行，同时会预估回表的代价

纠正办法

重新统计索引信息：

1

ANALYZE TABLE t;

强制告诉使用哪个索引，force index：

1

select * from t force index(a) where ...

其他tricky的方法，这里不做介绍了。

MySQL - 数据库选错索引怎么办

实验1

选错索引的逻辑

纠正办法

版权

评论

MySQL - 数据库选错索引怎么办

实验1

选错索引的逻辑

纠正办法

版权

相关文章

评论