数据库优化器什么情况下放弃索引

1. Oracle数据库强制索引

当where子句对某一列使用函数时 除非利用这个简单的技术强制索引 否则Oracle优化器不能在查询中使用索引

通常情况下 如果在WHERE子句中不使用诸如UPPER REPLACE 或SUBSTRD等函数 就不能对指定列建立特定的条件 但如果使用了这些函数 则会出现一粗闹个问题 这些函数会阻碍Oracle优化器对列使用索引 因而与采用索引的情况相比较 查询会花费更多的时间

庆幸的是 如果在使用函数的这些列中包含了字符型数据 可以用这样一种方法修改查询语句 以达到强制性使用索引 更有效地运行查询 这篇文章介绍了涉及的技术 并说明了在两种典型情况下怎样实现

大小写混合情况

在讨论由于函数修改了列的内容 如何强制使用索引前 让我们首先看看为什么Oracle优化器在这种情况下不能使用索引 假定我们要搜寻包含了大小写混合的数据 如在表 中ADDRESS表的NAME列 因为数据是用户输入的 我们无法使用已经统一改为大写的数据 为了找到每一个名为john的地址 我们使用包含了UPPER子句的查询语句 如下所示

SQL> select address from address where upper(name) like JOHN ;

在运行这个查询语句前 如果我们运行了命令 set autotrace on 将会得到下列结果 其中包含了执行过程

ADDRESS cleveland row selected Execution Plan SELECT STATEMENT TABLE ACCESS FULL ADDRESS

可以看到 在这种情况下 Oracle优化器对ADDRESS 表作了一次完整的扫描 而没有使用NAME 列的索引 这是因为索引是根据列中数据的实际值建立的 而UPPER 函数已经将字符转换成大写 即修改了这些值 因此该查询不能使用这列的索引 优化器不能与索引项比较 JOHN 没有索引项对应于 JOHN 只有 john

值得庆幸的是 如果在这种情况下想要强制使用索引 有一种简便的方法 只要在WHERE 子句中增加一个或多个特定友凳扮的条件 用于测试索引值 并减少需要扫描的行 但这并没有修改原来SOL 编码中的条件 以下列查询语句为例

SQL> select address from address where upper(name) like JO% AND (name like J% or name like j% );

使用这种查询语句（已设置AUTOTRACE） 可得到下列结果

ADDRESS cleveland row selected Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I

现在 优化器为WHERE 子句中AND 联结的两个语句中每一个语句确定的范围进行扫描 第二个语句没有引用函数 因而使用了索引 在两个范围扫描后 将运行结果合并

在这个例子中 如果数据库有成百上千行 可以用下列方法扩充WHERE 子句 进一步缩小扫描范围

select address from address where upper(name) like JOHN AND (name like JO% or name like jo% or name like Jo or name like jO );

得到的结果与以前相同 但是 其执行过程如好灶下所示 表明有 个扫描范围

Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I

如果试图进一步提高查询速度 我们可以在特定的 name like 条件中指明 个或更多的字符 然而 这样做会使得WHERE子句十分笨重 因为需要大小写字符所有可能的组合 joh Joh jOh joH等等 除此之外 指定一个或两个字符已足以加快查询的运行速度了

现在让我们看看 当我们引用不同的函数时 怎样运用这个基本技术

使用REPLACE的情况

正如名字不总是以大写输入一样 电话号码也会以许多格式出现 如 （ ） 等等

如果在列名为 PHONE_NUMBER中搜寻上述号码时 可能需要使用函数REPLACE以保证统一的格式 如果在PHONE_NUMBER列中只包含空格 连字符和数字 where 子句可以如下所示

WHERE replace(replace(phone_number ) ) =

WHERE子句两次使用REPLACE 函数去掉了连字符和空格 保证了电话号码是简单的数字串 然而 该函数阻止了优化器在该列使用索引 因此 我们按如下方法修改WHERE子句 以强制执行索引

WHERE replace(replace(phone_number ) ) = AND phone_number like %

如果我们知道数据中可能包含圆括号 WHERE 子句会稍微复杂一点 我们可以再增加REPLACE 函数（去掉圆括号 连字符和空格） 按如下所示扩充增加的条件

WHERE replace(replace(replace(replace(phone_number ) ) ( ) ) ) = AND (phone number like % or phone_number like ( % )

该例强调了巧妙地选用WHERE 子句条件的重要性 而且 这些条件不会改变查询结果 你的选择应基于完全了解该列中存在的信息类型 在该例中 我们需要知道 PHONE_NUMBER 数据中存在几种不同的格式 这样 我们能够修改WHERE 子句而不会影响查询结果

正确的条件 lishixin/Article/program/Oracle/201311/18519

2. 分析SQL执行过程中，哪些SQL条件会走索引

这样回答你，以下几种情况sql中索引不会被用到
1、查询谓词没有使用索引的主要边界,换句话说就是select *，可能会导致不走索引。
比如，你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引，但是优化器会认为需要一行行的扫描会更有效，这个时候，优化器可能会选择TABLE ACCESS FULL，但是如果换成了SELECT Y FROM T WHERE Y = XXX，优化器会直接去索引中找到Y的值，因为从B树中就可以找到相应的值。

2、单键值的b树索引列上存在null值，导致COUNT(*)不能走索引。
如果在B树索引中有一个空值，那么查询诸如SELECT COUNT(*) FROM T 的时候，因为HASHSET中不能存储空值的，所以优化器不会走索引，有两种方式可以让索引有效，一种是SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL或者把这个列的属性改为not null (不能为空)。

3、索引列上有函数运算，导致不走索引
如果在T表上有一个索引Y，但是你的查询语句是这样子SELECT * FROM T WHERE FUN(Y) = XXX。这个时候索引也不会被用到，因为你要查询的列中所有的行都需要被计算一遍，因此，如果要让这种sql语句的效率提高的话，在这个表上建立一个基于函数的索引，比如CREATE INDEX IDX FUNT ON T(FUN(Y));这种方式，等于Oracle会建立一个存储所有函数计算结果的值，再进行查询的时候就不需要进行计算了，因为很多函数存在不同返回值，因此必须标明这个函数是有固定返知皮禅回值的。

4、隐式转换导致不走索引。
索引不适用于隐式转换的情况，比如你的SELECT * FROM T WHERE Y = 5 在Y上面有一个索引，但是Y列是VARCHAR2的，那么Oracle会将上面的5进行一个隐式的转换，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。

5、表的数据库小或者需要选择大部分数据，不走索引
在Oracle的初始化参数中，有一个参数是一次读取的数据块的数目，比如你的表只有几个数据块大小，而且可以被Oracle一次性抓取，那么就没有使用索引的必要了，因为抓取索引还需要去根据rowid从数据块中获取相应的元素值，因此在表特别小的情况下，索引没有用到是情理当中的事搭尘情。
6、cbo优化器下统计信息不准确，导致不走索引
很长时间没有做表分析，或者重新收集表状态信息了，在数据字典中，表的统计信息是不准确的，这个情况下，可能会使用错误的索引，这个效率可能也是比较低的。
7、！=或者<>(不等于），可能导致不走索引，也可能走 INDEX FAST FULL SCAN
例如select id from test where id<>100
8、表字段的属性导致不走索引，字符型的索引列会导致优化器认为需要扫描索引大握数部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式，
由于字符型和数值型的在insert的时候排序不同，字符类型导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说，就是按照数字类型插入（1..3200000），按字符类型（'1'...'32000000'）t排序，在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。

3. oracle索引为什么会选不是最合适的索引

Oracle选择索引的过程是由查询优化器来完成的，其目标郑拍是尽可能地优化查询性能，最终选择最合适的索引。但是有时候，优化器也会选择不是最合适的索引，这可能是由以下原因造成的：

1. 统计信息不准确：查询优化器在选择索引时需要依赖数据表的统计信息，包括行数、列的基数和直方图等。如果这些统计信息不准确或者过时，就会导致查询优化器选择不合适的索引。

2. 索引失效：如果查询中含有不等于（<>）或者非（NOT）操作符，那么索引将无法使用。此时，查询优化器可能会选择其他索引或全表扫描。

3. 索引使用成本高：有时候，一个索引虽然能够帮助查询优化器加速查询速度，但是索引本身的使用成本可能很高。例如，在某些情况下，对索引进行大量的随机访问可能比执行全表扫描更为低效。

4. 数据分布均匀：当表的某个列的值分布比较均匀时，使用索引可能不如全表扫描更有效率。因为查询优化器前大需要在索引和表之间来回切换，消耗了额外的资源。

在实际应用中，优化查询性能是一个复慧丛竖杂的过程，需要仔细考虑多个因素。因此，在设计索引时，需要仔细评估不同的因素，以确定最佳的索引方案。

4. mysql索引问题

1.首选数据库都会有自动优化查询计划的能力，在语句一中，明显对seq进行了排序，而is_need_udate用in进行毁告范围查询，使用index2，开销就会小很多，但是语句二中is_need_update没有这个了，所以才会使用index1.
2.所以建立的原则
2.1根据对应表查询频率最高的属颤余闹性建立索引
2.2为经常需要排序，分组的字段茄罩建立索引
2.3尽量使用数据量少的索引
建议详细的使用方法看看书吧，数据库的优化是一门大学问，值得好好研究的

5. 数据库的多表大数据查询应如何优化

1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：x0dx0aselect id from t where num is nullx0dx0a可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：x0dx0aselect id from t where num=0x0dx0a2.应尽量避免在 where 子句颤洞中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。x0dx0a3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：x0dx0aselect id from t where num=10 or num=20x0dx0a可以这样查询：x0dx0aselect id from t where num=10x0dx0aunion allx0dx0aselect id from t where num=20x0dx0a4.in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：x0dx0aselect id from t where num in(1,2,3)x0dx0a对于连续的数值，能用 between 就不要用 in 了：x0dx0aselect id from t where num between 1 and 3x0dx0a5.尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。 x0dx0a见如下例子： x0dx0aSELECT * FROM T1 WHERE NAME LIKE ‘%L%’ x0dx0aSELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’ x0dx0aSELECT * FROM T1 WHERE NAME LIKE ‘L%’ x0dx0a即使NAME字段建有索引，前两个查询依然无法利用春哗索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。x0dx0a6.必要时强制查询优化器使用某个索引，如在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：x0dx0aselect id from t where num=@numx0dx0a可以改为强制查询使用索引：x0dx0aselect id from t with(index(索引名)) where num=@numx0dx0a7.应尽量避免在 where 子句中对字段进行表达式操扒洞行作，这将导致引擎放弃使用索引而进行全表扫描。如：x0dx0aSELECT * FROM T1 WHERE F1/2=100 x0dx0a应改为: x0dx0aSELECT * FROM T1 WHERE F1=100*2x0dx0aSELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’ x0dx0a应改为: x0dx0aSELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’x0dx0aSELECT member_number, first_name, last_name FROM members x0dx0aWHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 x0dx0a应改为: x0dx0aSELECT member_number, first_name, last_name FROM members x0dx0aWHERE dateofbirth < DATEADD(yy,-21,GETDATE()) x0dx0a即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。x0dx0a8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：x0dx0aselect id from t where substring(name,1,3)='abc'--name以abc开头的idx0dx0aselect id from t where datediff(day,createdate,񟭅-11-30')=0--‘2005-11-30’生成的idx0dx0a应改为:x0dx0aselect id from t where name like 'abc%'x0dx0aselect id from t where createdate>=񟭅-11-30' and createdate<񟭅-12-1'x0dx0a9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。x0dx0a10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。x0dx0a11.很多时候用 exists是一个好的选择：x0dx0aelect num from a where num in(select num from b)x0dx0a用下面的语句替换：x0dx0aselect num from a where exists(select 1 from b where num=a.num)x0dx0aSELECT SUM(T1.C1)FROM T1 WHERE( x0dx0a(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0) x0dx0aSELECT SUM(T1.C1) FROM T1WHERE EXISTS( x0dx0aSELECT * FROM T2 WHERE T2.C2=T1.C2) x0dx0a两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。

6. 在SQL语句的where子句中对存在索引的列使用函数时，为什么Oracle优化器会忽略掉这些索引

where子毕绝句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
因为SQL只有在运行时才会解析局部变量，但优化程序不能将尘芹访问计划的选择推迟到运行时；它必须在编手兄姿译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

7. SQL优化万能公式：5 大步骤 + 10 个案例

在应用开发的早期，数据量兆尺键少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，困链有时可能这些有问题的SQL就是整个系统性能的瓶颈。

1、通过慢查日志等定位那些执行效率较低的SQL语句

2、explain 分析SQL的执行计划

type由上至下，效率越来越高

Extra

3、show profile 分析

了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”

4、trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

5、确定问题并采用相应的措施

案例1、最左匹配

索引

SQL语句

查询匹配从左往右匹配，要使用order_no走索引，必须查询条件携带shop_id或者索引( shop_id , order_no )调换前后顺序

案例2、隐式转换

索引

SQL语句

隐式转换相当于在索引上做运算，会让索引失效。mobile是字符类型，使用了数字，应该使用字符串匹配，否则MySQL会用到隐式替换，导致索引失效。

案例3、大分页

索引

SQL语句

对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式， 一种是把上一次的最后一条数据，也即上面的c传过来，然后做“c < xxx”处理，但是这种一般需要改接口协议，并不一定可行。另一种是采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果，SQL改动如下

案例4、in + order by

索引

SQL语句

in查询在MySQL底层是族巧通过n*m的方式去搜索，类似union，但是效率比union高。in查询在进行cost代价计算时（代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(eq_range_index_pe_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。

处理方式，可以( order_status , created_at )互换前后顺序，并且调整SQL为延迟关联。

案例5、范围查询阻断，后续字段不能走索引

索引

SQL语句

范围查询还有“IN、between”

案例6、不等于、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、>、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、优化器选择不使用索引的情况

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。

查询出所有未支付的订单，一般这种订单是很少的，即使建了索引，也没法使用索引。

案例8、复杂查询

如果是统计某些数据，可能改用数仓进行解决；如果是业务上就有那么复杂的查询，可能就不建议继续走SQL了，而是采用其他的方式进行解决，比如使用ES等进行解决。

案例9、asc和desc混用

desc 和asc混用时会导致索引失效

案例10、大数据

对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存。那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理。

8. 技术分享 | 为什么 SELECT 查询选择全表扫描，而不走索引

SQL的执行成本（cost）是 MySQL 优化器选择 SQL 执行计划时一个重要考量因素。当优化器认为使用索引的成本高于全表扫描的时候，优化闹雹器将会选择全表扫描，而不是使用索引。

下面通过一个实验来说明。

如下结构的一张表，表中约有104w行数据：

查询1，并未用到ct_index(create_time)索引:

而查询2，则用到了ct_index(create_time)索引:

这里使用optimizer trace工具，观察MySQL对SQL的优化处理过程：

获得关于此SQL的详细优化器处理信息：

通过逐行阅读，发现优化器在join_optimization（SQL优化阶段）部分的rows_estimation内容里：

通过观察优化器的信息，不难发现，使用索引扫描行数约52w行，而全表扫描约为104w行。为什么优化器反宽弯猛而认为使用索引慎桥的成本比全表扫描还高呢？

因为当ct_index(create_time)这个普通索引并不包括查询的所有列，因此需要通过ct_index的索引树找到对应的主键id，然后再到id的索引树进行数据查询，即回表（通过索引查出主键，再去查数据行），这样成本必然上升。尤其是当回表的数据量比较大的时候，经常会出现MySQL优化器认为回表查询代价过高而不选择索引的情况。

这里可以回头看查询1 和 查询2的数据量占比：

另外，在MySQL的官方文档中对此也有简要的描述：

https://dev.mysql.com/doc/refman/5.7/en/where-optimization.html

参考文档：

https://opensource.actionsky.com/20201127-mysql/

https://blog.csdn.net/CSDNcircular/article/details/107253747

9. 索引的用途,在什么情况下索引会降低数据库操作性能

索引可以加快查询速度，但是会降低增加、删除老友、修改的速度
如果你的数据表，增加删除修改的时候比查询的时候侍液槐多，那么用索引就会降低性能埋弯

10. MySQL 索引优化器选择索引的规则是什么

在键租谨开始演示之前，我们先介绍下两个概念。

概念一，数据的可选择性基数，也就是常说的cardinality值。

查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步操作所涉及到的记录数，而这个相关数据就是cardinality。简单来说，就是每个值在每个字段中的唯一值分布状态。

比如表t1有100行记录，其中一列为f1。f1中唯一值的个数可以是100个，也可以是1个，当然也可以是1到100之间的任何一个数字。这里唯一值越的多少，就是这个列的可选择基数。

那看到这里我们就明白了，为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面，至于更深入的探讨就不在我这篇探讨的范围了。

概念二，关于HINT的使用。

这里我来说下HINT是什么，在什么时候用。

HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。

比如：表t1经过大稿基量的频繁更新操作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？

来看下具体演示

譬如，以下两条SQL，

• A：

• select * from t1 where f1 = 20;



• B：

• select * from t1 where f1 = 30;



如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等，那么对这两条语句来说，可能不准确的就是B了。

这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。

那回到正题上，MySQL 8.0 带来了几个HINT，我今天就举个index_merge的例子。

示例表结构：

• mysql> desc t1;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || rank1 | int(11) | YES | MUL | NULL | || rank2 | int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc1 | text | YES | | NULL | || rank3 | int(11) | YES | MUL | NULL | 型启 |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)



表记录数：


• mysql> select count(*) from t1;+----------+| count(*) |+----------+| 32768 |+----------+1 row in set (0.01 sec)



这里我们两条经典的SQL：

• SQL C：

• select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;



• SQL D：

• select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;



表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然，没有用到任何索引，扫描的行数为32034，cost为3243.65。

• mysql> explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3243.65" }, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "rows_examined_per_scan": 32034, "rows_proced_per_join": 115, "filtered": "0.36", "cost_info": { "read_cost": "3232.07", "eval_cost": "11.58", "prefix_cost": "3243.65", "data_read_per_join": "49K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)



我们加上hint给相同的查询，再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍。

• mysql> explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "441.09" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "union(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1103, "rows_proced_per_join": 1103, "filtered": "100.00", "cost_info": { "read_cost": "330.79", "eval_cost": "110.30", "prefix_cost": "441.09", "data_read_per_join": "473K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)



我们再看下SQL D的计划：

• 不加HINT，

• mysql> explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "534.34" }, "table": { "table_name": "t1", "access_type": "ref", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "idx_rank1", "used_key_parts": [ "rank1" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 555, "rows_proced_per_join": 0, "filtered": "0.07", "cost_info": { "read_cost": "478.84", "eval_cost": "0.04", "prefix_cost": "534.34", "data_read_per_join": "176" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" } }}1 row in set, 1 warning (0.00 sec)



• 加了HINT，

• mysql> explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.23" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "intersect(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1, "rows_proced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "5.13", "eval_cost": "0.10", "prefix_cost": "5.23", "data_read_per_join": "440" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" } }}1 row in set, 1 warning (0.00 sec)



对比下以上两个，加了HINT的比不加HINT的cost小了100倍。

总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。