MySQL-10：MySQL的索引选择

我就是我 2022-12-24 05:54 96阅读 0赞

**目的：** 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+的扫描行。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

### 10.1扫描行数 ###

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

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

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

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

我们可以通过show index来查看基数值，字段Cardinality就是。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]  
**那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.  索引选择异常的问题有哪几种处理方式?

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/780301495e574472b936acfc432d959e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/8824ae8c52464297b78e6ecb71f5a3b9.png