艹蛋的2020即将结束,期待2021年的到来。
选择索引是优化器的工作。查询优化器是专门负责优化查询语句的优化器模块,通过计算分析收集的各种系统统计信息,为查询给出最优的执行计划——最优的数据检索方式。
一、影响优化器索引选择的因素
优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。
当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
二、优化器索引选择
扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。Mysql在执行sql之前是不能精确知道满足条件的记录有多少条,而是根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。
1、索引信息查询
我们可以使用以下命令查询某个数据表的索引信息:
1 | mysql> SHOW INDEX FROM {table_name}; |
字段名称 | 描述 |
---|---|
Table | 表示创建索引的数据表名 |
Non_unique | 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为0 |
Key_name | 表示索引的名称 |
Seq_in_index | 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序 |
Column_name | 表示定义索引的列字段 |
Collation | 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类 |
Cardinality | 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大 |
Sub_part | 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NUL |
Packed | 指示关键字如何被压缩。若没有被压缩,值为 NULL |
Null | 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO |
Index_type | 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE) |
Comment | 显示评注 |
Index_omment | 创建索引时注释属性的内容 |
2、Cardinality
- 列值代表的是此列中存储的唯一值的个数(如果此列为primary key 则值为记录的行数)
- 列值只是个估计值,并不准确。
- 列值不会自动更新,需要通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库。
- 列值的大小影响Join时是否选用这个Index的判断。
- 创建Index时,InnoDB的表Cardinality的值大概为行数。
把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。
采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过1/M的时候,会自动触发重新做一次索引统计。在MySQL中,有两种存储索引统计的方式,可以通过设置参数innodb_stats_persistent的值来选择:
- 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
- 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
由于是采样统计,所以不管 N 是 20 还是 8,这个基数都是很容易不准的。
3、其他
在使用非聚簇索引的时候,Mysql优化器也会将回表的代价计算进去。因此优化器有时会直接选择全表扫描,即使从执行时间上看,全表扫描并不是最优的。
三、处理方式
1、ANALYZE TABLE T
1 | mysql> ANALYZE TABLE T |
既然是统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息。
执行 analyze table 时,会对表加上读锁(read lock)
- analyze table的需要扫描的page代价粗略估算公式:sample_pages * 索引数 * 表分区数。
- 因此,索引数量较多,或者表分区数量较多时,执行analyze table可能会比较费时,要自己评估代价,并默认只在负载低谷时执行。
- 如果某个表上当前有慢SQL,此时该表又执行analyze table,则该表后续的查询均会处于waiting for table flush的状态,严重的话会影响业务,因此执行前必须先检查有无慢查询。
2、FORCE INDEX
使用FORCE INDEX会让Mysql按照我们预想的索引执行查询而不去进行索引优化判断,但是这样的语句并不优美。当表的索引变更,sql语句也要同时修改。
3、修改sql引导Mysql使用我们期望的索引
根据索引的特征修改sql引导Mysql选择我们期望的索引,但是这种方式并不通用
3、优化索引
我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。