MySQL深度解析--优化器

优化器

Ⅰ.优化器的索引选择

一些概念：

优化器：选择SQL语句执行使用的索引，找到一个最优的执行方案，并用最小的代价去执行语句。
优化器的执行代价：扫描行数、是否使用临时表、是否排序

扫描行数
扫描行数是根据统计信息来估算的一个数。统计信息指索引的区分度，一个索引上，不同的值的个数（基数[cardinality]）越多，区分度就越好。
基数是通过采样统计的，取N个数据页，统计基数，得到一个平均值，然后再乘以索引的页面数。当修改数据行超过一定阈值后，会自动触发重新统计一次基数。
参数 innodb_stats_persistent

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

analyze table t # 重新统计索引信息，修正统计信息

#force index --- 强制使用某个索引
select * from t force index(a) where a between 10000 and 20000;

优化器的选择逻辑：

• 如果使用普通索引，会把回表的开销也考虑进去。

优化器选择错误的处理方法：

• 采用force index强行选择一个索引
• 修改语句，引导MySQL使用我们期望的索引
• 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引