强制索引
通过FORCE INDEX(索引1[,索引2])或者使用USE INDEX(索引1[,索引2]),来指定使用哪个索引,也可以指定多个索引,让
优化器从中挑选。
忽略索引
可以使用IGNORE INDEX(索引1[,索引2])来忽略一些索引,这样优化器,就不会考虑使用这些所有,减少优化器优化时间。
STRAGHT_JOIN
这个会优化器使用数据表的顺序
一般情况下,MySQL优化器会自行决定按照哪种顺序扫描数据表才能最快地检索出数据,但是我们可以通过STRAGHT_JOIN强制优化器按特定的顺序使用数据表,毕竟优化器做的判断不一定都是最优的。
使用原则是:
让限制最强的选取操作最先执行。 STRAIGHT_JOIN可以放在SELECT后面,也可以放在FROM子句中。
注:STRAIGHT_JOIN只适用于inner join,并不使用与left join,right join。
6.2 查询优化器所做的事情
常量转化
它能够对sql语句中的常量进行转化,比如下面的表达式: WHERE col1 = col2 AND col2 = ‘x’; 依据传递性:如果A=B and B=C,那么就能得出A=C。所以上面的表达式mysql查询优化器能进行如下的优化:WHERE col1 = ‘x’ AND col2 = ‘x’
无效代码的排除
查询优化器会对一些无用的条件进行过滤,比如说 WHERE 0=0 AND column1=’y’ 因为第一个条件是始终为true的,所以可以移除该条件,变为:WHERE column1=’y’再见如下表达式:WHERE (0=1 AND s1=5) OR s1=7因为前一个括号内的表达式始终为false,因此可以移除该表达式,变为:WHERE s1=7
一些情况下甚至可 以将整个WHERE子句去掉,见下面的表达式:WHERE (0=1 AND s1=5)我们可以看到, WHERE子句始终为FALASE,那么WHERE条件是不可能发生的。当然我们也可以讲,WHERE条件被优化掉了
常量计算
如下表达式:WHERE col1 = 1 + 2转化为:WHERE col1 = 3 Mysql会对常量表达进行计算,然后将结果生成条件
存取类型
当我们评估一个条件表达式,MySQL判断该表达式的存取类型。下面是一些存取类型,按照从最优到最差的顺序进行排列:
system系统表,并且是常量表
const 常量表
eq_ref unique/primary索引,并且使用的是’=’进行存取
ref 索引使用’=’进行存取
ref_or_null 索引使用’=’进行存取,并且有可能为NULL
range 索引使用BETWEEN、 IN、 >=、 LIKE等进行存取
ALL 表全扫描
优化器根据存取类型选择合适的驱动表达式。考虑如下的查询语句:以下是引用片段:
SELECT * FROM Table1 WHERE indexed_column=5 AND unindexed_column=6
因为indexed_column拥有更好的存取类型,所以更有可能使用该表达式做为驱动表达式。考虑到这个查询语句有两种可能的执行方法:
1) 不好的执行路径:读取表的每一行(称为“全表扫描” ),对于读取到的每一行,检查相应的值是否满足indexed_column以及 unindexed_column对应的条件。
2) 好的执行路径:通过键值indexed_column=5查找B树,对于符合该条件的每一行,判断是否满足unindexed_column对应的条件。
一般情况下,索引查找比全表扫描需要更少的存取路径,尤其当表数据量很大,并且索引的类型是UNIQUE的时候。因此称它为好的执行路径,使用 indexed_column列作为驱动表达式。
范围存取类型
一些表达式可以使用索引,但是属于索引的范围查找。这些表达式通常对应的操作符是:>、 >=、 <、 <=、IN、 LIKE、 BETWEEN。
对优化器而言,如下表达式:
column1 IN (1,2,3)
该表达式与下面的表达式是等价的:
column1 = 1 OR column1 = 2 OR column1 = 3
并且 MySQL也是认为它们是等价的,所以没必要手动将IN改成OR,或者把OR改成IN。
优化器将会对下面的表达式使用索引范围查找:column1 LIKE ‘x%’,但对下面的表达式就不会使用到索引了:column1 LIKE ‘%x’,这是因为当首字符是通配符的时候, 没办法使用到索引进行范围查找。
对优化器而言,如下表达式:
column1 BETWEEN 5 AND 7
该表达式与下面的表达式是等价的:
column1 >= 5 AND column1 <= 7同样,
MySQL也认为它们是等价的。