【MySql】10 笔记 MySQL 执行选错索引

一、问题

1、一条本来可以执行得很快的语句，却由于 MySQL 选错 了索引，而导致执行速度变得很慢?

2、例子

1. 建表

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

2. 插入数据

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

3. 查询 执行explain

# 查询
> select * from t where a between 10000 and 20000;
# 执行explain
> explain select * from t where a between 10000 and 20000;

4. 操作

a: session A 的操作开启了一个事务。随后，session B把数据都删除后，又调用了idata存储过程，插入了 10 万行数据。

session A	session B
start transaction with consistent snapshot;
无	delete from t;call idata();
无	explain select * from where a between 10000 and 20000;
commit;

b: session B 的查询语句 select * from t where a between 10000 and 20000 就 不会再选择索引 a 了。可以通过慢查询日志(slow log)来查看一下具体的执行情况。

# 将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查 询日志中;
> set long_query_time=0; 

# session B 原来的查询
> select * from t where a between 10000 and 20000; 

# 加了 force index(a) 查询
> select * from force index(a) where a between 10000 and 20000; 

c: 慢查询日志 或者 用explain 计划

二、优化器逻辑

1、目的：

1. 找到一个最优的执行方案，并用最小的代价去执行语句。

2. 在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的 次数越少，消耗的 CPU 资源越少。

3. 优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

2、扫描行数判断？

1. MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而是根据统计信息来估算记录数。

2. 统计信息: 是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。

3. 基数(cardinality): 一个索引上不同的值的个数,基数越大，索引的区分度越好。

> show index from `t`

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

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

b: 当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

5. MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择

a: 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。

b: 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

3、索引选择异常和处理

1. 多数时候优化器都能找到正确的索引,如果遇到执行SQL 比你预期的慢。

2. 优化器认为直接扫描主键索引更快,使用普通索引需要把回表的代价算进去。

3. analyze table t 命令，可以用来重新统计索引信息(索引统计不准确)

采用 force index 强行选择一个索引

修改语句，引导 MySQL使用期望的索引

新建一个更合适的索引，来提供给优化器做选择， 或删掉误用的索引。