MySQL-10：MySQL的索引选择

目的： MySQL在执行一个sql时，我们并没有主动指定使用哪个索引，具体的索引选择工作是由MySQL的优化器做的。忘记的可以去看下MySQL的基础架构。

第一步： 创建一个表并往里面插入数据，我们插入的数据数量为100000条

    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；
    
    //插入数据语句
    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个索引，一个主键索引和两个非主键索引(‘a’和’b’)，我们可以用show index指令来看下索引情况。

项目错误实现暂时复现不了!!!

MySQL在某些情况下出现扫描行数不准的情况，下面给出一张图，结合上面的数据将就看下，rows就是预估的扫描行数，从正常情况来看，第一句sql不应该是10000+的扫描行。

10.1扫描行数

优化器选择索引的目的是找到一个最优方案去地执行语句。优化器选择标准有：扫描行数，是否使用临时表，是否排序等因素。

我们先从扫描行数出发，那扫描行数是如何确认的呢？

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

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

我们可以通过show index来查看基数值，字段Cardinality就是。

那MySQL如何得到索引的基数呢？

MySQL采用采样统计的方式，InnoDB选取N个数据页，统计页面上的不同值，得到平均数，再乘以数据页的数量，就得到了索引的基数值。当数据修改的行数超过1/M时，会自动触发重做一次索引统计。

在MySQL中可以通过设置参数 “ innodb_stats_persistent ” ：

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

优化器结合索引的基数来对扫执行语句进行预估扫描行数，这来看明显是一次错误预估。

当扫描行数预估错误时，可以用analyze table tableName来进行修复扫描行数的错误问题。

10.2 索引选择错误处理

上面的数据样本继续，执行如下sql：

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

优化器会选择索引b，而不是a，但a明明会更少。 优化器之所以选择使用索引 b，是因为它认为使用索引 b 可以避免排序，所以即使扫描行数多，也判定为代价更小。

当出现索引选择错误的时候，我们可以从以下几点出发：

1. 使用 froce index(索引名)来强行指定即将使用索引
2. 可以考虑修改语句，引导 MySQL 使用我们期望的索引 ，例如我们将上面的语句修改为order by a,b即可。
3. 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

练习问题：

1. mysql如何预估一个查询的扫描行数?
2. 如何获得索引基数? 通过设置哪个参数可以修改索引统计的存储方式?
3. 如何进行重新统计索引信息?
4. 如何处理索引选择异常问题?
5. 索引选择异常的问题有哪几种处理方式?