mysql选错索引怎么解决_mysql为什么会选错索引

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；

用存储过程插入100000条数据

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();

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

set sql_long_query=0;这里表示接下来所有的查询都会进入到慢查询日志

select * from t where a between 10000 and 20000; /*Q1*/

select * from t force index(a) where a between 10000 and 20000;/*Q2*/

第一条是全表扫描，第二条扫描了10000条

二：优化器的逻辑

1.查看索引扫描的行数

2.索引基数是如何统计的

选出某几个页，算出这几个页的平均行数，然后乘以总页数

这里表明数据库的索引选择会错误，优化器不只会考虑扫描行数，会把回表的查询也会考虑进去。

看下边的这个语句：

mysql> select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

a 索引扫描1000条，b 索引扫描了50000条，但是用了索引B ,

三：索引选择和异常处理：

1：使用force index,问题是

2: 考虑修改语句，比如，

mysql> 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;

3，删除不适合的索引或者创建合适的索引

总结：频繁插入删除的表会有选错索引的情况

原文：https://www.cnblogs.com/hanguocai/p/10071844.html